性能优化专题 - MySql 性能优化 - 01 - MySql索引机制

前言

性能优化专题共计四个部分，分别是：

• Tomcat 性能优化
• MySql 性能优化
• JVM 性能优化
• 性能测试

本节是性能优化专题第二部分 —— MySql 性能优化篇，共计四个小节，分别是：

1. MySql索引机制
2. MySql运行机理
3. 深入理解InnoDB
4. MySql调优

本节重点：

➢ 索引是谁实现的
➢ 索引的定义
➢ 为什么选择B+Tree
➢ B+Tree在两大引擎中如何体现

MySql的体系结构

关于上图这些组件，其功能大体如下，这里简单做一个了解:

• Connectors：接入方支持协议很多

• Management Serveices & Utilities：备份恢复，mysql复制集群等

• Connection Pool：管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求

• SQL Interface：接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

• Parser：SQL命令传递到解析器的时候会被解析器验证和解析。

• Optimizer：SQL语句在查询之前会使用查询优化器对查询进行优化

• Cache和Buffer：如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据

• Pluggable Storage Engines：存储引擎是MySql中具体的与文件打交道的子系统。Mysql的存储引擎是插件式的。

• File System：数据、日志（redo，undo）、索引、错误日志、查询记录、慢查询等

那么MySql 运行时机理是怎样的？

MySql索引机制

说到MySql的索引机制，必要提一下MySql的存储引擎：Mysql在V5.1版本之前默认存储引擎是MyISAM；在此之后默认存储引擎是InnoDB。那么这二者之间又有怎样的联系呢，首先我们说下常见的与树的数据结构：

数据结构之树

与树相关的数据结构知识，我这里给大家提供了 二叉树、平衡二叉树 、红黑树、B+树以及二叉树遍历算法相关总结，在此转载声明！如果同学们不了解这里的知识点，更需要先掌握才能掌握，以便理解后续的MySql索引机制。

1. 二叉树遍历算法【转载】

• 前序遍历：根-左-右。
• 中序遍历：左-根-右。
• 后序遍历：左-右-根。

2. 二叉树与平衡二叉树【转载】

二叉树的缺陷：

• 顺序存储可能会浪费空间(在非完全二叉树的时候)，但是读取某个指定的节点的时候效率比较高O(0)
• 链式存储相对二叉树比较大的时候浪费空间较少，但是读取某个指定节点的时候效率偏低O(nlogn)

3. 平衡二叉树与红黑树【转载】

红黑树是一种弱平衡二叉树

4. B树与B+树【转载】

B+树与B树的区别：

• B+节点关键字搜索采用闭合区间
• B+非叶节点不保存数据相关信息，只保存关键字和子节点的引用
• B+关键字对应的数据保存在叶子节点中
• B+叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系

5. 红黑树,B树,B+树 本质区别及应用场景【转载】

叶子结点是离散数学中的概念。一棵树当中没有子结点（即度为0）的结点称为叶子结点，简称“叶子”。 叶子是指出度为0的结点，又称为终端结点。

int leaf(BiTree root){
	static int leaf_count = 0; --->在递归调用时只进行一次初始化。
	if (NULL != root) {
		leaf(root->lchild);
		leaf(root->rchild);
	if (root->lchild == NULL & root->rchild == NULL)
		leaf_count++;
	}
	return leaf_count;
}

二叉查找树，Binary Search Tree

每个分支最多有两个（路）

平衡二叉查找树，Balance binary search tree

平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。

先了解下磁盘的相关知识。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

X < 10 --> P1
X = 10 --> 命中
X > 10 --> P2

一个磁盘块：
​

• 绿色：关键字 （这里关键字就比如是id = 10）
• ​蓝色：数据区（比如图上为 id 为 10 整条记录 id 、name 、age 、 phoneNum）
• 粉色：P1 P2 子节点的引用

模拟查找关键字id = 8的过程：

1. 从根节点开始找磁盘块1，读入内存， 发现 8 < 10 --> P1 ，一次IO操作
2. 此时P1指向磁盘块2，读入内存， 发现 8 > 5 --> P2， 一次IO操作
3. 此时P2指向磁盘块5，读入内存， 发现 8 = 8，命中 ，一次IO操作

（这里的IO操作理解为读取一个磁盘块，包括关键字、数据区、子节点引用）

上面索引到id为8的记录，需要3次IO操作、3次内存查找。毕竟上面是举例子，假如我们的老师teacher表数据量非常大，那id就很多，这样这个平衡二叉树的高度就很大，假如很不幸我们要找的id = 8的记录，在这颗树最大的高度，那这样的命中索引，需要很多次IO操作。

可想而知，这种索引的规则太随机，MySQL肯定不是采用的这样

小结：

• 它太深了
  ​ 数据处的（高）深度决定着他的IO操作次数，IO操作耗时大
• 它太小了
  ​ 每一个磁盘块（节点/页）保存的数据量太小了
  ​ 没有很好的利用操作磁盘IO的数据交换特性，
  ​ 也没有利用好磁盘IO的预读能力（空间局部性原理），从而带来频繁的IO操作

多路平衡查找树，B-Tree

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

绝对平衡树

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

X < 17 --> P1
X = 17 命中
17< X < 35 --> P2
X = 35 命中
X > 35 --> P3

模拟查找关键字的过程：(省略)

B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

加强版多路平衡查找树 B+Tree

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

MySQL的B+Tree

左闭合B+Tree：

1 <= X < 28 --> P1
28 <= X < 66 --> P2
66 <= X --> P3

从B树结构图中可以看到每个节点中不仅包含数据的key值（关键字，子节点引用），还有data值（一整条记录的磁盘地址）。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

索引

什么是索引？

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构

正确的创建合适的索引，是提升数据库查询性能的基础

索引的分类？

• 单列索引
• 联合索引

单列索引是特殊的联合索引

联合索引选择原则？

1. 经常用的列优先【最左匹配原则】
2. 选择性（离散度）高的列优先【离散度高原则】
3. 宽度小的列优先【最小空间原则】

索引的优点?

• 极大的减少存储引擎需要扫描的数据量
• 可以把随机IO变成顺序IO
• 在分组排序时使用索引，可以避免使用临时表

索引的使用

关于索引的基础用法，这里不再赘述，如果还不会使用索引的同学可以参考一下：

mysql索引的使用【转载】

MySql索引默认的数据结构

前面我们了解了B树相关的数据结构，以及MySql索引的基本使用情况，那么MySql为什么会选择B+树这样的数据结构作为它的索引机制呢？

我们使用在线数据结构解析工具，查看二叉树的排列情况：

若仅仅是 select * from table where id=45 , hash算法可以轻易实现，但若是select * from table where id<6 , 就不好使了，它们的查找方式就类似于"全表扫描"，因为他们的高度是不可控的(如上图)。B+Tree的高度是可控的，mysql通常是3到5层。注意：B+Tree只在最末端叶子节点存数据，叶子节点是以链表的形势互相指向的。

• B+树扫库、表能力更强
• B+树的磁盘读写能力更强
• B+树的排序能力更强
• B+树的查询效率更加稳定

MySql B+Tree索引体现形式

MyISAM引擎

MyISAM是默认存储引擎（Mysql5.5前）。它基于更老的ISAM代码，但有很多有用的扩展。

每个MyISAM在磁盘上存储成三个文件，每一个文件的名字均以表的名字开始，扩展名指出文件类型。

• .frm文件存储表定义；

• ·MYD (MYData)文件存储表的数据；

• .MYI (MYIndex)文件存储表的索引。
  

若以这个引擎创建数据库表Create table user （…..），它实际是生成三个文件：

• user.myi ：索引文件
• user.myd： 数据文件
• user.frm ：数据结构类型，文件存储表定义。

如下图：当我们执行 select * from user where id = 1的时候，它的执行流程。

1. 查看该表的myi文件有没有以id为索引的索引树。

2. 根据这个id索引找到叶子节点的id值，从而得到它里面的数据地址。(叶子节点存的是索引和数据地址)。

3. 根据数据地址去myd文件里面找到对应的数据返回出来。

MyISAM的索引与行记录是分开存储的，叫做非聚集索引（UnClustered Index）。

其主键索引与普通索引没有本质差异：

• 有连续聚集的区域单独存储行记录
• 主键索引的叶子节点，存储主键，与对应行记录的指针
• 普通索引的叶子结点，存储索引列，与对应行记录的指针

画外音：MyISAM的表可以没有主键。

主键索引与普通索引是两棵独立的索引B+树，通过索引列查找时，先定位到B+树的叶子节点，再通过指针定位到行记录。

Innodb引擎

InnoDB，是MySQL的数据库引擎之一，为MySQL AB发布binary的标准之一。InnoDB由Innobase Oy公司所开发，2006年五月时由甲骨文公司并购。与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能，类似于PostgreSQL。

每个InnoDB在磁盘上存储成2个文件，每一个文件的名字均以表的名字开始，扩展名指出文件类型。
若以这个引擎创建数据库表Create table user （…..），它实际是生成两个文件：

• user.ibd ： 索引文件，数据与索引文件
• user.frm ：数据结构类型，文件存储表定义

因为innodb引擎创建表默认就是以主键为索引，所以不需要myi文件

百度百科-InnoDB

很显然它与myisam最大的区别是将整条数据存在叶子节点，而不是地址。(叶子节点存的是主键索引和数据信息)

若此时，你在其他列创建索引例如name，它就会另外创建一个以name为索引的索引树，(叶子节点存的是索引和主键索引)。

你在执行select * from user where name = ‘zhangsan’，他的执行过程如下：

1. 找到name索引树

2. 根据name的值找到该树下叶子的name索引和主键值

3. 用主键值去主键索引树去叶子节点到该条数据信息

MySQL的Innodb是以主键索引来组织数据结构的，主键索引与行记录是存储在一起的，故叫做聚集索引（Clustered Index）

因为这个特性，InnoDB的表必须要有聚集索引：

1. 如果表定义了PK，则PK就是聚集索引；
2. 如果表没有定义PK，则第一个非空unique列是聚集索引；
3. 否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

InnoDB的聚集索引，也只能够有一个，因为数据行在物理磁盘上只能有一份聚集存储。
InnoDB的普通索引，可以有多个，它与聚集索引是不同的。

InnoDB存储引擎中页的大小默认为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3 ）。也就是说一个深度为3的B+Tree索引可以维护103 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在24层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要13次磁盘I/O操作。

数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

MyISAM引擎和InnoDB引擎的区别

• MyISAM：支持全文索引;不支持事务;它是表级锁;会保存表的具体行数.
• InnoDB：5.6以后才有全文索引;支持事务；它是行级锁;不会保存表的具体行数.
  

Innodb: 假如你频繁的修改、更新辅助索引，主键索引是不需要重排序的

Myisam：主键索引和辅助键索引无区别

不用事务的时候,count计算多的时候适合myisam引擎。对可靠性要求高就是用innodby引擎。推荐用InnoDB引擎.

加了索引之后能够大幅度的提高查询速度，但是索引也不是越多越好，一方面它会占用存储空间，另一方面它会使得写操作变得很慢。通常我们对查询次数比较频繁，值比较多的列才建索引。

例如：select * from user where sex = "famale"， 这个就不需要建立索引，因为性别一共就两个值，查询本身就是比较快的。
select * from user where user_id = 1995 ,这个就需要建立索引，因为user_id的值是非常多的。

索引相关

避免冗余索引

冗余索引指的是索引的功能相同，能够命中 就肯定能命中 ，那么 就是冗余索引如（name,city ）和（nam个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有不是创建新索引。

MySQL5.7 版本后，可以通过查询 sys 库的 schemal_r dundant_indexes 表来查看冗余索引
参考wiki

Mysql如何为表字段添加索引？

1. 添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2. 添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3. 添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4. 添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5. 添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3`)

覆盖索引

如果查询列可通过索引节点中的关键字直接返回，则该索引称之为覆盖索引，覆盖索引可减少数据库IO，将随机IO变为顺序IO，可提高查询性能

create index idx_name_phoneNum on tbl_user(name,phoneNum);

select name,phoneNum from tbl_user where name = "张三"；

在索引的子节点命中了 返回的列，就不需要再继续往B+Tree的底部叶子节点 读取数据了，减少了IO操作，提高了查询速度。

这就是为什么不建议大家select * from table 的原因，需要什么列就查什么列，可能会命中覆盖索引，这样就会极大的提高查询效率。

写在最后

本节Tips：

1. 索引列的数据长度能少则少。
2. 索引一定不是越多越好，越全越好，一定是建立合适的
3. 匹配列前缀可用到索引 like 999%，like %999%，like %999用不到索引
4. where条件中not in 和<> 操作无法使用索引
5. 匹配范围值，order by也可以用到索引
6. 多用指定列查询，只返回自己想到的数据里，少用select *
7. 联合索引中如果不是按照索引最左列开始查找，无法使用索引
8. 联合索引中精确匹配1最左前列并范围匹配另外一列可以用到索引
9. 联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引

更多架构知识，欢迎关注本套系列文章：Java架构师成长之路