一、优化SQL的方法
SQL优化的一般步骤：先查询mysql数据库运行状况，然后定位慢查询，再分析sql的执行过程，然后进行优化
1.使用show status查询数据库的运行状况
//显示数据库运行状态
SHOW STATUS
//显示数据库运行总时间
SHOW STATUS LIKE 'uptime'
//显示连接的次数
SHOW STATUS LIKE 'connections'
//显示执行CRUD的次数
SHOW STATUS LIKE 'com_select'
SHOW STATUS LIKE 'com_insert'
SHOW STATUS LIKE 'com_update'
SHOW STATUS LIKE 'com_delete'2.定位慢查询sql
我们可以通过mysql来记录慢查询，一旦有sql执行时间超过了设置的慢查询时间，就会被记录到慢查询日志中。这样我们就可以从慢查询日志中定位慢查询sql，然后进行分析优化。
查看慢查询相关信息
//显示慢查询次数
SHOW STATUS LIKE 'slow_queries'
//显示慢查询时间，默认为10s
SHOW VARIABLES LIKE 'long_query_time'mysql的慢查询默认是关闭的，可以通过修改配置文件或通过命令来开启。
①修改配置文件方式
Linux下修改my.cnf，Windows下修改my.ini。修改后需要重启mysql才会生效。
#开启慢查询
slow-query-log=1
#慢查询的文件路径
slow_query_log_file="D:/Program Files/MySQL/Log/mysql-slow.log"
#慢查询时间。默认为10秒
long_query_time=10
复制代码②命令方式
也可以使用命令来修改。
【session级别】
#开启慢查询
SET slow_query_log='ON';
#设置慢查询日志存放位置
SET slow_query_log_file='/usr/local/mysql/data/slow.log';
#设置慢查询时间
SET long_query_time=3
【global级别】
SET global slow_query_log='ON';
SET global slow_query_log_file='/usr/local/mysql/data/slow.log';
SET global long_query_time=33.分析慢查询
在实际生产环境中，可能因为开发写了不正确的SQL语句，索引优化的不好，或其他查询操作而导致数据库整体性能下降。我们只需要分析一下慢查询日志就会知道问题出在哪。
//查看是否启用慢日志记录和状态
show variables like "%slow%"
如果慢查询日志中记录内容较多，则可以使用Mysql自带的慢查询日志分析工具mysqldumpslow工具来对慢查询日志进行分类汇总。该工具位于/mysql/bin目录下。mysqldumpslow将会自动将文本完全一致但变量不同的SQL语句视为同一个语句进行统计，变量值用N来代替。
mysqldumpslow -s r -t 10 /data/dbdata/frem-slow.log
4.使用explain分析sql执行过程
mysql会将慢查询记录到慢查询日志中，这时我们就可以针对这些慢查询的sql进行分析和优化，需要用到explain命令。
explain [要分析的sql]分析结果中有如下几列：
+----+-------------+---------+------+---------------+------+---------+------+------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+-------+
每列的含义如下。官方文档：Explain Output Colums
- simple：表示简单查询，join也是简单查询。(不包含union和子查询)。
#简单查询
select id from emp;
#简单查询(join)
select id from emp join dept on emp.dept_id=dept.id;- primary：表示主查询(最外层的查询)。包括union操作的第一个select查询。
#子查询形式：第一个select为primary
select * from app_school where id = (select id from app_school where id=100);
#union形式：第一个select为primary
select * from app_school where id=100
union
select * from app_school where id=101;- subquery：表示子查询(子查询中的第一个select查询)。
#子查询中的第1个select(即下面的第2个select)
select * from app_school where id = (select id from app_school where id=100);- derived：表示导出表，from后跟着的子查询。
#from后的select属于derived
select * from (select id from app_school) t;- union：表示UNION操作的第2个或后面的查询。
#第2个select为union
select * from app_school where id=100
union
select * from app_school where id=101;- union result：表示获取UNION最后结果的查询。
我的总结：
①简单查询(包括join):simple
②子查询:primary,subquery,derived
③union查询:union,union result
③table：查询的表。
④type：找到匹配行用到的访问类型。
最为常见的类型有system，const，eq_ref，ref，range，index，All
按照性能从高到低顺序如下：
- NULL：不用访问表或索引，就可直接得出结果。
- system：该表是最多仅有一行的系统表。(这是const类型的一个特例)
系统表中的数据通常已经加载到了内存中，所以不需要磁盘IO。
例子1：查询系统表
例子2：内层嵌套(const)返回了一个临时表，外层嵌套从临时表中查询，其扫描类型也是system，也不需要磁盘IO。 - const：最多只有一个匹配行，所以该行中的其它列的值可以当作常量来处理。
例如，根据主键primary key或唯一索引unique index进行查询。 - eq_ref：使用唯一索引，对于每个索引键值，表中只有一条记录匹配。简单说，就是多表连接中使用primary key或unique index作为关联条件。
- ref：使用非唯一索引，或唯一索引的前缀扫描。
- ref_or_null：与ref类似，区别在于条件中包含对NULL的查询。
- index_merge：索引合并优化。
- unique_subquery：in的后面是一个查询主键字段的子查询。
- index_subquery：与unique subquery类似，区别在于in的后面是查询非唯一索引字段的子查询。
- range：只检索指定范围的行，使用一个索引来选择行。常见于<，<=，>，>=，between或者IN操作符。
key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。 - index：索引全扫描。遍历整个索引来查询匹配的行。
- ALL：全表扫描，性能最差。
⑤possible_keys：表示查询时可能使用到的索引
⑥key：表示实际使用的索引
⑦key_len：使用到的索引字段的长度
⑧ref：显示该表的索引字段关联了哪张表的哪个字段
⑨rows：扫描行的数量
⑩Extra：执行情况的说明和描述。包含不适合在其它列中显示但对执行计划非常重要的额外信息。
5.使用show profile分析sql
有时候，仅仅通过explain分析执行计划并不能很快地定位SQL的问题，这时就可以选择profile联合分析。
Mysql从5.0.37开始增加了对show profiles和show profile语句的支持。默认profile是关闭的，可以通过set开启session级别的profiling。
//查看是否支持profile
SELECT @@have_profiling
//开启profile(session级别)
set profiling=1;①执行select语句
②show profiles,查询该sql语句的Query ID.
③通过show profile for query语句能看到执行中线程的每个状态和消耗的时间。
show profile for query [上面的query id]6.通过trace分析优化器如何选择执行计划
Mysql5.6提供了对sql的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划，帮助我们更好地理解优化器的行为。
使用方式：
①首先打开trace，设置格式为json，设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。
//打开trace，设置json格式
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
//设置内存
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;②接下来执行想做trace的sql语句
select xxx③检查INFORMATION_SCHEMA.OPTIMIZER_TRACE就知道mysql是如何执行sql的了
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G最后会输出一个跟踪文件。
7.确定问题并采用相应的优化措施
经过前面的一系列步骤，基本就可以确定问题出现的原因了。此时就可以根据情况采取相应的措施进行优化了。
8 百万级量的数据分页查询如何优化？
方法 1: 直接使用数据库提供的 SQL 语句
语句样式: MySQL 中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N
适应场景: 适用于数据量较少的情况(元组百/千级)
原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返
回 1,2,3,另外的一次返回 2,1,3). Limit 限制的是从结果集的 M 位置处取出 N 条
输出,其余抛弃.
方法 2: 建立主键或唯一索引, 利用索引(假设每页 10 条)
语句样式: MySQL 中,可用如下方法: SELECT FROM 表名称 WHERE id_pk > (pageNum10) LIMIT M
适应场景: 适用于数据量多的情况(元组数上万)
原因: 索引扫描,速度会很快. 有朋友提出: 因为数据查询出来并不是按照
pk_id 排序的，所以会有漏掉数据的情况，只能方法 3
方法 3: 基于索引再排序
语句样式: MySQL 中,可用如下方法: SELECT FROM 表名称 WHERE id_pk > (pageNum10) ORDER BY id_pk ASC LIMIT M
适应场景: 适用于数据量多的情况(元组数上万). 最好 ORDER BY 后的列对象
是主键或唯一所以,使得 ORDERBY 操作能利用索引被消除但结果集是稳定的(稳
定的含义,参见方法 1)
原因: 索引扫描,速度会很快. 但 MySQL 的排序操作,只有 ASC 没有
DESC(DESC 是假的,未来会做真正的 DESC,期待...).
方法 4: 基于索引使用 prepare（第一个问号表示 pageNum，第二个？表示 每页元组数）
语句样式: MySQL 中,可用如下方法: PREPARE stmt_name FROM
SELECT FROM 表名称 WHERE id_pk > (？ ？) ORDER BY id_pk ASC LIMIT M
适应场景: 大数据量
原因: 索引扫描,速度会很快. prepare 语句又比一般的查询语句快一点。
方法 5: 利用 MySQL 支持 ORDER 操作可以利用索引快速定位部分元组,避免全表扫描
比如: 读第 1000 到 1019 行元组(pk 是主键/唯一键).
17 SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20
方法 6: 利用"子查询/连接+索引"快速定位元组的位置,然后再读取元组. 道理 同方法 5
如(id 是主键/唯一键,蓝色字体时变量):
