MySQL性能优化(二) 顶原

• B-Tree索引和Hash索引

如果数据比较少时，或者查询的频率比较低的时候，索引的作用并不明显。因为这时候表中的数据差不多都可以完全缓存在内存中。所以就算是进行全表扫描也不会太慢。

随着表中数据越来越多，查询频率越来越高，内存已经不能完全缓存所有数据的时候，索引的作用就会显得越来越重要

我们常见的两种极端的情况

1. 在表中除了主键没有任何的索引。
2. 给表中的每一列都建立一个索引。

MySQL的索引是在存储引擎层来实现的，而不是在服务器层来实现的。同一种类型的索引在不同的存储引擎，其底层的实现也可能不相同。

• B-Tree索引的特点

B-Tree索引是以B+树的结构存储数据。

有关B树、B+树的内容可以参考数据结构整理 里红黑树一节。我们只要知道它是一颗绝对平衡的查找树。

其实2-3树就是一个3阶B树，还有2-3-4树属于4阶B树。而m阶的B树特性

    1.如果根节点不是叶子节点那么至少有两个子树。

　　2.所有叶子节点都位于同一层。

　　3.节点包含：关键字数组，指向孩子节点的指针数组，关键字数量。

以上也说明B树是一颗绝对平衡树。

而对于我们数据库mysql索引使用的B+树在B树之上做了修改

B+树：由于B树进行遍历的时候效率太低，而对于数据库和文件系统来说会经常进行范围查询，所以产生了B+树。

B+树可以看作是信息都是在叶子节点上，其他非叶子节点都是索引，目的是找到叶子节点，每个非叶子节点都保存叶子节点最小值及最小值所在叶子节点的索引，并且叶子节点之间有指针指向。

区别：

1.功能上说，B+遍历，范围查询效率高。

2.结构上说：B+信息都保存在叶子节点上，其他节点保存最小值索引，并且关键字对应的地址都在叶子节点上，而B树中非叶子节点也保存关键字对应的地址。

节点结构：B树的性质B+树一般都满足。

　　B树：

　　　　关键字数组，关键字对应的地址数组 子节点的指针数组，关键字的数量（子节点的最小数量是阶数的二分之一）

　　B+树：

　　　　关键字数组，关键字数量，子节点的指针数组。（每个节点关键字数量和子节点数量相同，并且每个关键字都是对应一个子节点关键字的最小值）

原理虽然相同，但对于不同存储引擎来说，具体的实现可能会有所不同，对于MyISAM来说，在叶子结点上是通过数据的物理地址来进行引用行的。而Innodb则是通过主键来引用索引的行的。

1、B-Tree索引能够加快数据的查询速度。

从索引的根节点，根节点存储了下层的指针，根据这些指针向下层进行查找。通过比较节点叶的值和要查找的值，再得到合适的指针进入下层的子节点。而这些指针是定义了子节点值的上限和下限。所以最终存储引擎要找到对应的值，要么就确定所查找的值是不存在的，要么存储引擎会通过B-Tree索引找到符合要求的节点。叶子节点是索引指针指向索引的数据，而不是其他的叶子结点。

2、由于B-Tree索引对数据是顺序存储的，B-Tree索引更适合进行范围查找，这一点和Hash索引有很大的不同。

B-Tree索引的使用

1、全值匹配的查询

order_no='9878987' 建立单独列索引。

2、匹配最左前缀的查询

如果一个联合索引的第一列(最左索引)，比如order_no跟date组成了一个联合索引，那么order_no='9878987'的查询同样可以使用该联合索引。但是对于date='2016-09-01'的查询，该联合查询索引将无法使用。

3、匹配列前缀查询

order_no like '9876%'可以使用到前面的order_no建立的单独索引，也可以使用第二种情况的联合索引。

4、匹配范围值的查询

order_no > '9878987' and order_no < '9879131'

5、精确匹配左前列并范围匹配另外一列

比如在第二种情况下的联合索引中，我们可以精确匹配order_no而范围查找date

order_no='9878987' and date > '2020-03-01' and date < '2020-03-05'

6、只访问索引的查询

比如我们在分页中，如果数据量比较大，我们可以先只查出索引(假设就是主键)，再通过索引来获取相应的数据，这种访问效率也是非常高的。

B+树索引在使用在order by中也是非常高效的，因为B+树本身就是有序的。

B-Tree索引的使用限制

1、如果不是按照索引的最左列开始查找，则无法使用索引。

2、使用索引时不能跳过索引中的列

假设有一个订单日期+下单人姓名+下单人电话组成的联合索引。如果我们在查找中只包括了订单日期和下单人的电话，那么这么查询就只能使用到订单日期这一列来进行过滤，而无法使用到下单人的电话这一列。这是因为我们的查询条件中跳过了下单人姓名这一列。

3、Not in 和 <> 操作无法使用索引。

4、如果查询中有某个列的范围查询，则其右边所有列都无法使用索引。

关于B-Tree索引的一些操作可以参考MYSQL的索引优化 ，多选择条件下的建索引规则

Hash索引的使用

Memory存储引擎使用的就是Hash和B-Tree两种索引的存储引擎，并且默认为Hash索引。另外Innodb也支持Hash索引，不过Innodb的Hash索引不是我们自己来建立的，Innodb存储引擎根据B-Tree索引的一种使用情况来自行建立的，所以又被称为一种自适应的Hash索引。关于Hash的原理可以参考数据结构整理 中Hash的小节。

1、Hash索引是基于Hash表实现的，只有查询条件精确匹配Hash索引中的所有列时，才能够使用到Hash索引。也就是说Hash索引只能应用到等值查询中，如果要用到范围查询或模糊查询的时候就不能使用到Hash索引。

2、对于Hash索引中的所有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码。

Hash索引的限制

1、Hash索引必须进行二次查找

2、Hash索引无法用于排序

3、Hash索引不支持部分索引查找也不支持范围查找

4、Hash索引中的Hash码的计算可能存在Hash冲突

由于HashCode容易产生Hash冲突，在Hash索引中一旦产生了大量的Hash冲突，就会影响Hash索引的查找和维护的性能，可以说Hash索引不适合用在选择性很差的列上。如果键值列的重复值很多，比方说性别这样的列，不管有多少行数据，那么只有两种可选的值，所以选择性就很差，这样的列上是不能使用Hash索引的。再比如生份证列，身份证的重复概率很小，所以在身份证列上建立Hash索引就比较合适。

使用索引的好处

1. 索引大大减少了存储引擎需要扫描的数据量。索引文件的大小通常会远小于数据文件的大小，以Innodb来说，Innodb发生一次I/O,最小的存取单位是以页为单位的，所以一页内可以存储的信息越多，那么它的读取效率也就越快。默认情况下Innodb一页的存取大小为16K,由于索引的大小远比数据行的大小小的多，所以一页内可以存储更多的数据行的数据，因此通过索引来查找读取的页的数量也就会越少，这样也就减少了存储引擎所要扫描的数据的数量，加快了数据查找的速度。
2. 索引可以帮助我们进行排序以避免使用临时表。
3. 索引可以把随机I/O变为顺序I/O.

使用索引的缺点

1. 索引会增加写操作的成本。由于我们在进行数据更新、插入或删除操作时，必须要对相关的索引和统计信息来进行相关的维护，所以索引越多，修改相关数据所需要的时间也就越长。为了解决这个问题，Innodb引入了一种插入缓存，以把多次插入合并成一次。增加数据导入的速度点最好方法是把所有的索引全部删除(主键除外)。
2. 太多的索引会增加查询优化器的选择时间。因为MySQL的查询优化器会根据索引的信息和查询的条件来为查询选择合适的索引，如果对同一个查询有很多的索引都可以使用，则会增加MySQL查询优化器对于查询进行分析的时间，从而也会影响数据库的查询效率。可见太多的索引无论对于写操作还是读操作都是没有什么好处的。只有在适当的列上建立了适当的索引，这时索引才能够提高数据库的性能。

索引优化策略

1、索引列上不能使用表达式或函数

select ... from product where to_days(out_date)-to_days(current_date)<=30

其中out_date为索引列，to_days()是一个函数，所以这条语句的out_date索引将不生效。修改如下

select ... from product where out_date<=date_add(current_date,interval 30 day)

经过以上优化，索引列将会生效。

2、前缀索引和索引列的选择性

我们在对列建立索引，对于Innodb来说，列的宽度是有限度的，这个宽度是767个字节。如果是int,date是没有问题的，但对于字符串来说，就存在问题。所以我们可以建立前缀索引来避免这种问题。差不多是255个字符。

create index index_name on table(col_name(n));

索引的选择性是不重复的索引值和表的记录数的比值。

由以上图可知，我们在建立前缀索引的时候，要尽可能的小，又要保证选择性不能太差，尽可能保持唯一。

3、联合索引

如何选择索引列的顺序

1. 经常会被使用到的列优先。
2. 选择性高的列优先
3. 宽度小的列优先

4、覆盖索引

B-Tree索引可以直接帮我们直接获取需要的数据，在B-Tree索引的叶子节点存储了索引的关键字的值，所以我们可以通过索引的关键字直接获取索引查询中所需要的数据，这样也就没有必要再读区数据行的信息了，这种包含了所有需要查询的字段的全部值的索引，就称之为覆盖索引。这里所说的全部值不仅是where子句中所出现的列的值，同时也包含了出现了select从句中的值，还包括order by和group by中的值。

优点

1. 可以优化缓存，减少磁盘I/O操作。
2. 可以减少随机I/O,变随机I/O操作为顺序I/O操作。
3. 可以避免对Innodb主键索引的二次查询。
4. 可以避免MyISAM表进行系统调用

无法使用覆盖索引的情况

1. 并不是所有的存储引擎都支持覆盖索引
2. 查询中使用了太多的列
3. 使用了双%号的like查询

比方说我们有这样一张表film

它有一个单字段索引和一个联合索引

当我们执行如下分析语句时可以看到

EXPLAIN select * from film where language_id=1

它是无法使用该索引的，但如果只查询该单一列时,或者只带主键时

EXPLAIN select language_id from film where language_id=1

EXPLAIN select film_id,language_id from film where language_id=1

它是使用了索引来查询的。

5、使用索引扫描来优化排序

通过排序操作

按照索引顺序扫描数据

1. 索引的列顺序和Order By子句的顺序完全一致
2. 索引中所有列的方向(升序，降序)和Order By子句完全一致
3. Order By中的字段全部在关联表中的第一张表中

现在我们执行如下分析语句

EXPLAIN select * from film where date>'2020-01-01' and date<'2020-01-05' order by film_id

我们可以看到它是通过索引来进行排序的

现在我们来看一下一个使用二级索引来进行排序的情况

explain select * from film where date='2020-02-01' order by title,description

但如果我们把排序字段调换一下位置

explain select * from film where date='2020-02-01' order by description,title

我们会发现出现了Using filesort，表示使用了文件排序，表示无法再使用二级索引排序

如果我们改变了两个二级索引的排序顺序

explain select * from film where date='2020-02-01' order by title desc,description

那么同样无法使用二级索引排序

又或者我们在最左索引列使用了范围查找

explain select * from film where date>'2020-02-01' order by title,description

那么也同样无法使用索引排序

6、模拟Hash索引优化查询

现在我们给表增加一个字段tile_md5

然后再增加一个索引

现在来进行一下查询

explain select * from film where title_md5=md5(title) and title='青春之歌'

执行结果可以看到它是使用了索引来进行查询

1. 只能处理键值的全值匹配查找
2. 所使用的Hash函数决定着索引键的大小

7、利用索引优化锁

1. 索引可以减少锁定的行数
2. 索引可以加快处理速度，同时也加快了锁的释放