MySQL 性能优化

未完待续...

1. 分库、分表结构优化

1.1 数据库设计

1.2 选择数据类型

1.3 数字类型

1.3.1 整数类型

1.3.2实数类型

1.4 字符串类型

1.4.1 CHAR与VARCHAR

1.4.2 BINARY与VARBINARY

1.4.3 TEXT与BLOB

1.4.4 ENUM类型

1.4.5 SET类型

1.5日期时间类型

1.5.1 日期类型

1.5.2 时间类型

2. 索引优化

2.1 索引简介

以下是一个简单的查询语句，它的作用是查找编号为5的员工：

SELECT * FROM employee WHERE emp_id = 5;

如果没有索引，数据库就只能扫描整个员工表，然后依次判断每个数据记录中的员工编号是否等于5并且返回满足条件的数据。这种查找数据的方法被称为**全表扫描**（Full Table Scan）。

全表扫描最大的一个问题，就是当表中的数据量逐渐增加时性能随之明显下降，因为磁盘 I/O 是数据库最大的性能瓶颈。

当表中的数据量很小（例如配置表），或者查询需要访问表中大量数据（数据仓库），索引对查询的优化效果不会很明显。

为了解决大量磁盘访问带来的性能问题，MySQL引入了一个新的数据结构：索引（Index）。索引在MySQL中也被称为键（Key）。MySQL默认使用B-树（B+树）索引，它就像图书后面的关键字索引一样，按照关键字进行排序并且提供了指向具体内容的页码。

B-树索引就像是一棵倒立的树，树的节点按照顺序进行组织，节点左侧的数据都小于该节点的值，节点右侧的数据都大于该节点的值。B+树索引基于B-树索引进行了优化，它们只在叶子节点存储索引数据（降低树的高度，从而减少了磁盘访问次数），并且增加了叶子节点或者兄弟节点之间的指针（优化范围查询）。

举例来说，假设索引的每个分支节点可以存储100个键值，100万条记录只需要3层B-树即可完成索引。数据库通过索引查找指定数据时需要读取3次磁盘I/O（每次磁盘I/O读取整个索引节点）就可以得到查询结果。

如果采用全表扫描的方式，数据库需要执行的磁盘I/O可能高出几个数量级。当数据量增加到1亿条记录时，通过索引访问只需要增加一次磁盘I/O即可，全表扫描则需要再增加几个数量级的磁盘I/O。

主流数据库默认使用的都是B-树（B+树、 B*树）索引，它们实现了稳定且快速的数据查找（O(log n) 对数时间复杂度），可以用于优化=、、 BETWEEN、 IN运算符以及字符串的前向匹配（“ABC%”）等查询条件。

2.2 聚簇索引

聚集索引（Clustered Index）将表中的数据按照索引（通常是主键）的结构进行存储。也就是说，聚集索引的叶子节点中直接存储了表的数据，而不是指向数据的指针。

聚集索引其实是一种特殊的表， MySQL（InnoDB）和 Microsoft SQL Server 将这种结构的表称为聚集索引， Oracle数据库中将其称为索引组织表（IOT）。这种存储数据的方式类似于Key-Value存储，适合基于主键进行查询的应用。

聚簇索引生成方式：

如果定义了主键，InnoDB使用主键聚集数据；
如果没有定义主键，InnoDB使用第一个非空的UNIQUE索引聚集数据；
如果没有主键和可用的UNIQUE索引，InnoDB使用一个隐藏的内部ID字段聚集数据。（存在问题：只有一个字段ID，如果多个表都是用该方式构建聚簇索引，此时内部ID的自增都是在同一个ID上自增）。

2.3 辅助索引

MySQL（InnoDB）中的辅助索引也被称为二级索引（Secondary Index），叶子节点存储了聚集索引的键值（通常是主键）。

我们通过二级索（上图中使用 name 作为二级索引）引查找数据时，系统需要先找到相应的主键值，再通过主键索引查找相应的数据（回表）。因此，创建聚集索引的主键字段越小，二级索引就越小。这也是我们通常使用自增数字而不是UUID作为MySQL主键的原因之一。

二级索引叶子节点存储聚集索引键值的好处：当当数据发生改变或移动时，可以保证二级索引的稳定性（只要ID不变则无需修改二级索引）。

二级索引叶子节点存储聚集索引键值的缺点：增加了一次回表操作。

2.4 复合索引

复合索引是基于多个字段创建的索引，也叫多列索引。

复合索引可以避免为每个字段创建单独的索引，使用复合索引时最重要的是索引字段的顺序。

复合索引首先按照第一个（最左侧）字段排序，然后按照第二个字段排序，以此类推。因此，一个选择索引字段顺序的经验法则是：将选择性最高的字段放在最前面。

通过如下命令可以查看不同前缀长度的选择性：

SELECT count(DISTINCT emp_name)/count(*) emp_name_sel,
       count(DISTINCT sex)/count(*) sex_sel
FROM employee;

emp_name_sel|sex_sel|
------------+-------+
      1.0000| 0.0800|

注意：如果数据分布不均匀，这种经验法则可能对于特定值的查询性能很差。

最左前缀匹配原则：复合索引(col1, col2, col3)，相当于以下三个索引：

(col1)
(col1, col2)
(col1, col2, col3)

举例来说，它可以用于优化以下查询条件（左侧的确定了才能使用索引查找右侧的）：

WHERE col1 = val1 AND col2 = val2 AND col3 = val3
WHERE col1 = val1 AND col2 = val2
WHERE col1 = val1
WHERE col1 = val1 AND col2 BETWEEN val2 AND val3
WHERE col1 BETWEEN val1 AND val2
WHERE col1 LIKE 'ABC%'

2.5 前缀索引

前缀索引（Prefix Index）是指基于字段的前一部分内容创建的索引。BLOB 、TEXT或者很长的VARCHAR类型字段必须使用前缀索引，因为MySQL对索引的长度有限制。MySQL 5.7默认不能超过3072字节。

前缀索引的优点是可以节省空间，提高索引性能，但缺点是会降低索引的选择性。

索引的选择性是指不重复的索引值（基数）和表中的数据总量的比值，范围处于（1/总数据量）到1之间。选择性越高的索引查询效率越高，因为可以过滤掉更多的数据。主键和唯一索引的选择性是1。

通过如下命令可以查看不同前缀长度的选择性：

SELECT count(DISTINCT LEFT(email,3))/count(DISTINCT email) left3,
       count(DISTINCT LEFT(email,4))/count(DISTINCT email) left4,
       count(DISTINCT LEFT(email,5))/count(DISTINCT email) left5,
       count(DISTINCT LEFT(email,6))/count(DISTINCT email) left6
FROM employee;

left3 |left4 |left5 |left6 |
------+------+------+------+
0.6000|0.7200|0.9200|1.0000|

示例中，当前缀长度到达6的时候，选择性和索引整个email字段没有区别。因此，可以基于该字段创建一个前缀索引：

CREATE INDEX idx_employee_email ON employee(email(6));

前缀索引也存在缺点，MySQL不能使用前缀索引进行排序（ORDER BY）和分组（GROUP BY），也不能实现索引覆盖扫描。

前缀索引的设计关键在于保证足够的选择性，同时又不能太长，以便节约存储。

2.6 函数索引

MySQL 8.0支持函数索引（Function-Based Index），也被称为表达式索引（Expression-Based Index），是基于函数或者表达式创建的索引。

例如，员工的电子邮箱不区分大小写并且唯一，我们可以基于LOWER(email)函数创建一个唯一的函数索引。

explain select * from employee where lower(email) = lower('ZhangFei@shuguo.com');

Name         |Value      |
-------------+-----------+
id           |1          |
select_type  |SIMPLE     |
table        |employee   |
partitions   |           |
type         |ALL        |
possible_keys|           |
key          |           |
key_len      |           |
ref          |           |
rows         |25         |
filtered     |100.0      |
Extra        |Using where|

create unique index uk_emp_email_lower on employee((lower(email)));
analyze table test;

explain select * from employee where lower(email) = lower('ZhangFei@shuguo.com');

Name         |Value             |
-------------+------------------+
id           |1                 |
select_type  |SIMPLE            |
table        |employee          |
partitions   |                  |
type         |const             |
possible_keys|uk_emp_email_lower|
key          |uk_emp_email_lower|
key_len      |403               |
ref          |const             |
rows         |1                 |
filtered     |100.0             |
Extra        |                  |

函数索引能够支持其他方式无法使用的数据类型，例如JSON数据。

CREATE TABLE employees (
  data JSON,
  INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin))
);
INSERT INTO employees VALUES
  ('{ "name": "james", "salary": 9000 }'),
  ('{ "name": "James", "salary": 10000 }'),
  ('{ "name": "Mary", "salary": 12000 }'),
  ('{ "name": "Peter", "salary": 8000 }');
  
SELECT * FROM employees WHERE data->>'$.name' = 'James';

函数索引要求完全按照索引定义的相同方式指定查询中的条件。

2.7 降序索引

MySQL 8.0支持降序索引（Descending index）：索引定义中的DESC不再被忽略，而是以降序方式存储索引键值。

在之前的版本中，索引支持反向扫描，但是性能稍差一些。降序索引可以进行正向扫描，效率更高。当查询需要针对某些列升序排序，同时针对另一些列降序排序时，降序索引使得优化器可以使用多列混合索引扫描。

CREATE TABLE t (
  id INT AUTO_INCREMENT PRIMARY KEY, c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

优化器可以为不同的ORDER BY子句使用正向索引扫描，而不需要执行 *filesort* 排序。

explain select * from t ORDER BY c1 ASC, c2 DESC;

Name         |Value      |
-------------+-----------+
id           |1          |
select_type  |SIMPLE     |
table        |t          |
partitions   |           |
type         |index      |
possible_keys|           |
key          |idx2       |
key_len      |10         |
ref          |           |
rows         |1          |
filtered     |100.0      |
Extra        |Using index|

MySQL 8.0不再对GROUP BY操作进行隐式排序，排序需要明确指定ORDER BY。

2.8 隐藏索引

MySQL 8.0支持隐藏索引（invisible index），也称为不可见索引。隐藏索引不会被优化器使用。

主键不能设置为隐藏（包括显式设置或隐式设置）。

CREATE TABLE t1 (
  i INT,
  j INT,
  k INT,
  INDEX i_idx (i) INVISIBLE
) ENGINE = InnoDB;

CREATE INDEX j_idx ON t1 (j) INVISIBLE;
ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

索引的可见性不会影响索引的维护。例如，无论索引是否可见，每次修改表中的数据时都需要对相应索引进行更新，而且唯一索引都会阻止插入重复的列值。

MySQL系统变量optimizer_switch中的use_invisible_indexes设置控制了优化器构建执行计划时是否使用隐藏索引。如果设置为off（默认值），优化器将会忽略隐藏索引（与引入该属性之前的行为相同）。如果设置为on，隐藏索引仍然不可见，但是优化器在构建执行计划时将会考虑这些索引。

不可见索引特性可以用于测试删除某个索引对于查询性能的影响，同时又不需要真正删除索引，也就避免了错误删除之后的索引重建。对于一个大表上的索引进行删除重建将会非常耗时，而将其设置为不可见或可见将会非常简单快捷。

隐藏索引应用场景：软删除、灰度发布。

2.9 覆盖索引

在某些情况下，查询语句通过索引访问就可以返回所需的结果，不需要访问表中的数据（回表），此时我们把这个索引称为覆盖索引（Covering Index）。某些数据库中称之为Index Only Scan。

explain select emp_id, dept_id  from employee where dept_id = 5;

Name         |Value       |
-------------+------------+
id           |1           |
select_type  |SIMPLE      |
table        |employee    |
partitions   |            |
type         |ref         |
possible_keys|idx_emp_dept|
key          |idx_emp_dept|
key_len      |4           |
ref          |const       |
rows         |8           |
filtered     |100.0       |
Extra        |Using index |

此时的执行计划中Extra列显示Using index。

覆盖索引是优化器选择的一种执行计划；或者也可以说，任何索引在某种情况下都可能称为覆盖索引。

任何索引都包含了主键列，可用覆盖通过索引查找主键的查询语句。

2.10 索引和排序

MySQL数据排序可用通过 *filesort* 或者索引顺序扫描的方式实现。

EXPLAIN SELECT * FROM employee e ORDER BY emp_name;

Name         |Value         |
-------------+--------------+
id           |1             |
select_type  |SIMPLE        |
table        |e             |
partitions   |              |
type         |ALL           |
possible_keys|              |
key          |              |
key_len      |              |
ref          |              |
rows         |25            |
filtered     |100.0         |
Extra        |Using filesort|

EXPLAIN SELECT emp_id, emp_name FROM employee e ORDER BY emp_name;

Name         |Value       |
-------------+------------+
id           |1           |
select_type  |SIMPLE      |
table        |e           |
partitions   |            |
type         |index       |
possible_keys|            |
key          |idx_emp_name|
key_len      |202         |
ref          |            |
rows         |25          |
filtered     |100.0       |
Extra        |Using index |

MySQL索引即可以用于查询数据，也可以用于实现排序。前提是索引字段的顺序和ORDER BY子句字段的顺序完全一致（最左前缀原则）。

对于复合索引(col1, col2, col3)，可以用于优化以下查询：

WHERE col1 = val1 ORDER BY col2, col3
WHERE col1 = val1 ORDER BY col2 DESC
WHERE col1 BETWEEN val1 AND val2 ORDER BY col1, col2

但是无法使用该索引实现以下查询中的排序：

WHERE col1 = val1 ORDER BY col2 DESC, col3
WHERE col1 = val1 ORDER BY col3
WHERE col1 BETWEEN val1 AND val2 ORDER BY col2, col3

如果查询连接了多个表，只有ORDER BY子句字段全部属于第一个表时，才能利用索引进行排序。

2.11 重复索引和冗余索引

MySQL允许在相同的字段上按照相同的顺序创建多个相同类型的索引，也就是**重复索引**。这样会占用更多存储空间，也导致优化器需要进行更多的评估。

CREATE TABLE t (
  id INT AUTO_INCREMENT PRIMARY KEY,
  c1 INT UNIQUE, 
  c2 INT,
  INDEX idx_pk (id),
  INDEX idx1 (c1)
);

以上示例中的索引idx_pk和idx1都属于重复索引。

复合索引字段顺序不同，则不算重复索引。例如(col1, col2)和(col2, col1)不是重复索引。

索引类型不同，则不算重复索引。例如INDEX(col)和FULLTEXT INDEX(col)不是重复索引。

MySQL（InnoDB）自动为主键、唯一约束以及外键约束创建相应的索引。

**冗余索引**是指已经字段被其他索引包含的索引。

如果已经存在复合索引(col1, col2)，那么索引(col1)就是冗余索引，因为前者可用替代索引(col1)。不过需要注意，索引(col2)不是冗余索引，因为col2不是索引(col1, col2)的最左前缀列。

索引(col1, id)是一个冗余索引，因为辅助索引中一定会包含主键字段。

一般建议基于已有的索引进行扩展，而不是不断增加新的冗余索引，但是也存在例外。

重复索引和冗余索引的处理方法就是删除索引，但是删除之前需要确认不会产生副作用。MySQL 8.0可用利用不可见索引特性减少影响。

另外，可能会存在从未使用过的索引，通过系统视图sys.schema_unused_indexes查看，建议确认后删除。

2.12 索引和DML

索引不仅会对查询产生影响，对数据进行插入、更新和删除操作时也需要同步维护索引结构。

**INSERT语句**

对于INSERT语句而言，索引越多执行越慢。插入数据必然导致增加索引项，这种操作的成本往往比插入数据本身更高，因为索引必须保持顺序和B+树的平衡（索引节点拆分）。因此，优化插入语句的最好方法就是减少不必要的索引。

没有任何索引时的插入性能是最好的，因此在加载大量数据时，可以临时删除所有的索引并在加载完成后重建索引。

**UPDATE语句**

UPDATE语句如果指定了查询条件，可以通过索引提高更新操作的性能，因为通过索引可以快速找到需要修改的数据。

另一方面，UPDATE语句如果修改了索引字段的值，需要删除旧的索引项并增加新的索引项。因此，更新操作的性能通常也取决于索引的数量。为了优化UPDATE语句，频繁更新的字段不适合创建索引；同时应该尽量避免修改过多的字段。

**DELETE语句**

对于DELETE语句而言，如果指定了查询条件，可以通过索引提高删除操作的性能。因为它和UPDATE语句一样，需要先执行一个SELECT语句找到需要删除的数据。

删除操作涉及的索引更新和插入操作类似，只不过它是删除一些索引项并确保索引树的平衡。因此，索引越多删除性能越差。不过有一个例外就是没有任何索引，这个时候性能会更差，因为数据库需要执行全表扫描才能找到需要删除的数据。

2.13 索引设计原则

> 推荐图书：《数据库索引设计与优化》

三星索引：

索引将相关的数据存储在一起，减少需要扫描的数据量，获得一星；
索引中的数据顺序和查询排序顺序一致，避免排序操作，获得二星；
索引包含了查询所需的全部字段，避免随机IO，获得三星。

CREATE TABLE t (
  id INT AUTO_INCREMENT PRIMARY KEY, 
  c1 INT,
  c2 INT,
  INDEX idx1 (c1, c2)
);

EXPLAIN SELECT * FROM t WHERE c1>100 ORDER BY c1, c2;

Name         |Value                   |
-------------+------------------------+
id           |1                       |
select_type  |SIMPLE                  |
table        |t                       |
partitions   |                        |
type         |index                   |
possible_keys|idx1                    |
key          |idx1                    |
key_len      |10                      |
ref          |                        |
rows         |1                       |
filtered     |100.0                   |
Extra        |Using where; Using index|

既然索引可以优化查询的性能，那么我们是不是遇到性能问题就创建一个新的索引，或者直接将所有字段都进行索引？显然并非如此，因为索引在提高查询速度的同时也需要付出一定的代价：