MySQL调优

MySQL数据库优化

概览

避免出现的问题

• 由于数据库连接timeout产生的5xx页面
• 由于慢查询导致的页面无法加载问题
• 由于阻塞造成数据无法提交

优化

成本逐次降低，效果逐次升高：硬件 > 系统配置 > 数据库表结构 > SQL及索引

MySQL官方提供的数据库测试用例-sakila

下载地址：https://dev.mysql.com/doc/index-other.html

文档：https://dev.mysql.com/doc/sakila/en/sakila-installation.html

SQL语句优化

查看log相关配置参数

USE sakila;

SHOW TABLES;

# 是否开启慢查询日志
SHOW VARIABLES LIKE 'slow_query_log';


SHOW VARIABLES LIKE '%log%';
# log_queries_not_using_indexes, OFF

# 查看慢查询日志的保存位置
SHOW VARIABLES LIKE 'slow%';
# slow_query_log_file, /var/lib/mysql/server4test1-slow.log


# 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes=on;

# 慢查询时间
SHOW VARIABLES LIKE 'LONG_QUERY_TIME';
# long_query_time, 10.000000
# 演示时可以设置为0来触发日志记录，设置后重连数据库进行刷新
SET GLOBAL LONG_QUERY_TIME=0;

# 开启查询日志
SET GLOBAL slow_query_log=ON;

SELECT * FROM staff;

注意：

# SET GLOBAL是在内存里操作，在服务重启后会失效。PERSIST会持久到配置文件。
SET PERSIST LONG_QUERY_TIME=1;

# 清空持久化变量
RESET PERSIST;

慢日志分析

原生工具mysqldumpslow

分析慢查询日志文件：

# 分析前三条慢查询日志
mysqldumpslow -t 3 server4taest1-slow.log 

结果：（包含执行次数、执行时间、锁定时间、发送的行数、执行者、sql语句）

Reading mysql slow query log from server4test1-slow.log
Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=2.0 (4), root[root]@[192.168.1.100]
SELECT * FROM staff
LIMIT N, N

Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[192.168.1.100]
SET GLOBAL slow_query_log=ON

Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=3.0 (6), root[root]@[192.168.1.100]
SHOW INDEX FROM sakila.staff

分析工具（pt-query-digest）

wget percona.com/get/percona-toolkit.tar.gz
# 解压后若不在当前路径下使用，请自行配置环境变量/etc/profile
tar -zxvf percona-toolkit.tar.gz -C /opt/module/
yum -y install perl-Digest-MD5

分析MySQL输出的慢日志：

pt-query-digest --report server4test1-slow.log > /home/report.txt

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

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

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

2. IO大的SQL

   pt-query-digest的Rows examine数据

3. 未命中索引的SQL

   pt-query-digest中Rows sent和Rows examine两项数据的对比

利用explain查询SQL的执行计划

EXPLAIN SELECT customer_id, first_name, last_name FROM customer;

结果

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

1, SIMPLE, customer, , ALL, , , , , 599, 100.00,

• table: 表。
• type: 重要，显示使用了何种类型，从最好到最差依次是const（主键、唯一索引）、eq_reg（有一个索引，范围查找）、ref（join）、range（索引的范围）、index（索引的扫描）、ALL（表的扫描）。
• possible_keys: 显示可能用在这张表中的索引。如果为空，没有可能得索引。
• key: 实际使用的所有。如果没空，则没有使用索引。
• key_len: 索引长度。在不损失精确度的情况下，长度越短越好。
• ref: 显示索引的哪一列被使用了，如果可能得话，是一个常数。
• rows: MySQL认为必须检查的用来返回请求数据的行数（表扫描的行数）。
• Extra: 出现Using filesort、Using temporary（对不同的列集进行ORDER BY上，而不是GROUP BY上） 都需要进行优化

示例

max的优化

原始数据：

EXPLAIN SELECT max(payment_date) FROM payment;

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

1, SIMPLE, payment, , ALL, , , , , 16086, 100.00,

**添加索引：**payment_date

ALTER TABLE payment ADD INDEX index_payment_date(payment_date);

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

1, SIMPLE, , , , , , , , , , Select tables optimized away

count优化

普通查询：

EXPLAIN SELECT COUNT(1) FROM film WHERE release_year = '2006';

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

1, SIMPLE, film, , ALL, , , , , 1000, 10.00, Using where

优化查询：

EXPLAIN SELECT COUNT(IF(release_year = '2006', TRUE, NULL)) FROM film;

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

1, SIMPLE, film, , ALL, , , , , 1000, 100.00,

COUNT(*)和COUNT(1)都包含空值、COUNT(id)不包含空值

子查询优化

IN查询转化为JOIN查询，由于1对多和多对多等关系的存在，会出现重复数据，用DISTINCT去重。IN和JOIN哪个性能好要视情况而定。

CREATE TABLE t1
(
	str varchar(11)
);

CREATE TABLE t2
(
	str varchar(11)
);

insert into t1(str) values(1), (2);
insert into t2(str) values(1), (1);

# IN查询
SELECT str FROM t1 WHERE t1.str IN (SELECT str FROM t2);
# JOIN查询
SELECT t1.str FROM t1 JOIN t2 ON t1.str = t2.str;
# DISTINCT+JOIN查询 = IN查询
SELECT DISTINCT t1.str FROM t1 JOIN t2 ON t1.str = t2.str;

本示例中的IN：

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

1, SIMPLE, t2, , index, index_str, index_str, 47, , 2, 50.00, Using where; Using index; LooseScan

1, SIMPLE, t1, , ref, index_str, index_str, 47, study.t2.str, 1, 100.00, Using index

本示例中的DISTINCT+JOIN：

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

1, SIMPLE, t2, , index, index_str, index_str, 47, , 2, 100.00, Using where; Using index; Using temporary

1, SIMPLE, t1, , ref, index_str, index_str, 47, study.t2.str, 1, 100.00, Using index

分析：本例中DISTINCT+JOIN这种查询出现了 Using temporary的情况，所以性能反而比IN查询差。

GROUP BY优化

查询有电影演员映射表（film_actor）和演员表（actor）都有记录的演员信息 — 每个演员参演影片的数量：

EXPLAIN SELECT a.actor_id, a.first_name, a.last_name, COUNT(1)
FROM film_actor fa
INNER JOIN actor a
ON fa.actor_id = a.actor_id
GROUP BY fa.actor_id;

结果：

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

1, SIMPLE, a, , ALL, PRIMARY, , , , 200, 100.00, Using temporary

1, SIMPLE, fa, , ref, PRIMARY,idx_fk_film_id, PRIMARY, 2, sakila.a.actor_id, 27, 100.00, Using index

分析：出现了使用临时表（Using temporary）的情况，性能差。

优化查询：

EXPLAIN SELECT a.actor_id, a.first_name, a.last_name, c.totalCount
FROM actor a
INNER JOIN 
	(SELECT COUNT(1) AS totalCount, actor_id
	FROM film_actor
	GROUP BY actor_id) c
ON a.actor_id = c.actor_id;

结果：

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

1, PRIMARY, a, , ALL, PRIMARY, , , , 200, 100.00,

1, PRIMARY, , , ref, <auto_key0>, <auto_key0>, 2, sakila.a.actor_id, 27, 100.00,

2, DERIVED, film_actor, , index, PRIMARY,idx_fk_film_id, PRIMARY, 4, , 5462, 100.00, Using index

分析：将GROUP BY语句放在JOIN的字表里，避免了创建临时表的操作，性能提升。

感悟：两表关联后如果没有DISTINCT、ORDER BY、GROUP BY等操作，既不会出现Using temporary、Using filesort等。所以尽可能（复杂查询通常没办法实现这样的优化）把DISTINCT、ORDER BY、GROUP BY操作放在关联的子表里面优先完成，有助于提高查询性能。

优化limit查询

原始查询：查询电影信息

EXPLAIN SELECT film_id, title, description FROM film ORDER BY title LIMIT 50, 5;

结果：

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

1, SIMPLE, film, , ALL, , , , , 1000, 100.00, Using filesort

分析：用了表扫描（ ALL）和文件排序（Using filesort），如果访问量较大的情况下回造成大量IO问题，性能差。

• 优化步骤1-使用索引列或主键进行ORDER BY操作

EXPLAIN SELECT film_id, title, description FROM film ORDER BY film_id LIMIT 50, 5;

结果：

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

1, SIMPLE, film, , index, , PRIMARY, 2, , 55, 100.00,

分析：本次使用索引进行扫描，总的扫描行数为55

• 优化步骤2-记录上次返回的主键，在下次查询时使用主键过滤（数据结构特殊，需事先知道film_id为连续增长且有序，该情况比较理想）

EXPLAIN SELECT film_id, title, description FROM film WHERE film_id > 50 AND film_id <= 55;

结果：

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

1, SIMPLE, film, , range, PRIMARY, PRIMARY, 2, , 5, 100.00, Using where

分析：使用索引，并且扫描行数为5。

索引优化

如何选择合适的列建立索引

1. 在WHERE、GROUP BY、ORDER BY、ON中出现的列
2. 索引字段越小越好
3. 离散度大的列放到联合索引的前面

建立联合索引

查询内容

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

判断索引的离散度，唯一值越多的，离散度越高。

SELECT COUNT(DISTINCT customer_id), COUNT(DISTINCT staff_id) FROM payment; 

结果：

COUNT(DISTINCT customer_id), COUNT(DISTINCT staff_id)

599, 2

分析：由于customer_id的离散度大，所以建立联合索引index(customer_id, staff_id)。

重复及冗余的索引

重复索引是指相同的列以相同的顺序建立的同类型索引，例如表中的primary key 和 id列上的唯一索引就是重复索引：

CREATE TABLE test (
	id INT NOT NULL PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    title VARCHAR(50) NOT NULL,
    unique(id)
) ENGINE=INNODB;

重复索引分析工具 pt-duplicate-key-checker

安装perl-DBD-MySQL

yum install perl-DBD-MySQL

修改认证方式：MySQL8默认为caching_sha2_password，需要修改为mysql_native_password。

vim /etc/my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password

修改已有root用户的认证方式：

SELECT host,user,plugin FROM mysql.user;

UPDATE mysql.user SET plugin='mysql_native_password' WHERE user='root';
UPDATE mysql.user SET plugin='mysql_native_password' WHERE user like 'mysql.%';


# 修改后：
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | mysql_native_password |
| localhost | mysql.infoschema | mysql_native_password |
| localhost | mysql.session    | mysql_native_password |
| localhost | mysql.sys        | mysql_native_password |
+-----------+------------------+-----------------------+

修改认证方式后，原有密码失效，需要进行root密码重置：

1. 修改配置文件并重启服务

vim /etc/my.cnf
# 在my.cnf中最后一行添加 skip-grant-tables
skip-grant-tables
# 重启服务
systemctl restart mysqld.service

2. 进入数据库清除原始root密码的加密记录

# password直接回车
mysql -u root -p

# 查看密码加密字段
SELECT host, user, authentication_string, plugin FROM mysql.user;

# 清除记录
UPDATE mysql.user SET authentication_string='' WHERE user='root';
UPDATE mysql.user SET authentication_string='' WHERE user like 'mysql.%';

SELECT host, user, authentication_string, plugin FROM mysql.user;

3. 修改配置文件，再次重启服务，然后进入数据库设置密码

vim /etc/my.cnf
删除 skip-grant-tables

# 重启服务
systemctl restart mysqld.service

# password直接回车
mysql -u root -p

# 设置root密码 我的host项已经修改为%了。如果没有修改，则使用：ALTER user 'root'@'localhost' IDENTIFIED BY '123456'

grant system_user on *.* to 'root';
ALTER user 'root'@'%' IDENTIFIED BY '123456';
#  WITH mysql_native_password  这个可以根据自己需要的认证方式修改（caching_sha2_password、mysql_native_password  两种）
ALTER user 'mysql.infoschema'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
ALTER user 'mysql.session'@'localhost' IDENTIFIED  WITH mysql_native_password BY '123456';
ALTER user 'mysql.sys'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';  

# 完成修改，重启服务。
systemctl restart mysqld.service

使用案例

# sakila中没有重复的冲突的索引，所以未分析出结果。
# pt-duplicate-key-checker -uroot -p123456 -h127.0.0.1 -P3306 -dsakila

# study中创建t3
# 表结构如下：
CREATE TABLE t3 (
	id INT(11) PRIMARY KEY NOT NULL,
    UNIQUE(id)
);

# 用study数据库中的表做测试
pt-duplicate-key-checker -uroot -p123456 -h127.0.0.1 -P3306 -dstudy

########################################################################

study.t3

########################################################################

Uniqueness of index_id ignored because PRIMARY is a duplicate constraint

index_id is a duplicate of PRIMARY

Key definitions:

UNIQUE KEY index_id (id)

PRIMARY KEY (id),

Column types:

id int(11) not null

To remove this duplicate index, execute:

ALTER TABLE study.t3 DROP INDEX index_id;

########################################################################

Summary of indexes

########################################################################

Size Duplicate Indexes 8

Total Duplicate Indexes 1

Total Indexes 10

未使用的索引 pt-index-usage

分析慢日志/var/lib/mysql/server4test1-slow.log

# 分析速度有可能较慢
pt-index-usage -uroot -p'123456' /var/lib/mysql/server4test1-slow.log > /home/report.txt
pt-index-usage --host localhost -uroot -p'123456' --database sakila /var/lib/mysql/ucloundserver-slow.log.ba
ckup > /home/report1.txt

结果：

ALTER TABLE mysql.db DROP KEY User; – type:non-unique

ALTER TABLE mysql.tables_priv DROP KEY Grantor; – type:non-unique

ALTER TABLE sakila.actor DROP KEY idx_actor_last_name; – type:non-unique

ALTER TABLE sakila.customer DROP KEY idx_fk_address_id, DROP KEY idx_fk_store_id, DROP KEY idx_last_name; – t
ype:non-unique

ALTER TABLE sakila.film DROP KEY idx_fk_original_language_id; – type:non-unique

ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id; – type:non-unique

ALTER TABLE sakila.payment DROP KEY fk_payment_rental, DROP KEY index_payment_date; – type:non-unique

ALTER TABLE sakila.staff DROP KEY idx_fk_address_id, DROP KEY idx_fk_store_id; – type:non-unique

数据库优化

选择合适的数据类型

1. 选择可以存下数据的最小数据类型；
2. 使用简单的数据类型，int要比varchar类型在MySQL中的处理简单；
3. 尽可能的使用NOT NULL定义字段；
4. 尽量少使用text类型，非用不可时最好考虑分表；

表的范式化和反范式化

范式化是指数据库设计的规范，目前说道范式化一般是指数据库设计的第三范式，也就是要求数据库中不存在非关键字段对任意候选关键字段的传递函数依赖。

• 范式化

商品名称	价格	容量	有效期	分类	分类描述
可乐	3.00	250ml	2014.06	饮料	碳酸饮料
北冰洋	3.00	250ml	2014.07	饮料	碳酸饮料

存在以下传递函数的依赖关系：

（商品名称）> （分类）> （分类描述）

也就是存在非关键字段 "分类描述"对关键字段”商品名称“的传递函数依赖。

范式化，把表进行拆分。

• 反范式化：利用空间换取时间

实际开发中，业务复杂，为了追求查询的效率，往往对表的设计采取“反范式化”，把重复数据放在一张表中。比如user_id、user_name、modify_id,、modify_name等信息通常也会成对出现在表中，提高SQL效率，避免进行关联查询。

数据库垂直和水平拆分

垂直拆分（解决表的宽度问题）：把原有表中的字段放入两张表中。

水平拆分（解决表的数据量问题）：mod(customer_id, 10)取0~9这10个值，对应10张表，进行分表查询。

水平拆分挑战：

1.跨分区表进行数据查询

2.统计及后台报表操作

解决方法：前后台查询业务进行拆分。前台为了速度，使用分表操作；后台为了统计，需要将数据汇总到一张表中。

系统配置优化

操作系统的优化

# linux系统对于目录的打开限制
ulimit -a 

关闭iptables, selinux等防火墙软件，使用硬件防火墙，而不是软件防火墙来消耗性能。

文件系统类型。

MySQL的配置参数优化

innodb_buffer_pool_size

重要参数，用户配置innodb缓冲池，如果数据库中只用 Innodb表，则推荐配置量为总内存的75%。

统计数据库容量

# 引擎的容量
SELECT 
	ENGINE,
	SUM(TABLE_ROWS) AS '记录数',
    SUM(TRUNCATE(DATA_LENGTH/1024/1024, 2)) as '数据容量(MB)',
	SUM(TRUNCATE(INDEX_LENGTH/1024/1024, 2)) as '索引容量(MB)'
FROM information_schema.TABLES
GROUP BY ENGINE;

# schema的容量
SELECT 
	TABLE_SCHEMA AS '数据库', 
	SUM(TABLE_ROWS) AS '记录数',
    SUM(TRUNCATE(DATA_LENGTH/1024/1024, 2)) as '数据容量(MB)',
	SUM(TRUNCATE(INDEX_LENGTH/1024/1024, 2)) as '索引容量(MB)'
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
ORDER BY SUM(DATA_LENGTH) desc, sum(INDEX_LENGTH) desc;

# 每张表的容量
SELECT 
	TABLE_SCHEMA AS '数据库', 
    TABLE_NAME AS '表名',
	SUM(TABLE_ROWS) AS '记录数',
    SUM(TRUNCATE(DATA_LENGTH/1024/1024, 2)) as '数据容量(MB)',
	SUM(TRUNCATE(INDEX_LENGTH/1024/1024, 2)) as '索引容量(MB)'
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA, TABLE_NAME
ORDER BY SUM(DATA_LENGTH) desc, sum(INDEX_LENGTH) desc;

innodb_buffer_pool_instances

控制缓冲池的个数，默认情况下只有一个缓冲池。

innodb_log_buffer_size

innodb log 缓冲的大小，由于日志最长每秒钟就会刷新，所以该缓冲区只需要保存刷新时间内（比如1秒）的日志内容，一般不用太大。

innodb_flush_log_at_trx_commit

关键参数，对innodb的IO效率影响很大。默认值1，可以取0,1,2三个值。

innodb_read_io_threads

innodb_write_io_threads

默认情况下读写的IO进程数，默认为4。

innodb_file_per_table

关键参数，是否使用独立的表空间，默认ON，每张表都有一个.ibd文件。

SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';

[root@server4test sakila]# pwd
/var/lib/mysql/sakila
[root@server4test sakila]# ls
actor.ibd          film_text.ibd                                      fts_000000000000042f_config.ibd
address.ibd        fts_000000000000042f_00000000000000b2_index_1.ibd  fts_000000000000042f_deleted_cache.ibd
category.ibd       fts_000000000000042f_00000000000000b2_index_2.ibd  fts_000000000000042f_deleted.ibd
city.ibd           fts_000000000000042f_00000000000000b2_index_3.ibd  inventory.ibd
country.ibd        fts_000000000000042f_00000000000000b2_index_4.ibd  language.ibd
customer.ibd       fts_000000000000042f_00000000000000b2_index_5.ibd  payment.ibd
film_actor.ibd     fts_000000000000042f_00000000000000b2_index_6.ibd  rental.ibd
film_category.ibd  fts_000000000000042f_being_deleted_cache.ibd       staff.ibd
film.ibd           fts_000000000000042f_being_deleted.ibd             store.ibd

innodb_stats_on_metadata

决定了MySQL在什么情况下会刷新innodb表的统计信息（比如默认查询information_schema、show create table的时候都会刷新统计信息）。应该设置为OFF，人为确定刷新时间。

innodb_flush_log_at_trx_commit

控制redo log写入磁盘的过程：

0：事务提交时，不将redo log刷入到磁盘，而是依靠InnoDB主线程每秒执行一次，刷新到磁盘。服务器宕机时，会丢失一部分事务数据。

1：事务提交时，将redo log刷入到磁盘，并且立即执行刷新fsync()。因为操作系统“延迟写”的特性，redo log刷入操作只是将内容写入到了操作系统的写缓存区中，只有执行fsync()同步操作，才能保证数据一定持久化到了磁盘中。

2：事务提价时，将redo log刷入到磁盘，但不会立即执行刷新fsync()。此时redo log的数据只是刷入到了操作系统的写缓冲区，并不一定持久化到磁盘中。所以该配置环境下，服务器宕机，可能会丢失一部分事务数据。

分析：参数0和2能保证InnoDB有更高的性能，参数1更能保证事务的持久性。

sync_binlog

控制bin log写入磁盘的过程：

0：事务提交后，将bin log刷入到磁盘，但不立即执行刷新操作fsync()，此时binlog的数据只是刷入到了操作系统的写缓冲区，并不一定持久化到磁盘中。所以该配置环境下，服务器宕机，可能会丢失一部分二进制日志。

1：事务提交后，将bin log刷入到磁盘，并立即执行刷新操作fsync()。

n：在执行n次事务提交后，执行一次fsync()操作，进行数据持久化。

分析：参数1能够保证数据的安全，参数0和n能够保证更高的性能。

bin log是server层面的，用于数据库拷贝以及固定时间点的数据恢复。

redo log是engine层面的，用于数据库宕机后数据库的恢复。

第三方工具 percona configuration wizard

服务器硬件优化

单核更快的CPU，MySQL5.5不超过32核

DISK IO优化：使用磁盘阵列 (RAID1+0)