MySQL查询性能优化
2018-07-09 14:01:15 
阿炯
为什么会有慢查询
通常来说，查询的生命周期大致可以按照顺序来看：从客户端>>服务器>>在服务器上进行解析>>生成执行计划>>执行>>返回结果给客户端。其中执行可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。了解查询的生命周期、清楚查询的时间消耗情况对于优化查询有很大的意义。
一、优化数据访问
查询性能低下的最基本的原因是访问的数据太多，大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。
1.确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。
2.确认MySQL服务器层是否在分析大量超过需要的数据行。
1是否向数据库请求了不需要的数据
请求多余的数据会给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU内存和资源。这里有一些典型案例：
1、查询不需要的记录：例如在新闻网站中取出100条记录，但是只是在页面上显示10条。实际上MySQL会查询出全部的结果，客户端的应用程序会接收全部的结果集数据，然后抛弃其中大部分数据。最简单有效的解决方法就是在这样的查询后面加上LIMIT。
2、多表关联时返回全部列。
3、总是取出全部的列：每次看到SELECT *的时候都需要怀疑是不是真的需要返回全部的列？取出全部列，会主优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的IO、内存和CPU的消耗。如果应用程序使用了某种缓存机制，或者有其他考虑，获取超过需要的数据也可能有其好处，但不要忘记这样做的代价是什么。获取并缓存所有的列的查询，相比多个独立的只获取部分列的查询可能就更有好处。
4、重复查询相同的数据：不要不断地重复执行相同的查询，然后每次都返回完全相同的数据。当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然更好。
2MySQL是否在扫描额外的记录
对于MySQL，最简单的衡量查询开销的三个指标有：响应时间、扫描的行数、返回的行数。这三个指标都会记录到MySQL的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。
响应时间
响应时间是两个部分之和：服务时间和排队时间，一般常见和重要的等待是IO和锁等待。
扫描的行数和返回的行数
分析查询时，查看该查询扫描的行数是非常有帮助的。一定程度上能够说明该查询找到需要的数据的效率高不高。理想的情况下扫描的行数和返回的行数应该是相同的，当然这只是理想情况。一般来说扫描的行数对返回的行数的比率通常很小，一般在1：1到10：1之间。
扫描的行数和访问类型
MySQL有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无须扫描就能返回结果。在EXPLAIN语句的TYPE列返回了访问类型。如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。索引让MySQL以最高效、扫描行最少的方式找到需要的记录。
一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：
1、在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
2、使用索引覆盖扫描在extra列中出现了using index来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须回表查询记录。
3、从数据表中返回数据，然后过滤不满足条件的记录在extra列中出现using where。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。
二、重构查询的方式
1、一个复杂查询还是多个简单查询
MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询也是很有必要的。
2、切分查询
有时候对于一个大查询我们需要“分而治之”，对于删除旧数据，如果用一个大的语句一次性完成的话，则可能需要一次性锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。
3、分解关联查询
1、让缓存的效率更高。对MySQL的查询缓存来说，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么该表的查询缓存能重复利用 。
2、将查询后，执行单个查询可以减少锁的竞争。
3、查询性能也有所提升，使用IN代替关联查询，可以让MySQL按照ID顺序进行查询，这比随机的关联要更高效。
三、查询执行的基础
当希望MySQL能够能更高的性能运行查询时，最好的办法就是弄清楚MySQL是如何优化和执行查询的。
1、MySQL客户端/服务端通信协议
MySQL客户端和服务器之间的通信协议是“半双工”的，在任何一个时刻，要么由服务器向客户端向服务端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。
一旦客户端发送了请求，它能做的事情就只是等待结果了，如果查询太大，服务端会拒绝接收更多的数据并抛出相应错误，所以参数max_allowed_packet就特别重要。相反，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后主服务器停止发送数据。这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果然后粗暴地断开连接，都不是好主意。这也是必要的时候需要在查询中加上limit限制的原因。
换一种方式解释这种行为：当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是MySQL在向客户端推数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。
当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题，但是如果需要返回一个很大的结果集时，这样做并不好，因为库函数会花很多时间和内存来存储所有的结果集。如果能尽早开始处理这些数据，就能大大减少内在的消耗，这种情况下可以不使用缓存来记录结果而是直接处理。PHP的 mysql_query()，此时数据已经到了PHP的缓存中，而mysql_unbuffered_query()不会缓存结果。
查询状态：可以使用SHOW FULL PROCESSLIST命令查看查询的执行状态。Sleep、Query、Locked、Analyzing and statistics、Copying to tmp table[on disk]、Sorting result、Sending data等。
2、查询缓存
在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这是检查是通过一个对大小写敏感的哈希查找实现的。如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。如果权限没有问题，MySQL会跳过执行阶段，直接从缓存中拿到结果并返回给客户端。
3、查询优化处理
查询生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。
1)、语法解析器和预处理首先MySQL通过关键字将SQL语句进行解析，并生成一棵解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。例如是否使用错误的关键字，或者使用关键字的顺序是否正确，引号是否能前后正确匹配等。
2)、预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如检查数据表和数据列是否存在，还会解析名字和别名看它们是否有歧义。
3)、一下步预处理会验证权限。
查询优化器：一条语句可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到最好的执行计划。MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。成本的最小单位是随机读取一个4K的数据页的成本，并加入一些因子来估算某引动操作的代价。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。
这是根据一系列的统计信息计算得来的：每个表或者索引的页面个数、索引的基数索引中不同值的数量、索引和数据行的长度、索引分布情况。
当然很多原因会导致MySQL优化器选择错误的执行计划：例如统计信息不准确或执行计划中的成本估算不等同于实际执行的成本。
MySQL如何执行关联查询：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到一个表中寻找匹配的行，依次下去直到找到的有匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。嵌套循环关联
执行计划：MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果，最终的执行计划包含了重构查询的全部信息。如果对某个查询执行EXPLAIN EXTENDED，再执行SHOW WARNINGS，就可以看到重构出的查询。
MySQL的执行计划是一棵左侧深度优先的树。
不过，如果有超过n个表的关联，那么需要检查n的阶乘种关联顺序。我们称之为所有可能的执行计划的“搜索空间”。实际上，当需要关联的表超过optimizer_search_depth的限制的时候，就会选择“贪婪”搜索模式。
排序优化：无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。如果需要排序的数据量小于排序缓冲区，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排序的块进行合并，最手返回排序结果。
MySQL有两种排序方法：
两次传输排序旧版，读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。显然是两次传输，特别是读取排序后的数据时第二次大量随机I/O，所以两次传输成本高。
单次传输排序新版，一次读取出所有需要的或SQL查询指定的列，然后根据排序列，排序，直接返回排序后的结果。顺序I/O，缺点：如果列多，额外占用空间。
MySQL在进行文件排序时需要使用的临时存储空间可能会比想象的要大得多，因为MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放，这个定长空间必须足够以容纳其中最长的字符串。
在关联查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序。如果ORDER BY子句的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表时就进行文件排序。如果是这样那么在MySQL的EXPLAIN结果中可以看到Extra字段会有Using filesort。除此之外的所有情况，MySQL都会将关联的结果存放在一个临时表中，然后在所有的关联都结束后，再进行文件排序。这种情况下Extra字段可以看到Using temporary;Using filesort。如果查询中有LIMIT的话，LIMIT也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。
4、查询执行引擎
相对于查询优化，查询执行简单些了，MySQL只根据执行计划输出的指令逐步执行。指令都是调用存储引擎的API来完成，一般称为 handler API，实际上，MySQL优化阶段为每个表都创建了一个 handler 实例，用 handler 实例获取表的相关信息列名、索引统计信息等。
存储引擎接口有着非常丰富的功能，但是底层接口却只有几十个，这些接口像搭积木一样能够完成查询的大部分操作。例如，有一个查询某个索引的第一行的接口，再有一个查询某个索引条件的下一条目的功能，有了这两个功能就可以完成全索引扫描操作。
5、返回结果给客户端
查询执行的最后一个阶段就是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，例如该查询影响到的行数。
MySQL将结果集返回客户端是一个增量、逐步返回的过程。一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。
这样处理有两个好处：服务端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让MySQL客户端第一时间获得返回的结果。
五、MySQL服务器及InnoDB配置调优
1、InnoDB缓冲池Buffer Pool
有一个流行的经验法则说，应该把缓冲池大小设置为服务器内存的约75~80%，但并不总是正确的。有一个更好的办法来设置缓冲池大小，大致如下：
1、从服务器内存总量开始
2、减去操作系统的内存占用
3、减去一些MySQL自身需要的内存
4、减去足够让操作系统缓存InnoDB日志文件的内存，至少是足够缓存最近经常访问的部分
5、减去其他配置的MySQL缓冲和缓存需要的内存，例如查询缓存，MYISAM键缓存
6、除以105%，把结果向下取一个合理的数值。
如果大部分都是InnoDB表，InnoDB缓冲池或许比其他任何东西更需要内存。InnoDB缓冲池并不仅仅缓存索引：它还缓存行的数据、自适应哈希索引、插入缓存Insert buffer、锁、以及其他内部数据结构。InnoDB还使用缓冲池来帮助延迟写入，这样就能合并多个写入操作，然后一起顺序地写回。总之InnoDB严重依赖缓冲池，你必须确认为它分配了足够的内存。
如果数据量不大，并且不会快速增长，就没必要为缓冲池分配过多的内存。把缓冲池配置得比需要缓存的表和索引还要大很多实际上没有什么意义。当然，对一个迅速增长的数据库做超前的规划没有问题，但有时我们也会看到一个巨大的缓冲池只缓存了一点点数据，这就没有必要了。
很大的缓冲池也会带一些挑战，例如，预热和关闭都会花费很长的时间。如果有很多脏页在缓冲池里，InnoDB关闭时可能会花费较长的时间把脏页写回数据文件。当然也可以强制快速关闭，但是重启时就必须做更多的恢复工作。
当脏页的百分比超过了innodb_max_dirty_pages_pct阈值，InnoDB将快速地刷写脏页，尝试让脏页的数量更低。当事务日志没有足够的空间剩余时，InnoDB将进入“激烈刷写”模式，这就是大日志可以提升性能的一个原因。
如果不能快速预热，可以在重启后立即进行全表扫描或者索引扫描，把索引载入缓冲池。也可以使用init_file设置，把SQL放在一个文件里，然后当MySQL启动的时候来执行。
2、线程缓存
线程缓存保存那些当前没有与连接关联但是为后面新的连接服务的线程。当一个新的连接创建时，如果缓存中有线程存在，MySQL从缓存中删除一个线程，并且把它分配给这个新的连接。当连接关闭时，如果线程缓存还有空间的话，MySQL又会把线程放回缓存。如果没有空间的话，MySQL会销毁这个线程。只要MySQL在缓存里还有空闲的线程，它就可以迅速地响应连接请求，因为这样就不用为每个连接创建新的线程。
一个好的办法是观察Threads_connected变量并且尝试设置thread_cache_size足够大以便能处理业务压力正常的波动。每个在线程缓存中的线程或者休眠状态的线程，通常使用256K左右的内存。相对于正在处理查询的线程来说，这个内存不算很大。
3、表缓存
表缓存可以重用资源。当一个查询请求访问一张MYISAM表，MySQL可以从缓存的对象中获取到文件描述符，避免打开一个文件描述符的开销。对MYISAM表来说，表缓存的真正好处是可以让服务器避免修改MYISAM文件头来标记表“正在使用中”，表缓存对于InnoDB重要性就小得多，因为InnoDB不依赖它来做那么多事，例如持有文件描述符等。
4、InnoDB I/O配置事务日志
InnoDB使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无须在每个事务提交时把缓冲池的脏块刷新(flush)到磁盘中。事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机IO。InnoDB假设使用常规磁盘，随机IO比顺序IO昂贵得多，因为一个IO请求需要时间把磁头移到正确的位置，然后等待磁盘上读出需要的部分，再转到开始位置。
InnoDB用日志把随机IO变成顺序IO。一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB可以重放日志并且恢复已经提交的事务。
InnoDB使用一个后台线程智能地刷新这些变更到数据文件。这个线程可以批量组合写入，使得数据写入更顺序，以提高效率。
整体的日志文件大小受控于innodb_log_file_size和innodb_log_files_in_group两个参数，这对写性能非常重要。日志文件的总大小是每个文件的大小之和。
InnoDB使用多个文件作为一组循环日志。通常不需要修改默认的日志数量，只修改每个日志文件的大小即可。要修改日志文件大小，需要完全关闭MySQL，将旧的日志文件移到其他地方保存，重新配置参数。
要确定理想的日志文件大小，必须权衡正常数据变更的开销和崩溃恢复需要的时间，如果日志太小，InnoDB必然将做更多的检查点，导致更多的日志写。如果日志太大，在崩溃恢复时InnoDB可能不得不做大量的工作。
当InnoDB变更任何数据时，会写一条变更记录到内存日志缓冲区中。在缓冲满的时候，事务提交的时候，或者每一秒钟，这三个条件无论哪个先达到，InnoDB都会刷新缓冲区的内容到磁盘日志文件。变量innodb_log_buffer_size可以控制日志缓冲区的大小，默认为1M。通常不需要把日志缓冲区设置得非常大。推荐的范围是1~8M。作为一个经验法则，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。
InnoDB怎么刷新日志缓冲？当InnoDB把日志缓冲刷新到磁盘日志文件时，会先使用一个Mutex锁住缓冲区，刷新到所需要的位置，然后移动剩下的条目到缓冲区的前面。日志缓冲必须被刷新到持久化存储，以确保提交的事务完全被持久化了。如果和持久相比更在乎性能，可以修改innodb_flush_log_at_trx_commit变量来控制日志缓冲刷新的频繁程度。可能的设置如下：
0：每秒一次把日志缓冲写到日志文件，但是事务提交时不做任何时。
1：将日志缓冲写到日志文件，然后每次事务提交都刷新到持久化存储默认并且最安全的设置，该设置保证不会丢失任何已提交的事务。
2：每秒钟做一次刷新，但每次提交时把日志缓冲写到日志文件，但是不刷新到持久化存储。
“把日志缓冲写到日志文件”和“把日志刷新到持久化存储”是不同的。在大部分操作系统中，把缓冲写到日志只是简单地把数据从InnoDB的内存缓冲转移到了操作系统的缓存，也是在内存里，并没有真正把数据写到持久化存储。
如果MySQL崩溃了或者断电了，设置0和2通常会导致最多1秒的数据丢失，因为数据可能存在于操作系统的缓存中。
相反，把日志刷新到持久化存储意味着InnoDB请求操作系统把数据刷出缓存，并且确认写到磁盘了，这是一个阻塞IO的调用，直到数据被完全写回才会完成，当写数据到磁盘比较慢，而该配置项设置为1时，可能明显地降低InnoDB每秒可以提交的事务数。
5、InnoDB并发配置
InnoDB有自己的“线程调度器”控制线程怎么进入内核访问数据，以及它们在内核中一次可以做哪些事。最基本的限制并发方式是使用innodb_thread_concurrency变量，它会限制一次性可以有多少线程进入内核，0表示不限制。
理论上，可以参考下面的公式：并发值＝CPU数量*磁盘数量*2
另外，也可以通过线程池(Thread Pool)来限制并发。
6、优化排序Filesorts
如果查询中所有需要的列和order by的列总大小超过max_length_for_sort_data字节，则采用two-pass算法，否则采用single-pass算法。
7、其他配置项
1.max_connections：这个设置的作用就像一个紧急刹车，以保证服务器不会因应用程序激增的连接而不堪重负。如果应用程序有问题，或者服务器遇到如连接延迟的问题，会创建很多新连接，但是如果不能执行查询，那打开一个连接没有好处，所以被“太多的连接”的错误拒绝是一种快速而代价小的失败方式。
要时时小心可能遇到连接限制的突然袭击。例如，若重启应用服务器，可能没有把它的连接关闭干净，同时MySQL可能没有意识到它们已经被关闭了。当应用服务器重新开始运行，并试图打开到数据库的连接，就可能由于挂起的连接还没有超时，导致新连接被拒绝。观察max_used_connections状态变量随着时间的变化，如果这个值达到了max_connections，说明客户端至少被拒绝了一次。
2.thread_cahce_size:观察Threads_connected状态变量并且找到它在一般情况下的最大值和最小值。可以设置为波动范围2~3倍大小。但是也不用设置得非常大，因为保持大量等待连接的空闲线程并没有什么真正的用处。
也可以观察threads_created状态随时间的变化，如果这个值很大或者一直增长，这可能在告诉你，需要调大thread_cahce_size变量。查看threads_cached来看有多少线程已经在缓存中了。
另一个相关的状态变量是slow_launch_threads。这个状态如果很大，那么意味着某些情况延迟了连接分配新线程。一般来说可能是系统过载了，导致操作系统不能为新创建的线程调度CPU。
总之，如果使用的是InnoDB，最重要的配置项是以下两个：innodb_buffer_pool_size和innodb_log_file_size。
六、一条SQL语句上如何被MySQL架构中的各个组件操作执行的
本节分享自华为云社区，原作者：砖业洋。
1、单表查询 SQL 在 MySQL 架构中的各个组件的执行过程
简单用一张图说明下，MySQL 架构有哪些组件，接下来给大家用 SQL 语句分析，假如 SQL 语句是这样
SELECT class_no FROM student WHERE name = 'lcy' AND age > 18 GROUP BY class_no
其中 name 为索引，按照时间顺序来分析一下：
1.客户端：客户端（如 MySQL 命令行工具、Navicat、MySQL Workbench 或其他应用程序）发送 SQL 查询到 MySQL 服务器。
2.连接器：连接器负责与客户端建立连接、管理连接和维护连接。当客户端连接到 MySQL 服务器时，连接器验证客户端的用户名和密码，然后分配一个线程来处理客户端的请求。
3.查询缓存：查询缓存用于缓存先前执行过的查询及其结果。当收到新的查询请求时，MySQL 首先检查查询缓存中是否已有相同的查询及其结果。如果查询缓存中有匹配的查询结果将直接返回缓存的结果，而无需再次执行查询。但如果查询缓存中没有匹配的查询结果，MySQL 将继续执行查询。查询缓存在 MySQL8.0 中已被移除，不详细解释。
4.分析器：
解析查询语句，检查语法。
验证表名和列名的正确性。
生成查询树。
5.优化器：分析查询树，考虑各种执行计划，估算不同执行计划的成本，选择最佳的执行计划。在这个例子中，优化器可能会选择使用 name 索引进行查询，因为 name 是索引列。
6.执行器：根据优化器选择的执行计划，向存储引擎发送请求，获取满足条件的数据行。
7.存储引擎（如 InnoDB）：负责实际执行索引扫描，如在 student 表的 name 索引上进行等值查询，因查询全部列，涉及到回表访问磁盘。在访问磁盘之前，先检查 InnoDB 的缓冲池（Buffer Pool）中是否已有所需的数据页。如果缓冲池中有符合条件的数据页，直接使用缓存的数据。如果缓冲池中没有所需的数据页，从磁盘加载数据页到缓冲池中。
8.执行器：对于每个找到的记录，再次判断记录是否满足索引条件 name。这是因为基于索引条件加载到内存中是数据页，数据页中也有可能包含不满足索引条件的记录，所以还要再判断一次 name 条件，满足 name 条件则继续判断 age > 18 过滤条件。根据 class_no 对满足条件的记录进行分组。执行器将处理后的结果集返回给客户端。
在整个查询执行过程中，这些组件共同协作以高效地执行查询。客户端负责发送查询，连接器管理客户端连接，查询缓存尝试重用先前查询结果，解析器负责解析查询，优化器选择最佳执行计划，执行器执行优化器选择的计划，存储引擎（如 InnoDB）负责管理数据存储和访问。这些组件的协同作用使得 MySQL 能够高效地执行查询并返回结果集。根据索引列过滤条件加载索引的数据页到内存这个操作是存储引擎做的。加载到内存中之后，执行器会进行索引列和非索引列的过滤条件判断。
2、SELECT 的各个关键字在哪里执行
根据执行顺序，如下：
（1）FROM：FROM 子句用于指定查询所涉及的数据表。在查询执行过程中，执行器需要根据优化器选择的执行计划从存储引擎中获取指定表的数据。
（2）ON：ON 子句用于指定连接条件，它通常与 JOIN 子句一起使用。在查询执行过程中，执行器会根据 ON 子句中的条件从存储引擎获取满足条件的记录。如果连接条件涉及到索引列，存储引擎可能会使用索引进行优化。
（3）JOIN：JOIN 子句用于指定表之间的连接方式（如 INNER JOIN, LEFT JOIN 等）。在查询执行过程中，执行器会根据优化器选择的执行计划，从存储引擎中获取需要连接的表的数据。然后，执行器根据 JOIN 子句的类型和 ON 子句中的连接条件，对数据进行连接操作。
（4）WHERE：执行器对从存储引擎返回的数据进行过滤，只保留满足 WHERE 子句条件的记录。部分过滤条件如果涉及到索引，在存储引擎层就已经进行了过滤。
（5）GROUP BY：执行器对满足 WHERE 子句条件的记录按照 GROUP BY 子句中指定的列进行分组。
（6）HAVING：执行器在进行分组后，根据 HAVING 子句条件对分组后的记录进行进一步过滤。
（7）SELECT：执行器根据优化器选择的执行计划来获取查询结果。
（8）DISTINCT：执行器对查询结果进行去重，只返回不重复的记录。
（9）ORDER BY：执行器对查询结果按照 ORDER BY 子句中指定的列进行排序。
（10）LIMIT：执行器根据 LIMIT 子句中指定的限制条件对查询结果进行截断，只返回部分记录
3、表关联查询 SQL 在 MySQL 架构中的各个组件的执行过程
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;
这个例子中，student_id 和 subject 是联合索引，age 是索引。按照时间顺序来分析一下：
1.连接器：当客户端连接到 MySQL 服务器时，连接器负责建立和管理连接。它验证客户端提供的用户名和密码，确定客户端具有相应的权限，然后建立连接。
2.查询缓存：MySQL 服务器在处理查询之前，会先检查查询缓存。如果查询缓存中已经存在相同的查询及其结果集，服务器将直接返回缓存中的结果，而不再执行后续的查询处理。由于查询缓存在 MySQL 8.0 中已被移除，我们在这个示例中不再详细讨论。
3.解析器：解析器的主要任务是解析 SQL 查询语句，确保查询语法正确。解析器会将查询语句分解成多个组成部分，例如表、列、条件等。在这个示例中，解析器会识别出涉及的表（student 和 score）以及需要的列（id、name、age、subject、score）。
4.优化器：优化器的职责是根据解析器提供的信息生成执行计划。它会分析多种可能的执行策略，并选择成本最低的策略。在这个示例中，优化器可能会分析各种表扫描和索引扫描的组合，最终选择一种成本最低的执行计划。
5.执行器：根据优化器生成的执行计划处理查询，向存储引擎发送请求，获取满足条件的数据行。
6.存储引擎（如 InnoDB）：存储引擎负责管理数据的存储和检索。
存储引擎首先接收来自执行器的请求。请求可能包括获取满足查询条件的数据行，以及使用哪种扫描方法（如全表扫描或索引扫描）。
假设执行器已经决定使用索引扫描。在这个示例中，存储引擎可能会先对 student 表进行索引扫描（使用 age 索引），然后对 score 表进行索引扫描（使用 student_id 和 subject 的联合索引）。
存储引擎会根据请求查询相应的索引结构。在 student 表中，存储引擎会找到满足 age > 18 条件的记录。在 score 表中，存储引擎会找到满足 subject = 'math' AND score > 80 条件的记录。一旦找到了满足条件的记录，存储引擎需要将这些记录所在的数据页从磁盘加载到内存中。存储引擎首先检查缓冲池（InnoDB Buffer Pool），看这些数据页是否已经存在于内存中。如果已经存在，则无需再次从磁盘加载。如果不存在，存储引擎会将这些数据页从磁盘加载到缓冲池中。
加载到缓冲池中的记录可以被多个查询共享，这有助于提高查询效率。
7.执行器：处理连接、排序、聚合、过滤等操作。
在内存中执行连接操作，将 student 表和 score 表的数据行连接起来。
对连接后的结果集进行过滤，只保留满足查询条件（age > 18、subject = 'math'、score > 80）的数据行。
将过滤后的数据行作为查询结果返回给客户端。
前面说过，根据存储引擎根据索引条件加载到内存的数据页有多数据，可能有不满足索引条件的数据，如果执行器不再次进行索引条件判断，则无法判断哪些记录满足索引条件的，虽然在存储引擎判断过了，但是在执行器还是会有索引条件 age > 18、subject = 'math'、score > 80 的判断。
4、LEFT JOIN 将过滤条件放在子查询中再关联和放在 WHERE 子句上有什么区别？
先看例子
查询1
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;
查询2
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM (SELECT id, name, age FROM student WHERE age > 18) s
LEFT JOIN (SELECT student_id, subject, score FROM score WHERE subject = 'math' AND score > 80) sc
ON s.id = sc.student_id
查询3
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id AND s.age > 18 AND sc.subject = 'math' AND sc.score > 80;
先给出结论：查询2 和 3 是一样的，也就是过滤条件放在子查询中和放在 on 上面是一样的，后面就只讨论查询1、2，查询1 和查询2 是不一样的，过滤条件放在 where 子句中和放在子查询再关联查询出的结果也是有区别的。
分析一下
从运行结果来看，对于查询1：
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;
在这个查询中，首先执行 LEFT JOIN，将 student 表和 score 表连接起来。连接操作是基于 s.id = sc.student_id 条件进行的。LEFT JOIN 操作会保留左表（student 表）中的所有行，即使它们在右表（score 表）中没有匹配的行。如果右表中没有匹配的行，那么右表的列将显示为 NULL。
然后，WHERE 子句会过滤连接后的结果集，只保留那些满足 s.age > 18 and sc.subject = 'math' and sc.score > 80 条件的行。这意味着，右表为 NULL 的记录将被排除，因为右表的过滤条件 sc.subject = 'math' and sc.score > 80 条件不满足。
对于查询2：
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM (select id, name, age from student where age > 18) s
LEFT JOIN (select subject, score from score where subject = 'math' AND score > 80) sc
ON s.id = sc.student_id
在这个查询中，首先执行两个子查询。第一个子查询从 student 表中选择所有 age > 18 的行，而第二个子查询从 score 表中选择所有 subject = 'math' and score > 80 的行。这意味着，在进行连接操作之前已经对两个表分别进行了过滤。
接下来，执行 LEFT JOIN 操作，将过滤后的 s 和 sc 子查询的结果集连接起来，基于 s.id = sc.student_id 条件。因为 LEFT JOIN 操作会保留左表（s 子查询的结果集）中的所有行，右表为 NULL 的记录包含了。
结果差异：
查询1和查询2的主要区别在于 WHERE 子句和子查询的使用。查询1 在连接操作后应用过滤条件，这可能导致右表为 NULL 的关联记录因为右表的过滤条件而被排除在外。而查询 2 在连接操作之前就已经过滤了表中的数据，这意味着查询结果会包含所有左表过滤条件的记录，以及右表过滤条件的记录和 NULL 的记录。
如果查询1 想保留右表为 NULL 的记录，只需要改为 WHERE s.age > 18 AND (sc.student_id is null OR (sc.subject = 'math' AND sc.score> 80)); 这样查询1 和 2 会有相同的结果集。分析一下这两个查询在 MySQL 架构中各个组件中执行的区别。
对于查询1：
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;
连接器：客户端与服务器建立连接。
查询缓存：检查缓存是否存在此查询的结果。如果有，直接返回结果。否则，继续执行。
解析器：解析查询语句，检查语法是否正确。
优化器：对查询进行优化，生成执行计划，决定连接和过滤条件的顺序等。
执行器：开始请求执行查询。
存储引擎（InnoDB）：从磁盘或者缓冲池读取满足条件的数据行（s.id = sc.student_id），因为是 left join，所以即便 sc.student_id 为 null 也会被关联。
执行器：将从存储引擎获取的数据行进行左连接，应用过滤条件 s.age > 18 and sc.subject = 'math' and sc.score > 80 进行过滤，将结果集返回给客户端。
当查询包含索引列的条件时，MySQL的存储引擎会首先利用索引在磁盘上定位到满足索引条件的记录。接着，将这些索引数据对应的数据页加载到内存中的缓冲池。然后，执行器在内存中对这些记录进行进一步的过滤，根据索引条件和非索引列的条件来过滤数据。当查询涉及到非聚集索引时，需要回表的操作会导致聚集索引和非聚集索引都被加载到内存中。但是，如果查询只涉及到聚集索引（如主键查询），那么只需要加载聚集索引的数据页即可。
对于查询2：
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM (SELECT id, name, age FROM student WHERE age > 18) s
LEFT JOIN (SELECT student_id, subject, score FROM score WHERE subject = 'math' AND score > 80) sc
ON s.id = sc.student_id
连接器：客户端与服务器建立连接。
查询缓存：检查缓存是否存在此查询的结果。如果有，直接返回结果。否则，继续执行。
解析器：解析查询语句，检查语法是否正确。
优化器：决定使用哪些索引进行查询优化，以及确定连接顺序。
执行器：开始请求执行子查询。
存储引擎（InnoDB）：首先，对 student 表进行扫描，将满足条件 s.age > 18 的记录对应的数据页加载到缓冲池 (如果缓冲池没有这个页的数据)。然后，使用 subject = 'math' AND score > 80 对 score 表进行扫描，将满足条件的记录对应的数据页加载到缓冲池 (如果缓冲池没有这个页的数据)。
执行器：对从存储引擎获取的数据应用所有的过滤条件，过滤后的结果存入临时表，执行主查询，从临时表中获取数据，将 s 和 sc 进行左连接，根据 s.id = sc.student_id 组合结果。将连接后的结果返回给客户端。
从这里我们可以看出，查询2是先过滤后连接，每张表的索引都很重要，如果没设置好索引，单表过滤会全表扫描。写 SQL 的时候，查询1和查询2到底采用哪种方式呢？
根据不同情况各有应用场景，需要注意的是，对于查询2，子查询的结果集被存储在一个临时表中，临时表不会继承原始索引，包括聚集索引和非聚集索引，所以刚刚的例子中，临时表中 s.id 和 sc.student_id 已经不是任何索引列了。对于查询1，最终满足关联条件 s.id = sc.student_id 的所有记录都会被加载到内存后再进行过滤。
当单表过滤后的数据量较小时，查询2可能是一个更好的选择，因为它可以减少关联操作的数据量，从而提高查询效率。子查询阶段，MySQL 依然会利用原始表上的索引进行过滤。子查询执行完成后，将过滤后的数据存储在临时表中。所以查询2的方式可以优化的点就是在单表查询时尽可能的利用索引。
当单表过滤后的数据量较大时，查询1可能更合适，因为它可以更好地利用索引进行关联操作。这样可以减少关联操作的时间开销，查询2因为临时表不继承索引，表关联的时间开销比较大。
5、聚集索引和全表扫描有什么区别呢？
走 PRIMARY 索引（聚集索引）和全表扫描有什么区别呢？准确来说，使用 InnoDB 存储引擎的情况下，全表扫描的数据和聚集索引的数据在 InnoDB 表空间中的存储位置是相同的，也就是说它们的内存地址也是相同的。所以也可以理解为，他们其实都是在聚集索引上操作的（聚集索引 B+ 树的叶子结点是根据主键排好序的完整的用户记录，包含表里的所有字段），区别就在于：全表扫描将聚集索引 B+ 树的叶子结点从左到右依次顺序扫描并判断条件。
聚集索引是利用二分思想将聚集索引 B+ 树到指定范围区间进行扫描，比如 select * from demo_info where id in (1, 2) 这种条件字段是主键 id，可以很好的利用 PRIMARY 索引进行二分的快速查询。
在 MyISAM 中，全表扫描的数据和索引数据的存储位置是分开的。然而 MyISAM 已经被 InnoDB 取代，不再是 MySQL 的推荐存储引擎，从 MySQL5.5 开始，InnoDB 就成了 MySQL 的默认存储引擎。默认情况下，InnoDB 使用一个名为 ibdata1 的共享表空间文件存储所有的数据和索引，包括聚集索引和二级索引（又称非聚集索引或辅助索引）。
52条SQL语句性能优化策略
这里会提到 52 条 SQL 语句性能优化策略，能在日常使用中适用到大多数场景。
1、对查询进行优化，应尽量避免全表扫描，首先应考虑在 WHERE 及 ORDER BY 涉及的列上建立索引。
2、应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，创建表时 NULL 是默认值，但大多数时候应该使用 NOT NULL，或者使用一个特殊的值，如 0，-1 作为默认值。
3、应尽量避免在 WHERE 子句中使用 != 或 <> 操作符。MySQL 只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的 LIKE。
4、应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以使用 UNION 合并查询：select id from t where num=10 union all select id from t where num=20。
5、IN 和 NOT IN 也要慎用，否则会导致全表扫描。对于连续的数值，能用 BETWEEN 就不要用 IN：select id from t where num between 1 and 3。
6、下面的查询也将导致全表扫描：select id from t where name like'%abc%' 或者select id from t where name like'%abc'若要提高效率，可以考虑全文检索。而select id from t where name like'abc%'才用到索引。
7、如果在 WHERE 子句中使用参数，也会导致全表扫描。
8、应尽量避免在 WHERE 子句中对字段进行表达式操作，应尽量避免在 WHERE 子句中对字段进行函数操作。
9、很多时候用 EXISTS 代替 IN 是一个好的选择：select num from a where num in(select num from b)。用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)。
10、索引固然可以提高相应的 SELECT 的效率，但同时也降低了 INSERT 及 UPDATE 的效率。因为 INSERT 或 UPDATE 时有可能会重建索引，所以怎样重建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
11、应尽可能的避免更新 clustered 索引数据列， 因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
12、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
13、尽可能的使用 varchar, nvarchar 代替 char, nchar。因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
14、最好不要使用返回所有：select from t ，用具体的字段列表代替 "*"，不要返回用不到的任何字段。
15、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
16、使用表的别名(Alias)：当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个 Column 上。这样一来，就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。
17、使用"临时表"暂存中间结果 ：
简化 SQL 语句的重要方法就是采用临时表暂存中间结果。但是临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在 tempdb 中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中"共享锁"阻塞"更新锁"，减少了阻塞，提高了并发性能。
18、一些 SQL 查询语句应加上 nolock，读、写是会相互阻塞的，为了提高并发性能。对于一些查询，可以加上 nolock，这样读的时候可以允许写，但缺点是可能读到未提交的脏数据。
使用 nolock 有3条原则：
查询的结果用于"插、删、改"的不能加 nolock；
查询的表属于频繁发生页分裂的，慎用 nolock ；
使用临时表一样可以保存"数据前影"，起到类似 Oracle 的 undo 表空间的功能，能采用临时表提高并发性能的，不要用 nolock。
19、常见的简化规则如下：
不要有超过 5 个以上的表连接(JOIN)，考虑使用临时表或表变量存放中间结果。少用子查询，视图嵌套不要过深，一般视图嵌套不要超过 2 个为宜。
20、将需要查询的结果预先计算好放在表中，查询的时候再Select。这在SQL7.0以前是最重要的手段，例如医院的住院费计算。
21、用 OR 的字句可以分解成多个查询，并且通过 UNION 连接多个查询。他们的速度只同是否使用索引有关，如果查询需要用到联合索引，用 UNION all 执行的效率更高。多个 OR 的字句没有用到索引，改写成 UNION 的形式再试图与索引匹配。一个关键的问题是否用到索引。
22、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。
23、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。
存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的 SQL 语句，是控制流语言的集合，速度当然快。反复执行的动态 SQL，可以使用临时存储过程，该过程(临时表)被放在 Tempdb 中。
24、当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用配制线程数量< 最大连接数，启用 SQL SERVER 的线程池来解决，如果还是数量 = 最大连接数+5，严重的损害服务器的性能。
25、查询的关联同写的顺序 ：
select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' (A = B, B = '号码')
select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' (A = B, B = '号码', A = '号码')
select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' (B = '号码', A = '号码')
26、尽量使用 EXISTS 代替 select count(1) 来判断是否存在记录。count 函数只有在统计表中所有行数时使用，而且 count(1) 比 count(*) 更有效率。
27、尽量使用 ">="，不要使用 ">"。
28、索引的使用规范：
索引的创建要与应用结合考虑，建议大的 OLTP 表不要超过 6 个索引；
尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过 index index_name 来强制指定索引；
避免对大表查询时进行 table scan，必要时考虑新建索引；
在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用；
要注意索引的维护，周期性重建索引，重新编译存储过程。
29、下列 SQL 条件语句中的列都有恰当的索引，但执行速度却非常慢：
SELECT * FROM record WHERE substrINg(card_no, 1, 4) = '5378' --13秒
SELECT * FROM record WHERE amount/30 < 1000 --11秒
SELECT * FROM record WHERE convert(char(10), date, 112) = '19991201' --10秒
分析：
WHERE 子句中对列的任何操作结果都是在 SQL 运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引。
如果这些结果在查询编译时就能得到，那么就可以被 SQL 优化器优化，使用索引，避免表搜索，因此将 SQL 重写成下面这样：
SELECT * FROM record WHERE card_no like '5378%' -- < 1秒
SELECT * FROM record WHERE amount < 1000*30 -- < 1秒
SELECT * FROM record WHERE date = '1999/12/01' -- < 1秒
30、当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新。
31、在所有的存储过程中，能够用 SQL 语句的，我绝不会用循环去实现。
例如：列出上个月的每一天，我会用 connect by 去递归查询一下，绝不会去用循环从上个月第一天到最后一天。
32、选择最有效率的表名顺序(只在基于规则的优化器中有效)：
Oracle 的解析器按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表(基础表 driving table)将被最先处理，在 FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。
如果有 3 个以上的表连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指那个被其他表所引用的表。
33、提高 GROUP BY 语句的效率，可以通过将不需要的记录在 GROUP BY 之前过滤掉。下面两个查询返回相同结果，但第二个明显就快了许多。
低效：
SELECT JOB, AVG(SAL)
FROM EMP GROUP BY JOB
HAVING JOB = 'PRESIDENT'
OR JOB = 'MANAGER'
高效：
SELECT JOB, AVG(SAL)
FROM EMP
WHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER'
GROUP BY JOB
34、SQL 语句用大写，因为 Oracle 总是先解析 SQL 语句，把小写的字母转换成大写的再执行。
35、别名的使用，别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快 1.5 倍。
36、避免死锁，在你的存储过程和触发器中访问同一个表时总是以相同的顺序；事务应尽可能地缩短，在一个事务中应尽可能减少涉及到的数据量；永远不要在事务中等待用户输入。
37、避免使用临时表，除非确有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替。大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在 TempDb 数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。
38、最好不要使用触发器：
触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程；
如果能够使用约束实现的，尽量不要使用触发器；
不要为不同的触发事件(Insert、Update 和 Delete)使用相同的触发器；
不要在触发器中使用事务型代码。
39、索引创建规则：
表的主键、外键必须有索引；
数据量超过 300 的表应该有索引；
经常与其他表进行连接的表，在连接字段上应该建立索引；
经常出现在 WHERE 子句中的字段，特别是大表的字段，应该建立索引；
索引应该建在选择性高的字段上；
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
复合索引的建立需要进行仔细分析，尽量考虑用单字段索引代替；
正确选择复合索引中的主列字段，一般是选择性较好的字段；
复合索引的几个字段是否经常同时以 AND 方式出现在 WHERE 子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
如果复合索引中包含的字段经常单独出现在 WHERE 子句中，则分解为多个单字段索引；
如果复合索引所包含的字段超过 3 个，那么仔细考虑其必要性，考虑减少复合的字段；
如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
频繁进行数据操作的表，不要建立太多的索引；
删除无用的索引，避免对执行计划造成负面影响。
表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。尽量不要对数据库中某个含有大量重复的值的字段建立索引。
40、MySQL 查询优化总结：
使用慢查询日志去发现慢查询，使用执行计划去判断查询是否正常运行，总是去测试你的查询看看是否他们运行在最佳状态下。
久而久之性能总会变化，避免在整个表上使用 count(*)，它可能锁住整张表，使查询保持一致以便后续相似的查询可以使用查询缓存，在适当的情形下使用 GROUP BY 而不是 DISTINCT，在 WHERE、GROUP BY 和 ORDER BY 子句中使用有索引的列，保持索引简单，不在多个索引中包含同一个列。
有时候 MySQL 会使用错误的索引，对于这种情况使用 USE INDEX，检查使用 SQL_MODE=STRICT 的问题，对于记录数小于5的索引字段，在 UNION 的时候使用LIMIT不使用OR。
为了避免在更新前 SELECT，使用 INSERT ON DUPLICATE KEY 或者 INSERT IGNORE；不要用 UPDATE 去实现，不要使用 MAX；使用索引字段和 ORDER BY子句 LIMIT M，N 实际上可以减缓查询在某些情况下，有节制地使用，在 WHERE 子句中使用 UNION 代替子查询，在重新启动的 MySQL，记得来温暖你的数据库，以确保数据在内存和查询速度快，考虑持久连接，而不是多个连接，以减少开销。
基准查询，包括使用服务器上的负载，有时一个简单的查询可以影响其他查询，当负载增加在服务器上，使用 SHOW PROCESSLIST 查看慢的和有问题的查询，在开发环境中产生的镜像数据中测试的所有可疑的查询。
41、MySQL 备份过程：
从二级复制服务器上进行备份；
在进行备份期间停止复制，以避免在数据依赖和外键约束上出现不一致；
彻底停止 MySQL，从数据库文件进行备份；
如果使用 MySQL dump 进行备份，请同时备份二进制日志文件 – 确保复制没有中断；
不要信任 LVM 快照，这很可能产生数据不一致，将来会给你带来麻烦；
为了更容易进行单表恢复，以表为单位导出数据——如果数据是与其他表隔离的；
当使用 mysqldump 时请使用 –opt；
在备份之前检查和优化表；
为了更快的进行导入，在导入时临时禁用外键约束；
为了更快的进行导入，在导入时临时禁用唯一性检测；
在每一次备份后计算数据库，表以及索引的尺寸，以便更够监控数据尺寸的增长；
通过自动调度脚本监控复制实例的错误和延迟；
定期执行备份。
42、查询缓冲并不自动处理空格，因此，在写 SQL 语句时，应尽量减少空格的使用，尤其是在 SQL 首和尾的空格(因为查询缓冲并不自动截取首尾空格)。
43、member 用 mid 做标准进行分表方便查询么？一般的业务需求中基本上都是以 username 为查询依据，正常应当是 username 做 hash 取模来分表。
而分表的话 MySQL 的 partition 功能就是干这个的，对代码是透明的；在代码层面去实现貌似是不合理的。
44、我们应该为数据库里的每张表都设置一个 ID 作为其主键，而且最好的是一个 INT 型的(推荐使用 UNSIGNED)，并设置上自动增加的 AUTO_INCREMENT 标志。
45、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON，在结束时设置 SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
46、MySQL 查询可以启用高速查询缓存。这是提高数据库性能的有效MySQL优化方法之一。当同一个查询被执行多次时，从缓存中提取数据和直接从数据库中返回数据快很多。
47、EXPLAIN SELECT 查询用来跟踪查看效果：
使用 EXPLAIN 关键字可以让你知道 MySQL 是如何处理你的 SQL 语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的。
48、当只要一行数据时使用 LIMIT 1 ：
当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。
在这种情况下，加上 LIMIT 1 可以增加性能。这样一来，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 来定义整型字段，应该尽量把字段设置为 NOT NULL，这样在将来执行查询的时候，数据库不用去比较 NULL 值。
对于某些文本字段，例如"省份"或者"性别"，我们可以将它们定义为 ENUM 类型。因为在 MySQL 中，ENUM 类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
51、字符串数据类型：char, varchar, text 选择区别。
52、任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
通常来说，查询的生命周期大致可以按照顺序来看：从客户端>>服务器>>在服务器上进行解析>>生成执行计划>>执行>>返回结果给客户端。其中执行可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。了解查询的生命周期、清楚查询的时间消耗情况对于优化查询有很大的意义。
一、优化数据访问
查询性能低下的最基本的原因是访问的数据太多，大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。
1.确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。
2.确认MySQL服务器层是否在分析大量超过需要的数据行。
1是否向数据库请求了不需要的数据
请求多余的数据会给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU内存和资源。这里有一些典型案例：
1、查询不需要的记录：例如在新闻网站中取出100条记录，但是只是在页面上显示10条。实际上MySQL会查询出全部的结果，客户端的应用程序会接收全部的结果集数据，然后抛弃其中大部分数据。最简单有效的解决方法就是在这样的查询后面加上LIMIT。
2、多表关联时返回全部列。
3、总是取出全部的列：每次看到SELECT *的时候都需要怀疑是不是真的需要返回全部的列？取出全部列，会主优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的IO、内存和CPU的消耗。如果应用程序使用了某种缓存机制，或者有其他考虑，获取超过需要的数据也可能有其好处，但不要忘记这样做的代价是什么。获取并缓存所有的列的查询，相比多个独立的只获取部分列的查询可能就更有好处。
4、重复查询相同的数据：不要不断地重复执行相同的查询，然后每次都返回完全相同的数据。当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然更好。
2MySQL是否在扫描额外的记录
对于MySQL，最简单的衡量查询开销的三个指标有：响应时间、扫描的行数、返回的行数。这三个指标都会记录到MySQL的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。
响应时间
响应时间是两个部分之和：服务时间和排队时间，一般常见和重要的等待是IO和锁等待。
扫描的行数和返回的行数
分析查询时，查看该查询扫描的行数是非常有帮助的。一定程度上能够说明该查询找到需要的数据的效率高不高。理想的情况下扫描的行数和返回的行数应该是相同的，当然这只是理想情况。一般来说扫描的行数对返回的行数的比率通常很小，一般在1：1到10：1之间。
扫描的行数和访问类型
MySQL有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无须扫描就能返回结果。在EXPLAIN语句的TYPE列返回了访问类型。如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。索引让MySQL以最高效、扫描行最少的方式找到需要的记录。
一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：
1、在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
2、使用索引覆盖扫描在extra列中出现了using index来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须回表查询记录。
3、从数据表中返回数据，然后过滤不满足条件的记录在extra列中出现using where。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。
二、重构查询的方式
1、一个复杂查询还是多个简单查询
MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询也是很有必要的。
2、切分查询
有时候对于一个大查询我们需要“分而治之”，对于删除旧数据，如果用一个大的语句一次性完成的话，则可能需要一次性锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。
3、分解关联查询
1、让缓存的效率更高。对MySQL的查询缓存来说，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么该表的查询缓存能重复利用 。
2、将查询后，执行单个查询可以减少锁的竞争。
3、查询性能也有所提升，使用IN代替关联查询，可以让MySQL按照ID顺序进行查询，这比随机的关联要更高效。
三、查询执行的基础
当希望MySQL能够能更高的性能运行查询时，最好的办法就是弄清楚MySQL是如何优化和执行查询的。
1、MySQL客户端/服务端通信协议
MySQL客户端和服务器之间的通信协议是“半双工”的，在任何一个时刻，要么由服务器向客户端向服务端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。
一旦客户端发送了请求，它能做的事情就只是等待结果了，如果查询太大，服务端会拒绝接收更多的数据并抛出相应错误，所以参数max_allowed_packet就特别重要。相反，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后主服务器停止发送数据。这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果然后粗暴地断开连接，都不是好主意。这也是必要的时候需要在查询中加上limit限制的原因。
换一种方式解释这种行为：当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是MySQL在向客户端推数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。
当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题，但是如果需要返回一个很大的结果集时，这样做并不好，因为库函数会花很多时间和内存来存储所有的结果集。如果能尽早开始处理这些数据，就能大大减少内在的消耗，这种情况下可以不使用缓存来记录结果而是直接处理。PHP的 mysql_query()，此时数据已经到了PHP的缓存中，而mysql_unbuffered_query()不会缓存结果。
查询状态：可以使用SHOW FULL PROCESSLIST命令查看查询的执行状态。Sleep、Query、Locked、Analyzing and statistics、Copying to tmp table[on disk]、Sorting result、Sending data等。
2、查询缓存
在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这是检查是通过一个对大小写敏感的哈希查找实现的。如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。如果权限没有问题，MySQL会跳过执行阶段，直接从缓存中拿到结果并返回给客户端。
3、查询优化处理
查询生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。
1)、语法解析器和预处理首先MySQL通过关键字将SQL语句进行解析，并生成一棵解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。例如是否使用错误的关键字，或者使用关键字的顺序是否正确，引号是否能前后正确匹配等。
2)、预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如检查数据表和数据列是否存在，还会解析名字和别名看它们是否有歧义。
3)、一下步预处理会验证权限。
查询优化器：一条语句可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到最好的执行计划。MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。成本的最小单位是随机读取一个4K的数据页的成本，并加入一些因子来估算某引动操作的代价。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。
这是根据一系列的统计信息计算得来的：每个表或者索引的页面个数、索引的基数索引中不同值的数量、索引和数据行的长度、索引分布情况。
当然很多原因会导致MySQL优化器选择错误的执行计划：例如统计信息不准确或执行计划中的成本估算不等同于实际执行的成本。
MySQL如何执行关联查询：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到一个表中寻找匹配的行，依次下去直到找到的有匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。嵌套循环关联
执行计划：MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果，最终的执行计划包含了重构查询的全部信息。如果对某个查询执行EXPLAIN EXTENDED，再执行SHOW WARNINGS，就可以看到重构出的查询。
MySQL的执行计划是一棵左侧深度优先的树。
不过，如果有超过n个表的关联，那么需要检查n的阶乘种关联顺序。我们称之为所有可能的执行计划的“搜索空间”。实际上，当需要关联的表超过optimizer_search_depth的限制的时候，就会选择“贪婪”搜索模式。
排序优化：无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。如果需要排序的数据量小于排序缓冲区，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排序的块进行合并，最手返回排序结果。
MySQL有两种排序方法：
两次传输排序旧版，读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。显然是两次传输，特别是读取排序后的数据时第二次大量随机I/O，所以两次传输成本高。
单次传输排序新版，一次读取出所有需要的或SQL查询指定的列，然后根据排序列，排序，直接返回排序后的结果。顺序I/O，缺点：如果列多，额外占用空间。
MySQL在进行文件排序时需要使用的临时存储空间可能会比想象的要大得多，因为MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放，这个定长空间必须足够以容纳其中最长的字符串。
在关联查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序。如果ORDER BY子句的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表时就进行文件排序。如果是这样那么在MySQL的EXPLAIN结果中可以看到Extra字段会有Using filesort。除此之外的所有情况，MySQL都会将关联的结果存放在一个临时表中，然后在所有的关联都结束后，再进行文件排序。这种情况下Extra字段可以看到Using temporary;Using filesort。如果查询中有LIMIT的话，LIMIT也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。
4、查询执行引擎
相对于查询优化，查询执行简单些了，MySQL只根据执行计划输出的指令逐步执行。指令都是调用存储引擎的API来完成，一般称为 handler API，实际上，MySQL优化阶段为每个表都创建了一个 handler 实例，用 handler 实例获取表的相关信息列名、索引统计信息等。
存储引擎接口有着非常丰富的功能，但是底层接口却只有几十个，这些接口像搭积木一样能够完成查询的大部分操作。例如，有一个查询某个索引的第一行的接口，再有一个查询某个索引条件的下一条目的功能，有了这两个功能就可以完成全索引扫描操作。
5、返回结果给客户端
查询执行的最后一个阶段就是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，例如该查询影响到的行数。
MySQL将结果集返回客户端是一个增量、逐步返回的过程。一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。
这样处理有两个好处：服务端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让MySQL客户端第一时间获得返回的结果。
五、MySQL服务器及InnoDB配置调优
1、InnoDB缓冲池Buffer Pool
有一个流行的经验法则说，应该把缓冲池大小设置为服务器内存的约75~80%，但并不总是正确的。有一个更好的办法来设置缓冲池大小，大致如下：
1、从服务器内存总量开始
2、减去操作系统的内存占用
3、减去一些MySQL自身需要的内存
4、减去足够让操作系统缓存InnoDB日志文件的内存，至少是足够缓存最近经常访问的部分
5、减去其他配置的MySQL缓冲和缓存需要的内存，例如查询缓存，MYISAM键缓存
6、除以105%，把结果向下取一个合理的数值。
如果大部分都是InnoDB表，InnoDB缓冲池或许比其他任何东西更需要内存。InnoDB缓冲池并不仅仅缓存索引：它还缓存行的数据、自适应哈希索引、插入缓存Insert buffer、锁、以及其他内部数据结构。InnoDB还使用缓冲池来帮助延迟写入，这样就能合并多个写入操作，然后一起顺序地写回。总之InnoDB严重依赖缓冲池，你必须确认为它分配了足够的内存。
如果数据量不大，并且不会快速增长，就没必要为缓冲池分配过多的内存。把缓冲池配置得比需要缓存的表和索引还要大很多实际上没有什么意义。当然，对一个迅速增长的数据库做超前的规划没有问题，但有时我们也会看到一个巨大的缓冲池只缓存了一点点数据，这就没有必要了。
很大的缓冲池也会带一些挑战，例如，预热和关闭都会花费很长的时间。如果有很多脏页在缓冲池里，InnoDB关闭时可能会花费较长的时间把脏页写回数据文件。当然也可以强制快速关闭，但是重启时就必须做更多的恢复工作。
当脏页的百分比超过了innodb_max_dirty_pages_pct阈值，InnoDB将快速地刷写脏页，尝试让脏页的数量更低。当事务日志没有足够的空间剩余时，InnoDB将进入“激烈刷写”模式，这就是大日志可以提升性能的一个原因。
如果不能快速预热，可以在重启后立即进行全表扫描或者索引扫描，把索引载入缓冲池。也可以使用init_file设置，把SQL放在一个文件里，然后当MySQL启动的时候来执行。
2、线程缓存
线程缓存保存那些当前没有与连接关联但是为后面新的连接服务的线程。当一个新的连接创建时，如果缓存中有线程存在，MySQL从缓存中删除一个线程，并且把它分配给这个新的连接。当连接关闭时，如果线程缓存还有空间的话，MySQL又会把线程放回缓存。如果没有空间的话，MySQL会销毁这个线程。只要MySQL在缓存里还有空闲的线程，它就可以迅速地响应连接请求，因为这样就不用为每个连接创建新的线程。
一个好的办法是观察Threads_connected变量并且尝试设置thread_cache_size足够大以便能处理业务压力正常的波动。每个在线程缓存中的线程或者休眠状态的线程，通常使用256K左右的内存。相对于正在处理查询的线程来说，这个内存不算很大。
3、表缓存
表缓存可以重用资源。当一个查询请求访问一张MYISAM表，MySQL可以从缓存的对象中获取到文件描述符，避免打开一个文件描述符的开销。对MYISAM表来说，表缓存的真正好处是可以让服务器避免修改MYISAM文件头来标记表“正在使用中”，表缓存对于InnoDB重要性就小得多，因为InnoDB不依赖它来做那么多事，例如持有文件描述符等。
4、InnoDB I/O配置事务日志
InnoDB使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无须在每个事务提交时把缓冲池的脏块刷新(flush)到磁盘中。事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机IO。InnoDB假设使用常规磁盘，随机IO比顺序IO昂贵得多，因为一个IO请求需要时间把磁头移到正确的位置，然后等待磁盘上读出需要的部分，再转到开始位置。
InnoDB用日志把随机IO变成顺序IO。一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB可以重放日志并且恢复已经提交的事务。
InnoDB使用一个后台线程智能地刷新这些变更到数据文件。这个线程可以批量组合写入，使得数据写入更顺序，以提高效率。
整体的日志文件大小受控于innodb_log_file_size和innodb_log_files_in_group两个参数，这对写性能非常重要。日志文件的总大小是每个文件的大小之和。
InnoDB使用多个文件作为一组循环日志。通常不需要修改默认的日志数量，只修改每个日志文件的大小即可。要修改日志文件大小，需要完全关闭MySQL，将旧的日志文件移到其他地方保存，重新配置参数。
要确定理想的日志文件大小，必须权衡正常数据变更的开销和崩溃恢复需要的时间，如果日志太小，InnoDB必然将做更多的检查点，导致更多的日志写。如果日志太大，在崩溃恢复时InnoDB可能不得不做大量的工作。
当InnoDB变更任何数据时，会写一条变更记录到内存日志缓冲区中。在缓冲满的时候，事务提交的时候，或者每一秒钟，这三个条件无论哪个先达到，InnoDB都会刷新缓冲区的内容到磁盘日志文件。变量innodb_log_buffer_size可以控制日志缓冲区的大小，默认为1M。通常不需要把日志缓冲区设置得非常大。推荐的范围是1~8M。作为一个经验法则，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。
InnoDB怎么刷新日志缓冲？当InnoDB把日志缓冲刷新到磁盘日志文件时，会先使用一个Mutex锁住缓冲区，刷新到所需要的位置，然后移动剩下的条目到缓冲区的前面。日志缓冲必须被刷新到持久化存储，以确保提交的事务完全被持久化了。如果和持久相比更在乎性能，可以修改innodb_flush_log_at_trx_commit变量来控制日志缓冲刷新的频繁程度。可能的设置如下：
0：每秒一次把日志缓冲写到日志文件，但是事务提交时不做任何时。
1：将日志缓冲写到日志文件，然后每次事务提交都刷新到持久化存储默认并且最安全的设置，该设置保证不会丢失任何已提交的事务。
2：每秒钟做一次刷新，但每次提交时把日志缓冲写到日志文件，但是不刷新到持久化存储。
“把日志缓冲写到日志文件”和“把日志刷新到持久化存储”是不同的。在大部分操作系统中，把缓冲写到日志只是简单地把数据从InnoDB的内存缓冲转移到了操作系统的缓存，也是在内存里，并没有真正把数据写到持久化存储。
如果MySQL崩溃了或者断电了，设置0和2通常会导致最多1秒的数据丢失，因为数据可能存在于操作系统的缓存中。
相反，把日志刷新到持久化存储意味着InnoDB请求操作系统把数据刷出缓存，并且确认写到磁盘了，这是一个阻塞IO的调用，直到数据被完全写回才会完成，当写数据到磁盘比较慢，而该配置项设置为1时，可能明显地降低InnoDB每秒可以提交的事务数。
5、InnoDB并发配置
InnoDB有自己的“线程调度器”控制线程怎么进入内核访问数据，以及它们在内核中一次可以做哪些事。最基本的限制并发方式是使用innodb_thread_concurrency变量，它会限制一次性可以有多少线程进入内核，0表示不限制。
理论上，可以参考下面的公式：并发值＝CPU数量*磁盘数量*2
另外，也可以通过线程池(Thread Pool)来限制并发。
6、优化排序Filesorts
如果查询中所有需要的列和order by的列总大小超过max_length_for_sort_data字节，则采用two-pass算法，否则采用single-pass算法。
7、其他配置项
1.max_connections：这个设置的作用就像一个紧急刹车，以保证服务器不会因应用程序激增的连接而不堪重负。如果应用程序有问题，或者服务器遇到如连接延迟的问题，会创建很多新连接，但是如果不能执行查询，那打开一个连接没有好处，所以被“太多的连接”的错误拒绝是一种快速而代价小的失败方式。
要时时小心可能遇到连接限制的突然袭击。例如，若重启应用服务器，可能没有把它的连接关闭干净，同时MySQL可能没有意识到它们已经被关闭了。当应用服务器重新开始运行，并试图打开到数据库的连接，就可能由于挂起的连接还没有超时，导致新连接被拒绝。观察max_used_connections状态变量随着时间的变化，如果这个值达到了max_connections，说明客户端至少被拒绝了一次。
2.thread_cahce_size:观察Threads_connected状态变量并且找到它在一般情况下的最大值和最小值。可以设置为波动范围2~3倍大小。但是也不用设置得非常大，因为保持大量等待连接的空闲线程并没有什么真正的用处。
也可以观察threads_created状态随时间的变化，如果这个值很大或者一直增长，这可能在告诉你，需要调大thread_cahce_size变量。查看threads_cached来看有多少线程已经在缓存中了。
另一个相关的状态变量是slow_launch_threads。这个状态如果很大，那么意味着某些情况延迟了连接分配新线程。一般来说可能是系统过载了，导致操作系统不能为新创建的线程调度CPU。
总之，如果使用的是InnoDB，最重要的配置项是以下两个：innodb_buffer_pool_size和innodb_log_file_size。
六、一条SQL语句上如何被MySQL架构中的各个组件操作执行的
本节分享自华为云社区，原作者：砖业洋。
1、单表查询 SQL 在 MySQL 架构中的各个组件的执行过程
简单用一张图说明下，MySQL 架构有哪些组件，接下来给大家用 SQL 语句分析，假如 SQL 语句是这样
SELECT class_no FROM student WHERE name = 'lcy' AND age > 18 GROUP BY class_no
其中 name 为索引，按照时间顺序来分析一下：
1.客户端：客户端（如 MySQL 命令行工具、Navicat、MySQL Workbench 或其他应用程序）发送 SQL 查询到 MySQL 服务器。
2.连接器：连接器负责与客户端建立连接、管理连接和维护连接。当客户端连接到 MySQL 服务器时，连接器验证客户端的用户名和密码，然后分配一个线程来处理客户端的请求。
3.查询缓存：查询缓存用于缓存先前执行过的查询及其结果。当收到新的查询请求时，MySQL 首先检查查询缓存中是否已有相同的查询及其结果。如果查询缓存中有匹配的查询结果将直接返回缓存的结果，而无需再次执行查询。但如果查询缓存中没有匹配的查询结果，MySQL 将继续执行查询。查询缓存在 MySQL8.0 中已被移除，不详细解释。
4.分析器：
解析查询语句，检查语法。
验证表名和列名的正确性。
生成查询树。
5.优化器：分析查询树，考虑各种执行计划，估算不同执行计划的成本，选择最佳的执行计划。在这个例子中，优化器可能会选择使用 name 索引进行查询，因为 name 是索引列。
6.执行器：根据优化器选择的执行计划，向存储引擎发送请求，获取满足条件的数据行。
7.存储引擎（如 InnoDB）：负责实际执行索引扫描，如在 student 表的 name 索引上进行等值查询，因查询全部列，涉及到回表访问磁盘。在访问磁盘之前，先检查 InnoDB 的缓冲池（Buffer Pool）中是否已有所需的数据页。如果缓冲池中有符合条件的数据页，直接使用缓存的数据。如果缓冲池中没有所需的数据页，从磁盘加载数据页到缓冲池中。
8.执行器：对于每个找到的记录，再次判断记录是否满足索引条件 name。这是因为基于索引条件加载到内存中是数据页，数据页中也有可能包含不满足索引条件的记录，所以还要再判断一次 name 条件，满足 name 条件则继续判断 age > 18 过滤条件。根据 class_no 对满足条件的记录进行分组。执行器将处理后的结果集返回给客户端。
在整个查询执行过程中，这些组件共同协作以高效地执行查询。客户端负责发送查询，连接器管理客户端连接，查询缓存尝试重用先前查询结果，解析器负责解析查询，优化器选择最佳执行计划，执行器执行优化器选择的计划，存储引擎（如 InnoDB）负责管理数据存储和访问。这些组件的协同作用使得 MySQL 能够高效地执行查询并返回结果集。根据索引列过滤条件加载索引的数据页到内存这个操作是存储引擎做的。加载到内存中之后，执行器会进行索引列和非索引列的过滤条件判断。
2、SELECT 的各个关键字在哪里执行
根据执行顺序，如下：
（1）FROM：FROM 子句用于指定查询所涉及的数据表。在查询执行过程中，执行器需要根据优化器选择的执行计划从存储引擎中获取指定表的数据。
（2）ON：ON 子句用于指定连接条件，它通常与 JOIN 子句一起使用。在查询执行过程中，执行器会根据 ON 子句中的条件从存储引擎获取满足条件的记录。如果连接条件涉及到索引列，存储引擎可能会使用索引进行优化。
（3）JOIN：JOIN 子句用于指定表之间的连接方式（如 INNER JOIN, LEFT JOIN 等）。在查询执行过程中，执行器会根据优化器选择的执行计划，从存储引擎中获取需要连接的表的数据。然后，执行器根据 JOIN 子句的类型和 ON 子句中的连接条件，对数据进行连接操作。
（4）WHERE：执行器对从存储引擎返回的数据进行过滤，只保留满足 WHERE 子句条件的记录。部分过滤条件如果涉及到索引，在存储引擎层就已经进行了过滤。
（5）GROUP BY：执行器对满足 WHERE 子句条件的记录按照 GROUP BY 子句中指定的列进行分组。
（6）HAVING：执行器在进行分组后，根据 HAVING 子句条件对分组后的记录进行进一步过滤。
（7）SELECT：执行器根据优化器选择的执行计划来获取查询结果。
（8）DISTINCT：执行器对查询结果进行去重，只返回不重复的记录。
（9）ORDER BY：执行器对查询结果按照 ORDER BY 子句中指定的列进行排序。
（10）LIMIT：执行器根据 LIMIT 子句中指定的限制条件对查询结果进行截断，只返回部分记录
3、表关联查询 SQL 在 MySQL 架构中的各个组件的执行过程
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;
这个例子中，student_id 和 subject 是联合索引，age 是索引。按照时间顺序来分析一下：
1.连接器：当客户端连接到 MySQL 服务器时，连接器负责建立和管理连接。它验证客户端提供的用户名和密码，确定客户端具有相应的权限，然后建立连接。
2.查询缓存：MySQL 服务器在处理查询之前，会先检查查询缓存。如果查询缓存中已经存在相同的查询及其结果集，服务器将直接返回缓存中的结果，而不再执行后续的查询处理。由于查询缓存在 MySQL 8.0 中已被移除，我们在这个示例中不再详细讨论。
3.解析器：解析器的主要任务是解析 SQL 查询语句，确保查询语法正确。解析器会将查询语句分解成多个组成部分，例如表、列、条件等。在这个示例中，解析器会识别出涉及的表（student 和 score）以及需要的列（id、name、age、subject、score）。
4.优化器：优化器的职责是根据解析器提供的信息生成执行计划。它会分析多种可能的执行策略，并选择成本最低的策略。在这个示例中，优化器可能会分析各种表扫描和索引扫描的组合，最终选择一种成本最低的执行计划。
5.执行器：根据优化器生成的执行计划处理查询，向存储引擎发送请求，获取满足条件的数据行。
6.存储引擎（如 InnoDB）：存储引擎负责管理数据的存储和检索。
存储引擎首先接收来自执行器的请求。请求可能包括获取满足查询条件的数据行，以及使用哪种扫描方法（如全表扫描或索引扫描）。
假设执行器已经决定使用索引扫描。在这个示例中，存储引擎可能会先对 student 表进行索引扫描（使用 age 索引），然后对 score 表进行索引扫描（使用 student_id 和 subject 的联合索引）。
存储引擎会根据请求查询相应的索引结构。在 student 表中，存储引擎会找到满足 age > 18 条件的记录。在 score 表中，存储引擎会找到满足 subject = 'math' AND score > 80 条件的记录。一旦找到了满足条件的记录，存储引擎需要将这些记录所在的数据页从磁盘加载到内存中。存储引擎首先检查缓冲池（InnoDB Buffer Pool），看这些数据页是否已经存在于内存中。如果已经存在，则无需再次从磁盘加载。如果不存在，存储引擎会将这些数据页从磁盘加载到缓冲池中。
加载到缓冲池中的记录可以被多个查询共享，这有助于提高查询效率。
7.执行器：处理连接、排序、聚合、过滤等操作。
在内存中执行连接操作，将 student 表和 score 表的数据行连接起来。
对连接后的结果集进行过滤，只保留满足查询条件（age > 18、subject = 'math'、score > 80）的数据行。
将过滤后的数据行作为查询结果返回给客户端。
前面说过，根据存储引擎根据索引条件加载到内存的数据页有多数据，可能有不满足索引条件的数据，如果执行器不再次进行索引条件判断，则无法判断哪些记录满足索引条件的，虽然在存储引擎判断过了，但是在执行器还是会有索引条件 age > 18、subject = 'math'、score > 80 的判断。
4、LEFT JOIN 将过滤条件放在子查询中再关联和放在 WHERE 子句上有什么区别？
先看例子
查询1
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;
查询2
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM (SELECT id, name, age FROM student WHERE age > 18) s
LEFT JOIN (SELECT student_id, subject, score FROM score WHERE subject = 'math' AND score > 80) sc
ON s.id = sc.student_id
查询3
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id AND s.age > 18 AND sc.subject = 'math' AND sc.score > 80;
先给出结论：查询2 和 3 是一样的，也就是过滤条件放在子查询中和放在 on 上面是一样的，后面就只讨论查询1、2，查询1 和查询2 是不一样的，过滤条件放在 where 子句中和放在子查询再关联查询出的结果也是有区别的。
分析一下
从运行结果来看，对于查询1：
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;
在这个查询中，首先执行 LEFT JOIN，将 student 表和 score 表连接起来。连接操作是基于 s.id = sc.student_id 条件进行的。LEFT JOIN 操作会保留左表（student 表）中的所有行，即使它们在右表（score 表）中没有匹配的行。如果右表中没有匹配的行，那么右表的列将显示为 NULL。
然后，WHERE 子句会过滤连接后的结果集，只保留那些满足 s.age > 18 and sc.subject = 'math' and sc.score > 80 条件的行。这意味着，右表为 NULL 的记录将被排除，因为右表的过滤条件 sc.subject = 'math' and sc.score > 80 条件不满足。
对于查询2：
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM (select id, name, age from student where age > 18) s
LEFT JOIN (select subject, score from score where subject = 'math' AND score > 80) sc
ON s.id = sc.student_id
在这个查询中，首先执行两个子查询。第一个子查询从 student 表中选择所有 age > 18 的行，而第二个子查询从 score 表中选择所有 subject = 'math' and score > 80 的行。这意味着，在进行连接操作之前已经对两个表分别进行了过滤。
接下来，执行 LEFT JOIN 操作，将过滤后的 s 和 sc 子查询的结果集连接起来，基于 s.id = sc.student_id 条件。因为 LEFT JOIN 操作会保留左表（s 子查询的结果集）中的所有行，右表为 NULL 的记录包含了。
结果差异：
查询1和查询2的主要区别在于 WHERE 子句和子查询的使用。查询1 在连接操作后应用过滤条件，这可能导致右表为 NULL 的关联记录因为右表的过滤条件而被排除在外。而查询 2 在连接操作之前就已经过滤了表中的数据，这意味着查询结果会包含所有左表过滤条件的记录，以及右表过滤条件的记录和 NULL 的记录。
如果查询1 想保留右表为 NULL 的记录，只需要改为 WHERE s.age > 18 AND (sc.student_id is null OR (sc.subject = 'math' AND sc.score> 80)); 这样查询1 和 2 会有相同的结果集。分析一下这两个查询在 MySQL 架构中各个组件中执行的区别。
对于查询1：
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;
连接器：客户端与服务器建立连接。
查询缓存：检查缓存是否存在此查询的结果。如果有，直接返回结果。否则，继续执行。
解析器：解析查询语句，检查语法是否正确。
优化器：对查询进行优化，生成执行计划，决定连接和过滤条件的顺序等。
执行器：开始请求执行查询。
存储引擎（InnoDB）：从磁盘或者缓冲池读取满足条件的数据行（s.id = sc.student_id），因为是 left join，所以即便 sc.student_id 为 null 也会被关联。
执行器：将从存储引擎获取的数据行进行左连接，应用过滤条件 s.age > 18 and sc.subject = 'math' and sc.score > 80 进行过滤，将结果集返回给客户端。
当查询包含索引列的条件时，MySQL的存储引擎会首先利用索引在磁盘上定位到满足索引条件的记录。接着，将这些索引数据对应的数据页加载到内存中的缓冲池。然后，执行器在内存中对这些记录进行进一步的过滤，根据索引条件和非索引列的条件来过滤数据。当查询涉及到非聚集索引时，需要回表的操作会导致聚集索引和非聚集索引都被加载到内存中。但是，如果查询只涉及到聚集索引（如主键查询），那么只需要加载聚集索引的数据页即可。
对于查询2：
SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM (SELECT id, name, age FROM student WHERE age > 18) s
LEFT JOIN (SELECT student_id, subject, score FROM score WHERE subject = 'math' AND score > 80) sc
ON s.id = sc.student_id
连接器：客户端与服务器建立连接。
查询缓存：检查缓存是否存在此查询的结果。如果有，直接返回结果。否则，继续执行。
解析器：解析查询语句，检查语法是否正确。
优化器：决定使用哪些索引进行查询优化，以及确定连接顺序。
执行器：开始请求执行子查询。
存储引擎（InnoDB）：首先，对 student 表进行扫描，将满足条件 s.age > 18 的记录对应的数据页加载到缓冲池 (如果缓冲池没有这个页的数据)。然后，使用 subject = 'math' AND score > 80 对 score 表进行扫描，将满足条件的记录对应的数据页加载到缓冲池 (如果缓冲池没有这个页的数据)。
执行器：对从存储引擎获取的数据应用所有的过滤条件，过滤后的结果存入临时表，执行主查询，从临时表中获取数据，将 s 和 sc 进行左连接，根据 s.id = sc.student_id 组合结果。将连接后的结果返回给客户端。
从这里我们可以看出，查询2是先过滤后连接，每张表的索引都很重要，如果没设置好索引，单表过滤会全表扫描。写 SQL 的时候，查询1和查询2到底采用哪种方式呢？
根据不同情况各有应用场景，需要注意的是，对于查询2，子查询的结果集被存储在一个临时表中，临时表不会继承原始索引，包括聚集索引和非聚集索引，所以刚刚的例子中，临时表中 s.id 和 sc.student_id 已经不是任何索引列了。对于查询1，最终满足关联条件 s.id = sc.student_id 的所有记录都会被加载到内存后再进行过滤。
当单表过滤后的数据量较小时，查询2可能是一个更好的选择，因为它可以减少关联操作的数据量，从而提高查询效率。子查询阶段，MySQL 依然会利用原始表上的索引进行过滤。子查询执行完成后，将过滤后的数据存储在临时表中。所以查询2的方式可以优化的点就是在单表查询时尽可能的利用索引。
当单表过滤后的数据量较大时，查询1可能更合适，因为它可以更好地利用索引进行关联操作。这样可以减少关联操作的时间开销，查询2因为临时表不继承索引，表关联的时间开销比较大。
5、聚集索引和全表扫描有什么区别呢？
走 PRIMARY 索引（聚集索引）和全表扫描有什么区别呢？准确来说，使用 InnoDB 存储引擎的情况下，全表扫描的数据和聚集索引的数据在 InnoDB 表空间中的存储位置是相同的，也就是说它们的内存地址也是相同的。所以也可以理解为，他们其实都是在聚集索引上操作的（聚集索引 B+ 树的叶子结点是根据主键排好序的完整的用户记录，包含表里的所有字段），区别就在于：全表扫描将聚集索引 B+ 树的叶子结点从左到右依次顺序扫描并判断条件。
聚集索引是利用二分思想将聚集索引 B+ 树到指定范围区间进行扫描，比如 select * from demo_info where id in (1, 2) 这种条件字段是主键 id，可以很好的利用 PRIMARY 索引进行二分的快速查询。
在 MyISAM 中，全表扫描的数据和索引数据的存储位置是分开的。然而 MyISAM 已经被 InnoDB 取代，不再是 MySQL 的推荐存储引擎，从 MySQL5.5 开始，InnoDB 就成了 MySQL 的默认存储引擎。默认情况下，InnoDB 使用一个名为 ibdata1 的共享表空间文件存储所有的数据和索引，包括聚集索引和二级索引（又称非聚集索引或辅助索引）。
52条SQL语句性能优化策略
这里会提到 52 条 SQL 语句性能优化策略，能在日常使用中适用到大多数场景。
1、对查询进行优化，应尽量避免全表扫描，首先应考虑在 WHERE 及 ORDER BY 涉及的列上建立索引。
2、应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，创建表时 NULL 是默认值，但大多数时候应该使用 NOT NULL，或者使用一个特殊的值，如 0，-1 作为默认值。
3、应尽量避免在 WHERE 子句中使用 != 或 <> 操作符。MySQL 只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的 LIKE。
4、应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以使用 UNION 合并查询：select id from t where num=10 union all select id from t where num=20。
5、IN 和 NOT IN 也要慎用，否则会导致全表扫描。对于连续的数值，能用 BETWEEN 就不要用 IN：select id from t where num between 1 and 3。
6、下面的查询也将导致全表扫描：select id from t where name like'%abc%' 或者select id from t where name like'%abc'若要提高效率，可以考虑全文检索。而select id from t where name like'abc%'才用到索引。
7、如果在 WHERE 子句中使用参数，也会导致全表扫描。
8、应尽量避免在 WHERE 子句中对字段进行表达式操作，应尽量避免在 WHERE 子句中对字段进行函数操作。
9、很多时候用 EXISTS 代替 IN 是一个好的选择：select num from a where num in(select num from b)。用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)。
10、索引固然可以提高相应的 SELECT 的效率，但同时也降低了 INSERT 及 UPDATE 的效率。因为 INSERT 或 UPDATE 时有可能会重建索引，所以怎样重建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
11、应尽可能的避免更新 clustered 索引数据列， 因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
12、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
13、尽可能的使用 varchar, nvarchar 代替 char, nchar。因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
14、最好不要使用返回所有：select from t ，用具体的字段列表代替 "*"，不要返回用不到的任何字段。
15、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
16、使用表的别名(Alias)：当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个 Column 上。这样一来，就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。
17、使用"临时表"暂存中间结果 ：
简化 SQL 语句的重要方法就是采用临时表暂存中间结果。但是临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在 tempdb 中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中"共享锁"阻塞"更新锁"，减少了阻塞，提高了并发性能。
18、一些 SQL 查询语句应加上 nolock，读、写是会相互阻塞的，为了提高并发性能。对于一些查询，可以加上 nolock，这样读的时候可以允许写，但缺点是可能读到未提交的脏数据。
使用 nolock 有3条原则：
查询的结果用于"插、删、改"的不能加 nolock；
查询的表属于频繁发生页分裂的，慎用 nolock ；
使用临时表一样可以保存"数据前影"，起到类似 Oracle 的 undo 表空间的功能，能采用临时表提高并发性能的，不要用 nolock。
19、常见的简化规则如下：
不要有超过 5 个以上的表连接(JOIN)，考虑使用临时表或表变量存放中间结果。少用子查询，视图嵌套不要过深，一般视图嵌套不要超过 2 个为宜。
20、将需要查询的结果预先计算好放在表中，查询的时候再Select。这在SQL7.0以前是最重要的手段，例如医院的住院费计算。
21、用 OR 的字句可以分解成多个查询，并且通过 UNION 连接多个查询。他们的速度只同是否使用索引有关，如果查询需要用到联合索引，用 UNION all 执行的效率更高。多个 OR 的字句没有用到索引，改写成 UNION 的形式再试图与索引匹配。一个关键的问题是否用到索引。
22、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。
23、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。
存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的 SQL 语句，是控制流语言的集合，速度当然快。反复执行的动态 SQL，可以使用临时存储过程，该过程(临时表)被放在 Tempdb 中。
24、当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用配制线程数量< 最大连接数，启用 SQL SERVER 的线程池来解决，如果还是数量 = 最大连接数+5，严重的损害服务器的性能。
25、查询的关联同写的顺序 ：
select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' (A = B, B = '号码')
select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' (A = B, B = '号码', A = '号码')
select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' (B = '号码', A = '号码')
26、尽量使用 EXISTS 代替 select count(1) 来判断是否存在记录。count 函数只有在统计表中所有行数时使用，而且 count(1) 比 count(*) 更有效率。
27、尽量使用 ">="，不要使用 ">"。
28、索引的使用规范：
索引的创建要与应用结合考虑，建议大的 OLTP 表不要超过 6 个索引；
尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过 index index_name 来强制指定索引；
避免对大表查询时进行 table scan，必要时考虑新建索引；
在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用；
要注意索引的维护，周期性重建索引，重新编译存储过程。
29、下列 SQL 条件语句中的列都有恰当的索引，但执行速度却非常慢：
SELECT * FROM record WHERE substrINg(card_no, 1, 4) = '5378' --13秒
SELECT * FROM record WHERE amount/30 < 1000 --11秒
SELECT * FROM record WHERE convert(char(10), date, 112) = '19991201' --10秒
分析：
WHERE 子句中对列的任何操作结果都是在 SQL 运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引。
如果这些结果在查询编译时就能得到，那么就可以被 SQL 优化器优化，使用索引，避免表搜索，因此将 SQL 重写成下面这样：
SELECT * FROM record WHERE card_no like '5378%' -- < 1秒
SELECT * FROM record WHERE amount < 1000*30 -- < 1秒
SELECT * FROM record WHERE date = '1999/12/01' -- < 1秒
30、当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新。
31、在所有的存储过程中，能够用 SQL 语句的，我绝不会用循环去实现。
例如：列出上个月的每一天，我会用 connect by 去递归查询一下，绝不会去用循环从上个月第一天到最后一天。
32、选择最有效率的表名顺序(只在基于规则的优化器中有效)：
Oracle 的解析器按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表(基础表 driving table)将被最先处理，在 FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。
如果有 3 个以上的表连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指那个被其他表所引用的表。
33、提高 GROUP BY 语句的效率，可以通过将不需要的记录在 GROUP BY 之前过滤掉。下面两个查询返回相同结果，但第二个明显就快了许多。
低效：
SELECT JOB, AVG(SAL)
FROM EMP GROUP BY JOB
HAVING JOB = 'PRESIDENT'
OR JOB = 'MANAGER'
高效：
SELECT JOB, AVG(SAL)
FROM EMP
WHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER'
GROUP BY JOB
34、SQL 语句用大写，因为 Oracle 总是先解析 SQL 语句，把小写的字母转换成大写的再执行。
35、别名的使用，别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快 1.5 倍。
36、避免死锁，在你的存储过程和触发器中访问同一个表时总是以相同的顺序；事务应尽可能地缩短，在一个事务中应尽可能减少涉及到的数据量；永远不要在事务中等待用户输入。
37、避免使用临时表，除非确有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替。大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在 TempDb 数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。
38、最好不要使用触发器：
触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程；
如果能够使用约束实现的，尽量不要使用触发器；
不要为不同的触发事件(Insert、Update 和 Delete)使用相同的触发器；
不要在触发器中使用事务型代码。
39、索引创建规则：
表的主键、外键必须有索引；
数据量超过 300 的表应该有索引；
经常与其他表进行连接的表，在连接字段上应该建立索引；
经常出现在 WHERE 子句中的字段，特别是大表的字段，应该建立索引；
索引应该建在选择性高的字段上；
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
复合索引的建立需要进行仔细分析，尽量考虑用单字段索引代替；
正确选择复合索引中的主列字段，一般是选择性较好的字段；
复合索引的几个字段是否经常同时以 AND 方式出现在 WHERE 子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
如果复合索引中包含的字段经常单独出现在 WHERE 子句中，则分解为多个单字段索引；
如果复合索引所包含的字段超过 3 个，那么仔细考虑其必要性，考虑减少复合的字段；
如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
频繁进行数据操作的表，不要建立太多的索引；
删除无用的索引，避免对执行计划造成负面影响。
表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。尽量不要对数据库中某个含有大量重复的值的字段建立索引。
40、MySQL 查询优化总结：
使用慢查询日志去发现慢查询，使用执行计划去判断查询是否正常运行，总是去测试你的查询看看是否他们运行在最佳状态下。
久而久之性能总会变化，避免在整个表上使用 count(*)，它可能锁住整张表，使查询保持一致以便后续相似的查询可以使用查询缓存，在适当的情形下使用 GROUP BY 而不是 DISTINCT，在 WHERE、GROUP BY 和 ORDER BY 子句中使用有索引的列，保持索引简单，不在多个索引中包含同一个列。
有时候 MySQL 会使用错误的索引，对于这种情况使用 USE INDEX，检查使用 SQL_MODE=STRICT 的问题，对于记录数小于5的索引字段，在 UNION 的时候使用LIMIT不使用OR。
为了避免在更新前 SELECT，使用 INSERT ON DUPLICATE KEY 或者 INSERT IGNORE；不要用 UPDATE 去实现，不要使用 MAX；使用索引字段和 ORDER BY子句 LIMIT M，N 实际上可以减缓查询在某些情况下，有节制地使用，在 WHERE 子句中使用 UNION 代替子查询，在重新启动的 MySQL，记得来温暖你的数据库，以确保数据在内存和查询速度快，考虑持久连接，而不是多个连接，以减少开销。
基准查询，包括使用服务器上的负载，有时一个简单的查询可以影响其他查询，当负载增加在服务器上，使用 SHOW PROCESSLIST 查看慢的和有问题的查询，在开发环境中产生的镜像数据中测试的所有可疑的查询。
41、MySQL 备份过程：
从二级复制服务器上进行备份；
在进行备份期间停止复制，以避免在数据依赖和外键约束上出现不一致；
彻底停止 MySQL，从数据库文件进行备份；
如果使用 MySQL dump 进行备份，请同时备份二进制日志文件 – 确保复制没有中断；
不要信任 LVM 快照，这很可能产生数据不一致，将来会给你带来麻烦；
为了更容易进行单表恢复，以表为单位导出数据——如果数据是与其他表隔离的；
当使用 mysqldump 时请使用 –opt；
在备份之前检查和优化表；
为了更快的进行导入，在导入时临时禁用外键约束；
为了更快的进行导入，在导入时临时禁用唯一性检测；
在每一次备份后计算数据库，表以及索引的尺寸，以便更够监控数据尺寸的增长；
通过自动调度脚本监控复制实例的错误和延迟；
定期执行备份。
42、查询缓冲并不自动处理空格，因此，在写 SQL 语句时，应尽量减少空格的使用，尤其是在 SQL 首和尾的空格(因为查询缓冲并不自动截取首尾空格)。
43、member 用 mid 做标准进行分表方便查询么？一般的业务需求中基本上都是以 username 为查询依据，正常应当是 username 做 hash 取模来分表。
而分表的话 MySQL 的 partition 功能就是干这个的，对代码是透明的；在代码层面去实现貌似是不合理的。
44、我们应该为数据库里的每张表都设置一个 ID 作为其主键，而且最好的是一个 INT 型的(推荐使用 UNSIGNED)，并设置上自动增加的 AUTO_INCREMENT 标志。
45、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON，在结束时设置 SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
46、MySQL 查询可以启用高速查询缓存。这是提高数据库性能的有效MySQL优化方法之一。当同一个查询被执行多次时，从缓存中提取数据和直接从数据库中返回数据快很多。
47、EXPLAIN SELECT 查询用来跟踪查看效果：
使用 EXPLAIN 关键字可以让你知道 MySQL 是如何处理你的 SQL 语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的。
48、当只要一行数据时使用 LIMIT 1 ：
当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。
在这种情况下，加上 LIMIT 1 可以增加性能。这样一来，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 来定义整型字段，应该尽量把字段设置为 NOT NULL，这样在将来执行查询的时候，数据库不用去比较 NULL 值。
对于某些文本字段，例如"省份"或者"性别"，我们可以将它们定义为 ENUM 类型。因为在 MySQL 中，ENUM 类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
51、字符串数据类型：char, varchar, text 选择区别。
52、任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。