深入Mysql 索引实现及优化

索引

Mysql索引文件存储在磁盘上,衡量一个索引实现的数据结构优劣的标准,就是减少索引搜索产生的磁盘I/O次数。

实现

B+树是一种 树型数据结构,通常用于数据库操作系统的文件系统中。B+ 树的特点是能够保持数据稳定有序,其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入,这与二叉树恰好相反。

B+树的特性

1.所有关键字都出现在叶子结点的链表中(稠密索引),且链表中的关键字恰好是有序的;
这一点很好理解,所有结点自上而下搜索插入位置,自底向上插入。

2.不可能在非叶子结点命中;
3.非叶子结点相当于是叶子结点的索引(稀疏索引),叶子结点相当于是存储(关键字)数据的数据层;

InnoDB 中的各种索引

InnoDB B+树索引内容按索引类型不同,内容也不同

主键索引

主键索引

可以看到 InnoDB的索引及数据文件,主键索引的B+树叶子结点,就是主键对应的数据和数据地址。

一般索引

比如用户表中的用户姓名 Name 的单列 普通索引,索引树的非叶子结点存储的是主键的信息,叶子结点存储的是索引字段 Name 数据,和数据地址,所以用一般索引查询,定位了叶子结点之后,如果Sql查询的结果集,包含了除Name的其他字段,那么还需要再根据查询到的主键,去主键索引再次查询,获取对应列的其他信息,这个过程,称为回表

覆盖索引

如一般索引中提到的 回表 操作 ,比如:

# 创建普通索引
ALTER TABLE User ADD INDEX age (age)
# sql
select  * from  User where age between  3 and 5

这样一条在用户表中查询age 在 3到 5 之间的 sql,假设用户表有 age 索引,我们知道的,在索引的叶子结点上,有连续的、密集的、稠密的、有序的链式索引数据,非常适合这种范围查询,但是不可避免的,再查询一次索引之后,还需要回表的操作,利用如下的sql语句,可以使用覆盖索引来有效的避免回表的操作,提升查询效率,缩减查询的时间,比如有一个高频查询 按照身份证 来查询 名称的查询,就可以建立 (name,card) 的联合索引来在这个查询中形成覆盖索引,避免回表:

#创建联合索引
create index card_c_name on User(name,card)
#查询
select  name from User where card = "320122198373838383"

前缀索引

那么,如果此时还是那个 User 表,又有一个低频查询,要求是 按照身份证查询地址,是否又要建一个 联合索引 来满足这个查询呢?

# 新建索引
create index card_c_addr on User(addr,name)
# sql
select  name from User where card = "320122198373838383"

实际上,在 覆盖索引 中,建立的 (name,card) 也可以配合这种需要根据 name 来查询被作为索引使用,这就是 B+树的索引的 最左前缀匹配,形如 (a,b,c) 的联合索引,可以满足查询 a,查询 a,b,和 a,b,c 的查询,但是如果是查询条件只有 b 的话,就无法使用了,所以还需要额外维护一个b的索引,这需要在创建索引的时候,对索引内部的顺序有一个考量。

如果在实际情况中,即需要 (a,b) 又需要 ab 的单独查询索引,那么和时候我们要考量的,就是 a 和 b 哪一个的 单独空间占用 较少了,来决定联合索引的顺序。

唯一索引

顾名思义,唯一索引就是从sql-Server层限制索引列的数据唯一性,如果能够确保从业务上保证数据唯一性,即不会重复插入,那么不建议使用唯一索引,使用唯一索引会在插入时带来额外的开销,影响插入/更新语句的执行速度。

# 创建唯一索引
ALTER TABLE User ADD UNIQUE (column_list)

索引误选

有的时候,即使我们根据业务需求等因素,综合考量之后创建了合适的索引,但是sql 引擎仍然会 "倔强" 的不使用我们为他准备好的索引,这是为什么呢?

举个例子:
这里有一张表t,有10w行数据,a,b 列,分别是1到10w。分别创建单独索引 a,b 。

# 查询 表t a 1到1000;b为 5w到10w 的
explain select * from t    where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

这里即使不看执行计划,我们也能猜想到,优化器应该使用 a 索引来进行加速查询,因为,a 的范围更小,而实际上,我们可以看到执行计划显示,优化器选择 b 索引,查询行数是50128条,查询时间 40ms

如何解决这种 索引误选 的情况呢 ?

  • 1 force index :缺点就是需要提前指定索引列 ,我们实际开发中,可能更多的是在慢查询日志中进行分析,而不会在开发阶段就特意去指定sql使用的索引,比如上面这个sql语句,难道我们会预先想到优化器会 弃a择b
# force index 
explain select * from t force index(a)   where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
# excute result
rows 999      1.2ms

可以看到,强行指定了使用的索引之后,优化器使用了a索引,执行结果符合我们的预期,效率也提高了40倍

  • 2 干预sql语义
    在默认的sql中,因为排序使用的是b ,所以优化器认为使用b索引,可以减少排序的查询次数。那么我们修改排序的字段为 b,a 试试:
# sql
explain select * from t   where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;
# execute result
rows 999 , index a ; 

可以看到,这里因为 limit 1 ,所以无论是按照a还是b排序,查出的结果都不会变 。按照b,a 排序,优化器 就去除了,b索引排序带来的优势,自然选择了a 。但是改了sql的业务逻辑 ,sql的语义,这样并不好。

  • 3 根据 数据特征 诱导索引选择
# explain 
explain select * from (select * from t    where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100  ) alias limit 1  ;

发现还是选择b ,rows 5w 。

# explain 
explain select * from (select * from t    where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1000  ) alias limit 1  ;

扩大到1000 ,rows 999 选择了 a 根据数据特征诱导了优化器,通过把 limit 提升,让优化器意识到 使用b为索引排序,再去找a的代价很大。

  • 4删除索引b
    最直接的办法,如果业务确实不需要b索引,有时候说不定可以尝试一下。

给字符串建立索引

对于一个字符串字段来说,建立字符串对应的完整索引,比较占用空间,可以通过一些办法来优化字符串索引:

  • 前缀索引
    通过建立前缀索引,如:email(6),可以节省创建字符串索引带来的空间占用,但是,只要使用了前缀索引,在查到结果集之后就需要 回表 ,就是查询一次 主键索引 ,主索引上的这个字段,是否完整的等于查询值,也就是说必须回表,无法使用 (id,email) 这张覆盖索引。

如何查看区分度
select
count(distinct nickname) as L, # 100
count(distinct left(nickname,1) ) as L1, # 10
count(distinct left(nickname,2) ) as L2, # 30
count(distinct left(nickname,3) ) as L3, # 50
count(distinct left(nickname,4) ) as L4 from User2 # 80
区分度分别为 10% ~ 80%,区分度越高,查询效果越好,联合忍受的容错率,来进行合适的前缀索引建立。

  • 建立hash 或 reverse
    有些时候,建立前缀索引,可能需要很长很长才能建立到一个合适的索引,比如身份证这种字段,前6位的区分度很低,比如南京建邺区就都是320105,上海静安区就是 310106 。可以采用 新建一列身份证 hash 或者 用 mysql 的 reverse 函数

    如果采用的是 建立Hash字段,那么在存储身份证时,就要创建对应的 card-hash-value ,然后在查询时候使用,这种查询需要注意的是,需要查询 card-hash-value原字段

    如果采用的是 mysql 的 reverse 函数 ,那么就可以直接查询身份证的后面字段,这样区分度较高。

    值得一提的是,这两个办法都不支持 范围扫描

合理的索引能够帮助我们加速查询的效率。

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页