一、什么是索引?
1、对数据库表中一列或多列的值进行排序的一种数据存储结构。 2、类似书的目录,使用索引可以快速访问数据库表中的行记录数据。 3、基本原理:存储引擎通过遍历索引定位基表中的行,然后返回给Server层,再去为这些行记录数据进行where条件过滤。 4、InnoDB存储引擎的叶子结点数据是按照主键rowid进行排序的,目的是方便对磁盘做顺序I/O
二、索引的数据结构
MySQL数据库使用B+tree索引结构,B+tree是由二叉树 -> 平衡二叉树 -> B-tree树逐步演化而成。
1、二叉树结构
每个结点至多有2个子结点 二叉树有左右序之分,次序不能颠倒 二叉树中,左子树的键值永远比右子树的键值小,并且小于根结点的键值
2、平衡二叉树结构
- 解决了二叉树节点深度增加,查询的均分复杂度上升的问题,拥有比二叉树更快的查询速度 - 条件: 左右两个子树的高度差的绝对值不超过1 左右两个子树也都是平衡二叉树
3、B-tree结构(Btree、B树)
一个结点可以拥有多于2个子结点的多叉查找树 所有叶子结点都在同一层 叶子结点不包含任何关键字信息
4、B+tree索引结构
B-tree结构的变体,双向链表结构,一种多路搜索树 所有关键字信息都存放在叶子结点,并包含这些关键字记录的指针 叶子结点按照关键字大小顺序连接,因此访问关键字的顺序是连续性的,不需要回溯上一个结点 所有数据都存放在叶子结点中
三、B+tree索引分类
show index from 表名, 可以查看表中有哪些索引,使用expalin命令可以查看SQL语句的执行计划(优化器对SQL语句的优化执行方案)。
1、聚集索引和普通索引
- B+tree索引分为两大类:聚集索引和非聚集索引
聚集索引
叶子结点存放表中所有行数据记录的信息; 对于聚集索引,数据即索引,索引即数据; 创建主键时会自动创建;
普通索引
叶子结点不包含所有行的数据记录,只存放索引字段的键值和行记录数据的主键值; 检索数据时,通过普通索引叶子结点找到主键,再通过主键来获取行数据记录;
MySQL语法:
ALTER TABLE 表名 ADD INDEX 索引名(列名); CREATE INDEX 索引名 ON 表名(列名); create index ename_index on emp(ename); drop index ename_index on emp; show index on emp;
2、主键索引(聚集索引)和唯一索引
主键索引必须满足三个条件:
主键索引就是聚集索引
主键值必须唯一;
不能包含NULL值;
必须保证值是自增的(自增保证insert是顺序的,基于磁盘的顺序访问特性,提高了存取效率);
- 唯一索引必须满足一个条件:
值必须是唯一的,但是值允许是NULL;
- 一张表只能有一个主键索引,但是唯一索引可以有多个
- MySQL语法:
创建主键索引:创建主键时自动创建;
创建唯一索引:ALTER TABLE emp add unique(列名);
3、覆盖索引
- 通过索引就可以直接返回查询数据,不需要在查询索引之后再去回表查询数据,减少了磁盘I/O操作,因此查询速度快
- 使用explain命令查看SQL语句的执行计划
extra列出现Using index关键字,表明SQL语句使用了覆盖索引; - 符合覆盖索引的场景
必须在select列出所需要的列,而不能写select * from emp;
4、前缀索引
- BLOB、TEXT、较长的VARCHAR类型的列,为前面最左侧的若干个字符创建的一种索引
- MySQL语法:
ALTER TABLE emp add key(columnName(prefixLength)); - 这种索引很小,所查询较快
- 不足之处:
不能在ORDER BY和GROUP BY中使用;
不能用做覆盖索引
5、联合索引(复合索引)
- 在表中两个或者两个以上的列上创建的索引
- 利用索引上的附加列,可以缩小检索的段池范围,更快的检索数据
- MySQL语法:
create index idx_c1_c2 epm(列名c1,列名c2); - 在使用过程中,必须要满足最左前缀原则:
创建索引时,将选择性高的列放在前面;
一条查询语句可以只使用索引中的一部分,但是必须从最左索引列开始;
如使用索引idx_c1_c2,可以检索c1或者c1,c2,但是不能直接检索c2;
- 例外情况select * from emp where c1=? or c2=?
不能使用联合索引,需要创建两个单列索引或者考虑使用union;
6、哈希索引
- 采用哈希算法,把键值换算成哈希值
- 只能进行等值查询,不能进行排序、模糊查找、范围查询
- 查询速度快,检索时不需要像B+tree从根节点到叶子结点逐级查找,只需一次哈希算法即可立刻定位到数据位置
四、使用explain命令查看SQL语句的执行计划
1、type列,表示查询类型
- 出现all关键字,说明全表扫描
2、key列,表示是否使用索引
- 出现null关键字,说明没有使用索引
3、rows列,表示在SQL语句执行过程中扫描行数的估算值
- 数值越大,说明需要扫描的行数越多,耗时越长
4、extra列,
- 出现Using filesort关键字,说明排序使用了文件系统,性能较差
- 出现Using temporary关键字,说明使用了内存临时表空间,性能较差
- 出现Using index condition关键字,说明使用了索引过滤(ICP优化),where条件可以使用索引,在存储引擎层直接做过滤操作
- 出现Using MRR关键字,说明范围扫描使用了MRR优化特性,
把普通索引的叶子结点上查找到的主键值的集合存储到read_rnd_buffer;
在read_rnd_buffer中对主键值排序;
最后利用排序后的主键值集合进行顺序回表读取行记录数据;
因此达到了进行顺序I/0访问磁盘的目标,避免查询过程中出现I/0随机访问,提高了I/O效率;
- 出现Using join buffer(Batched Key Access)关键字,说明表连接使用BKA优化特性,
BKA是提高join性能的算法,在读取被join表的记录时使用顺序I/0;
当MySQL使用索引访问第二个join表时,使用一个join buffer收集第一个操作对象生成的相关列值,批量传递给引擎层做索引查找;
使用时基于MRR特性;
5、filtered列,表示返回结果的行占需要读取到的行(rows列的值)的百分比
五、索引的利弊
1、使用索引的好处
- 提高数据检索效率
- 提高聚合函数的效率 (组函数 max(), min()...)
- 提高排序效率
- 使用覆盖索引可以避免回表
2、使用索引的正确方式
- 适合创建索引的列:
经常被查询的条件列;
经常用于表连接的列;
经常排序分组的列;
- 不适合创建索引的列:
选择性低的字段不要创建索引,如性别字段只能筛选出1/2的数据,区分度不大,不该创建索引;
很少查询的列不要创建索引,需要事先和需求方沟通确认;
大数据类型字段不要创建索引,如text、blob类型字段不要创建索引;
- 避免的情况
尽量避免不要使用NULL,MySQL对含有NULL值的列很难进行查询优化,会让索引、索引的统计信息及比较运算更加复杂,因此DBA经常推荐使用NOT NULL;
尽量在MySQL客户端的应用程序中做运算和判断,不要让数据库做各种运算;
3、索引失效的情况
- 通过索引扫描的行记录数超过全表的30%,优化器不会走索引,变成全表扫描
- 联合索引中,第一个查询条件不是最左索引列
- 联合索引中,第一个索引列使用范围查询,只能使用到部分索引,有ICP出现
- 联合索引中,第一个查询条件不是最左前缀列
- 模糊查询条件列,最左以通配符%开始(可以放到子查询里)
- 两个单列索引,一个检索,一个排序,只能使用其中一个索引(查询语句中最多只能使用一个索引),这种情况考虑创建联合索引
- 查询字段有索引,但是使用了函数运算
4、索引的副作用
- 创建太多的索引,在DML操作时,将会增加维护索引的负担