Mysql索引
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql索引,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含15698字,纯文字阅读大概需要23分钟。
内容图文
![Mysql索引](/upload/InfoBanner/zyjiaocheng/869/955c1aaae3044482bbdbc7313943394d.jpg)
Mysql索引
1.索引概念
1.1索引定义
数据库索引,是数据库管理系统(DBMS)中一个排序的数据结构,以协助快速查询、
更新数据库表中数据。
先数据是以文件的形式存放在磁盘上面的,每一行数据都有它的磁盘地址。没有索引,只能遍历这张表的所有数据才能找到所需的数据,有了索引,只需要在索引里面检索这条数据,拿着这条数据的磁盘地址直接去找到它
1.2 索引类型
InnoDB中索引分为三种:普通索引,唯一索引,全文索引
- 普通索引
也叫非唯一索引,是最普通的索引,没有任何的限制。 - 唯一索引
唯一索引的键值不能重复。主键索引是一种特殊的唯一索引,除了键值不能重复,还不能为空。主键索引用 primay key
创建。 - 全文索引
针对比较大的数据,比如我们存放的是消息内容,有几 KB 的数据的这种情况,如果要解决 like 查询效率低的问题,可以创建全文索引。只有文本类型的字段才可以创建全文索引,比如 char、varchar、text。
2.索引存储模型
2.1索引常见模型
2.1.1 二叉查找树(BST Binary Search Tree)
- 二叉查找树的优点
左子树所有的节点都小于父节点,右子树所有的节点都大于父节点。投影到平面以
后,就是一个有序的线性表。 因此二叉查找树既能够实现快速查找,又能够实现快速插入。 - 二叉查找树的缺点
二叉查找树的查询耗时与树的深度相关,最坏的情况下,查询的时间复杂度会退化成O(n)
如果插入的数据正好是有序的,那么这个时候,就变成了一颗斜树,成了链表,与顺序查询没有区别
造成倾斜的原因:
因为左右子树深度差太大,这棵树的左子树根本没有节点——也就是它不够平衡
2.1.2 平衡二叉树(AVL/Balanced Binary Search Tree)
2.1.2.1 平衡二叉树的定义
左右子树深度差绝对值不能超过 1
以顺序插入1~6示例平衡二叉树的计算过程:
:当我们插入了 1、2 之后,如果按照二叉查找树的定义,3 肯定是要在2 的右边的,这个时候根节点 1 的右节点深度会变成 2,但是左节点的深度是 0,因为它没有子节点,所以就会违反平衡二叉树的定义。那应该怎么办呢?因为它是右节点下面接一个右节点,右-右型,所以这个时候我们要把 2 提上去,这个操作叫做左旋。
2.1.2.2 平衡二叉树作为索引如何查找数据
首先要知道,平衡二叉树作为索引,存储了什么:
- 索引的键值
比如,在 id字段上创建了一个索引,在用 where id =1 的条件查询的时候就会找到索引里面的 id 的这个键值 - 是数据的磁盘地址
索引的作用就是去查找数据的存放的地址。 - 左右子节点的引用,找到下一个节点
2.1.2.3 AVL树存储索引数据的缺点
索引的数据,是放在硬盘上的。 当我们用树的结构来存储索引的时候,访问一个节点就要跟磁盘之间发生一次 IO。 InnoDB 操作磁盘的最小的单位是一页(或者叫一个磁盘块),大小是 16K(16384 字节)。那么, 一个树的节点就是 16K 的大小。 如果我们一个节点只存一个键值+数据+引用,例如整形的字段,可能只用了十几个或者几十个字节,它远远达不到 16K 的容量,所以 访问一个树节点,进行一次 IO 的时候,浪费了大量的空间。如果每个节点存储的数据太少,从索引中找到我们需要的数据,就要访问更多的节点,跟磁盘交互次数就会过多。
如何解决这个问题?
(1)让每个节点存储更多的数据。
(2)节点上的关键字的数量越多,指针数也越多,意味着可以有更多的分叉(我们把它叫做“路数”),树的深度就会减少(根节点是 0)
2.1.3 多路平衡查找树(B Tree)
多路平衡查找树(Balanced Tree)跟 AVL 树一样, B 树在枝节点和叶子节点存储键值、数据地址、节点引用。
- B树特点
分叉数(路数)永远比关键字数多 1
比如下面这棵树,每个节点存储两个关键字,那么就会有三个指针指向三个子节点。
- B树查询规则
假设在上面这张表里面查找 15。因为 15 小于 17,走左边。因为 15 大于 12,走右边。在磁盘块 7 里面就找到了 15,只用了 3 次 IO,比AVL树效率高,那么它是如何实现的?—— 分裂 合并
比如 Max Degree(路数)是 3 的时候,我们插入数据 1、2、3,在插入 3 的时候,本来应该在第一个磁盘块,但是如果一个节点有三个关键字的时候,意味着有 4 个指针,子节点会变成 4 路,所以这个时候必须进行分裂。把中间的数据 2 提上去,把 1 和 3 变成 2 的子节点。如果删除节点,会有相反的合并的操作。因此,数据库在更新索引的时候会有大量的索引的结构的调整,这解释了为什么我们不要在频繁更新的列上建索引,或者为什么不要更新主键。
节点的分裂和合并,其实就是 InnoDB 页的分裂和合并。
2.1.4 增强版多路平衡查找树(B+ Tree)
- InnoDB的 B+树的存储结构
- B+树特点
1、 它的关键字的数量跟路数相等
2、 B+Tree 的根节点和枝节点中都不会存储数据,只有叶子节点才存储数据。搜索到关键字不会直接返回,会到最后一层的叶子节点。
比如我们搜索 id=28,虽然在第一层直接命中了,但是全部的数据在叶子节点上面,所以还要继续往下搜索,一直到叶子节点。举个例子:假设一条记录是 1K,一个叶子节点(一页)可以存储 16 条记录。非叶子节点可以存储多少个指针?假设索引字段是 bigint 类型,长度为 8 字节。指针大小在 InnoDB 源码中设置为6 字节,这样一共 14 字节。非叶子节点(一页)可以存储 16384/14=1170 个这样的单元(键值+指针),代表有 1170 个指针。树 深 度 为 2 的 时 候 , 有 1170^2 个 叶 子 节 点 , 可 以 存 储 的 数 据 为1170117016=21902400。
3、 B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针,它的最后一个数据会指向下一个叶子节点的第一个数据, 形成了一个有序链表的结构。
4、它是根据左闭右开的区间 [ )来检索数据。 - B+树查找过程
1)假设要查找 28,在根节点就找到了键值,但是因为它不是页子节点,所以会继续往下搜寻,28 是[28,66)的左闭右开的区间的临界值,所以会走中间的子节点,然后继续搜索,它又是[28,34)的左闭右开的区间的临界值,所以会走左边的子节点,最后在叶子节点上找到了需要的数据。
2)如果是范围查询,比如要查询从 22 到 60 的数据,当找到 22 之后,只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点,极大地提高了区间查询效率(不需要返回上层父节点重复遍历查找)。
2.1.5 Hash索引
(1)一种键-值(key-value)存储数据的结构,哈希的思路很简单,把值放在数组里,用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置
(2)多个 key 值经过哈希函数的换算,会出现同一个值的情况。处理这种情况的一种方法是,拉出一个链表,用哈希索引做区间查询速度很慢
(3)哈希表这种结构适用于只有等值查询的场景,比如 Memcached 及其他一些NoSQL 引擎
InnoDB 只支持显式创建 B+Tree 索引,对于一些热点数据页,InnoDB 会自动建立自适应 Hash 索引,也就是在 B+Tree 索引基础上建立 Hash 索引,InnoDB内存结构中,buffer pool 里面有一块区域是 Adaptive Hash Index 自适应哈希索引,就是这个。
3.B+树索引如何存储数据
MySQL 的数据是以文件的形式存放在磁盘中的,我们可以找到这个数据目录的地址( show VARIABLES LIKE ‘datadir’;)每个数据库有一个目录,这个目录下存放每张表的.frm 和.ibd文件; .frm 是 MySQL 里面表结构定义的文件,与存储引擎类型无关。InnoDB 里面是以主键为索引来组织数据的存储的,所以索引文件和数据文件是同一个文件,都在.ibd 文件里面。
3.1 主键索引
InnoDB中主键索引也称为聚集索引/聚簇索引(clusterd index)。
- 什么叫聚集索引:
索引键值的逻辑顺序跟表数据行的物理存储顺序一致
InnoDB主键索引的叶子节点存储的是整行数据。
3.2 非主键索引
以主键索引是聚集索引,非主键都是非聚集索引。非主键索引的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引(secondary index)。
在 name 字段上面建的普通索引,又是怎么存储和检索数据的呢?
InnoDB 中,主键索引和辅助索引是有一个主次之分的。辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询,会根据主键值在主键索引中查询,最终取得数据。
比如我们用 name 索引查询 name= ‘青山’,它会在叶子节点找到主键值,也就是id=1,然后再到主键索引的叶子节点拿到数据(回到主键索引树搜索的过程,称为回表)
- 为什么辅助索存储主键值而不是主键磁盘地址
是因为有分叉和合并的操作,这个时候键值的地址会发生变化,所以在辅助索引里面不能存储地址 - 如果创建表时没有显式创建索引怎么办
1、如果定义了主键(PRIMARY KEY),那么 InnoDB 会选择主键作为聚集索引。
2、如果没有显式定义主键,则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引。
3、如果也没有这样的唯一索引,则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引,它会随着行记录的写入而主键递增。(select _rowid name from user;)
4. 索引使用原则
4.1 索引列的离散度
- 索引列的离散度
列的全部不同值和所有数据行的比例: count(distinct(column_name)) : count(*)。数据行数相同的情况下,分子越大,列的离散度就越高。
建立索引,要选择离散度高的列。当我们用在性别这样的字段上建立的索引去检索数据的时候,如果这个表数据量大,由于重复值太多,需要扫描的行数就更多,这种索引也就没有必要建立了。
4.3 联合索引最左匹配
假设有个user表,现在给这个表的name phone字段创建一个联合索引:
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
联合索引在 B+Tree 中是复合的数据结构,它是按照从左到右的顺序来建立搜索树的(name 在左边,phone 在右边)第一个字段name会有序排列,第二个字段phone无序(只有name相等时phone才有序)。如果查询条件没有 name,就不知道第一步应该查哪个节点,因为建立搜索树的时候 name 是第一个比较因子,所以用不到索引。因此建立联合索引时,要把最常用的列放在最左边。
以上面创建的联合索引为例, 下面的查询语句分别使用索引的情况如下( 注意,这里是select * ):
- 使用 name 和phone字段,可以用到索引
EXPLAIN SELECT * FROM user_innodb WHERE name= '朱伽' AND phone = '13105767157';
- 单独使用name字段,可以用到索引
EXPLAIN SELECT * FROM user_innodb WHERE name= '朱伽' ;
- 单独使用phone字段,无法用到索引,全表扫描
EXPLAIN SELECT * FROM user_innodb WHERE phone = '13105767157';
可以看到执行计划keys(实际使用到的索引)为空,rows显式998197,扫全表了
因此,创建一个索引comidx_name_phone (name,phone) 其实相当于创建了如下两个索引:
comidx_name (name);
comidx_name_phone (name,phone);
如果我们创建三个字段的索引 index(a,b,c),相当于创建三个索引:
index(a)
index(a,b) 用 where b=? 用不到索引
index(a,b,c) where b=? and c=? 和 where a=? and c=? 用不到索引
总的原则就是第一个字段必须用,且不能中断,否则就用不到索引了。
4.4 覆盖索引
非主键索引数据的查询,是先通过索引找到主键索引的键值,再通过主键值查出所需要的数据,它比基于主键索引的查询多扫描了一棵索引树,这个过程叫回表。
在辅助索引里面,不管是单列索引还是联合索引,如果 select 的数据列只用从索引中就能够取得,不必从数据区中读取,这时候使用的索引就叫做覆盖索引,这样就避免了回表。
- 覆盖索引
在辅助索引里面,不管是单列索引还是联合索引,如果 select 的数据列只用从索引中就能够取得,不必从数据区中读取,这时候使用的索引就叫做覆盖索引,这样就避免了回表。
ALTER TABLE user_innodb add INDEX `comixd_name_phone` (`name`,`phone`);
EXPLAIN SELECT name,phone FROM user_innodb WHERE name= '朱伽' AND phone = '13105767157';
EXPLAIN SELECT name FROM user_innodb WHERE name= '朱伽' AND phone = '13105767157';
EXPLAIN SELECT phone FROM user_innodb WHERE name= '朱伽' AND phone = '13105767157';
上面三个查询都可以用到覆盖索引,执行计划的Extra 列值为“Using index”代表使用了覆盖索引。
上面这个执行计划跟SELECT * FROM user_innodb WHERE name= ‘朱伽’ AND phone = ‘13105767157’; 的执行计划结果相比,区别在于 select的字段是联合索引中的字段时,会进行覆盖索引查询,不会回表查,而当使用 select * 查询时,虽然走了联合索引,但是进行了回表查询。
4.5 索引下推
假设有如下这么一张表和数据:
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_date` date NULL, PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
alter table employees add index idx_lastname_firstname(last_name,first_name);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (1, NULL, '698', 'liu', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (2, NULL, 'd99', 'zheng', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (3, NULL, 'e08', 'huang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (4, NULL, '59d', 'lu', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (5, NULL, '0dc', 'yu', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (6, NULL, '989', 'wang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (7, NULL, 'e38', 'wang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (8, NULL, '0zi', 'wang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (9, NULL, 'dc9', 'xie', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10, NULL, '5ba', 'zhou', 'F', NULL);
索引下推开关默认是打开的:
show variables like 'optimizer_switch' ;
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on, index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
- 关闭索引下推
现在先把索引下推关掉:set optimizer_switch='index_condition_pushdown=off';
现在我们要查询所有姓 wang,并且名字最后一个字是 zi 的员工,比如王胖子,王瘦子。查询的 SQL:select * from employees where last_name='wang' and first_name LIKE '%zi' ;
这条 SQL 有两种执行方式:
1、根据联合索引查出所有姓 wang 的二级索引数据,然后回表,到主键索引上查询全部符合条件的数据(3 条数据)。然后返回给 Server 层,在 Server 层过滤出名字以zi 结尾的员工。
2、根据联合索引查出所有姓 wang 的二级索引数据(3 个索引),然后从二级索引中筛选出 first_name 以 zi 结尾的索引(1 个索引),然后再回表,到主键索引上查询全部符合条件的数据(1 条数据),返回给 Server 层。
很明显,第二种方式到主键索引上查询的数据更少。
注意,索引的比较是在存储引擎进行的,数据记录的比较,是在 Server 层进行的。而当 first_name 的条件不能用于索引过滤时,Server 层不会把 first_name 的条件传递给存储引擎,所以读取了两条没有必要的记录。这时候,如果满足 last_name='wang’的记录有 100000 条,就会有 99999 条没有必要读取的记录。
执行以下 SQL,Using where:
Using Where 代表从存储引擎取回的数据不全部满足条件,需要在 Server 层过滤。先用 last_name 条件进行索引范围扫描,读取数据表记录,然后进行比较,检查是否符合 first_name LIKE ‘%zi’ 的条件。此时 3 条中只有 1 条符合条件。
- 开启索引下推
set optimizer_switch='index_condition_pushdown=on';
此时的执行计划 如下:
把 first_name LIKE '%zi’下推给存储引擎后,只会从数据表读取所需的 1 条记录。索引条件下推(Index Condition Pushdown),5.6 以后完善的功能。只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。
4.6 索引的创建原则
1、在用于 where 判断 order 排序和 join 的(on)字段上创建索引
2、索引的个数不要过多。——浪费空间,更新变慢。
3、区分度低的字段,例如性别,不要建索引。 ——离散度太低,导致扫描行数过多。
4、频繁更新的值,不要作为主键或者索引。 ——页分裂
5、组合索引把散列性高(区分度高)的值放在前面。
6、创建复合索引,而不是修改单列索引。
7、过长的字段,怎么建立索引?—— 全文索引
8、为什么不建议用无序的值(例如身份证、UUID )作为索引?
4.7 索引失效
如下场景会导致索引失效:
1、索引列上使用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式、计算(+ - * /)
explain SELECT * FROM `t2` where id+1 = 4;
正确姿势:可以改为 SELECT * FROM t2
where id = 4 -1 ;
2、字符串不加引号,出现隐式转换
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
explain SELECT * FROM `user_innodb` where name = 136;
explain SELECT * FROM `user_innodb` where name = '136';
上面两条sql中,name字段是varchar类型,但是查询是未加引号,mysql查询时会发生隐式转换,将136转换为字符串类型的136,导致索引列name失效
3、like 条件中前面带%
explain select *from user_innodb where name like 'wang%';
explain select *from user_innodb where name like '%wang';
过滤的开销太大,所以无法使用索引。这个时候可以用全文索引。
4、 负向查询
NOT LIKE :
explain select *from employees where last_name not like 'wang'
需要注意的是,一个 SQL 语句是否使用索引,跟数据库版本、数据量、数据选择度都有关系。用不用索引最终都是优化器说了算。优化器的优化原则是基于 cost 开销(Cost Base Optimizer),哪种查询方式时间最快,就采用该方式去查询。
内容总结
以上是互联网集市为您收集整理的Mysql索引全部内容,希望文章能够帮你解决Mysql索引所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。