评价校园网站建设范例,佛山 网络推广,六安房产网,永灿网站建设公司文章目录 简介什么时候创建索引#xff1f;什么时候不需要创建索引索引在什么情况下会失效索引使用举例#xff08;兴趣篇#xff09;参考文献 简介
如何通过索引让查询效率最大化呢#xff1f;本节主要考虑以下几个问题#xff1a;
什么样的情况下需要创建索引#xf… 文章目录 简介什么时候创建索引什么时候不需要创建索引索引在什么情况下会失效索引使用举例兴趣篇参考文献 简介
如何通过索引让查询效率最大化呢本节主要考虑以下几个问题
什么样的情况下需要创建索引什么样的情况下不需要创建索引索引在哪些情况下会失效如何避免
什么时候创建索引
字段的数值有唯一性的限制比如说用户名
这种情况非常适合创建索引可以直接创建唯一性索引或者主键索引。
频繁作为where查询条件的字段可以创建索引
在数据量大的情况下如果一个字段在SQL查询的where条件里被经常使用到那么就需要给这个字段创建索引。即使是普通索引也能大幅度提升查询的效率。
经常作为group by或者order by的字段可以创建索引
这里有个有意思的点。
对于group by user_id order by comment_time desc我对user_id和comment_time字段分别建索引查询速度要比创建联合索引user_id, comment_time的速度慢。
这是因为多个单列索引在多条件查询时一般只会生效一个索引MySQL会选择其中一个限制最严格的作为索引。
因此在多条件联合查询的时候最好创建联合索引。
而且联合索引的创建顺序也是有讲究的。在上个例子里user_id, comment_time的联合索引速度要比comment_timeuser_id的联合索引查的更快二者同时比两个单列索引都快。
这主要是因为进行select查询的时候是先进行group by后执行order by因此按照这个顺序的联合索引的效率是最高的。
这时候就有疑问了既然是先进行group by后order by那根据索引最左原则我如果声明的是comment_timeuser_id的联合索引那岂不是索引就失效了
但其实并没有失效只是使用的效率稍微低了些。这是因为当语句中只用了一部分索引字段的时候才需要考虑最左原则如果语句中使用了联合索引中的全部字段那就不需要考虑最左匹配原则了不会失效。
2023-10-31 22:43:42 在MySQL8.0里测试了下确实是这样explain里显示两种情况下其实都用了索引。这块有点杂暂时不深入了有兴趣之后可以再看吧。
update、delete的where条件列也可以创建索引
这是因为update和delete的时候都得先根据where条件列检索出符合情况的数据只要涉及检索了用索引都可以在一定程度上提速。
distinct的字段也可以创建索引
如果我们经常需要对某个字段做distinct那么为这个字段创建索引也可以大大加快distinct处理的速度。这是因为索引会对数据按照某种顺序进行排序对有序数据的去重自然比对无序数据的去重快得多。
在做多表join的时候对用于连接的字段也可以创建索引。注意两边字段类型要一致。
什么时候不需要创建索引
where条件、group by和order by、或者是on连接里不经常用到的字段不需要创建索引
因为索引的价值在于快速定位数据至于起不到定位作用的字段没有创建索引的价值。即使它们会出现在select里。
表记录太少比如小于1000个没有创建索引的必要。因为即使创建了索引提升也不大而且如果数据太少优化器会认为使用索引还不如直接全表扫描索引检索后还得回表这时候就会强制进行全表扫描忽略索引如果字段中有大量重复数据一般也不用创建索引。除非是数据比重偏差很大的情况比如100w数据里有10个男性这时候可以对性别字段创建索引。频繁更新的字段不需要创建索引。因为更新数据的时候也需要同步更新索引频繁的更新会带来负担从而影响效率。
索引在什么情况下会失效
一些常见的索引失效的例子主要实际生产中可以考虑避免这些问题。
如果对索引的字段进行了表达式计算该索引会失效。
如where comment_id170000这时字段comment_id的索引会失效。
这是因为我们需要把这个字段的每个值取出来进行表达式的计算因此实际上是进行了一次全表扫描索引用不上。
这种情况下如果想要使用索引我们可以把代码重写成where comment_id69999。
如果对索引的字段使用函数该索引会失效。
比如where substring(comment_text, 1, 3) abc这时字段comment_text的索引会失效。
失效的原因跟上面是一样的都是得做一次全表扫描依次取出每个值来做函数计算。
可以重写成where comment_text like abc%。
在where子句里如果在or前的条件列进行了索引而没有对or后面的条件列进行索引那么索引会失效。
这个原理其实很好理解。
因为or的含义是满足一个即可因此只对一个条件列做索引是没有意义的其他条件列仍然需要通过全表扫描来完成计算。
像and就没有这种顾虑。如果and里只有一个条件列做了索引那么可以先使用这个条件列快速检索出一个符合该条件的子集然后再对这个子集基于另一个条件列做扫描。这样就可以避免全表扫描了索引正常生效。
当使用like进行模糊查询时前面不能是%否则索引失效。
同样很好理解像是like %ab这种肯定是用不了索引的。
因为索引在匹配的时候是从首位开始进行匹配不会是对中间位置进行匹配的。
索引列尽量设置成Not Null约束。在使用联合索引时需要注意最左原则。联合索引最多作用于一个范围列范围列之后的字段无法使用索引。比如说我定义了联合索引(x,y,z)对于where x1 and y2 and z3只能使用索引(x,y)
一条SQL语句可以只使用联合索引的一部分但是比如从联合索引声明时的最左侧字段开始否则就会失效。
索引使用举例兴趣篇
教程里并没有这一节出于兴趣自己补了一小节。
首先是建表工作我建了三张表
users表没有索引users_2表联合索引(user_id, create_time)users_3表联合索引(create_time, user_id)
其次通过一个存储过程往users表里插入10w条数据然后再将users表里的数据insert进其他两个表中保证三个表的数据是一致的。
建表逻辑和插数逻辑如下
CREATE DEFINERrootlocalhost PROCEDURE insert_many_user(IN start INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT (2017-01-01 00:00:00);
DECLARE date_temp DATETIME;
SET date_temp date_start;
SET autocommit0;
REPEAT
SET ii1;
SET date_temp date_add(date_temp, interval RAND()*60 second);
INSERT INTO nba.users(user_id, user_name, create_time)
VALUES((starti), CONCAT(user_,i), date_temp);
UNTIL i max_num
END REPEAT;
COMMIT;
ENDcall insert_many_user(100, 100000);create table users(user_id int,user_name varchar(100),create_time timestamp
);drop table if exists users_2;
create table users_2(user_id int,user_name varchar(100),create_time timestamp,index(user_id, create_time)
);
insert into nba.users_2 select * from nba.users;create table users_3(user_id int,user_name varchar(100),create_time timestamp,index(create_time, user_id)
);
insert into nba.users_3 select * from nba.users;接下来我们仿照教程里讲的案例分别用explain看一下以下SQL的执行过程。
先介绍一下explain里几个重要参数的意思
rows表示找到所需的记录大概要读取的行数是一个大概估计值。
type参数的一些取值的定义 typeref表示查询使用了非唯一行索引扫描属于是实打实的利用索引。 typeindex表示遍历了索引树虽然跟ALL一样还是全表扫描但是比ALL快也算是利用到了索引。
extra的一些取值的定义
using filesort使用外部的索引排序而不是按照表内的索引顺序进行读取。using temporary使用了临时表来保存中间结果。常见于order by和group by时。using index表示select语句中使用了覆盖索引即直接从索引中取值而不需要回表从磁盘中取数据using where使用了where进行过滤using index condition表示查询的列有非索引列先判断索引的条件以减少IO。
上一下教程里的几个例子
set sql_mode0 ;
-- 未使用索引, typeALL, extraUsing temporary; Using filesort
explain select a.user_id, count(*) from nba.users a group by a.user_id order by a.create_time;-- 使用索引typeindex, keyuser_id, extraUsing index; Using temporary; Using filesort
explain select a.user_id, count(*) from nba.users_2 a group by a.user_id order by a.create_time;-- 使用索引typeindex, keycreate_time, extraUsing index; Using temporary; Using filesort
explain select a.user_id, count(*) from nba.users_3 a group by a.user_id order by a.create_time;-- 使用索引typeindex, keycreate_time, extraUsing index; Using temporary; Using filesort
explain select a.create_time, count(*) from nba.users_3 a group by a.create_time order by a.user_id;可以看到除了第一个表是走了全表扫描之外其他的表都是间接利用到了索引来做加速typeindex。
下面我们以第三张表为例取消order by语句看查询是否还能利用索引
-- 使用索引typeindex, keycreate_timeextraUsing index; Using temporary
explain select a.user_id, count(*) from nba.users_3 a group by a.user_id;
-- 使用了索引keycreate_timeextraUsing index;
explain select create_time, count(*) from nba.users_3 a group by a.create_time ;可以看到仍然走了索引。
我们尝试保留order by语句取消group by语句同时改变select体看查询是否还会利用索引
-- 使用索引typeindex, keycreate_timeextraUsing index; Using filesort
explain select a.user_id from nba.users_3 a order by a.user_id;
-- 未使用索引, typeALLextraUsing filesort
explain select a.user_id, a.create_time ,a.user_name from nba.users_3 a order by a.user_id;可以看到第二个SQL没有走索引这是因为select体里出现了非索引字段user_name在没有任何where条件的情况下查询只能走全表扫描取出所有值。
那我如果加上where条件呢下面这几个例子是我觉得很经典的
-- typerefkeycreate_time, extraUsing index condition, rows1
explain select create_time, user_id, user_name from nba.users_3 a where a.create_time 100; -- order by a.user_id ;
-- typeindex, keycreate_time, rows104341, extraUsing where; Using index
explain select create_time, user_id from nba.users_3 a where a.user_id 100;
-- typeALL, rows104341, extraUsing where
explain select create_time, user_id, user_name from nba.users_3 a where a.user_id 100; 可以看到第一个查询是确确实实的使用了联合索引rows1属于是一击命中。
第二个查询其实不符合索引的最左原则该表的联合索引是user_id create_time但由于select的字段都是索引列所以查询是直接遍历了索引树来提取需要的数据并没有回表去读磁盘所以也算是变相利用了索引。
第三个查询属于是一点儿都没有利用到索引了。其不满足索引的最左原则而且又因为它的select体里有一个非索引列字段也没法只遍历索引树所以最终在磁盘里全表扫描了。
参考文献
26丨索引的使用原则如何通过索引让SQL查询效率最大化
