MySQL：索引优化、查询优化

一、哪些情况适合创建索引

1、字段的数值有唯一性的限制；

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引；

说明：创建唯一索引会影响添加的速度（在添加的时候会维护索引），但是这个速度影响可以忽略，但是可以显著的提高查询的速度；

2、频繁作为WHERE查询条件的字段；

3、经常使用GROUP BY 和ORDER BY的列；

说明：在创建索引的时候，B+树中的所有是按照一定顺序排列的，所有在分组和排序的时候会提高查询速度；

补充：如果既有GROUP BY又有ORDER BY，建议添加联合索引，联合索引中GROUP BY中的索引在前，ORDER BY的索引在后，因为在查询的时候先查询GROUP BY中的再查询ORDER BY中的；

4、 UPDATE、DELETE的WHERE条件列；

5、DISTINCT字段需要创建索引；

6、多表JOIN连续操作的时候，创建索引注意事项：

首先：连接表的数量不要超过3张，因为每多一个表，就相当于多了一次嵌套循环，是一个数量级的增长，影响查询效率；

其次，对WHERE条件创建索引，因为WHERE才是对数据条件的过滤。如果再数据量非常大的情况下，没有WHERE影响是非常大的；

最后，对于连接的字段创建索引，并且该字段再多张表中的类型必须一致，因为如果类型不一致，可能出现数据类型的隐式转换，这个过程会用到函数，当用到函数的时候，创建的索引就失效了；

7、使用列的类型小的创建索引（能用小的就用小的）

类型小指的是该类型表示的数据范围大小；

数据类型小，再查询的时候操作比较快；

数据类型小，索引所占用的空间也就越少，在一个数据页中也就可以放下更多的记录，B+Tree就会更加矮胖，从而减少磁盘I/O的次数，提高效率；

说明：这个对于主键来说更加适用，因为不仅在聚簇索引中会存储主键，在二级索引的节点处也会存储主键，如果主键使用更小的数据类型，就意味着节省更多的存储空间，就可以在一个数据页中放更多的内容，减少IO次数；

8、使用字符串前缀创建索引；

1、过长的字符串会占用很大的空间，如果用过长的字符串创建索引，就会导致索引中占用的存储空间大；

2、B+树索引中的所有列存储的字符串很长，在做字符串比较的时候就会占用更多的时间；

9、区分度高（散列性高）的列适合作为索引，也就是重复项少；

10、使用最频繁的列放在联合索引的最左侧（遵循最左侧前缀原则）

11、在多个字段都要创建索引的时候，联合索引优于单值索引；

限制索引的数目，建议单张表索引数量不超过6个；

1、每个索引都会占用磁盘空间，索引越多需要的磁盘空间也就越大；

2、索引会影响INSERT DELETE UPDATE等语句的性能，因为表中数据在更改的时候，索引也需要进行维护；

3、优化器在选择如何优化查询的时候，会根据统一信息，对每一个可以用到的所有来进行评估，生成一个好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划的时间，降低查询性能；

二、哪些情况不适合创建索引

1、在WHERE中使用不到的字段，不要设置索引；

2、数据量小的表最好不要使用索引；

3、有大量重复数据的列上不要建立索引；

4、避免对经常更新的列创建过多的索引；

5、不建议用无序的值作为索引；（出现页分裂次数频繁）

6、不再使用或者很少使用的索引；

7、不要定义冗余或者重复的索引；

三、索引优化、查询优化

SQL优化大致上可以分为：物理查询优化和逻辑查询优化；

物理查询优化是通过：索引、表连接方式进行优化；（重点掌握）

逻辑查询优化是通过：SQL等价变换提升查询效率（也就是换一种查询写法执行效率可能更高）；

3.1 索引失效

1、最佳左前缀法则

索引文件具有B-Tree的最左前缀匹配特性，如果最左边的值没有确定，就无法使用索引；

2、计算、函数、类型转换（自动或者手动）导致索引失效；

3、范围条件右边的索引失效

ALTER TABLE student ADD INDEX idex_age_classId_name(age,classId,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.`classId` > 20 AND student.`name` = 'abc';
-- classId是一个范围条件，创建索引的时候再name在他的右边，所以namae字段索引失效；
-- 和WHERE中的顺序没有关系
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.`name` = 'abc' AND student.`classId` > 20;

 创建联合索引的时候，务必把范围查找的字段设置在最后；

4、不等于（！= 或者<>）索引失效；

5、is null （相当于等于）可以使用索引，is not null不可以使用索引（相当于不等于）

结论：在创建数据表的时候将字段设置为 NOT NULL约束，如果一定要有NULL的需求，可以把默认值设置为0，字符串类型的默认值设置为空字符串’‘。

拓展：在查询中使用 not like也无法使用，会导致全表扫描；

6、like以通配符%开头索引失效（因为B+Tree中不知道你前面是啥，找不到）

强制：页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决；

7、OR前后存在非索引的列，索引失效 （取并集，没有索引的哪一个会进行全表查询）

8、数据库和表的字符不统一；

不同的字符集进行比较的时候需要进行转换会造成索引失效 

3.2 关联查询优化

1、采用左外连接

        左外连接中左边的表全要，所以type是All不需要添加索引，所以需要在被驱动表中添加索引；（右外连接类似）

2、采用内连接、

        两表都没有索引，或者两表都有索引的时候，小表驱动大表（大结果集的作为被驱动表）；

        两表中只有一个有索引的时候，有索引的表被作为被驱动表；

 JOIN语句原理

小结：

1、索引嵌套循环连接（Index Nested-Loop Join）(添加索引 ) > Block Nested-Loop Join （运用了Join Buffer）> 简单嵌套循环

2、永远用小结果集驱动大结果集（本质就是减少外层循环的数据数量）；

3、为被驱动匹配的条件增加索引（减少内层表的循环匹配次数）；

4、增大join buffer size的大小（一次缓存的数据多，内层表的扫表次数就减少）；

5、减少不必要的字段查询（字段越少，join buffer缓存的数据也就越多）；

MySQL 8.0 的时候用Hash Join取代了Block Nested-Loop Join；

3.3 子查询优化

子查询执行效率不高的原因：

1、在执行子查询的时候，MySQL需要给内层查询语句的查询结果创建一个临时表，然后外层擦汗寻语句，从临时表中查询数据，查询完毕之后再撤销临时表。创建和销毁的过程会消耗过多的CPU和IO资源，产生大量的慢查询；

2、临时表是没有索引的，对于查询性能有一定的影响，特别是数据量很大的时候影响更大；

解决方案：使用连接（JOIN）查询来替代子查询，连接查询不需要建立临时表，速度比子查询要快，在查询中使用索引的话，性能会更好；

3.4 排序优化

 问题：在WHERE条件字段上加索引，但是为什么在ORDER BY字段上还要加索引

回答：

在MySQL，支持两种排序方式，分别是FileSort，和Index排序

1、在Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高；

2、FileSort排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低；

优化建议：

1、SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY 子句中避免FileSort排序，当然某些情况下全表扫描，或者FileSort排序不一定比索引慢，但总的来说，我们还是要避免，提高查询效率；

2、尽量使用Index完成ORDER BY排序，说如果WHERE和ORDER BY后面是相同的列就使用单索引列，如果不同就使用联合索引；

3、无法使用Index时，需要对FileSort方式进行调优；

3.5 覆盖索引

        一个索引包含了满足查询结果的数据叫做覆盖索引；

好处：

1、避免Innodb表进行索引的而此查询（回表）

2、可以把随机IO变成顺序IO（因为回表的时候是随机IO的，在索引中排好序了，但是在回表的时候，两个数据可能在不同的数据页 ）加快查询效率

四、索引下推

4.1 定义

        索引下推一般运用在联合索引中

        索引下推运用在非聚簇索引中（二级索引），因为索引下推主要就是在回表之前先进行一下判断、过滤，减少回表的次数然后减少随机IO；

         目前存在一个联合索引 zipcode_lastname_pero 

        虽然lastname中有模糊查询并且是%开头的，索引会失效，但是，如果通过成本计算之后，优化器发现，通过索引下推也就是用到了索引lastname之后过滤掉的条件可以减少回表的次数；减少随机IO；

4.2 好处

        ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数；

补充：

一、SELECT COUNT(*) 、SELECT COUNT(1) 、SELECT COUNT(具体字段)

1、如果是对所有结果进行COUNT(*)、COUNT(1)是相等的；

2、如果是对行数进行统计：

        2.1 如果是MyISAM存储引擎，统计数据表的行数只需要O(1)的复杂度，因为在MyISAM中有一个信息记录了行数值；

        2.2 如果是InnoDB存储引擎，没有记录行数，所有需要进行全表扫描是O(n)复杂度；

3、在InnoDB中，如果采用COUNT(字段)来统计行数，尽量采用二级索引，因为聚簇索引中包含的信息多，大于二级索引，对于COUNT(*)、COUNT(1)来说不需要去找具体字段，只需要统计行数，所以回去自动选择一个占用空间小的二级索引来进行统计（根据ken_len来进行选择）；

二、不建议使用SELECT *的原因

        1、MySQL在解析的时候，会通过查询数据字典，将”*“转换为所有列名，会消耗资源和时间；

        2、无法使用覆盖索引；

三、LIMIT 1对优化的影响

        如果是一个进行全表扫描的SQL语句，如果确定结果集只有一个就可以加上LIMIT 1来加快查询速度；

        如果已经对字段建立了唯一索引（不会重复，找到了之后就不会继续往下找了），这个时候就不需要加上LIMIT；

五、范式

        第一范式：确保每列保持原子性；、

        数据库的每一列都是不可分割的原子数据项，不可再 分的最小数据单元，而不能是集合、数组、记录等非原子数据项；

        第二范式 ： 确保每一列都和主键完全依赖，也就是要和主键有关；

        第三范式：确保每列都和主键直接相关，非主键之间不能有关系；

        范式的优点：减少数据冗余；

        范式的缺点：降低查询的效率；（表变多了）；