1 介绍
索引(
index
)是帮助
MySQL
高效获取数据的数据结构
(
有序
)
。在数据之外,数据库系统还维护着满足 特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构 上实现高级查找算法,这种数据结构就是索引。
特点
优势
|
劣势
|
提高数据检索的效率,降低数据库 的IO
成本
|
索引列也是要占用空间的
|
通过索引列对数据进行排序,降低 数据排序的成本,降低CPU
的消 耗
|
索引大大提高了查询效率,同时却也降低更新表的速度, 如对表进行INSERT
、
UPDATE
、
DELETE
时,效率降低
|
2
索引结构
MySQL
的索引是在存储引擎层实现的,不同的存储引擎有不同的索引结构,主要包含以下几种:
索引结构
|
描述
|
B+Tree
索引
|
最常见的索引类型,大部分引擎都支持
B+
树索引
|
Hash
索引
|
底层数据结构是用哈希表实现的
,
只有精确匹配索引列的查询才有效
,
不 支持范围查询
|
R-tree(
空间索 引)
|
空间索引是
MyISAM
引擎的一个特殊索引类型,主要用于地理空间数据类 型,通常使用较少
|
Full-text(
全文 索引)
|
是一种通过建立倒排索引
,
快速匹配文档的方式。类似于
Lucene,Solr,ES
|
上述是
MySQL中所支持的所有的索引结构,接下来,我们再来看看不同的存储引擎对于索引结构的支持情况。
索引 |
InnoDB
|
MyISAM
|
Memory
|
B+Tree
索引
|
支持
|
支持
|
支持
|
Hash
索引
|
不支持
|
不支持
|
支持
|
R-tree(
空间索 引)
|
不支持
|
支持
|
不支持
|
Full-text(
全文 索引)
|
5.6
版本之后支持
|
支持
|
不支持
|
2.1 二叉树
如果选择二叉树作为索引结构,会存在以下缺点:
- 顺序插入时,会形成一个链表,查询性能大大降低。
- 大数据量情况下,层级较深,检索速度慢
2.2 B-Tree
B-Tree
,
B
树是一种多叉路衡查找树,相对于二叉树,
B
树每个节点可以有多个分支,即多叉。
以一颗最大度数(
max-degree
)为
5(5
阶
)
的
b-tree
为例,那这个
B
树每个节点最多存储
4
个
key
,
5
个指针:
![](https://i-blog.csdnimg.cn/blog_migrate/5b40c0fae56242e077bc002300cf02ab.png)
2.3 B+Tree
B+Tree
是
B-Tree
的变种,我们以一颗最大度数(
max-degree
)为
4
(4阶)的
b+tree
为例,来看一
下其结构示意图:
![](https://i-blog.csdnimg.cn/blog_migrate/b27724919f70781d7d2a84fb6ef4be34.png)
最终我们看到,
B+Tree
与
B-Tree
相比,主要有以下三点区别:
- 所有的数据都会出现在叶子节点。
- 叶子节点形成一个单向链表。
- 非叶子节点仅仅起到索引数据作用,具体的数据都是在叶子节点存放的。
上述我们所看到的结构是标准的
B+Tree
的数据结构,接下来,我们再来看看
MySQL
中优化之后的
B+Tree
。
MySQL
索引数据结构对经典的
B+Tree
进行了优化。在原
B+Tree
的基础上,增加一个指向相邻叶子节点 的链表指针,就形成了带有顺序指针的B+Tree
,提高区间访问的性能,利于排序。
2.4 Hash
1).
结构
哈希索引就是采用一定的
hash
算法,将键值换算成新的
hash
值,映射到对应的槽位上,然后存储在 hash表中。
![](https://i-blog.csdnimg.cn/blog_migrate/8c644a44378700baf4d3a502c10d20ad.png)
2).
特点
A. Hash
索引只能用于对等比较
(=
,
in)
,不支持范围查询(
between
,
>
,
<
,
...
)
B.
无法利用索引完成排序操作
C.
查询效率高,通常
(
不存在
hash
冲突的情况
)
只需要一次检索就可以了,效率通常要高于
B+tree
索
引
3).
存储引擎支持
在
MySQL
中,支持
hash
索引的是
Memory
存储引擎。 而
InnoDB
中具有自适应
hash
功能,
hash
索引是 InnoDB存储引擎根据
B+Tree
索引在指定条件下自动构建的。
思考题: 为什么
InnoDB
存储引擎选择使用
B+tree
索引结构
?
A.
相对于二叉树,层级更少,搜索效率高;
B.
对于
B-tree
,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储
的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;
C.
相对
Hash
索引,
B+tree
支持范围匹配及排序操作;
3 索引分类
3.1
索引分类
在
MySQL
数据库,将索引的具体类型主要分为以下几类:主键索引、唯一索引、常规索引、全文索引。
分类
|
含义
|
特点
|
关键字
|
主键 索引
|
针对于表中主键创建的索引
|
默认自动创建
,
只能
有一个
|
PRIMARY
|
唯一 索引
|
避免同一个表中某数据列中的值重复
|
可以有多个
|
UNIQUE
|
常规
索引
|
快速定位特定数据
|
可以有多个
| |
全文
索引
|
全文索引查找的是文本中的关键词,而不是比 较索引中的值
|
可以有多个
|
FULLTEXT
|
3.2
聚集索引
&
二级索引
而在在
InnoDB
存储引擎中,根据索引的存储形式,又可以分为以下两种:
分类
|
含义
|
特点
|
聚集索引
(Clustered Index)
|
将数据存储与索引放到了一块,索引结构的叶子 节点保存了行数据
|
必须有
,
而且只
有一个
|
二级索引
(Secondary Index)
|
将数据与索引分开存储,索引结构的叶子节点关 联的是对应的主键
|
可以存在多个
|
聚集索引选取规则
:
- 如果存在主键,主键索引就是聚集索引。如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
- 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索
- 引
具体过程如下
:
①
.
由于是根据
name
字段进行查询,所以先根据
name='Arm'
到
name
字段的二级索引中进行匹配查
找。但是在二级索引中只能查找到
Arm
对应的主键值
10
。
②
.
由于查询返回的数据是
*
,所以此时,还需要根据主键值
10
,到聚集索引中查找
10
对应的记录,最 终找到10
对应的行
row
。
③
.
最终拿到这一行的数据,直接返回即可。
回表查询: 这种先到二级索引中查找数据,找到主键值,然后再到聚集索引中根据主键值,获取
数据的方式,就称之为回表查询。
思考题:
以下两条
SQL
语句,那个执行效率高
?
为什么
?
A. select * from user where id = 10 ;
B. select * from user where name = 'Arm' ;
备注
: id
为主键,
name
字段创建的有索引;
解答:
A
语句的执行性能要高于
B
语句。
因为
A
语句直接走聚集索引,直接返回数据。 而
B
语句需要先查询
name
字段的二级索引,然
后再查询聚集索引,也就是需要进行回表查询。
4 索引语法
1). 创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;
2).
查看索引
SHOW INDEX FROM table_name ;
3).
删除索引
DROP INDEX index_name ON table_name ;
.5 SQL性能分析
5.1 SQL
执行频率
MySQL
客户端连接成功后,通过
show [session|global] status
命令可以提供服务器状态信
息。通过如下指令,可以查看当前数据库的
INSERT
、
UPDATE
、
DELETE
、
SELECT
的访问频次:
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';
Com_insert:
插入次数
Com_select:
查询次数
Com_update:
更新次数
Com_delete: 删除次数
我们可以在当前数据库再执行几次查询操作,然后再次查看执行频次,看看
Com_select
参数会不会 变化。
![](https://i-blog.csdnimg.cn/blog_migrate/c1f625903610472b8fea6d33457ca189.png)
通过上述指令,我们可以查看到当前数据库到底是以查询为主,还是以增删改为主,从而为数据
库优化提供参考依据。 如果是以增删改为主,我们可以考虑不对其进行索引的优化。 如果是以
查询为主,那么就要考虑对数据库的索引进行优化了。
那么通过查询
SQL
的执行频次,我们就能够知道当前数据库到底是增删改为主,还是查询为主。 那假 如说是以查询为主,我们又该如何定位针对于那些查询语句进行优化呢? 次数我们可以借助于慢查询 日志。
5.2 慢查询日志
慢查询日志记录了所有执行时间超过指定参数(
long_query_time
,单位:秒,默认
10
秒)的所有
SQL
语句的日志。
MySQL
的慢查询日志默认没有开启,我们可以查看一下系统变量
slow_query_log
。
![](https://i-blog.csdnimg.cn/blog_migrate/895f928f8057ec05fc69d1665b1be805.png)
如果要开启慢查询日志,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
#
开启
MySQL
慢日志查询开关
slow_query_log
=
1
#
设置慢日志的时间为
2
秒,
SQL
语句执行时间超过
2
秒,就会视为慢查询,记录慢查询日志
long_query_time
=
2
配置完毕之后,通过以下指令重新启动
MySQL
服务器进行测试,查看慢日志文件中记录的信息
/var/lib/mysql/localhost-slow.log
。
systemctl restart mysqld
然后,再次查看开关情况,慢查询日志就已经打开了.
![](https://i-blog.csdnimg.cn/blog_migrate/8bcf89eb6d3ef48588ae5835b58c98cb.png)
5.3 profile详情
show profiles
能够在做
SQL
优化时帮助我们了解时间都耗费到哪里去了。通过
have_profiling
参数,能够看到当前
MySQL
是否支持
profile
操作:
SELECT @@have_profiling ;
![](https://i-blog.csdnimg.cn/blog_migrate/d006b6fc829892694204968270bfbca2.png)
可以看到,当前
MySQL
是支持
profile
操作的,但是开关是关闭的。可以通过
set
语句在
session/global
级别开启
profiling
SET profiling = 1;
执行一系列的业务SQL的操作,然后通过如下指令查看指令的执行耗时:
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profiles for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
查看每一条
SQL
的耗时情况
:
![](https://i-blog.csdnimg.cn/blog_migrate/0c68bbe79cbefd3f693f805b94b0c460.png)
5.4 explain
EXPLAIN
或者
DESC
命令获取
MySQL
如何执行
SELECT
语句的信息,包括在
SELECT
语句执行 过程中表如何连接和连接的顺序。
语法
:
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
Explain
执行计划中各个字段的含义
:
字段
|
含义
|
id |
select
查询的序列号,表示查询中执行
select
子句或者是操作表的顺序
(id
相同,执行顺序从上到下;
id
不同,值越大,越先执行
)
。
|
select_type
|
表示
SELECT
的类型,常见的取值有
SIMPLE
(简单表,即不使用表连接 或者子查询)、PRIMARY
(主查询,即外层的查询)、 UNION(
UNION
中的第二个或者后面的查询语句)、
SUBQUERY
(
SELECT/WHERE
之后包含了子查询)等
|
type
|
表示连接类型,性能由好到差的连接类型为
NULL
、
system
、
const
、 eq_ref、
ref
、
range
、
index
、
all
。
|
possible_key
|
显示可能应用在这张表上的索引,一个或多个。
|
key
|
实际使用的索引,如果为
NULL
,则没有使用索引。
|
key_len
|
表示索引中使用的字节数, 该值为索引字段最大可能长度,并非实际使用长 度,在不损失精确性的前提下, 长度越短越好 。
|
rows
|
MySQL
认为必须要执行查询的行数,在
innodb
引擎的表中,是一个估计值, 可能并不总是准确的。
|
filtered
|
表示返回结果的行数占需读取行数的百分比,
filtered
的值越大越好。
|