优化介绍

1.1 MySQL

MySQL凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。可以看到Google，Facebook，Twitter，百度，新浪，腾讯，淘宝，网易，久游等绝大多数互联网公司数据库都是用的MySQL数据库，甚至将其作为核心应用的数据库系统。

虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。

我们将这里进行一个较为全面的分析，让大家了解到MySQL的性能到底与哪些地方有关，以便于让大家寻找出其性能问题的根本原因，而尽可能清楚的知道该如何去优化自己的数据库。

1.2 优化需要考虑的问题

• 优化有风险，涉足需谨慎！

• 优化工作,是由业务需要驱使的！

1.2.1 优化可能带来的问题

- 优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统！
- 优化手段有很大的风险，一定要意识到和预见到！
- 任何的技术可以解决一个问题，但必然存在带来一个问题的风险！
- 对于优化来说调优而带来的问题,控制在可接受的范围内才是有成果。
- 保持现状或出现更差的情况都是失败！

1.2.2 优化需求

1
2
3
4
- 稳定性和业务可持续性,通常比性能更重要！
- 优化不可避免涉及到变更，变更就有风险！
- 优化使性能变好，维持和变差是等概率事件！
- 优化应该是各部门协同，共同参与的工作！

1.3 优化思路

1.3.1 优化的方向

在数据库优化上有两个主要方向：即安全与性能。
- 安全 : 数据安全性
- 性能 : 数据的高性能访问

本章节主要是在性能优化方向进行介绍

1.3.2 优化的维度

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
msyqladmin #mysql客户端，可进行管理操作

mysqlshow #功能强大的查看shell命令

show [SESSION | GLOBAL] variables #查看数据库参数信息

SHOW [SESSION | GLOBAL] STATUS #查看数据库的状态信息

SHOW ENGINE INNODB STATUS Innodb #引擎的所有状态

information_schema #获取元数据的方法

SHOW PROCESSLIST #查看当前所有连接session状态

explain #获取查询语句的执行计划

how index #查看表的索引信息

slow-log #记录慢查询语句

mysqldumpslow #分析slowlog文件的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
zabbix #监控主机、系统、数据库（部署zabbix监控平台）

mysqlslap #分析慢日志

sysbench #压力测试

workbench #管理、备份、监控、分析、优化工具（比较费资源）

pt-query-digest #分析慢日志

mysql profiling #统计数据库整体状态工具

Performance Schema mysql #性能状态统计的数据

1
2
3
4
5
1. show processlist（查看连接session状态）
2. explain(分析查询计划)，show index from tableName（分析索引）
3. 通过执行计划判断，索引问题（有没有、合不合理）或者语句本身问题
4. show status like '%lock%'; # 查询锁状态
5. kill sessionId; # 杀掉有问题的session

1
2
3
4
1. 查看slowlog，分析slowlog，分析出查询慢的语句。
2. 按照一定优先级，进行一个一个的排查所有慢语句。
3. 分析top sql，进行explain调试，查看语句执行时间。
4. 调整索引或语句本身。

我们该如何进行sql优化呢， 首先我们需要知道，sql优化其实主要是解决查询的优化问题，所以我们先从数据库的查询开始入手，查询的执行路径：

　① 客户端将查询发送到服务器；

　② 服务器检查查询缓存，如果找到了，就从缓存中返回结果，否则进行下一步。

　③ 服务器解析，预处理。

　④ 查询优化器优化查询

　⑤ 生成执行计划，执行引擎调用存储引擎API执行查询

　⑥服务器将结果发送回客户端。

1
2
3
4
5
6
7
mysql的慢查询日志是mysql提供的一种日志记录，它用来记录在mysql中响应时间超过阀值的语句,mysql 的日志是跟踪mysql性能瓶颈的最快和最直接的方式了，系统性能出现瓶颈的时候，首先要打开慢查询日志，进行跟踪，尽快的分析和排查出执行效率较慢的SQL ,及时解决避免造成不好的影响。
 
作用：记录具体执行效率较低的SQL语句的日志信息。

注意：
- 在默认情况下mysql的慢查询日志记录是关闭的。
- 同时慢查询日志默认不记录管理语句和不使用索引进行查询的语句

1
2
-- 在mysql客户端输入如下指令，默认是关闭的
show variables like '%slow_query_log%';

1
2
-- 开启mysql慢查询(注意，只针对当前数据库生效，重启MySQL服务后，则会失效)
set global slow_query_log=1;

1
2
3
4
5
6
7
我们可以在my.cnf或my.ini文件中添加如下配置：

slow_query_log=ON
slow-query-log-file=/var/lib/mysql/slow-query.log
long_query_time=0

修改完成后，重启mysql：service mysqld restart。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[root@mysql_data]# mysqldumpslow slow-query.log
Reading mysql slow query log fromslow-query.log
Count: 2 Time=11.00s (22s) Lock=0.00s (0s)Rows=1.0 (2), root[root]@mysql
select count(N) from t_user;

Count：执行的次数
Time：每次执行时间（一共执行了多长时间）
Lock：等待锁的时间
Rows：每次返回的记录数（总记录数）


[root@mysql_data]# mysqldumpslow -s c -t 10 -g “leftjoin” slow-query.log
参数说明：
-s：按照哪种方式进行排序
 c：访问计数
 l：锁定时间
 r：返回记录
 al：平均锁定时间
 ar：平均返回记录数
 at：平均查询时间
-t：是top，即返回前多少条数据
-g：指定匹配模式

1
2
3
4
5
6
-查看表的读取顺序
- 数据读取操作的操作类型
- 查看哪些索引可以使用
- 查看哪些索引被实际使用
- 查看表之间的引用
- 查看每张表有多少行被优化器执行

1
2
3
4
5
-- 语法
explain sql语句

-- 示例
explain select * from tb_user;

explain select cus_id from testemployee where cus_id > 10

参数说明：
id：select查询的序列号，包含一组数字,表示查询中执行select子句或操作表的顺序。
 id值相同：执行顺序由上到下
 id值不同：如果是子查询,id的序号会递增,id值越大优先级越高,优先被执行

1
2
-- id值相同
EXPLAIN SELECT * from employee e,department d,customer c where e.dep_id = d.id and e.cus_id = c.id;

-- id值不同
EXPLAIN SELECT * from department WHERE id = (SELECT id from employee WHERE id=(SELECT id from customer WHERE id = 1))

• select_type

查询类型,主要用于区别普通查询,联合查询,子查询等复杂查询

1
2
3
4
5
6
7
8
9
10
11
- SIMPLE : 简单的select查询,查询中不包含子查询或者UNION

- PRIMARY: 查询中若包含复杂的子查询,最外层的查询则标记为PRIMARY

- SUBQUERY : 在SELECT或者WHERE列表中包含子查询

- DERIVED : 在from列表中包含子查询被标记为DRIVED衍生,MYSQL会递归执行这些子查询,把结果放到临时表中

- UNION: 若第二个SELECT出现在union之后,则被标记为UNION, 若union包含在from子句的子查询中,外层select被标记为:derived

- UNION RESULT: 从union表获取结果的select

1
2
3
4
-- union 和 union result 示例
EXPLAIN select * from employee e LEFT JOIN department d on e.dep_id = d.id
UNION
select * from employee e RIGHT JOIN department D ON e.dep_id = d.id

• table

显示这一行的数据是关于哪张表的

• partitions

如果查询是基于分区表的话, 会显示查询访问的分区

• type

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
访问类型排列
结果值:(最好到最差) system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > ALL

 一般来说，得保证查询至少达到range级别，最好能达到ref。
- system：表中只有一行记录(系统表), 这是const类型的特例, 基本上不会出现

- const：通过索引一次查询就找到了，const用于比较primary key或者unique索引，该表最多有一个匹配行, 在查询开始时读取。由于只有一行, 因此该行中列的值可以被优化器的其余部分视为常量。const 表非常快, 因为它们只读一次。

- eq_ref：读取本表中和关联表表中的每行组合成的一行。除 了 system 和 const 类型之外, 这是最好的联接类型。当连接使用索引的所有部分时, 索引是主键或唯一非 NULL 索引时, 将使用该值。

- ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有符合条件的行。

- range: 只检索给定范围的行, 使用一个索引来选择行.key列显示的是真正使用了哪个索引,一般就是在where条件中使用between,>,<,in 等范围的条件,这种在索引范围内的扫描比全表扫描要好,因为它只在某个范围中扫描,不需要扫描全部的索引

- index: 扫描整个索引表, index 和all的区别为index类型只遍历索引树. 这通常比all快,因为索引文件通常比数据文件小,虽然index和all都是读全表,但是index是从索引中读取,而all是从硬盘中读取数据

- all: 全表扫描 ,将遍历全表以找到匹配的行

注意: 开发中,我们得保证查询至少达到range级别,最好能达到ref. 如果百万条数据出现all, 一般情况下就需要考虑使用索引优化了

1
2
-- system 表中有一行记录(系统表) 这是const类型的特例,平时不会出现
explain select HOST from mysql.db where HOST='localhost'

1
2
-- const 表示通过索引一次就找到了，const用于比较primary 或者 unique索引.  直接查询主键或者唯一索引，因为只匹配一行数据,所以很快
EXPLAIN select id from testemployee where id=1000

1
2
-- eq_ref 唯一性索引扫描 对于每个索引键,表中只有一条记录与之匹配, 常见于主键或唯一索引扫描
EXPLAIN select * from employee e,department d where e.id=d.id

1
2
-- ref 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体
EXPLAIN select e.id,e.dep_id,d.id from employee e,department d where e.dep_id = d.id

1
2
-- range 只检索给定范围的行,使用一个索引来选择行 一般就是在你的where语句中出现between\<\>\ in等查询,这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点.而结束语另一点,不用扫描全部索引
explain select * from employee where id>2

1
2
-- index index与All区别为index类型只遍历索引树,通常比All要快,因为索引文件通常比数据文件要小all和index都是读全表,但index是从索引中读取,all是从硬盘当中读取
explain select id from employee

1
2
-- ALL 将全表进行扫描,从硬盘当中读取数据,如果出现了All 切数据量非常大, 一定要去做优化
explain select * from employee

• possible_keys

1
2
3
显示可能应用在这张表中的索引,一个或者多个
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
可能自己创建了4个索引,在执行的时候,可能根据内部的自动判断,只使用了3个

1
2
-- 可能不会用到索引，实际用到索引
explain select dep_id from employee

1
2
-- 可能会使用索引，实际没用到索引
EXPLAIN select * from employee e,department d where e.dep_id = d.id

• key

1
2
3
实际使用的索引,如果为NULL,则没有使用索引,查询中若使用了覆盖索引 ,则该索引仅出现在key列表
possible_keys与key关系,理论应该用到哪些索引 实际用到了哪些索引
覆盖索引 查询的字段和建立的字段刚好吻合,这种我们称为覆盖索引

• key_len

索引中使用的字节数，可通过该列计算查询中使用的索引的长度，长度越短越好。

1
2
-- 表示索引中使用的字节数,可通过该列计算查询中使用的索引长度 .
explain select * from employee where dep_id=1 and name='java禅道' and age=10

• ref

索引是否被引入到, 到底引用到了哪几个索引

Explain select * from employee e,department d where e.dep_id = d.id and e.cus_id = 1

1
2
Explain select e.dep_id from employee e,department d,customer c where e.dep_id = d.id
and e.cus_id = c.id and e.name='java禅道'

• rows

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,每长表有多少行被优化器查询过

• filtered

显示了通过条件过滤出的行数的百分比估计值

1
2
-- 满足查询的记录数量的比例，注意是百分比，不是具体记录数 . 值越大越好，filtered列的值依赖统计信息，并不十分准确
Explain select e.dep_id from employee e,department d where e.dep_id = d.id

• Extra

1
2
3
4
5
6
7
8
9
10
该列包含MySQL解决查询的详细信息。
Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

1
2
-- Using filesort (需要优化)说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行Mysql中无法利用索引完成排序操作称为"文件排序"
explain select * from employee where dep_id =1 ORDER BY cus_id

1
2
-- Using temporary (需要优化)使用了临时表保存中间结果,Mysql在对查询结果排序时, 使用了临时表,常见于排序orderby 和分组查询group by
explain select name from employee where dep_id in (1,2,3) GROUP BY cus_id

1
2
-- impossible where (需要优化)where 子句的值总是false 不能用来获取任何元组
explain select name from employee where name='java禅道' and name='zs'

结语

本篇文章内容主要通过慢查询以及执行计划来分析我们执行的SQL语句，在下篇文章中咱们会学习具体的优化方案以及实践。