MySQL索引

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/HoldonWithYourGoal/article/details/80820265

一、索引类型

MySQL索引是一种特殊的数据结构,InnoDB的索引是表空间的一部分。索引保存着涉及所有记录的引用指针。

1、普通索引(index)

2、唯一索引

    主键索引(primary key)

    唯一索引(unique)

3、联合索引

    联合主键索引:primary key(id, name, age)

    联合唯一索引:unique(id, name)

    联合索引:index(id,name,age)

4、全文索引(fulltext)

仅有MyISAM支持。

 

三、BTree索引和Hash索引

1. B+Tree索引

★关于BTree和B+Tree:

类似于二叉树,却可以拥有更多的子节点。

BTree是一种多路自平衡搜索树,特点:

(1)键值分布在整个树的所有节点;

(2)任何关键字(索引key)出现且仅出现在一个节点中;

(3)搜索可能在非叶子节点结束;

(4)在关键字全集内做一次查找,性能逼近二分查找算法。

B+Tree为BTree的变体,也是一种多路平衡查找树。与BTree的区别:

(1)所有关键字存储在叶子节点,非叶子节点不存储真正的数据;

(2)为所有叶子节点增加了一个链指针。所以基于该索引的顺序扫描时,可以利用双向指针快速移动,效率非常高。

★B+Tree索引以B+树为存储结构实现,但是MySQL文档中将其写为BTree索引。

★B+Tree索引在MyISAM和InnoDB中有很大区别。

(1)MyISAM

2. 哈希索引

哈希索引就是采用Hash算法,把键值换算成新的哈希值,检索时不需要类似B+Tree那样从根节点逐级查找,只需一次Hash即可立刻定位到相应的位置。

MySQL只有Heap和Memory引擎才支持哈希索引(NDB也支持,但是不常用),InnoDB的自适应哈希索引(Adaptive Hash Idex)不在此列,因为这不是创建索引时可以指定的。

Hash索引与BTree索引的区别:

1.等值查询与范围查询

如果是等值查询,Hash索引有绝对优势,因为通过一次Hash就可以立马找到值(如果键值不唯一,则需先找到键所在位置,然后根据链表往后扫描,知道找到相应数据);

如果是范围查询(like,>, between),Hash索引无效(原有连续键值通过Hash算法计算后就不连续了,同理,Hash索引也不能利用索引排序)。

2. 哈希索引不支持联合索引的最左匹配规则;

3. 效率

B+Tree检索效率稳定,哈希索引波动巨大。因为哈希索引受重复键值的数量影响巨大,重复键值越多,效率越低,因为存在哈希碰撞问题。

 

二、聚集索引和非聚集索引

1. 聚集索引

聚集索引决定在物理磁盘上的物理排序,一个表只能有一个聚集索引。

如果定义了主键,InnoDB会通过主键来聚集数据;如果没有主键,InnoDB会选择一个唯一的非空索引代替;如果都没有,InnoDB会隐式地定义一个主键作为聚集索引。

聚集索引可以很大提高访问速度,因为它将索引和行数据保存在同一个B-Tree中,所以找到索引也就找到了相应的数据。但在使用聚集索引时应避免随机的聚集索引(一般指主键不连续,且分布不均匀),如使用uuid作为聚集索引,性能会很差,因为uuid值的不连续会导致增加很多的索引碎片和随机I/O,最终导致性能急剧下降。

2. 非聚集索引

与聚集索引不同的是,非聚集索引不决定数据在磁盘上的物理排序,并且B-Tree中只保存索引,不保存数据行。行数据只能通过B-Tree中的索引对应的指针来指向行数据。

 

 

四、B-Tree索引规则

1. 全值匹配

指查询条件与索引列全部匹配。如where name=XX and age=XX and sex=XX匹配(name,age,sex)的索引。

* 这里查询条件的顺序不会影响索引检索,因为MySQL查询优化器会对查询条件进行优化。

2. 匹配列前缀

指匹配列值的开头部分。如 select * from user where name like 'li%';  可以走name的索引,但是如果%在值前面就不走索引了。

3. 匹配最左前缀

指优先最左侧的索引列,直到出现范围搜索(<,>,between..and..),范围搜索不能命中索引。如(name,sex,age)的联合索引可以满足如下的查询:

name=XX;

name=XX and sex=XX;

name=XX and sex=XX and age=XX.

如果查询条件为name=XX and age=XX,则只走name的索引。

* 与查询条件顺序无关。

 

 

 

五、高效索引的策略及索引建立的注意事项

1. 模糊查询条件以%开头不能使用索引( where name like '%_li')

2. 范围查询不能使用索引。如索引为(name,age,sex),where name=XX and age>10 and sex=XX只能命中name的索引。

3. 索引列不能是表达式的一部分,也不能是函数参数,否则无法使用索引。如

        select * from user where name=concat( real_name, '_li');

4. 选择合适的索引列顺序

        建联合索引时,应当以业务需要为主,如果业务需要不能区分主次,则将选择性高的列放前面。选择性比例计算方式如下:

 

SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name;  -- index_column代表要添加前缀索引的列

5. 索引列尽量避免null值,因为空值索引列会使索引、索引的统计信息以及比较运算更为复杂。所以建表时应当指定索引列not null,设默认值。

6. 根据实际情况选择前缀索引

当需要建立索引的列很长时,会增加索引的存储空间,降低索引效率。一种策略是使用hash索引,一种是使用前缀索引,选择列的前n个字符作为索引,可以大大节省索引空间,提高效率。

ALTER TABLE table_name ADD INDEX index_name (index_column(length));

创建前缀索引,需要足够的长度保证高选择性,否则建索引意义不大;同时不能太长。可以通过如下方式选择索引长度:

(1)

SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name;  -- index_column代表要添加前缀索引的列

我们通常使用该方法来计算索引的选择性比值,比值越高索引的效率越高。

(2)

SELECT

COUNT(DISTINCT LEFT(index_column,1))/COUNT(*),

COUNT(DISTINCT LEFT(index_column,2))/COUNT(*),

COUNT(DISTINCT LEFT(index_column,3))/COUNT(*)

...

FROM table_name;

与(1)同理,测试结果越接近(1)的结果越好,最终结合实际情况选择索引长度。

★ MySQL无法使用前缀索引做order by和group by。

 

 

没有更多推荐了,返回首页