前文
继上篇:MySQL进阶必备知识(二):行格式、数据页结构,本篇继续分享MySQL另一大知识点:索引。说到索引,基本是MySQL最最核心的一点了,当然不同的存储引擎实现的方式不同,本篇还是基于MySQL默认的innodb引擎来阐述。
首先提几个问题,带着问题往下看(以下指代的都是innodb):
- 什么是索引?为什么要有索引?怎么使用索引?
- 索引的类型都有哪些?聚簇索引、联合索引、唯一索引、普通索引、覆盖索引、二级索引都指的哪些?
- 创建一个包含2个列的索引,和创建2个包含1个列的索引,差别是?
- 除了B+tree的索引,你还知道其他索引?innodb又支持多少种索引?
- 索引的优缺点(如果优点你仅仅能提出加快查询,那这篇文章值得一看)
- 是否为字段创建索引时,应该考虑什么?如果要创建前缀索引,该如何选择?
- 二级索引的“回表”指的是什么?索引一定比全表扫描要快吗?
索引简介
在认识新概念的时候,基本就三个问题:是什么,为什么,怎么用。
- 索引是什么?
答:简单地说,就是存储引擎为了加快找到记录的数据结构。在innodb里,这种数据结构就是指的B+ Tree。 - 为什么要有索引?
答:当然是为了加快找到记录! - 索引怎么用?
答:可以在建表的时候定义,也可以在建完表后用alter增加,具体如下:
# 建表时创建age的索引
create table demo(age int,key idx_age(age));
# 建完表后创建age的索引
create table demo(age int);
alter table demo add index idx_age(age);
从上一篇数据页和行格式我们知道,如果全表扫描找一条数据,需要遍历数据页,然后再遍历页里的记录,如果这张表数据达到百万、千万级,那速度可想而知。索引就是构造了一个“目录”的形式,因为记录都是存在页里的,而在MySQL里要定位到对应的页则通过页号+空间号(暂时了解)即可,所以通过索引构造目录,在目录里存放着页号+该页的最小记录(对应的索引列),然后通过目录定位到对应的页,再找到对应的记录即可。
关于索引的简介到此,另外补充(关联前两篇):
1.B+Tree构造的索引,存放的都是索引页(页的类型一种),而在这里又要区分叶子页和非叶子页,亦称数据页和目录页,这两者存放的记录通过行格式里record_type来标识,1为目录页,0为数据页。
2.索引的构成依赖于排序,以哪个列构造索引,则需要按照对应的列在目录页开始排序,然后数据页也是从小到大排序,方便查找记录时快速定位!而排序规则又依赖于第一篇介绍的collation。
索引的类型
索引的类型有很多种分法,比如:
- 按照存储引擎:哈希索引(memory)、B+Tree索引(innodb)、B-Tree索引(MyISAM)等等
- 按照innodb类型:聚簇索引、二级索引
- 按照innodb功能:唯一索引、普通索引、联合索引、覆盖索引、前缀索引
看着有点多,但是存储引擎的分法了解即可,知道有这么个索引就好。重点聊下innodb的索引类型。不过哈希索引也是蛮重要的,这里简单介绍:
- 哈希索引只有memory引擎显示支持(memory引擎的默认索引)!(与之相对的innodb某种情况下隐式创建)
- 哈希索引的优势:即数据结构散列表的优势!速度贼快,要远远快于B+Tree,因为都不需要定位,直接匹配即可。
- 主要的劣势:哈希冲突、不支持范围查找(仅支持key等值查找)
- 应用:在《高性能MySQL》里提到,由于hash索引的查询高效,所以在查找一些复杂的字符串可以手动创建hash索引,比如查找url这种,有的很长,如果真的通过字符串比对效率还是很慢,所以可以增加一个字段(比如命名MD5_url),然后在代码里实现封装亦或者在MySQL里定义个触发器也行。
聚簇索引和二级索引
innodb里最重要的索引->聚簇索引,“聚簇”咋听起来比较难记,实际上理解了就好记,因为含义指的是数据紧密的聚集在一起,这也是这个索引的定义,将所有的数据都存储在该索引上,而聚簇索引也是innodb默认在表创建时创建的!,另外,聚簇索引是依赖于主键来进行排序的,而在行格式那章讲过,在表中如果没有主键,则innodb默认会帮忙建一个主键id!所以定义是:
- 页的记录、页与页之间、目录项记录全部都是按主键大小进行排序
- B+树的叶子节点存放的是完整的数据
- innodb引擎默认帮我们建立
与聚簇索引相对的即二级索引,即我们自己手动建的都是二级索引,二级索引与聚簇索引的区别是:
- 页的记录、页与页之间、目录项记录全部都是按所建索引列的大小排序
- B+树的叶子节点存放的是索引列+主键(聚簇索引引则是所有数据)
- 目录项(非叶子节点)存放的是索引列+页号(聚簇索引则是主键+页号)
- 拿到对应的索引列后需要回表操作
上面的第四点,即回表操作是二级索引最大的特点,因为聚簇索引所在的B+Tree,当在目录项找到了所在页号后,就可以在页号直接找到对应的记录,但二级索引不行,只能找到对应记录所在的主键,然后通过主键回到聚簇索引再进行一次查找!所以二级索引需要查找二次!
这里另外提一句,MyIsam的索引全部都是“二级索引”格式,即它们都需要回表操作!这是因为MyIsam存储数据的格式是将索引和记录分开,记录以一行行的形式存在数据文件里(每一行对应行号),而索引里就存放着索引列+行号,即使是主键索引,也是主键+行号,所以都需要回表!
联合索引、覆盖索引、前缀索引、唯一索引、普通索引
唯一索引、普通索引
在二级索引下,又分为几种索引,其中唯一索引和普通索引又包括了联合索引、覆盖索引、前缀索引,这两者顾名思义就是前者针对字段值唯一的字段建索引(如果字段不唯一,则会报错),后者则可以针对任何字段内容建立索引。两者的键索引语句也不相同,如下:
唯一索引:alter table xx add unique index_name(column);
普通索引:alter table xx add index index_name(column);
当我们执意要给重复字段增加唯一索引,则报错如下:
为什么要有唯一索引的存在呢?就是因为它与聚簇索引一致,当我们要查询这两者索引里的一条数据时,通过目录索引可以极快地定位到对应的数据页,然后定位到对应的记录,就是因为没有重复值!而这个速度被innodb称之为const,即contance常量速度。熟悉算法的应该知道O(1)即是我们说的const速度!
联合索引
说到联合索引就回到了所提问题的第三个,即单列索引和多列索引!前面所讲的索引都是基于单列索引,而在innodb里还有多列索引:指的是一个索引(B+Tree)服务于多个字段。定义如下:
—> 为多个列建立索引,当第一个列相同的时候比较对第二个列进行排序所建的索引
所以为多个字段建立多个单列索引,和为多个字段建立1个单列索引是大大不同的,区别如下:
- 前者要建立和维护多个B+Tree,后者则只建立一个
- 当where条件包含多个索引列,前者可能需要用到index merge(索引合并,即多个索引查找的内容取交集或并集后再回表查找!);后者在满足多列匹配规则后可以直接获取结果然后回表!
上述提到的多列匹配规则如下:
- 匹配左边的列:因为联合索引排序的关系,只能从最左边的列开始才能用上索引
- where条件把联合索引顺序颠倒:即使在where里顺序不同,因为优化器的原因,也不影响其匹配最左边的规则;
- 匹配范围值:即当最左边列相等时,第2个列是范围查询也能使用;但最左边列是范围查询,则后续的列无法用到索引。
覆盖索引
讲完联合索引不得不说覆盖索引,定义:
- 当查询的列和索引一致的时候,就可以直接用二级索引而不需要回表,此时速度是最快的!也称为覆盖索引
覆盖索引可谓是innodb里优化查询的非常重要的手段,因为它的定义,所以我们再select的时候最好不要select *,而是select需要的字段,这样可以减少回表操作!
MySQL里最拖累查询速度的是:随机IO!指的就是回表操作,为什么索引那么快?因为排序后建立的B+Tree就是顺序IO,顺序IO即指的是在磁盘上读取时是相邻地址的数据,而随机IO顾名思义则是随机地址的数据。所以二级索引读取索引列很快,但是回表都是随机IO,速度则非常慢!
所以用覆盖索引的速度是很快的!
前缀索引和索引选择性
前缀索引则是innodb在索引上提供的一大新类,我们知道索引是按索引列从小到大排序的,而针对字符串这种的比对,就是从头到尾1个字符1个字符的比对,那如果有的字符串几十上百个字母,那花在比较的时间就太长了,这个时候有两个选择,一个是把字符串压缩或加密成MD5、SHA这种,利用hash的特性快速匹配。另一个也就是用前缀索引,因为我们完全没必要把整个字段都作为索引列,可以取该列的前几位比如前10位作为索引列!
拖累索引最大的因素就是重复值!重复值过多,则排序后的结果就越难快速查找,所以重复值越少越好!在《高性能的MySQL》里提到了如何选择索引,如果要对一个字段建立索引,可以通过获取该列的基数来判断重复值的多寡,如下语句:
#获取索引列的不重复值
select count(distinct(字段名))/count(*) from table
而在前缀索引这里,可以通过left字段来分别获取前几位,比如下面的语句:
#分别获取前3、4、5列的不重复值来比较
select count(distinct(left(字段名,3)))/count(*) as sel3,
count(distinct(left(字段名,4)))/count(*) as sel4,
count(distinct(left(字段名,5)))/count(*) as sel5,
from table
当然上述只是重要参考,并不一定越大就越好,因为如果数据分布不平均,也会导致平均情况虽然好,但是最坏情况却很差!
索引的优缺点
索引的优点:
- 大大减少了服务器需要扫描的数据量
- 辅助服务器避免排序和创建临时表(服务于比如去重、分组)
- 将随机IO变为顺序IO
索引的缺点:
- 维护B+Tree的成本很高,如果频繁插入、删除,则每一次都得在所有的B+Tree进行增加、删除,同时如果涉及到页分裂,则会更复杂
- 回表的时间过长,所以在大表里(比如百万级、千万级数据),因为回表的随机IO过多,还不如全表扫描!
如何建立合适的索引
- 只为用于搜索、排序或分组的列创建索引
- 考虑列的基数:即列的重复值,重复值越多,则排序越难,就无法快速查找,即索引就用不上了
- 索引字符串值的前缀:KEY idx_demo(name(10), birthday, phone_number)
-比如name(10),就可以根据name的前10位排序,这样在存储大字符串的时候是非常好的!但缺点就是排序时无法使用
总结
本次就总结到此,关于索引的话题太多了,如何说也说不完。感兴趣的朋友可以拓展阅读《高性能的MySQL》第五章:创建高性能的索引!