mysql索引(三)

03|MySQL的索引

• 索引的出现就是为了提高数据查询的效率。

索引的常见模型

常见的三种索引模型

1、哈希表

• key-value存储的结构。
• 多个key值在hash之后出现同一值则进行链表加入。
• 哈鼠标适用于等值查询的场景，不适合范围查询。

2、有序数组

• 当查询的某个数值在[x,y]区间内，可以使用二分法进行查找。
• 插入记录成本高，因此只适用与静态存储引擎。

3、二叉搜索树

• 二叉树搜索树特点：左子节点小于父节点，右子节点大于父节点。
• 搜索时间复杂度O(log(N))—二叉平衡树
• 在实际过程中大多数的数据库存储并不使用二叉树。
• 原因在于：索引不止存在内存中，还要写到磁盘上。

InnoDB的索引模型

• 在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种表成为索引组织表。
• InnoDB使用B+树索引模型。
• 每一个索引在InnoDB里面对应一颗B+树。

---

• 假设有一个主键列为ID的表，表中有字段k，并且k上有索引。
• 建表语句

create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;
)

• 表中R1~R5的（ID,K）分别为（100，1）、（200，2）、（500，5）和（600，6），两棵树示意如下。
  

• 根据叶子节点的内容，索引分为主键索引和非主键索引。

  • 主键索引的叶子节点存储整行数据，也被成为聚簇索引。
  • 非主键索引的叶子节点时主键的值。也被称为二级索引。

---

• 基于主键索引与普通索引的查询区别
  • select * from T where Id = 500,即主键查询方式，只需要搜索ID这个B+树
  • select * from T where k = 5,即普通索引查询方式，则要先查K索引树，得到Id = 500，再到Id索引树搜索一次，这个过程就是回表。
• 即非主键索引的查询需要多扫描一颗索引树。因此再查询红金量多使用主键查询。

索引维护

• B+树为了维护索引有序性，在插入值会做必要维护，根据B+树算法做页分裂。
• 页分裂会导致性能下降，以及数据页的利用率。

---

• 哪些场景下使用自增主键。
  • NOT NULL PRIMARY KEY AUTO_INCREAMENT
  • 插入不指定Id值，系统获取当前ID最大值+1作为吓一跳ID值。
  • 每次插入一条新数据都是追加，不会挪动其他记录，也不会触发叶子节点的分裂。
  • 有业务逻辑的字段做追按，则不容易保证有效插入，写成本比较高。
  • 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
• 什么场景适合直接用业务字段做主键？
  • 只有一个索引。
  • 改索引必须是唯一索引。
  • KV场景

覆盖索引

• 执行select ID from T where k between 3 and 5,这时候只需要查ID的值，而ID的值已经在K索引树上了，因此可以直接提供查询结果，不需要回表。
• 由于覆盖索引可以减少树的搜索次数，显著提高查询性能，所以使用覆盖索引是一个常用的性能优化手段。
  注意：
• 在引擎内部使用覆盖索引在索引K上其实读了三个记录,R3~R5（对应的索引K上的记录项），但是对于MySQL的Server层来说，它就是找引擎拿了两条记录，因此MySQL认为扫描行数就是2。

---

• 在一个市民信息表上，是否有必要将身份证和名字建立联合索引？
• 假设市民表

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

• 如果现在有个高频请求，要根据市民的身份证号查询他的姓名，那么就有（身份证号、姓名）的联合索引，它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少SQL执行时间。

最左前缀原则

• B+树的索引结构，可以利用索引的“最左前缀”，来定位记录。
  

• 索引项时按照索引定义里面出现的字段顺序排序的。

  • 当逻辑需求查到所有名字是“张三”的人时，可以快速定位到ID4，然后向后遍历得到所需要的结果。
  • 如果需要查第一个字是“张”，SQL语句：where name like ‘张%’。这时，也可以使用这个索引，查找到一个符合条件的记录是ID3，然后向后遍历，直到不满足条件为止。

• 由此可知，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引最左N个字段，也可以是字符串索引的最左N个字符。

---

• 建立联合索引的时候，如何安排索引内的字段顺序。
• 索引的复用能力，因为可以支持最左前缀。所以当已经有了(a,b)联合索引之后，一般就不需要单独在a上建立索引了。
  • 第一原则，如果通过调整顺序，可以少维护一个索引，那么这个顺序就是优先考虑的。
  • 第二原则，空间状况。比如上表，name字段比age字段大，那么就可以考虑创建一个(name,age)的联合索引和一个(age)的但字段索引。

索引下推

• 以上表为例：检索出表中名字第一个是张，而且年龄是10岁的所有孩子。

select * from tuser where name like '张%' and age=10 and ismale=1;

• 在MySQL5.6之前，只能从ID3开始一个个回表，到主键索引上找出数据行，再对比字段值。
  

• 在此过程中，InnoDB并不会去看age的值，只是按顺序把"name第一个是张的"的记录一条条取出来回表，因此回表4次。

---

• MySQL5.6引入的索引下推优化，可以再索引遍历过程中，对所以中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表的次数。
• 而在此过程中，InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。在这个例子中，只需要对ID4,ID5进行回表判断。

小结

• 问题：实际上主键索引也是可以使用多个字段的。

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

• 需要a、b联合主键。

如何避免长事务对业务的影响。

应用开发端

1. 是否使用set autocommit = 0。这个可以将genral_log开起来，跑一个业务逻辑，将其设置为1。
2. 确认是否有不必要的只读事务。
3. 业务连接数据库的时候，根据业务本身的预估，通过set MAX_EXECUTION_TIME命令，来控制每个语句执行的最长使劲按，避免单个语句意外执行太长。

数据库端

1. 监控information_schema.Innodb_trx表，设置长事务阈值，超过就报警、kill
2. Percona的pt-kill工具。
3. 在业务功能呢测试阶段输出所有的genral_log，分析日志行为。
4. 使用MySQL5.6或之后版本，把innodb_undo_tablespaces设置成2.如果出现大事务导致回滚段过大，设置后清理更方便。

• 执行select * from T where k between 3 and 5,需要执行几次树的搜索操作，会扫描多少行。

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');

• 这条SQL查询的执行流程

  1. 在K索引树上找到K=3的记录，取得ID=300;
  2. 再到ID索引树上查到ID=300对应的R3;
  3. 在K索引树上取K=5，取得ID=500；
  4. 再回到ID查找500对应的R4；
  5. 再K索引树取下一个值K=6，不满足条件，循环结束。

• 回到主键索引树搜索的过程成为回表，再这个查询过程读了K索引树的3条记录（步骤1、3、5），回表两次（2、4）.

• 由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么可不可以避免回表过程呢。

• B+能够很好的配合磁盘的读写特性、减少单次查询的磁盘访问次数。

---