MySQL学习之提高SQL查询的效率

建索引时

        1. 首先应考虑在常常用于条件判断的字段上创建索引

        2. 尽量选择区分度高的列建立索引
            如果为区分度低的列建立索引，会导致索引树很高
            一般在2 ~ 4层
            区分度公式：count(distinct col) / count(*)
            数值越大表示查询的记录越少，表示字段不重复的比例

        3. 当需要组合搜索时, 使用组合索引代替多个单列索引

        4. 在创建联合索引时，根据业务需求，将where子句中使用最频繁的一列放在最左边, 像这种范围的放到后面去

查询时

查询时要正确命中索引

        1. select子句中尽量避免使用*

        2. count(1) / count(主键/列) 代替 count(*)

        3. 避免对字段使用例如：UCASE() / LCASE() / MID() 等内置函数

        4. where子句比较符号左侧避免表达式、函数
            尽量避免在where条件子句中，比较符号的左侧出现表达式、函数等操作
            例如：
                where 成绩 + 5 > 90  （表达式在比较符号的左侧）

                优化方法：

                where 成绩 > 90 – 5（表达式在比较符号的右侧）

        5. 索引字段不要参与计算

        6. 索引字段不要使用函数

        7. 索引类型要一致
         例：where id = 3  而不是 where id = '3'

        8. 尽量避免在 where 子句中对字段进行 null 值判断

        9. 对于范围查询：
            查询的范围较大时，速度肯定很慢
            查询的范围小，速度依然很快
            特别的：
                对于 != 或者 <> 来说，如果是主键，还是会走索引
                对于 > 来说，如果是主键，还是会走索引，而且类型为整型，也会走索引

        10. 在MySQL中，模糊查询避免开头就使用“%”
            例如： LIKE  '%C%';

        11. 尽量避免使用in和not in
            in里面的结果集如果是通过一个子查询出来的是不走索引的
            in 可以用 between and
            not in 可以用 not exists

        12. 尽量避免使用or，使用union all代替
           （如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描）
            例如：elect 学号 from 成绩表 where 成绩 = 88 or 成绩 = 89
            优化后：
                select 学号 from 成绩表 where 成绩 = 88

                union

                select 学号 from 成绩表 where 成绩 = 89

        13. 对于联合索引，要遵循最左前缀匹配原则

            联合索引的最左前缀：

            A、B、C3个字段--联合索引

            这个时候，可以使用的查询条件有：A、A+B、A+C、A+B+C，唯独不能使用B+C，即最左侧那个字段必须匹配到

            解释一下最左前缀原则：
            当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，
            比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，
            如果name相同再依次比较age和sex，最后得到检索的数据；

            但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，
            因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。
            比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，
            所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，
            这个是非常重要的性质，即索引的最左匹配特性。

            范围查询：
                范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。
                同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

                mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
                比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，
                d是用不到索引的，
                如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

        14. 排序条件为索引，select的时候也是索引字段
            特别的：
                对于主键来说，还是会走索引

        15. 使用limit子句限制返回的数据行数