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

执行计划

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

explain select * from user;

id 执行顺序

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

1.id 一样顺序执行。

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

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

select_type 查询类型

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

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

table 使用的表

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

type * 访问类型

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

All：全表扫描

index:全索引扫描

range:范围查询

const:只有一行匹配

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

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

possible_keys 可能索引

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

key 使用索引

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

key_len 索引长度

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

ref 索引列

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

rows * 预估行数

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

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

extra 额外信息

额外信息

通过索引进行优化

索引结构来源

1.二叉查找树

问题 ：数据倾斜

2.AVL 树

问题：插入新数据可能造成旋转，插入删除效率低（最短子树和最长子树，高度不超过 1）

3.红黑树

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

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

4.B 树

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

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

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

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

5.B+ 树

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

InnoDB--B+Tree

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

MyISAM--B+Tree

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

索引优点

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 关联。

方式二效率更高。

案例

索引设计方案

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

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

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

Mysql 查询效率快吗？

快

表现的慢的原因：

1.IO 问题

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

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