《SQL 优化极简法则与优化方法.docx》由会员分享，可在线阅读，更多相关《SQL 优化极简法则与优化方法.docx（11页珍藏版）》请在三一办公上搜索。
1、目录法则一：只返回需要的结果法则二：确保查询使用了正确的索引法则三：尽量避免使用子查询法则四：不要使用OFFSET实现分页法则五：了解SQ1.子句的逻辑执行顺序总结方法SQ1.作为关系型数据库的标准语言，是IT从业人员必不可少的技能之一。SQ1.本身并不难学，编写查询语句也很容易，但是想要编写出能够高效运行的查询语句却有一定的难度。查询优化是一个复杂的工程,涉及从硬件到参数配置、不同数据库的解析器、优化器实现、SQ1.语句的执行顺序、索引以及统计信息的采集等，甚至应用程序和系统的整体架构。本文介绍几个关键法则，可以帮助我们编写高效的SQ1.查询；尤其是对于初学者而言，这些法则至少可以避免我们写
2、出性能很差的查询语句。以下法则适用于各种关系型数据库，包括但不限于:MySQ1.OracleSQ1.ServerPostgreSQ1.以及SQ1.ite等。法则一：只返回需要的结果一定要为查询语句指定WHERE条件，过滤掉不需要的数据行。通常来说，O1.TP系统每次只需要从大量数据中返回很少的几条记录；指定查询条件可以帮助我们通过索引返回结果，而不是全表扫描。绝大多数情况下使用索引时的性能更好，因为索引(B-树、B+树、B*树)执行的是二进制搜索，具有对数时间复万(1003)条记录只需要3层B-树即可完成索引。通过索引查找数据时需要读取3次索引数据(每次磁盘IO读取整个分支节点)，加上1次磁盘
3、IO读取数据即可得到查询结果。相反，如果采用全表扫描，需要执行的磁盘IO次数可能高出几个数量级。当数据量增加到1亿(1004)时，B-树索引只需要再增加1次索引IO即可；而全表扫描则需要再增加几个数量级的10。同理，我们应该避免使用SE1.ECT*FROM,因为它表示查询表中的所有字段。这种写法通常导致数据库需要读取更多的数据，同时网络也需要传输更多的数据，从而导致性能的下降。法则二：确保查询使用了正确的索引如果缺少合适的索引，即使指定了查询条件也不会通过索引查找数据。因此,我们首先需要确保创建了相应的索引。一般来说，以下字段需要创建索引：经常出现在WHERE条件中的字段建立索引可以避免全表扫
4、描；将ORDERBY排序的字段加入到索引中，可以避免额外的排序操作；多表连接查询的关联字段建立索引，可以提高连接查询的性能；将GRoUPBY分组操作字段加入到索引中，可以利用索引完成分组。即使创建了合适的索引，如果SQ1.语句写的有问题，数据库也不会使用索引。导致索引失效的常见问题包括：在WHERE子句中对索引字段进行表达式运算或者使用函数都会导致索引失效，这种情况还包括字段的数据类型不匹配，例如字符串和整数进行比较；使用1.IKE匹配时，如果通配符出现在左侧无法使用索引。对于大型文本数据的模糊匹配，应该考虑数据库提供的全文检索功能，甚至专门的全文搜索引擎(Elasticsearch等)；如果
5、WHERE条件中的字段上创建了索引，尽量设置为NOTNU1.1.；不是所有数据库使用1SNOTNU1.1.判断时都可以利用索引。执行计划(executionplan,也叫查询计划或者解释计划)是数据库执行SQ1.语句的具体步骤，例如通过索引还是全表扫描访问表中的数据，连接查询的实现方式和连接的顺序等。如果SQ1.语句性能不够理想，我们首先应该查看它的执行计划，通过执行计划(EXP1.AlN)确保查询使用了正确的索引。法则三：尽量避免使用子查询以MySQ1.为例，以下查询返回月薪大于部门平均月薪的员工信息：ExplainanalyzeSE1.ECTemp_id,emp_nameFROMemplo
6、yeeeWHEREsalary(SE1.ECTAVG(salary)FROMemployeeWHEREdept_id=e.dept_id);Filter:(e.salary(select#2)(cost=2.75rows=25)(actualtime=0.232.4.401rows=6loops=1)Tablescanone(cost=2.75rows=25)(actualtime=0.099.0.190rows=251oops=1)Select#2(subqueryincondition;dependent)Aggregatezavg(employee.salary)(actualtime=
7、0.147.0.149rows=lloops=25)Indexlookuponemployeeusingidx_emp_dept(dept_id=e.dept_id)(cost=1.12rows=5)(actualtime=0.068.0.104rows=7loops=25)从执行计划可以看出，MySQ1.中采用的是类似Nested1.oopJoin实现方式；子查询循环了25次，而实际上可以通过一次扫描计算并缓存每个部门的平均月薪。以下语句将该子查询替换为等价的JOIN语句，实现了子查询的展开(SubqueryUnnest)：ExplainanalyzeSE1.ECTe.emp_id,e.em
8、p_nameFROMemployeeeJoIN(SE1.ECTdept_id,AVG(SaIary)ASdept_aVerageFROMemployeeGROUPBYdeptJd)tONe.dept_id=t.dept_idWHEREe.salaryt.dept-average;Nestedloopinnerjoin(actualtime=0.722.2.354rows=61oops=l)Tablescanone(cost=2.75rows=25)(actualtime=0.096.0.205rows=251oops=l)Filter:(e.salaryt.dept_average)(actu
9、altime=0.068.0.076rows=01oops=25)Indexlookupontusing(dept_id=e.dept_id)(actualtime=0.011.0.015rows=lloops=25)Materialize(actualtime=0.048.0.057rows=lloops=25)Groupaggregate:avg(employee.salary)(actualtime=0.228.0.510rows=51oops=l)Indexscanonemployeeusingidx_emp_dept(cost=2.75rows=25)(actualtime=0.18
10、1.0.348rows=251oops=1)改写之后的查询利用了物化(MaterialiZation)技术，将子查询的结果生成一个内存临时表；然后与employee表进行连接。通过实际执行时间可以看出这种方式更快。以上示例在Oraele和SQ1.SerVer中会自动执行子查询展开，两种写法效果相同；在PostgreSQ1.中与MySQ1.类似，第一个语句使用Nested1.oopJoin,改写为Jc)IN之后使用HaShJOin实现，性能更好。另外，对于IN和EXISTS子查询也可以得出类似的结论。由于不同数据库的优化器能力有所差异，我们应该尽量避免使用子查询，考虑使用JolN进行重写。法则四
11、：不要使用OFFSET实现分页分页查询的原理就是先跳过指定的行数，再返回TOP-N记录。分页查询的示意图如下：分页查询数据库一般支持FETCH/1.IMIT以及OFFSET实现Top-N排行榜和分页查询。当表中的数据量很大时，这种方式的分页查询可能会导致性能问题。以MySQ1.为例：-MySQ1.SE1.ECT*FROMlarge_tableORDERBYid1.imit100ffsetn;以上查询随着OFFSET的增加，速度会越来越慢；因为即使我们只需要返回10条记录，数据库仍然需要访问并且过滤掉N(比如1000000)行记录，即使通过索引也会涉及不必要的扫描操作。对于以上分页查询，更好的方
12、法是记住上一次获取到的最大id,然后在下一次查询中作为条件传入：-MySQ1.SE1.ECT*FROMlarge_tableWHEREidlastJdORDERBYid1.IMIT10;如果id字段上存在索引，这种分页查询的方式可以基本不受数据量的影响。法则五：了解SQ1.子句的逻辑执行顺序以下是SQ1.中各个子句的语法顺序，前面括号内的数字代表了它们的逻辑执行顺序：(6)SE1.ECTDISTlNCTA1.1.col1,co12,agg_func(col3)ASalias(l)FROMtlJOINt2(2)ON(join_conditions)(3)WHEREwhere_conditions
13、(4)GR0UPBYcoll,co!2(5)HAVINGhaving_condition(7)UNIONA1.1.(8)0RDERBYcollASC,col2DESC(9)0FFSETmRoWSFETCHNEXTnUm_rOWSROWSoN1.Y;也就是说，SQ1.并不是按照编写顺序先执行SE1.ECT,然后再执行FROM子句。从逻辑上讲，SQ1.语句的执行顺序如下：首先，FROM和JOIN是SQ1.语句执行的第一步。它们的逻辑结果是一个笛卡尔积，决定了接下来要操作的数据集。注意逻辑执行顺序并不代表物理执行顺序，实际上数据库在获取表中的数据之前会使用ON和WHERE过滤条件进行优化访问；其次，
14、应用ON条件对上一步的结果进行过滤并生成新的数据集；然后，执行WHERE子句对上一步的数据集再次进行过滤。WHERE和ON大多数情况下的效果相同，但是外连接查询有所区别，我们将会在下文给出示例；接着，基于GROUPBY子句指定的表达式进行分组；同时，对于每个分组计算聚合函数agg_func的结果。经过GROUPBY处理之后，数据集的结构就发生了变化，只保留了分组字段和聚合函数的结果；如果存在GROUPBY子句，可以利用HAVING针对分组后的结果进一步进行过滤，通常是针对聚合函数的结果进行过滤；接下来，SE1.ECT可以指定要返回的列；如果指定了DISTINeT关键字，需要对结果集进行去重操作
15、。另外还会为指定了AS的字段生成别名；如果还有集合操作符(UNION、INTERSECTEXCEPT)和其他的SE1.ECT语句，执行该查询并且合并两个结果集。对于集合操作中的多个SE1.ECT语句，数据库通常可以支持并发执行；然后，应用ORDERBY子句对结果进行排序。如果存在GROUPBY子句或者DISTlNCT关键字，只能使用分组字段和聚合函数进行排序；否则，可以使用FROM和JOIN表中的任何字段排序；最后，OFFSET和FETCH(1.IMITTOP)限定了最终返回的行数。了解SQ1.逻辑执行顺序可以帮助我们进行SQ1.优化。例如WHERE子句在HAVING子句之前执行，因此我们应该
16、尽量使用WHERE进行数据过滤，避免无谓的操作；除非业务需要针对聚合函数的结果进行过滤。除此之外，理解SQ1.的逻辑执行顺序还可以帮助我们避免一些常见的错误，例如以下语句：-错误示例SE1.ECTemp_nameASempnameFROMemployeeWHEREemPname=张飞；该语句的错误在于WHERE条件中引用了列别名；从上面的逻辑顺序可以看出，执行WHERE条件时还没有执行SE1.ECT子句，也就没有生成字段的别名。另外一个需要注意的操作就是GRoUPBY,例如：-GROUPBY错误示例SE1.ECTdeptJd,emp_name,AVG(salary)FROMemployeeGR
17、OUPBYdeptJd;由于经过GRoUPBY处理之后结果集只保留了分组字段和聚合函数的结果，示例中的emp_name字段已经不存在；从业务逻辑上来说，按照部门分组统计之后再显示某个员工的姓名没有意义。如果需要同时显示员工信息和所在部门的汇总，可以使用窗口函数。如果使用了GRoUPBY分组，之后的SE1.ECT、ORDERBY等只能引用分组字段或者聚合函数；否则，可以引用FROM和JOIN表中的任何字段。还有一些逻辑问题可能不会直接导致查询出错，但是会返回不正确的结果；例如外连接查询中的ON和WHERE条件。以下是一个左外连接查询的示例：SE1.ECTe.emp_name,d.dept_nam
18、eFROMemployeee1.EFTJOINdepartmentdON(e.dept_id=d.dept_id)WHEREe.emp_name=，张飞：emp_namedept_name张飞行政管理SE1.ECTe.emp_name,d.dept_nameFROMemployeee1.EFTJolNdePartmentde)N(e.dept_id=d.dept_idANDe.emp_name=张飞)；emp_namedept_name刘备NU1.1.关羽NU1.1.张飞行政管理部诸葛亮NU1.1.第一个查询在ON子句中指定了连接的条件，同时通过WHERE子句找出了“张飞”的信息。第二个查询将
19、所有的过滤条件都放在ON子句中，结果返回了所有的员工信息。这是因为左外连接会返回左表中的全部数据，即使ON子句中指定了员工姓名也不会生效;而WHERE条件在逻辑上是对连接操作之后的结果进行过滤。总结SQ1.优化本质上是了解优化器的的工作原理，并且为此创建合适的索引和正确的语句；同时，当优化器不够智能的时候，手动让它智能。SQ1.优化方法一、查询SQ1.尽量不要使用SeleCt*,而是具体字段1、反例SE1.ECT*FROMuser2、正例SE1.ECTid,username,telFROMuser3、理由节省资源、减少网络开销。可能用到覆盖索引，减少回表，提高查询效率。注意：为节省时间，下面的
20、样例字段都用*代替了。二、避免在where子句中使用or来连接条件1反例SE1.ECT*FROMuserWHEREid=1ORsalary=50002、正例(1)使用UnionallSE1.ECT*FROMuserWHEREid=1UnionallSE1.ECT*FROMuserWHEREsalary=5000(2)分开两条SQ1.写SE1.ECT*FROMuserWHEREid=1SE1.ECT*FRMuserWHEREsalary=50003、理由使用Or可能会使索引失效，从而全表扫描；对于Or没有索引的SaIary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表
21、扫描；也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定；虽然mysql是有优化器的，出于效率与成本考虑，遇到。r条件，索引还是可能失效的；三、尽量使用数值替代字符串类型1、正例主键(id)：Primarykey优先使用数值类型int,tinyint性别(SeX)：O代表去，1代表男；数据库没有布尔类型，mysql推荐使用tinyint2、理由因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；而对于数字型而言只需要比较一次就够了；字符会降低查询和连接的性能，并会增加存储开销。四、使用varchar代替char1、反例addresschar(1
22、00)DEFAU1.TNU1.1.COMMENT也址2、正例addressVarchar(100)DEFAUErNU1.1.COMMENT地址3、理由varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；Char按声明大小存储，不足补空格；其次对于查询来说，在一个相对较小的字段内搜索，效率更高；五、技术延伸，char与varchar2的区别？1Char的长度是固定的，而VarChar2的长度是可以变化的。比如,存储字符串“101”，MTchar(10),表示你存储的字符将占10个字节(包括7个空字符)，在数据库中它是以空格占位的，而同样的VarChar2(10)则只占用3
23、个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储。2、char的效率比varchar2的效率稍高。3、何时用char,何时用varchar2?char和varchar2是一对矛盾的统一体，两者是互补的关系，varchar2比char节省空间，在效率上比char会稍微差一点，既想获取效率，就必须牺牲一点空问，这就是我们在数据库设计上常说的“以空间换效率:varchar2虽然比Char节省空间，但是假如一个VarChar2列经常被修改，而且每次被修改的数据的长度不同，这会引起“行迁移”现象，而这造成多余的I/O,是数据库设计中要尽力避免的，这种情况下用Char代替VarCha
24、r2会更好一些。char中还会自动补齐空格，因为你insert到一个char字段自动补充了空格的,但是SeIeCt后空格没有删除，因此ChaI类型查询的时候一定要记得使用trim,这是写本文章的原因。如果开发人员细化使用rpad()技巧将绑定变量转换为某种能与Char字段相比较的类型(当然，与截断trim数据库列相比，填充绑定变量的做法更好一些，因为对列应用函数trim很容易导致无法使用该列上现有的索引),可能必须考虑到经过一段时间后列长度的变化。如果字段的大小有变化，应用就会受到影响，因为它必须修改字段宽度。正是因为以上原因，定宽的存储空间可能导致表和相关索引比平常大出许多，还伴随着绑定变量
25、问题，所以无论什么场合都要避免使用Char类型。六、Where中使用默认值代替null1、反例SE1.ECT*FROMuserWHEREageISNOTNU1.1.2、正例SE1.ECT*FROMuserWHEREage03、理由并不是说使用了isnull或者isnotnull就会不走索引了，这个跟mysql版本以及查询成本都有关；如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件!=,isnull,isnotnull经常被认为让索引失效；其实是因为一般情况下，查询的成本高，优化器自动放弃索引的；如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也
26、相对清晰一点；七、避免在Where子句中使用!二或o操作符1、反例SE1.ECT*FROMuserWHEREsalary!=5(X)0SE1.ECT*FROMuserWHEREsalary50002、理由使用!=和很可能会让索引失效应尽量避免在Where子句中使用!二或。操作符，否则引擎将放弃使用索引而进行全表扫描实现业务优先，实在没办法，就只能使用，并不是不能使用八、innerjoinleftjoinrightjoin,优先使用innerjoin三种连接如果结果相同，优先使用innerjoin,如果使用leftjoin左边表尽量小。innerjoin内连接，只保留两张表中完全匹配的结果集；l
27、eftjoin会返回左表所有的行，即使在右表中没有匹配的记录；rightjoin会返回右表所有的行，即使在左表中没有匹配的记录；为什么？如果innerjoin是等值连接，返回的行数比较少，所以性能相对会好一点；使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；九、提高groupby语句的效率1、反例先分组，再过滤selectjob,avg(salary)fromemployeegroupbyjobhavingjob=develop,orjob=test,;2、正例先过滤，后分
28、组selectjob,avg(salary)fromemployeewherejob=,develop,orjob=,test,groupbyjob;3、理由可以在执行到该语句前，把不需要的记录过滤掉十、清空表时优先使用truncatetruncatetable在功能上与不带where子句的delete语句相同：二者均删除表中的全部行。但truncatetable比delete速度快，且使用的系统和事务日志资源少。delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncatetable通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。truncat
29、etable删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用DE1.ETEo如果要删除表定义及其数据，请使用droptable语句。对于由foreignkey约束引用的表，不能使用truncatetable,而应使用不带Where子句的DE1.ETE语句。由于truncatetable不记录在日志中，所以它不能激活触发器。truncatetable不能用于参与了索引视图的表。H、操作delete或者UPdate语句，加个Iimit或者循环分批次删除1、降低写错SQ1.的代价清空表数据可不是小事情，一个手抖全没了，删库跑路
30、？如果加limit,删错也只是丢失部分数据，可以通过binlog日志快速恢复的。2、SQ1.效率很可能更高SQ1.中加了Iimit1.如果第一条就命中目标return,没有Iimit的话，还会继续执行扫描表。3、避免长事务delete执行时,如果age加了索引，MySQ1.会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。4、数据量大的话，容易把CPU打满如果你删除数据量很大时，不加Iimit限制一下记录数，容易把CPU打满，导致越删越慢。5、锁表一次性删除太多数据，可能造成锁表，会有IOCkWaittimeOUteXCeed的错误，所以建议分
31、批操作。十二、UNloN操作符UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNloN。如：selectusername,telfromuserunionSelectdepartmentnamefromdepartment这个SQ1.在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。推荐方案：采用UNlONA1.1.操作符替代UNlON,因为UNIoNA1.1.操作只是简单的将两个结果合并后
32、就返回。十三、批量插入性能提升1、多条提交INSERTINTOUSer(id,username)VA1.UES(l,编程)；INSERTlNTOUSer(id,username)VA1.UES(2,妲己)；2、批量提交INSERTlNTOUSer(id,username)VA1.UES(l,编程)(2：妲己,)；3、理由默认新增SQ1.有事务控制，导致每条都需要事务开启和事务提交，而批量处理是一次事务开启和提交，效率提升明显，达到一定量级，效果显著，平时看不出来十四、表连接不宜太多，索引不宜太多，一般5个以内1、表连接不宜太多，一般5个以内关联的表个数越多，编译的时间和开销也就越大每次关联内存
33、中都生成一个临时表应该把连接表拆开成较小的几个执行，可读性更高如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了阿里规范中，建议多表联查三张表以下2、索引不宜太多，一般5个以内索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；索引表的数据是排序的，排序也是要花时间的；insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；十五、避免在索引列上使用内置函数1
34、、反例SE1.ECT*FROMuserWHEREDATE_ADD(birthday,INTERVA1.7DAY)=NOW0;2、正例SE1.ECT*FROMuserWHEREbirthday=DATE_ADD(NOW(),lNTERVA1.7DAY)；3、理由使用索引列上内置函数，索引失效。十六、组合索引排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。createindexIDX_USERNAME_TE1.onuser(deptid,position,createtime);selectusername,telfromuserwheredepti
35、d=1andposition=ava开发orderbydeptid,position,Createtimedesc;实际上只是善询出符合deptid=1andposition=,java开发，条件的记录并按createtime降序排序，但写成Orderbycreatetimedesc性能较差。十七、复合索引最左特性1、创建复合索引A1.TERTAB1.EemployeeADDlNDEXidx_name_salary（name,salary）2、满足复合索引的最左特性，哪怕只居部分，复合索引生效SE1.ECT.FROMemployeeWHERENAME=编程3、没有出现左边的字段，则不满足最左特
36、性，索引失效SE1.ECT*FRMemployeeWHEREsalary=50004、复合索引全使用，按左侧顺序出现name,salary,索引生效SE1.ECT*FROMemployeeWHERENAME=编程ANDsalary=50005、虽然违背了最左特性，但MySQ1.执行SQ1.时会进行优化，底层进行颠倒优化SE1.ECT*FROMemployeeWHEREsalary=5000ANDNAME=编程6、理由复合索引也称为联合索引，当我们创建一个联合索引的时候，如（kl,k2,k3）,相当于创建了（kl）、（kl,k2）和（kl,k2,k3）三个索引，这就是最左匹配原则。联合索引不满足
37、最左原则，索引一般会失效。十八、优化Iike语句模糊查询，程序员最喜欢的就是使用like,但是like很可能让你的索引失效。1反例SeleCt*fromcityswherenamelike%大连，（不使用索引）SeleCt*fromcityswherenamelike%大连（不使用索引）2、正例SeleCt*fromcityswherenamelike大连（使用索引）。3、理由首先尽量避免模糊查询，如果必须使用，不采用全模糊查询，也应尽量采用右模糊查询，即like；.%，是会使用索引的；左模糊like，%.J无法直接使用索引，但可以利用reverse+functionindex的形式，变化成l
38、ike；.%，；全模糊查询是无法优化的，一定要使用的话建议使用搜索引擎。十九、使用explain分析你SQ1.执行计划1、typesystem：表仅有一行，基本用不到；const：表最多一行数据配合，主键查询时触发较多；eqef：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了ConSt类型；ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；range：只检索给定范围的行，使用一个索引来选择行。当使用=、=、V=、ISNU1.1.、.BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range；index：该联接类型与A1.1
39、.相同，除了只有索引树被扫描。这通常比A1.1.快，因为索引文件通常比数据文件小；all：全表扫描；性能排名：systemconsteq-refrefrangeindexallo实际Sql优化中，最后达到ref或range级别。2、EXtra常用关键字Usingindex：只从索引树中获取信息，而不需要回表查询；Usingwhere:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Usingwhere并且表联接类型为A1.1.或index,查询可能会有一些错误。需要回表查询。Usingtemporary：mysql常建一个临时表来容
40、纳结果，典型情况如查询包含可以按不同情况列出列前GROUPBY和ORDERBY子句时；二十、一些其它优化方式1、设计表的时候，所有表和字段都添加相应的注释。2、SQ1.书写格式，关键字大小保持一致，使用缩进。3、修改或删除重要数据前，要先备份。4、很多时候用exists代替in是一个好的选择。5、Where后面的字段，留意其数据类型的隐式转换。未使用索引SE1.ECT*FROMuserWHERENAME=I10因为不加单引号时，是字符串跟数字的比较，它们类型不匹配；MySQ1.会做隐式的类型转换，把它们转换为数值类型再做比较；6、尽量把所有列定义为NoTNU1.1.NOTNU1.1.列更节省空
41、间，NU1.1.列需要一个额外字节作为判断是否为NU1.1.的标志位。NU1.1.列需要注意空指针问题，NU1.1.列在计算和比较的时候,需要注意空指针问题。7、伪删除设计8、数据库和表的字符集尽量统一使用UTF8可以避免乱码问题；可以避免，不同字符集比较转换，导致的索引失效问题。9、selectcount(*)fromtable；这样不带任何条件的COUnt会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。10、避免在where中对字段进行表达式操作SQ1.解析时，如果字段相关的是表达式就进行全表扫描；字段干净无表达式，索引生效。11、关于临时表避免频繁创建和删除临时表，以减少系统表资源
42、的消耗；在新建临时表时，如果一次性插入数据量很大，那么可以使用SeleCtintO代替Createtable,避免造成大量log；如果数据量不大，为了缓和系统表的资源，应先Createtable,然后insert；如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除。先truncatetable,然后droptable,这样可以避免系统表的较长时间锁定。12、索引不适合建在有大量重复数据的字段上，比如性别，排序字段应创建索引13、去重distinct过滤字段要少带distinct的语句占用cpu时间高于不带distinct的语句当查询很多字段时，如果使用distinct,数据库引擎就会对数据进行比较，过滤掉重复数据然而这个比较、过滤的过程会占用系统资源，如CPU时间14、尽量避免大事务操作，提高系统并发能力15、所有表必须使用InnOdb存储引擎Innodb支持事务，支持行级锁，更好的恢复性，高并发下性能更好，所以呢，没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用InnOdb存储引擎。16、尽量避免使用游标因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
