MySQL系列文章
分析SQL
搜集sql
- 先搜集慢sql集合，可以通过之前文章搭建的工具扫一遍：搭建慢SQL搜集分析工具
- 按平均耗时倒序排序，取前10个进行优化
查看执行计划
- 通过explain查看sql执行计划
关键字 |
解释 |
table |
表名 |
type |
连接的数据类型，由好到差const、eq_ref、ref、range、index、all |
const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次 |
|
eq_ref：对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。 |
|
ref：对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。 |
|
range：只检索给定范围的行,使用一个索引来选择行。 |
|
index：该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小 |
|
all：对于每个来自于先前的表的行组合，进行完整的表扫描。 |
|
rows |
估算扫描行数 |
extra |
using index condition：搜索条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。 |
key |
适用的索引 |
- 主要注意几个地方
- key：执行器选择的索引
- rows：估算的扫描数据行数
缺少索引
看完执行计划之后判断是否缺少索引就很明朗了，直接看explain语句返回结果的key，如果为空则说明没有走索引。
创建索引要本着以结合where条件区分度最高的原则创建，考虑以下几个方面
联合索引最左匹配原则
- 最左前缀匹配：mysql会一直向右匹配直到遇到范围查询
- 联合索引结合索引结构推断，索引会在范围查询断掉
- 最左优先，在检索数据时从联合索引的最左边开始匹配
- 遇到范围查询(>、<、between、like)比较就会停止
- 举例：
- 创建一个 index_magor_class(magor,class) 的联合索引，那么它的索引树就是下图的样子
- 我们查询的where 条件如果只传入了班级，是走不到联合索引的，但是如果只传了学院编号，是可能会走到联合索引的。（为什么说可能，MYSQL的执行计划和查询的实际执行过程并不完全吻合，比如你数据库数据量很少，可能直接全量遍历速度更快，就不走索引了）
索引覆盖
- 结合业务场景适当考虑索引覆盖
- 普通的索引查询步骤为，现根据索引定位到主键，再根据主键去查找数据行(回表操作)
- 指查询列在索引列中，不需要回表操作
索引失效
- 时间类型data_format会导致时间字段索引失效
- 可以用date(ctime) < str_to_date('2019-12-30', '%Y-%m-%d')
- 对索引字段使用计算操作或函数失效，类似&运算等
- 使用like %key%使索引失效，大数据量全文检索尽量使用es
- or关键字前后没有同时使用索引
- 联合索引最左匹配原则顺序不符合规则
- 索引字段使用is null 或 is not null
- join语句字段编码不一致导致引擎走错索引
- case when不走索引
业务分析
深度分页
- 尽量在产品层上规避掉此类需求，比如操作日志，除了出大问题谁会看自己几月前甚至几年前的操作。
- 规避不了的话就要考虑深度分页方案
- limit n, m; n代表偏移量通常在业务系统需要page_no * page_size如果偏移量很大如何优化
- 参考es的深度分页策略，滚动分页。利用id连续返回上次查询的最大记录(偏移量)，基于上一次查询偏移量作为where条件
- 业务中限制页数
- 先通过索引列拿到主键，通过主键回表查详情
select a.*from emp a,(select id from emp limit1000000,10) b where a.id= b.id
是否需要select *
- 查询字段会占用网络带宽，也会拖慢sql执行，所以尽量避免select *情况，按需查询
