我们不管在写代码，或者对执行数据库操作的时候，SQL优化是不可缺少的一环。所以这个功能至关重要。下面我们来说说SQL语句优化：
定位慢查询
show status like 'connections' ------------------------当有多少客户端连接数据库
show status like 'slow_queries'----------------------查询有多少慢查询(默认情况，mysql认为查询时间到达10秒才是一个慢查询)
show processlist-------------------------查看哪些sql出现问题
定位到慢查詢后。就分析下SQL
explain分析SQL
Explain select * from emp where ename=“zrlcHd”会产生如下信息：
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引key_len:索引字段的长度rows:扫描出的行数(估算的行数)
Extra:执行情况的描述和说明
分析好后，就需要添加索引
使用索引
1.添加PRIMARY KEY（主键索引）：
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引) ：
ALTER TABLE `table_name` ADD UNIQUE ( `column` )
3.添加INDEX(普通索引) ：
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引) ：
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引：
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
説明：
PRIMARY, INDEX, UNIQUE 这3种是一类
PRIMARY 主键。 就是 唯一 且 不能为空。
INDEX 索引，普通的
UNIQUE 唯一索引。 不允许有重复。
FULLTEXT 是全文索引，用于在一篇文章中，检索文本信息的。
索引优化策略
1：不要在索引列上进行运算或使用函数
在列上进行运算或使用函数会使索引失效，从而进行全表扫描。如下面例子在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'
2:小心隐式类型转换
假设id为varchar类型
-- 全表扫描
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
MySQL 使用操作符的一些特性:
- 当操作符左右两边的数据类型不一致时，会发生隐式转换。
- 当 where 查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
- 当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
- 字符串转换为数值类型时，非数字开头的字符串会转化为
0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。
3：前导模糊查询不会使用索引
---全表扫描
select * from article where author like '%李'
%李,%李%都会导致全表扫描，非前导模糊查询可以使用索引
select * from article where author like '李%'
4：联合索引最左前缀原则
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整
1.将区分度最高的字段放在最左边
当不需要考虑排序和分组时，将区分度最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找
如果在a b列上建立联合索引，该如何建立，才能使查询效率最高
select count(distinct a) / count(*), count(distinct b) / count(*), count(*) from table
执行如下语句，假设3个输出依次为0.0001,0.373,16049，可以看到b列的选择性最高，因此将其作为联合索引的第一列，即建立(b, a)的联合索引
查询时=可以乱序
如果建立了联合索引（a, b）。例如下面的2个写法是等价的，因为MySQL会将查询的顺序优化成和联合索引的顺序一致
select * from table where a = '1' and b = '1'
select * from table where b = '1' and a = '1'
2.优化查询，避免出现filesort
select * from table where a = ? and b = ? order by c
最左前缀原则不仅用在查询中，还能用在排序中。MySQL中，有两种方式生成有序结果集：
通过有序索引顺序扫描直接返回有序数据
Filesort排序，对返回的数据进行排序
因为索引的结构是B+树，索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作。EXPLAIN分析查询时，Extra显示为Using index。
所有不是通过索引直接返回排序结果的操作都是Filesort排序，也就是说进行了额外的排序操作。EXPLAIN分析查询时，Extra显示为Using filesort，当出现Using filesort时对性能损耗较大，所以要尽量避免Using filesort
对于如下sql
select * from table where a = ? and b = ? order by c
可以建立联合索引（a, b, c）
如果索引中有范围查找，那么索引有序性无法利用，如
select * from table where a > 10 order by b
索引(a，b)无法排序。
放几个例子
-- 使用了a列
where a = 3
-- 使用了a b列
where a = 3 and b = 5
-- 使用了a b c列
where a = 3 and c = 4 and b = 5
-- 没有使用索引
where b = 3
-- 使用了a列
where a = 3 and c = 4
-- 使用了a b列
where a = 3 and b > 10 and c = 7
-- 使用了a b 列
where a = 3 and b like 'xx%' and c = 7
union，or，in都能命中索引，建议使用in
select * from article where id = 1
union all
select * from article where id = 2
select * from article where id in (1 , 2)
新版MySQL的or可以命中索引
select * from article where id = 1 or id = 2
效率从高到低为union，in，or。in和union的效率差别可以忽略不计，建议使用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)
建立覆盖索引
众所周知，表数据是放在一个聚集索引上的，而建立的索引为非聚集索引，非聚集索引的叶子节点存放索引键值，以及该索引键指向的主键。一般查找的过程是从非聚集索引上找到数据的主键，然后根据该主键到聚集索引上查找记录，这个过程称为回表，不清楚的看推荐阅读。
如有下面这个sql
select uid, login_time from user where username = ? and passwd = ?
可以建立(username, passwd, login_time)的联合索引，由于 login_time的值可以直接从索引中拿到，不用再回表查询，提高了查询效率
经常更改，区分度不高的列上不宜加索引
更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。
“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。
一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算
明确知道只会返回一条记录，可以加limit1
当查询确定只有一条记录时，可以加liimit1，让MySQL停止游标移动，提高查询效率
select uid from user where username = ? and passwd = ?
可改为
select uid from user where username = ? and passwd = ? limit 1
对文本建立前缀索引
用邮箱登录是一个常见的问题，如果对email整个字段建立索引，会让索引变得大且慢
select username from user where email='xxx';
这时我们可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但这样也会降低索引的区分度。索引的区分度是指，不重复的索引值和数据表的记录总数的比值。索引的区分度越高则查询效率越高，因为区分度高的索引可以让MySQL在查找时过滤掉更多的行。
因此我们选择足够长的前缀保证较高的区分度，同时又不能太长（以便节约空间）
可以进行如下实验
select count(distinct left(email, 5)) / count(*) as col5,
count(distinct left(email, 6)) / count(*) as col6,
count(distinct left(email, 7)) / count(*) as col7
from user
假设输出依次为0.0305，0.0309，0.0310
查询显示当前缀长度达到7的时候，再增加前缀长度，区分度提升的幅度已经很小了，因此创建email(7)的前缀索引即可
需要注意的一点是，前缀索引不能使用覆盖索引
建立索引的列不为NULL
只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。
因此，在数据库设计时，除非有一个很特别的原因使用 NULL 值，不然尽量不要让字段的默认值为 NULL。
分页查询优化
MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写.
SQL优化排查
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
6.下面的模糊查询也将导致全表扫描：
select id from t where name like ‘%abc%’
select id from t where name like ‘%abc’
但是下面的这种模糊查询依然可以使用索引
select id from t where name like ‘abc%’
7.应尽量避免在 where 子句中对字段的“=”左边进行函数、算术运算或其他表达式运算，这将导致引擎放弃使用索引而进行全表扫描。
如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
又如查找name以abc开头的id：
select id from t where substring(name,1,3)=’abc’
应改为:
select id from t where name like ‘abc%’
8.不要使用 select * from table ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
9.不要使用 select count(*) from table，这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，可以用count(1)代替。
10.注意：对于非常复杂的sql，有些人习惯对其建立视图，然后在视图的基础上进行查询。虽然这样sql写的简单了很多，但是并不会提升查询效率，因为该执行的sql还是得执行。
可以在视图中建立索引来提供查询效率，但会增加数据变更的开销。
标签：where,查询,索引,num,SQL,优化,id,select From： https://blog.51cto.com/u_16147772/6397213