数据库优化手册

发表于 更新于

以MySQL为例的数据库优化手册

数据库优化手册

本文使用MySQL 5.7.35为例

请重点关注文档中的加粗部分

介绍 § Introduction

天下苦数据库优化久矣, 本文以读者已熟悉基本的MySQL增删改查语句为前提, 介绍以索引, Explain语句与慢查询日志三项为主的优化方法, 希望对您有所帮助

还有如事务, 分库分表之类的做法可能会在日后完善

索引 § Index

介绍

数据库可以通过索引来提高数据的检索速度

在查询语句中的WHERE部分使用到索引字段时, 就会使用索引进行查询

使用

普通索引

若存在表user如下

1
2
3
4
5
6
-- 创建一个用户表
CREATE TABLE `user` (
    `id` INT PRIMARY KEY,
    `username` VARCHAR(32) COMMENT '用户名',
    `password` VARCHAR(32) COMMENT '密码'
);

  • 方法一, 可以通过如下语句来创建一个将username作为索引字段的索引

    1
    2
    -- 以用户名作为索引字段, 给用户表建立索引
    CREATE INDEX `username_index` ON `user` (`username`);

  • 方法二, 或者在创建表的同时创建索引

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE `user` (
        `id` INT PRIMARY KEY,
        `username` VARCHAR(32) COMMENT '用户名',
        `password` VARCHAR(32) COMMENT '密码',
        -- 在创建表的同时创建索引
        INDEX `username_index` (`username`)
    );

  • 方法三, 也可以在表创建后修改表内容来创建索引

    1
    ALTER TABLE `user` ADD INDEX `username_index`(`username`);

查看表的索引

可以通过如下语句来查看一个表有哪些索引

1
2
-- 显示用户表的索引
SHOW INDEX FROM `user`;

显示结果

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
user 0 PRIMARY 1 id A 0 null null BTREE
user 1 username_index 1 username A 0 null null YES BTREE

可以看到一条默认存在的以id作为索引字段的索引和刚创建的以username作为索引字段的username_indesx

字段解释

  1. Table: 表名

  2. Non_unique: 是否是唯一索引, 0为唯一索引, PRIMARY就是唯一索引

  3. Key_Name: 索引名

  4. Squ_in_index: 列的序列号, 标识该列在该索引中的顺序

    • 普通索引只有一个列, 故序号为1, 下文提及的联合索引会有所不同

  5. Column_name: 列名

  6. Collation: 列的存储字符序

    • 值可以为: A(升序)或NULL(无分类)

  7. Cardinality: 基数, 代表表中此列数据为唯一值的估计值

    • 若该值较低, 则需要重新思考是否有这条索引存在的必要

    • 此处为零因为数据库中暂无数据

  8. Sub_part: 前缀索引的前缀长度

    • 若索引非前缀索引, 或字段并未取前缀, 则为null

    • 前缀索引的概念见下

  9. Packed: 索引字段的压缩方式, 若未压缩则为null

  10. NULL: 列中是否允许存在null值

    • 作为索引的列不允许存在null值, 所以这里的username作为索引是不符合规范的
    • 这里可以为username列添加NOT NULL来避免这一情况

  11. Index_type: 索引类型

    • 可能值
      1. FULLTEXT: 全表检索索引
      2. HASH: 哈希值索引, 类似键值对的形式存储索引, 效率极高
        • InnoDB引擎不支持HASH类型Index_type
      3. BTREE: 一种树形索引, 默认且最常用
      4. RTREE: 一种树形索引, 较少用, 比BTREE的优势在于范围查找

  12. Comment与Index_comment: 注释

查看语句使用到的索引

详细解释请阅读下文 执行计划 § Explain 部分

可以通过在查询语句前加上关键字EXPALIN来查看这条语句所使用到的索引

1
2
-- 查看这条查询语句所用到的索引
EXPLAIN SELECT * FROM `user` WHERE `username` = "";

显示结果

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user null ref username_index username_index 99 const 1 100.00 null

可以看到, 这里用到了username_index这个索引

其他索引

前缀索引

当列的类型为BLOB或TEXT一类或者长度很长的VARCHAR时, 将列的完整数据作为索引时不合适的, 这样会导致索引效率慢

当遇到这种情况时, 可以在创建索引时, 给列添加前缀长度限制, 这种索引成为前缀索引

1
2
-- 该索引只取用户名的前2字符作为索引字段
CREATE INDEX `sub_username_index` ON `user` (`username`(2));

联合索引

此处可在阅读下文 执行计划 § Explain 部分介绍之后再阅读

在普通索引之后还有联合索引, 这种索引比普通索引有更少的时间与空间复杂度, 并以索引树的方式存储

可以将多列同时作为索引字段来创建联合索引, 这样创建的索引与普通索引有所不同

1
2
-- 创建一个3列的联合索引
CREATE INDEX `index` ON `table` (`column1`, `column2`, `column3`);

这样相当于创建了

  1. column1的单列索引
  2. column1与column2的联合索引
  3. column1, column2与column3的联合索引
最左匹配原则

联合索引从最左开始匹配, 最左指索引创建时的顺序最左

1
2
3
4
5
6
7
8
-- 以column2与column3作为条件, 不会用到联合索引
SELECT * FROM `table` WHERE `column2` = 2 AND `column3` = 3;

-- 以column2与column1作为条件, 会用到联合索引, 顺序不影响
SELECT * FROM `table` WHERE `column2` = 2 AND `column1` = 1;

-- 以column1与column3作为条件, 会用到索引, 但是是column1的单列索引
SELECT * FROM `table` WHERE `column1` = 1 AND `column3` = 3;
举例

以上述用户表为例, 若实际场景中经常有需要通过username与password共同判断来查询的内容的情况, 那么可以创建联合索引

创建方式也有如上多种, 此处不赘述

1
2
-- 创建一个id与用户名的联合索引
CREATE INDEX `username_password_index` ON `user` (`username`, `password`);

查看user的索引

1
SHOW INDEX FROM `user`;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
user 0 PRIMARY 1 id A 0 null null BTREE
user 1 username_password_index 1 username A 0 null null BTREE
user 1 username_password_index 2 password A 0 null null BTREE

此处可以看到, 同一索引名产生了两条索引, 它们的索引顺序与对应列名不同

接下来进行Explain分析

1
2
3
4
5
6
7
8
-- 通过用户名与密码查询用户
EXPLAIN SELECT * FROM `user` WHERE `username` LIKE 'T%' AND `password` = 'password1';

-- 通过用户名查询用户
EXPLAIN SELECT * FROM `user` WHERE `username` LIKE 'T%';

-- 通过密码查询用户
EXPLAIN SELECT * FROM `user` WHERE                          `password` = 'password1';

显示结果

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user null range username_password_index username_password_index 198 null 2 25.00 Using index condition
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user null range username_password_index username_password_index 99 null 2 100.00 Using index condition
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user null ALL null null null null 4 25.00 Using where

对比三条语句所用到的结果, 可以发现

  1. 同时将username与password作为判断条件时, 用到了索引username_password_index
  2. 只将username作为判断条件时, 用到了索引username_password_index
  3. 只将password作为判断条件时, 没有用到索引

也就证明了, 存在username的单列索引, 存在username与password的联合索引, 但是不存在password的单列索引

执行计划 § Explain

介绍

在查询语句前加上Explain关键字, 会进行语句的执行计划分析, 会检查这条语句用到了哪些检索, 是否有进行全表扫描等

使用

测试

若存在表user与表user_type如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 用户表
CREATE TABLE `user` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(32) COMMENT '用户名',
    `password` VARCHAR(32) COMMENT '密码',
    `type_id` INT COMMENT '类型ID'
);

-- 用户名索引
CREATE INDEX `username_index` ON `user` (`username`);

-- 用户类型表
CREATE TABLE `user_type` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `typename` VARCHAR(32) COMMENT '类型名称'
);

-- 类型名索引
CREATE INDEX `typename_index` ON `user_type` (`typename`);

并存在数据

user

id username password type_id
1 Token1 null 1
2 Token2 null 2
3 User1 null 1
4 User2 null 2

user_type

id typename
1 user
2 admin

使用Explain语句对多表关联语句进行分析

1
2
-- 查询用户表中用户名为T开头, 且类型为管理员的用户
EXPLAIN SELECT u.`username` FROM `user` AS u LEFT JOIN `user_type` AS t ON u.`type_id` = t.`id` WHERE u.`username` LIKE "T%" AND t.`typename` = "admin";

显示结果

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t null ref PRIMARY,typename_index typename_index 99 null 1 100.00 Using index
1 SIMPLE u null range username_index username_index 99 const 2 25.00 Using index condition; Using where; Using join buffer (Block Nested Loop)
字段解释

  1. id: SELECT的查询顺序序列号

  2. select_type: 查询类型

  3. table: 表名

  4. partition: 匹配的分区

  5. type: 对表的查询方式

    • 常用的类型(性能排序): ALL < index < range < ref < eq_ref < const < system < NULL

  6. possible_keys: 能用的索引, 但不一定被用到

  7. keys: 实际用到的索引

  8. key_len: 索引字段的最大可能长度

  9. ref: 使用常量查询则显示const, 连接查询则显示关联字段

  10. rows: 估算的结果行数

    • 只是预估行数, 并非准确行数

  11. filtered: 数据经过过滤后满足条件的数据的百分比

  12. Extra: 查询的其他详细信息

    常见信息

    1. Using index: 使用了索引
    2. Using where: 不用读取表中所有信息，仅通过索引就可以获取所需数据
    3. Using join buffer: 说明在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果
      • 一般出现这句话可以通过添加索引来优化
    4. Using filesort: 说明MySQL无法通过索引来进行排序, 需要通过外部排序, 这种排序就是文件排序
      • 出现这句话需要注意, 数据量大的情况下可能会拖累性能, 需要及时优化
    5. Using temporary: 说明使用了临时表保存中间结果来进行排序
      • 需要马上优化
    6. Impossible where: 说明存在会导致无结果的WHERE

慢查询日志 § Slow Query Log

介绍

慢查询日志开启后会记录执行时间大于某指定时间长度的查询

可以通过查看慢查询记录, 配合Explain关键字进行SQL语句优化

配置与使用

开启慢查询日志记录

以下为Linux的开启方法, Windows类似

  • 方法一, 通过配置文件开启

    Windows系统中配置文件为my.ini, 在MySQL安装过程中手动创建
    Linux系统中配置文件为my.cnf, 在/etc或/etc/mysql中

    1
    2
    3
    4
    5
    6
    7
    [mysqld]
    # 开启慢查询日志, 值也可为1
    slow_query_log = on
    # 指定日志文件, 需要手动创建文件
    sloq_query_log_file = /var/log/mysql/slow_query.log
    # 指定慢查询时间, 单位秒
    long_query_time = 2

  • 方法二, 在MySQL中通过指令开启

    1
    2
    -- 开去慢查询日志, 其他属性配置方式与上配置类似
    SET GLOBAL slow_query_log = on;

    需要通过bash重启mysql服务

    1
    service mysqld restart

  • 注意点

    1. CentOS需要配置日志文件的所有者, 执行chown mysql:mysql /var/log/mysql/slow_query.log可以将日志文件所有者设置为mysql

    2. CentOS或其他Linux系统环境若在如上所有配置完之后依然不能开启慢查询日志, 则还需要配置SELinux(安全增强型Linux)信息

      步骤

      1. 查找 & 安装工具包
        1. 执行yum provides /usr/sbin/semanage查询指令所在包
        2. 执行yum install -y xxx进行安装, xxx是上面一条指令所提供的包, 一般完整包名以policycoreutil-python开头
      2. 执行semanage fcontext -a -t mysqld_log_t /var/log/mysql/slow_query.log, 这条指令将日志文件的默认安全类型设定为mysql_log_t
      3. 执行restorecon -Rv /var/log/mysql/slow_query.log, 这条指令将回复日志文件的安全上下文至刚设定的状态

查看慢查询日志记录

bash中执行如下语句可以查看慢查询记录, 也可以通过cat指令查看

1
mysqldumpslow -a /var/log/mysql/slow_query.log

在查询前可以通过如下SQL语句来手动添加一条慢查询语句:

1
2
-- 休眠3秒
SELECT SLEEP();

查询结果为:

1
2
3
4
5
6

Reading mysql slow query log from /var/log/mysql/slow_query.log
Count: 1  Time=3.00s (3s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[192.168.56.1]
  SELECT SLEEP(3)
  LIMIT 0, 1000

其他优化手段与细节 § Details

  1. 任何地方都不要使用星号*来进行查询, 使用具体的字段来代替它, 不要返回用不到的字段

  2. 注意缓存的使用

    MySQL会产生SQL缓存, 重复查询时会使用缓存中的数据, 在表内容更新后消失

    在调试过程中若重复执行统一语句, 产生结果的时间可能会受缓存影响, 所以在必要时需要加上关键字SQL_NO_CACHE

    1
    2
    3
    4
    5
    -- 可能会受缓存影响
    SELECT * FROM `user`;

    -- 不会受到缓存影响
    SELECT SQL_NO_CACHE * FROM `user`;

    测试过程中使用SQL_NO_CACHE并不代表数据库不适用缓存, 只是该语句不使用

  3. 模糊查询时不要前置百分号%

    1
    2
    -- 不要这么做
    SELECT `id` FROM `user` WHERE `username` LIKE '%T%';

  4. 使用事务

    当一次请求涉及到多条SQL修改操作, 若失败时也需要按顺序回滚, 这时可以使用到事务

    使用BEGIN, COMMIT与ROLLBACK指令进行事务处理

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    -- 开始事务
    BEGIN;
    -- 数据操作
    INSERT INTO ...
    -- 数据操作
    UPDATE ...
    -- 完成事务, 数据操作成功
    COMMIT;

    -- 开始事务
    BEGIN;
    -- 数据操作
    INSERT INTO ...
    -- 数据操作
    UPDATE ...
    -- 回滚事务, 数据将会回滚至BEGIN时的状态
    ROLLBACK;

  5. 分库分表

    介绍

    • 分库: 将单个数据库中的表拆分到多个数据库中

    • 分表: 将单个表中的数据拆分到多个表中

    原因

    • 性能方面
      • 表过多将增大数据库的连接压力, 分库将连接压力分散到多个数据库上
      • 单表数据量过大将降低CRUD性能, 分表将提高每个表的性能
    • 可用性方面
      • 单数据库或表在发生意外时容易同时丢失所有数据, 分库分表后若发生意外还可以继续维持一部分服务
    • 使用: 什么是分库分表，为什么要分库分表？

总结 § Summary

  1. 使用索引, 可以的话使用联合索引, 要注意大字段使用前缀索引
  2. 开启慢查询日志, 记录慢查询, 一般在测试或生产环境中使用
  3. 使用Explain来分析慢查询的SQL语句, 是否有用到索引, 是否时全表检查等
  4. SQL分析时注意MySQL的缓存
  5. 注意基本SQL的书写细节, 不要用星号*进行查询, 不要在LIKE内容的最前面加百分号%, 合理使用事务功能, 在表数量过多或数据量过大时进行分库分表

参考