Fork me on GitHub

MySQL———优化索引

发表于 | 分类于 | |

由于二叉查找树最坏情况下会变成线性的二叉树，时间复杂度由O(logn)变为O(n)，故一般数据库索引的结构是B+Tree、Hash，我们先来介绍下B树。

B-Tree索引

定义

  1. 根结点至少包括两个孩子
  2. 树中每个节点最多含有m个孩子（m>=2）
  3. 除根结点和叶节点外，其他每个节点至少有ceil取上限（m/2）个孩子
  4. 所有叶子节点都在同一层

假设每个非终端节点中包含n个关键字信息，其中

  1. Ki（i=1..n）为关键字，且关键字按顺序升序排序K（i-1）<Ki
  2. 关键字的个数必须满足：[ceil(m/2)-1]<=n<=m-1
  3. 非叶子节点的指针：P[1],P[2]…P[M];其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其他P[i]指向关键字属于(K[i-1],K[i])的子树

B+-Tree索引

B+树是B树的变体，其定义基本与B树相同，除了

  1. 非叶子节点的子树指针与关键字个数相同
  2. 非叶子节点的子树指针P[i]，指向关键字[K[i],K[i+1]]的子树
  3. 非叶子节点仅用来索引，数据都保存在叶子节点中
  4. 所有叶子节点均有一个链指针指向下一个叶子节点

B+Tree更适合用来做存数索引

  1. B+树的磁盘读写代价更低
  2. B+树的查询效率更加稳定
  3. B+树更有利于对数据库的扫描
Hash索引

Hash结构可以直接根据key取到value，查询效率高，但是Hash索引也有一下缺点

  1. 仅仅能满足 = IN，不能使用范围查询
  2. 无法被用来避免数据的排序操作
  3. 不能利用部分索引键查询
  4. 不能避免表扫描
  5. 遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高
索引模块（T）

:memo: 为什么要使用索引

使用索引可以避免全表扫描，提高检索效率。

:memo:什么样的信息能成为索引

主键唯一键等，能让数据具备一定区分性的字段。

:memo:索引的数据结构​

B+树，Hash，BigMap，MySQL不支持BigMap，Innodb和MyISAM不显式支持Hash。

:memo: 密集索引和稀疏索引的区别

  1. 密集索引文件中的每个搜索码值都对应一个索引值
  2. 稀疏索引文件只为索引码的某些值建立索引项

MyISAM不管是主键索引、唯一键索引或者普通索引，其索引均属于稀疏索引，而InnoDB有且只有一个密集索引

  1. 若一个主键被定义，该主键则作为密集索引
  2. 若没有主键被定义，该表的第一个唯一非空索引则作为密集所以
  3. 若不满足以上条件，innodb内部会生成一个隐藏主键(密集索引)
  4. 非主键索引存储相关键位和其对应的主键值，包含两次查找

索引衍生问题，MySQL为例(T)

:memo:如何定位并优化慢查询Sql

1.根据慢日志定位慢查询sql

1
2
3
4
5
6
7
8
show variables like '%quer%';	#1.查询变量
show status like '%slow_queries%'	#2.查询慢查询数量

set global slow_query_log = on;`	#打开查询日志
set global long_query_time=1;	#设置慢查询时间为1s

#3.extra是Using filesort，此时查询时间为0.003s
EXPLAIN SELECT shop_price FROM product ORDER BY shop_price DESC;

2.使用explain等工具分析sql

1
2
3
4
5
6
7
8
explain关键字段 

type:
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all(all代表全表查询)

extra:
Using filesort:表示MySQL会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL中无法利用索引完成排序操作称为“文件排序”
Using temporary：表示MySQL在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。

3.修改sql或者尽量让sql走索引

1
2
3
4
5
6
7
8
9
10
11
12
EXPLAIN SELECT shop_price FROM product ORDER BY shop_price DESC;	#extra是Using filesort

EXPLAIN SELECT cid FROM product ORDER BY cid DESC;	#4.cid使用了索引，用cid代替shop_price

alter table product add index idx_name(shop_price)	#5.给shop_price加索引

SELECT shop_price FROM product ORDER BY shop_price DESC;	#使用索引后查询时间为0.000s

EXPLAIN SELECT shop_price FROM product ORDER BY shop_price DESC;

SELECT COUNT(*) from product;	#耗时0.001s,运用主键idx_name，是sql优化器选择的
SELECT COUNT(*) from product force index(PRIMARY);	#将索引变为主键 耗时0.005s

:memo:联合索引的最左匹配原则的成因

  1. 最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询(>,<,between,like)就停止匹配，比如a=3 and b=4 and c>5 and d=6 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  2. =和in可以乱序，比如a=1 and b=2 and c=3建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
  3. 联合索引的最左匹配原则的成因:mysql创建复合索引的规则是首先会对复合索引的最左边，也就是索引中的第一个字段进行排序，在第一个字段排序的基础上，在对索引上第二个字段进行排序，其实就像是实现类似order by 字段1，字段2这样的排序规则，那么第一个字段是绝对有序的，而第二个字段就是无序的了，因此一般情况下直接只用第二个字段判断是用不到索引的，这就是为什么mysql要强调联合索引最左匹配原则的原因。

:memo:索引是建立得越多越好吗

  1. 数据量小的表不需要建立索引，建立会增加额外的索引开销
  2. 数据变更需要维护索引，意味着更多的索引意味着更多的维护成本
  3. 更多的索引也需要跟多的存储空间