• 创建索引
  • 哈希索引和B+树索引
  • 聚集（聚簇）索引和非聚集索引
  • 联合索引与最左前缀原则
  • 覆盖索引
  • 查询性能
  • EXPLAIN 命令
  • 优化数据访问
  • 重构查询方式
  • 分布式
  • 主从复制
  • 读写分离
  • 应用

本文出现的部分 sql 语句的数据库表设计详见 github。

没有特别说明，以下内容均针对 MySQL 数据库 InnoDB 存储引擎。

 

创建索引

《高性能 MySQL》一书中提到，除了让服务器快速定位到表的指定位置，索引还有以下三个优点：

  • 大大减少服务器需要扫描的数据量
  • 帮助服务器避免排序和临时表
  • 将随机 I/O 变为顺序 I/O

对于中大型表，索引是非常有效的。（当单个数据库或单个表的数据量继续不断增长时，还需要考虑分库分表的问题。）

但是从另一方面看，创建和维护索引需要时间，存储索引需要额外的空间，对于非常小的表，大部分情况下简单的全表扫描更高效。

总之，索引并非越多越好，正确地创建和使用索引才是实现高性能查询的基础。

InnoDB 和 MyISAM 的其中一个不同点是， InnoDB 支持行锁，MyISAM 只支持表锁。实际上行级锁的基础也是索引，而为某一行加锁其实是为索引加锁。在 InnoDB 引擎中，当其它查询、修改通过索引找到这一行时，发现索引被上锁，就会等待。反之，进行 like 查询、整表查询或是任何用不到索引的操作时，不会使用行级锁，而是使用表级锁。

哈希索引和B+树索引

哈希索引

哈希索引的时间复杂度为 O(1)，检索效率非常高，但是存在以下缺点：

  • 仅仅满足 =、in 和 <=> 查询，不能进行范围查询
  • 无法利用索引完成排序
  • 联合索引中，不能利用部分索引键查询
  • 任何时候都不能避免表扫描，因为键值对的值保存行指针，始终需要从表中访问完整行数据
  • 如果存在大量重复键，容易产生哈希碰撞

哈希索引只适用于等值查询的情况。

InnoDB 不支持哈希索引，但支持自适应哈希索引。InnoDB 为热点数据建立哈希索引，让其在 O(1) 的时间内就可以访问到。自适应哈希索引由存储引擎自己创建和使用，用户不能对其进行干预。

B+树索引

B+树在B树的基础上有一些变化：首先是所有非叶子结点只作为中间结点，不存放直接的数据（相当于索引），所有数据存放在叶子结点上；其次叶子结点中的所有数据构成双向链表结构，每两个节点之间都有前后指针，可以实现双向查找。

B+树相对于B树的优点显著：由于中间节点存储索引而非完整数据，所以容纳的关键字比B树要多，同样的数据量下，B+树更“矮胖”，查询需要的I/O次数更少，磁盘读写代价更低；每一次查询都是从根节点到叶子结点的过程，由于所有的叶子结点都在同一高度，所以查询的时间复杂度很稳定；除此之外，B+树的叶子结点是双向链表结构，对于范围查询，直接左右遍历即可，效率很高。

B+ 树索引是大多数 MySQL 存储引擎的默认索引类型。

聚集（聚簇）索引和非聚集索引

聚集索引

聚集索引是按照每张表的主键构造一棵 B+ 树，叶子结点中存放的即为整张表的行记录数据。聚集索引实际上是在同一个结构中保存了索引和数据行两个部分。

每张表只有一个聚集索引，如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式地定义一个主键来作为聚集索引。

InnoDB 引擎中使用自增的主键有助于提高性能。因为自增的主键在插入时只需要添加到最末尾就可以了，而如果插入新行的主键位于中间，或者被更新导致需要移动行时，可能面临“页分裂”的问题。按主键值要求必须将行插入到某个已满的页中，会将页面分成两部分。“页分裂”会导致数据存储不连续，且占用更多磁盘空间。

非聚集索引

叶子结点中不包含行记录的数据，也不包含指向行物理位置的指针，而是存储相应行的聚集索引键。由于非聚集索引的存在不影响聚集索引和数据，所以每张表可以包含多个非聚集索引。

通过非聚集索引查找数据时，首先遍历非聚集索引并获取叶子结点上的主键，再通过该主键值在聚集索引上查找，找到完整的行记录。每一次的非聚集索引访问都是两次索引查找，而不是一次。

和 InnoDB 的聚集索引不同的是，MyISAM 的数据和索引是完全分开的，MyISAM 的主键索引、辅助索引等均可看成非聚集索引。它的叶子结点存储的不是主键值，也不是数据行，而是指向数据行的指针。不管是走主键索引还是非主键索引，在叶子节点得到的都是数据的地址，还需要通过该地址，才能在数据文件中找到行数据。所有的查找都需要一次遍历和一次地址访问。

联合索引与最左前缀原则

对多个列创建联合索引时遵循最左前缀原则，即检索数据时，从最左边的字段开始匹配。

例如建立 (col1, col2, col3) 索引时，实际上建立了 3 个索引，分别是 (col1), (col1, col2), (col1, col2, col3)。

SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”

执行上面的 sql 语句时，将会使用索引 (col1, col2) 进行数据匹配。

SELECT * FROM test WHERE col1=“1” AND clo2=“2”
SELECT * FROM test WHERE col2=“2” AND clo1=“1”

以上两个语句都会用到索引 (col1, col2) ，索引字段的顺序是任意的。

SELECT * FROM test WHERE col2=2;

上面的语句也会用到索引，只不过不是通常意义上的“索引”。MySQL 会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个联合索引的一部分，MySQL 都可能会采用 index 类型的方式扫描。但是呢效率不高，MySQL 会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。

不止是上面提到的情况，只要是查询条件不包括首列字段，都不会使用联合索引。

还有，当 范围查询 使用在第一列（如 col1 > 1）上时，联合索引只能使用第一列，当范围查询使用在第二列（如 col1 = 1 and col2 > 2）上时，联合索引只能使用前两列。使用联合索引对某个字段 排序 和对某字段范围查询差不多。

联合索引的 索引列顺序 会影响索引的效率。索引效率依赖于使用该索引的查询，同时需要考虑更好地满足于排序和分组的需要。联合索引意味着索引最先按照最左列进行排序，然后是第二列… 以此类推。一般情况下，把选择性较高的索引放在前面。

索引的 选择性 指的是不重复的索引值（基数）与表记录数的比值，显然选择性的取值范围为(0, 1]。选择性越高的索引价值越大，对于单列索引也是如此。如果一个索引的选择性很低，那根本就没有建索引的必要。

有一种与索引选择性有关的索引优化策略叫 前缀索引，就是用列的前缀代替整个列作为索引 key，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引 key 变短而减少了索引文件的大小和维护开销，其缺点是不能用于 ORDER BY和 GROUP BY 操作，也不能用于覆盖索引。

使用联合索引有以下好处：

  • 减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。
  • 覆盖索引。下文会解释。
  • 效率高。索引列越多，通过索引筛选出的数据越少。如索引 (col1,col2,col3)，执行 sql 语句 “select from table where col1=1 and col2=2 and col3=3”，如果分成 3 个单值索引，将要筛选 3 次，而联合索引只需要筛选一次即可。

覆盖索引

覆盖索引是一个概念，而不是一种具体的索引类型。

从非聚集索引中就可以得到想要查询的数据，而不需要再次在聚集索引中查询，称为覆盖索引。除了不需要二次查询之外，由于非聚集索引不包含整行数据，因此会极大减少 I/O 操作，而其大小也远远小于聚集索引。

覆盖索引必须覆盖整个查询涉及的字段，否则依然会回表获取完整数据。

如果表 test 中有联合索引 (col1, col2)，执行如下查询：

SELECT COUNT(*) FROM test

将会选择非聚集索引，不会选择聚集索引，因为非聚集索引的数据量远小于聚集索引，可以大大减少 I/O 操作。

就算是对列 col2 执行如下语句：

SELECT COUNT(*) FROM test WHERE col2 > 0

仅仅是统计个数，也可以利用到覆盖索引。

由此看出，聚集索引并不一定比非聚集索引好，在上述例子中，针对统计操作而言，实际上非聚集索引比聚集索引效率更高。

索引失效

  1. 如果条件中有 or，必须将条件中每个列都加上索引。加上索引后，实际上是将每个索引查到的结果取并集（union）。
  2. 联合索引的最左前缀未匹配。
  3. like 语句以 % 开头。
  4. 需要类型转换。
  5. where 的条件中有运算、函数等等。
    …

以上列举的是客观上不选择索引的一部分情况。

另外还有很多特殊情况下，优化器判定不需要使用索引时，也不会使用索引。例如在表中数据量很小时，使用全表扫描。

或者以下例子：

SELECT * FROM test WHERE col2 > 100 AND col2 < 1000000

表中存在主键 col1 和单列索引 col2，此时并不一定会使用索引 col2，原因是用户要选取的是整行数据，如果使用索引 col2，还需要在聚集索引中二次查找。而重点就是，在此过程中，显而易见二次查找需要在磁盘中离散读，当数据量较大时，使用聚集索引的顺序读远远快于离散读，所以优化器会选择聚集索引，而不是列 col2 上的辅助索引。

当然这只是理论情况，对于不同的优化器和硬件（磁盘），将会出现不同的结果。此时还需要根据 explain 命令的情况具体分析。

LIKE 语句以 % 开头

LIKE 语句以 % 开头时，索引并不一定失效。

以下测试环境为 MySQL 8.0，测试程序来源 索引法则–LIKE以%开头会导致索引失效进而转向全表扫描（使用覆盖索引解决）。

建表如下，并在表中插入 100000 条数据：

DROP TABLE IF EXISTS staff;
CREATE TABLE IF NOT EXISTS staff (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(50),
    age INT,
    pos VARCHAR(50),
    salary DECIMAL(10,2)
);

建立联合索引：

CREATE INDEX idx_name_age_pos ON staff(name, age, pos);

考虑以下 sql 语句：

EXPLAIN SELECT age FROM staff WHERE name LIKE '%l%';
EXPLAIN SELECT age FROM staff WHERE pos LIKE '%R';
EXPLAIN SELECT * FROM staff WHERE name LIKE '%l%';
EXPLAIN SELECT age FROM staff WHERE name LIKE 'Al%';
EXPLAIN SELECT age FROM staff WHERE pos LIKE 'H%';
EXPLAIN SELECT * FROM staff WHERE name LIKE 'Al%';

第一条和第二条语句的 EXPLAIN 结果中主要属性为：type=index，key=idx_name_age_pos，extra=using index;using where。说明查询不会全表扫描。由于覆盖索引包含了所要查询的所有字段，所以只会扫描覆盖索引的数据。

第三条语句的 EXPLAIN 结果中显示 type=all，将会全表扫描。

第四条语句的 EXPLAIN 结果中主要属性为 type=range，key=idx_name_age_pos，extra=using index;using where。由于 like 语句不是以 % 开头，且覆盖索引包含了要查询的所有列，查询时将会利用覆盖索引进行范围查询。

第五条语句不符合最左前缀匹配原则，和第二条语句的结果一样。

最后一条语句的 EXPLAIN 结果中主要属性为 type=all，extra=using where。也会使用全表扫描。

 

查询性能

EXPLAIN 命令

EXPLAIN 只是近似结果，别无其他。有时候它是一个很好的近似，有时候可能与真相相差甚远。

EXPLAIN 只能解释 SELECT 查询，不会对存储过程调用和 INSERT、UPDATE、DELETE 或其他语句做解释。可以重写某些非 SELECT 查询以利用 EXPLAIN。

EXPLAIN 中包含以下几列：

  • id 列：查询语句编号，多个子查询（派生表或 UNION 查询）按顺序编号
  • select_type 列：SIMPLE 表示简单查询；DERIVED 表示 FROM 子句中的 SELECT；UNION 表示 UNION 中第二个和随后的 SELECT；UNION RESULT 表示从 UNION 的匿名临时表检索结果的 SELECT
  • table 列：正在访问的表
  • type 列：all 表示全表扫描；index 表示遍历索引；range 表示有限制的索引扫描，即范围扫描，典型的范围扫描是带有 BETWEEN 或 WHERE 里带有大于小于的语句，IN 和 OR 也会显示 range；ref 表示索引查找，返回所有匹配单个值的行；eq_ref 表示最多返回一条记录的索引查找；const、system 主键查找，system只有一行；NULL 表示效率最高，甚至不用回表扫，例如从索引中选取最小值。
  • possible_keys 列：可能用到的索引
  • key 列：决定采用的索引
  • key_len 列：索引里使用的字节数
  • ref 列：在 key 列记录的索引中查找所用的列或常量
  • rows 列：要读取行数的估计值
  • filter 列：符合某个条件的记录数的百分比估计
  • Extra 列：其他信息。Using index 表示将使用覆盖索引

优化数据访问

  • 确认应用程序是否检索太多超出需要的数据行：
  • MySQL 是先返回结果集再计算，所以应当只返回必要的行，最简单有效的方法是在查询后加上 LIMIT
  • 只查询需要的列，尽量减少使用 SELECT *
  • 重复查询相同的数据时，善用缓存
  • 确认 MySQL 服务器层是否扫描了太多数据行：
  • 合理使用索引

重构查询方式

  • 分解复杂查询和关联查询。使用多个简单查询的好处包括：让缓存效率更高、减少锁竞争、在应用层关联使程序可扩展性高、减少冗余记录查询等。
  • 切分大查询为多个小查询，每次只完成大量数据的一小部分。

 

分布式

主从复制

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

dm查询索引_服务器

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

  • 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
  • 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
  • 增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

dm查询索引_dm查询索引_02

此小节完全引用自 CS-Note 数据库。

 

应用

本文依照的 “秒杀系统” 中的 sql 语句基本上是按聚集索引直接查询，例如下面的查询语句：

select * from miaosha_user where id = ?

其功能是根据用户 id 查询单个秒杀用户的信息，在查询时，将直接使用聚集索引的叶子结点获取数据行，而且是单个数据行，效率很高，不需要优化。

以下两个 sql 语句类似：

select * from miaosha_goods where id = ?
select * from order_info where id = ?

其功能分别是根据秒杀商品 id 查询秒杀商品信息，根据订单 id 查询订单信息。需要用到的两个表均设置 id 为主键。

对于语句：

select * from miaosha_goods where start_date >= ? and start_date <= ?

此 sql 语句的功能是查询指定时间范围内的秒杀商品信息，where 之后只用到了 start_date 一个字段，为其建立索引即可。

对于语句：

select * from miaosha_order where user_id = ? and goods_id = ?

秒杀订单表的主键是订单 id，此 sql 语句时根据用户 id 和 商品 id 查询订单信息。显而易见地，在 user_id 和 goods_id 两个字段上建立联合索引将会大大提高效率。（此表和这两列相关的没有其他查询语句，暂且以 user_id 为最左前缀。）

相对来说较麻烦的是下面的 sql 语句：

select g.*, mg.stock_count, mg.start_date, mg.end_date, mg.miaosha_price from miaosha_goods mg left join goods g on mg.goods_id = g.id where g.id = ?

涉及到连接操作。此连接操作的执行顺序一次为 FROM 、ON、JOIN、WHERE，先把两个表连接并缓存，然后才执行 WHERE 过滤操作。虽然连接查询的好处是能直接获取到所有信息，但是实际情况中若合并的表太大，效率会非常低。最终选择将上面的 sql 语句拆分成以下两个语句：

select * from goods where id = ?
select * from miaosha_goods where id = ?

然后在应用层把两次查询的结果合并起来。

 

参考

  • 姜承尧. MySQL技术内幕: InnoDB存储引擎（第2版）[M]. 机械工业出版社. 2013.
  • 施瓦茨, PeterZaitsev, VadimTkacbenko, et al. 高性能MySQL[M]. 2010.
  • 【MySQL笔记】正确的理解MySQL的索引机制以及内部实现（二）
  • Mysql联合索引最左匹配原则
  • CS-Note 数据库