优化步骤
查看系统性能参数
show [global | session] status like '参数';
一些常用的性能参数：
connections:连接MySQL服务器次数
uptime：MySQL服务器的上线时间
slow_queries：慢查询次数
innodb_rows_read：select查询返回的行数
innodb_rows_inserted：执行insert操作插入的行数
innodb_rows_updated：执行update操作更新的行数
innodb_rows_deleted：执行delete操作删除的行数
com_select：查询操作的次数
com_insert：插入操作的次数，批量插入只累加一次
com_update：跟新操作的次数
com_delete：删除操作的次数
show status like 'slow_queries';
show status like 'innodb_rows%';
统计sql查询的成本
show status like 'last_query_cost'; -- value:数据页
定位执行慢的sql
-- 开启慢查询日志参数
show variables like '%slow_query_log%';
-- 开启慢查询日志
set GLOBAL slow_query_log = 'ON';
-- 查看慢查询时间阈值
show variables like '%long_query_time%';
-- 修改慢查询阈值(global and session 级别变量)
set global long_query_time = 1;
-- 查看慢查询记录
show status like 'slow_queries';
分析工具
慢查询日志分析工具：mysqldumpslow
分析优化器执行计划：trace（mysql自带）
MySQL监控分析视图：sys schema
-- 查看当前线程执行情况，如果发现了异常的sql语句，可以直接kill掉，确保数据库不会出现严重的问题
show processlist
查看sql执行开销(mysql cmd界面操作)
-- 查看并开启会话级别的执行概述
show variables like 'profiling';
set profiling = 'ON';
-- 执行相关查询后再查看近期的查询开销
show profiles;
/**
查看指定query_ID的开销
all:显示所有的开销信息
block io:显示块io开销
context switches:上下文切换开销
cpu:显示cpu开销信息
ipc:显示发送和接收开销信息
memory:显示内存的开销信息
page faults:显示页面错误开销信息
source:显示source相关的开销信息
swaps:显示交换次数的开销信息
*/
show profile for query 1;
show profile all ,block io for query 1;
Explain
-- explain字段解析
（重点）id:在一个大的查询语句中，每个select关键字都对应一个唯一的id。
1 一个select对应一个id，explain会出现多行相同id
2 id相同为一组，从上往下执行；
3 在所有组中，id值越大就越先执行；
4 每个不同的id标识一趟独立的查询，一个sql查询次数越少越好
select_type: 查询类型
1 select关键字对应的查询类型，确定小查询在大查询中扮演的角色。
2 查询中不包含 union 或 子查询，都算作simple类型
table:输出行所引用的表的名称
1 可观察产生的临时表 | extra：using temporary
2 首行: 驱动表，往下: 被驱动表
partitions：代表分区表中的命中情况，非分区表该值为 null，一般情况下该值都为 null。
（重点）type: 执行查询时的访问方法/访问类型
1 system:当表中只有一条记录，且该表使用的存储引擎的统计数是精确的，如MyISAM、Memory
2 const:对单表的主键或unique索引列与常数进行等值匹配
3 eq_ref:在连接查询时，如果被驱动表（join右侧的表）是通过主键或unique索引列，进行等值匹配的方式进行访问的（如果该主键或unique索引是联合索引的话，所有的索引列都必须进行等值比较）
4 ref：当通过普通的二级索引与常量进行等值匹配
possible_keys：可能用到的索引
key：真实用到的索引
key_len：实际用到的索引长度（单位：字节数）
1 主要针对联合索引，值越大越好：4个字段构成联合索引共200bytes，若值为200说明可能只用到联合索引中的前两个
ref: 当使用索引列进行等值查询时，与索引列进行等值匹配的对象信息
rows: 预估需要读取的记录条数
filtered：查询表经过搜索条件过滤后，查出数据条数/过滤剩余记录条数的百分比，值越大越好，说明使用的数据页少
1 连接查询时关注: 它决定被驱动表要执行的次数（rows * filtered）
（重点）Extra: 额外信息
1 using filesort: 性能低下需规避
-- explain其他输入格式
explain format = json select SID from tb_relation_student;
-- 通过诊断语句查看当前会话中执行语句所产生的条件（错误、警告和注释）的信息(cmd界面)
show warnings ;
InnoDB SQL优化
关联查询优化
- *** 关联查询小结果集驱动大结果集（本质就是减少外层循环的数据数量）（小的度量单位：过滤后的表行数*每行大小）
- 查询优化器会自动优化sql，被驱动表不一定是left join 右侧的表，主要看explain 同id最下方表名
- 优先给被驱动表的过滤条件添加索引(一般是 t2.name)，类型要一致，否则类型转换索引失效
- select * from t1 left join t1 on t1.name = t2.name
子查询查询优化
-
不建议使用子查询，执行效率不高
- 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的 CPU和 IO资源，产生大量的慢查询
- 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表，都不会存在索引，所以查询性能会受到一定的影响
- 对于返回结果集比较大的子查询，其对查询性能的影响也就越大
-
优化
- 能够直接多表关联的尽量直接关联，不应子查询（减少查询的次数）
- 不建议使用子查询，建议将子查询 sql拆开结合程序多次查询，或使用 join来代替子查询
- 衍生表建立不了索引
- 尽量不要使用 not in 或者 not exists，用 left join...on...where...is null 代替
order by排序优化
-
在 order by字段上加索引，避免 select *
-
在 MySQL中支持两种排序方式，分别是 filesort 和 index排序
index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高
filesort排序一般在内存中进行排序，占用CPU较多。如果带排结果较大，会产生临时文件IO到磁盘进行排序的情况，效率较低
-
-
优化
-
sql中可以在 where子句和 order by子句中使用索引，避免在 where子句的全表扫面，order by子句的filesort排序
-
尽量使用 index完成 order by排序，如果 where和 order by后面是相同的列就使用单索引列，如果不同就使用联合索引
-
无法使用 index时，需要对 filesort方式进行调优
filesort算法：双路排序和单路排序
调优：提高 sort_buffer_size, max_length_for_sort_data
-
order by 后使用 limit，数据量小可以使用索引后回表
create index idx_age_classid_name on student(age,classid,name);
select * from student order by age,classid limit 10;
-
覆盖索引可以提升效率 create index idx_age_classid_name on student(age, classid,name);
select age, classid, name from student order by age,classid;
-
-
order by 索引失效
-
需要回表的数据量过大导致索引失效，查询优化器会直接使用filesort进行排序
create index idx_age_classid_name on student(age,classid,name);
select * from student order by age,classid;(数据量小时使用索引，大时失效，以explain为准)
-
order by顺序错误索引失效：跳过联合索引最左字段
-
order by规则不一致，索引失效
联合索引默认按每个字段升序排序，多字段排序时，一个字段升序，一个字段降序，索引失效
-
group by 分组优化
- group by即使没有过滤条件用到索引，也可以直接使用索引。
- group by先排序再分组，遵照索引建立的最佳左前缀法则
- 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数设置
- where效率高于 having，尽量再where时写限定条件
- 减少使用group by，尽量在程序端做。order by、group by、distinct较为耗费CPU
- 包含order by、group by、distinct这些查询语句，where条件过滤出来的结果集保持在1000行以内，否则 sql会很慢
- 和order by优化差不多
分页查询优化
-- 通过创建覆盖索引提升深度分页的性能，避免 innodb进行索引的二次查询（回表）
-- 在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容
select * from t1, (select id from t1 order by id limit 2000000,10) t2 where t1.id = t2.id;
-- 该方案使用于主键自增的表，可以把 limit查询转换成某个位置的查询(不用回表)
select * from t1 where id > 2000000 limit 10;
覆盖索引
-
覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引
-
优点
- 避免innodb进行索引的二次查询（回表）：innodb是以聚簇索引的顺序来存储的，覆盖索引避免了对主键的二次查询，减少了io操作
- 可以把随机 IO变成顺序 IO加快查询效率：使用二级索引获取到需要的记录id后，回表时使用的id可能时无序的
-
弊端：维护代价高，索引冗余
-
覆盖索引下，(!=)( like %)不一定失效
create index idx_age_classid_name on student(age, classid, name);
select age, classid, name from student where age != 20;
select age, classid, name from student where name like '%德';
索引条件下推
-- ICP: Index Condition Pushdown
-- 主要针对联合索引，在使用左侧通配符时也能使用索引
create index idx_age_classid_name on student(age, classid, name);
select * from student where age = 20 and classid like '%2%' and name like '%德%'
-- 关闭索引下推，默认开启
SET OPTIMIZER_SWITCH = 'index_condition_pushdown=off'
其他查询优化策略
-
exists 和 in 的区分（索引时前提）
- select * from t1 where xx in ( select xx from t2 ); t1表 大于 t2表用 in，此时 t2是外循环（小表驱动大表）
- select * from t1 where exists ( select xx from t2 where t2.xx = t1.xx ); t1表 小于 t2表用 exists，此时 t1是外循环（小表驱动大表）
-
select（*）
- MySQL在解析过程中会通过查数据字典将 * 解析为所有列明，会大大的消耗资源和时间。
- 无法使用覆盖索引
-
limit 1 对优化的影响
- 若确定结果集只有一条，加上 limit 1，在找到一条结果后就不在继续扫描了，加快查询速度
- 若对字段建立了唯一索引则不需要加上 limit 1
-
多使用commit：在程序中多使用commit会因为资源的释放而提高程序的性能，释放的资源如下
- 回滚段上用于恢复数据的信息
- 被程序语句获得的锁
- redo / undo log buffer 中的空间
- 管理上述3种资源中的内部花费
