MySQL性能调优二

　　覆盖索引尽量用
　　覆盖索引是非常有用的工具，能够极大地提高性能，三星索引里最重要的那颗星就是宽索引星。考虑一下如果查询只需要扫描索引而无须回表，会带来多少好处：
　　索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于IO密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中。
　　因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少得多。
　　由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。
　　尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），不是必要的情况下减少select，除非是需要将表中的全部列检索后，进行缓存。EXPLAINselectfromorderexpcutwhereinserttime2021032218：34：55andorderstatus0andexpiretime2021032218：35：04；
　　使用具体名称取代EXPLAINselectexpiretime，idfromorderexpcutwhereinserttime2021032218：34：55andorderstatus0andexpiretime2021032218：35：04；
　　解释一下Extra中的Usingindex
　　当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在Extra列将会提示该额外信息。以上的查询中只需要用到uidxdaystatus而不需要回表操作：不等于要慎用
　　mysql在使用不等于（！或者）的时候无法使用索引会导致全表扫描EXPLAINSELECTFROMorderexpWHEREordernoDD006S；
　　解释一下Extra中的Usingwhere当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，在Extra列中会提示上述额外信息。NullNot有影响
　　需要注意nullnotnull对索引的可能影响
　　表orderexp的orderno为索引列，同时不允许为null，
　　explainSELECTFROMorderexpWHEREordernoisnull；explainSELECTFROMorderexpWHEREordernoisnotnull；
　　可以看见，ordernoisnull的情况下，MySQL直接表示ImpossibleWHERE（查询语句的WHERE子句永远为FALSE时将会提示该额外信息），对于isnotnull直接走的全表扫描。
　　表orderexpcut的orderno为索引列，同时允许为null，explainSELECTFROMorderexpcutWHEREordernoisnull；explainSELECTFROMorderexpcutWHEREordernoisnotnull；
　　isnull会走ref类型的索引访问，isnotnull；依然是全表扫描。所以总结起来：
　　isnotnull容易导致索引失效，isnull则会区分被检索的列是否为null，如果是null则会走ref类型的索引访问，如果不为null，也是全表扫描。
　　但是当联合索引上使用时覆盖索引时，情况会有一些不同（orderexpcut表的orderno可为空）：explainSELECTorderstatus，expiretimeFROMorderexpWHEREinserttimeisnull；explainSELECTorderstatus，expiretimeFROMorderexpWHEREinserttimeisnotnull；
　　explainSELECTorderstatus，expiretimeFROMorderexpcutWHEREinserttimeisnull；explainSELECTorderstatus，expiretimeFROMorderexpcutWHEREinserttimeisnotnull；
　　根据systemconsteqrefrefrangeindexALL的原则，看起来在联合索引中，isnotnull的表现会更好（如果列可为null的话），但是keylen的长度增加了1。所以总的来说，在设计表时列尽可能的不要声明为null。
　　Like查询要当心
　　like以通配符开头（abc。。。），mysql索引失效会变成全表扫描的操作explainSELECTFROMorderexpWHEREordernolike6S；
　　此时如果使用覆盖索引可以改善这个问题
　　explainSELECTorderstatus，expiretimeFROMorderexpcutWHEREinserttimelike18：35：09；
　　字符类型加引号
　　字符串不加单引号索引失效explainSELECTFROMorderexpWHEREorderno6；explainSELECTFROMorderexpWHEREorderno6；
　　MySQL的查询优化器，会自动的进行类型转换，比如上个语句里会尝试将orderno转换为数字后和6进行比较，自然造成索引失效。使用or关键字时要注意explainSELECTFROMorderexpWHEREordernoDD006SORordernoDD009S；explainSELECTFROMorderexpWHEREexpiretime2021032218：35：09ORordernoteabc；
　　表现是不一样的，第一个SQL的or是相同列，相当于产生两个扫描区间，可以使用上索引。
　　第二个SQL中or是不同列，并且ordernote不是索引。所以只能全表扫描
　　当然如果两个条件都是索引列，情况会有变化：explainSELECTFROMorderexpWHEREexpiretime2021032218：35：09ORordernoDD006S；
　　这也给了我们提示，如果我们将SQL改成unionallexplainSELECTFROMorderexpWHEREexpiretime2021032218：35：09unionallSELECTFROMorderexpWHEREordernoteabc；
　　当然使用覆盖扫描也可以改善这个问题：explainSELECTorderstatus，idFROMorderexpcutWHEREinserttime2021032218：34：55orexpiretime2021032218：28：28；
　　使用索引扫描来做排序和分组
　　MySQL有两种方式可以生成有序的结果通过排序操作或者按索引顺序扫描施如果EXPLAIN出来的type列的值为index，则说明MySQL使用了索引扫描来做排序。
　　扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机IO，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在IO密集型的工作负载时。
　　MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。
　　只有当索引的列顺序和ORDERBY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当0RDERBY子句引用的字段全部为第一个表时，才能使用索引做排序。排序要当心
　　ASC、DESC别混用
　　对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序。
　　排序列包含非同一个索引的列
　　用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序explainSELECTFROMorderexporderbyorderno，inserttime；
　　尽可能按主键顺序插入行
　　最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于IO密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕，它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。
　　最简单的方法是使用AUTOINCREMENT自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。
　　注意到向UUID主键插入行不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长另一方面毫无疑问是由于页分裂和碎片导致的。
　　因为主键的值是顺序的，所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子是页大小的1516，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，这也正是所期望的结果。
　　如果新行的主键值不一定比之前插入的大，所以InnoDB无法简单地总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置通常是已有数据的中间位置并且分配空间。这会增加很多的额外工作，并导致数据分布不够优化。下面是总结的一些缺点：
　　写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机IO。
　　因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
　　所以使用InnoDB时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。优化Count查询
　　首先要注意，COUNT（）是一个特殊的函数，有两种非常不同的作用：它可以统计某个列值的数量，也可以统计行数。
　　在统计列值时要求列值是非空的（不统计NULL）。
　　COUNT（）的另一个作用是统计结果集的行数。常用的就是就是当我们使用COUNT（）。实际上，它会忽略所有的列而直接统计所有的行数。
　　selectcount（）fromtest；selectcount（c1）fromtest；
　　通常来说，COUNT（）都需要扫描大量的行（意味着要访问大量数据）才能获得精确的结果，因此是很难优化的。在MySQL层面能做的基本只有索引覆盖扫描了。如果这还不够，就需要考虑修改应用的架构，可以用估算值取代精确值，可以增加汇总表，或者增加类似Redis这样的外部缓存系统。优化limit分页
　　在系统中需要进行分页操作的时候，我们通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDERBY子句。
　　一个非常常见又令人头疼的问题就是，在偏移量非常大的时候，例如可能是selectfromorderexplimit10000，10；
　　这样的查询，这时MySQL需要查询10010条记录然后只返回最后10条，前面10000条记录都将被抛弃，这样的代价非常高。
　　优化此类分页查询的一个最简单的办法是
　　会先查询翻页中需要的N条数据的主键值，然后根据主键值回表查询所需要的N条数据，在此过程中查询N条数据的主键id在索引中完成，所以效率会高一些。EXPLAINSELECTFROM（selectidfromorderexplimit10000，10）b，orderexpawherea。idb。id；
　　从执行计划中可以看出，首先执行子查询中的orderexp表，根据主键做索引全表扫描，然后与a表通过id做主键关联查询，相比传统写法中的全表扫描效率会高一些。
　　从两种写法上能看出性能有一定的差距，虽然并不明显，但是随着数据量的增大，两者执行的效率便会体现出来。
　　上面的写法虽然可以达到一定程度的优化，但还是存在性能问题。最佳的方式是在业务上进行配合修改为以下语句：EXPLAINselectfromorderexpwhereid67orderbyidlimit10；
　　采用这种写法，需要前端通过点击More来获得更多数据，而不是纯粹的翻页，因此，每次查询只需要使用上次查询出的数据中的id来获取接下来的数据即可，但这种写法需要业务配合。
　　关于Null的特别说明
　　对于Null到底算什么，存在着分歧：
　　1、有的认为NULL值代表一个未确定的值，MySQL认为任何和NULL值做比较的表达式的值都为NULL，包括selectnullnull和selectnull！null；
　　所以每一个NULL值都是独一无二的。
　　2、有的认为其实NULL值在业务上就是代表没有，所有的NULL值和起来算一份；
　　3、有的认为这NULL完全没有意义，所以在统计数量时压根儿不能把它们算进来。
　　假设一个表中某个列c1的记录为（2，1000，null，null），在第一种情况下，表中c1的记录数为4，第二种表中c1的记录数为3，第三种表中c1的记录数为2。
　　在对统计索引列不重复值的数量时如何对待NULL值，MySQL专门提供了一个innodbstatsmethod的系统变量，
　　这个系统变量有三个候选值：
　　nullsequal：认为所有NULL值都是相等的。这个值也是innodbstatsmethod的默认值。
　　如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。
　　nullsunequal：认为所有NULL值都是不相等的。
　　如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别少，所以倾向于使用索引进行访问。
　　nullsignored：直接把NULL值忽略掉。
　　而且有迹象表明，在MySQL5。7。22以后的版本，对这个innodbstatsmethod的修改不起作用，MySQL把这个值在代码里写死为nullsequal。也就是说MySQL在进行索引列的数据统计行为又把null视为第二种情况（NULL值在业务上就是代表没有，所有的NULL值和起来算一份），看起来，MySQL中对Null值的处理也很分裂。所以总的来说，对于列的声明尽可能的不要允许为null。