SQL索引失效的情况有哪些

免费教程 2024年05月10日 0:05

评论»

这篇文章主要介绍了SQL索引失效的情况有哪些的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇SQL索引失效的情况有哪些文章都会有所收获，下面我们一起来看看吧。

数据库调优的大致方向：

索引失效，没有充分利用到索引——建立索引

关联查询太多join——sql优化

服务器调优及各个参数设置——my.cnf

数据过多——分库分表

sql查询优化技术有很多，大体分为物理查询优化和逻辑查询优化:

物理查询优化：通过索引和表连接方式等技术进行优化

逻辑查询优化：通过SQL等价变换提升查询效率，就是换一种sql写法

数据准备：

CREATEDATABASEatguigudb2;USEatguigudb2;#############class表#################CREATETABLE`class`(`id`INT(11)NOTNULLAUTO_INCREMENT,`className`VARCHAR(30)DEFAULTNULL,`address`VARCHAR(40)DEFAULTNULL,`monitor`INTNULL,PRIMARYKEY(`id`))ENGINE=INNODBAUTO_INCREMENT=1DEFAULTCHARSET=utf8;#############student表#################CREATETABLE`student`(`id`INT(11)NOTNULLAUTO_INCREMENT,`stuno`INTNOTNULL,`name`VARCHAR(20)DEFAULTNULL,`age`INT(3)DEFAULTNULL,`classId`INT(11)DEFAULTNULL,PRIMARYKEY(`id`)#CONSTRAINT`fk_class_id`FOREIGNKEY(`classId`)REFERENCES`t_class`(`id`))ENGINE=INNODBAUTO_INCREMENT=1DEFAULTCHARSET=utf8;#################################SETGLOBALlog_bin_trust_function_creators=1;#不加global只是当前窗口有效。#随机产生字符串DELIMITER//CREATEFUNCTIONrand_string(nINT)RETURNSVARCHAR(255)BEGINDECLAREchars_strVARCHAR(100)DEFAULT'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLAREreturn_strVARCHAR(255)DEFAULT'';DECLAREiINTDEFAULT0;WHILEi<nDOSETreturn_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));SETi=i+1;ENDWHILE;RETURNreturn_str;END//DELIMITER;#假如要删除#dropfunctionrand_string;#用于随机产生多少到多少的编号DELIMITER//CREATEFUNCTIONrand_num(from_numINT,to_numINT)RETURNSINT(11)BEGINDECLAREiINTDEFAULT0;SETi=FLOOR(from_num+RAND()*(to_num-from_num+1));RETURNi;END//DELIMITER;#假如要删除#dropfunctionrand_num;#创建往stu表中插入数据的存储过程DELIMITER//CREATEPROCEDUREinsert_stu(STARTINT,max_numINT)BEGINDECLAREiINTDEFAULT0;SETautocommit=0;#设置手动提交事务REPEAT#循环SETi=i+1;#赋值INSERTINTOstudent(stuno,NAME,age,classId)VALUES((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));UNTILi=max_numENDREPEAT;COMMIT;#提交事务END//DELIMITER;#执行存储过程，往class表添加随机数据DELIMITER//CREATEPROCEDURE`insert_class`(max_numINT)BEGINDECLAREiINTDEFAULT0;SETautocommit=0;REPEATSETi=i+1;INSERTINTOclass(classname,address,monitor)VALUES(rand_string(8),rand_string(10),rand_num(1,100000));UNTILi=max_numENDREPEAT;COMMIT;END//DELIMITER;#执行存储过程，往class表添加1万条数据CALLinsert_class(10000);#执行存储过程，往stu表添加50万条数据CALLinsert_stu(100000,500000);SELECTCOUNT(*)FROMclass;SELECTCOUNT(*)FROMstudent;###############################删除索引的存储过程########################DELIMITER//CREATEPROCEDURE`proc_drop_index`(dbnameVARCHAR(200),tablenameVARCHAR(200))BEGINDECLAREdoneINTDEFAULT0;DECLAREctINTDEFAULT0;DECLARE_indexVARCHAR(200)DEFAULT'';DECLARE_curCURSORFORSELECTindex_nameFROMinformation_schema.STATISTICSWHEREtable_schema=dbnameANDtable_name=tablenameANDseq_in_index=1ANDindex_name<>'PRIMARY';#每个游标必须使用不同的declarecontinuehandlerfornotfoundsetdone=1来控制游标的结束DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=2;#若没有数据返回,程序继续,并将变量done设为2OPEN_cur;FETCH_curINTO_index;WHILE_index<>''DOSET@str=CONCAT("dropindex",_index,"on",tablename);PREPAREsql_strFROM@str;EXECUTEsql_str;DEALLOCATEPREPAREsql_str;SET_index='';FETCH_curINTO_index;ENDWHILE;CLOSE_cur;END//DELIMITER;#执行存储过程CALLproc_drop_index("dbname","tablename");索引失效案例【1】. 全值匹配#【1】.全值匹配#student表,主键id,此时无索引,耗时大EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHEREage=30;EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHEREage=30ANDclassId=4;EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHEREage=30ANDclassId=4ANDNAME='abcd';#注：SQL_NO_CACHE不使用查询缓存#建立索引CREATEINDEXidx_ageONstudent(age);CREATEINDEXidx_age_classidONstudent(age,classId);CREATEINDEXidx_age_classid_nameONstudent(age,classId,NAME);#此时第三条查询语句默认使用最后一条索引,而不是前两个【2】. 最佳左前缀法则#【2】.最佳左前缀法则EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHEREstudent.age=30ANDstudent.name='abcd';#查age&name，用age的索引EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHEREstudent.classid=1ANDstudent.name='abcd';#查classid&name，classid在前，有索引的话先找classid相同的，再找name，#但现在没有这样的索引，idx_age_classid_name的字段顺序是先找age，所以不符合，所以此时不能用索引EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHEREclassid=4ANDstudent.age=30ANDstudent.name='abcd';#idx_age_classid_name联合索引中所有字段均出现，可以使用该索引EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHEREstudent.age=30ANDstudent.name='abcd';#现在，删除idx_age和idx_age_classid，发现用到idx_age_classid_name，而key_len=5,即只用到age字段，int(4)+null(1)#因为索引完age后没有classid了，不能再查找到name【3】. 主键插入顺序

在定义表时，让主键auto_increment，否则，插入一条数据时可能会移动大量数据。

如，往 1 5 8 10 15 … 100 中插9，会放在8 10 中间，因为索引默认升序排列。那么10往后的数据都要挪动，页不够时又要放到下一页，每插一条数据都这样挪一次，开销很大

我们自定义的主键列id 拥有AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

【4】. 计算、函数、类型转换(自动或手动)导致索引失效#【4】.计算、函数、类型转换(自动或手动)导致索引失效#####例1：EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHEREstudent.nameLIKE'abc%';#更好，能够使用上索引#type=range使用了索引中的排序EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHERELEFT(student.name,3)='abc';#left(text,num_chars):截取左侧n个字符#type=all全表的访问#该语句的执行过程：针对每一条数据，一个一个取出，先作用一遍函数，再拿函数结果与abc对比，用不上b+树CREATEINDEXidx_nameONstudent(NAME);#####例2：CREATEINDEXidx_snoONstudent(stuno);EXPLAINSELECTSQL_NO_CACHEid,stuno,NAMEFROMstudentWHEREstuno+1=900001;#type=all需要做运算，无法直接用索引找值EXPLAINSELECTSQL_NO_CACHEid,stuno,NAMEFROMstudentWHEREstuno=900000;#type=ref【5】. 类型转换导致索引失效#【5】.类型转换导致索引失效#未使用到索引EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHERENAME=123;#这里使用了隐式转换#使用到索引EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHERENAME='123';#name本身就是字符串类型【6】. 范围条件右边的列索引失效#【6】.范围条件右边的列索引失效（><>=<=between等）SHOWINDEXFROMstudent;CALLproc_drop_index('atguigudb2','student');CREATEINDEXidx_age_classid_nameONstudent(age,classId,NAME);EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHEREstudent.age=30ANDstudent.classId>20ANDstudent.name='abc';#这三个and先写谁无所谓，优化器会调优#key_len=10,age=5,classId=5,name用不上。classId是范围，索引右侧的name用不上#改写索引：CREATEINDEXidx_age_name_cidONstudent(age,NAME,classId);#把需要排序的classid放到最后#此时在执行上面的语句，就使用了这个索引，key_len=73

创建的联合索引中，必须把涉及到范围的字段写在最后。

【7】. 不等于(!= 或者<>)索引失效#【7】.不等于(!=或者<>)索引失效CREATEINDEXidx_nameONstudent(NAME);EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHEREstudent.name<>'abc';#索引失效索引查的是等于【8】. is null可以使用索引，is not null无法使用索引#【8】.isnull可以使用索引，isnotnull无法使用索引EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHEREageISNULL;#type=ref相当于等于某个值EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHEREageISNOTNULL;#索引失效相当于不等于【9】. like以通配符%开头索引失效#【9】.like以通配符%开头索引失效EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHERENAMELIKE'ab%';#可用索引EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHERENAMELIKE'%ab';#type=all索引失效

页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

【10】. OR 前后存在非索引的列，索引失效#【10】.OR前后存在非索引的列，索引失效CALLproc_drop_index('atguigudb2','student');SHOWINDEXFROMstudent;#创建一个age的索引CREATEINDEXidx_ageONstudent(age);EXPLAINSELECTSQL_NO_CACHE*FROMstudentWHEREage=10ORclassid=100;#未使用索引，索引+全表扫描->全表扫描#再加一个字段的单独索引CREATEINDEXidx_cidONstudent(classid);#再执行上条语句，此时type=index_merge，key=idx_age,idx_cid。【11】. 数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

关于“SQL索引失效的情况有哪些”这篇文章的内容就介绍到这里，感谢各位的阅读！相信大家对“SQL索引失效的情况有哪些”知识都有一定的了解，大家如果还想学习更多知识，欢迎关注行业资讯频道。