Mysql优化学习笔记

前置问题

• 数据库连接超时问题？
• 数据库查询慢速问题？
• 数据库操作阻塞问题？

解决方案

1. SQL（索引）
2. 库表结构
3. 系统配置
4. 硬件优化

1 SQL（索引）优化

问题发现

Q：如何发现有问题的 SQL？

A：MySQL 慢查日志。使用 MySQL 慢查日志对有效率问题的 SQL 进行监控。

慢查日志

MySQL 命令：

# 查看慢查日志是否开启 show variables like 'slow_query_log'; # 开启慢查日志 set global slow_query_log=on; # 查看慢查日志存储位置 show variables like 'slow_query_log_file'; # 指定慢查日志存储位置 set global show_query_log_file='/home/mysql/data/mysql-slow.log'; # 查看记录未使用索引日志是否开启 show variables like 'log_queries_not_using_indexes'; # 开启记录未使用索引日志 set global log_queries_not_using_indexes=on; # 查看慢查时间 show variables like 'long_query_time'; # 指定慢查时间 set global long_query_time=1; -- 0.01s 

日志命令：

# 查看日志 tail -50 /home/mysql/data/mysql-slow.log 

日志信息：

// SQL 的发生时刻 # Time: 140712 12:12:12 // SQL 的主机信息 # User@Host:root[root] @localhost[] // SQL 的执行信息 # Query_time:0.0000493 Lock_time:0.000126 Rows_sent:3 Rows_esamined:3 // SQL 的执行时间 SET timestamp=1402389324;
// SQL 的执行内容 select * from store;

慢查日志分析工具：mysqldumpslow（MySQL）

mysqldumpslow 命令：

# 查看参数列表 mysqldumpslow -h # 分析慢查询日志中前三条比较慢的SQL mysqldumpslow -t 3 /home/mysql/data/mysql-slow.log | more # 输出分析结果 Count:1 Time:0.20s (0s) Lock=0.00s (0s) Rows=10.0 (s) root[root]@localhost select * from store 

慢查日志分析工具：pt-query-digest（PRECONA）

pt-query-digest 命令：

# 输出到文件 pt-query-digest slow-log > slow_log.report  # 输出到数据表 pt-query-digest slow.log -review \
	h=127.0.0.1,D=test,p=root,P=3306,u=root,t=query_review \
    --create-reviewtable \
    --review-history t=hostname_slow  # 查看参数列表 pt-query-digest --help  # 分析慢查询日志中前三条比较慢的SQL pt-query-digest /home/mysql/data/mysql-slow.log | more 

MySQL explain 命令分析 SQL 执行计划

MySQL explain 命令：

explain select * from store; explain select * from store \G; -- 纵向显示结果 

结果字段：

字段	值	描述
id	1
select_type	SIMPLE
table	store
type	ALL	重要字段，表示连接使用了何种类型。 从好到差：const, eq_reg, ref, range, index, ALL。
possible_keys	NULL	可能应用在该表的索引。空，没有可能的索引。
key	NULL	实际使用的索引。空，没有使用索引。
key_len	NULL	实际使用的索引长度。不损失精度下，越短越好。
ref	NULL	显示索引的哪一列被使用，建议常数。
rows	100	扫描行数，越少越好。
Extra	NULL	注意避免：Using filesort, Using tempoary。

优化细节

优化 count() 函数

• 【强制】不要使用 count(列名) 或 count(常量) 来替代 count(*)，count(*) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

  说明：count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行。

# 优化 SQL 示例 select count(release_year='2006' or null) as '2006年电影数量', count(release_year='2007' or null) as '2007年电影数量' from film;

count() 语法：

• count(*)：

  • 包括所有列，返回表的记录数。
  • 统计结果中，会统计值为 NULL 的行。

• count(1)/count(常量)：

  • 忽略所有列，返回表的记录数。
  • 统计结果中，会统计值为 NULL 的行。

• count(列名)：

  • 包括指定列，返回指定列的记录数。
  • 统计结果中，不会统计指定列值为 NULL 的行。

• count(distinct 列名1, 列名2, ...)：

  • 包括指定列，返回指定列不同值的记录数。
  • 统计结果中，不会统计指定列值为 NULL 的行。

count() 不用用法性能比较：

• 如果表仅一列：
  • count(*) 效率最优。
• 如果表有多列：
  • 没有主键：
    • count(1) 优于 count(*)。
  • 存在主键：
    • count(主键) 优于 count(1)。
    • count(1) 优于 count(非主键列)。

count() 特例：

select count('') from table; -- 返回表的记录数 select count(0) from table; -- 返回表的记录数 select count(null) from table; -- 返回 0 

优化 sum() 函数

• 【强制】当某一列的值全是 NULL 时，count(col) 的返回结果为 0，但 sum(col) 的返回结果为 NULL，因此使用 sum() 时需注意 NPE 问题。

  正例：可以使用如下方式来避免 sum() 的 NPE 问题：

  SELECT IFNULL(SUM(col), 0) FROM table;

优化 max() 函数

• 【推荐】利用覆盖索引来进行查询操作，避免回表。

  说明：如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。

  正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用 explain 的结果，extra 列会出现：using index。

# SQL 示例 select max(payment_data) from payment; # 优化方案：对 max() 函数参数建立索引 create index idx_payment_data on payment(payment_data); # 优化结果 explain select max(payment_data) from payment;
Extra: Select tables optimized away

优化子查询

通常情况下，需要把子查询优化为 join 查询，但在优化时要注意关联键是否有一对多的关系（distinct）。

# 优化 SQL 示例 select distinct t.id from t join t1 on t.id = t1.tid;

优化 where/group by 语句

将 where/group by 语句放入子查询中。

# 优化 SQL 示例 select actor.first_name, actor.last_name, c.cnt from sakila.film_actor inner join ( select actor_id, count(*) as cnt from sakila.film_actor group by actor_id
) as c using(actor_id);

优化 limit 语句

• 【推荐】利用延迟关联或者子查询优化超多分页场景。

  说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

  正例：先快速定位需要获取的 id 段，然后再关联：

  SELECT t1.* FROM 表1 as t1,
  	(SELECT id from 表1 WHERE 条件 LIMIT 100000,20 ) as t2 WHERE t1.id = t2.id;

# 优化 SQL 示例 # 1. 使用主键（索引列）进行 order by 操作； # 2. 记录上次返回的主键（索引列），在下次查询的时候使用主键（索引列）过滤。 select film_id, description from sakila.film where film_id > 55 and film_id <= 60 order by film_id limit 1, 5;

索引优化

优化普通索引

• 在 where 从句，group by 从句，order by 从句，on 从句中出现的列使用索引。

• 索引字段越小越好。

• 【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。

  说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

• 【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

  说明：即使双表 join 也要注意表索引、SQL 性能。

• 【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

  说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*) 的区分度来确定。

• 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

  说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

优化联合索引

• 联合索引中离散度大的列放到前面。

• 【推荐】建组合索引的时候，区分度最高的在最左边。

  正例：如果 where a=? and b=?，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。

  说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引 idx_d_c。

删除冗余索引

冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引。

冗余索引分析 SQL：

# 分析冗余索引 SQL USE INFORMATION_SCHEMA; SELECT a.TABLE_SCHEMA,
       a.TABLE_NAME,
       a.COLUMN_NAME,
       a.INDEX_NAME AS 'INDEX1',
       b.INDEX_NAME AS 'INDEX2' 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; # 分析库表结构 SQL SHOW CREATE TABLE employees.dept_emp; ALTER TABLE 'employees'.'dept_emp' DROP INDEX 'emp_no';

冗余索引分析工具：pt-duplicate-key-checker（PRECONA）

pt-duplicate-key-checker 命令：

pt-duplicate-key-checker \ -uroot -p '123456' -h 127.0.0.1 

删除不用索引

目前 MySQL 中还没有记录索引的使用情况，只能通过慢查日志配合 pt-index-usage 工具来进行索引使用情况分析。但在 PerconMySQL 和 MariaDB 中可通过 INDEX_STATISTICS 表来查看哪些索引未使用。

不用索引分析工具：pt-index-usage（PRECONA）

pt-index-usage \
	-uroot -p '123456' mysql-slow.log

2 库表结构优化

选择合适的数据类型

1. 使用可以存下你的数据的最小的数据类型。
2. 使用简单的数据类型。int 要比 varchar 类型在 MySQL 处理上更简单。
3. 尽可能的使用 not null 定义字段。
4. 尽量少用 text 类型，非用不可时最好考虑分表。

使用 int 存储日期时间

优点：字段长度比 datetime 小。

缺点：使用不方便，要进行函数转换。

限制：只能存储到 2038-1-19 11:14:07，即 2^32 = 2147483648。

# 使用 int 存储日期时间示例 CREATE TABLE test(id INT AUTO_INCREMENT NOT NULL,
                  timestr INT,
                  PRIMARY KEY(id)); # 使用 UNIX_TIMESTAMP() 函数获取时间戳 INSERT INTO test(timestr) VALUES(UNIX_TIMESTAMP('2020-08-13 18:00:00')); # 使用 FROM_UNIXTIME()  函数获取时间串 SELECT FROM_UNIXTIME(timestr)FROM test;

使用 bigint 存储 IP 地址

# 使用 bigint 存储 IP 地址示例 CREATE TABLE sessions(id INT AUTO_INCREMENT NOT NULL,
                      ipaddress BIGINT,
                      PRIMARY KEY(id)); # 使用 INET_ATON() 函数获取 IP 戳 INSERT INTO sessions(ipaddress) VALUES(INET_ATON(192.168.0.1)); # 使用 INET_NTOA() 函数获取 IP 串 SELECT INET_NTOA(ipaddress) FROM sessions;

表的范式化和反范式化

范式化

范式化是指数据库设计的规范，目前说到范式化一般是指第三设计范式。

第三范式要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖。

不符合第三范式要求的表存在问题：数据冗余、数据的写操作异常（插入、更新、删除）。

反范式化

反范式化是指为了查询效率的考虑把原本符合第三范式的表适当的增加冗余，以达到优化查询的目的。

反范式化是一种以空间来换取时间的操作。

表的垂直拆分和水平拆分

垂直拆分

垂直拆分：解决表的宽度问题。把原来一个有很多列的表拆分成多个表。

常用的垂直拆分原则：

• 把不常用的字段单独存放到一个表中。
• 把大字段独立存放到一个表中。
• 把经常一起使用的字段放到一起。

水平拆分

水平拆分：解决表的数据量过大的问题。每一个表的结构完全一致。

常用的水平拆分方法：

1. 对主键 id 进行哈希运算，如果要拆分成 5 个表则使用 mod(id, 5) 去除 0-4 个值。
2. 针对不同的哈希 ID 把数据存到不同的表中。

• 【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

  说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

3 系统配置优化

MySQL 配置文件

MySQL 可以通过启动时指定配置参数和使用配置文件两种方法进行配置。

Linux 系统配置文件位于/etc/my.cnf 或 /etc/mysql/my.cnf，Windows 系统配置文件可位于 C:/windows/my.ini 。

MySQL 查找配置文件命令：

/usr/sbin/mysqld --verbose --help | grep -A 1 'Default options' # 注意：如果存在多个位置的配置文件，则后面会覆盖前面 

MySQL 配置文件常用参数：

innodb_buffer_pool_size：

关键参数，用于配置 Innodb 的缓冲池大小，如果数据库中只有 Innodb 表，则推荐配置量为总内存的 75%。

SELECT ENGINE, ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB" FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA not in ("information_schema","performance_schema") GROUP BY ENGINE;

# innodb_buffer_pool_size 最小值 innodb_buffer_pool_size >= total MB 

innodb_buffer_pool_instances：

MySQL 5.5 新增参数，可以控制缓冲池的个数，默认情况下只有一个缓冲池。

innodb_log_buffer_size：

Innodb log 缓冲的大小，由于日志最长每秒钟就会刷新所以一般不用太大。

innodb_flush_log_at_trx_commit：

关键参数，对 Innodb 的 IO 效率影响很大。

默认为 1，可取 0、1、2 三个值，建议为 2，但如果数据安全性要求比较高则使用默认值 1。

innodb_read_io_threads/innodb_write_io_threads：

以上两个参数决定了 Innodb 读写的 IO 进程数，默认为 4。

innodb_file_per_table：

关键参数，控制 Innodb 每一个表使用独立的表空间，默认为 OFF，也就是所有表都会建立在共享表空间中，建议为 ON。

innodb_stats_on_metadata：

决定了 MySQL 在什么情况下会刷新 Innodb 表的统计信息。

MySQL 配置文件工具网址：（PRECONA）

https://www.percona.com/software/database-tools/percona-toolkit

4 硬件优化

CPU 选择

• MySQL 有一些工作只能使用到单核 CPU。如 Replicate, SQL 执行等。
• MySQL 对 CPU 核数的支持并不是越多越快。MySQL 5.5 使用的服务器不要超过 32 核。

磁盘 IO 优化

常用 RAID 级别简介：

• RAID0：也称条带，就是把多个磁盘链接成一个硬盘使用，这个级别 IO 最好。
• RAID1：也称镜像，要求至少有两个磁盘，每组磁盘存储的数据相同。
• RAID5：也是把多个硬盘合并成一个逻辑盘使用，数据读写时会建立奇偶校验信息，分别存储在不同磁盘上。
• RAID1+0：就是 RAID1 和 RAID0 的结合，同时具备两个级别的优缺点。一般建议数据库使用这个级别。

思考：SNA 和 NAT 是否适合数据库？

1. 常用于高可用解决方案。

2. 顺序读写效率很高，但是随机读写不如人意。

3. 数据库随机读写比率很高。