本文会提到52个SQL语句性能优化策略。1.优化查询，尽量避免全表扫描。首先，您应该考虑在where和orderby涉及的列上建立索引。2、尽量避免在where子句中判断字段的空值。NULL是建表时的默认值，但大多数时候应该使用NOTNULL，或者使用特殊的值，比如0、-1作为默认值。3.尽量避免在where子句中使用!=或<>运算符。MySQL仅对以下运算符使用索引：<、<=、=、>、>=、BETWEEN、IN，有时还有LIKE。4.尽量避免在where子句中使用or来连接条件，否则引擎会放弃使用索引而进行全表扫描。您可以使用UNION合并查询：selectidfromtwherenum=10unionallselectidfromtwherenum=20。5.慎用in和不使用in，否则会导致全表扫描。对于连续值，使用between而不是in：Selectidfromtwherenum介于1和3之间。6.Below查询也会导致全表扫描：selectidfromtwherenamelike'%abc%'或selectidfromtwherenamelike'%abc'为了提高效率，可以考虑全文检索。并从t中选择id，其中namelike'abc%'仅使用索引。7、如果where子句中使用了参数，也会造成全表扫描。8、尽量避免在where子句中对字段进行表达式操作，尽量避免在where子句中对字段进行函数操作。9.在很多情况下，使用exists代替in是一个不错的选择：selectnumfromawherenumin(selectnumfromb)。替换为以下语句：selectnumfromawhereexists(select1frombwherenum=a.num)。10、索引可以提高相应select的效率，但是也会降低insert和update的效率，因为insert或者update的时候可能会重建索引，所以如何建立索引需要慎重考虑，具体取决于具体情况。一张表的索引最好不要超过6个。如果过多，则应考虑是否有必要在一些不常用的列上建立索引。11、尽量避免更新聚簇索引数据列，因为聚簇索引数据列的顺序就是表记录的物理存储顺序。一旦该列的值发生变化，整个表记录的顺序就会随之调整，这会消耗相当大的资源。如果应用系统需要频繁更新聚簇索引数据列，则需要考虑是否将该索引构建为聚簇索引。12.尽量使用数字字段。如果只包含数字信息的字段尽量不要设计成字符类型，这样会降低查询和连接的性能，还会增加存储开销。13、尽量使用varchar/nvarchar而不是char/nchar，因为一来变长字段的存储空间小，可以节省存储空间，二来对于查询来说，在比较小的范围内查找效率领域显然更高。14、最好不要用“”返回全部：selectfromt，把“*”换成具体的字段列表，不要返回任何没有用到的字段。15、尽量避免向客户端返回大量数据。如果数据量太大，就要考虑对应的需求是否合理。16、使用表的别名（Alias）：在SQL语句中连接多个表时，请使用表的别名，并在每个Column前加上别名。这样可以减少解析时间，减少Column歧义引起的语法错误。17、使用“临时表”临时存储中间结果：简化SQL语句的一个重要方法是使用临时表临时存储中间结果，但临时表的好处远不止这些。临时结果临时存放在临时表中，后续查询会在tempdb中，这样可以避免程序中多次扫描主表，也大大减少程序执行时“共享锁”阻塞“更新锁”，减少阻塞和提高并发性能。18、部分SQL查询语句要加nolock。读和写会互相阻塞。为了提高并发性能，对于一些查询，可以加nolock，这样读的时候可以允许写，但是缺点是可能会读到未提交的脏数据。使用nolock有3个原则：用于“增删改查”的查询结果不能加Nolock；查询表频繁分页，慎用nolock；使用临时表还可以保存“数据伏笔”，起到类似Oracle的undotablespace的作用，使用临时表提高并发性能，不要使用nolock。19、常见的简化规则如下：表连接（JOIN）不要超过5个，考虑使用临时表或表变量来存储中间结果。少用子查询，不要嵌套视图太深。一般来说，最好不要嵌套超过2个视图。20、预先计算好需要查询的结果放在表中，查询的时候再选择。这是SQL7.0之前最重要的方法，比如医院住院费用的计算。21、使用OR的词可以分解成多个query，多个query可以通过UNION连接起来。它们的速度只与是否使用索引有关。如果查询需要使用联合索引，使用UNIONall执行效率更高。多个OR子句不使用索引，改写成UNION的形式，尽量匹配索引。一个关键问题是是否使用索引。22、在IN之后的值列表中，将出现次数最多的值放在最前面，出现次数最少的值放在最后，减少判断次数。23、尽量把数据处理放在服务器上，减少网络开销，比如使用存储过程。存储过程是经过编译、优化、组织成执行计划并存储在数据库中的SQL语句。是控制流语言的集合，速度当然快。重复执行的动态SQL可以使用临时存储过程，临时存储过程放在Tempdb中。24、当服务器内存足够时，配置线程数=最大连接数+5，这样可以最大化效率；否则，使用配置的线程数<最大连接数启用SQLSERVER的线程池来解决问题，如果还是数=最大连接数+5严重损害服务器的性能。25.查询关联和书写顺序：selecta.personMemberID,*fromchineseresumea,personmemberbwherepersonMemberID=b.referenceidanda.personMemberID='JCNPRH39681'(A=B,B='number')selecta.personMemberID,*fromchineseresumea,personmemberbwherea.personMemberID=b.referenceidanda.personMemberID='JCNPRH39681'andb.referenceid='JCNPRH39681'（A=B,B='number',A='number')selecta.personMemberID,*fromchineresumea,personmemberbwhereb.referenceid='JCNPRH39681'and.personMemberID='JCNPRH39681'(B='Number',A='Number')26、尝试用exists代替selectcount(1)来判断是否有记录。count函数只在对表中的所有行进行计数时使用，count(1)比count(*)效率更高。27、尽量用“>=”代替“>”。28、索引使用规范：索引的创建应结合应用考虑。建议大型OLTP表不要超过6个索引；尽可能使用索引字段作为查询条件，尤其是聚簇索引，必要时使用索引index_name强制指定索引；查询大表时避免表扫描，必要时考虑创建新索引；使用索引字段作为条件时，如果索引是联合索引，则必须使用索引中的第一个字段作为条件，以保证系统使用索引，否则索引不会被使用；注意索引的维护，定期重建索引，重新编译存储过程。29、以下SQL条件语句中的列有相应的索引，但是执行速度很慢：SELECT*FROMrecordWHEREsubstrING(card_no,1,4)='5378'(13秒)SELECT*FROMrecordWHEREamount/30<1000(11秒)SELECT*FROMrecordWHEREconvert(char(10),date,112)='19991201'(10秒)分析：WHERE子句中任何对列的操作结果都是SQL运行时逐列计算的，所以一定不是A如果不使用列上的索引，则不会执行表搜索。如果这些结果在查询编译时可用，它们可以被SQL优化器优化，使用索引，避免表搜索，因此重写SQL如下：SELECT*FROMrecordWHEREcard_nolike'5378%'(<1second)SELECT*FROMrecordWHEREamount<1000*30(<1second)SELECT*FROMrecordWHEREdate='1999/12/01'(<1second)30.当有批量insert或update时，使用batchinsert或batchupdate，千万不要一个一条记录去update.31、在所有的存储过程中，如果可以用SQL语句，我是绝对不会用循环来实现的。比如：列出上个月的每一天，我会用connectby递归查询，绝对不会用从上个月的第一天到最后一天的循环。32、选择最高效的表名顺序（只在基于规则的优化器中有效）：Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句写在最后一个表（驱动表）将首先被处理。如果FROM子句中有多个表，则必须选择记录数最少的表作为基表。如果查询连接的表超过3个，则需要选择交集表作为基表。交集表是指被其他表引用的表。33、要提高GROUPBY语句的效率，可以在GROUPBY之前过滤掉不需要的记录。以下两个查询返回相同的结果，但第二个查询要快得多。低效：SELECTJOB,AVG(SAL)FROMEMPGROUPBYJOBHAVINGJOB='PRESIDENT'ORJOB='MANAGER'高效：SELECTJOB,AVG(SAL)FROMEMPWHEREJOB='PRESIDENT'ORJOB='MANAGER'GROUPBYJOB34，SQL语句大写，因为Oracle总是先解析SQL语句，将小写字母转换为大写字母，然后执行。35、别名的使用，别名是大型数据库的应用技巧，即在查询中将表名和列名用一个字母作为别名，查询速度比建连接表快1.5倍。36.避免死锁，在你的存储过程和触发器中总是以相同的顺序访问同一个表；尽可能缩短事务，尽可能减少事务涉及的数据量；从不等待交易中的用户输入。37.避免使用临时表。除非必要，尽量避免使用临时表。相反，您可以改用表变量；大多数时候（99%），表变量驻留在内存中，所以比临时表快，临时表驻留在TempDb数据库中，所以对临时表的操作需要跨库通信，速度自然慢.38、最好不要使用触发器：触发触发器和执行触发器事件本身就是一个资源密集型过程；如果可以使用约束实现，尽量不要使用触发器；不要对不同的触发事件（插入、更新和删除）使用同一个触发器；不要在触发器中使用事务代码。39、索引创建规则：表的主键和外键必须有索引；超过300条数据的表要有索引；经常与其他表连接的表应该在连接字段上有一个索引；经常出现在Where子句中的字段，尤其是大表的字段，应该被索引；索引应该建立在选择性高的字段上；索引应该建立在小字段上，不要为大文本字段甚至超长字段建立索引；复合索引的建立需要仔细分析，尽量改用单字段索引；正确选择复合索引中的主列字段，一般是选择性比较好的字段；复合索引的几个字段是否经常以AND的形式同时出现在Where子句中？是否很少或没有单字段查询？如果是这样，你可以建立一个复合索引；否则，考虑单字段索引；如果复合索引包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；如果复合索引包含的字段超过3个，则慎重考虑其必要性，考虑减少复合字段；如果这些字段上同时存在单字段索引和复合索引，一般可以删除复合索引；频繁地为执行数据操作的表不要创建过多的索引；删除无用的索引，避免对执行计划产生负面影响；在表上创建的每个索引都会增加存储开销，索引也会增加处理插入、删除和更新操作的开销。另外，复合索引太多，一般在单字段索引的情况下是没用的；相反，它们还会降低数据增删改查的性能，尤其是对于频繁更新的表，负面影响就更大big了。尽量不要索引数据库中包含大量重复值的字段。40.MySQL查询优化总结：使用慢查询日志来发现慢查询，使用执行计划来判断查询是否正常运行，并且经常测试你的查询是否以最佳状态运行。性能会随时间变化，避免对整张表使用count(*)，它可能会锁住整张表，使查询保持一致以便后续类似的查询可以使用查询缓存，适当时使用GROUPBY而不是DISTINCT，使用索引WHERE、GROUPBY和ORDERBY子句中的列，保持索引简单，并且不要在多个索引中包含相同的列。有时MySQL会使用错误的索引。在这种情况下，使用USEINDEX并检查使用SQL_MODE=STRICT的问题。对于少于5条记录的索引字段，使用LIMIT而不是UNION中的OR。避免更新前SELECT，使用INSERTONDUPLICATEKEY或INSERTIGNORE，不要使用UPDATE来实现，不要使用MAX，使用索引字段和ORDERBY子句，LIMITM,N在某些情况下实际上会减慢查询速度，使用有节制地，在WHERE子句中使用UNION而不是子查询，重启MySQL后，记得预热数据库以确保数据在内存中并快速查询，考虑持久连接，而不是多连接，以减少开销。基准查询，包括使用服务器上的负载，有时一个简单的查询会影响其他查询，当服务器上的负载增加时，使用SHOWPROCESSLIST可以看到缓慢和有问题的查询，在开发环境中生成的镜像数据中测试所有可疑查询。41、MySQL备份过程：从副复制服务器备份；备份时停止复制，避免数据依赖和外键约束不一致；完全停止MySQL并从数据库文件备份；如果使用MySQLdump进行备份，请同时备份二进制日志文件——确保复制不中断；不要相信LVM快照，它很可能会产生数据不一致，以后会给你带来麻烦；为了更容易的单表恢复，以表为单位导出数据——如果数据与其他表隔离。使用mysqldump时请使用--opt；备份前检查和优化表；为了更快地导入，在导入时暂时禁用外键约束。;为了更快地导入，在导入期间暂时禁用唯一性检查；每次备份后计算数据库、表和索引的大小，以更好地监控数据大小的增长；监控复制实例的错误和延迟；定期执行备份。42.查询缓冲区不会自动处理空格。因此，在编写SQL语句时，应尽量减少空格的使用，尤其是SQL开头和末尾的空格（因为查询缓冲区不会自动截取首末空格）。43、会员以mid作为分表标准方便吗？在一般的业务需求中，基本都是以用户名作为查询依据。一般情况下，应该使用username作为hash取模来分表。至于分表，MySQL的分区函数就是这样做的，对代码是透明的；在代码层面实现似乎不太合理。44、我们应该为数据库中的每个表设置一个ID作为其主键，最好是INT类型（推荐使用UNSIGNED），并设置自动递增的AUTO_INCREMENT标志。45.在所有存储过程和触发器的开头设置SETNOCOUNTON，在结束时设置SETNOCOUNTOFF。存储过程和触发器的每条语句执行完后，不需要向客户端发送DONE_IN_PROC消息。46.MySQL查询可以启用高速查询缓存。这是提高数据库性能的有效MySQL优化方法之一。当多次执行同一个查询时，从缓存中获取数据并直接从数据库中返回要快得多。47、EXPLAINSELECT查询用于跟踪查看效果：使用EXPLAIN关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮助您分析查询语句或表结构中的性能瓶颈。EXPLAIN的查询结果也会告诉你你的索引主键是怎么用的，你的数据表是怎么查找排序的。48.只有一行数据时使用LIMIT1：当你查询表的时候，你已经知道只会有一个结果，但是因为你可能需要取游标，或者你可能会检查返回的记录数.在这种情况下，添加LIMIT1可以提高性能。这样，MySQL数据库引擎在找到一条数据后就会停止搜索，而不是继续搜索下一条与该记录匹配的数据。49、为表选择合适的存储引擎：myisam：应用主要是读和插入，只有少量的更新和删除，对事务的完整性和并发性要求不是很高。InnoDB：事务处理，并发条件下需要的数据一致性。除了插入和查询之外，还包括许多更新和删除。（InnoDB有效减少了删除和更新引起的锁）。对于支持事务的InnoDB表，影响速度的主要原因是AUTOCOMMIT的默认设置是开启的，程序没有显式调用BEGIN启动事务，导致每次插入的项都自动提交，严重影响速度。可以在执行SQL之前调用begin，多个SQL组成一个东西（即使开启了autocommit），这样会大大提高性能。50、优化表的数据类型，选择合适的数据类型：原则：通常越小越好，简单就好，所有字段必须有默认值，尽量避免null。例如：在设计数??据库表时，尽可能使用较小的整数类型，以占用较小的磁盘空间。（mediumint比int更适合）比如时间字段：datetime和timestamp，datetime占8个字节，timestamp占4个字节，只用了一半，timestamp表示的范围是1970-2037，适合对于更新时间MySQL可以很好地支持大量数据的访问，但一般来说，数据库中的表越小，对其执行查询的速度就越快。因此，在创建表格时，为了获得更好的性能，我们可以将表格中字段的宽度设置得尽可能小。例如：在定义邮政编码字段时，如果设置为CHAR(255)，显然会给数据库增加不必要的空间。即使使用VARCHAR类型也是多余的，因为CHAR(6)可以很好地完成这项工作。同样，如果可能的话，我们应该使用MEDIUMINT而不是BIGIN来定义整数字段，并且我们应该尽量将字段设置为NOTNULL，这样数据库在以后执行查询时就不需要比较NULL值了。对于一些文本字段，例如“省”或“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值数据处理，数值数据的处理速度比文本类型要快得多。这样，我们就可以提高数据库的性能。51.String数据类型：char、varchar、text选择区别。52、任何对列的操作都会导致表扫描，包括数据库函数、计算表达式等，查询时尽量将操作移到等号右边。
