SQL优化（二）

五、索引使用策略及优化

1、最左前缀原理与相关优化

（1）全列匹配

        当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。需要注意，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句
的条件顺序以使用适合的索引，即使我们将where中的条件顺序颠倒效果也是一样的；

（2）最左前缀匹配

        如果有多个索引做为条件的去查询的时候，最左边的的索引如果没有做为条件去查询，则后续的查询索引都会失效；

例如：stu_no、stu_name、stu_age三个都是索引；

EXPLAIN SELECT * FROM student WHERE stu_name='张三' and stu_age='28';

查询不会走索引查询；

（3）复合索引中间某个条件未提供

        如果stu_name未提供，所以查询只用到了索引的第一列，而后面的stu_age虽然也在索引中，但是由于stu_name不存在而无法和左前缀连接，因此需要对结果进行扫描过滤stu_age（这里由于stu_no唯一，所以不存在扫描）。如果想让stu_age也使用索引而不是where
过滤，可以增加一个辅助索引<stu_no, stu_age>，此时上面的查询会使用这个索引；

 （4）匹配某列的前缀字符串

EXPLAIN SELECT * FROM student WHERE stu_no='10001' and stu_name='张%';

此时可以用到索引，但是如果通配符不是只出现在末尾，则无法使用索引；

（5）范围查询

EXPLAIN SELECT * FROM student WHERE stu_no<'10010' and stu_name='张%';

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

（可以使用Between  and）改成 ：

EXPLAIN SELECT * FROM student WHERE emp_no
BETWEEN '10001' AND '10010' and stu_name='张%';

（6）查询条件中含有函数或表达式

WHERE stu_no='10001' AND left(stu_name,1)='张';
改成 ↓
WHERE stu_no='10001' AND stu_name like '张%'

WHERE stu_no- 1='10000';
改成 ↓
WHERE stu_no='10001';

2、索引选择性与前缀索引

        不建议加索引情况：
                ① 表记录比较少；
                ② 索引的选择性较低；
                所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表         记录数（#T）的比值：Index Selectivity = Cardinality / #T  ；

        超过2000条还经常被查询，可以酌情考虑索引；

3、InnoDB的主键选择与插入优化

        在使用InnoDB存储引擎时，如果没有特别的需要，使用一个与业务无关的自增字段作为主键。不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大；
        为什么不用UUID作为主键，要用自增的id；
        使用UUID作为id，容易造成空间浪费，可能还没放满就开辟一个新的空间；

六、Join优化

理解JOIN原理

（1）Simple Nested-Loop Join

        简单嵌套循环连接实际上就是简单粗暴的嵌套循环，如果table1有1万条数据，table2有1万条数据，那 么数据比较的次数=1万 * 1万 =1亿次，这种查询效率会非常慢；

（2）Index Nested-Loop Join

        索引嵌套循环连接是基于索引进行连接的算法，索引是基于内层表的，通过外层表匹配条件直接与 内层表索引进行匹配，避免和内层表的每条记录进行比较，从而利用索引的查询减少了对内层表 的匹配次数，优势极大的提升了 join的性能. ​ 使用场景：只有内层表join的列有索引时，才能用到Index Nested-LoopJoin进行连接。 ​ 由于用到索引，如果索引是辅助索引而且返回的数据还包括内层表的其他数据，则会回内层表查询 数据，多了一些IO操作；

（3）Block Nested-Loop Join

        这种算法是将外层循环的行/结果集存入join buffer，内存循环的每一行数据与整个buffer中的记录做比较，可以减少内层循环的扫描次数。 ​ 举个简单的例子：外层循环结果集有1000行数据，使用NLJ算法需要扫描内层表1000次，但如果使用BNL算法，则先取出外层表结果集的100行存放到join buffer, 然后用内层表的每一行数据去和这100行结果集做比较，可以一次性与100行数据进行比较，这样内层表其实只需要循环1000/100=10次，减少了9/10；

七、其他

优化ORDER BY

sort buffer 存储有限，

排序算法：

        1）双路排序会读取两次表， 且第二次是随机读取的；

        2）单路排序的元组比双路排序要长，导致它需要多次向临时文件写入内容，增加IO操作，当需要返回的列的总长度很长时尤其明显；

        单路排序效率更高，不用像双路那样根据排序字段排序后，再去查询相关的数据；

        MySQL根据max_length_for_sort_data变量来确定使用哪种算法，默认值是1024字节，如果需要返回的列的总长度大于max_length_for_sort_data，使用第一种算法单路排序，否则使用第二种算法双路排序；