mysql官网：https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html
一、查询执行过程
二、优化思路
数据查询慢，不代表sql语句写法有问题，用一张流程图展示MySQL优化思路
主要包括：
1、适当添加索引（四种：普通索引、主键索引、唯一索引、全文索引）
2、分表技术（水平分割、垂直分割）
3、读写（写：update/delete/add）分离
4、对mysql配置优化（my.conf）
5、mysql服务器硬件升级
2.1mysql查询执行顺序
示例SQL：
SELECT *
FROM user
LEFT JOIN order ON user.id = order.uid
WHERE order.price > 1000
GROUP BY user.name
HAVING count(1) > 5
ORDER BY user.name
LIMIT 0,10
1、FROM（将最近的两张表，进行笛卡尔积）—VT1
2、ON（将VT1按照它的条件进行过滤）—VT2
3、LEFT JOIN（保留左表的记录）—VT3
4、WHERE（过滤VT3中的记录）–VT4…VTn
5、GROUP BY（对VT4的记录进行分组）—VT5
6、HAVING（对VT5中的记录进行过滤）—VT6
7、SELECT（对VT6中的记录，选取指定的列）–VT7
8、ORDER BY（对VT7的记录进行排序）–游标
9、LIMIT（对排序之后的值进行分页）
WHERE条件执行顺序（影响性能）
1、MYSQL：从左往右去执行WHERE条件的。
2、Oracle：从右往左去执行WHERE条件的。
结论：写WHERE条件的时候，优先级高的部分要去编写过滤力度最大的条件语句。
三、查看MySQL服务器运行的状态值/服务器配置信息
3.1 查询MySQL 服务器运行的状态值
如果系统的并发请求数不高，且查询速度慢，可以忽略该步骤直接进行 SQL 语句调优步骤。
执行命令：show status/show global status 可以查看所有的性能会话参数/全局参数；或 show status like '参数名称' 可以查看指定参数名称的性能参数，一般某一类参数都有相同的前缀。
返回结果太多，我们主要关注“Queries”、“Threadsconnected” 和 “Threadsrunning” 的值，即查询次数、线程连接数和线程运行数。
3.2 查询MySQL服务器配置信息
执行命令：show variables/show global variables
3.3 status 与 variables 区别
status查看的参数值是由MySQL自己统计计算得到的。它是MySQL服务运行状态具体的量化体现。都是不可以修改的，也就是不能通过setxxx=yyy;的方式来改变它的值的。参数大多数以大写的英文字母开头。
variables查看的参数是MySQL服务的配置参数，在启动MySQL服务的时候，是可以修改具体的参数值来达到对MySQL进行动态配置的目的，通常配置在MySQL的my.cnf配置文件中。这些参数中，有些动态的参数可以通过setxxx=yyy;的方式来动态修改。这种参数大多数以小写的英文字母开头。
3.4 什么是全局参数？什么是会话session级别的参数？
全局参数：某个参数的值设置之后，对所有连接到MySQL服务的用户都生效，所有用户登录到MySQL后，该参数都是使用同一个值。
会话级别的参数：某个参数的值设置之后，只对当前连接到MySQL服务器的当前会话生效，当前会话退出后，这个参数就恢复了默认值。并且当连接A登录的时候，这个参数对A生效。但是对于新开一个连接服务B，在B的会话中，设置的参数并不会生效。
3.5 核心参数解析
3.5.1 慢查询
mysql> show variables like '%slow%';
+——————+——-+
| Variable_name | Value |
+——————+——-+
| log_slow_queries | ON |
| slow_launch_time | 2 |
+——————+——-+
mysql> show global status like '%slow%';
+———————+——-+
| Variable_name | Value |
+———————+——-+
| Slow_launch_threads | 0 |
| Slow_queries | 4148 |
+———————+——-+
配置中打开了记录慢查询，执行时间超过2秒的即为慢查询，系统显示有4148个慢查询，你可以分析慢查询日志，找出有问题的SQL语句，慢查询时间不宜设置过长，否则意义不大，最好在5秒以内，如果你需要微秒级别的慢查询，可以考虑给MySQL打补丁：http://www.percona.com/docs/wiki/release:start，记得找对应的版本。
打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主－从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。
3.5.2 连接数
经常会遇见”MySQL: ERROR 1040: Too many connections”的情况，一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max_connections值过小：
mysql> show variables like 'max_connections';
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 256 |
+—————–+——-+
这台MySQL服务器最大连接数是256，然后查询一下服务器响应的最大连接数：
mysql> show global status like 'Max_used_connections';
MySQL服务器过去的最大连接数是245，没有达到服务器连接数上限256，应该没有出现1040错误，比较理想的设置是
Max_used_connections / max_connections * 100% ≈ 85%
最大连接数占上限连接数的85％左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。
3.5.3 进程使用情况
mysql> show global status like 'Thread%';
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+——————-+——-+
如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器thread_cache_size配置：
mysql> show variables like 'thread_cache_size';
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| thread_cache_size | 64 |
+——————-+——-+
四、获取需要优化的SQL语句
4.1 方式一：查看运行的线程
执行命令：show processlist / show full processlist
返回结果：
从返回结果中我们可以了解该线程执行了什么命令/SQL 语句以及执行的时间。实际应用中，查询的返回结果会有 N 条记录。
其中，返回的 State 的值是我们判断性能好坏的关键，其值出现如下内容，则该行记录的 SQL 语句需要优化：
- Converting HEAP to MyISAM # 查询结果太大时，把结果放到磁盘，严重
- Create tmp table #创建临时表，严重
- Copying to tmp table on disk #把内存临时表复制到磁盘，严重
- locked #被其他查询锁住，严重
- loggin slow query #记录慢查询
- Sorting result #排序
4.2 方式二：开启慢查询日志（推荐）
在配置文件 my.cnf 中的 [mysqld] 一行下边添加几个参数：
slow_query_log = 1
slow_query_log_file=/var/lib/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
其中，slowquerylog = 1 表示开启慢查询；slowquerylogfile 表示慢查询日志存放的位置；longquerytime = 2 表示查询 >=2 秒才记录日志；logqueriesnotusing_indexes = 1 记录没有使用索引的 SQL 语句。
注意：slowquerylog_file 的路径不能随便写，否则 MySQL 服务器可能没有权限将日志文件写到指定的目录中。
修改保存文件后，重启 MySQL 服务。在 /var/lib/mysql/ 目录下会创建 slow-query.log 日志文件。连接 MySQL 服务端执行如下命令可以查看配置情况。
show variables like 'slow_query%';
show variables like 'long_query_time';
测试慢查询日志：
打开慢查询日志文件
我们可以看到刚才执行了 2 秒的 SQL 语句被记录下来了。
虽然在慢查询日志中记录查询慢的 SQL 信息，但是日志记录的内容密集且不易查阅。因此，我们需要通过工具将 SQL 筛选出来。
MySQL 提供 mysqldumpslow 工具对日志进行分析。我们可以使用 mysqldumpslow --help 查看命令相关用法。
常用参数如下：
案例：
获取返回记录集最多的10个sql
mysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log
获取访问次数最多的10个sql
mysqldumpslow -s c -t 10 /var/lib/mysql/slow-query.log
获取按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow-query.log
五、分析SQL语句
5.1 方式一：explain
筛选出有问题的 SQL，我们可以使用 MySQL 提供的 explain 查看 SQL 执行计划情况（关联表，表查询顺序、索引使用情况等）。
用法：explain select * from user;
返回结果：
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
注意：在5.7以前的版本中，想要显示partitions需要使用explain partitions命令；想要显示filtered需要使用explain extended命令。在5.7版本后，默认explain直接显示partitions和filtered中的信息。
字段解释：
- id：select 查询序列号。id相同，执行顺序由上至下；id不同，id值越大优先级越高，越先被执行
- select_type：查询数据的操作类型，其值如下：
- simple：简单查询，不包含子查询或 union
- primary:包含复杂的子查询，最外层查询标记为该值
- subquery：在 select 或 where 包含子查询，被标记为该值
- derived：在 from 列表中包含的子查询被标记为该值，MySQL 会递归执行这些子查询，把结果放在临时表
- union：若第二个 select 出现在 union 之后，则被标记为该值。若 union 包含在 from 的子查询中，外层 select 被标记为 derived
- union result：从 union 表获取结果的 select
- table：显示该行数据是关于哪张表
- partitions：匹配的分区
- type：表的连接类型，其值，性能由高到底排列如下：
- system：表只有一行记录，相当于系统表
- const：通过索引一次就找到，只匹配一行数据
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引扫描
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行。用于=、< 或 > 操作符带索引的列
- range：只检索给定范围的行，使用一个索引来选择行。一般使用between、>、<情况
- index：只遍历索引树
- ALL：全表扫描，性能最差
注：前5种情况都是理想情况的索引使用情况。通常优化至少到range级别，最好能优化到 ref
- possible_keys：指出 MySQL 使用哪个索引在该表找到行记录。如果该值为 NULL，说明没有使用索引，可以建立索引提高性能
- key：显示 MySQL 实际使用的索引。如果为 NULL，则没有使用索引查询
- key_len：表示索引中使用的字节数，通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好 显示的是索引字段的最大长度，并非实际使用长度
- ref：显示该表的索引字段关联了哪张表的哪个字段
- rows：根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好
- filtered：返回结果的行数占读取行数的百分比，值越大越好
- extra：包含不合适在其他列中显示但十分重要的额外信息，常见的值如下：
- using filesort：说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。出现该值，应该优化 SQL
- using temporary：使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。出现该值，应该优化 SQL
- using index：表示相应的 select 操作使用了覆盖索引，避免了访问表的数据行，效率不错
- using where：where 子句用于限制哪一行
- using join buffer：使用连接缓存
- distinct：发现第一个匹配后，停止为当前的行组合搜索更多的行
注意：出现前 2 个值，SQL 语句必须要优化。
5.2 方式二：profiling
使用 profiling 命令可以了解 SQL 语句消耗资源的详细信息（每个执行步骤的开销）。
5.2.1 查看 profile 开启情况
执行命令：select @@profiling;
返回结果：
0 表示关闭状态,1 表示开启
5.2.2 启用 profile
执行命令：set profiling = 1;
返回结果：
在连接关闭后，profiling 状态自动设置为关闭状态。
5.2.3 查看执行的 SQL 列表
执行命令：show profiles;
返回结果：
该命令执行之前，需要执行其他 SQL 语句才有记录。
5.2.4 查询指定 ID 的执行详细信息
执行命令：show profile for query Query_ID;
返回结果：
每行都是状态变化的过程以及它们持续的时间。Status 这一列和 show processlist 的 State 是一致的。因此，需要优化的注意点与上文描述的一样。
六、优化手段
6.1 查询优化
- 避免 SELECT *，需要什么数据，就查询对应的字段。
- 小表驱动大表，即小的数据集驱动大的数据集。如：以 A，B 两表为例，两表通过 id 字段进行关联。
当 B 表的数据集小于 A 表时，用 in 优化 exist；使用 in ，两表执行顺序是先查 B 表，再查 A 表
select * from A where id in (select id from B)
当 A 表的数据集小于 B 表时，用 exist 优化 in；使用 exists，两表执行顺序是先查 A 表，再查 B 表
select * from A where exists (select 1 from B where B.id = A.id)
- 一些情况下，可以使用连接代替子查询，因为使用 join，MySQL 不会在内存中创建临时表。
- 适当添加冗余字段，减少表关联。
- 合理使用索引。如：为排序、分组字段建立索引，避免 filesort 的出现。
6.2 索引使用
6.2.1 适合使用索引的场景
- 主键自动创建唯一索引
- 频繁作为查询条件的字段
- 查询中与其他表关联的字段
- 查询中排序的字段
- 查询中统计或分组字段
6.2.2 不适合使用索引的场景
- 频繁更新的字段
- where 条件中用不到的字段
- 表记录太少
- 经常增删改的表
- 字段的值的差异性不大或重复性高
6.2.3 索引创建和使用原则
- 单表查询：哪个列作查询条件，就在该列创建索引
- 多表查询：left join 时，索引添加到右表关联字段；right join 时，索引添加到左表关联字段
- 不要对索引列进行任何操作（计算、函数、类型转换）
- 索引列中不要使用 !=，<> 非等于
- 索引列不要为空，且不要使用 is null 或 is not null 判断
- 索引字段是字符串类型，查询条件的值要加''单引号,避免底层类型自动转换
- 违背上述原则可能会导致索引失效，具体情况需要使用 explain 命令进行查看
6.2.4 索引失效情况
除了违背索引创建和使用原则外，如下情况也会导致索引失效：
- 模糊查询时，以 % 开头
- 使用 or 时，如：字段1（非索引）or 字段2（索引）会导致索引失效。
- 使用复合索引时，不使用第一个索引列。index(a,b,c) ，以字段 a,b,c 作为复合索引为例：
注意：检索字段加了索引，但是范围查询时mysql优化器不一定会使用该索引，因为如果查询的数据较多，优化器会选择走全表扫描
6.3 数据库表结构设计
6.3.1 选择合适的数据类型
使用可以存下数据最小的数据类型
- 使用简单的数据类型。int 要比 varchar 类型在mysql处理简单
- 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int
- 尽可能使用 not null 定义字段，因为 null 占用4字节空间
- 尽量少用 text 类型,非用不可时最好考虑分表
- 尽量使用 timestamp 而非 datetime
- 单表不要有太多字段，建议在 20 以内
6.3.2 表的拆分
当数据库中的数据非常大时，查询优化方案也不能解决查询速度慢的问题时，我们可以考虑拆分表，让每张表的数据量变小，从而提高查询效率。
- 垂直拆分：将表中多个列分开放到不同的表中。例如用户表中一些字段经常被访问，将这些字段放在一张表中，另外一些不常用的字段放在另一张表中。插入数据时，使用事务确保两张表的数据一致性。
- 水平拆分：按照行进行拆分。例如用户表中，使用用户ID，对用户ID取10的余数，将用户数据均匀的分配到0~9的10个用户表中。查找时也按照这个规则查询数据。
6.3.3 读写分离
一般情况下对数据库而言都是“读多写少”。换言之，数据库的压力多数是因为大量的读取数据的操作造成的。我们可以采用数据库集群的方案，使用一个库作为主库，负责写入数据；其他库为从库，负责读取数据。这样可以缓解对数据库的访问压力。
6.4 补充
6.4.1 MySQL自带压力测试工具mysqlslap
常用参数 [options] 详细说明：
-u:连接数据库用户名
-p:链接数据库密码
--auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。
--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)。
--auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持。
--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1
--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1
--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
--query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。
--create-schema 代表自定义的测试库名称，测试的schema，MySQL中schema也就是database。
--commint=N 多少条DML后提交一次。
--compress, -C 如果服务器和客户端支持都压缩，则压缩信息传递。
--concurrency=N, -c N 表示并发量，也就是模拟多少个客户端同时执行select。可指定多个值，以逗号或者--delimiter参数指定的值做为分隔符。例如：--concurrency=100,200,500。
--engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开。例如：--engines=myisam,innodb。
--iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次。
--only-print 只打印测试语句而不实际执行。
--detach=N 执行N条语句后断开重连。
--debug-info, -T 打印内存和CPU的相关信息。
说明：测试的过程需要生成测试表，插入测试数据，这个mysqlslap可以自动生成，默认生成一个mysqlslap的schema，如果已经存在则先删除。可以用--only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹。
各种测试参数实例（-p后面跟的是mysql的root密码）：
单线程测试。测试做了什么。
mysqlslap -a -uroot -p123456
多线程测试。使用–concurrency来模拟并发连接。
mysqlslap -a -c 100 -uroot -p123456
迭代测试。用于需要多次执行测试得到平均值。
mysqlslap -a -i 10 -uroot -p123456
mysqlslap ---auto-generate-sql-add-autoincrement -a -uroot -p123456
mysqlslap -a --auto-generate-sql-load-type=read -uroot -p123456
mysqlslap -a --auto-generate-secondary-indexes=3 -uroot -p123456
mysqlslap -a --auto-generate-sql-write-number=1000 -uroot -p123456
mysqlslap --create-schema world -q "select count(*) from City" -uroot -p123456
mysqlslap -a -e innodb -uroot -p123456
mysqlslap -a --number-of-queries=10 -uroot -p123456
测试同时不同的存储引擎的性能进行对比：
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -p123456
执行一次测试，分别50和100个并发，执行1000次总查询：
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -p123456
50和100个并发分别得到一次测试结果(Benchmark)，并发数越多，执行完所有查询的时间越长。为了准确起见，可以多迭代测试几次:
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -p123456
七、补充
7.1 数据表设计建议
《阿里巴巴Java开发手册》，数据库的表设计允许适当冗余，以提升SQL查询的性能，避免表的关联查询。
7.1.1 适度冗余，减少join的关联
冗余更新频率不高，但是查询频率极高的字段。如订单中的商品名称。
7,1.2 大字段垂直拆分
比如商品SPU的商品的描述
7.1.3 大表水平拆分
比如优惠券、帖子等
7.1.4 合适的数据类型
如果数据量一样，但数据类型更小的话，数据存放同样的数据就会占用更少的空间，这样检索同样的数据所带来的IO消耗自然会降低，性能也就很自然的得到提升。此外，MySQL 对不同类型的数据，处理方式也不一样，比如在运算或者排序操作中，越简单的数据类型操作性能越高，所以对于要频繁进行运算或者排序的字段尽量选择简单的数据类型。
7.2 SQL优化建议
7.2.1 联合索引尽量覆盖条件
比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
7.2.2 不要在小基数字段上建立索引
索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查 找的优势来。
7.2.3 where与order by冲突时优先where
因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。
7.2.4 分页查询优化
select * from A ORDER BY name limit 90000,5;
可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL改写如下
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id= ed.id;
7.2.5 对于join关联sql的优化
1.关联字段加索引
2.小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间
straight_join解释：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。
比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。
straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）
尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。
3.对于小表定义的明确
在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
4.in和exsits优化
原则：小表驱动大表，即小的数据集驱动大的数据集
in：当B表的数据集小于A表的数据集时，in优于exists
select * from A where id in (select id from B)
等价于：
for(select id from B){
select * from A where A.id= B.id
}
exists：当A表的数据集小于B表的数据集时，exists优于in
select * from A where exists (select 1 from B where B.id = A.id)
等价于:
for(select * from A){
select * from B where B.id = A.id
}
