SQL优化已经成为衡量程序员优秀与否的硬性指标，就连各大厂的工作职能都明码标价。如果是你，你能解决这个问题吗？吊打面试官还是被绞死？有朋友问，SQL优化真的那么重要吗？如下图所示，SQL优化是提升系统性能的途径：成本最低，优化效果最明显。如果你的团队在SQL优化方面做得很好，那无疑是你整个大型系统可用性的质的飞跃，真的会为你的老板省下不少大笔的钱。成本优化：硬件>系统配置>数据库表结构>SQL和索引。优化效果：硬件<系统配置<数据库表结构3.FROM4.JOIN5.ON6.WHERE7??.GROUPBY8。HAVING9.ORDERBY10.LIMITSELECT语句，执行顺序如下：FROM<表名>#选择一个表，将多张表数据通过笛卡尔积转换为一张表。ON#过滤笛卡尔积的虚表JOIN#指定join，用于on之后向虚表添加数据，比如leftjoin将剩余的左表的数据添加到虚表中WHERE#过滤上面的虚表GROUPBY<分组条件>#分组#用于判断having子句，这是写的Class聚合函数写在having判断中HAVING#聚合过滤分组后的结果SELECT#返回的单列必须在groupby子句中，除了聚合函数DISTINCT#数据去重ORDERBY#sortingLIMIT以下SQL优化策略适用于数据量较大的场景。如果数据量小，就没必要以此为标准，以免蛇吞没。避免不使用索引的场景①尽量避免在字段开头进行模糊查询，这样会导致数据库引擎放弃索引，扫描全表，如下：SELECT*FROMtWHEREusernameLIKE'%陈%'优化方法：尽量在字段后面使用模糊查询。如下：SELECT*FROMtWHEREusernameLIKE'陈%'如果要求前面使用模糊查询：使用MySQL内置函数INSTR(str,substr)进行匹配，功能类似于Java中的indexOf()，下标在查询字符串中出现的位置。使用FullText全文索引，匹配搜索。在数据量大的情况下，推荐使用ElasticSearch和Solr，亿级数据的检索速度秒级。当表的数据量较小时（几千条），不要花哨，直接用like'%xx%'即可。②尽量避免使用in和notin，这样会导致引擎扫描整张表如下：SELECT*FROMtWHEREidIN(2,3)优化方法：如果是连续值，可以改用between。如下：SELECT*FROMtWHEREidBETWEEN2AND3如果是子查询，可以用exists代替。如下：--不使用索引select*fromAwhereA.idin(selectidfromB);--使用索引select*fromAwhereexists(select*fromBwhereB.id=A.id);③尽量避免使用or，会导致数据库引擎放弃全表索引扫描如下：SELECT*FROMtWHEREid=1ORid=3优化方法：可以用union代替or。如下：SELECT*FROMtWHEREid=1UNIONSELECT*FROMtWHEREid=3④尽量避免判断空值，这样会导致数据库引擎放弃索引，进行全表扫描如下：SELECT*FROMtWHEREscoreISNULL优化方法：可以加一个默认值0的字段，判断0的值。如下：SELECT*FROMtWHEREScore=0⑤尽量避免在where条件中等号左边进行表达式和函数操作，这样会导致数据库引擎放弃索引进行全表扫描。可以将表达式和函数操作移到等号右边，如下：--全表扫描SELECT*FROMTWHEREScore/10=9--去索引SELECT*FROMTWHEREScore=10*9⑥数据量大时，避免使用where1=1的条件通常为了方便拼装查询条件，我们会默认使用这个条件，数据库引擎会丢弃索引进行全表扫描。如下：SELECTusername,age,sexFROMTWHERE11=1优化方法：用代码组装SQL时判断，如果没有where条件就去掉where，如果有where条件就加上and。⑦查询条件不能使用<>或!=。使用索引列作为查询条件时，需要避免使用<>或!=等判断条件。如果业务确实需要，如果使用了不等号，需要重新评估索引的创建，避免在该字段上建索引，在查询条件中替换为其他索引字段。⑧where条件只包含复合索引的非前列如下：复合（联合）索引包含key_part1、key_part2、key_part3三列，但SQL语句不包含索引前列“key_part1”。根据MySQL联合索引的最左匹配原则，没有会取联合索引。selectcol1fromtablewherekey_part2=1andkey_part3=2⑨隐式类型转换导致不使用索引。SQL语句如下，因为索引对列类型是varchar，但是给定的值是一个值，涉及到隐式类型转换，导致索引不正确。selectcol1fromtablewherecol_varchar=123;⑩orderby条件必须和where条件一致，否则orderby不会使用索引排序如下：--不使用年龄索引SELECT*FROMtorderbyage;--使用年龄索引SELECT*FROMtwhereage>0orderbyage;对于上述语句，数据库的处理顺序为：第一步：根据where条件和统计信息生成执行计划，获取数据。第二步：对得到的数据进行排序。在执行数据处理（orderby）时，数据库会先检查第一步的执行计划，看orderby字段是否使用了执行计划中的索引。如果是这样，可以直接使用索引顺序获取排序后的数据。如果不是，请重新排序操作。第三步：返回排序后的数据。当orderby中的字段出现在where条件中时，会使用索引代替二次排序。更准确地说，当orderby中的字段使用执行计划中的索引时，不需要进行排序操作。这个结论不仅对orderby有效，对其他需要排序的操作也有效。如groupby、union、distinct等。?正确使用hint优化语句在MySQL中，hint可以用来指定优化器在执行过程中选择或忽略特定的索引。一般来说，版本变化导致表结构索引发生变化时，建议避免使用hint，而是通过Analyzetable收集更多的统计信息。但是在某些特定的场合，指定hint可以排除其他索引的干扰，指定更好的执行计划：USEINDEX在你的查询语句中的表名后面，加上USEINDEX，提供你希望MySQL引用的索引列表，这样MySQL其他不再考虑可用索引。例子：SELECTcol1FROMtableUSEINDEX(mod_time,name)...IGNOREINDEX如果你只是想让MySQL忽略一个或多个索引，你可以使用IGNOREINDEX作为Hint。示例：SELECTcol1FROMtableIGNOREINDEX(priority)...FORCEINDEX要强制MySQL使用特定索引，FORCEINDEX可以用作查询中的Hint。例子：SELECTcol1FROMtableFORCEINDEX(mod_time)...查询时，数据库系统会自动分析查询并选择最合适的索引。但是很多时候，数据库系统的查询优化器不一定总能使用到最优的索引。如果我们知道如何选择索引，我们可以使用FORCEINDEX强制查询使用指定的索引。例如：SELECT*FROMstudentsFORCEINDEX(idx_class_id)WHEREclass_id=1ORDERBYidDESC;SELECT语句的其他优化①避免select*首先，select*操作在任何类型的数据库中都不是一个好的SQL写法。使用select*取出所有列会使优化器无法完成索引覆盖扫描的优化，影响优化器对执行计划的选择，增加网络带宽消耗，带来额外的I/O、内存和CPU消耗。建议提出业务实际需要的列数，列名会指定，而不是select*。详见为什么大家都说SELECT*效率低下②避免不确定结果的功能是主从复制等业务场景特有的。由于原则上从库复制主库执行的语句，使用now()、rand()、sysdate()、current_user()等结果不确定的函数很容易导致主库之间数据不一致库和从库。另外，对于取值不确定的函数，生成的SQL语句不能使用querycache。③多表关联查询时，小表在前，大表在后。在MySQL中，执行from后的表关联查询是从左到右执行的（Oracle则相反），第一个表会涉及到全表扫描。所以把小桌子放在前面，先扫描小桌子，扫描速度更高。扫描完后面的大表，可能只扫描大表的前100行，满足返回条件就返回。例如：表1有50条数据，表2有30亿条数据；如果你想完整地扫描表2，我们先吃饭吧？④使用表的别名在SQL语句中连接多个表时，请使用表的别名，并在每个列名前加上别名。这减少了解析时间并减少了由不明确的友元列名称引起的语法错误。⑤将HAVING子句换成where子句，避免使用HAVING子句，因为HAVING只会在检索完所有记录后才对结果集进行过滤，而where是在聚合前选择记录。如果where子句可以用来限制记录的数量，那可以减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，否则要写在where子句中。where和having的区别：where后面不能使用组函数。⑥调整Where子句中的连接顺序MySQL从左到右，从上到下解析Where子句。根据这个原则，应该提出过滤大量数据的条件，尽快缩小结果集。增删改DML语句优化①大批量插入数据如果同时进行大量插入，建议使用多值INSERT语句（方法二）。这比使用单独的INSERT语句（方法1）更快，而且通常情况下，批量插入的效率相差数倍。方法一：insertintoTvalues(1,2);插入到T值（1,3）；插入到T值（1,4）；方法二：InsertintoTvalues(1,2),(1,3),(1,4);选择后者方法的原因有以下三个：为减少解析SQL语句的操作，MySQL没有类似Oracle的共享池。使用方法2，可以在解析后只进行一次数据插入。在某些场景下，可以减少数据库连接数。SQL语句更短，可以减少网络传输的IO。②适当使用commit适当使用commit可以释放事务占用的资源，减少消耗。commit后可以释放的资源如下：Undo事务占用的数据块。重做日志中事务记录的数据块。释放事务强加的锁，减少影响性能的锁争用。尤其是当需要使用delete删除大量数据时，一定要分解删除量，定期commit。③避免重复查询更新的数据为了满足业务中经常出现的更新行的需求，同时又想获取行信息，MySQL不支持像PostgreSQL那样的UPDATERETURNING语法，可以通过MySQL中的变量来实现。比如更新一行记录的时间戳，同时想查询当前记录存储的时间戳是多少？简单方法实现：Update1settime=now()wherecol1=1;Selecttimefromt1whereid=1;使用变量，可以改写如下：Update1settime=now()wherecol1=1and@nownow:=now();Select@now;before和after都需要两次网络往返，但是使用变量避免了再次访问数据表，尤其是当t1表的数据量很大时，后者比前者快很多。④查询优先级或更新（插入、更新、删除）优先级MySQL还允许改变语句调度的优先级，可以使多个客户端的查询更好地协作，使单个客户端不会因为锁定时间而等待很长时间。更改优先级还可以确保更快地处理某些类型的查询。首先要确定应用的类型，确定应用是面向查询还是面向更新，是保证查询效率还是更新效率，决定优先查询还是更新。下面说的改变调度策略的方法，主要是针对只有表锁的存储引擎，比如MyISAM、MEMROY、MERGE。对于Innodb存储引擎来说，语句的执行是由获取行锁的顺序决定的。MySQL默认的调度策略可以概括为：写操作优先于读操作。对数据表的写操作一次只能发生一次，写请求按照到达的先后顺序进行处理。可以同时对一个数据表进行多次读操作。MySQL提供了几个语句修饰符，允许您修改其调度策略：LOW_PRIORITY关键字适用于DELETE、INSERT、LOADDATA、REPLACE和UPDATE。HIGH_PRIORITY关键字适用于SELECT和INSERT语句。DELAYED关键字适用于INSERT和REPLACE语句。如果写操作是一个LOW_PRIORITY（低优先级）请求，那么系统不会认为它比读操作有更高的优先级。在这种情况下，如果第二个读取器在写入器等待时到达，则允许第二个读取器在写入器之前插入。只有在没有其他读者时才允许写者开始操作。这种调度修改可能会出现LOW_PRIORITY写操作被永远阻塞的情况。SELECT查询的HIGH_PRIORITY关键字也是如此。它允许在挂起的写操作之前插入SELECT，即使写操作通常具有更高的优先级。另一个影响是高优先级SELECT在普通SELECT语句之前执行，因为这些语句被写操作阻塞。如果您希望默认情况下以低优先级处理所有支持LOW_PRIORITY选项的语句，请使用--low-priority-updates选项启动服务器。通过使用INSERTHIGH_PRIORITY将INSERT语句提高到正常写入优先级，可以消除此选项对单个INSERT语句的影响。查询条件优化①对于复杂的查询，可以使用中间临时表临时存储数据②优化groupby语句默认情况下，MySQL会对所有按GROUPBY分组的值进行排序，例如“GROUPBYcol1,col2,...;”查询方式与指定“ORDERBYcol1,col2,...;”相同在查询中。如果你显式地包含一个包含相同列的ORDERBY子句，MySQL可以优化它而不会减慢速度，尽管排序仍然完成。因此，如果查询包含GROUPBY但您不想对分组值进行排序，则可以指定ORDERBYNULL来抑制排序。例如：SELECTcol1,col2,COUNT(*)FROMtableGROUPBYcol1,col2ORDERBYNULL;③优化join语句在MySQL中，可以使用SELECT语句通过子查询创建一个单列的查询结果，然后在另一个查询中将该结果作为过滤条件。使用子查询可以一次完成很多逻辑上需要多步才能完成的SQL操作，还可以避免事务或表锁，而且编写简单。但是，在某些情况下，可以用更高效的连接（JOIN）来代替子查询。示例：假设要检索所有没有订单记录的用户，可以使用以下查询来完成：SELECTcol1FROMcustomerinfoWHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)如果你使用连接（JOIN）来完成这个查询，速度会有所提高。特别是当salesinfo表中有CustomerID的索引时，性能会更好。查询如下：SELECTcol1FROMcustomerinfoLEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerIDWHEREsalesinfo.CustomerIDISNULLconnection(JOIN)..效率更高，因为MySQL不需要在内存中创建临时表来完成这个逻辑上的两步查询。④优化联合查询MySQL通过创建和填充临时表来执行联合查询。除非你真的想消除重复的行，否则建议使用unionall。原因是如果没有关键字all，MySQL会给临时表加上distinct选项，会造成对整个临时表数据的唯一性校验，这样做的消耗是相当大的。高效：SELECTCOL1,COL2,COL3FROMTABLEWHERECOL1=10UNIONALLSELECTCOL1,COL2,COL3FROMTABLEWHERECOL3='TEST';低效：SELECTCOL1、COL2、COL3FROMTABLEWHERECOL1=10UNIONSELECTCOL1、COL2、COL3FROMTABLEWHERECOL3='TEST'；inefficient:SELECTCOL1,COL2,COL3FROMTABLEWHERECOL1=10UNIONSELECTCOL1,COL2,COL3FROMTABLEWHERECOL3='TEST'事务如下：简单的SQL好用MySQL的QUERYCACHE。减少表锁定时间，尤其是对于使用MyISAM存储引擎的表。可以使用多核CPU。⑥使用truncate而不是delete删除整表记录时，使用delete语句的操作会记录在undoblock中，被删除的记录也会记录在binlog中。当确认需要删除整张表时，会产生大量的binlog，占用大量的undo数据块。这时候效率不是很高，消耗的资源也很多。改用truncate，不会记录任何可恢复的信息，也无法恢复数据。因此，使用truncate操作，资源占用极少，时间极快。另外，使用truncate回收表的水位，使自增字段的值归零。⑦使用合理的分页方式，提高分页效率使用合理的分页方式，提高分页效率对于演示文稿等分页需求，采用合适的分页方式可以提高分页效率。情况一：select*fromtwherethread_id=10000anddeleted=0orderbygmt_createasclimit0,15;上面的例子一次性根据过滤条件提取所有字段，排序返回。数据访问开销=索引IO+所有索引记录对应的表数据IO。所以这种写法执行效率越差，执行时间越长，尤其是表数据量大的时候。适用场景：适用于中间结果集较小（小于10000行）或查询条件复杂（指多个不同查询字段或多表连接）的情况。情况2：selectt.*from(selectidfromtwherethread_id=10000anddeleted=0orderbygmt_createasclimit0,15)a,twherea.id=t.id;上面的例子必须满足t表的主键是id列，并且有一个覆盖索引副键：(thread_id,deleted,gmt_create)。先用覆盖索引提取主键id，根据过滤条件进行排序，再进行join操作提取其他字段。数据访问开销=索引IO+索引分页结果对应的表数据IO（本例15行）。所以这种写法每次翻页消耗的资源和时间基本一样，就跟翻第一页一样。适用场景：适用于查询和排序字段（即where子句和orderby子句涉及的字段）有对应的覆盖索引，中间结果集较大的情况。建表优化①在表中建立索引，优先考虑where和orderby使用的字段。②尽量使用数字字段（如性别，男：1，女：2）。如果字段只包含数值信息，尽量不要设计成字符类型，这样会降低查询和连接的性能，增加存储开销。这是因为引擎在处理查询和连接时，会把字符串中的每个字符一个一个地进行比较，但是对于数字类型，只需要一个比较就可以了。③查询数据量大的表会导致查询慢。主要原因是扫描线太多。这时候可以使用程序按段、页查询，循环遍历，合并结果显示。查询100000到100050的数据，如下：SELECT*FROM(SELECTROW_NUMBER()OVER(ORDERBYIDASC)ASrowid,*FROMinfoTab)tWHEREt.rowid>100000ANDt.rowid<=100050④将char/nchar换成varchar/nvarchar。尽量使用varchar/nvarchar而不是char/nchar，因为一来变长字段的存储空间小，可以节省存储空间，二来对于查询来说，在比较小的字段中查找效率明显更高。不要以为NULL不需要空间，例如：char(100)类型，在创建字段时，空间是固定的，无论是否插入值（也包括NULL），都会占用空间100个字符，如果是varchar这样的变长字段，null不占空间。
