一、性能分析
1. 查看系统性能参数
语法如下:
show [global | session] status like ‘参数’
- connections 【连接mysql次数】
- uptime 【mysql服务上线时间】
- slow_queries 【共发生多少次慢查询 - 系统有阀值】
采用show variables like 'long_query_time';查询阀值
- innodb_rows_read【服务至上线以来 共执行多少次查询】
- innodb_rows_delete【服务至上线以来 共执行多少次删除】
- innodb_rows_insert【服务至上线以来 共执行多少次插入】
- innodb_rows_update【服务至上线以来 共执行多少次更新】
- com_select 【本次连接共执行多少次查询】
- com_delete 【本次连接共执行多少次删除】
- com_insert 【本次连接共执行多少次插入】
- com_update 【本次连接共执行多少次更新】
- last_query_cost 【上一次查询读取的数据页数量】
2. 定位慢SQL
2.1 慢SQL日志
- 查看慢SQL日志开启状态
查看慢SQL日志开启状态:show variables like ‘slow_query_log’;
开启慢SQL日志:set global slow_query_log = ON;
slow_query_log_file
为慢SQL日志存储位置(默认存储在mysql下data目录中,默认名称为hostname-slow.log)
- 慢SQL判定阀值
查看:show variables like ‘long_query_time’;
设置:set [global | session] long_query_time = 秒数;
tips: 除了long_query_time这个变量觉得SQL是否是慢SQL外,还有一个变量min_examined_row_limit,默认为0。
意思是若当前查询超过long_query_time的时间,且扫描了min_examined_row_limit条记录,才会被记录在日志当中。
补充:
可在mysql配置文件my.cnf
中配置
[mysqld]
long_query_time=1 #慢查询阀值为1秒
slow_query_log=ON #开启慢SQL查询日志记录
slow_query_log_file=/data/mysql/data/xx-slow.log #指定日志存储位置
log_output=FILE #日志输入为文件方式
3. 慢SQL日志分析工具
在Linux 命令行中执行mysqldumpslow
- -help查看每个参数含义
mysqldumpslow -s t -a -t 5 xx-slow.log
-s 使用t 执行时间进行排序显示
-a 不带该参数则打印出来的sql where条件数字会变成N 字符串变为S
-t 5 打印最慢的前5条sql
执行结果如下:
4. SQL分析工具:explain
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|
字段解释
① table
查询的表名
② id
每个Select关键字都对应一个唯一的ID
(非100%)
- id如果相同,可以认为是一组,从上往下顺序执行
- 在所有组中 id值越大,优先级越高,越先执行
- 关注点:id号每个号码,表示一趟独立的查询,一个sql的查询趟数越少越好
③ select_type
查询类型(请考虑优化器重构SQL情况 在执行完SQL后紧接着执行SHOW WARNINGS;
可查看优化器重写SQL)
- 除了union和子查询 其余都是SIMPLE类型
- 对于包含UNION或UNION ALL 或子查询的大查询来说,最左边的是PRIMARY,其余是UNION
- MYSQL选择使用临时表来完成UNION查询去重工作,针对临时表的类型是UNION RESULT
- 子查询与主查询不相关 类型是SUBQUERY 相关则是 DEPENDENT SUBQUERY(有依赖性的)因为嵌套关系 DEPENDENT SUBQUERY可能会执行多次
- 在包含UNION或UNION ALL的大查询中 如果各个子查询都依赖于外层查询的话,那么除了最左边的子查询是DEPENDENT SUBQUERY外,其余都是DEPENDENT UNION
- 对于派生表对应的查询类型是derived+id
- 当优化器优化子查询变为物化表(常量)进行连接查询时,子查询对应的类型是MATERIALIZED
④ partitions
代表分区表命中情况,非分区表则显示null 可忽略
⑤ type ★
执行查询的访问方法
完整访问方法:system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、all (顺序:性能由高到低排序)
- system:表中只有一条数据,且该表使用的存储引擎的统计数据是精确(非近似)的,如MyISAM、Memory(在Innodb中对应是ALL 因为他没有变量去存储结果)
- const:根据主键或唯一索引列与常量进行等值匹配时,是const。如select * from tb1 where id = 1;
- eq_ref:在连接查询时,被驱动表是通过主键或唯一二级索引列等值匹配的方式进行访问的(如果涉及联合索引,所有索引列都必须参与等值匹配)
- ref:当通过某个普通索引和常量进行等值匹配来查询表时,则访问方法可能是ref
- ref_or_null:同上,但出现了查询条件为该普通索引列为null 如 key = ‘a’ or key is null
- index_merge:表示索引合并查询 如 where key1 = ‘a’ or key2 = ‘a’ (key1和key2都建立了索引) 这只有使用or才行 and将遵循最左原则
- unique_subquery:针对在一些包含in子查询的查询语句中 如果查询优化器决定将in子查询转换为exists子查询 且子查询可以使用到主键进行等值匹配的话,那么访问方法就是unique_subquery
- range:如果使用索引来获取某些范围区间的记录 则可能使用到range访问方法
- index:当我们可以使用索引覆盖(key2、key3均包含在联合索引中,不需要回表查询),但需要扫描全部索引记录时:(包含联合索引key1、key2、key3时)查询语句为select key2 from tb1 where key3 = ‘a’,这种情况下的访问方法为index
- all:全表扫描
SQL性能优化目标:最少达到range级别,要求是ref级别,最好是const级别。(来源:Alibaba开发手册)
⑥ possible_keys和key
- possible_key列显示在查询语句中,涉及到的字段上存在索引,则该索引会被列出,但不一定会被使用到。
- key表示实际用到的索引
⑦ key_len ★
实际使用到的索引长度(即:字节数)
针对联合索引有一定意义上的参考价值,与自身相比,值越大越好
⑧ ref
当使用索引列进行等值查询时,与索引列进行等值匹配的对象信息(字段、函数等)
⑨ rows ★
预估的需要读取的记录条数 值越小越好
⑩ filtered
某个表经过搜索条件过滤后剩余记录条数的百分比
- 值越大越好,说明搜索出来的数据都是符合条件的
- 该字段主要针对联合查询中驱动表对应的执行计划记录的filtered值,它决定了被驱动表要执行的次数(即驱动表rows*filtered)
⑪ Extra
一些额外的信息 更准确的理解Mysql到底将如何执行给定的查询语句
以下列举一些比较重要的内容:
- using filesort : 文件排序;有些情况下,当我们使用order by时无法使用到索引,只能在内存中(数据量小)或磁盘中(数据量大)进行排序,MYSQL把这种排序方式称为文件排序(因为.ibd文件中不存在已经排好序的数据时会出现这种情况)
- using temporary:临时表;当查询时没使用到索引,MYSQL很可能会寻求通过建立内部的临时表辅助执行查询。建立和维护临时表需要很大成本,最好可以使用索引来替代掉临时表的创建。
5. 系统表分析
# 查询冗余索引
select * from sys.schema_redundant_indexes sri;
# 查看表索引
show INDEX from jrwms.wm_wv_header;
# 查询未使用过的索引
select * from sys.schema_unused_indexes sui ;
# 查询索引使用情况
select * from sys.schema_index_statistics sis where table_schema = 'jrwms';
# 查询表的访问量
select table_schema , table_name, sum(io_read_requests+io_write_requests) as io
from sys.schema_table_statistics sts group by table_schema ,table_name order by io desc;
# 查询表占用bufferpool的情况
select object_schema , object_name , allocated , `data`
from sys.innodb_buffer_stats_by_table ibsbt order by allocated limit 10;
# 查看全表扫描情况
select * from sys.schema_tables_with_full_table_scans stwfts ;
# 查看sql执行频率
select * from sys.statement_analysis sa order by exec_count desc;
# 查询使用了排序的SQL
select * from sys.statements_with_sorting sws limit 1;
# 查询使用了临时表或磁盘临时表的SQL
select * from sys.statement_analysis sa
where tmp_tables > 0 or tmp_disk_tables > 0 order by (tmp_tables + tmp_disk_tables) desc;
# 查看行锁
select * from sys.innodb_lock_waits ilw ;
风险提示:
通过sys库去查询时,MYSQL会消耗大量资源去收集信息,严重的可能会影响业务请求被阻塞,从而引发故障。建议生产环境下不要频繁查询这些系统表去来完成分析工作。
二、SQL性能调优
调优方向:
- 索引
- SQL
- 服务器:缓存、线程等(my.cnf)
- 分库分表:数据量大
1. 外连接和内连接的优化
优化器可决定谁是驱动表和谁是被驱动表,小结果集驱动大结果集。(度量单位:表行数 * 每行大小)
建议:在连接条件上将被驱动表的连接字段加上索引。
select * from tb1 left join tb2 where tb1.name = tb2.name;
原理:驱动表tb1全表查询,每循环到一条数据时,会根据该数据的name值去循环被驱动表tb2全表。
而对于内连接来说
select * from tb1 inner join tb2 where tb1.name = tb2.name;
如果tb2数据量大于tb1,则tb2作为驱动表,tb1作为被驱动表。
如果tb1.name上存在索引,则tb1会作为被驱动表,tb2作为驱动表。
小结:
- 整体效率:INLJ > BNLJ > SNLJ
- 小结果集驱动大结果集(其本质就是减少被驱动表的循环次数)
- 为被驱动表的过滤条件增加索引(减少被驱动表匹配的次数)
- 增大Join Buffer Size大小(一次缓存的数据越多,内层包的扫表次数就越少)
- 减少驱动表的不必要字段的查询(字段越少,Join Buffer Size缓存数据越多)
2. 子查询优化
尽量不使用NOT IN 或 NOT EXISTS,使用LEFT JOIN xx WHERE xx IS NULL
3. 排序优化
创建组合索引:
create index idx_age_classid_name on student(age,classid,name)
1. 使用索引
- 不限制,索引失效:
select SQL_NO_CACHE * from student order by age,classid limit 10;
对于上面的例子来说,如果没有limit,则mysql可能会使用filesort进行排序。因为如果数据量大的情况下,使用索引可能还需要回表查询,这样会导致开销比filesort还大,所以mysql会采用filesort。
- order by顺序错误,索引失效:
select SQL_NO_CACHE * from student order by classid, age limit 10;
对于这种sql来说不遵循最佳左前缀原则,索引失效。因为索引创建时,age顺序比classid靠前。
- order by方向反,索引失效:
select SQL_NO_CACHE * from student order by age ASC, classid DESC limit 10;
该例子,age正序排列,classid逆序排列。如果mysql使用索引,则还需要因为classid逆序排列而进行一次filesort,资源浪费。所以该例子索引失效。
- 数据量大小
select SQL_NO_CACHE * from student where age=45 order by classid;
该例子中,因为age过滤后的结果集数据量较小,则仅使用了age索引,并直接回表,没有再使用到classid了。
其他请查看索引失效的情况
在查询中,mysql自动选择最优方案,但随着数据量的变化,是否使用索引还是优化器决定。
当【范围条件】和【group by 或 order by】的字段出现二选一时,优先观察条件字段的过滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上,反之,亦然。
2. 优化Filesort
2.1 filesort算法:双路排序和单路排序
- 双路排序(慢):
在MYSQL 4.1之前使用的是双路排序。就是扫描两次磁盘,最终得到数据。第一次磁盘IO将行指针(指向行所在的磁盘地址)和order by列读取到内存Buffer中,对order by列进行排序,然后根据排好列的数据通过行指针找到对应数据第二次磁盘IO操作读取全部数据。 - 单路排序(快):
单路排序仅进行一次磁盘IO。从磁盘读取查询需要的所有列,按照order by列在buffer中对他们进行排序,然后输出排序后的列表,它的效率更快一些,避免第二次磁盘IO,并把随机IO变为了顺序IO,但它会使用更多的空间,因为它把每一行都保存在了内存中。
单路排序的问题:
虽然单路排序总体而言好过双路排序
但是单路需要更大的内存空间,假设需要排序的数据大于内存buffer的大小,就会导致每次只能取buffer容量大小的数据进行排序(创建tmp文件缓存排序结果,多路合并),排完再次读取buffer大小数据,导致多次IO
优化策略:
- 提高sort_buffer_size(1M~8M 默认1M)
不管使用两种算法之间的哪种算法,提高这个参数都会提高效率。但要根据系统的能力去提高。因为这个参数决定每个连接(mysql connection)所使用的内存大小,如果设置过大,在多个连接产生后可能会耗尽系统资源。
- 提高max_length_for_fort_data(1024字节~8192字节 默认1k)
读取数据最大限制。但是如果设置的太高,数据总容量超出sort_buffer_size的概览就增大。
如果需要返回的列的总长度大于max_length_for_sort_data,则使用双路算法,否则使用单路算法。
- order by时最好只查询需要的字段
当查询的字段大小总和小于max_length_for_sort_data,且排序字段不是text|blob类型,会使用单路算法,否则使用双路算法。
- 减少使用order by
order by、group by、distinct这些语句比较耗费CPU,数据库CPU资源极其宝贵。若允许,尽量放在程序端进行操作。
4. GROUP BY优化
- GROUP BY 使用索引原则几乎和ORDER BY一致,GROUP BY即时无过滤条件也可以直接使用索引。
- 先ORDER BY 再 GROUP BY遵循索引建的最佳左前缀原则
- 当无法使用索引列时,增加max_length_for_sort_data和sort_buffer_size大小
- where效率高于having
- 包含了order by、group by、distinct这些查询的语句,where条件过滤出来的结果集请保持在1000行以内,否则sql会很慢。
5. LIMIT 优化
select * from tb1 limit 200000,10;
针对以上SQL,有两种优化思路:主要是根据聚簇索引进行优化
- 优化思路1
select * from tb1, (select id from tb1 order by id limit 200000,10) as tb2 where tb1.id = tb2.id;
- 优化思路2
该方案针对主键自增的表
select * from tb1 where id > 200000 limit 10;
6. 其他查询策略
6.1 EXISTS和IN
小结果集驱动大结果集
:减少外层循环
select * from A where cc in (select cc from B)
select * from A where EXISTS (select cc from B where B.cc = A.cc)
① 当B小于A时 使用IN,因为实现的逻辑类似于
for i in B
for j in A
if i.cc = j.cc then ...
② 当A小于B时,使用EXISTS。因为实现的逻辑类似于
for i in A
for j in B
if i.cc = j.cc then ...
6.2 count(1)、count(*)、count(具体字段)
- 可以认为count(1)和count()的执行效率几乎一致, 以下以count()为例。
- 如果是MyISAM引擎,count(*)统计行数只需要O(1)的复杂度,这是因为每个MyISAM表中都有一个meta信息存储了row_count值,而一致性由表级锁决定。
如果是Innodb引擎,需要全表扫描,进行循环+计数的方式来完成统计,O(n)的复杂度。 - 在Innodb引擎中,如果采用Count(具体字段)来统计数据行数,尽量采用二级索引。因为主键采用聚簇索引,包含的信息多,加载进内存需要一定的时间和空间。而对于COUNT(1)和COUNT(*)来说,他们不需要寻找字段,只统计行数,mysql会自动选择一个占用空间最小的索引进行统计。
6.3 select *
尽量不使用select *的两点原因
- MYSQL在解析过程中,会通过查询数据字典将*按序转换成所有的列名,这个过程占用资源极大
- 无法使用覆盖索引
6.4 LIMIT 1
- 如果对于结果集你可以确定只有一条的时候,使用LIMIT 1会加快查询速度,因为mysql在找到一条记录时就返回。
- 如果对于一个已经建立了唯一索引的列来说,LIMIT 1意义不大。
6.5 多使用COMMIT
查看数据库是否开启自动COMMIT:show variables like ‘%autocommit%’;
COMMIT释放的资源:
- 回滚段上用于恢复数据的信息
- 被程序语句获得的锁
- redo / undo log buffer中的空间
- 管理上述3种资源所需要的内部花费也会减少
三、数据库调优
1. 优化表设计
- 遵循三范式原则
- 如果多表联查较多,采用反范式设置冗余字段提高查询效率
- 表字段的数据类型选择:关系到了查询效率的高低及存储空间的大小;字符长度尽可能设计得短一些。可以采用数值类型就不选择字符类型,字符长度确定选择char类型,长度不固定选择varchar类型。
2. 使用redis等缓存
3. 库级优化
4. 数据库结构优化
4.1 拆分表:冷热数据分离
思路是把1个包含了很多字段的表拆分成2个或多个相对较小的表。这样做的原因是,这些表中的某些字段操作频率很高(热数据),而另外一些字段使用频率很低(冷数据),冷热数据分离可以减少表的宽度,降低资源消耗。
表越宽(字段越多)把表装载进内存缓冲池所占用的内存也就越大,也会消耗很多的IO。
冷热数据分离可以① 减少IO,保证热数据的内存缓存命中率 ②更有效利用缓存,避免读入无用冷数据。
4.2 增加中间表
把经常需要联合查询的数据插入中间表中,然后将原来的联合查询改为中间表查询,提高查询效率。
4.3 增加冗余字段
4.4 优化数据类型
在数据量很大的时候,数据类型的定义很大程度上会影响到系统整体的执行效率。
-
对整数类型数据进行优化
对于非负型
数据,优先使用无符号整型UNSIGNED
来存储。因为无符号相对于有符号同样的字节数,存储数值范围更大。如tinyint有符号范围是-128-127,无符号的取值范围是0-255,多了一倍的存储空间。 -
在文本类型和整数类型中,要选择整数类型
跟文本类型数据相比,大整数往往占用更少的存储空间。 -
避免使用TEXT、BLOB
Mysql内存临时表
不支持TEXT、BLOB这样的大数据类型,如果查询中包含这样的数据,在排序等操作时,就不能使用内存临时表,必须使用磁盘临时表
进行。并且对于这种数据,Mysql还是要进行二次查询
,会使sql性能变差。
如果一定要使用,建议把BLOB或TEXT列分离到单独的扩展表中,查询时一定不要使用select * ,而只需要取出必要的列,不需要使用TEXT列的数据时不要对该列进行查询 -
避免使用ENUM类型
修改ENUM类型需要使用ALTER语句
ENUM类型的order by操作效率较低,需要额外操作,推荐使用TINYINT代替 -
使用TIMESTAMP存储时间
TIMESTAMP存储时间范围:1970-01-01 00:00:00 ~ 2033-01-19 03:14:07。TIMESTAMP采用4个字节,DATETIME使用8个字节。同时TIMESTAMP具有自动赋值及自动更新的特性。 -
使用DECIMAL代替FLOAT和DOUBLE存储高精度浮点数
尤其是财务相关的金融类数据,在计算时不会丢失精度。占用空间由定义的宽度决定,每4个字节可存储9位数字,并且小数点要占用一个字节。可用于存储比BIGINT更大的整数类型。
4.5 优化插入记录的速度
影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。
MyISAM表:
① 禁用索引
对于非空表,在大量插入数据前禁用索引,会因为维护索引数据而降低插入记录速度,待插入完毕后再开启索引。
对于空表来说则不需要禁用索引,因为MyISAM表是在导入数据之后才建立索引的。
禁用索引:ALTER TABLE table_name DISABLE KEYS;
开启索引:ALTER TABLE table_name ENABLE KEYS;
② 禁用唯一性检查
对于含有唯一性的列(如主键,唯一索引等),在插入数据时,Mysql会对插入的数据进行唯一性校验从而降低了插入数据的效率。当我们大批量插入数据时,可以禁用唯一性检查,待数据导入完毕后再开启。
禁用唯一性检查:SET UNIQUE_CHECKS=0;
开启唯一性检查:SET UNIQUE_CHECKS=1;
③ 使用LOAD DATA INFILE批量导入数据
LOAD DATA INFILE速度比INSERT快
Innodb表:
① 禁用唯一性检查
同上
② 禁用外键检查
因为Innodb支持外键,所以当插入数据涉及到外键时,mysql还会去对应的表中查找是否存在该条记录。
禁用外键检查:SET FOREIGN_KEY_CHECKS=0;
开启外键检查:SET FOREIGN_KEY_CHECKS=1;
③ 禁止自动提交
Innodb支持事务,所以在每次增删改查时都会commit一次进行刷盘,当大批量插入数据时会导致资源浪费,可在批量导入数据时关闭自动提交,待数据导入成功后再开启。
禁用自动提交:SET AUTOCOMMIT=0;
开启自动提交:SET AUTOCOMMIT=1;
4.6 大表优化
当一个表的数据量过大时,有以下几种调优方案:
- 限定查询范围:如查询日志表,默认只查询一个月内的数据。
- 读写分离 搭建集群+主从复制(使用Mycat等进行流量分发)
- 垂直拆分:将数据分成多块放在不同的服务器上,减少单个服务器的访问压力
- 水平拆分:将数据拆分成多个表但同时保持表结构一致,如将2017年数据放入一个表,2018年数据放入另一个表。这样做仅解决了单一表数据量过大的问题,并没有实质性作用,所以
水平拆分最好分库
。从而达到分布式目的。但水平拆分会带来分片事务难以解决
,跨节点JOIN性能差
,逻辑复杂等问题。所以尽量不要对数据进行分片。如果实在要分片尽量选择客户端分片架构,这样可以减少一次和中间件的网络IO。
数据库分片的两种常见方案:
- 客户端代理:分片逻辑在应用端,封装在jar包里,通过修改或封装JDBC来实现。当当网Sharing-JDBC、阿里TDDL都是这种方式。
- 中间件代理:在应用层和数据层中间加一个代理层,分片逻辑统一维护在中间件服务中。Mycat、360的Atlas及网易DDB都是这种架构实现。