索引优化学习

等哈哆

已于 2022-12-20 18:00:51 修改

阅读量984

点赞数

分类专栏： mysql 文章标签：学习数据库 java

于 2022-12-09 14:21:38 首次发布

本文链接：https://blog.csdn.net/weixin_43139254/article/details/128251958

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

背景

最近做查询优化，学到的。

字段长度，索引长度
联合索引计算是否使用
范围查询使用索引
知识更新（1216）
索引长度并不能代表一定走了那个字段
回表
索引下推
Using index for skip scan
子查询
几乎唯一但较长的字段，要不要和一个较短的字段做复合索引

字段长度（varchar）

只谈论varchar：首先我们建表varchar(20) 中的20是字符数。看你的数据库编码

执行：show create table <table>
可以看到：CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
其中utf8mb4是编码，utf8mb4_0900_ai_ci是默认排序方式

utf8mb4：1个字符占用4字节。索引varchar(20):占用20*4=100字节。（按这样计算，不考虑其他）。其他编码可，仿照。
如果你的字符串字符不到20，mysql会自定义存储长度。意思是，varchar(100)和varchar(20)如果存20字符占用是一样的。

索引长度

考虑到字符会自定义适应长度，索引会吗？(还不知道)
网上锁单索引长度超过：767，就不能给建。在utf8mb4编码下，大概能装191的，索引varchar(191)大的，就不能建索引。复合索引除外。

select version()#可查看数据库版本我的：8.0.23

在我的版本下，索引最大长度是3072。详情请看：
https://www.cnblogs.com/haha029/p/15727550.html
如果你计算出来太大，就需要截取建索引了。

联合索引计算是否使用

上面讨论了，索引的长度和字段的关系，于是想到explain出来的key_len和索引的关系：
在这里插入图片描述
我有个复合索引，varchar(255)+date。图中是我explain出来的，第一个1027就是对应索引的key_len，所以，联合索引到底用到，我后面个date字段没？
我们来计算一下长度，公式如下：

1.所有的索引字段，如果没有设置not null，则需要加一个字节。 

2.定长字段，int占四个字节、date占三个字节、char(n)占n个字符。 

3.对于变成字段varchar(n)，则有n个字符+两个字节。 

4.不同的字符集，一个字符占用的字节数不同。 utf8mb4占用4字节。

计算一下：255*4(字符占用字节)+1(能为空)+2(varchar需要+2)+3(date占用)+1(可能为空)=1027，所以上面用到了我联合索引的下一个字段。
详情：索引计算

范围查询使用索引

范围查询时，使用联合索引，如果前面的使用了范围查询，就会导致后面的失效。例如：abc联合索引。a使用了范围查询，索引只会使用到a。可根据上面自己计算。

知识更新（1216）

索引长度并不能代表一定走了那个字段

最好索引长度和extra里面的信息联合查看。详情

回表

什么是回表？：都知道mysql用b+树，叶子节点才存信息。而非聚集索引，叶子节点存的是主键值。主键索引是聚集索引，聚集索引叶子节点存的是数据地址。而回表就是你拿主键去主键索引查数据。一般就是extra：Using where。所以这里还牵扯到索引覆盖，就是你查的值，其实就在索引字段。就直接用了索引上的值，而不去回表查字段。

索引下推

什么是索引下推？：回表是不是感觉有点消耗时间。索引下推能够减少些回表。可以看官网

 INDEX (zipcode, lastname, firstname).

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

官网的意思是，在上面索引，像这样查询的时候能用到索引下推。
官网：MySQL can use the index to scan through people with zipcode=‘95054’. The second part (lastname LIKE ‘%etrunia%’) cannot be used to limit the number of rows that must be scanned, so without Index Condition Pushdown, this query must retrieve full table rows for all people who have zipcode=‘95054’.

With Index Condition Pushdown, MySQL checks the lastname LIKE ‘%etrunia%’ part before reading the full table row. This avoids reading full rows corresponding to index tuples that match the zipcode condition but not the lastname condition.
翻译：LIKE '%etrunia%'不能用索引，如果不下推，就要用zipcode=‘95054’.所有人去回表查。
更多细节看官网吧。
我不知道如果有firstname查，能不能推2个，猜想不能。
2022-12-20：我试了可以，你可以像下面试试。

SELECT * FROM people
  WHERE zipcode='95054'
  AND address LIKE '%Main Street%';

原理：都在索引上，又是数组都排查一个字段和几个差别不大。

Using index for skip scan

索引都推了，能不能跳过。都说左匹配，要是匹配不了，直接跳过行不行。行！extra：Using index for skip scan，这样的就是。
官网
第一次遇到的时候，发现很奇怪，就像你使用下面这个sql，用了索引。它跳过了第一个匹配。当然，这是有一定条件，百度说，前面个字段，值不多。

SELECT * FROM people
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

详情查看：mysql官方文档
注意：The query references only columns in the index.查询字段要全是index字段，count也行的。

子查询

你是否觉得子查询慢。以下那个快？都一样的，你可以看下执行计划和优化后的sql。子查询会优化成join。

drop table student;
create table student
(
    id BIGINT PRIMARY KEY
);
drop table student_info;
create table student_info
(
    id          BIGINT PRIMARY KEY,
    student_id  long,
    info        varchar(20),
    enable_flag varchar(1)
)；


explain
select *
from student
         left join(select * from student_info where enable_flag = 'Y') student_info
                  on student.id = student_info.student_id;
explain
select *
from student
         left join student_info
                  on student.id = student_info.student_id and enable_flag = 'Y';

几乎唯一但较长的字段，要不要和一个较短的字段做复合索引

假如我有个表，一个字段UUID(varcahr(60))，一个字段time(bigint)。uuid基本唯一，time是时间搓。时间搓查询范围，默认6小时，最大7天。表总共10000w。要是每天 2.4w数据，一直在增加。假设数据增加得很均匀。那默认6小时就是，6000条。每次查询都会带time，UUID是条件。time已经有和其他字段的组合索引（time,?）。
你说我是建UUID一个索引，还是建（time,UUID）索引？
答：我感觉建UUID一个就行。首先排除(UUID,time)。
花费几个小时百度了一下，然后根据信息差我来算一下（可能是错的）参考：
1.UUID建立索引
一页16k，索引链接占6b。计算一下索引树长什么样子。
60(varchar) * 4(utf8mb4) + 6 = 246b
16kb * 1024 / 246b < 67 取66。
非聚集索叶子节点存的是主键id和索引值。那我就这样算(我的猜想)：240（UUID） + 8（主键bigInt）+ 6（链接，这里应该加少了，先算大概）= 254 （叶子节点存储大小）
16 * 1024 / 254 < 65 取64
3层计算：66(索引页第一层) * 66(索引页第二层) * 64 （叶子）= 278784(条数据)
4层计算：18399744 。
所以大概4层。每层查一半能匹配上的话：33* 3 + 32 = 131次。(实际如果是二分查找的话，应该只用8次)
131 * 240（字节匹配）= 34060

第二种（time,UUID）：范围查询6000 * 240 肯定大于上面算的吧。
所以，直接（UUID建立索引建）好。算起来感觉没毛病，等学多了再看看