阿里巴巴Java手册——MySQL数据库索引规约理解

1. 【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
   说明： 不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的； 另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

2. 【强制】超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致； 多表关联查询时，保证被关联的字段需要有索引。
   说明： 即使双表 join 也要注意表索引、 SQL 性能。

3. 【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。
   说明： 索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

索引基数cardinality=count(distinct 列名),索引的选择性=cardinality/count(*),这个数值通常在0~1之间，越接近1，查询效率越高，因为越接近1的时候，innodb引擎可以过滤的更多的行。而长度很长的varchar列会使索引变得很大且很慢，如果选择该列的部分左前缀，可以使索引选择性接近1，可以仅选择一定长度的左前缀进行索引。

4. 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
   说明： 索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

b+树就是最左前缀匹配

5. 【推荐】如果有 order by 的场景，请注意利用索引的有序性。 order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
   正例： where a=? and b=? order by c; 索引： a_b_c
   反例： 索引中有范围查找，那么索引有序性无法利用，如： WHERE a>10 ORDER BY b; 索引a_b 无法排序。

对于建一个表union_index_test（id,a,b,c）,在此表上建立一个联合主键（a_b_c）

• 以a和b作为where条件，order by c看一下执行计划：

mysql> explain select * from union_index_test where a ='aa' and b='bb' order by c;
+----+-------------+------------------+------+---------------+-----------+---------+-------------+------+--------------------------+
| id | select_type | table            | type | possible_keys | key       | key_len | ref         | rows | Extra                    |
+----+-------------+------------------+------+---------------+-----------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | union_index_test | ref  | uni_index     | uni_index | 18      | const,const |    3 | Using where; Using index |
+----+-------------+------------------+------+---------------+-----------+---------+-------------+------+--------------------------+
1 row in set (0.01 sec)

• 以a作为where条件，order by b 的执行计划：

mysql> explain select * from union_index_test where a='aa' order by b;
+----+-------------+------------------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table            | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+------------------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | union_index_test | ref  | uni_index     | uni_index | 9       | const |    7 | Using where; Using index |
+----+-------------+------------------+------+---------------+-----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

• 以a作为where条件，order by c 的执行计划：

mysql> explain select * from union_index_test where a ='aa' order by c;
+----+-------------+------------------+------+---------------+-----------+---------+-------+------+------------------------------------------+
| id | select_type | table            | type | possible_keys | key       | key_len | ref   | rows | Extra                                    |
+----+-------------+------------------+------+---------------+-----------+---------+-------+------+------------------------------------------+
|  1 | SIMPLE      | union_index_test | ref  | uni_index     | uni_index | 9       | const |    7 | Using where; Using index; Using filesort |
+----+-------------+------------------+------+---------------+-----------+---------+-------+------+------------------------------------------+

可以看到extra里面额外利用了filesort进行排序。

6. 【推荐】利用覆盖索引来进行查询操作， 避免回表。
   说明： 如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。
   正例： 能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用 explain 的结果， extra 列会出现： using index。

覆盖索引指的是要查询的数据列都包含在索引中。
还是对于表tt（id,a,b,c）,在此表上建立一个联合主键（a_b）
当要返回所有列的时候，extra列没有using index。

mysql> explain select * from tt where a='aa' and b='bb';
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | tt    | ref  | tt_a_b        | tt_a_b | 14      | const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

如果只返回索引里面的列， extra 列出现： using index。如果返回索引里的列和主键，也会返回using index，这是因为二级索引的叶子节点是有主键列的。

mysql> explain select a,b  from tt where a='aa' and b='bb';
+----+-------------+-------+------+---------------+--------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref         | rows | Extra                    |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | tt    | ref  | tt_a_b        | tt_a_b | 14      | const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

7. 【推荐】利用延迟关联或者子查询优化超多分页场景。
   说明： MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。
   正例： 先快速定位需要获取的 id 段，然后再关联：SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

8. 【推荐】 SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好。
   说明：
   1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
   2） ref 指的是使用普通的索引（normal index） 。
   3） range 对索引进行范围检索。
   反例： explain 表的结果， type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大巫。

用explain来解释执行计划，type列指的是MySQL在表中找到所需行的方式。常见类型如下：

ALL	index	range	ref	eq_ref	const,system	NULL

从左到右，查找性能由差到好。

• ALL是全表扫描，MySQL要遍历全表来找到匹配的行
• index是索引全扫描，MySQL遍历整个索引来找到匹配的行
• range索引范围扫描，一般用于<、>、<=、>=、between操作
• ref使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配单独值的记录行
• eq_ref,唯一索引，对于每个键值，表中只有一行记录与之匹配
• const/system,单表只有一个匹配行，例如根据主键或者唯一索引记性的查询

9. 【推荐】建组合索引的时候，区分度最高的在最左边。
   正例： 如果 where a=? and b=? ， a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即
   可。
   说明： 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如： where a>?and b=? 那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。

建立组合索引时（a，b）时，索引会先按照a排序，再按照b排序

10.【推荐】 防止因字段类型不同造成的隐式转换， 导致索引失效。
11.【参考】创建索引时避免有如下极端误解：
1） 宁滥勿缺。 认为一个查询就需要建一个索引。
2） 宁缺勿滥。 认为索引会消耗空间、严重拖慢更新和新增速度。
3） 抵制惟一索引。 认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

一些基本知识（都是基于innodb存储引擎）

一、innodb引擎

1.1 InnoDB体系架构

image.png

后台线程

• Master Thread
  将缓存池中的数据异步刷新到磁盘
• IO Thread
  show engine innodb status \G;
  image.png 也可以用show variables like 'innodb_%io_threads' \G;命令查看
  image.png InnoDB一共有4种IO Thread：insert buffer、log 、write和read IO Thread
• Purge Thread
  事务被提交后，其所使用的undolog可能不再需要，因此需要Purge Thread来回收已经已经使用并分配的undo页。在InnoDB1.1版本之前，purge操作只能在Master线程完成，从1.1版本开始，可以用单独的线程处理。用户可以在配置文件中配置：

[mysqld]
innodb_purge_threads=1

从1.2版本之后，可以设置多个purge线程，可以加快undo页的回收。

• Page Cleaner Thread
  版本1.2.X中加入的，将之前版本中脏页的刷新独立到单独的线程中。

内存

• 缓冲池(Buffer Pool)
  缓冲池是主存中的一个区域，从中可以获取InnoDB缓存表和索引数据。缓冲池是的那些频繁使用的数据可以直接从内存中获取访问
  InnoDB存储引擎给予磁盘存储，将记录按照页的方式进行管理。由于CPU速度和磁盘速度不同，采取了缓冲池的技术来提高性能。为了提高高容量读取操作的效率，缓冲池被划分为页（page）的链表。

查看缓存池实例show variables like 'innodb_buffer_pool_instances' \G;

image.png
还可以通过information_schema库里面的表来看缓存池的状态
innodb_buffer_pool_stats表

LRU List、FreeList 和FlushList

缓冲池的使用情况

缓存池命中率：
（1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request）*100%

• ChangeBufer
  change buffer是一种特殊的数据结构，当要修改的页不在buffer pool中时，用来缓存对辅助索引页的修改。缓存起来的改变，可能是insert、update和delete操作，等到相关的页被其他的读进程读入buffer pool中后，才会进行merge操作。ChangeBuffer位于共享表空间，即ibdata文件，是物理页的组成部分。
  不同于聚集索引，二级索引通常都是非唯一的，向二级索引的插入操作相对比较随机。同样的，删除和更新操作经会影响在二级索引里面不相邻的页。等到受影响的页被其他操作读到缓冲池之后，再对缓存在changebuffer中的内容进行merge操作，可以避免大量的磁盘随机IO访问。周期性地，当系统处于空闲状态时运行的清除操作，或在缓慢关机期间，将更新后的索引页写入磁盘。
  在系统空闲或缓慢关闭过程中，会执行purge操作，将更新过的索引页写入磁盘。purge操作一次写多个索引值会比每次修改后就立即写入磁盘的效率高。
  如果有大量的二级索引要更新和大量受影响的行，那么change buffer的merge可能需要好几个小时。在merge过程中，磁盘的I/O会增加，可能会引起其他查询的性能的降低。
  merge操作也可能发生在事务提交后。事实上，即使在实例重启后，还会可能发生merge操作。
  在内存中，change buffer会占用buffer pool的空间；在物理磁盘上，change buffer是系统表空间的一部分，所以对索引的修改在数据库重启后仍然存在change buffer中。
  change buffer包含的特性也叫作change buffering，包含insert buffering、delete buffering、purge buffering。
• Adaptive Hash Index 自适应hash索引
  根据对查询模式的观测，如果某些索引页频繁被访问，MySQL基于索引键的前缀构建hash索引。
• Doublewrite Buffer二次写缓冲
  MySQL数据库的页默认是16K，但是linux文件系统的页默认是4k，在页向磁盘写的时候，可能会发生丢失。MySQL用两次写机制保证了数据的可靠性。
  doublewrite buffer是位于系统表空间的存储区域，在innodb将页写到磁盘正确位置之前，会将页缓冲到这个缓冲区。只有将页写到这个缓冲区之后，系统才会将页写到磁盘。如果在写入磁盘的过程中，发生了系统崩溃，后来恢复的时候也可以从系统表空间中恢复这部分页。虽然写了两次，但是磁盘IO的开销并不会增大一倍，因为Doublewrite buffer是一个连续的块，只需要对操作系统进行一次fsync()调用。

二、聚集索引和非聚集索引的结构

在innodb，表结构称之为索引组织表。innodb的索引的底层结构都是B+树，B+树包括叶子节点和非叶子节点。索引包括聚集索引和二级索引（secondary index），每个表只有一个聚集索引，是根据主键顺序存放的，聚集索引的叶子节点保存了行记录。mysql行由以下部分组成：

DB_TRX_ID	DB_ROLL_PTR	DB_ROW_ID	other_rows

• DB_TRX_ID :插入或者更新这个行的事务id，6字节
• DB_ROLL_PTR ：回滚指针，指向回滚日志记录rollback_segment（Tablespace包括leafnode segment、non-leaf node segment和rollback segment）中记录的undo 日志记录。undo日志记录包含了在rebuild数据更新前的这行记录。7字节。
• B_ROW_ID: 如果innodb引擎的表没有显示的创建主键，那么本列就是自动根据行插入增长的row_id，如果显示创建了主键，那么这列就不存在索引里面了。
• ps:行里面有一个单独的bit用来标志该行是否被删除。

二级索引的叶子节点保存了所有的索引字段以及主键，如果where条件是二级索引，那么先要通过二级索引定位到主键，再去聚集索引里面获取其他字段。

三、多版本并发控制和二级索引 InnoDB multiversion concurrency control(MVCC)

InnoDB存储结构

rollback segment里面的回滚（undo）日志分为delete日志和update日志。插入回滚日志只有事务回滚的时候才会被用到，而且可以在事务提交时丢弃。更新回滚日志可以在一致性读中使用，它们只有在所有事务都执行完毕后才可以删除，因为InnoDB在一致性读中分配了一个快照，需要根据update undo log中的信息重建一个更早版本的数据库的行。（Update undo logs are used also in consistent reads, but they can be discarded only after there is no transaction present for which InnoDB has assigned a snapshot that in a consistent read could need the information in the update undo log to build an earlier version of a database row.这个长句给我看哭了~~~）
所以要经常性的提交事务，包括哪些只有重复读的事件。否则，innodb就不能及时丢弃update重做日志，rollback segment就会占用太多表空间。
在innodb多版本模式下，行删除并不是执行sql语句后立即物理性地从数据库移除，innodb会在它丢弃了update undo log之后，物理性移动对应的行和他的索引记录。这个移除操作称为purge,他十分迅速，一般和sql 声明执行删除同时发生。

聚集索引的更新操作是在原始位置（in-place）操作的，他们指向旧版本记录的指针列可以重构。二级索引则不是这样，二级索引不含有隐藏的系统列更新操作也不是in-place的。
当一个二级索引列更新时，旧的二级索引记录会被标记为deleted，新的记录会被插入，然后删除的记录会产生purge操作。当二级索引被标记为删除或者二级索引页被新的的事务更新时，InnoDB在聚集索引中查找数据库记录。在聚集索引中，检查该记录的DB_TRX_ID（事务版本号），如果记录在这个读事务开始之后被修改了，可以从回滚日志中回复正确的记录版本。如果一个二级索引的记录被标志为已经删除，或者二级索引页被一个新的事务更新了，那么覆盖索引技术接没有用了，只能从聚集索引中获取数据而不能直接从二级索引结构中直接返回。

ICP

index condition pushdown（ICP）是MySQL5.6启用的新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。没有启用ICP，存储引擎遍历整个索引定位到基表中的行，然后将所有行返回至MySQL server层，server层根据where条件来定位行。当ICP启用时，如果经评估可以使用部分存在于索引列的where条件来查找，server层将这部分条件查询列下发到存储引擎层进行查找。然后，存储引擎通过使用索引项来检索pushed索引条件，并且只有行记录的值满足where条件时才将其返回至server层。ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数。
ICP使用条件：

• explain显示的执行计划中type值（join 类型）为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据，即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)。
• ICP可以用于innodb和MyISAM表，包括分区表。
• 在InnoDB表结构中，ICP只用于二级索引，ICP的目标是减少全表读取的数目以降低IO操作，对于聚集索引，完整的记录直接读到了InnoDB缓存中，所以不会减少I/O操作。

四、分区

创建一个数据库

建库
对应的文件夹下面自动创建了一个opt文件 空的数据库
创建一个分区表之后，数据库的表结构

create table partition_test01(
id int auto_increment,
birth_day datetime not null,
sex char(1),
primary key(id,birth_day))
engine=innodb default charset=utf8 
partition by range(TO_DAYS(birth_day))(
partition p20180417 values less than (TO_DAYS('2018-04-18')),
partition p20180418 values less than (TO_DAYS('2018-04-19'))
);

对应的文件夹下面的文件如下，可以看到数据库为每个分区创建了一个文件： 包含分区表的数据库目录文件