Mysql学习-sql优化

1. 选择优化的数据类型

1）更小的通常更好：一般情况下，尽量使用可以正确存储数据的最小数据类型。

2）简单就好：简单数据类型的操作通常需要更小的CPU周期

3）尽量避免NULL：很多表都包含可为NULL（空值）的列，通常情况下最好指定为NOT NULL。因为如果查询中包含可为NULL的列，对于Mysql来说更难优化。

4）选择具体的数据类型

使用枚举代替字符串类型，对于确切的分类类型，可以采用ENUM，而不是字符串类型，除此之外还可以在java代码中采用枚举。

2.三范式和反三范式混用

三范式和反三范式在实际项目中，通常是混用的，因为对于设计冗余字段，可以避免外键约束。

3..使用物化视图

预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。

4. Mysql索引

Mysql索引为什么会采用B+Tree，需要考虑以下几个问题：

对具体的数据可以快速搜索、如何快速查找区间数据、支持模糊查询、支持分页、支持排序(正序和逆序)。

解决第一个问题的方案：我们能快速想到的就是Hash，因为Hash采用函数的方式可以映射，有点类似f(x)=y这样，给定一个x的key，就会输出一个y的value。其定位为精确查找，而对于排序、模糊查询、区间匹配是不支持的，同时存在hash 冲突的情况，不能使用索引中的值来避免读取行。因此，在Mysql中，只有Memory引擎显式支持哈希索引，同时这也是Memory引擎表的默认索引类型。NDB集群索引支持唯一哈希索引。

考虑方案树tree，因为tree的时间复杂度是log2N，相比其他数据结构，其具有优越性，同时二叉查找树，其性能就会更好，因为其本身就带排序功能。但其不能保证平衡，因此就需要进一步考虑平衡树了，因此可以考虑红黑树、B树，因此进一步考虑B+树。B+树的优越性在于其可以解决排序、模糊查询、树的平衡、树的时间复杂度、分页等问题，因此采用B+树索引。同时按顺序存储的，并且每一个叶子页到根的距离都相等。

正是它的结构：因此其可以全值匹配、匹配最左前缀、匹配范围值等。

空间数据索引R-tree：在使用MyISAM引擎时，其支持空间索引，可以用作地理数据存储。

使用索引的优点：

索引大大减少了服务器需要扫描的数据量、帮助服务器避免排序和临时表、将随机I/O变为顺序I/O。通常对于中型和大型的表，索引就是十分有效的，而对于特大型的表，采用分区技术。

多列索引：通常的思路时将索引建立在where条件上，同时进行前缀匹配。也即：

对应组合索引index(A，B，C)，采用前缀匹配，也即匹配到A，再会进行B、C的匹配，如果不匹配A，后面的索引是不会进行匹配的，只有匹配了A，后面的B、C才会进行匹配。索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的order by、group by、district等子句的查询需求。

三星索引：索引将相关的记录放在一起则获得一星，如果索引中的数据顺序和排序顺序一致则获得二星，如果索引中的列包含了查询中需要的全部列则获得三星。

如何选择索引的列顺序的经验法则：将选择性最高的列发到索引最前列，在某些场景下有帮助，但通常不如避免随机I/O和排序那么重要。当不考虑排序分组时，将选择性最高的列放在前面通常是很好的 。这时候索引的作用只是用于WHERE条件的查找。考虑将选择性最高的作为索引的第一列，而不是某个具体的查询。

对于sql的优化：

1.通常如果查询中带有or的join时，可以考虑将其变成两个确切的sql进行UNION ALL，此时执行的效率比采用or要高得多。

2. 对于sql中采用年、月等截取sql时，可以考虑在程序中进行拼接，而不是在sql中采用sql函数获取，从而是一个确切的区间，从而提高效率。

3. 对于多个 join 可以考虑将其分解成多个sql子句，在程序中拼接，此时的效率比join的效率要高。

4. 采用sql的执行计划，对查询慢的sql子句进行优化。

5. 考虑采用延迟关联。