SQL性能分析&优化（explain+select）
索引：
-
分类
- 唯一索引（主键索引）
- 单列索引
- 复合索引（一般而言，复合索引要优于单列索引）
-
优缺
- 大大提高数据查询的效率
- 维护额外的索引文件，必须保证顺序。即插入/更新操作不仅操作真实数据还需要额外维护索引表，降低了insert/update效率
- 使用索引之前最好是读写分离，主库不要添加索引，从库根据业务实际需求创建最优索引
explain-sql.png
-
explain select ...分析结果输出-
id,表明执行顺序，先大后小，相同id自上往下； -
select_type,类型-
simple,表示简单的select查询,查询语句不包含子查询或union -
primary,表示查询中包含复杂子部分，最外层查询标记为primary -
subquery,表示在select或where列表包含子查询 -
derived,表示在from列表包含子查询被标记为衍生derived，mysql会递归这些子查询，把结果放到临时表里 -
union,result从union表获取结果的select
-
-
type,可以理解为查询方式-
system，单表单行查找 -
const，常量查找 -
eq_ref，索引查询，单结果 -
ref，索引查询，多结果 -
range，范围查询 -
index，索引查询 -
all，全表扫描 - 这个字段作为性能指标最重要几个字段之一，越往后的类型性能越差
-
-
possible_keys, 显示可能使用到的索引 -
key,显示查询时真正使用到的索引，根据字段可以直观看到作引是否失效 -
key_len,表示索引中使用的字节数，可通过该列计算查询中使用索引的长度，不损失精度下越短越好，key_len显示的值为索引字段的最大可能长度，不是实际使用长度，查询条件越多越精确，但产生key长度越长 -
ref,显示那一列索引被使用 -
rows,根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数（读取行判断有多少行被优化） -
filtered,新版才有的字段 -
extra,本次查询额外描述-
using filesort,排序没有使用到索引，产生了文件内排序。必须优化 -
using temporary,使用临时表保存中间结果，mysql对查询结果排序使用临时表，常见于排序order by和分组查询gourp by，多索引情况，排序规则最好按照索引个数和顺序使用 -
using index,使用索引查找，这里注意需要按照最左原则覆盖索引，否则索引将失效。 -
using where,使用了where过滤 -
using join buffer,使用了连接缓存,可以通过配置文件根据实际需求相应调整缓存大小 -
impossible where,语句逻辑有问题
-
-
索引失效情况（应该尽量避免的）
- 最好是全值匹配查找，即所查询的字段最好是跟创建的复合索引一致
- 复合索引，遵循最左原则。即所查询字段要跟创建复合索引的最左字段匹配上，不可跳过最左的索引字段，否则索引失效
- 索引字段类型进行急计算/函数/类型转换等，索引失效
// mobile varchart 类型
explain SELECT id,mobile from users WHERE mobile =17620439807; // 传入的是number，产生了类型转换，但是mysql57及之后版本还是会使用到索引查询，type：index
explain SELECT id,mobile from users WHERE mobile ='17620439807'; // 传入正确数据类型，此时查type:const,可见虽然做了优化但还不是最优，这个坑还是需要注意的（计算/函数/类型转换等）
- 范围查找，范围条件之后的索引会失效
- 尽量减少
select *,查询最好覆盖索引，即所查字段包含与表索引中，超出则索引失效 - mysql早期版本在使用
!= / <> / is null / is not null时索引失效，全表扫描。mysql57版本之后已做优化，索引并不会失效 - 使用
like模糊查询，%str%索引将会失效，进行全表扫描；str%,这种情况索引依然生效但是基本无法满足业务需求。所以模糊查询是最好是覆盖索引
// user 表只有主键索引 & mobile索引
explain SELECT id,mobile from users WHERE mobile LIKE "%9807%" // 索引生效，因为索引覆盖了查询字段
explain SELECT id,mobile,nick_name from users WHERE mobile LIKE "%9807%" // 索引失效，因为索引未覆盖查询字段，nick_name超出索引范围
- "少用or,用它来连接时索引将会失效"。mysql版本优化之后此类问题慢慢减少
explain SELECT id,mobile from users WHERE mobile ='17620439807'; // type: const 极好
explain SELECT id,mobile from users WHERE mobile ='17620439807' or mobile ='17620439808' or mobile ='17620439809'; // type: range 可以接受
最后
- 先完成再完美，先功能再性能。
- 后期db性能优化，作为开发者能做的就是尽量优化自己写的sql了，多使用explain分析sql，根据实际场景来优化。
- 性能优化思路：
- 测试环境压测
- 慢日志查询，分析得到慢sql
-
explain分析sql语句，创建最优索引，避免索引失效等问题（条件允许可以读写分离，读库创建索引）。 -
show profile更近一步分析 - 以上全部完成如果还不能承载业务量，才因该考虑横向扩展
