索引

2024-01-09 14:33:04

索引规则

（1）选择唯一性索引。唯一性索引能快速从索引中定位到值，过多相同的值会降低查询效率。 
（2）为经常排序，分组，联合查询（外键）的字段建立索引。排序操作会浪费很多时间，建立索引可以有效避免排序操作。 
（3）为经常查询的字段建立索引。经常查询的字段会影响整个表的查询速度，为该字段建立索引能提高整表的查询速度。 
（4）尽量使用数据量少的索引字段。char(100)字段进行全文检索明显比char(10)索引花费时间要多。 
（5）限制索引的数量，索引并不是越多越好，索引需要存储，消耗磁盘空间，同时对于修改更新数据需要重建索引带来额外的消耗与麻烦。 
（6）尽量使用前缀来索引。如果索引的字段很长，只能放text或blog,进行全文检索耗费时间会很长。如果只是检索字段前面的值会提高检索速度。 
（7）删除不再使用或很少使用的索引。索引会占用磁盘空间与影响数据的更新，删除这些索引能减少对数据更新的影响。 
（8）最左前缀匹配原则，很重要的原则。mysql会一直向右匹配制导遇到范围查询（> < between like）就停止匹配。例如查询a=1,b=5,c<8,d=9，同时建立了（a,b,c,d）复合索引，d是用不到索引的。如果建立（a,b,d,c）顺序索引a,b,d顺序可以任意调整。 
（9）=和in可以乱序。例如查询a=1,b=5,c=9.建立（a,b,c）索引可以任意顺序。sql查询优化器会将a,b,c顺序调整成可以识别的顺序。 
（10）尽量选择区分度高的列作为索引。选择重复值少的列作为索引查询速度会更快。 
（11）索引列不能参与运算，要保持干净。参与计算的列不能运用索引，原因是如果使用索引b+树每一个值都要应用函数才能比较，显然成本会很高。 
（12）尽量尽量扩展索引，不要新建索引。比如表中已经有了a索引，现在要加(a,b)索引，那么只要修改原索引即可。

索引失效情况：是用了>, <, !=, like%开头，or, between......

 

设置索引的方式 ：

ALTER TABLE 表名 ADD INDEX 索引名(列名…)

ALTER TABLE tb_student ADD INDEX test_index(student_age,student_addr);

最左前缀匹配原则，是一个非常重要的原则，可以通过以下这几个特性来理解。

  • 对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。比如 a = 3 and b = 4 and c > 5 and d = 6，如果建立的是（a,b,c,d）这种顺序的索引，那么 d 是用不到索引的，但是如果建立的是 （a,b,d,c）这种顺序的索引的话，那么就没问题，而且 a，b，d 的顺序可以随意调换。
  • = 和 in 可以乱序，比如 a = 3 and b = 4 and c = 5 建立 （a，b，c）索引可以任意顺序。
  • 如果建立的索引顺序是 （a，b）那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。

这么说还看不懂也没关系，下面会我通过四个简单的小例子来帮助你明白。

看例子之前，先要普及一下 explain 这个关键字的用法。

explain 是用来分析 SELECT 查询语句的，开发人员可以通过分析 explain 结果来优化查询语句。文章接下来将会大量使用 explain 来观察索引是否被使用到，我们先简单的看一个 explain 使用的小例子。

就用最简单的，扫描 tb_student 全表。

SELECT * FROM tb_student

我们用 explain 分析一下

EXPLAIN SELECT * FROM tb_student

返回结果
索引
注意我圈红的这仨字段，这是使用 explain 语句需要重点关注的字段

  • type：访问类型，要是显示 ALL ，那你可要小心了，这是全表扫描的意思，性能最差，说明你的查询有很大的优化余地，如果显示的是 index ，说明会使用索引来优化查询。关于 type 的更多解释请参考这个文章 ：mysql中explain的type的解释
  • key：具体使用的索引名，这里没有。
  • rows：扫描的行数。

好了，言归正传，现在开始兑现承诺，举四个小例子了，在上面刚给 student_age 和 student_addr 设置完联合索引，现在我们使用 explain 分析四种 where 子句的执行情况。
 
一、where student_age = 10 and student_addr = '北京’

explain select * from tb_student where student_age = 10 and student_addr = '北京';

返回结果
索引
OK,使用了索引。
 
二、where student_addr = ‘北京’ and student_age = 10

跟第一种情况相比只是调换了顺序。

explain select * from tb_student where student_addr = '北京' and  student_age = 10;

返回结果
索引
OK,还是正常。
 
三、where student_age = 10

explain select * from tb_student where student_age = 10;

索引
没问题
 
四、where student_addr = '北京’

explain select * from tb_student where student_addr = '北京';
返回结果

索引
睁大眼睛，这就是最左匹配原则。

 

最左匹配原则的成因：

MySQL 建立联合索引的规则是这样的，它会首先根据联合索引中最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推。

综上，第一个字段是绝对有序的，从第二个字段开始是无序的，这就解释了为什么直接使用第二字段进行条件判断用不到索引了（从第二个字段开始，无序，无法走 B+ Tree 索引）！这也是 MySQL 在联合索引中强调最左前缀匹配原则的原因。

 

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

最左前缀匹配原则

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，

示例：

CREATE TABLE `student` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增Id',
  `Gid` int(11) unsigned DEFAULT NULL COMMENT '年级id',
  `Cid` int(11) unsigned DEFAULT NULL COMMENT '班级id',
  `SId` int(11) unsigned DEFAULT NULL COMMENT '学号',
  `Name` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

对列Gid、列Cid和列Sid建一个联合索引

create unique index uni_Gid_Cid_SId on student(Gid,Cid,SId)

联合索引 uni_Gid_Cid_SId 实际建立了(Gid)、(Gid,Cid)、(Gid,Cid,SId)三个索引。

插入模拟数据

INSERT INTO `student` (`Gid`, `Cid`, `SId`, `Name`) VALUES (floor(rand() * rand() *rand() * 1000000000) , floor(rand() *  rand() *rand() * 1000000000) , floor(rand() * rand() * rand() *1000000000) , rand());

查询实例：

SELECT * FROM student WHERE Gid=68778 AND Cid=465176354 AND Name='0.56437948'

上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(Gid,Cid)进行数据匹配。

注意

索引的字段可以是任意顺序的，如：

  1.   SELECT * FROM student WHERE Gid=68778 AND Cid=465176354 ;
  2.   SELECT * FROM student WHERE Cid=465176354 AND Gid=68778;

这两个查询语句都会用到索引(Gid,Cid)，mysql创建联合索引的规则是首先会对联合合索引的最左边的，也就是第一个字段Gid的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个字段Cid进行排序。其实就相当于实现了类似 order by Gid Cid这样一种排序规则。

有人会疑惑第二个查询语句不符合最左前缀匹配：首先可以肯定是两个查询语句都保函索引(Gid,Cid)中的Gid、Cid两个字段，只是顺序不一样，查询条件一样，最后所查询的结果肯定是一样的。既然结果是一样的，到底以何种顺序的查询方式最好呢？此时我们可以借助mysql查询优化器explain，explain会纠正sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。

索引

索引

那么问题产生了？既然结果是一样的，到底以何种顺序的查询方式最好呢？

所以，而此时那就是我们的mysql查询优化器该登场了，sql语句中字段的顺序不需要和联合索引中定义的字段顺序一致，查询优化器会自己调整顺序，mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以mysql查询优化器会最终以这种顺序进行查询执行。

为什么要使用联合索引

减少开销。建一个联合索引(Gid,Cid,SId)，实际相当于建了(Gid)、(Gid,Cid)、(Gid,Cid,SId)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引。对联合索引(Gid,Cid,SId)，如果有如下的sql: select Gid,Cid,SId from student where Gid=1 and Cid=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where Gid=1 and Cid=2 and SId=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合Gid=2 and Cid= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

缺点。联合索引越多，索引列越多，则创建的索引越多，索引都是存储在磁盘里的，通过索引算法(Btree代表索引算法使用二叉树的形式来做索引的)来查找数据，的确可以极大的提高查询效率，但是与此同时增删改的同时，需要更新索引，同样是需要花时间的，并且索引所占的磁盘空间也不小。

建议。单表尽可能不要超过一个联合索引，单个联合索引不超过3个字段。

引申

对于联合索引(Gid,Cid,SId)，查询语句SELECT * FROM student WHERE Cid = 465176354 ;是否能够触发索引？
大多数人都会说NO，实际上却是YES。

原因：

  1.   EXPLAIN SELECT * FROM student WHERE SId=465176354;
  2.   EXPLAIN SELECT * FROM student WHERE Gid=68778

观察上述两个explain结果中的type字段。查询中分别是：

index：这种类型表示mysql会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个联合索引的一部分，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。所以，上述语句会触发索引。


ref：这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。

 

 

 

转自：

https://blog.csdn.net/oracle_29/article/details/85645883

https://blog.csdn.net/u013568373/article/details/93891531

https://blog.csdn.net/qq_27559331/article/details/89632566?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param

 

上一篇：open、pause、goBack的用法


下一篇：从0.01开始的SQL之路之sql50题