MYSQL索引

1、索引作用

2、索引分类

3、索引原理

4、B+Tree数据结构

5、索引原则

6、索引缺点

1、索引作用

索引存储在存储文件中,存储文件是在磁盘中。

(1)索引对单个表的影响。索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大,花费时间越多。如果表对于查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要考虑所有数据。如果一个表有1000行,这比顺序读取至少快100倍。
(2)索引对多个表的影响。索引对多个表的影响更大。 假如有三个未索引的表 t1、t2、t3,分别只包含列 c1、c2、c3,每个表分别由含有数值 1 到 1000 的 1000 行组成。查找对应值相等的表行组合的查询如下所示:
此查询的结果应该为 1000 行,每个组合包含 3 个相等的值。如果我们在无索引的情况下处理此查询,则不可能知道哪些行包含那些值。因此,必须寻找出所有组合以便得出与 WHERE 子句相配的那些组合。可能的组合数目为 1000×1000×1000(十亿),比匹配数目多一百万倍。很多工作都浪费了,并且这个查询将会非常慢,即使在如像 MySQL 这样快的数据库中执行也会很慢。而这还是每个表中只有 1000 行的情形。如果每个表中有一百万行时,将会怎样?很显然,这样将会产生性能极为低下的结果。如果对每个表进行索引,就能极大地加速查询进程,因为利用索引的查询处理如下:
1) 如下从表 t1 中选择第一行,查看此行所包含的值。
2) 使用表 t2 上的索引,直接跳到 t2 中与来自 t1 的值匹配的行。类似,利用表 t3 上的索引,直接跳到 t3 中与来自 t1 的值匹配的行。
3) 进到表 t1 的下一行并重复前面的过程直到 t1 中所有的行已经查过。
在此情形下,我们仍然对表 t1 执行了一个完全扫描,但能够在表 t2 和 t3 上进行索引查找直接取出这些表中的行。从道理上说,这时的查询比未用索引时要快一百万倍。

2、索引分类

索引类型主要有B-TREE索引、哈希索引、聚簇索引等等。如果不指明类型,多半指的是B-TREE索引。索引是在存储引擎层实现的,InnoDB使用的是B+Tree索引。这里,只介绍B+TREE索引。

索引有如下的几种情况:
l INDEX索引:通常意义的索引,某些情况下KEY是它的一个同义词。索引的列可以包括重复的值。
l UNIQUE索引:唯一索引,保证了列不包含重复的值,对于多列唯一索引,它保证值的组合不重复。 PRIMARY KEY索引:也UNIQUE索引非常类似。事实上,PRIMARY KEY索引仅是一个具有PRIMARY名称
UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY。

创建索引:
可以用alter table add 添加索引,名字可选
也可以用create index indexname on tablename(columnlist);
columnlist是几个列的集合,即多个列构成一个索引。
ALTER TABLE tbl_name ADD INDEX index_name (column_list)
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
ALTER TABLE tbl_name ADD PRIMARY KEY index_name (column_list)
名字是必须写的。CREATE INDEX可对表增加普通索引或UNIQUE索引,不能用CREATE INDEX语句创建PRIMARY KEY索引。

删除索引: drop index indexname on tablename

mysql> ALTER TABLE student DROP PRIMARY KEY, DROP INDEX index_name ON tbl_name
或者:
alter table tablename drop index ........

3、索引原理

考虑如下情况,假设数据库中一个表有10^6条记录,DBMS的页面大小为4K,并存储100条记录。如果没有索引,查询将对整个表进行扫描,最坏的情况下,如果所有数据页都不在内存,需要读取10^4个页面,如果这10^4个页面在磁盘上随机分布,需要进行10^4次I/O,假设磁盘每次I/O时间为10ms(忽略数据传输时间),则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引,则只需要进行log100(10^6)=3次页面读取,最坏情况下耗时30ms。这就是索引带来的效果,很多时候,当你的应用程序进行SQL查询速度很慢时,应该想想是否可以建索引。
也就是说,索引减少了I/O的读取次数,加快了数据的查询。

4、B+TREE数据结构

B+TREE结构是每一个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历。

B+TREE树种数据是按顺序存储的,利用二分查找及在关键字中查找交叉的过程。

如上图,是一颗b+树,关于b+树的定义可以参见B+树,这里只说一些重点,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

模拟查找文件29的过程:
(1) 根据根结点指针找到文件目录的根磁盘块1,将其中的信息导入内存。【磁盘IO操作1次】
(2) 此时内存中有两个文件名17,35和三个存储其他磁盘页面地址的数据。根据算法我们发现17<29<35,因此我们找到指针p2。
(3) 根据p2指针,我们定位到磁盘块3,并将其中的信息导入内存。【磁盘IO操作2次】
(4) 此时内存中有两个文件名26,30和三个存储其他磁盘页面地址的数据。根据算法我们发现26<29<30,因此我们找到指针p2。
(5) 根据p2指针,我们定位到磁盘块8,并将其中的信息导入内存。【磁盘IO操作3次】
(6) 此时内存中有两个文件名28,29。根据算法我们查找到文件29,并定位了该文件内存的磁盘地址。

分析上面的过程,发现需要3次磁盘IO操作和3次内存查找操作。关于内存中的文件名查找,由于是一个有序表结构,可以利用折半查找提高效率。至于3次磁盘IO操作时影响整个B-tree查找效率的决定因素。

实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

b+树性质

1.通过上面的分析,我们知道IO次数取决于b+数的高度h,假设当前数据表的数据为N,每个磁盘块的数据项的数量是m,则有h=㏒(m+1)N,当数据量N一定的情况下,m越大,h越小;而m = 磁盘块的大小 / 数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的,如果数据项占的空间越小,数据项的数量越多,树的高度越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。
2.当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。

5、创建索引原则

1)最左前缀匹配原则
假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列, 因此它们也会自动地按照state/city和state次序排列。这意味着,即使你在查询中只指定了state值, 或者指定state和city值,MySQL也可以使用这个索引。因此,这个索引可以被用于搜索如下所示的数据列组合:
state, city, zip
state, city
state
MySQL 不能使用不涉及左前缀的搜索。例如,如果按 city 或 zip 进行搜索,则不能使用该索引。如果要搜索某个州以及某个 zip 代码(索引中的列1和列3),则此索引不能用于相应值的组合。但是,可利用索引来寻找与该州相符的行,以减少搜索范围。

2)不能过度使用索引
要以为索引“越多越好”,什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能,这一点我们前面已经介绍过。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。如果有一个索引很少利用或从不使用,那么会不必要地减缓表的修改速度。此外,MySQL 在生成一个执行计划时,要考虑各个索引,这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多,也可能会使 MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。
如果想给已索引的表增加索引,应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是,则就不要费力去增加这个索引了,因为已经有了。

3)使用唯一索引或者区分度较高的
考虑某列中值的分布。对于惟一值的列,索引的效果最好,而具有多个重复值的列,其索引效果最差。例如,存放年龄的列具有不同值,很容易区分各行。而用来记录性别的列,只含有“M”和“F”,则对此列进行索引没有多大用处(不管搜索哪个值,都会得出大约一半的行)。区分较高意思及时重复的不多。

4)给指定的列加索引
给where语句及联查指定的列添加索引

5)索引列排序
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
6)不在索引进行计算
select * from users where YEAR(adddate)<2007; 将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成select * from users where adddate<‘2007-01-01’;
7)like语句
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

6、索引缺点

索引文件要占磁盘空间。如果有大量的索引,索引文件可能会比数据文件更快地达到最大的文件尺寸。其次,索引文件加快了检索,但增加了插入和删除,以及更新索引列中的值的时间(即,降低了大多数涉及写入的操作的时间),因为写操作不仅涉及数据行,而且还常常涉及索引。一个表拥有的索引越多,则写操作的平均性能下降就越大。

--------EOF---------
本文微信分享/扫码阅读