高频面试MySQL之索引、查询优化

1、简介

索引和查询优化是面试比不可少的环节，下面针对B+Tree做一些常见的面试点的分析，从建表、索引、查询三个方面分析。

2、数据库和表设计

1. 从大的设计原则上，可以考虑分库分表，读写分离，使单表数据不会太大，同时流量分散到不同数据库实例上，减轻数据库压力
2. 字段数据类型选择：1）选择占用空间较小的数据类型，使用定长代替变长，如使用unsigned tinyint、smallint代替int；2）对于布尔、枚举类型的字段，使用char(1) 来代替；3）分离text、blob等类型的字段；4）避免单表字段过多，使用主从表拆分；5）如果无特殊要求，尽量避免列为 null；6）避免使用flot、double，使用decimal，防止数据精度丢失

3、索引

索引优化，包含索引的建立、使用、维护三个方面：

1. 正确建立索引：
   1. 考察字段的可选择性，选择合适的列。例如：性别，选择性低，不适合索引；用户名，选择性高，适合索引
   2. 联合索引区分度最高的在最左边，如username一般是唯一的，要放在索引的最左侧
   3. 前缀索引，1）：较长的字段，使用用前缀索引；2）：text、blob，MySql不支持完整索引。3）原则：保证完整列的选择性的前提下，索引尽量的短。select count(distinct left(列, 长度)) / count(*) ，截取前缀长度来比较并选择一个较优结果即可
   4. 如业务需要字段唯一性，使用唯一索引
2. 正确使用索引：
   1. like 语句的前导模糊查询导致索引失效。可以考虑使用联合索引、或覆盖索引来使索引生效
   2. 负向条件查询导致索引失效，如 !=、<>、not in、not exists、not like
   3. where 条件左侧使用了函数、运算表达式。当使用函数时最好explain执行计划，确定是否生效，一般情况下如果函数对列值有修改，如DATE_FORMAT，会导致索引失效
   4. where 查询条件隐式转换导致索引失效，例如索引列的数据类型是 varchar，查询传递的值为 int
   5. where 条件中使用了 or 导致索引失效，使用覆盖索引解决
   6. join 条件导致索引失效，一般是 on 表达式两侧的列数据类型不一致导致。可以考虑修改表列属性，改为一致的数据类型，也可以考虑使用类型转换函数将两侧数据转换为一致。
   7. order by 请注意利用索引的有序性。order by 最后的字段是组合 索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。 正例:where a=? and b=? order by c; 索引:a_b_c 反例:索引中有范围查找，那么索引有序性无法利用，如:WHERE a>10 ORDER BY b; 索引 a_b 无法排序。
3. 维护：
   1. 定期清理重复索引和冗余索引。重复索引：相同列上建立了单独索引、或联合索引。即使相同列，mysql依然要单独维护重复索引；冗余索引：A、B两列联合索引，A列独立索引，A列上的索引为冗余索引。pt-dumplicate-key-checker 找出重复和冗余的索引
   2. 定期清理未使用的索引，可以使用 PT-INDEX-USAGE 来分析查询日志，查看哪些索引从未被使用

4、查询优化

1. 目标：explain：all、index（全称Full Index Scan，索引物理文件全扫描，效率较低）、range（对索引进行范围检索）、ref（表的连接匹配条件，即哪些列、常量被用于查找索引列上的值）、const（常数级别）。至少要达到 range 级别
2. 常见优化：避免select * 、避免无用字段查询、表指定别名等。减少网络、磁盘、sql解析方面的开销
3. 分解查询，对于一些非常复杂、或者非常消耗资源的SQL可以考虑切分
   1. 删除大量数据，以间隔的方式每次删除1万条，通过这种方式，分摊数据库的压力、分摊锁表时长
   2. 对于复杂、嵌套、多表联合查询等操作，可以考虑分解sql，以当前查询结果，作为下次的查询条件，分批次查询
4. count 优化：
   1. count（ * ）替代count(列) 、count（1）。count（*）可以直接返回行数，与列值为null、非null无关，sql语义更为清晰。同时要注意 count（列）不会统计值为null的行
   2. 索引覆盖，count 需要需要扫描大量的行才能获得结果，通常很难优化，使用覆盖索引可以起到一定的优化作用
   3. 使用近似值代替精确值。在业务允许的条件下可以使用explain查询计划中估算的行数
   4. 使用外部计数表、或者缓存存放count结果
      1. 场景一：统计点击数，计数表预存100条数据，每点击一下随机更新一次记录即可；
      2. 场景二：分时间段统计：如按天、按月统计，可以考虑使用 ON DUPLICATE KEY UPDATE（有则更新，无则插入），再结合定时任务，合并历史数据，保证查询速度
   5. 其它：MyISAM引擎会存存储表的总行数，count（*）效率比InnoDb高（没有where查询条件）
5. limit 优化：
   1. limit 1，提前终止扫描
   2. 分页优化，当偏移量增大时会造成大量无数据行扫描，
      • 记录每次取出的最大id，然后where id > 最大id，select * from table_name Where id > 最大id limit 10000, 10;
      • join方式 + 覆盖索引（推荐），select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)
6. join 优化
   1. 小表驱动大表（EXPLAIN的第一行是驱动表）
   2. join on 表达式两侧字段建立索引，使用相同的数据类型（数据类型不同可能会导致索引失效）
   3. 避免多表 join
7. 使用 ISNULL()来判断是否为 NULL 值。NULL 与任何值的直接比较都为 NULL。而不是 true 或者 false。
8. 数据修改的时候要先select，在update、delete避免误修改、误删除

5、线上维护

• 开启慢查询日志，定期优化线上SQL，因为一个项目组的成员开发水平不同，而且随着项目历史数据的积累，导致部分sql变得越来越慢，需要定期去做优化
• 对于一些自己无法优化的sql，请教同事、DBA
• 尽量避免函数、存储过程的使用，如果要使用，一定要通知DBA知晓
• 使用explain命令分析查询，重点看select_type、type、possible_keys、key 这几个字段，确定查询类型是否全表扫描、确定索引是否生效