数据库开发标题-mysql性能优化

性能测试

1. 安装 sysbench

sysbench 是一个基于 LuaJIT(Lua 编程语言的即时编译器(JIT))的可脚本多线程基准测试工具，可用于测试系统 CPU、内存、文件 IO、数据库等等性能；

Ubuntu/Debian

sudo apt-get install sysbench

CentOS/RHEL

sudo yum install sysbench

macOS（使用 Homebrew）

brew install sysbench

2. 建立测试表

确保数据库中存在一个名为 test 的库，并运行以下指令，可在数据库中建立 20 张表，每张表 10000 条测试数据；

sysbench --db-driver=mysql \
--time=300 --threads=10 --report-interval=1 \
--mysql-host=127.0.0.1 \
--mysql-port=3306 --mysql-user=root --mysql-password=root \
--mysql-db=test --tables=20 --table_size=10000 \
oltp_read_write --db-ps-mode=disable prepare

3. 测试数据库性能

sysbench --db-driver=mysql \
--time=300 --threads=10 --report-interval=1 \
--mysql-host=127.0.0.1 \
--mysql-port=3306 --mysql-user=root --mysql-password=root \
--mysql-db=test --tables=20 --table_size=10000 \
oltp_read_write --db-ps-mode=disable run

性能分析

查看执行频次

查看数据库 CRUD 的执行总数、事务提交数量、日志写入数量等信息，可大致了解到数据库是以写入还是查询为主，便于优化定位。

  mysql> show global status like 'Com_______';
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | Com_binlog    | 0     |
  | Com_commit    | 0     |
  | Com_delete    | 0     |
  | Com_import    | 0     |
  | Com_insert    | 0     |
  | Com_repair    | 0     |
  | Com_revoke    | 0     |
  | Com_select    | 2     |
  | Com_signal    | 0     |
  | Com_update    | 0     |
  | Com_xa_end    | 0     |
  +---------------+-------+

慢查询日志

慢查询日志记录了查询时间超过指定时间（long_query_time，单位：秒，默认 10 秒）的 SQL 语句日志，通过日志可直接定位出异常 SQL 。日志文件位置：/var/lib/mysql/localhost-slow.log。

MySQL 慢查询日志可能没有开启（不同版本会有差异），可通过如下语句查看：

show variables like 'slow_query_log';

如未开启慢查询日志，修改 MySQL 的配置文件（/etc/my.cnf）如下：

# 开启慢查询日志开关
slow_query_log=1
# 设置慢查询日志的时间为3秒，SQL语句执行时间超过3秒，就会视为慢查询，记录慢查询日志
long_query_time=3
# 默认不会记录未使用索引的查询，可通过如下配置打开
log_queries_not_using_index=1

更改后重启 MySQL 服务；

systemctl restart mysqld

profile

profile 能展示每条 SQL 的执行时间，了解一下用法即可；

-- 查看当前 MySQL是否支持 profile
select @@have_profiling;
-- 查看profile功能是否开启，0 未开启；1 开启
select @@profiling;
set profiling = 1;
-- 查看所有 SQL 语句的耗时列表
show profiles;
-- 查看某条语句耗时详情，query_id 是 SQL 列表的序号
show profile for query query_id;
show profile cpu for query query_id;

explain

explain 或者 desc （二者等效）命令可以获取执行 select 语句的信息，如联表查询的先后顺序、子查询先后顺序、查询性能、。

用法： 直接在 select 语句之前加上关键字 explain / desc 即可。

mysql> explain select * from  sys_menu;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | sys_menu | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  104 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+

explain 结果参数说明：

参数	说明
id	select 查询的序列号；子查询会有多个 id，id 越大越先执行，id 相同按顺序执行
select_type	查询类型，常见取值有 SIMPLE（不使用多表查询或子查询）、PRIMARY、UNION、SUBQUERY（包含子查询）等
partitions	数据分区，显示查询所涉及到的分区数量
type	查询性能类型，由好到差的查询性能类型为 NULL（不查询任何表）、system、const（主键或唯一索引查询）、eq_ref（主键联合外键查询）、ref（非唯一索引查询）、range（范围查询）、index（索引全扫描，）、all（全表扫描）等
possible_key	可能应用在这张表上的索引，一个或多个
key	实际使用的索引
key_len	索引占用字节大小，越小性能越高
ref	显示查询中使用的索引或表的连接条件
rows	查询的行数预估，可能不准确
filtered	结果行数/读取行数，越大越好
Extra	联合索引下使用 order by 的排序方式，见 SQL 优化

索引

MySQL 索引采用 B+Tree 数据类型，B+Tree 是一种多叶子节点的树形结构；

二叉树由于只有两个叶子节点，大量数据会使层级很深，且插入有序数据会全部偏向树的左侧，相当于一个链表；

红黑树解决了二叉树的链表问题，但是 Node 依然只有两个叶子节点。

B Tree 和 B+Tree 的区别在于 B+tree 不会在非叶子节点上存储数据，非叶子节点只保存索引值，内存较小，会提升节点的检索效率。B+Tree 数据都在叶子节点，并通过双向链表连接，对于范围查询很友好。

-- 创建索引
create [ unique | fulltext ] index index_name on table_name (index_col_name, ...);
-- 查看索引
show index from table_name;
-- 删除索引
drop index index_name ON table_name;

回表查询

在介绍回表查询前，需要先搞清楚聚簇索引和非聚簇索引（也被称为聚集索引和非聚集索引）。聚簇索引和非聚簇索引不是索引类型，而是一种存储方式。

可以把主键索引就看做聚簇索引，因为在 B+Tree 叶子节点中同时保存了索引和整行数据。

对于非聚簇索引，可理解为不使用主键创建的索引，B+Tree 叶子节点中保存的是索引和主键值。用非聚簇索引查询，会先定位到该数据的主键值（无法直接获取整行数据），然后再使用聚簇索引定位到该主键对应的整行数据，这一过程叫做回表查询。

避免回表查询能提升查询效率。

索引失效

• 最左前缀法则

  索引关联了多列（联合索引），查询时不能跳过索引包含的列，若跳过某个列，则后面的索引字段失效。

  例：user 表中用 id、name、age 三个字段建立一个联合索引，索引使用情况如下：

  select * from user where id='1' and name='zs' and age=18;  -- 三个字段的索引全部使用
  select * from user where name='zs' and age=18;  -- 索引失效，不包含索引最左列（id）
  select * from user where id='1' and age=18;     -- 索引部分生效，age 关联的索引失效

• 范围查询

  联合索引中，出现范围查询（<, >），范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题。

  例：user 表中用 id、name、age 三个字段建立一个联合索引，索引使用情况如下：

  select * from user where id='1' and age>=18 and name='zs' ;  -- 三个字段的索引全部使用
  select * from user where id='1' and age>18 and name='zs' ;  -- name 字段的索引失效

• 索引列运算

  索引列上进行运算操作，索引将失效。

  select * from user where substring(id, 1, 2) = '12';   -- 索引失效

• 字符串未加单引号

  select * from user where name = zs;   -- 索引失效

• 模糊查询

  尾部模糊匹配，索引不会失效；头部模糊匹配，索引失效。

  select * from user where name like '软件%';    -- 使用索引
  select * from user where name like '%工程';    -- 索引失效

• or 关键字

  or 两端都有索引时，索引才会生效。

  例：user 表中用 id、name 有索引，age 没有。

  select * from user where id='1' or name='zs';   -- 使用索引
  select * from user where name='zs' or age=18;  -- 索引失效

索引优化

• 指定某个索引

  若某个字段关联了多个索引，MySQL 默认会选择其中一个使用，也可以人为指定要使用的索引，达到优化效果。

  select * from user use index(idx_user_name) where name='zs';     -- 建议MySQL使用指定索引，MySQL有可能不接受你的建议
  select * from user ignore index(idx_user_name) where name='zs';  -- 忽略指定索引
  select * from user force index(idx_user_name) where name='zs';   -- 强制使用指定索引

• 确保索引被覆盖

  确保索引覆盖了查询所需的所有列，避免使用 select *。

  例：user 表中用 id 为主键、name 和 age 建立了联合索引，sex 字段没有索引。

  -- 会用到回表查询，先通过name得到id，由id拿到整行数据
  select id,name,age,sex from user where name='zs';
  -- 不会回表查询，name和age建立的索引中可以直接拿到id
  select id,name,age from user where name='zs';

• 前缀索引

  若数据列中包含大量文本，直接建立索引会导致索引庞大，影响性能，可通过前缀索引，截取其开头部分建立索引。

  create index idx_xxxx on table_name(column_name(n));  -- 截取前n个字符创建索引
  -- 截取的字符长度可根据截取字符 不重复的总数/数据总条数 的比值计算，越趋近于1越好
  select count(distinct substring(name, 1, 2)) / count(*) from user;

• 单列索引和联合索引

  如果存在多个查询条件，针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

  例：user 表中用 id 为主键、name 和 age 建立了联合索引，phone、addr 为单列索引。

  select phone,addr from user where phone='110' and addr='火星';  -- 只会选取其中一个单列索引，且存在回表查询
  select name,age from user where name='zs' and age=18;           -- 使用联合索引，无回表查询

MySQL 索引类型

分类方式	索引类型	说明	创建语法示例	版本要求
数据结构	B-Tree	最常用索引，支持=, >, >=, <, <=, BETWEEN, LIKE 'pattern%' 等操作	CREATE INDEX idx_name ON table(column)	所有版本
	哈希	仅支持精确匹配(=, IN())，Memory 引擎默认	CREATE INDEX idx_name USING HASH ON table(column)	所有版本
	全文	用于文本搜索，支持 MATCH AGAINST，适用于 CHAR, VARCHAR 和 TEXT 列	CREATE FULLTEXT INDEX idx_name ON table(text_column)	MyISAM/InnoDB
	R-Tree	用于地理空间数据	CREATE SPATIAL INDEX idx_name ON table(geo_column)	5.7+
功能特性	普通	最基本的索引类型	CREATE INDEX idx_name ON table(column)	所有版本
	唯一	保证列值唯一性	CREATE UNIQUE INDEX idx_name ON table(column)	所有版本
	主键	特殊的唯一索引，不允许 NULL	ALTER TABLE table ADD PRIMARY KEY (column)	所有版本
	复合	多列组合索引，遵循最左前缀原则	CREATE INDEX idx_name ON table(col1,col2)	所有版本
	前缀	只索引字段前 N 个字符	CREATE INDEX idx_name ON table(column(10))	所有版本
	覆盖	查询列都包含在索引中	通过合理设计复合索引实现	所有版本
特殊类型	降序	支持索引列降序排序	CREATE INDEX idx_name ON table(column DESC)	8.0+
	函数	基于表达式/函数创建的索引	CREATE INDEX idx_name ON table((UPPER(column)))	8.0+
	隐藏	对优化器不可见的索引	CREATE INDEX idx_name ON table(column) INVISIBLE	8.0+

SQL 优化

插入优化

1. insert 一次插入的数据建议不超过 1000 条；
2. 批量插入最好手动提交事务，因为 MySQL 默认会自动提交事务，每一次 insert 操作都会有一个事务被提交；
3. 根据主键顺序插入；
4. 若插入数据量特别大，建议用 load 指令插入。

order by 优化

MySQL 创建的索引默认会根据数据升序排列，order by 若不指定排序规则默认也是升序排列；联合索引的排序方向需和 order by 方向一致，才会完全使用索引。

例：user 表中用 id 为主键、 age 和 phone 建立了联合索引。

create index idx_age_phone ON user (age, phone);      -- 索引定义

select id,age,phone from user order by age;                   -- 根据age升序，索引有效
select id,age,phone from user order by age,phone;             -- 索引有效
select id,age,phone from user order by age desc,phone desc;   -- 降序，反向扫描索引，有效
select id,age,phone from user order by phone desc,age desc;   -- 违背最左前缀，索引失效
-- 只有age使用到索引，phone需要在获取结果后额外排序，不会用到索引
-- 方向不一致的体现: 联合索引都为升序，而查询一个升序，一个降序
select id,age,phone from user order by age asc,phone desc;
-- 可以重新定义索引,解决phone降序不会使用索引问题
create index idx_age_phone_ad ON user (age asc, phone desc);

提示

通过 explain 可查看 order by 的排序方式，例如:

explain select id,age,phone from user order by age,phone;

在查询结果的 Extra 部分，主要有如下两个内容，可通过他们来判断性能：

1. Using filesort：通过索引无法返回有序数据，需要在缓冲区额外排序
2. Using index：通过索引直接返回有序数据，性能高

默认缓缓冲区大小为 256k，如果不可避免出现 filesort，且是大数据量排序，可以适当增大排序缓冲区大小，编辑 MySQL 配置文件，修改 sort_buffer_size 即可。

-- 查看缓冲区大小
show variables like 'sort_buffer_size';

group by 优化

分组操作可能会创建临时表，影响查询速度，通过索引直接获取分组数据则比较高效。

例：user 表中用 id 为主键、 age 和 phone 建立了联合索引。

explain select age,count(*) from user group by age;       -- 索引有效
explain select phone,count(*) from user group by phone;   -- 违背最左前缀，索引失效
-- 查询条件先出现age再出现phone，索引有效
explain select phone,count(*) from user where age=18 group by phone;

limit 优化

limit 在大数据量的条件下，查询页数越靠后性能越差，如 limit 60000, 10，MySQL 需要对查询的数据排序，再取出后 10 条，排序代价非常大。

• 创建覆盖索引

  创建覆盖索引能够比较好地提高性能，可以通过 覆盖索引 + 子查询 形式进行优化。

  select * from user limit 60000,10;      -- 低效率查询
  -- limit 优化方案
  select * from user u ,(select id from user order by id limit 60000,10) i where u.id = i.id;

• 游标式分页

  在分库分表系统中，推荐使用游标方式（跨表分页在技术上很难实现），直接放弃使用传统分页方式，不再传递页码和每页的条数。例如：

  -- 此种方式，需要基于天然支持排序的字段（如 create_time 或 snowflake_id）
  -- 根据 id 范围查找，每次分页时需记录一下上一次分页的最后一条数据id
  select * from user where id < 123 order by id desc;

count 优化

InnoDB 在执行 count(*) 时，需要把数据一行一行地从引擎里面读出来，然后累计计数，海量数据下比较缓慢。

这种情况数据库级别没有优化方案，可以自己使用缓存来统计计数，如增加数据就加一，删除减一。

count 各种用法的性能：count(*) ≈ count(1) > count(主键) > count(字段)，其中 count(字段)会读取每一行并判断是否为空，不为空加一。

update 优化

InnoDB 的行锁是针对索引加的锁，不会对记录加锁，未加索引的字段做为查询条件进行修改，会从行锁升级为表锁。

例：user 表中用 id 为主键、其他字段未加索引。

update user set age=18 where id = '1';   -- 主键为查询条件，只会锁这一行；
-- name未加索引，会锁住整张表，导致事务只能一个一个执行，并发修改其他行会一直等待。
update user set age=18 where name = 'zs';

根据唯一索引做等值查询，若记录不存在，则会加间隙锁，其他事务在这个间隙内无法新增数据。

例：user 表中用 id 为主键，表中分别有 id 为 1、5、8 三条数据。

-- id为3表中不存在，3在1~5之间，MySQL会自动在1~5之间加间隙锁，防止其他事务在此范围新增数据
update user set age=10 where id=3;

锁

全局锁

在做全库数据备份时，可能需要用到全局锁，备份完成后再释放，防止备份时有其他客户端写入导致数据异常。

flush tables with read lock;                                 -- 添加全局锁
unlock tables;                                               -- 释放全局锁

mysqldump -h 127.0.0.1 -uroot -proot my-bd > D:/my-db.sql;   # 备份数据
# 下面是 InnoDB 提供的快照读方式备份，不需要用到全局锁也能实现备份，推荐使用
mysqldump --single-transaction -h 127.0.0.1 -uroot -proot my-bd > D:/my-db.sql;

全局锁缺点：加锁期间无法写入，业务得凉凉；从库上备份，不能同步主库的日志（binlog），导致主从延迟（从库释放锁后，会加载未读取的日志保证最终一致）。

表级锁

• 表锁

  lock tables my-table read;    # 加读锁，客户端只能读不能写（写操作当前客户端报错，其他阻塞）。
  lock tables my-table write;   # 加写锁，其他客户端不能读不能写（阻塞状态）。
  unlock tables;                # 释放读/写锁

• 元数据锁（meta data lock，MDL）

  由 MySQL 系统自动控制，若某张表存在未提交的事务，无法修改表结构。

• 意向锁

  客户端 A 对某个表加了一个行锁，客户端 B 此时对这个表加表锁， B 端需要检查每一行的加锁情况，性能很低。

  InnoDB 中使用意向锁解决行锁、表锁之间的冲突。客户端 A 添加行锁，会对整张表加一个意向锁，客户端 B 加表锁直接检查意向锁，若不冲突则成功（读写冲突），无需检查每行加锁情况。

行级锁

锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在 InnoDB 存储引擎中，MyISAM 不支持行级锁。

InnoDB 行级锁是对索引加锁，而不是对数据行加锁，更新数据需避免行锁升级为表锁的情况。

以下是行级锁中的几个分类：

• 行锁

  锁定单行记录，防止其他事务对此行 update 和 delete。

• 间隙锁

  锁定范围数据，确保索引记录间隙不变，防止其他事务在这个范围内 insert，解决幻读问题。在可重复度这种隔离级别下支持间隙锁。

• 临键锁

  锁住当前行及其前面的所有数据。

日志

• 错误日志

  该日志默认开启，存放目录/var/log/，默认日志文件名 mysqld.log。

  -- 查看错误日志保存位置
  show variables like '%log_error%';

• 二进制日志

  二进制日志（binlog）记录了所有的 DDL 语句和 DML 语句，但不包括查询语句。MySQL 的数据备份和主从复制都会用到这个日志。

  show variables like '%log_bin%';

  # 查看二进制日志
  # -d 数据库名称
  # -o 忽略掉前n行内容
  # -v 重构为sql语句
  # -vv 重构为sql语句，并输出注释信息
  mysqlbinlog [参数选项] logfilename

• 查询日志

  查询日志会记录所有的数据库操作记录，由于日志很多，默认是未开启的。

  -- 查看查询日志是否开启，以及查询日志保持路径
  show variables like '%general%';

  提示

  开启查询日志，修改 MySQL 的配置文件/etc/my.cnf，添加如下内容：

  general_log=1
  # 日志文件名，不指定默认为host_name.log
  general_log_file=my_mysql_query.log

• 慢查询日志

  详见上文慢查询日志。