107 MySQL_12 _索引【简单总结】
原创
©著作权归作者所有：来自51CTO博客作者悬浮海分海的原创作品，请联系作者获取转载授权，否则将追究法律责任
107 MySQL_12 _索引
文章目录
1.索引定义
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
提取句子主干，就可以得到索引的本质：索引是数据结构。
2.索引的作用
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化.
3.索引的分类
在一个表中，主键索引只能有一个，唯一索引可以有多个
- 避免重复的列出现，唯-索引可以重复，多个列都可以标识位唯一索引
CREATE TABLE `Grade`(
`GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
-- 或 UNIQUE KEY `GradeID` (`GradeID`)
)
- 快速定位特定数据
- 默认的，index, key 关键字来设置
- 应加在查询找条件的字段
- 不宜添加太多常规索引,影响数据的插入,删除和修改操作
CREATE TABLE `result`(
-- 省略一些代码
INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)
-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);
- 在特定的数据库引擎下才有，MyISAM
- 快速定位数据
基础语法：
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2、创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引 索引名 (列名) ,括号里的是列名，前面是索引名
ALTER TABLE schodl.student ADD FULLTEXT INDEX 'studentname' ( studentName ) ;
-- EXPLAIN 分析sq1执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引，没有加全文索引的查询
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘');
4.创建索引与基础语法
/*
#方法一：创建表时
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 版本、存储引擎和数据类型是否支持全文索引。
*/
5.索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段
6.索引的数据结构
-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
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 等索引；
案例
1.建表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 UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
2.批量插入数据：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();
3.索引效率测试
无索引
SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时 0.993 sec
SELECT * FROM app_user WHERE name = '用户9999'; -- 1.098 sec
SELECT * FROM app_user WHERE name = '用户9999'; -- 0.798 sec
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'; -- 0.001 sec
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表
- B-Tree：多路平衡查找树
- B+Tree：B+树
B-Tree：B树
- 度–节点的数据存储个数
- 叶节点具有相同的深度，叶节点的指针为空
- 所有索引元素不重复
- 节点中的数据索引从左到右递增排列
B+Tree：B+树
- 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引（增大度）
- 叶子节点包含所有索引字段
- 叶子节点用指针（双向）连接，提高区间访问的性能 （B-Tree 没有这样的双向指针）
B-Tree与B+Tress的区别：
- 先说一下B树比之前的红黑树好的原因（改进点）： B树和之前的红黑树相比是每一个节点（将一个节点的空间分配大一点，一般为16kb）可以放多个索引，这样保证树的高度不大（因为树的高度越小，查找的次数越少，效率越高），但可以存放更多的数据
- data 的存放B-Tree是和索引放在一起，而B+Tree是只放在叶节点上（这就导致了B-Tree，每个节点可以存放的索引很少）
- B-Tree索引是不重复的，B+Tree索引是冗余的
- B+Tree叶节点上每个节点之间都有双向指针，而B-Tree没有
- 综上，不同之处就在于当高度相同时，B+Tree能存储的元素要远远大于B-Tree
hash表：
- 对索引的key进行一次hash运算就可以定位出数据存储的位置
- 很多时候Hash索引要比B+树索引更高效
- 仅能满足 “=” ，“IN”，不支持范围查询（这是关键）
- 存在hash冲突问题
存储引擎
存储引擎是形容数据库中的表的
-- 我们可以在创建索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
hash类型索引是通过对查询条件的hash运算，来得到一个hash散列值，通过这个散列值快速得到要查询数据所在 的磁盘文件地址
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
两种索引类型中常用的还是btree类型索引，这是因为虽然hash类型索引查找单个数据的效率要比btree类型索引要高，但是在范围查询中如：(select * from Student where stuid > 20;) ,b+tree类型索引要更好；通过分析两种类型的索引组织结构可以看出，hash类型索引组织的数据无顺序，查找数据时，每一个数据都要进行一次hash运算，而btree类型索引组织的数据是有顺序的（排好序的），并且是双向链表，可以快速的定位一个数据的左右节点数据
-- 不同的存储引擎支持的索引类型也不一样
（默认支持）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 等索引；
MyISAM存储引擎
本机MySQL版本为5.7 数据存储在C:\ProgramData\MySQL\MySQL Server 5.7\Data 文件夹下
- 使用MyISAM存储引擎的表在磁盘中存储时会产生大概三种文件：.frm文件、.MYD文件、.MYI文件
- frm、MYD、MYI分别是myisam引擎表的结构文件，数据文件，索引文件；
- CSM、CSV是和csv引擎表相关的文件， .csv是可以直接查看的，
- MyISAM索引文件和数据文件是分离的（非聚集）
- 如上图，是以表中的列Col1为主键索引建立的 b+tree数据结构，MyIsam引擎表的查询过程是先在.MYI索引文件中按照b+tree这个数据结构来查询，例如查询30，最后查询到的是0xF3，代表的是磁盘文件地址，再根据这个磁盘文件地址到 .MYD文件中快速地定位到我们要查询的数据。MyIsam引擎表是非聚集索引，而InnoDB引擎表是聚集索引，差别就在于聚集索引是将索引和数据放在了一起，在查找到目标索引之后，直接就可以取数据，而非聚集索引还要再到.MYD文件中去找数据。
InnoDB存储引擎
- InnoDB索引实现（聚集）
- 表数据文件本身就是按B+Tree组织的一个索引结构文件
- 聚集索引-叶节点包含了完整的数据记录（聚集的意思是指，索引与数据放在了一起，如下图的叶节点）
- 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
- 如果表没有设置主键，InnoDB索引引擎会自动查找数据不重复的一个列作为主键来组织B+Tree结构
如果找不到这样的一个列，则它会自动生成一个隐藏列来组织 - 因为定位索引的过程中，我们要做多次的比大小来确定树的走向，而（bigint类型）整型数据比大小的速度很快，并且整型数据最大也就8个字节，节约空间
- 自增是在于B+Tree是按顺序来存放数据的，如果不按照自增，当我们在7和9之后再插入8，它会将8这个主键索引插入到7和9之间，加入在这之前已经插入了很多数据，这个操作就会导致B+Tree中的（节点）数据存放位置发生很大的变化，导致效率比较低
- 为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）
- 使用InnoDB存储引擎的表在磁盘文件中大致会产生两种文件：.frm文件 .ibd文件
- .frm文件 (表结构文件) .ibd文件(索引和表数据文件，将表中的数据按B+Tree来组织)，如下图
- 下图展示的是主键索引：
在这个索引中，叶子节点只存储对应的主键，查找到这个主键之后，根据这个主键，再去主键索引树中查找相对应的数据，多做了一次索引树的扫描
联合索引底层数据结构
上图显示的是三个字段name age position 的联合索引，对于顺序的安排是按照三个字段的先后顺序逐个字段来进行匹配，此处就是先比较name字段，如果name字段已经可以比较出大小，就按这个大小顺序来排，如果name字段比较不出，就比较age字段，依次往下。
- 索引最左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索
引中的列。（条件严格，必须从索引的第一个字段开始，才能使用该联合索引）
EXPLAIN SELECT * FROM employees WHERE name = ‘Bill’ and age = 30;
EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = ‘dev’ :
EXPLAIN SELECT * FROM employees WHERE position = ‘manager’ :
上面三条查询语句只有第一条会用到上图中的索引 - 这是为什么呢？
- 我们可以看联合索引的底层数据结构，是按照字段的先后顺序来排序的，如果跳过了某个字段，那下面的那些字段不是按顺序来排列的，例如第二条语句，忽略了name字段，直接从age字段来查询，我们发现直接看age字段，整个B+Tree中的节点数据并不是按age字段来排好序的
- 索引（Index）是帮助MySQL高效获取数据的 排好序 的数据结构。----这是核心
