Mysql的几个灵魂拷问（四）
Mysql前面已经把基础和原理部分铺垫的差不多了，现在要来讲讲的是Sql优化和调优部分了，这个基本是Mysql拷问系列最直接灵魂的环节了，优化这几乎是每场面试对话的最高潮部分，但这里又是一个开放的话题，能不能绽放出亮点，就看平时在调优方面下的功夫够不够了，数据库优化维度有四个：硬件、系统配置、数据库表结构、SQL及索引。这一篇的重点是放在SQL语句的优化上，表设计与大表优化方案则放在下一个篇文章中。
一、总优化思路
对于程序开发者来说，MySQL层优化一般可以遵从五个原则：
- 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘 IO。
- 返回更少的数据：只返回需要的字段和数据分页处理，减少磁盘 IO 及网络 IO。
- 减少交互次数：批量 DML 操作，函数存储等减少数据连接次数。
- 减少服务器 CPU 开销：尽量减少数据库排序操作以及全表查询，减少 CPU 内存占用
- 利用更多资源：使用表分区，可以增加并行操作，更大限度利用 CPU 资源。
总结到 SQL 优化中，就如下三点：
- 最大化利用索引。
- 尽可能避免全表扫描。
- 减少无效数据的查询。
二、索引优化策略
众所周知，索引类似于字典的目录，可以提高查询的效率。索引从物理上可以分为：聚集索引，非聚集索引。从逻辑上可以分为：普通索引，唯一索引，主键索引，联合索引，全文索引。这一部分主要总结Sql优化的一些技巧和规范，尤其对于一些还有歧义和误导性的说法做一个纠正。
1、合理建立覆盖索引
合理的覆盖索引，可以减少回表次数。表数据是放在一个聚集索引上的，而建立的索引为非聚集索引，非聚集索引的叶子节点存放索引键值，以及该索引键指向的主键。一般查找的过程是从非聚集索引上找到数据的主键，然后根据该主键到聚集索引上查找记录，这个过程称为回表。
如有下面这个sql
select uid, login_time from user where username = ? and passwd = ?
可以建立(username, passwd, login_time)的联合索引，由于 login_time的值可以直接从索引中拿到，不用再回表查询，提高了查询效率。
建立联合索引的时候注意将区分度最高的字段放在最左边！
2、union，or，in都能命中索引，建议使用in
新版Mysql中union，or，in都是会走索引的，所以如下三条sql语句都是等价的：
select * from article where id = 1
union all
select * from article where id = 2
select * from article where id in (1 , 2)
select * from article where id = 1 or id = 2
效率从高到低为union，in，or。in和union的效率差别可以忽略不计，所以直接建议使用in。union 比 union all 多一个去重逻辑，业务上知道已经有字段有唯一属性的话，直接用union all 会比 union 效率更高点儿。
exist和in的差别，并不是exist替换都比in要好：
- 子查询使用 exists，会先进行主查询，将查询到的每行数据循环带入子查询校验是否存在，过滤出整体的返回数据；子查询使用 in，会先进行子查询获取结果集，然后主查询匹配子查询的结果集，返回数据
- 外表内表相对大小情况不一样时，查询效率不一样：两表大小相当，in 和 exists 差别不大；内表大，用 exists 效率较高；内表小，用 in 效率较高。
- 不管外表与内表的大小，not exists 的效率一般要高于 not in，跟子查询的索引访问类型有关。
负向条件索引不会使用索引，建议用in
负向条件有：!=、<>、not in、not exists、not like 等
-- 全表扫描
select * from article where id != 1 and id != 2
知道id的所有取值范围，可以改为类似如下形式
-- 走索引
select * from article where id in (0, 3, 4)
在索引列上进行运算或使用函数会走全表
在列上进行运算或使用函数会使索引失效，从而进行全表扫描。如下面例子在publish_time，id列上分别加上索引，publish_time为datetime类型，id为int类型:
-- 全表扫描
select * from article where year(publish_time) < 2019
-- 走索引
select * from article where publish_time < '2019-01-01'
-- 全表扫描
select * from article where id + 1 = 5
-- 走索引
select * from article where id = 4
小心隐式类型转换
假设id为varchar类型，用int类型查会走全表。
-- 全表扫描
select * from article where id = 100
-- 走索引
select * from article where id = '100'
隐式类型转换在索引字段上做了函数操作，因此会全表扫描。
select * from article where id = 100
-- 等价于
select * from article where CAST(id AS signed int) = 100
那么如果id是int，执行下面这个语句是否会导致全表扫描呢？select * from article where id = '100'，答案是会用到索引。
不建议使用%前缀模糊查询
%李,%李%都会导致全表扫描，非前导模糊查询可以使用索引
避免在where子句中进行null值判断
对于null的判断会导致引擎放弃使用索引而进行全表扫描。
经常更改，区分度不高的列上不宜加索引
- 更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。
- 一般区分度在80%以上的时候就可以建立索引。
区分度可以使用 count(distinct(列名))/count(*) 来计算
“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。
多表关联查询时，小表在前，大表在后
在 MySQL 中，执行 from 后的表关联查询是从左往右执行的（Oracle 相反），第一张表会涉及到全表扫描。
调整 Where 字句中的连接顺序
MySQL 采用从左往右，自上而下的顺序解析 where 子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。
查询是否存在可以加limit1
查询是否存在或者当查询确定只有一条记录时，可以加liimit1，让MySQL停止游标移动，提高查询效率
select uid from user where username = ? and passwd = ?
可改为:
select uid from user where username = ? and passwd = ? limit 1
Explain调优
说了这么多的优化总结，最重要的可能还是通过分析手段去，使用explain关键字可以模拟优化器执行SQL语句，号称SQL中的debug。通过explain可以知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。
explain + SQL
explain包含很多的Item，主要关注type，possible_key，key，row和Extra。
id：执行权重，查询多张表后对应的id越大优先级越高
type：查询使用的类型，system > const > eq_ref > ref > range > index > all
system 只有一行数据，一般不出现
const 通过索引一次就找到
eq_ref 唯一性索引扫描
ref 非唯一性索引扫描
range 检索制定范围
index 只遍历索引树
all 全表扫描
table：表名
possible_key：表中存在的索引
key：本次查询使用的索引
row：找到目标大致所需的行数
Extra：
using filesort 使用外部的索引排序
using temporary 使用临时表保存数据
using index 使用了覆盖索引
using where 使用where条件
using join buffer 使用连接缓存
impossible where where条件无结果
select tables optimized away 最佳优化状态，无需遍历索引
优化思路还是沿着我们上面总结的那些来进行即可。
- 使用 explain 分析语句时若发现 rows 非常大的查询语句可以考虑在对应的关键词上加入索引
- 用联合索引来减少回标次数
- 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不会使用索引
- 应尽量避免在 where 子句中使用
!=或<>not in、not exisit等负向条件的操作符，改成in之类。 - 避免在where后面用函数或者计算，走全表了
- 避免在where后面做null值判断
- 在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，避免前缀也是模糊。
参考引用
1、这次被问懵了！搞定了这些SQL优化技巧，下次横着走
2、MySQL索引优化实战
3、巧用 explain 优化 MySQL 语句
