看这篇就够了!MySQL 索引知识点超全总结( 三 )
Innodb B+树索引前文介绍了索引的基本数据结构,现在开始我们从 Innodb 的角度了解如何使用 B+树构建索引,索引如何工作和如何使用索引提升查找效率 。
聚集索引和非聚集索引数据库中的 B+树索引可以分为聚集索引和非聚集索引 。聚集索引和非聚集索引的不同点在于叶子节点是否是完整行数据 。
Innodb 存储引擎表是索引组织表,即表中的数据按照主键顺序存放 。聚集索引就是按照每张表的主键构造一棵 B+树,叶子节点存放的是表的完整行记录 。非聚集索引的叶子节点不包含行记录的全部数据 。Innodb 存储引擎的非聚集索引的叶子节点的内容为主键索引值 。
若数据表没有主键聚集索引是怎么建立的?在没有主键时 Innodb 会给数据表的每条记录生成一个 6 个字节长度的 RowId 字段,会以此建立聚集索引 。
Select 语句查找记录的过程下面例子将展示索引数据的组织形式及 Select 语句查询数据的过程 。
- 建表语句:
create table T (ID int primary key,k int NOT NULL DEFAULT 0,s varchar(16) NOT NULL DEFAULT '',index k(k)) engine=InnoDB DEFAULT CHARSET=utf8;insert into T values(100, 1, 'aa'),(200, 2, 'bb'),(300, 3, 'cc'),(500, 5, 'ee'),(600,6,'ff'),(700,7,'gg');
- 索引结构示意

文章插图
左边是以主键 ID 建立起的聚集索引,其叶子节点存储了完整的表记录信息;右边是以普通字段 K 建立的普通索引,其叶子节点的值是主键 ID 。
- Select 语句执行过程
select * from T where k between 3 and 5;
执行流程如下:- 在 K 索引树上找到 k=3 的记录,取得 ID=300;
- 再到 ID 索引树上查找 ID=300 对应的 R3;
- 在 k 索引树取下一个值 k=5,取得 ID=500;
- 再回到 ID 索引树查到 ID=500 对应的 R4;
- 在 k 索引树取下一个值 k=6,不满足条件,循环结束 。
注:该例子来自《MySQL 实战 45 讲》
覆盖索引MySQL 5.7,建表语句:
CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` enum('M','F') NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`),KEY `i_first_name` (`first_name`),KEY `i_hire_date` (`hire_date`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- SQL 语句 A
explain select * from employees where hire_date > '1990-01-14';
explain 结果:
文章插图
- SQL 语句 B
explain select emp_no from employees where hire_date > '1990-01-14';
explain 结果:
文章插图
- 分析
索引 K 中包含了查询语句所需要的字段 ID 的值,无需再次回到主键索引树查找,也就是“覆盖”了我们的查询需求,我们称之为覆盖索引 。覆盖索引可以减少树的搜索次数,显著提升查询性能 。
最左匹配
- SQL 语句 A
explain select * from employees where hire_date > '1990-01-14' and first_name like '%Hi%';

文章插图
- SQL 语句 B
explain select * from employees where hire_date > '1990-01-14' and first_name like 'Hi%';

文章插图
- 分析
索引下推何为索引下推?我们先从下面这组对比测试开始,将在 MySQL5.5 版本和 MySQL5.7 版本中执行同一条 SQL 语句:
推荐阅读
- 像素150dpi是什么意思?图片150dpi是什么意思_4
- 只需一条命令,就可以查出哪些端口被防火墙阻止了,真方便
- 不推荐别的了,IDEA 自带的数据库工具就很牛逼
- 一次解决mysql锁表问题的经历
- 音频如何剪切?电脑上处理音频用这个方法就够了
- 怎么查看电脑配置?这4种方法一定要学会
- 从 Linux 源码看 Socket 的阻塞和非阻塞
- 吃鸡蛋的禁忌有哪些?
- 陕西|47场特色服务活动!找工作的你看过来
- 艾尔登法环|光有了好看的衣服可不行,包包也得跟上