MySQL 性能优化

[zhongdeming428]

1字段优化

MySQL 字段设计参考：MySQL 数据类型。

关于 CHAR 和 VARCHAR：

• CHAR(N) 用于存储定长字符串，比如 md5 计算的 hash 值，N 最大为 255，表示字符串的长度（字符数，不是字节数），存储大小为 N 字节。所以如果字符编码比较特殊，N 最大可能达不到 255，要考虑到字符和字节的转换关系。
• VARCHAR(N) 用于存储变长字符串，比如姓名，文章标题等。N 最大值为 65535（也有说 65532，因为需要 2 个字节存储字段具体长度，一个字段存储是否为空）。

如果字符集通过多个字节存储一个字符，则 VARCHAR 和 CHAR 的最大值无法达到理论最大值（255 for CHAR and 65535 for VARCHAR），比如如果采用 utf-8 编码字符集（非 utf8mb4），则一个字符需要占用最多 3 个字节，所以 VARCHAR(N) 中 N 的最大值为 21844（超过这个数量可能会导致 MySQL 行尺寸限制被超出）。

如果需要确定 CHAR 和 VARCHAR 的最大 N 值，需要综合考虑字符编码、是否可选、是否变长（变长字段需要存储字符长度）等因素。

关于 TEXT 和 BLOB 类型字段：

前文提到 MySQL 对于数据行的总长度是有限制的，一般是 65535 bytes。但是这个限制是不包含 TEXT 和 BLOB 类型的字段的，这两个字段在计算数据行尺寸的时候是只占 9 - 12 个字节的，因为这两种类型的字段不存储在数据行中。

因此，针对一些尺寸比较大的字段，我们可以指定其类型为 TEXT 或者 BLOB 来进行存储。

其中，TEXT 字段用于存储大文本类型的字段，BLOB 用于存储大的二进制对象字段。两种类型一共包含以下这些衍生的子类型：

TEXT：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT

BLOB：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

它们占用的空间大小依次为：2^8、2^16、2^24、2^32。

需要注意的是：

• TEXT 和 BLOB 类型的字段不能有默认值（5.x 版本会报错，可以通过关闭严格模式来跳过这个报错，但是这肯定是不推荐的做法）。
• TEXT 和 BLOB 类型的字段如果需要建立索引，需要指定你需要索引的字段的长度（从前往后）。

关于字段设计时需要注意的问题：

• 能用数值型就不要使用字符串型。
  因为数值型存储空间占用小，查询更快，排序更方便。有一个典型面试题：MySQL 如何有效地存储 IP 地址？
• 字段都设计为 NOT NULL 类型。
  NULL 类型的字段浪费存储空间，索引复杂，SQL 操作特殊。
• 一个数据行的大小不要超过 8k，超长的字段使用 TEXT 或者 BLOB 进行存储（参考上方的部分）。
• 优先使用枚举和 SET，提高性能。
• 索引字段最好保持有序插入。我认为这里主要是主键字段最好采用 auto_increment，以保证聚簇索引在维护数据时可以顺序插入，这样可以保证相邻索引对应的数据也存放在相邻的存储空间。

2 索引优化

索引是数据库查询优化手段最有效的一种，下面记载一下学习索引相关知识的一些笔记。

2.1 索引实现

索引的终极目标是通过实现多路查找来降低磁盘 IO 的访问次数，从而提升数据操作的性能。

为了实现多路查找，InnoDB 基于 B+Tree （平衡多路查找树）实现索引。

B+Tree 的图示如下：

每个节点可以有四个值，所以图中是一颗四阶 B+Tree，每一层存储四个键值，五个指向子节点的指针。

B+Tree 的规律为 m 阶 B+Tree 扇出为 m+1 （即每个节点存储 m 个值以及 m+1 个指向字节点的指针），N 条数据的 m 阶 B+Tree 树高为 log(m+1)N。

B+Tree 的特点是：

• 节点左边的值小于当前节点，右边的值大于当前节点。
• 只有叶子结点会存储数据。
• 非叶子节点只存储索引列的值，具体存储什么键值需要看索引的类型，如果是非联合索引则存储对应列的值即可，如果是联合索引则会存储多个列的组合值。
• B+Tree 只能帮助快速找到查找的记录所在的存储空间，后续还需要在内存中继续对数据进行二分查找，但是这个过程速度会比进行磁盘 IO 快很多。
• 叶节点的容量必须半满以上（上图磁盘块一没有满足这个条件），即节点内 key 的个数在 M/2 ~ M之间,叶子节点在同一层，并用指针连接成链表。
• 叶子结点之间存在指针互相连接，最终所有叶子节点形成一个环，所以 InnoDB 可以轻松实现数据的遍历查找和分页查找。
• B+Tree 默认的 innodb_page_size 为 16k，这是 innodb 操作内存和磁盘的基本单位，每个叶子节点的大小都是它的整数倍。每个数据行的尺寸越小，B+Tree 的阶数就可能越大，最差的情况下（一行数据超过 8K（innodb_page_size / 2），B+Tree 会退化成双向链表）。参考 MySQL 原理 —— InnoDB 表的限制。

每个节点数据的存取都需要操作一次磁盘（没有缓存时），通过这个 B+Tree 可以有效地减少磁盘的操作次数（等于树的高度）。

所以可以发现树的阶数越高，每个节点存储的数据越多，树的高度越小，性能越好。

下面是索引的一些分类，它们之间并不一定是独立互斥的关系。

2.2 聚簇索引

聚簇索引是一种索引，也是一种数据的存储（组织）方式， 其最大特点是叶子节点会存储完整的数据行 。

当一个 table 被新建的时候，MySQL 会默认使用主键列建立聚簇索引；如果没有主键，则会使用第一个不为 NULL 的具有唯一约束的列作为聚簇索引，否则会自己定义一个隐形字段作为主键建立聚簇索引。

table 中的所有数据都会基于聚簇索引进行存储，所以说聚簇索引既是一种索引，也是一种数据存储方式；只要有 table 存在，就一定会有聚簇索引，且一张表最多只有一个聚簇索引。

2.3 辅助索引

与聚簇索引相对的是第二索引（aka：辅助索引、非聚簇索引），第二索引的特征是叶子节点不存储完整的数据行，为了节约空间只会存储主键值。

所以第二索引在查找的时候可能会查找两次索引树，一次通过第二索引查找主键值，一次根据聚簇索引查找完整的数据行。所以推荐在实际编码中不要写 select * from ... 这样的查找句式，这样写会有以下两个问题：

1. 默认查找所有字段是一种较差的实践，语义性很差是其一，在字段新增或者删除之后容易造成 bug 是其二。
2. 有些时候我们不需要查找所有数据，只需要查找部分索引已经覆盖到了的值，所以本来可以只查找一次索引树就可以完成查询任务的，写了 select * from ... 之后还需要查找一遍聚簇索引树来查找整条记录，实际上我们可能根本不会用到第二次查找出来的数据，这就造成了性能的浪费。

2.4 联合索引

针对表上的多个列建立联合索引，创建索引时的顺序十分重要，因为在建立索引树的时候会按照这几个列的顺序进行排序（按照联合索引定义时候的顺序依次进行比较，只有前一个字段的值相等的时候才会继续比较下一个字段的值）。顺序不同，构造的索引树也不同，且会影响之后索引的使用。

联合索引示意：

联合索引的最左前缀匹配原则： 在 MySQL 建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

比如针对 col1、col2 和 col3 三个字段建立联合索引：

KEY test_col1_col2_col3 on test(col1,col2,col3);

则 col1、(col1, col2) 和 (col1, col2, col3) 都可以使用。

创建联合索引后，基于联合索引进行排序查找的一些场景：

1. 建立索引：KEY(key1, key2)

2. 可以使用索引的排序语句

   SELECT * FROM t1 ORDER BY key1,key2
   SELECT * FROM t1 WHERE key1 = k1 ORDER BY key2
   SELECT * FROM t1 WHERE key1 > k1 ORDER BY key1 ASC
   SELECT * FROM t1 WHERE key1 = k1 AND key2 > k2 ORDER BY key2

3. 不可以使用的排序语句

   SELECT * FROM t1 ORDER BY key1,key2, key3;
   SELECT * FROM t1 ORDER BY key2, key1;
   SELECT * FROM t1 ORDER BY key1 DESC, key2 ASC;
   SELECT * FROM t1 WHERE key1> k1 ORDER BY key2;

感觉这种判断不太好死记硬背，可以在不确定的时候通过 explain 语句来判断语句是否走了索引进行查询。

2.5 索引覆盖

回表查询： 指的是先通过第二索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引 B+ Tree，它的性能较扫一遍索引树更低。

索引覆盖： 只需要在一棵索引树上就能获取 SQL 所需的所有列数据，无需回表，速度更快。例如：select id,age from user where age = 10;。这个 SQL 里面 age 是第二索引，id 是主键，所以只需要在第二索引上查找到主键的值就可以和 age 一并返回了，无需回表查询。

如何实现索引覆盖？

1. 移除不需要查找的字段，尽量让所有字段都在索引覆盖的范围中。
2. 如果要查找的字段一定不在索引范围内且查找比较频繁，可以尝试建立联合索引，但是要评估建立索引的必要性，不要过多的建立索引。

索引覆盖的可能场景：

SELECT a FROM table WHERE b=1 and c=2;

1. 如果索引 index(b,c) 并且 a 是主键，不需要查主索引，查询结束
2. 如果索引 index(b,c) 并且 a 不是主键，需要查主索引
3. 如果索引 index(b,c,a) 不需要查主索引，查询结束

参考：MySQL 的索引覆盖与回表。

2.6 索引和锁

InnoDB 行级锁的实现与索引节点有关，这里需要深入了解一下。

2.7 使用建议

索引适用于以下场景：

1. 全值匹配，和（联合）索引中的所有列进行匹配。
2. 匹配最左前缀，只使用（联合）索引的第一个索引，或者第一个 + 第二个索引……，不跳过中间的索引即可。
3. 匹配列前缀，匹配某一列值的开头部分，但是这里要注意筛选范围尽可能精确，因为扫描范围超过全表 20% 之后存储引擎同样会不使用索引进行查找。
4. 匹配范围值，B+Tree 是按照顺序组织索引树的，很适合进行顺序范围查找。
5. 精确匹配某一列并范围匹配另一列，这里要遵循最左前缀原则，最左边的列进行精确查找，右边的列进行范围匹配。
6. 只访问索引的查询，参考索引覆盖。

在编写 SQL 语句时遵循一定的规则可以提高 SQL 语句使用索引进行查询的可能性。

以下场景会导致 SQL 引擎不使用索引而降低操作性能，应当尽可能避免：

1. 在 where 语句中基于索引列进行运算，比如：

   SELECT * FROM t_video_comm_sphinx WHERE c_id + 1 = 894657; // cid 为索引列

2. 在 where 语句中对索引列使用函数：

   SELECT * FROM t_video_comm_sphinx  WHERE UNIX_TIMESTAMP(c_ctime) < UNIX_TIMESTAMP('2006-10-10') - 86400; // ctime 为索引列

3. % 开头的模糊查询：

   SELECT * FROM t_video_comm_sphinx WHERE c_vid LIKE '%01DmPZ';

4. 查询数据过多，大约是超过全部数据的 20%：

   SELECT * FROM t_video_comm_sphinx WHERE c_vid LIKE '8x%'; // good
   SELECT * FROM t_video_comm_sphinx WHERE c_vid LIKE '8%'; // not good

5. 隐式的数据类型转换，比如 phone 字段为 char(11) 类型，但是在查询的时候却使用了 where phone = 18112344321，这里即使 phone 是索引列，查询的时候也不会走索引查询，所以我们应该避免出现这种隐式的类型转换。

6. 针对联合索引，不遵循最左前缀原则的查询，会不使用索引。必须要从最左侧的列开始查找才能基于索引进行优化，不能跳过左边的列。

7. 如果查询中有某个列的范围查询，则其右边的列都无法使用索引优化查询。比如：

   where last_name='Smith' and first_name LIKE 'J%' and dob='1990-03-02';

   即使建立了 (last_name,first_name,dob) 的联合索引，由于 LIKE 的存在，右边的 dob 查询是无法通过索引进行优化查询的。索引只能覆盖到 last_name 和 first_name。

3优化建议

3.1 开发建议

1. 不要在一张表中建立太多索引，只有在真正需要的时候才加，建议单张表中的索引不要超过 5 个。
   索引太多时，写操作的性能会比较差，如果字段实在太多，建议作垂直拆分。

2. 联合索引中的字段不要超过 5 个。

3. 不要在更新频繁或者区分度不高的字段上建立索引。
   更新会变更 B+Tree，更新频繁的字段建立索引会降低数据库性能。
   对于 name、age、status、gender 这种区分度不大的字段建立索引没有意义，区分度的计算公式：

   select COUNT(DISTINCT column_name)/COUNT(*) from table_name;

4. 建立联合索引时，把区分度高的字段放在前面。

5. 不要使用 select * 语句，建议手动列出所有需要查询的字段。
   凭空消耗 IO 资源，而且可能会破坏索引覆盖。

6. 不要在 where 条件语句中的字段上使用函数或者进行运算。

7. 不要做负向查询（比如 is not、!=、<>、!>、!<、not in、not like 等）、以及以 % 开头的模糊查询。
   负向查询和 % 开头的模糊查询会导致进行全表扫描。

8. 尽量减少主键的大小。
   节约建立 B+Tree 时的空间，因为所有第二索引的叶子节点都会保存主键的值。

3.2 性能优化

MySQL 语句可以通过 explain 语句进行性能优化。

下面是使用 explain 对 select 语句进行分析的结果：

表 user_tab 中 user_name 是一个唯一索引，因为这里索引覆盖了，所以查询的时候会基于索引进行查询，性能比较好。

可以看到 type 为 index（全索引扫描），key 为 idx_user_name，Extra 为 Using index（表明索引覆盖了）；这说明这里的查询是基于 user_name 的唯一索引进行的查询。

下面是 explain 语句返回的各个字段的含义：

在进行性能调优的时候可以查一查这张表，看看 SQL 性能是否符合要求。