MySQL中的索引

InnoDB的索引模型

1. B+树索引模型

2. 每个索引在innoDB里对应一颗B+树。

3. 根据椰子节点的内容，索引类型分为主键索引和非主键索引。

主键索引和普通索引的查询区别？

• 使用主键索引只需要搜索ID这颗B+树；

• 普通索引需要先搜索普通索引树，得到对应的ID再到ID索引树搜索一次，这个过程称为回表。

索引的维护

B +树为了维护索引有序性，在插入新值的时候需要做必要的维护。如果插入的数据不是有序的（小于现有的最小数据）余姚逻辑上移动后面的数据，空出位置。如果数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，人后挪动部分数据过去，这个过程称为页分裂，这种情况下性能会受到影响。

也分裂操作还会影响数据页的利用率。原本防砸第一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。

当然有分裂就有合并. 当相邻另个页由于删除了数据,利用率很低之后,会将数据 也做合并.合并的过程,可以认为是分裂过程的逆过程.

为什么使用自增主键

自增主键: 插入新纪录的时候可以不指定ID的值,系统柜获取当前ID最大值加1作为下一条记录的ID值

1. 自增主键的插入数据模式,正符合了我们前面提到的递增插入的场景。每次插入一条新记录都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。而业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。
2. 因为每个非主键索引的椰子节点上都是主键的值,如果主键过长普通索引占的空间就会变大

演示表的初始化语句：

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

回表： 通过普通索引找到主键，然后拿着主键回到主键索引树上搜索的过程。回表影响查询效率。

如何解决回表：

覆盖索引：

1. 在一个查询中，使用的索引包含了我们的查询需求，我们称为覆盖索引。
2. 覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
3. 联合索引可以实现索引覆盖

如果执行的语句是select ID from T where kbetween 3 and 5,这时只需要查ID的值, "而ID的值已经在k索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引k已经"覆盖了"我们的查询需求,我们称为覆盖索引。

联合索引

演示表的初始化语句：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

最左前缀原则

查询时只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左M个字符。

当我们执行 select * from tuser where name = “张三” 时可以使用到索引,

select * from tuser where name like “张%” 也能使用到索引

select * from tuser where name like “%三” 则不能使用到索引

select * from tuser where age = 18也不能使用到索引

select * from tuser where age = 18 and name = “张三” 理论上是不能使用联合索引,但是优化器进行优化后的sql语句是这样的: select * from tuser where name = “张三” and age = 18 这样的话就能使用联合索引了.

普通索引与唯一索引

查询过程：

普通索引：查询条件满足第一条记录后，需要查找下一个记录直到碰到不满足条件的记录。

唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

这个不同带来的性能差别是微乎其微的。

更新插入过程：

情况一:这个记录要更新的目标页在内存中

唯一索引: 找到要插入的位置,判断插入的数据有没有冲突,没有冲突插入,语句执行结束.

普通索引: 找到位置插入这个值,语句执行结束

这种情况普通索引和唯一索引对更新语句性能影响是微乎其微的.

情况二: 这个记录要更新的目标页不在内存中

唯一索引: 需要将数据页读到内存,判断到没有冲突,插入这个值,语句执行结束

普通索引: 将更新记录在change buffer,语句执行就结束了.

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

什么是change buffer

当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下, InooDB会将这些更新操作缓存在change buffer 中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

change buffer 在内存中有拷贝，也会被写入到磁盘上。

如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

change buffer的使用场景

• change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。 这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。 唯一索引的优势也就是能保证数据的唯一性。在使用机械硬盘时，change buffer 这个机制的收效是非常显著的
• 因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。 因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
• 一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

索引失效(其实是sql写的有问题)

环境准备:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB；

然后，我们往表 t 中插入 10 万行记录，取值按整数递增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到 (100000,100000,100000)。：

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

哪些情况可能使用不到索引:

查询条件使用函数;

隐式转换(实际上还是使用了函数):

字符型与数字型发生隐式转换时会造成索引失效;

隐式转换会把查询条件转换成数字的判断语句,即: 数字型 = 字符型, 字符型 = 数字型 都会转换成 数字型 = 数字型。

情况一: select * from where a = “11” ; 这种情况sql变成了: select * from where a = int(“11”)a字段没有变化所以可是使用索引

情况二: 假设我们的a字段是varchar型; select * from where a = 11,; 这种情况sql变成了: select * from where int(a) = 11,这个字段已经不是我们创建索引的字段了,所以这种情况下使用不到索引

详细的转换规则: https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

我们要善于使用explain查看sql的执行情况:

使用explain查看sql语句执行情况

key: 用到的索引

如何给字符段加索引

前缀索引

演示表结构:

mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64), 
... 
)engine=innodb; 

由于要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句：

mysql> select f1, f2 from SUser where email='xxx';

什么是前缀索引

MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

mysql> alter table SUser add index index1(email);#完整索引
或
mysql> alter table SUser add index index2(email(6));#前缀索引

第一个语句创建的 index1 索引里面，包含了每个记录的整个字符串；而第二个语句创建的 index2 索引里面，对于每个记录都是只取前 6 个字节。

定义好合适的长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

前缀索引对覆盖索引的影响

select id,name,email from SUser where email='[e-mail]';

因为前缀索引没有存储字段的完整数据,不得不回表查询这个字段数据.

即使你将 index2 的定义修改为 email(18) 的前缀索引，这时候虽然 index2 已经包含了所有的信息，但 InnoDB 还是要回到 id 索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。

使用倒叙存储

对于后缀区分度比前缀区分度好的数据我们可以使用倒叙存储。

问题来了：

比如，我们国家的身份证号，一共 18 位，其中前 6 位是地址码，所以同一个县的人的身份证号前 6 位一般会是相同的。假设你维护的数据库是一个市的公民信息系统，这时候如果对身份证号做长度为 6 的前缀索引的话，这个索引的区分度就非常低了。按照我们前面说的方法，可能你需要创建长度为 12 以上的前缀索引，才能够满足区分度要求。但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。那么，如果我们能够确定业务需求里面只有按照身份证进行等值查询的需求，还有没有别的处理方法呢？这种方法，既可以占用更小的空间，也能达到相同的查询效率。

方式一： 是使用倒序存储。查询可以这样写：

mysql> select field_list from t where id_card =reverse('input_id_card_string');

方式二： 使用hash字段。 在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这样，索引的长度变成了 4 个字节，比原来小了很多。

它们的区别，主要体现在以下三个方面：从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。

在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。

从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

小结

1. 直接创建完整索引，这样可能比较占用空间；
2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
3. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。