诗与远方

Mysql性能优化笔记

数据库优化与检查是一项比较艰难的任务,必须借助于一些工具,而且往往问题比较隐蔽,多是在部署在生产环境一段时间之后,因为数据库的性能瓶颈,导致一系列问题的产生。

本文整理自慕课网教程《mysql数据库性能优化》

常用参数

  • 1)查看mysql是否开启慢查询日志

    1
    2
    show variables like 'slow_query_log';
    set global slow_query_log=on; //开启慢查日志
  • 2)设置没有索引的记录到慢查询日志

    1
    set global log_queries_not_using_indexes=on;
  • 3)查看超过多长时间的sql进行记录到慢查询日志

    1
    2
    show variables like 'long_query_time';
    set global long_query_time=1;

慢查日志分析工具

mysqldumpslow

使用mysqldumpslow -h来查看帮助信息
使用mysqldumpslow -t 3 /home/mysql/data/mysql-slow.log | more来对日志进行分析。

pt-query-digest

输出到文件:

1
pt-query-digest show.log > slow_log.report

如何通过慢查日志发现有问题的SQL?

1. 查询次数多且每次查询占用时间长的SQL

通常为pt-query-digest分析的前几个查询

2. IO大的SQL,数据库的主要瓶颈就在于IO

注意pt-query-digest分析中的Rows examine项。扫描行数多,占用io大

3. 未命中索引的sql

注意pt-query-digest分析中Rows examine(扫描行数)和Rows send(发送行数)的对比
如果这个比值比较大,说明索引命中率不高

怎么优化有问题的sql

1. 使用explain查询SQL的执行计划

1
explain select * from user;
  • table:表名;
  • type:连接的类型,const、eq_reg、ref、range、index和ALL
    • const:主键、索引;eq_reg:主键、索引的范围查找;ref:连接的查找(join),range:索引的范围查找;index:索引的扫描;All:表扫描
  • possible_keys:可能用到的索引;
  • key:实际使用的索引;
  • key_len:索引的长度,越短越好;因为mysql每次的读取都是以页为单位,一页中的索引越大,查询的效率就越高
  • ref:索引的哪一列被使用了,常数较好;
  • rows:mysql认为必须检查的用来返回请求数据的行数;
  • extra:using filesort、using temporary(常出现在使用order by时)时需要优化,因为都是用了外部文件或者临时表的存储

2. 选择合适的索引列

  • 在where,group by,order by,on从句中出现的列

  • 索引字段越小越好(因为数据库的存储单位是页,一页中能存下的数据越多越好 )
    索引字段越小越好,是因为数据库的存储是以页为单位,一页数据存储越大,获取的数据也越多,对我们io的效率也更好

  • 离散度大得列放在联合索引前面

1
select count(distinct customer_id), count(distinct staff_id) from payment;

查找字段的唯一值(不同数的个数)来确定count(distinct customer_id)离散度的大小来确定索引建立在哪一个列上.
离散度,我的理解就是唯一性了,比如主键,绝对是离散度最大的,而一些用来标识状态标识的列,基本只有几个可选项,离散度就很小
index(col1,col2) 在建立联合索引时,离散度高的列放到前面

3. 冗余索引检查

索引存在的目的是为了加快查询的效率,不过不是索引越多越好,建立索引要适当才好。

过多的索引会增加数据库判断使用什么索引来查询的开销,所以,有时候也会出现以去掉重复或者无效的索引为优化手段的优化方式。

冗余索引是指多个索引的前缀列相同,或是在联合索引中包含了主键的索引,下面这个例子中key(name, id)就是一个冗余索引

1
create table test(id int not null primay key,name varchar(10) not null,title varchar(50) not null, key(name,id))engine=innodb;

主键索引本来就是唯一索引

索引检查语句

1
2
3
4
5
6
7
8
9
10
11
12
use information_schema;
SELECT a.TABLE_SCHEMA AS 'db'
,a.TABLE_NAME AS 'table'
,a.INDEX_NAME AS 'index1'
,b.INDEX_NAME AS 'index2'
,a.COLUMN_NAME AS 're_col'
FROM STATISTICS a
JOIN STATISTICS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE a.SEQ_IN_INDEX = 1 AND a.INDEX_NAME <> b.INDEX_NAME

索引检查工具 pt-duplicate-key-checker

查询冗余索引工具 pt-duplicate-key-checker -uroot -p '123456' -h 127.0.0.1

4. 字段类型优化

  • 可以使用int类型存储时间类型:
    int类型转时间类型函数:from_unixtime(),时间类型转int类型:unix_timestemp()

  • 可以使用bigint存储ip地址
    bigint类型转ip函数:inet_aton(),ip转bigint类型:inet_ntoa()