解析MySQL数据库SQL优化与索引优化

　　一、索引优化：
　　1、like语句的前导模糊查询不使用索引：selectfromdocwheretitlelikeXX；不能使用索引selectfromdocwheretitlelikeXX；非前导模糊查询，可以使用索引
　　2、负向条件查询不能使用索引：
　　负向条件有：！、、notin、notexists、notlike等
　　例如下面SQL语句：（假设status的取值为0、1、2、3、4）selectfromdocwherestatus！1andstatus！2；不能使用索引selectfromdocwherestatusin（0，3，4）；优化为in查询，可以使用索引
　　3、范围条件右边的列不能使用索引（范围列可以用到索引）：
　　范围条件有：、、、、between等。
　　索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。
　　假如有联合索引（empno、title、fromdate），那么下面的SQL中empno可以用到索引，而title和fromdate则使用不到索引。selectfromemployees。titleswhereempno10010andtitleSeniorEngineerandfromdatebetween19860101and19861231
　　4、在索引列做任何操作（计算、函数、表达式）会导致索引失效而转向全表扫描：selectfromdocwhereYEAR（createtime）2016；不能使用索引selectfromdocwherecreatetime20160101；可以使用索引selectfromorderwheredateCURDATE（）；不能使用索引selectfromorderwheredate2018012412：00：00；可以使用索引selectidfromtwheresubstring（name，1，3）’abc’不能使用索引selectidfromtwherenamelike‘abc’可以使用索引selectidfromtwherenum2100不能使用索引selectidfromtwherenum1002可以使用索引
　　5、where子句中索引列使用参数，也会导致索引失效：
　　因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：selectidfromtwherenumnum不能使用索引selectidfromtwith（index（索引名））wherenumnum可以改为强制查询使用索引：
　　6、强制类型转换会导致全表扫描：
　　字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换，相当于在索引列上进行了操作。
　　如果phone字段是varchar类型，则下面的SQL不能命中索引，因为内部发生的类型转换。selectfromuserwherephone13800001234；不能使用索引selectfromuserwherephone13800001234；可以使用索引
　　7、isnull，isnotnull在无法使用索引，不过在mysql的高版本已经做了优化，允许使用索引mysql低版本不能使用索引selectidfromtwherenum0；可以在num上设置默认值0，确保表中num列没有null值，然后这样查询
　　8、使用组合索引时，要符合最左前缀原则：
　　组合索引的字段数不允许超过5个。如果在a，b，c三个字段上建立联合索引index（a，b，c），那么他会自动建立a、（a，b）、（a，b，c）三组索引。
　　（1）建立联合索引的时候，区分度最高的字段在最左边：
　　（2）存在等号和非等号混合判断条件时，在建立索引时，把等号条件的列前置，如wherea？andb？，那么即使a的区分度更高，也必须把b放在索引的最前列。
　　（3）最左前缀查询时，并不是指SQL语句的where顺序要和联合索引一致，但还是建议where条件的顺序和联合索引一致。
　　（4）假如index（a，b，c），wherea3andblikeabcandc4，a能用，b能用，c不能用。
　　9、利用覆盖索引来进行查询操作，避免回表，减少select的使用：
　　覆盖索引：被查询列要被所建的索引覆盖，被查询列的数据能从索引中直接取得，不用通过行定位符再到row上获取，加速查询速度。
　　例如登录业务需求，SQL语句如下。Selectuid，logintimefromuserwhereloginname？andpasswd？
　　可以建立（loginname，passwd，logintime）的联合索引，由于logintime已经建立在索引中了，被查询的uid和logintime就不用去row上获取数据了，从而加速查询。
　　10、利用索引下推减少回表的次数：
　　索引下推是Mysql5。6版本推出的功能，用于优化查询。不使用索引下推的情况下，在使用非主键索引进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务层，服务层然后判断数据是否符合条件。使用索引下推的情况下，如果存在某些被索引的列的判断条件时，MySQL服务层将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务层传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务层。
　　所以，索引下推就是存储引擎查询数据时，根据查询条件过滤掉一些记录，减少回表的次数，也可以减少MySQL服务层从存储引擎接收数据量。
　　11、使用前缀索引：
　　短索引不仅可以提高查询性能而且可以节省磁盘空间和IO操作，减少索引文件的维护开销，但缺点是不能用于ORDERBY和GROUPBY操作，也不能用于覆盖索引。比如有一个varchar（255）的列，如果该列在前10个或20个字符内，可以做到既使前缀索引的区分度接近全列索引，那么就不要对整个列进行索引。为了减少keylen，可以考虑创建前缀索引，即指定一个前缀长度，可以使用count（distinctleftIndex（列名，索引长度））count（）来计算前缀索引的区分度（计算前缀索引的区分度在文章第三部分会介绍）。
　　12、orderby、groupby后面的列如果有索引，可以利用索引的有序性可以消除排序带来的CPU开销。
　　（1）orderby最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现filesort的情况，影响查询性能。例如对于语句wherea？andb？orderbyc，可以建立联合索引（a，b，c）。
　　（2）如果索引中有范围查找，那么索引有序性无法利用，如WHEREa10ORDERBYb；索引（a，b）无法排序。
　　（3）如果是前缀索引，是不能消除排序的
　　（4）orderby排序字段顺序，即ascdesc升降要跟索引保持一致，充分利用索引的有序性来消除排序带来的CPU开销
　　12、进行join联表查询的字段需要建立索引，join最好不要超过三个表，需要join的字段，数据类型必须一致：
　　多表关联查询时，保证被关联的字段需要有索引。leftjoin是由左边决定的，左边的数据一定都有，所以右边是我们的关键点，建立索引要建右边的。当然如果索引在左边，可以用rightjoin。
　　13、单表索引建议控制在5个以内。
　　索引不是越多越好，索引固然可以提高相应的select的效率，但同时也降低了insert及update的效率，因为insert或update时有可能会重建索引，同时也会暂用空间。一个表的索引数较好不要超过5个。
　　14、SQL性能优化explain中的type：至少要达到range级别，要求是ref级别，如果可以是consts最好。
　　consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
　　ref：使用普通的索引
　　range：对索引进行范围检索。
　　当typeindex时，索引物理文件全扫，速度非常慢。
　　15、业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引，防止脏数据产生：
　　不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然。
　　16、更新十分频繁、数据区分度不高的列不宜建立索引：
　　数据更新会变更B树，在更新频繁的字段建立索引会大大降低数据库性能。类似于性别这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。一般区分度在80以上的时候就可以建立索引，区分度可以使用count（distinct（列名））count（）来计算。二、SQL语句优化：
　　1、减少请求的数据量：
　　（1）只返回必要的列，用具体的字段列表代替select语句
　　MySQL数据库是按照行的方式存储，而数据存取操作都是以一个页大小进行IO操作的，每个IO单元中存储了多行，每行都是存储了该行的所有字段。所以无论取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。但是如果查询的字段都在索引中，也就是覆盖索引，那么可以直接从索引中获取对应的内容直接返回，不需要进行回表，减少IO操作。除此之外，当存在orderby操作的时候，select子句中的字段多少会在很大程度上影响到我们的排序效率。
　　（2）只返回必要的行，使用Limit语句来限制返回的数据。如果不使用Limit的话，MySQL将会一行一行的将全部结果按照顺序查找，最后返回结果，借助Limit可以实现当找到指定行数时，直接返回查询结果，提高效率
　　2、优化深度分页的场景：利用延迟关联或者子查询
　　对于limitm，n的分页查询，越往后面翻页（即m越大的情况下）SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。因为MySQL并不是跳过offset行，而是取offsetN行，然后放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。
　　延迟关联示例如下，先快速定位需要获取的id段，然后再关联：延迟关联：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据覆盖索引：select的数据列只用从索引中就能够得到，不用回表查询selecta。from表1a，（selectidfrom表1where条件limit100000，20）bwherea。idb。
　　但对于深度分页的情况，最好还是将上次遍历到的最末尾的数据ID传给数据库，然后直接定位到该ID处再往后面遍历数据
　　3、分解大连接查询：
　　将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：（1）减少锁竞争；（2）让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。（3）分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。（4）在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。（5）查询本身效率也可能会有所提升。比如使用IN（）代替连接查询，可以让MySQL按照ID顺序进行查询，这可能比随机的连接要更高效。
　　4、避免使用select的内联子查询：
　　在select后面有子查询的情况称为内联子查询，SQL返回多少行，子查询就需要执行过多少次，严重影响SQL性能。
　　5、尽量使用Join代替子查询：
　　由于MySQL的优化器对于子查询的处理能力比较弱，所以不建议使用子查询，可以改写成InnerJoin，之所以join连接效率更高，是因为MySQL不需要在内存中创建临时表selectb。memberid，b。membertype，a。createtime，a。devicemodelfrommemberoperationlogainnerjoin（selectmemberid，membertypefrommemberbaseinfowherestatus1）asbona。memberidb。
　　6、多张大数据量的表进行JOIN连接查询，最好先过滤在JOIN：
　　在多个表进行join连接查询的时候，最好先在一个表上先过滤好数据，然后再用过滤好的结果集与另外的表Join，这样可以尽可能多的减少不必要的IO操作，大大节省IO操作所消耗的时间
　　7、避免在使用or来连接查询条件：
　　如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。
　　8、union、in、or都能够命中索引，但推荐使用in：
　　（1）union：能够命中索引，并且MySQL耗费的CPU最少selectfromdocwherestatus1unionallselectfromdocwherestatus2；
　　（2）in：能够命中索引，查询优化耗费的CPU比unionall多，但可以忽略不计selectfromdocwherestatusin（1，2）；
　　（3）or：新版的MySQL能够命中索引，但是如果一个字段有建立索引、一个字段没有建立索引，那么将导致索引失效而进行全表扫描，or查询优化耗费的CPU比in多selectfromdocwherestatus1orstatus2
　　对于上面三种关键词：unionall分两步执行，而in和or只用了一步，效率高一点。用or的执行时间比in时间长。因为使用or条件查询，会先判断一个条件进行筛选，再判断or中另外的条件再筛选，而in查询直接一次在in的集合里筛选，并且or查询优化耗费的CPU比in多，所以推荐使用in
　　9、对于连续的数值，能用between就不要用in：
　　10、小表驱动大表，即小的数据集驱动大的数据集：
　　in和exists都可以用于子查询，那么MySQL中in和exists有什么区别呢？（1）使用exists时会先进行外表查询，将查询到的每行数据带入到内表查询中看是否满足条件；使用in一般会先进行内表查询获取结果集，然后对外表查询匹配结果集，返回数据。（2）in在内表查询或者外表查询过程中都会用到索引；exists仅在内表查询时会用到索引（3）一般来说，当子查询的结果集比较大，外表较小使用exist效率更高；当子查询的结果集较小，外表较大时，使用in效率更高。（4）对于notin和notexists，notexists效率比notin的效率高，与子查询的结果集无关，因为notin对于内外表都进行了全表扫描，没有使用到索引。notexists的子查询中可以用到表上的索引。
　　11、使用unionall替换union：
　　当SQL语句需要union两个查询结果集合时，这两个结果集合会以unionall的方式被合并，然后再输出最终结果前进行排序。如果用unionall替代union，这样排序就不是不要了，效率就会因此得到提高。。需要注意的是，UNIONALL将重复输出两个结果集合中相同记录。
　　12、优化Groupby，使用where子句替换Having子句：
　　避免使用having子句，having只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序分组，如果能通过where子句提前过滤查询的数目，就可以减少这方面的开销。
　　on、where、having这三个都可以加条件的子句，on是最先执行，where次之，having最后。
　　提高GROUPBY语句的效率，可以通过将不需要的记录在GROUPBY之前过滤掉。低效：SELECTJOB，AVG（SAL）FROMEMPGROUPbyJOBHAVINGJOB‘PRESIDENTORJOB‘MANAGER高效：SELECTJOB，AVG（SAL）FROMEMPWHEREJOB
　　13、尽量使用数字型字段：
　　若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能。引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
　　14、写出统一的SQL语句：
　　对于以下两句SQL语句，很多人都认为是相同的。不过数据库查询优化器则认为是不同的，虽然只是大小写不同，但必须进行两次解析，生成2个执行计划。所以应该保证相同的查询语句在任何地方都一致，多一个空格都不行。selectfromdualselectFromdual
　　15、使用复合索引须遵守最左前缀原则：
　　复合索引必须使用到最左边字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
　　16、当需要全表删除且无需回滚时，使用Truncate替代delete
　　17、使用表的别名
　　当在SQL语句中连接多个表时，使用表的别名并把别名前缀用于每个Column上，这样可以减少解析的时间并减少那些由Column歧义引起的语法错误。
　　18、避免使用耗费资源的操作：
　　带有DISTINCT，UNION，MINUS，INTERSECT，ORDERBY的SQL语句，会启动SQL引擎执行耗费资源的排序功能，DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。通常。带有UNION，MINUS，INTERSECT的SQL语句都可以用其他方式重写，如果你的数据库的SORTAREASIZE调配得好，使用UNION，MINUS，INTERSECT也是可以考虑的，毕竟它们的可读性很强。
　　19、Update语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。
　　20、应尽可能的避免更新聚簇索引数据列，因为聚簇索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。
　　21、尽量使用表变量来代替临时表。
　　22、考虑使用临时表暂存中间结果。临时表并不是不可使用，适当地使用它们可以使某些查询更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。将临时结果暂存在临时表，后面的查询就在临时表中查询了，这可以避免程序中多次扫描主表，也大大减少了程序执行中共享锁阻塞更新锁，减少了阻塞，提高了并发性能。但是，对于一次性事件，较好使用导出表。
　　23、在新建临时表时，如果一次性插入数据量很大，那么可以使用selectinto代替createtable，避免造成大量log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先createtable，然后insert。
　　24、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncatetable，然后droptable，这样可以避免系统表的较长时间锁定。
　　25、避免频繁创建和删除临时表，以减少系统表资源的消耗。
　　26、尽量避免使用游标，因为游标的效率较差。与临时表一样，游标并不是不可使用。对小型数据集使用FASTFORWARD游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括合计的例程通常要比使用游标执行的速度快。
　　27、在所有的存储过程和触发器的开始处设置SETNOCOUNTON，在结束时设置SETNOCOUNTOFF。
　　28、尽量避免大事务操作，提高系统并发能力。
　　29、在运行代码中，尽量使用PreparedStatement来查询，不要用Statement。三、索引的选择性与前缀索引：
　　既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。
　　第一种情况是表记录比较少，没必要建索引，让查询做全表扫描就好了。
　　第二种情况是索引的选择性较低。所谓索引的选择性，是指不重复的索引值与表记录数量的比值：
　　显然选择性的取值范围为（0，1〕，选择性越高的索引价值越大，这是由BTree的性质决定的。
　　例如，employees。titles表，如果title字段经常被单独查询，是否需要建索引，我们看一下它的选择性：SELECTcount（DISTINCT（title））count（）ASSelectivityFROMemployees。Selectivity0。0000
　　title的选择性不足0。0001（精确值为0。00001579），所以实在没有什么必要为其单独建索引。
　　有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees。employees表为例介绍前缀索引的选择和使用。
　　假设employees表只有一个索引，那么如果我们想按名字搜索一个人，就只能全表扫描了：EXPLAINSELECTFROMemployees。employeesWHEREfirstnameEricANDlastnameAidselecttypetabletypepossiblekeyskeykeylenrefrowsExtra1SIMPLEemployeesALLNULLNULLNULLNULL300024Usingwhere
　　如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建或firstname，lastname，看下两个索引的选择性：SELECTcount（DISTINCT（firstname））count（）ASSelectivityFROMemployees。Selectivity0。0042SELECTcount（DISTINCT（concat（firstname，lastname）））count（）ASSelectivityFROMemployees。Selectivity0。9313
　　显然选择性太低，firstname，lastname选择性很好，但是firstname和lastname加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用firstname和lastname的前几个字符建立索引，例如firstname，left（lastname，3），看看其选择性：SELECTcount（DISTINCT（concat（firstname，left（lastname，3））））count（）ASSelectivityFROMemployees。Selectivity0。7879
　　选择性还不错，但离0。9313还是有点距离，那么把lastname前缀加到4：SELECTcount（DISTINCT（concat（firstname，left（lastname，4））））count（）ASSelectivityFROMemployees。Selectivity0。9007
　　这时选择性已经很理想了，而这个索引的长度只有18，比firstname，lastname短了接近一半，我们把这个前缀索引建上：ALTERTABLEemployees。employeesADDINDEXfirstnamelastname4（firstname，lastname（4））；
　　此时再执行一遍按名字查询，比较分析一下与建索引前的结果：SHOWPROFILES；QueryIDDurationQuery870。11941700SELECTFROMemployees。employeesWHEREfirstnameEricANDlastnameAnido900。00092400SELECTFROMemployees。employeesWHEREfirstnameEricANDlastnameAnido
　　性能的提升是显著的，查询速度提高了120多倍。
　　前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDERBY和GROUPBY操作，也不能用于覆盖索引作者：张维鹏
　　原文链接：https：blog。csdn。neta745233700articledetails84455241