MySQL优化（索引优化）

最新推荐文章于 2024-08-25 20:30:00 发布

垃圾继承拖拉机

最新推荐文章于 2024-08-25 20:30:00 发布

阅读量209

点赞数

分类专栏：数据库文章标签： mysql 数据库索引

本文链接：https://blog.csdn.net/a219219219219/article/details/108063101

版权

数据库专栏收录该内容

24 篇文章 0 订阅

订阅专栏

索引优化

1.建立聚集索引

实例：现在我们想查出满足以下条件的用户id：
mysql>SELECT ｀uid｀ FROM people WHERE lname｀=’Liu’ AND ｀fname｀=’Zhiqun’ AND ｀age｀=26
因为我们不想扫描整表，故考虑用索引。

单列索引：
ALTER TABLE people ADD PRIMARY KEY (‘lname’);
将lname列建索引，这样就把范围限制在lname=’Liu’的结果集1上，之后扫描结果集1，产生满足fname=’Zhiqun’的结果集2，再扫描结果集2，找到 age=26的结果集3，即最终结果。

由于建立了lname列的索引，与执行表的完全扫描相比，效率提高了很多，但我们要求扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除lname列上的索引，再创建fname或者age 列的索引，但是，不论在哪个列上创建索引搜索效率仍旧相似。

2.常查询数据建立索引或者组合索引。

普通索引：

ALTER TABLE people ADD INDEX lname (lame);

组合索引：

ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);
为了提高搜索效率，我们需要考虑运用多列索引,由于索引文件以B－Tree格式保存，所以我们不用扫描任何记录，即可得到最终结果。

注：在mysql中执行查询时，只能使用一个索引，如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。

注：

1.添加PRIMARY KEY（主键索引）

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加INDEX(普通索引)

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

上面的五种方式不用加’ ‘号。

3.最左前缀原则

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的搜索方向，如果name相同再依次比较age和sex，最后得到检索的数据。但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。

而对于联合索引(col1,col2,col3)，查询语句SELECT * FROM test WHERE col2=2;是能够触发索引的。正常情况下，直接使用col2字段进行条件判断是用不到索引的。但是在这里，触发索引是因为col2字段是有序的。当col2字段是有序的，即使我直接使用col2字段进行条件判断，也会用到索引。

例如：

以某一个表的(name,cid)复合索引为例，创建复合索引的规则是首先会对复合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的cid字段进行排序。所以：第一个name字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个cid字段进行条件判断是用不到索引的。

å¨è¿éæå¥å¾çæè¿°

要想用到索引，cid字段的索引数据必须要是有序的。什么时候才是有序的？观察可知，当然是在name字段是等值匹配的情况下，cid才是有序的。当两个name名字都为 c时，cid字段从上往下分别是4 和5，显然是有序的。这也就是mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因。（而且第一个索引必须是等值匹配,只有在进行等值匹配时，由name=c得到第一个索引的位置，然后在此基础上得到cid为4,5，这时候cid明显是有序的，就可以轻易得到要搜寻的某元组了）。

另一个例子：

EXPLAIN SELECT * FROM student WHERE cid=1 AND name='小红';

没有错，而且复合索引中的两个索引字段都能很好的利用到了！因为语句中最左面的name字段进行了等值匹配，所以cid是有序的，也可以利用到索引了。

你可能会问：我建的索引是(name,cid)。而我查询的语句是cid=1 AND name=‘小红’; 我是先查询cid，再查询name的，不是先从最左面查的呀？

好吧，我再解释一下这个问题：首先可以肯定的是把条件判断反过来变成这样 name=‘小红’ and cid=1; 最后所查询的结果是一样的。那么问题产生了？既然结果是一样的，到底以何种顺序的查询方式最好呢？所以，而此时那就是我们的mysql查询优化器该登场了，mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以mysql查询优化器会最终以这种顺序进行查询执行。

作者：沈杰
链接：https://www.zhihu.com/question/36996520/answer/93256153
来源：知乎
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。