索引优化示例

川长思鸟来 2023-09-27 14:04 3阅读 0赞

**目录**

1.单表优化

2.两表优化

3.三表优化

4.总结

--------------------

### 1.单表优化 ###

创建索引前

![65788102f9ec487f98edd748421977ee.png][]

(1)先按照where条件创建索引

![26564babc2144de4820d91df0aed98a0.png][]

按照查询条件中的三个项目创建索引，并且索引中的项目存在顺序，分别是1,2和3。

(2)创建索引

![c7ede4c43df6491aa3ade19e4f02ea75.png][]

type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。  
但是我们已经建立了索引,为啥没用呢?  
这是因为按照 BTree 索引的工作原理  
先排序 category\_id,  
如果遇到相同的 category\_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。当 comments 字段在联合索引里处于中间位置时\#因comments >1 条件是一个范围值(所谓 range),\#MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效

(3)索引优化

只建立vategory\_id和views的索引

![7b524476aaf248a3a55465af7b1bb762.png][]

从结果可以看到type变成了ref，文件排序也没有出现。

### 2.两表优化 ###

创建索引前

![f7aeb987e42b4a068592ec60687571b7.png][]

(1)创建索引

因为是左连接，所以右边的表是关键，需要在右边的表创建索引，所以先为book表的card字段创建索引。如果是右连接的话，左边的表需要创建索引。

![e09f0ab4b2fe453886a8c3b35975d3c2.png][]

### 3.三表优化 ###

创建索引前

![7f8e3482c5f7448899d11bf807ade9c1.png][]

(2)创建索引

因为是左连接，所以在右表上创建索引。分别在book表的card字段和phone表的card字段创建索引。

![65dc63db0da24cc6a492036fadd614be.png][]

后 2 行的 type 都是 ref 且总 rows 优化很好,效果不错。因此索引最好设置在需要经常查询的字段中

### 4.总结 ###

尽可能减少Join语句中的NestedLoop的循环总次数。“永远用小结果集驱动大的结果集” 。

比如3中的例子，class代表书的类型，book代表书，class是小表，book是大表。

优先优化NestedLoop的内层循环;  
保证Join语句中被驱动表上Join条件字段已经被索引；

当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。

[65788102f9ec487f98edd748421977ee.png]: https://img-blog.csdnimg.cn/65788102f9ec487f98edd748421977ee.png
[26564babc2144de4820d91df0aed98a0.png]: https://img-blog.csdnimg.cn/26564babc2144de4820d91df0aed98a0.png
[c7ede4c43df6491aa3ade19e4f02ea75.png]: https://img-blog.csdnimg.cn/c7ede4c43df6491aa3ade19e4f02ea75.png
[7b524476aaf248a3a55465af7b1bb762.png]: https://img-blog.csdnimg.cn/7b524476aaf248a3a55465af7b1bb762.png
[f7aeb987e42b4a068592ec60687571b7.png]: https://img-blog.csdnimg.cn/f7aeb987e42b4a068592ec60687571b7.png
[e09f0ab4b2fe453886a8c3b35975d3c2.png]: https://img-blog.csdnimg.cn/e09f0ab4b2fe453886a8c3b35975d3c2.png
[7f8e3482c5f7448899d11bf807ade9c1.png]: https://img-blog.csdnimg.cn/7f8e3482c5f7448899d11bf807ade9c1.png
[65dc63db0da24cc6a492036fadd614be.png]: https://img-blog.csdnimg.cn/65dc63db0da24cc6a492036fadd614be.png