Mysql 调优 (二) 执行计划 索引入门

本贴最后更新于 1353 天前,其中的信息可能已经时移世易

执行计划

通过 explain 看对应 sql 语句中的问题,是否走索引(查询类型),是否全表查询,预估行数

explain select * from user;

image.png

id 执行顺序

表示当前 sql 执行的子句或操作表的顺序

1.id 一样顺序执行。

2.id 不同,值越大优先级越高。

3.同和不同,大的先,相同顺序执行。

select_type 查询类型

分辨查询的类型,是普通查询还是联合查询还是子查询

如用了子查询(sub query),最外层会标记为:primary

image.png

table 使用的表

对应行正在访问哪一个表,表名或别名,可能是临时表或 union 合并结果集。

image.png

type * 访问类型

访问类型,表示当前是以何种方式去访问我们的数据,最容易想的是全表扫描,直接暴力的遍历一张表去寻找需要数据。

All:全表扫描

index:全索引扫描

range:范围查询

const:只有一行匹配

访问类型非常多,效率才好到坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

image.png

possible_keys 可能索引

显示可能应用在这张表的索引,一个或多个,查询设计到的字段上若存在索引,则该索引将被列出,但实际查询不一定使用。

key 使用索引

实际使用的索引,如果为 null,则没有用索引。查询中若用了覆盖索引,则该索引和查询的 select 字段重叠。

key_len 索引长度

表示索引中使用的字节数,可通过 key_len 计算查询中使用的索引长度。在不损失精度的情况下长度越短越好。

ref 索引列

显示索引的哪一列被使用了,如果可能,是一个常数

rows * 预估行数

根据表的统计信息及索引使用情况,估算出找出所需记录需要读取的行数。

直接反应了 sql 找了多少数据,越少越好。

extra 额外信息

额外信息

image.png

通过索引进行优化

索引结构来源

1.二叉查找树

问题 :数据倾斜

2.AVL 树

问题:插入新数据可能造成旋转,插入删除效率低(最短子树和最长子树,高度不超过 1)

3.红黑树

最长子树不超过最短子树两倍即可,通过旋转和变色,提升插入效率。AVL 树变种,损失部分查询性能,满足插入性能的提升。

问题:有且仅有两个分支,节点过生造成 I/O 次数过多

4.B 树

(M 阶,代表一个树节点最多有多少查询路径。M=2 二叉树)

image.png

  1. 更多的分支(多叉树)
  2. 所有节点关键字按递增次序排序,并遵循左小,右大。
  3. 子节点数:给叶子节点的子节点数》1,且 <=M,M>2

4.所有叶子节点均在同一层,叶子节点包含关键字和关键字记录的指针外,也有指向器子节点指针。

问题:非叶子节点也含有数据,占用空间大。

5.B+ 树

image.png

1.在 B 树基础上坐了优化,非叶子节点不存数据信息,可存更多键值。

InnoDB--B+Tree

叶子节点直接放置---整行数据

image.png
image.png

MyISAM--B+Tree

叶子节点中,存放实际数据所在文件地址(offset, seek)

image.png

索引优点

1.大大减少了服务器需要扫描的数据量

2.帮助服务器避免排序和零时表

3.随机 IO 变成顺序 IO

索引用处

1.快速查找匹配 Where 子句的行

2.从 consideretion 中消除行,如果可以多个索引间进行选择,mysql 通常会使用找到最少行的索引

3.如果表具有多列索引,则优化器可以使用索引的任何最左前缀来查行。

4.当有表连接的时候,从其他表检索行数据。

5.查找特定索引列的 min 或 max 值

6.如果排序或分组时在可用索引的最左前缀上完成,再对表进行排序和分组

7.在某些情况下,可以优化查询以检索值而无需查询数据行。

索引分类

主键索引

数据库建立索引是:唯一且非空

唯一索引

普通索引

全文索引

组合索引

技术名词

回表

使用普通索引进行查询时:

1.在普通索引对应的 B+Tree 上找,返回该列对应的主键索引。

2.查主键索引对应的 B+Tree,找到对应的数据

普通索引存:当前列对应数据及主键

需要两次 IO

覆盖索引

用普通索引查询对应列主键

1.1.在普通索引对应的 B+Tree 上找,直接返回该列的主键。

没有回表过程

最左比配

组合索引,按最左字段匹配

索引下推

组合索引:name,age

查: select xxx from aaa where name='xxx' and age= 'xxx'

老版本时:先将 name 符合条件的都取出来,在 server 层过滤 age.

高版本用索引下推:在从存储引擎获取数据时,取 name 值时直接把 age 值过滤掉,无需在放到 server 层。 减少 server 层到存储引擎层 IO.

谓词下推

select t1.name ,t2.name from t1 join t2 on t1.id=t2.id

方式一:将 t1 表与 t2 表数据按照 id 关联,如果每张 10 列,则一共 20 列。从 20 列中取出 name.

方式二:按表把 t1.name,t1.id 及 t2.name,t2.id 取出来,再按 id 关联。

方式二效率更高。

案例

image.png

索引设计方案

1.建立 name,age 的组合索引

2.建立 age 的单独索引(age 占用空间比 name 少)

单独建立索引,效率不一定高,设计索引合并,由优化器来完成,效率不一定高。

Mysql 查询效率快吗?

表现的慢的原因:

1.IO 问题

2.并发请求,会有 n 多个缓存,内存不够,则内存需频繁替换。

查询缓存效率低,命中率低。8 已经删除

  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    690 引用 • 535 回帖
  • 总结
    205 引用 • 1748 回帖 • 2 关注

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...