mysql索引解析及优化

作者: 浅乔未果 | 来源:发表于2020-08-20 15:52 被阅读0次

`索引是排好序的数据结构`

1. 索引

索引的作用

提高查询速度
确保数据的唯一性
可以加速表和表之间的连接 , 实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
全文检索字段进行搜索优化.

分类

主键索引 (Primary Key)
唯一索引 (Unique)
常规索引 (Index)
全文索引 (FullText)

主键索引

主键 : 某一个属性组能唯一标识一条记录

特点 :

最常见的索引类型
确保数据记录的唯一性
确定特定数据记录在数据库中的位置

 --创建表时创建
 create table `test`(
  -- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
  PRIMARY KEY (id),
 )ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表';

唯一索引

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

主键索引只能有一个
唯一索引可能有多个

CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
  -- 或 UNIQUE INDEX (GradeName),
 )
 --创建后添加
 -- 假设建表时fileuploadercode字段没创建索引(注:同一个字段可以创建多个索引，但一般情况下意义不大)
 -- 给projectfile表中的fileuploadercode创建索引
 ALTER TABLE Grade ADD UNIQUE INDEX (GradeName);

常规索引

作用 : 快速定位特定数据

注意 :

index 和 key 关键字都可以设置常规索引
应加在查询找条件的字段
不宜添加太多常规索引,影响数据的插入,删除和修改操作

 CREATE TABLE `result`(
  -- 省略一些代码
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加，组合索引
 )
 -- 创建后添加
 ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

全文索引

百度搜索：全文索引

作用 : 快速定位特定数据

注意 :

只能用于MyISAM类型的数据表
只能用于CHAR , VARCHAR , TEXT数据列类型
适合大型数据集

 /*
 #方法一：创建表时
  CREATE TABLE 表名 (
  字段名1 数据类型 [完整性约束条件…],
  字段名2 数据类型 [完整性约束条件…],
  [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
  [索引名] (字段名[(长度)] [ASC |DESC])
  );


 #方法二：CREATE在已存在的表上创建索引
  CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
  ON 表名 (字段名[(长度)] [ASC |DESC]) ;
 
 
 #方法三：ALTER TABLE在已存在的表上创建索引
  ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
  索引名 (字段名[(长度)] [ASC |DESC]) ;

 #删除索引：DROP INDEX 索引名 ON 表名字;
 #删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
 
 
 #显示索引信息: SHOW INDEX FROM student;
 */
 
 /*增加全文索引*/
 ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
 
 /*EXPLAIN : 分析SQL语句执行性能*/
 EXPLAIN SELECT * FROM student WHERE studentno='1000';
 
 /*使用全文索引*/
 -- 全文搜索通过 MATCH() 函数完成。
 -- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
 EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
 
 /*
 开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况
 
 MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
 MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
 测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
 */

拓展：测试索引

建表app_user：

CREATE TABLE `app_user` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(50) DEFAULT '' COMMENT '用户昵称',
 `email` varchar(50) NOT NULL COMMENT '用户邮箱',
 `phone` varchar(20) DEFAULT '' COMMENT '手机号',
 `gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
 `password` varchar(100) NOT NULL COMMENT '密码',
 `age` tinyint(4) DEFAULT '0' COMMENT '年龄',
 `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
 `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

批量插入数据：100w

 DROP FUNCTION IF EXISTS mock_data;
 DELIMITER $
 CREATE FUNCTION mock_data()
 RETURNS INT
 BEGIN
 DECLARE num INT DEFAULT 1000000;
 DECLARE i INT DEFAULT 0;
 WHILE i < num DO
  INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
  VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
 END WHILE;
 RETURN i;
 END;
 SELECT mock_data();

索引效率测试

无索引

 SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
 SELECT * FROM app_user WHERE name = '用户9999';
 SELECT * FROM app_user WHERE name = '用户9999';
 
 mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
 *************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
  table: app_user
  partitions: NULL
  type: ALL
 possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
  rows: 992759 //查询次数
  filtered: 10.00
  Extra: Using where
 1 row in set, 1 warning (0.00 sec)

创建索引

CREATE INDEX idx_app_user_name ON app_user(name);

测试普通索引

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
 *************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
  table: app_user
  partitions: NULL
  type: ref
 possible_keys: idx_app_user_name
  key: idx_app_user_name
  key_len: 203
  ref: const
  rows: 1  //查询次数
  filtered: 100.00
  Extra: NULL
 1 row in set, 1 warning (0.00 sec)
 
 mysql> SELECT * FROM app_user WHERE name = '用户9999';
 1 row in set (0.00 sec)
 
 mysql> SELECT * FROM app_user WHERE name = '用户9999';
 1 row in set (0.00 sec)
 
 mysql> SELECT * FROM app_user WHERE name = '用户9999';
 1 row in set (0.00 sec)

索引准则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表建议不要加索引
索引一般应加在查找条件的字段

索引的数据结构

 -- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
 hash类型的索引：查询单条快，范围查询慢
 btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
 
 -- 不同的存储引擎支持的索引类型也不一样
 InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
 MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
 Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
 NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
 Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

2. 为什么MySQl选择B+树构建索引

2.1 二叉树的弊端

Snipaste_2020-08-18_20-30-57.png

当存储的数据一致自增时，就会类似于链表的数据结构了

2.2 AVL树的弊端

每个子节点的长度不超过其他节点1个节点

缺点：会经常发生旋转，非常消耗性能

2.3 红黑树的弊端

真对AVL树进行修改，取得一个相对平衡的情况，最长的自阶段不超过其他节点的两倍，是的发生旋转的次数降低，而且加入了变色的机制。

当数据量足够大的时候，h的深度会不断增加，索引向下查找的次数会不断增加。

2.4 B树

注意:之前有看到有很多文章把B树和B-tree理解成了两种不同类别的树，其实这两个是同一种树;

概念

B树和平衡二叉树稍有不同的是B树属于多叉树又名平衡多路查找树（查找路径不只两个），数据库索引技术里大量使用者B树和B+树的数据结构，让我们来看看他有什么特点;

规则

（1）排序方式：所有节点关键字是按递增次序排列，并遵循左小右大原则；（2）子节点数：非叶节点的子节点数>1，且<=M ，且M>=2，空树除外（注：M阶代表一个树节点最多有多少个查找路径，M=M路,当M=2则是2叉树,M=3则是3叉）；（3）关键字数：枝节点的关键字数量大于等于ceil(m/2)-1个且小于等于M-1个（注：ceil()是个朝正无穷方向取整的函数如ceil(1.1)结果为2); （4）所有叶子节点均在同一层、叶子节点除了包含了关键字和关键字记录的指针外也有指向其子节点的指针只不过其指针地址都为null对应下图最后一层节点的空格子; 最后我们用一个图和一个实际的例子来理解B树（这里为了理解方便我就直接用实际字母的大小来排列C>B>A）

Snipaste_2020-08-18_20-42-47.png

特点：

B树相对于平衡二叉树的不同是，每个节点包含的关键字增多了，特别是在B树应用到数据库中的时候，数据库充分利用了磁盘块的原理（磁盘数据存储是采用块的形式存储的，每个块的大小为4K，每次IO进行数据读取时，同一个磁盘块的数据可以一次性读取出来）把节点大小限制和充分使用在磁盘快大小范围；把树的节点关键字增多后树的层级比原来的二叉树少了，减少数据查找的次数和复杂度;

2.4 B+树

概念

B+树是B树的一个升级版，相对于B树来说B+树更充分的利用了节点的空间，让查询速度更加稳定，其速度完全接近于二分法查找。为什么说B+树查找的效率要比B树更高、更稳定；我们先看看两者的区别

规则

（1）B+跟B树不同B+树的非叶子节点不保存关键字记录的指针，只进行数据索引，这样使得B+树每个非叶子节点所能保存的关键字大大增加； （2）B+树叶子节点保存了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样；（3）B+树叶子节点的关键字从小到大有序排列，有利于范围查找(在所有的树叶节点的前后维护前后树叶节点的位置，成为一个双向链表，当查找到叶子节点后，大于或小于可以直接获取前后的数据)。

特点

在B+树的基础上因其初始化的容量变大，使得节点空间使用率更高，而又存有兄弟节点的指针，可以向兄弟节点转移关键字的特性使得B*树额分解次数变得更少；

Snipaste_2020-08-18_20-44-45.png

3.MyISAM和InnoDB

3.1 MyISAM和InnoDB存储的结构信息

Snipaste_2020-08-18_19-04-11.png

3.2 MyISAM索引实现(非聚集)：

索引文件和数据文件是分离的

Snipaste_2020-08-18_19-05-32.png

大致的实现过程：

通过在.MYI文件，B+Tree的索引方式查询到对应索引所在的地址
根据查询到的地址在.MYD查询到相对应的地址，再返回数据

3.3 InnoDB索引实现(聚集)：

索引文件和数据文件是聚集的

表数据文件本身就是B+Tree组织的一个索引结构文件

聚集(聚簇)索引-叶节点包含了完整的数据记录

3.3.1 在InnoDB下的主键索引

Snipaste_2020-08-18_18-48-36.png

大致的实现过程：

通过.ibd文件，使用B+Tree的索引方式查询到相应的索引数据，在索引数据下，直接存储了value的信息，直接返回。

3.3.2 在InnoDB下的非主键索引(二级索引、普通索引)

Snipaste_2020-08-18_20-09-03.png

在非主键索引中，所有叶子节点存储的都是主键的值

3.4 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键

必须有主键

每个InnoDB表中，mysql会选择一列不重复列作为B+树索引的构建，如果用户创建主键的话，会自动根据主键创建主键索引，如果没有主键，mysql会一列一列往下查找，知道找到有一列的内容时不重复的，如果没有查找到，mysql后端会自动创建一个隐藏的rowId去构建索引，在整个过程中每次往下一列一列查找会销毁多余的性能，因此InnoDB表下，推荐要设置主键。

整型的自增主键

在索引构建后，查找数据的时候会比较大小，对于各种数据类型来说，整型式比较适合比大小的，而且存储的内容撑死就8个字节，因此主键选择整型最为合适.

当每次插入一个节点的时候，对于自增来说，他都会往后移，对于B+树的影响最小，而非自增来说，他每次插入一个节点，都有可能导致叶子节点的分裂，叶子节点分裂后需要重新平衡，这对与mysql来说会影响性能，所以整型的自增主键最为合适。

4. 索引名词

4.1 什么是回表查询

Snipaste_2020-08-19_20-42-29.png

通过第二索引(普通索引)查找到主键对应的值，通过查询到的值回到主键索引中再次查找，称之为回表查询

4.2 索引覆盖

select * from table where name = ? --需要回表查询全部字段
select id from table where name = ? --直接通过普通索引查询到id值，直接返回  索引覆盖

4.3 索引下推

Snipaste_2020-08-19_21-19-20.png

索引下推的唯一缺点是需要再磁盘上多做数据赛选，原来放在内存中的，现在放到磁盘上进行筛选，在磁盘筛选过程中可能成本相对来说较高，但是索引下推创建之后是排序的，所有数据聚集存放，而且io量会大大减少，查找时性能大大提高。

4.4 谓词下推

 select t1.name t2.age from t1 join t2 t1.id = t2.id
 --先做表连接，然后查询需要的字段
 --先把需要的字段都拿出来，然后再做关联   效率要高很多   谓词下推

4.5 MRR(mult_range read)

通过B+树，查询name时，发现多个相同的name,这是，会把name下对于的id在内存中进行排序，方便查找

4.6 FIC(fast index create)

插入和删除数据(原来情况)

先创建临时表，将数据导入到临时表
删除原始表
修改临时表的名字

FIC:

给当前表添加一个Share锁，不会有创建临时文件的资源消耗，还是在源文件中，但是此时如果有人发起DML操作，会出现数据不一致的问题，所以添加share锁，读取时不会有问题，但是DML会有问题

5. 索引匹配方式

5.1 最左优化匹配原则

···
KEY 'idx_name_age_position' ('name','age','position') USING BTREE
根据上面创建的联合索引，判断以下哪个用可以用到索引
EXPLAIN SELECT * FROM exployees WHERE name = 'LiLei' AND age = 18; yes
EXPLAIN SELECT * FROM exployees WHERE position = 'manager'; no
EXPLAIN SELECT * FROM exployees WHERE name = 'LiLei'; no</pre>
···

Snipaste_2020-08-18_21-32-02.png

联合索引如上图，他是根据创建的联合索引的的每个字段进行排序的，从第一个字段开始，第一个字段相同的情况下，在第二层使用第二个字段进行排序，所以，如果是想使用到联合索引，首先要知道第一个字段排序如上述示例就是name，如果联合索引中间断了一个，则会不进行联合索引查找，如name = 'LiLei' AND position = 'manager'则不会查找到联合索引，mysql内部进行了优化，字段的顺序可以不跟索引创建的一致，比如age = 18 AND posiition = 'manager' AND name'Lilei'也可以，mysql会自己进行排序，然后根据索引查找.

5.2 全值匹配

和所有索引中的列进行匹配

 --索引中有3个字段name、age、pos
 explain select * from staffs where name = 'july' and age = '24' and pos = 'dev'

5.3 匹配列前缀

可以匹配某一列的值的开通部分

explain select * from staffs where name like 'j%'--可以匹配
explain select * from staffs where name like '%j%'--不可以匹配

5.4 匹配范围值

匹配范围值

explain select * from staff where name > 'july';
 ------------------------
 explain select * from staff where name = 'july' and score > 60 and sex = '女';--在这条语句中只有name和score用到了索引，在范围查找之后，索引失效

5.5 精确匹配到某一列范围匹配另外一列

explain select * from staff where name = 'july' and score > 60;

5.6 只访问索引的查询

查询的时候只需访问索引，不需要访问数据行，本质上就是索引覆盖

explain select name,age,pos from staffs where name = 'july' and age = 22 and pos = 'dev';

5.7.哈希索引

概述

基于哈希表实现，只能紧缺匹配索引索引列的查询才有效
在mysql中，只有memory的存储应该去显示支持哈希索引
哈希索引自身秩序存储对于的hash值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快

限制

哈希索引值包括哈希值的行指针，而不存储字段值，索引不能使用索引中的值来避免读取行
哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
哈希索引不支持部分匹配查找，哈希索引是使用索引的全部内容来计算哈希值
哈需索引支持等值比较查询，不支持任何范围查询
访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，知道找到所有符合条件的行
哈希冲突比较多的话，维护代价也很高

案例

当存储大量的url并根据url进行搜索行查找，如果使用B+树，存储的内容就会很大，可以利用CRC32做哈希，select id from url where url = "" and url_crc = crc32("")此查询吸能较高的原因是使用体积很小的所有完成查找

6. 覆盖索引

6.1 基本介绍：

如果一个索引包括所有需要查询的字段的值，我们称值为覆盖索引
不是所有类型的所有都可以称之为覆盖所有，覆盖索引必须要存储所有列的值
不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖所有，memory不支持覆盖索引

6.2 优势：

索引条目通常远小于数据行大小，如果只需读取索引，那么mysql就会极大的减少数据访问量
因为所有是安装列值顺序存储的，所有对于io密集型的范围查询会比随机从磁盘读取每一行数据的io要少得多
一些引擎所有如MYISAM在内存中值存储索引，数据则依赖与操作系统来春初，因此要访问数据需要一次系统调用，这可能导致严重的性能问题
由于innodb的聚簇索引，覆盖所有对innodb表特别有用

6.3 特殊情况

 --表abc有字段a,b,c,创建组合索引a,b,c
 explain select * from abc where a = 1 or b = 2;---会走索引
 
 --表abc有字段a,b,c,d,创建组合索引a,b,c
 explain select * from abc where a = 1 or b = 2;---不会走索引
 explain select a,b,c from abc where a = 1 or b = 2;---会走索引</pre>

结论：

在使用or查询的使用，如果查询的字段在索引内全部包含，是能走索引的，如果有一个不包含，是不走索引的。

7. 优化细节

当使用索引列进行查询的时候尽量不要使用表达式，把计算放到也无从而不是数据库层
尽量使用主键查询，使用其他索引查询，可能会触发回表操作
使用前缀索引
Snipaste_2020-08-20_14-48-28.png
通过查询city的前7个符合进行排序，降低了排序的成本
使用索引扫描来排序
union all,in,or都能使用索引，但是推荐使用in
范围列可以用到索引
1. 范围条件是：<,<=,>,>=,between
2. 范围列可以用到索引，但是范围列后面的无法用到索引，索引最多的用于一个范围列
不要在索引列上做任何操作，比如计算、使用函数、自动或手动进行类型转换，会导致索引失效，从而使查询转向全表扫描。
存储引擎不能使用范围条件右边的索引列。
尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句。
1. 将换成索引列，查询时使用了索引，用索引列覆盖查询的，叫做覆盖索引。
mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。
is null，is not null 也无法使用索引。
like以通配符开头（like '%aaa'）mysql索引失效会变成全表扫描操作。