5如何正确使用索引加速小技巧：500万建表sql参考网盘sql脚本[root@linux-141bin]#./mysql-uroot-pitcastselectcount(1)fromproduct_list;+----------+|计数(1)|+----------+|5072825|+--------+1rowinset(1.71sec)mysql>1)QuerybyIDSELECT*FROMproduct_listWHEREid=121926;查询速度很快，接近0s，主要原因是id为主键，有索引；2).根据store_name执行精确查询需要4分钟SELECT*FROMproduct_listWHEREstore_name='LenovoBeidaXingkeStore';查看SQL语句的执行计划：explainSELECT*FROMproduct_listWHEREstore_name='LenovoBeidaXingkeStore';处理方案，针对store_name字段，创建索引：createindexproduct_list_stnameonproduct_list(store_name);创建好索引后，再次查询：SELECT*FROMproduct_listWHEREstore_name='联想北大兴科店';通过explain，查看执行计划，执行SQL时使用了刚才创建的索引--查看SQL语句的执行计划explainSELECT*FROMproduct_listWHEREstore_name='联想北大兴科店';5.2索引的使用5.2.1准备环境createtable`tb_seller`(`sellerid`varchar(100),`name`varchar(100)notnull,`nickname`varchar(50),`password`varchar(60),`status`varchar(1)notnull,`address`varchar(100)notnullull,`createtime`datetime,primarykey(`sellerid`))engine=innodbdefaultcharset=utf8;insertinto`tb_seller`(`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`)values('alibaba','Alibaba','AliShop','e10adc3949ba59abbe56e057f20f883e','1','北京','2088-01-0112:00:00');insertinto`tb_seller`(`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`)values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京','2088-01-0112:00:00');insertinto`tb_seller`(`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`)values('huawei','华为技术有限公司,Ltd','HuaweiStore','e10adc3949ba59abbe56e057f20f883e','0','Beijing','2088-01-0112:00:00');insertinto`tb_seller`(`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`)values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京','2088-01-0112:00:00');insertinto`tb_seller`(`sellerid`,`name`,`昵称`,`密码`,`状态`,`地址`,`createtime`)values('iheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京城市','2088-01-0112:00:00');insertinto`tb_seller`(`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`)values('luoji','罗技科技有限公司','LogitechStore','e10adc3949ba59abbe56e057f20f883e','1','北京','2088-01-0112:00:00');insertinto`tb_seller`(`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`)values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京','2088-01-0112:00:00');insertinto`tb_seller`(`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`)values('ourpalm','PalmTechnologyCo.,Ltd','PalmShop','e10adc3949ba59abbe56e057f20f883e','1','北京','2088-01-0112:00:00');insertinto`tb_seller`(`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`)values('qiandu','千都科技','千都小电','e10adc3949ba59abbe56e057f20f883e','2','北京','2088-01-0112:00:00');insertinto`tb_seller`(`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`)values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京','2088-01-0112:00:00');insertinto`tb_seller`(`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`)values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安','2088-01-0112:00:00');insertinto`tb_seller`(`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`)values('yijia','宜家','宜家旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京','2088-01-0112:00:00');在tb_seller(name,status,address)上创建索引idx_seller_name_sta_addr;5.2.2避免索引失效Compositeindex(name,status,address)1）全值匹配为索引值中的所有列指定特定值--全值匹配说明select*fromtb_sellerwherename='XiaomiTechnology'andstatus='1'andaddress='Beijing';ken_len=3*N+2;--namevarchar(100)==302--statusvarchar(1)==5--addressvarchar(100)==3022)最左前缀规则如果索引多列，则最左必须遵循前缀规则。指的是从索引最左边的前列开始查询，不跳过索引中的列。匹配最左前缀规则，进入索引：explainselect*fromtb_sellerwherename='小米科技';违反最左前缀规则，索引无效：explainselect*fromtb_sellerwherestatus='1';解释select*fromtb_sellerwherestatus='1'andaddress='北京';如果符合最左规则，但某列跳转，则只有最左列索引生效：explainselect*fromtb_sellerwherename='小米科技'andaddress='北京';3）右边的列范围查询——使用范围查询时，右边的列无效。说明select*fromtb_sellerwherename='XiaomiTechnology'andstatus='1'andaddress='Beijing';说明select*fromtb_sellerwherename='XiaomiTechnology'andstatus>'1'andaddress='Beijing';根据前面两个字段名，状态查询使用了索引，但是最后一个条件地址没有使用索引。4）禁止列操作——不对索引列进行操作，索引将失效。说明select*fromtb_sellerwheresubstring(name,3,2)='Technology';5)如果字符串没有加单引号，索引会失败。--没有单引号的字符串会导致索引失败。解释select*fromtb_sellerwherename='Technology'andstatus='0';解释select*fromtb_sellerwherename='Technology'andstatus=0;因为，查询的时候，字符串上没有单引号，MySQL的查询优化器会自动进行类型转换，导致索引失败。6）尽量使用覆盖索引，避免select*尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select*。--尝试使用覆盖索引解释select*fromtb_sellerwherename='Technology'andstatus='0'andaddress='Xi'anCity';explainselectnamefromtb_sellerwherename='Technology'andstatus='0'andaddress='Xi'anCity';explainselectname,statusfromtb_sellerwherename='Technology'andstatus='0'andaddress='Xi'anCity';explainselectname,status,addressfromtb_sellerwherename='Technology'andstatus='0'andaddress='Xi'anCity';如果查询列超过索引列，性能也会降低。解释从tb_seller中选择状态、地址、密码，其中name='Technology'andstatus='0'andaddress='Xi'anCity';TIP:usingindex:使用覆盖索引时，会出现usingwhere:搜索索引时某些情况下需要回表查询需要的数据usingindex条件：搜索使用索引，但是需要回表使用索引查询数据；usingwhere：查找使用了索引，但是在索引列中可以找到需要的数据，所以不需要回表查询数据7）合理使用or条件，条件用or分隔。如果条件中的列之前or有索引，但后面的列没有索引，则不会使用涉及的索引。比如name字段是索引列，但是createtime不是索引列，中间是or连接不带索引：explainselect*fromtb_sellerwherename='DarkHorseProgrammer'orcreatetime='2088-01-0112:00:00'；8）正确使用like查询以%like开头的模糊查询，索引会失效。--如果只是尾部模糊匹配，索引不会失效。如果是header模糊匹配，索引就失效了。解释select*fromtb_sellerwherenamelike'DarkHorseProgrammer%';解释select*fromtb_sellerwherenamelike'%DarkHorseProgram';解释select*fromtb_sellerwherenamelike'%DarkHorseProgrammer%';解决方案：通过覆盖Index来解决explainselectselleridfromtb_sellerwherenamelike'%Technology%';解释selectsellerid,namefromtb_sellerwherenamelike'%Technology%';explainselectsellerid,name,status,addressfromtb_sellerwherenamelike'%Technology%'%';9)合理评估索引执行如果MySQL评估使用索引比全表慢，不要使用索引。--如果MySQL评估使用索引比全表慢，则不要使用索引。在tb_seller(address)上创建索引idx_seller_addr；解释select*fromtb_sellerwhereaddress='北京';说明select*fromtb_sellerwhereaddress='Xi'an';10)isNULLandisNOTNULL有时索引失败。--isNULLandisNOTNULLexplainselect*fromtb_sellerwherenameisnull;解释select*fromtb_sellerwherenameisnotnull;解决方法：设置null值为默认值11)inandnotinin取索引，notinIndex无效。--在索引中，不在索引中无效。explainselect*fromtb_sellerwhereselleridin('oppo','xiaomi','sina');explainselect*fromtb_sellerwhereselleridnotin('oppo','xiaomi','sina');12)单列索引和复合索引尽量使用复合索引，少用单列索引。创建复合索引createindexidx_name_sta_addressontb_seller(name,status,address);相当于创建三个索引：namename+statusname+status+address创建单列索引createindexidx_seller_nameontb_seller(name);在tb_seller(status)上创建索引idx_seller_status；在tb_seller(address)上创建索引idx_seller_address；数据库会选择一个最优的索引（认可度最高的索引）使用，不会使用所有的索引。5.3查看索引使用情况showstatuslike'Handler_read%';显示全局状态，如“Handler_read%”；mysql>showstatuslike'Handler_read%';+----------------------+---------+|变量名|值|+------------------------+--------+|处理程序_read_first|18||Handler_read_key|19||处理程序_read_last|0||处理程序_read_next|5072825||处理程序_read_prev|0||--------------+--------+7rowsinset(0.02sec)mysql>Handler_read_first:索引中第一行的次数读。如果它很高，则意味着服务器正在进行大量的全索引扫描（值越低越好）。Handler_read_key：如果索引在工作，这个值代表一行被索引值读取的次数。如果该值较低，则说明该索引获得的性能提升不高，因为该索引使用不频繁（值越高越好）。Handler_read_next：按键顺序读取下一行的请求数。如果您查询具有范围约束的索引列或执行索引扫描，则此值会增加。handler_read_prev：按key顺序读取上一行的请求数。这种读方法主要用于优化ORDERBY...DESC。handler_read_rnd：根据固定位置读取一行的请求数。如果您正在进行大量查询并且需要对结果进行排序，则该值更高。您可能正在使用大量需要MySQL扫描整个表的查询，或者您的连接没有正确使用键。高值意味着操作效率低下，应该通过建立索引来弥补。Handler_read_rnd_next：请求读取数据文件中下一行的次数。如果您正在进行大量表扫描，则此值会更高。这通常意味着你的表索引不正确或者编写的查询没有利用索引。本文由传智教育博学谷-狂野建筑师教研团队发布，转载请注明出处！如果本文对您有帮助，请关注并点赞；如果您有什么建议，也可以留言或私信。您的支持是我坚持创作的动力
