Mysql 索引优化

最新推荐文章于 2023-12-18 17:58:49 发布

白耘

最新推荐文章于 2023-12-18 17:58:49 发布

阅读量147

点赞数

CC 4.0 BY-SA版权

分类专栏： Mysql 文章标签： mysql 索引 mysql优化

本文链接：https://blog.youkuaiyun.com/weixin_43558515/article/details/117000356

Mysql 专栏收录该内容

5 篇文章

订阅专栏

前驱知识

1.Explain关键词：Mysql优化之Explain

2.联合索引结构：

现有 people 表数据如下：

people表

如下面 SQL 语句建立索引：

create index idx_lnameFnameDob on people(last_name, first_name, dob);

其索引结构为：

联合索引

对于表中的每一行数据，联合索引包含了 last_name,，first_name 和 dob 列的值。对于相同 last_name 的值，则根据 first_name 来排序，对于相同的 last_name 与 first_name，则根据 dob 来排序。

常见的索引优化方法

现有如下职工表：

staffs表

对 name，age，pos 建立联合索引：

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name,age,pos);

1.违背最左前缀法则

最左前缀法则：使用联合索引进行查询，查询应从索引的第一列开始，并且不跳过索引中的列。

针对name，name and age，name and age and pos查询：

1.1

通过 explain 我们不难看出，三个查询都使用了索引index_staffs_nameAgePos，但是 key_len 不同，ref 列中的 const 数量也不同，这是因为每次查询精度变高了使用到索引的地方变多，这只是个铺垫，重点在下方。

针对age and pos，pos，name and pos查询：

1.2

可以看到对 age and pos，pos 的查询没有使用索引，这是因为联合索引 index_staffs_nameAgePos 是根据 name 属性建立的，根据最左前缀法则，没有从对索引第一列 name 开始查询，因此无法使用该索引。

对 name and pos 的查询使用到了索引，但通过观察可以看出，ref 列只有一个 const，按照最左前缀法则，该查询跳过了中间列 age，因此对pos上的查询无法使用上该索引。

2.在索引上做任何操作（计算、函数、类型转换）

在索引上进行函数操作

2.0

使用LEFT(name, 4)获取 name 属性的左边四位字符，可以看出搜索结果没有错误，但是通过 explain 分析，不使用 left 的 SQL使用了索引，使用了 left 的 SQL 没有使用索引。

在索引上进行类型转换

2.1

name 属性是字符（varchar）类型，不加引号时，存储引擎认为2000时整数型，赋值给 name 会自动转换成字符型，正因为这个类型转换，导致索引失效。

3.范围查找右边的属性索引失效

3.0

key_length 表示索引中使用的字节数，可以看出第二个查询索引使用的更充分。这两个查询的唯一不同在于第一个查询对 age 进行范围查找，第二个查询对 pos 进行查找。而范围查找会导致右边的属性失效，因此第一个查询会导致联合索引对 pos （age 右边的属性）的检索失效。而 pos 右侧属性为空，因此不会导致索引失效。

结合联合索引的结构图，我们可以进一步分析：

第一个查询中肯定使用上了联合索引中的 name 属性，查找到所有的 July 叶子节点，再使用 age 属性，查找到所有满足范围查找条件的数据，根据联合索引建立的原理，对相同 age 值的pos 值进行排序，因此 age 范围查找之后的 pos 不满足有序性，因此联合索引对 pos 的检索失效。