mysql优化基础--查询优化
一 Explain
- 使用expllatin查看SQL执行计划
- 常见字段及含义
- select_type
- 表示SELECT的类型，常见的取值有：
- SIMPLE：简单表，不使用表连接或子查询
- PRIMARY：著查询，即外层查询
- UNION：UNION中的第二个或者后面的查询语句
- SUBQUERY：子查询中的第一个
- type
- 表示MYSQL在表中找到所在行的方式，或者叫访问类型。常见的访问类型有：
- system，const：表示表中只有一行记录(等于系统表)，这是const类型的特例
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
- ref：非唯一性索引扫描，扫描匹配某个单独值的所有行，本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
- range：只检索给定范围的行，使用一个索引来选择行，key列显示了使用了哪个索引。这种范围扫描索引比全表扫描好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引
- index：Full index scan，index与all的区别为index类型只遍历索引树。者通常比ALL快，因为索引文件通常比数据文件小
- all：全表扫描，性能最差
- key
- 使用到的索引名。如果没有选择索引，值为NULL。可以采用轻质索引方式
- key_len
- rows
- extra
- using index：表示相应select操作中使用了覆盖索引，避免访问了表的数据行，效率不错。如果同时出现using where，表示索引被用来执行索引键值的查找。如果没有同时出现using where，表示索引用来读取数据而非执行查找动作
- using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MYSQL中无法利用索引完成的排序操作称为文件排序
- using temporary：使用了临时表保存中间结果，mysql在对查询结果进行排序时使用临时表。常见于排序order by和分组查询group by
二 SQL语句中IN包含的值不应过多
- mysql对in做了优化，即将in中的常量全部存储在一个数组里面，而这个数组时排好序的。但是如果数值较多，产生的消耗也比较大
- 对于连续的数值，能用between就不要使用in
- 再或者使用连接来替换
三 SELECT务必指明字段名称
四 当只需要一条数据时，使用 limit 1
五 如果排序字段没有用到索引，就尽量少排序
六 如果限制条件中其他字段没有索引，尽量少用or
七 尽量使用union all代替union
- union和union all的差异主要是前者需要将结果集合并之后在进行唯一性过滤，这就会涉及到排序，增加了CPU运算，增加资源消耗和延迟。使用union all的前提是两个结果集没有重复数据
八 不使用 order by rand()
九 区分in和exists，not in和not exists
- 区分in和exist主要时造成了驱动顺序的改变(这是性能变化的关键)
- 如果是exists，那么外层表为驱动表，先被访问
- 如果是in，那么内层表为驱动表，先执行子查询
- in适合外表大而内表小的情况
- exist适合外表小而内表大的情况
- 小表驱动大表
- 关于not in和not exist，推荐使用not exists，不仅仅是效率的问题，not in可能存在逻辑问题
十 使用合理的分页方式以提高分页效率
十一 分段查询
十二 避免在where子句中对字段进行null判断
- 对于null的判断会导致引擎放弃使用索引而进行全表扫描
十三 不建议使用%前缀模糊查询
十四 避免在where子句中对字段进行计算
十五 避免隐式类型转换
- where子句中出现column字段的类型和传入的参数类型不一致的时候会发生类型转换
十六 对于联合索引，遵守最左前缀原则
十七 必要时可以使用force index来强制查询走某个索引
十八 注意范围查询语句
- 对于联合索引来说，如果存在范围查询，会造成后面的索引字段失效
十九 关于join优化
- mysql选择驱动表规则
- inner join时mysql会自动找到数据量少的表作为驱动表
- 借助union all来实现full join
- 被驱动表的索引字段作为on的限制字段
- 小表驱动大表可以减少IO总量以及CPU运算次数
