图片来自Pexels有些优化策略需要你有一定的SQL实践才能明白其中的道理。当然你也可以按照这些优化策略进行练习，可以加深理解和记忆。01优化查询，应尽量避免全表扫描。首先，您应该考虑在WHERE和ORDERBY涉及的列上建立索引。02尽量避免在WHERE子句中判断字段的NULL值。NULL是建表时的默认值，但大多数时候应该使用NOTNULL，或者使用特殊的值，比如0、-1作为默认值。03尽量避免在WHERE子句中使用!=或<>运算符。MySQL仅对以下运算符使用索引：<、<=、=、>、>=、BETWEEN、IN，有时还有LIKE。04尽量避免在WHERE子句中使用OR来连接条件，否则引擎会放弃使用索引而进行全表扫描。可以使用UNION组合查询：selectidfromtwherenum=10unionallselectidfromtwherenum=2005IN和NOTIN也要慎用，否则会造成全表扫描。对于连续值，使用BETWEEN而不是IN：selectidfromtwherenumbetween1and306以下查询也会导致全表扫描：selectidfromtwherenamelike'%abc%'或selectidfromtwherenamelike'%abc'为了提高效率，可以考虑全文搜索。并从t中选择id，其中namelike'abc%'仅使用索引。07如果WHERE子句中使用了参数，也会造成全表扫描。08尽量避免对WHERE子句中的字段进行表达式操作，尽量避免对WHERE子句中的字段进行函数操作。09很多时候使用EXISTS代替IN是一个不错的选择：selectnumfromawherenumin(selectnumfromb)替换为如下语句：selectnumfromawhereexists(select1frombwherenum=a.num)10个索引固然可以提高对应SELECT的效率，但是也降低了INSERT和UPDATE的效率。因为在INSERT或者UPDATE的时候可能会重建索引，所以如何建立索引需要慎重考虑，具体要看具体情况。一张表中的索引最好不要超过6个。如果过多，则应考虑是否有必要在一些不常用的列上建立索引。11尽量避免更新聚簇索引数据列，因为聚簇索引数据列的顺序就是表记录的物理存储顺序。一旦该列的值发生变化，整个表记录的顺序就会随之调整，这会消耗相当大的资源。如果应用系统需要频繁更新聚簇索引数据列，那么就需要考虑是否将索引构建为聚簇索引。12尝试使用数字字段。如果只包含数字信息的字段尽量不要设计成字符类型，这样会降低查询和连接的性能，还会增加存储开销。13尽量使用varchar、nvarchar代替char、nchar。因为一来变长字段的存储空间小，可以节省存储空间，二来对于查询来说，在一个比较小的字段中搜索效率明显更高。14最好不要用returnall:selectfromt，把“*”换成具体的字段列表，不用的字段不要返回。15尽量避免向客户端返回大量数据。如果数据量太大，就要考虑对应的需求是否合理。16使用表别名（Alias）：在SQL语句中连接多个表时，请使用表别名，并在每个Column前加上别名。这减少了解析时间并减少了由Column歧义引起的语法错误。17使用“临时表”临时存储中间结果：简化SQL语句的一个重要方法是使用临时表临时存储中间结果。但是临时表的好处远不止这些。临时结果临时存放在临时表中，后续查询在tempdb中。这样可以避免程序中多次扫描主表，大大减少程序执行过程中的“共享锁”阻塞。“更新锁”减少阻塞，提高并发性能。18有些SQL查询语句要加nolock，因为读写会互相阻塞，以提高并发性能。对于某些查询，可以加nolock允许读时写，但缺点是可能读到未提交的脏数据。使用nolock有3个原则：查询结果用于“插入、删除、修改”，不能加nolock。查询表分页频繁，慎用nolock。使用临时表还可以保存“数据伏笔”，其功能类似于Oracle的undotablespace。如果可以使用临时表来提高并发性能，就不要使用nolock。19常见的简化规则如下：不要超过5个表连接（JOIN），考虑使用临时表或表变量来存储中间结果。少用子查询，不要嵌套视图太深。一般来说，最好不要嵌套超过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,*fromchineserasumea,personmemberbwherepersonMemberID=b.referenceidanda.personMemberID='JCNPRH39681'(A=B,B='Number')selecta.personMemberID,*fromchineserasumea,personmemberbwherea.personMemberID=b.referenceidanda.personMemberID='JCNPRH39681'andb.referenceid='JCNPRH39681'(A=B,B='Number',A='Number')选择a.personMemberID,*fromchineserasumea,personmemberbwhereb.referenceid='JCNPRH39681'anda.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正在运行的列是计算出来的，因此它必须在不使用该列上的索引的情况下进行表搜索。如果这些结果在查询编译时可用，它们可以被SQL优化器优化以使用索引并避免表搜索，因此重写SQL如下：SELECT*FROMrecordWHEREcard_nolike'5378%'(<1second)SELECT*FROMrecordWHEREamount<1000*30(<1second)SELECT*FROMrecordWHEREdate='1999/12/01'(<1second)30当有批量插入或更新时，使用批量插入或批量更新永远不会一一更新记录。31在所有的存储过程中，如果能用SQL语句，我是绝对不会用循环来实现的。例如：要列出上个月的每一天，我会使用connectby递归查询，绝不会使用从上个月的第一天到最后一天的循环。32Choosethemostefficientorderoftablenames(onlyvalidintherule-basedoptimizer):Oracle的parser按照从右到左的顺序处理FROM子句中的表名，FROM子句中的最后一张表（Basictabledrivingtable)将首先处理。FROM子句中有多个表时，必须选择记录最少的表作为基表。如果表连接查询超过3个，则需要选择交集表作为基表。交集表是指被其他表引用的表。33为了提高GROUPBY语句的效率，可以在GROUPBY之前过滤掉不需要的记录。以下两个查询返回相同的结果，但第二个查询要快得多。低效：SELECTJOB,AVG(SAL)FROMEMPGROUPBYJOBHAVINGJOB='PRESIDENT'ORJOB='MANAGER'高效：SELECTJOB,AVG(SAL)FROMEMPWHEREJOB='PRESIDENT'ORJOB='MANAGER'的GROUPBYJOB34SQL语句是大写的，因为Oracle总是先解析SQL语句，将小写字母转换成大写字母再执行。35别名的使用，别名是大型数据库的应用技巧，即在查询中将表名和列名用一个字母作为别名，查询速度比建连接表快1.5倍。36避免死锁，在你的存储过程和触发器中总是以相同的顺序访问同一个表；事务尽量缩短，事务中涉及的数据量尽量减少；从不在交易中等待用户输入。37避免使用临时表。除非必要，尽量避免使用临时表。相反，您可以改用表变量。大多数时候（99%），表变量驻留在内存中，因此比临时表更快，临时表驻留在TempDb数据库中，因此对临时表的操作需要跨数据库通信，自然更慢。38最好不要使用触发器：触发触发器和执行触发器事件本身就是一个消耗资源的过程。如果可以使用约束来实现，尽量不要使用触发器。不要对不同的触发事件（插入、更新和删除）使用相同的触发器。不要在触发器中使用事务代码。39索引创建规则：表的主键和外键必须有索引。超过300条数据的表应该有索引。经常与其他表连接的表应该在连接字段上有索引。经常出现在WHERE子句中的字段，尤其是在大表中，应该被索引。索引应该建立在高度选择性的字段上。索引应该建在小字段上，不要建在大文本字段甚至超长字段上。建立复合索引需要仔细分析，尽量考虑使用单字段索引代替。正确选择复合索引中的主列字段，一般是选择性比较好的字段。复合索引的几个字段是否经常以AND的形式同时出现在WHERE子句中？是否很少或没有单字段查询？如果是这样，你可以建立一个复合索引；否则，请考虑单字段索引。如果复合索引包含的字段经常单独出现在WHERE子句中，则将其分解为多个单字段索引。如果复合索引包含的字段超过3个，请慎重考虑其必要性，考虑减少复合字段。如果这些字段上同时存在单字段索引和复合索引，一般可以删除复合索引。对于数据操作频繁的表，不要创建过多的索引。删除无用的索引以避免对执行计划产生负面影响。在表上创建的每个索引都会增加存储开销，索引也会增加插入、删除和更新操作的处理开销。另外，复合索引太多，一般在单字段索引的情况下是没用的；相反，它们还会降低数据增删改查的性能，尤其是对于频繁更新的表，负面影响就更大big了。尽量不要索引数据库中包含大量重复值的字段。40MySQL查询优化小结：使用慢查询日志发现慢查询，使用执行计划判断查询是否正常运行，并时时测试你的查询是否以最佳状态运行。性能总是会随着时间的推移而变化，避免对整个表使用count(*)，它可能会锁定整个表，使查询保持一致，以便后续类似的查询可以使用查询缓存。在适当的地方使用GROUPBY而不是DISTINCT，在WHERE、GROUPBY和ORDERBY子句中使用索引列，保持索引简单，不要在多个索引中包含相同的列。有时MySQL会使用错误的索引。在这种情况下，使用USEINDEX并检查使用SQL_MODE=STRICT的问题。对于少于5条记录的索引字段，使用LIMIT而不是UNION中的OR。为避免在更新前选择SELECT，请使用INSERTONDUPLICATEKEY或INSERTIGNORE；不要用UPDATE来做，不要用MAX。使用索引字段和带有LIMITM的ORDERBY子句，N在某些情况下实际上会减慢查询速度，请谨慎使用。在WHERE子句中使用UNION而不是子查询，重启MySQL后，记得预热数据库以确保数据在内存中并快速查询，考虑使用持久连接而不是多连接以减少开销。基准查询，包括使用服务器上的负载，有时一个简单的查询会影响其他查询，当服务器上的负载增加时，使用SHOWPROCESSLIST可以看到缓慢和有问题的查询，在开发环境中生成的镜像数据中测试所有可疑查询。41MySQL备份过程：从辅助复制服务器备份。在备份过程中停止复制，以避免数据依赖和外键约束不一致。完全停止MySQL，从数据库文件中备份。如果使用MySQL转储进行备份，还要备份二进制日志文件-以确保复制不会中断。不要相信LVM快照，它可能会造成数据不一致，将来会给您带来麻烦。为了更轻松地恢复单表，以表为单位导出数据-如果数据与其他表隔离。使用mysqldump时使用--opt。在备份之前检查和优化表。为了更快地导入，在导入时暂时禁用外键约束。为了更快地导入，导入时暂时禁用唯一性检查。在每次备份后计算数据库、表和索引的大小，以更好地监控数据大小的增长。通过自动调度脚本监控复制实例的错误和延迟。定期执行备份。42查询缓冲区不会自动处理空格。因此，在编写SQL语句时，应尽量减少空格的使用，尤其是SQL开头和末尾的空格（因为查询缓冲区不会自动截取首末空格）。43会员用mid作为分表标准方便吗？在一般的业务需求中，基本都是以用户名作为查询依据。通常情况下，用户名应该作为哈希取模来分表。至于分表，MySQL的分区功能就是这样做的，对代码是透明的；在代码层面实现似乎不太合理。44我们应该为数据库中的每张表设置一个ID作为它的主键，最好是INT类型（推荐UNSIGNED），并设置自动增加的AUTO_INCREMENT标志。45.在所有存储过程和触发器的开头设置SETNOCOUNTON，在结束时设置SETNOCOUNTOFF。存储过程和触发器的每条语句执行完后，不需要向客户端发送DONE_IN_PROC消息。46MySQL查询可以启用高速查询缓存。这是提高数据库性能的有效MySQL调优方法之一。当多次执行同一个查询时，从缓存中获取数据并直接从数据库中返回要快得多。47EXPLAINSELECT查询用于跟踪查看效果：使用EXPLAIN关键字让你知道MySQL是如何处理你的SQL语句的。这可以帮助您分析查询语句或表结构中的性能瓶颈。EXPLAIN查询结果还会告诉你你的索引主键是如何使用的，你的数据表是如何被搜索和排序的。48UseLIMIT1whentheonlyonerowofdata:当你查询表的时候，你已经知道只会有一个结果，但是因为你可能需要取游标，或者你可能会检查返回的记录数。在这种情况下，添加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字符串数据类型：char、varchar、text选择的区别。52对列的任何操作都会引起表扫描，包括数据库函数、计算表达式等，查询时尽量将操作移到等号右边。作者：SimpleWu编辑：陶佳龙来源：cnblogs.com/SimpleWu/p/9929043.html
