【mysql】mysql索引，及索引优化

目录

• 索引是什么
  • 索引类型
• B+ Tree
  • mysql中的B+ Tree的特点
  • B+ Tree的数据搜寻过程
  • B+ Tree特性带来的优势
  • 扩展
    • B Tree（多路平衡查找树）
    • 红黑树
• mysql中B+Tree的落地
  • MyISAM
  • InnoDB
    • 聚集索引（聚簇索引）
• 索引的使用原则
  • 列的离散度
  • 联合索引最左匹配
    • 什么时候用到联合索引，如何创建联合索引
  • 覆盖索引
    • 回表
    • 覆盖索引
  • 索引条件下推（ICP）
• 索引的创建与使用
  • 索引的创建
  • 什么时候用不到索引

索引是什么

数据库索引是一种数据结构，协助快速查询、更新数据库表中的数据。

数据是以文件的形式存放在磁盘上面的,每一行数据都有它的磁盘地址。如果没有索引的话,我们要从500万行数据里面检索一条数据,只能依次遍历这张表的全部数据直到找到这条数据。

但是我们有了索引之后,只需要在索引里面去检索这条数据就行了,因为它是一种特殊的专门用来快速检索的数据结构,我们找到数据存放的磁盘地址以后,就可以拿到数据了。

索引类型

在InnoDB中，索引类型有三种，普通索引、唯一索引、全文索引。

普通索引（normal）：也叫非唯一索引，没有任何限制。

唯一索引（unique）：唯一索引的键值不能重复。主键索引是一种特殊的唯一索引，比普通的唯一索引多了个限制条件，就是键值不可为空。主键索引用primay key创建。

全文索引（fulltext）：针对较大的数据（几kb的数据），如果要解决like查询在全文匹配的时候的效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，如char、varchar、text。MyISAM和InnoDB引擎支持全文索引。

创建全文索引（MyISAM和InnoDB支持全文索引）：

create table 'fulltxet_test'(
	'content' varchar(500) default null,
    fulltext key 'content' ('content')
);

使用全文索引：

select * from fulltxet_test where match(content) against('全文索引查询测试' IN NATURAL LANGUAGE MODE);

B+ Tree

mysql中的B+ Tree的特点

1. 关键字数量跟路数的数量是相等的；

2. 根节点和枝节点中都不会存储数据，只有叶子节点才存储数据，这里的数据指的是完整记录的地址。

   即使在枝节点搜索到关键字之后也不会直接返回，会到最后一层的叶子节点去获取数据

3. 每个叶子节点增加了一个指向相邻叶子节点的指针，最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构；

B+ Tree的数据搜寻过程

1. 若在根节点就找到了键值会一直往下搜寻直到在叶子节点上获取到所需的数据
2. 如果是范围查询，则在找到范围值中的最小（或最大）值的叶子节点之后，只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高了区间查询效率，不需要返回上层父节点重复遍历查找。

B+ Tree特性带来的优势

1. 是B Tree的变种，B Tree能解决的问题（每个节点存储更多关键字、路数更多），它都能解决；
2. 扫库、扫表能力更强，如果要进行全表扫描，只需要遍历子节点就可以了，不需要遍历整个B+ Tree。
3. B+ Tree的磁盘读写能力相对于B Tree来说更强（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘（IO）加载的关键字更多）；
4. 排序能力更强，因为叶子节点上有下一个数据区的指针，数据形成了链表）；
5. 效率更加稳定（B+ Tree永远是在叶子节点拿到数据，所以IO次数是稳定的）

扩展

B Tree（多路平衡查找树）

Balanced Tree

在枝节点和叶子结点存储键值、数据地址、节点引用。

特点：分叉数（路上）永远比关键字数多1。

B Tree怎么实现一个节点存储多个关键字还保持平衡的呢？

比如Max Degree（路数）是3的时候，插入数据1/2/3，在插入3的时候，本来应该在第一个磁盘块，但是如果一个节点有三个关键字的时候，意味着有4个指针，子节点会变成4路，所以这个时候必须进行分裂。把中间的数据“2”提上去，把1和3编程2的子节点。

如果删除节点，会有相反的合并操作。

也就是说，在更新索引的时候会有大量的索引结构的调整，所以也就解释了为什么不要在频繁更新的列上建索引或者说为什么不要更新主键。

节点的分裂和合并，其实就是InnoDB页的分裂和合并。

如果索引键值有序，写满一页接着开辟一个新的页；如果索引键值无序，存储工程造成大量磁盘碎片，带来频繁的page分裂和合并。

红黑树

红黑树也是BTS（二叉查找树），但它不是严格平衡的，通过变色和旋转来保持平衡。

必须满足5个约束条件：

1. 节点分为红色和黑色；
2. 根节点必须是黑色的；
3. 叶子结点都是黑色的NULL节点；
4. 红色节点的两个子节点都是黑色（不允许两个相邻的红色节点）；
5. 从任意节点出发，到其每个叶子结点的路径中包含相同数量的黑色节点。

mysql为什么不用红黑树呢？

1. 只有两路；
2. 不够平衡。

红黑树一般只放在内存里面用。例如Java的TreeMap、HashMap等，它可以用来实现一致性哈希。

mysql中B+Tree的落地

mysql是一个支持插件式存储引擎的数据库。在mysql里面每个表在创建的时候都可以指定它所使用的存储引擎。

MyISAM和InnoDB是mysql最常用的两种存储引擎。

mysql的数据都是以文件的形式存放在磁盘中的，可以通过命令来查看这些文件。

show VARIABLES LIKE 'datadir';

MyISAM

一个索引会有一棵B+Tree,存放在一个索引文件中。

索引和数据是两个独立不同的文件。

MyISAM的B+Tree里面，叶子结点存储的是数据文件对应的磁盘地址。所以从索引文件中找到键值后，会到数据文件中获取相应的数据记录。

非主键索引跟主键索引存储和检索数据的方式是一样的，都是先在索引文件里面找到磁盘地址，然后到数据文件里面获取数据。

InnoDB

在InnoDB的中，数据直接存储在索引的叶子节点上。

所以说InnoDB中索引即数据，数据即索引。

聚集索引（聚簇索引）

聚集索引：索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。

InnoDB组织数据的方式就是（聚集）索引组织表（clustered index organize table）。如果说一张表创建了主键索引，那么这个主键索引就是聚集索引，决定数据行的屋里存储顺序。

主键索引之外的索引并不会在叶子结点也存放一份数据，主键索引和辅助索引（二级索引）是有一个主次之分的。如果有主键索引，那么主键索引就是聚集索引，其他的叫“二级索引”。

二级索引存储的是二级索引的键值，如在name上建立索引，节点上存放的是name的值：zhangsan、lisi等。

二级索引的叶子结点存的是这条记录对应的主键的值。比如zhangsan id=1，lisi id=3等等。

所以二级索引的数据检索流程就是先用二级索引的键值（key）找到对应的叶子节点，在叶子节点拿到主键的值，再到主键索引的叶子节点拿到数据。（为什么不存地址而是存键值？因为地址是会发生变化的。）

因此不难看出，主键索引比二级索引少扫描了一颗B+Tree（避免了回表），所以主键索引的速度会快一些。

没有主键的表或者没有索引的表数据存放在哪里？

1. 如果有主键，InnoDB会选择主键作为聚集索引；
2. 如果没有显式的定义主键，InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引；
3. 如果既没有主键也没有唯一索引，InnoDB会选择内置6字节长的ROWID作为隐式的聚集索引，它会随着行记录的写入而递增。

索引的使用原则

列的离散度

列的离散度公式：

count(distinct(column_name)):count(*);

列的全部不同值和所有数据行的比例。数据行相同的情况下，分子越大，列的离散度就越高。

简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。

列的离散度低了，也就是说重复的值多的情况下，mysql的优化器发现走索引跟全表扫描差不多的时候，就不一定会走索引。

联合索引最左匹配

多条件查询时，有时候会建立联合索引。联合索引在B+Tree中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的。

什么时候用到联合索引，如何创建联合索引

创建联合索引

--CREATE INDEX idx_a on test_innodb(a);
--CREATE INDEX idx_a_b on test_innodb(a,b);
CREATE INDEX idx_a_b_c on test_innodb(a,b,c);

ALTER TABLE test_innodb add INDEX 'index'('a','b','c')

建立联和索引的时候，一定要把较常用的列放在最左边。

按照最左匹配原则，第三个联和索引index（a,b,c)相当于创建了三个索引：

1. index(a)
2. index(a,b)
3. index(a,b,c)

所以创建联合索引时若创建了index(a,b,c)，则就没有必要再去创建index(a)和index(a,b);

另外创建index(a,b,c)时，用where b=? 或 where b=? and c=?等查询条件是不走索引的。

覆盖索引

回表

非主键索引，先通过索引（二级索引）找到主键索引的键值，再通过主键索引查出数据，这个过程叫做回表。它比通过主键索引查询多扫描了一棵索引树。

覆盖索引

在二级索引里面，不管是单列索引还是联合索引，如果select的数据列只用从索引中就能取到，不必从数据区中是读取，这个时候索引就叫做覆盖索引，这样就避免了回表。

什么意思呢？通俗一点讲就是索引index(a,b,c)，我select a,b,c from test_innodb where a=？时，因为要查询的内容刚好是索引内容，所以直接从索引中取数据（二级索引中保存），不需要再回表。

EXPLAIN SELECT a,b from test_innodb where a="1";
EXPLAIN SELECT a from test_innodb where a="1" and b="2";
EXPLAIN SELECT b from test_innodb where a="1" and b="2";
EXPLAIN SELECT * from test_innodb where a="1" and b="2";
EXPLAIN SELECT a from test_innodb where b="2";

其中查询1,2,3都可以用到覆盖索引，查询4不是覆盖索引。

那查询5呢？

按上面的分析来讲，查询5是用不到索引的，实际上却可以用的覆盖索引，这是因为查询优化器觉得用索引更快，所以做了处理。

覆盖索引减少了IO次数，减少了数据的访问量，可以大大提升查询效率。

索引条件下推（ICP）

索引条件下推（Index Condition Pushdown），5.6以后完善的功能。只适用于二级索引。ICP的目标是减少访问表的完整行的读数量从而减少IO操作。

这里说的下推，其实是把过滤的动作在存储引擎做完，而不需要到Server层过滤。
建一张员工表：

drop table employees
CREATE TABLE employees
'emp_no' int(11) NOT NULL,
'birth_date' date NULL
'first_name' varchar(14) NOT NULL,
'last_name' varchar(16) NOT NULL,
'gender' enum('M','F') NOT NULL,
'hire_datedate' NULL
PRIMARY KEY (emp no
)ENGINE=InnoDB DEFAULT CHARSET=latinl;

alter table employees add index idx_lastname_firstname(last_name, first_name):

现在要查询所有姓zhang，并且名字后面最后一个字母是n的员工：

select * from employess where last_name='wang' and first_name like '%n';

正常来说，“%n”已经不符合最左匹配原则了，查询的时候是用不到索引的，只有last_name这个字段能过用于索引比较和过滤。

所以正常的查询过程是：

1. 根据联合索引查出所有姓wang的二级索引数据；
2. 回表，找到主键索引上全部符合条件的数据；
3. 把符合条件的数据返回给Server层，在Server层过滤出名字以n结尾的员工。

索引的比较是在存储引擎进行的，数据记录的比较实在Server层进行的。当first_name的条件不能用于索引过滤是，Server层不会把first_name的条件传递给存储引擎，所以读取了两条没有必要的记录。

这时候如果满足了last_name="zhang"的记录有10w条，就会有99999条没有必要读取的数据。

那根据first_name字段过滤的动作，能不能再存储引擎完成呢？

另一种查询过程：

1. 根据联合索引查出所有姓wang的二级索引数据；
2. 在二级索引中筛选出first_name以n结尾的索引；
3. 回表，到主键索引上查询出符合条件的数据返回给Server层。

第二种查询过程就是所说的索引条件下推（ICP）。

ICP是默认开启的，也就是说针对于二级索引，只要能够把条件下退给存储引擎，它就会下推，不需要人工干涉。

--开启
set optimizer_switch='index_condition_pushdown=on';
--关闭
set optimizer_switch='index_condition_pushdown=on';
--查看
show variables like 'optimizer_switch';

explain select * from employess where last_name='wang' and first_name like '%n';

执行计划explain中Extra显示Using index condition，说明用了索引条件下推。

当执行计划explain中Extra显示Using where时，代表从存储引擎取回的数据不是全部满足条件的，需要在Server层过滤。也就是上面说的没有用的索引条件下推的第一种查询过程。

索引的创建与使用

索引的创建

1. 在用于where条件、order判断和join（on）、group by的字段上创建索引；

2. 索引的个数不宜过多，会浪费空间，导致更新变慢；

3. 过长的字段，建立前缀索引；

   create table 'pre_index_test'(
   	'content' varchar(20) default null,
       key 'pre_idx'('content'(6))
   )ENGINE=InnoDB DEFAULT charset=utf8mb4;
   

4. 区分度低的字段，不建索引，离散度太低会导致扫描行数过多，比如性别；

5. 频繁更新的值，不作为主键或索引。会造成频繁的页分裂；

6. 随机无序的值，不建议作为索引，会造成分裂，如身份证、UUID等；

7. 组合索引把区分度高（离散度高）的值放在前面；

8. 创建复合索引，而不是修改单列索引。

什么时候用不到索引

1. 索引列上使用函数(replace、substr、concat、sum、count、avg)、表达式、计算（+-*/);

   select * from test_table where id+1 = 4;
   

2. 字符串不加引号，出现隐式转换时

   alter table test_table drop index idx_a;
   alter table test_table add index idx_a(a);
   
   explain select * from 'test_table' where a = 123;
   explain select * from 'test_table' where a = '123';
   

3. like中前面带'%'

   explain select * from 'test_table' where b like '%zhang';
   

4. 负向查询not like时，！=、<>和not in在某些情况下可以

   explain select * from 'test_table' where c not in(1);
   explain select * from 'test_table' where c <> 1;
   

   因为索引是有序的，只要从1之后开始顺序读就可以了。

索引是否生效还与数据库版本、数据量、数据选择度都有关系。用不用索引，最终都是查询优化器说了算。

mysql的查询优化器是基于成本开销（cost开销）的优化器，怎么开销小怎么来。