MySQL 优化 MySQL 优化MySQL 优化导入的优化insert 的优化group by 的优化order by 的优化优化嵌套查询count 的优化limit 分页的优化SQL 中 IN 包含的值不应该太多只需要一条数据的情况如果没有使用索引，就尽量减少排序尽量用 union all 来代替 unionwhere 条件优化查询时，尽量指定查询的字段名MySQL 分析表MySQL 检查表MySQL 优化表索引介绍索引分类索引使用查看索引的使用情况索引使用细则通过 show status 命令了解 SQL 执行次数定位执行效率较低的 SQL通过 EXPLAIN 命令分析 SQL 的执行计划SQL 优化步骤索引MySQL 分析表、检查表和优化表常用 SQL 优化一般传统互联网公司很少接触到 SQL 优化问题，其原因是数据量小，大部分厂商的数据库性能能够满足日常的业务需求，所以不需要进行 SQL 优化，但是随着应用程序的不断变大，数据量的激增，数据库自身的性能跟不上了，此时就需要从 SQL 自身角度来进行优化，这也是我们这篇文章所讨论的。SQL 优化步骤当面对一个需要优化的 SQL 时，我们有哪几种排查思路呢？通过 show status 命令了解 SQL 执行次数首先，我们可以使用 show status 命令查看服务器状态信息。show status 命令会显示每个服务器变量 variable_name 和 value，状态变量是只读的。如果使用 SQL 命令，可以使用 like 或者 where 条件来限制结果。like 可以对变量名做标准模式匹配。图我没有截全，下面还有很多变量，读者可以自己尝试一下。也可以在操作系统上使用 mysqladmin extended-status 命令来获取这些消息。但是我执行 mysqladmin extended-status 后，出现这个错误。应该是我没有输入密码的原因，使用 mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 extended-status 后，问题解决。这里需要注意一下 show status 命令中可以添加统计结果的级别，这个级别有两个session 级： 默认当前链接的统计结果global 级：自数据库上次启动到现在的统计结果如果不指定统计结果级别的话，默认使用 session 级别。对于 show status 查询出来的统计结果，有两类参数需要注意下，一类是以 Com_ 为开头的参数，一类是以 Innodb_ 为开头的参数。下面是 Com_ 为开头的参数，参数很多，我同样没有截全。Com_xxx 表示的是每个 xxx 语句执行的次数，我们通常关心的是 select 、insert 、update、delete 语句的执行次数，即Com_select：执行 select 操作的次数，一次查询会使结果 + 1。Com_insert：执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。Com_update：执行 UPDATE 操作的次数。Com_delete：执行 DELETE 操作的次数。以 Innodb_ 为开头的参数主要有Innodb_rows_read：执行 select 查询返回的行数。Innodb_rows_inserted：执行 INSERT 操作插入的行数。Innodb_rows_updated：执行 UPDATE 操作更新的行数。Innodb_rows_deleted：执行 DELETE 操作删除的行数。通过上面这些参数执行结果的统计，我们能够大致了解到当前数据库是以更新（包括插入、删除）为主还是查询为主。除此之外，还有一些其他参数用于了解数据库的基本情况。Connections：查询 MySQL 数据库的连接次数，这个次数是不管连接是否成功都算上。Uptime：服务器的工作时间。Slow_queries：满查询次数。Threads_connected：查看当前打开的连接的数量。下面这个博客汇总了几乎所有 show status 的参数，可以当作参考手册。https://blog.csdn.net/ayay_870621/article/details/88633092定位执行效率较低的 SQL定位执行效率比较慢的 SQL 语句，一般有两种方式可以通过慢查询日志来定位哪些执行效率较低的 SQL 语句。MySQL 中提供了一个慢查询的日志记录功能，可以把查询 SQL 语句时间大于多少秒的语句写入慢查询日志，日常维护中可以通过慢查询日志的记录信息快速准确地判断问题所在。用 --log-slow-queries 选项启动时，mysqld 会写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件，通过查看这个日志文件定位效率较低的 SQL 。比如我们可以在 my.cnf 中添加如下代码，然后退出重启 MySQL。log-slow-queries = /tmp/mysql-slow.loglong_query_time = 2通常我们设置最长的查询时间是 2 秒，表示查询时间超过 2 秒就记录了，通常情况下 2 秒就够了，然而对于很多 WEB 应用来说，2 秒时间还是比较长的。也可以通过命令来开启：我们先查询 MySQL 慢查询日志是否开启show variables like "%slow%";启用慢查询日志set global slow_query_log='ON';然后再次查询慢查询是否开启如图所示，我们已经开启了慢查询日志。慢查询日志会在查询结束以后才记录，所以在应用反应执行效率出现问题的时候慢查询日志并不能定位问题，此时应该使用 show processlist 命令查看当前 MySQL 正在进行的线程。包括线程的状态、是否锁表等，可以实时的查看 SQL 执行情况。同样，使用mysqladmin processlist语句也能得到此信息。下面就来解释一下各个字段对应的概念Id ：Id 就是一个标示，在我们使用 kill 命令杀死进程的时候很有用，比如 kill 进程号。User：显示当前的用户，如果不是 root，这个命令就只显示你权限范围内的 SQL 语句。Host：显示 IP ，用于追踪问题Db：显示这个进程目前连接的是哪个数据库，为 null 是还没有 select 数据库。Command：显示当前连接锁执行的命令，一般有三种：查询 query，休眠 sleep，连接 connect。Time：这个状态持续的时间，单位是秒State：显示当前 SQL 语句的状态，非常重要，下面会具体解释。Info：显示这个 SQL 语句。State 列非常重要，关于这个列的内容比较多，读者可以参考一下这篇文章https://blog.csdn.net/weixin_34357436/article/details/91768402这里面涉及线程的状态、是否锁表等选项，可以实时的查看 SQL 的执行情况，同时对一些锁表进行优化。通过 EXPLAIN 命令分析 SQL 的执行计划通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。比如我们使用下面这条 SQL 语句来分析一下执行计划explain select * from test1;上表中涉及内容如下select_type：表示常见的 SELECT 类型，常见的有 SIMPLE，SIMPLE 表示的是简单的 SQL 语句，不包括 UNION 或者子查询操作，比如下面这段就是 SIMPLE 类型。PRIMARY ，查询中最外层的 SELECT（如两表做 UNION 或者存在子查询的外层的表操作为 PRIMARY，内层的操作为 UNION），比如下面这段子查询。UNION，在 UNION 操作中，查询中处于内层的 SELECT（内层的 SELECT 语句与外层的 SELECT 语句没有依赖关系时）。SUBQUERY：子查询中首个SELECT（如果有多个子查询存在），如我们上面的查询语句，子查询第一个是 sr（sys_role）表，所以它的 select_type 是 SUBQUERY。table ，这个选项表示输出结果集的表。type，这个选项表示表的连接类型，这个选项很有深入研究的价值，因为很多 SQL 的调优都是围绕 type 来讲的，但是这篇文章我们主要围绕优化方式来展开的，type 这个字段我们暂时作为了解，这篇文章不过多深入。type 这个字段会牵扯到连接的性能，它的不同类型的性能由好到差分别是system ：表中仅有一条数据时，该表的查询就像查询常量表一样。const ：当表中只有一条记录匹配时，比如使用了表主键（primary key）或者表唯一索引（unique index）进行查询。eq-ref ：表示多表连接时使用表主键或者表唯一索引，比如select A.text, B.text where A.ID = B.ID这个查询语句，对于 A 表中的每一个 ID 行，B 表中都只能有唯一的 B.Id 来进行匹配时。ref ：这个类型不如上面的 eq-ref 快，因为它表示的是因为对于表 A 中扫描的每一行，表 C 中有几个可能的行，C.ID 不是唯一的。ref_or_null ：与 ref 类似，只不过这个选项包含对 NULL 的查询。index_merge ：查询语句使用了两个以上的索引，比如经常在有 and 和 or 关键字出现的场景，但是在由于读取索引过多导致其性能有可能还不如 range（后面说）。unique_subquery ：这个选项经常用在 in 关键字后面，子查询带有 where 关键字的子查询中，用 sql 来表示就是这样value IN (SELECT primary_key FROM single_table WHERE some_expr)range ：索引范围查询，常见于使用 =，<>，>，>=，<，<=，IS NULL，<=>，BETWEEN，IN() 或者 like 等运算符的查询中。index ：索引全表扫描，把索引从头到尾扫一遍。all ： 这个我们接触的最多了，就是全表查询，select * from xxx ，性能最差。上面就是 type 内容的大致解释，关于 type 我们经常会在 SQL 调优的环节使用 explain 分析其类型，然后改进查询方式，越靠近 system 其查询效率越高，越靠近 all 其查询效率越低。possible_keys ：表示查询时，可能使用的索引。key ：表示实际使用的索引。key_len ：索引字段的长度。rows ：扫描行的数量。filtered ：通过查询条件查询出来的 SQL 数量占用总行数的比例。extra ：执行情况的描述。通过上面的分析，我们可以大致确定 SQL 效率低的原因，一种非常有效的提升 SQL 查询效率的方式就是使用索引，接下来我会讲解一下如何使用索引提高查询效率。索引索引是数据库优化中最常用也是最重要的手段，通过使用不同的索引可以解决大多数 SQL 性能问题，也是面试经常会问到的优化方式，围绕着索引，面试官能让你造出火箭来，所以总结一点就是索引非常非常重！要！不只是使用，你还要懂其原！理！索引介绍索引的目的就是用于快速查找某一列的数据，对相关数据列使用索引能够大大提高查询操作的性能。不使用索引，MySQL 必须从第一条记录开始读完整个表，直到找出相关的行，表越大查询数据所花费的时间就越多。如果表中查询的列有索引，MySQL 能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。索引分类先来了解一下索引都有哪些分类。全局索引(FULLTEXT)：全局索引，目前只有 MyISAM 引擎支持全局索引，它的出现是为了解决针对文本的模糊查询效率较低的问题，并且只限于 CHAR、VARCHAR 和 TEXT 列。哈希索引(HASH)：哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构，很适合作为索引。HASH 索引具有一次定位的好处，不需要像树那样逐个节点查找，但是这种查找适合应用于查找单个键的情况，对于范围查找，HASH 索引的性能就会很低。默认情况下，MEMORY 存储引擎使用 HASH 索引，但也支持 BTREE 索引。B-Tree 索引：B 就是 Balance 的意思，BTree 是一种平衡树，它有很多变种，最常见的就是 B+ Tree，它被 MySQL 广泛使用。R-Tree 索引：R-Tree 在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种，相对于 B-Tree 来说，R-Tree 的优势在于范围查找。从逻辑上来对 MySQL 进行分类，主要分为下面这几种普通索引：普通索引是最基础的索引类型，它没有任何限制 。创建方式如下create index normal_index on cxuan003(id);删除方式drop index normal_index on cxuan003;唯一索引：唯一索引列的值必须唯一，允许有空值，如果是组合索引，则列值的组合必须唯一，创建方式如下create unique index normal_index on cxuan003(id);主键索引：是一种特殊的索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。CREATE TABLE \`table\` (         \`id\` int(11) NOT NULL AUTO_INCREMENT ,         \`title\` char(255) NOT NULL ,         PRIMARY KEY (\`id\`) )组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则，下面我们就会创建组合索引。全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较，目前只有 char、varchar，text 列上可以创建全文索引，创建表的适合添加全文索引CREATE TABLE \`table\` (    \`id\` int(11) NOT NULL AUTO_INCREMENT ,    \`title\` char(255) CHARACTER NOT NULL ,    \`content\` text CHARACTER NULL ,    \`time\` int(10) NULL DEFAULT NULL ,    PRIMARY KEY (\`id\`),     FULLTEXT (content) );当然也可以直接创建全局索引CREATE FULLTEXT INDEX index_content ON article(content)索引使用索引可以在创建表的时候进行创建，也可以单独创建，下面我们采用单独创建的方式，我们在 cxuan004 上创建前缀索引我们使用 explain 进行分析，可以看到 cxuan004 使用索引的情况如果不想使用索引，可以删除索引，索引的删除语法是索引使用细则我们在 cxuan005 上根据 id 和 hash 创建一个复合索引，如下所示create index id_hash_index on cxuan005(id,hash);然后根据 id 进行执行计划的分析explain select * from cxuan005 where id = '333';可以发现，即使 where 条件中使用的不是复合索引（Id 、hash），索引仍然能够使用，这就是索引的前缀特性。但是如果只按照 hash 进行查询的话，索引就不会用到。explain select * from cxuan005 where hash='8fd1f12575f6b39ee7c6d704eb54b353';如果 where 条件使用了 like 查询，并且 % 不在第一个字符，索引才可能被使用。对于复合索引来说，只能使用 id 进行 like 查询，因为 hash 列不管怎么查询都不会走索引。explain select * from cxuan005 where id like '%1';可以看到，如果第一个字符是 % ，则没有使用索引。explain select * from cxuan005 where id like '1%';如果使用了 % 号，就会触发索引。如果列名是索引的话，那么对列名进行 NULL 查询，将会触发索引。explain select * from cxuan005 where id is null;还有一些情况是存在索引但是 MySQL 并不会使用的情况。最简单的，如果使用索引后比不使用索引的效率还差，那么 MySQL 就不会使用索引。如果 SQL 中使用了 OR 条件，OR 前的条件列有索引，而后面的列没有索引的话，那么涉及到的索引都不会使用，比如 cxuan005 表中，只有 id 和 hash 字段有索引，而 info 字段没有索引，那么我们使用 or 进行查询。explain select * from cxuan005 where id = 111 and info = 'cxuan';我们从 explain 的执行结果可以看到，虽然 possible_keys 选项上仍然有 id_hash_index 索引，但是从 key、key_len 可以得知，这条 SQL 语句并未使用索引。在带有复合索引的列上查询不是第一列的数据，也不会使用索引。explain select * from cxuan005 where hash = '8fd1f12575f6b39ee7c6d704eb54b353';如果 where 条件的列参与了计算，那么也不会使用索引explain select * from cxuan005 where id + '111' = '666';索引列使用函数，一样也不会使用索引explain select * from cxuan005 where concat(id,'111') = '666';索引列使用了 like ，并且 % 位于第一个字符，则不会使用索引。在 order by 操作中，排序的列同时也在 where 语句中，将不会使用索引。当数据类型出现隐式转换时，比如 varchar 不加单引号可能转换为 int 类型时，会使索引无效，触发全表扫描。比如下面这两个例子能够显而易见的说明这一点在索引列上使用 IS NOT NULL 操作在索引字段上使用 <>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。关于设置索引但是索引没有生效的场景还有很多，这个需要小伙伴们工作中不断总结和完善，不过我上面总结的这些索引失效的情景，能够覆盖大多数索引失效的场景了。查看索引的使用情况在 MySQL 索引的使用过程中，有一个 Handler_read_key 值，这个值表示了某一行被索引值读的次数。 Handler_read_key 的值比较低的话，则表明增加索引得到的性能改善不是很理想，可能索引使用的频率不高。还有一个值是 Handler_read_rnd_next，这个值高则意味着查询运行效率不高，应该建立索引来进行抢救。这个值的含义是在数据文件中读下一行的请求数。如果正在进行大量的表扫描，Handler_read_rnd_next 的值比较高，就说明表索引不正确或写入的查询没有利用索引。MySQL 分析表、检查表和优化表对于大多数开发者来说，他们更倾向于解决简单 SQL的优化，而复杂 SQL 的优化交给了公司的 DBA 来做。下面就从普通程序员的角度和你聊几个简单的优化方式。MySQL 分析表分析表用于分析和存储表的关键字分布，分析的结果可以使得系统得到准确的统计信息，使得 SQL 生成正确的执行计划。如果用于感觉实际执行计划与预期不符，可以执行分析表来解决问题，分析表语法如下analyze table cxuan005;分析结果涉及到的字段属性如下Table：表示表的名称；Op：表示执行的操作，analyze 表示进行分析操作，check 表示进行检查查找，optimize 表示进行优化操作；Msg_type：表示信息类型，其显示的值通常是状态、警告、错误和信息这四者之一；Msg_text：显示信息。对表的定期分析可以改善性能，应该成为日常工作的一部分。因为通过更新表的索引信息对表进行分析，可改善数据库性能。MySQL 检查表数据库经常可能遇到错误，比如数据写入磁盘时发生错误，或是索引没有同步更新，或是数据库未关闭 MySQL 就停止了。遇到这些情况，数据就可能发生错误： Incorrect key file for table: ' '. Try to repair it. 此时，我们可以使用 Check Table 语句来检查表及其对应的索引。check table cxuan005;检查表的主要目的就是检查一个或者多个表是否有错误。Check Table 对 MyISAM 和 InnoDB 表有作用。Check Table 也可以检查视图的错误。MySQL 优化表MySQL 优化表适用于删除了大量的表数据，或者对包含 VARCHAR、BLOB 或则 TEXT 命令进行大量修改的情况。MySQL 优化表可以将大量的空间碎片进行合并，消除由于删除或者更新造成的空间浪费情况。它的命令如下optimize table cxuan005;我的存储引擎是 InnoDB 引擎，但是从图可以知道，InnoDB 不支持使用 optimize 优化，建议使用 recreate + analyze 进行优化。optimize 命令只对 MyISAM 、BDB 表起作用。常用 SQL 优化前面我们介绍了使用索引来优化 MySQL ，那么对于 SQL 的各种语法，句法来说，应该怎样优化呢？下面，我会从 SQL 命令的角度来聊一波 SQL 优化。导入的优化对于 MyISAM 类型的表，可以通过下面这种方式导入大量的数据ALTER TABLE tblname DISABLE KEYS; loading the dataALTER TABLE tblname ENABLE KEYS;这两个命令用来打开或者关闭 MyISAM 表非唯一索引的更新。在导入大量的数据到一个非空的 MyISAM 表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的 MyISAM 表，默认就是先导入数据然后才创建索引，所以不用进行设置。但是对于 InnoDB 搜索引擎的表来说，这样做不能提高导入效率，我们有以下几种方式可以提高导入的效率：因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果 InnoDB 表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。在导入数据前执行 SET UNIQUE_CHECKS = 0，关闭唯一性校验，在导入结束后执行SETUNIQUE_CHECKS = 1，恢复唯一性校验，可以提高导入的效率。如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT = 0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT = 1，打开自动提交，也可以提高导入的效率。insert 的优化当进行插入语句的时候，可以考虑采用下面这几种方式进行优化如果向同一张表插入多条数据的话，最好一次性插入，这样可以减少数据库建立连接 -\> 断开连接的时间，如下所示insert into test values(1,2),(1,3),(1,4)如果向不同的表插入多条数据，可以使用 insert delayed 语句提高执行效率。delayed 的含义是让 insert 语句马上执行，要么数据都会放在内存的队列中，并没有真正写入磁盘。对于 MyISAM 表来说，可以增加 bulk_insert_buffer_size 的值提高插入效率。最好将索引和数据文件在不同的磁盘上存放。group by 的优化在使用分组和排序的场景下，如果先进行 Group By 再进行 Order By 的话，可以指定 order by null 禁止排序，因为 order by null 可以避免 filesort ，filesort 往往很耗费时间。如下所示explain select id,sum(moneys) from sales2 group by id order by null;order by 的优化在执行计划中，经常可以看到 Extra 列出现了 filesort，filesort 是一种文件排序，这种排序方式比较慢，我们认为是不好的排序，需要进行优化。优化的方式是要使用索引。我们在 cxuan005 上创建一个索引。create index idx on cxuan005(id);然后我们使用查询字段和排序相同的顺序进行查询。explain select id from cxuan005 where id > '111' order by id;可以看到，在这次查询中，使用的是 Using index。这表明我们使用的是索引。如果创建索引和 order by 的顺序不一致，将会使用 Using filesort。explain select id from cxuan005 where id > '111' order by info;MySQL 支持两种方式的排序，filesort 和 index，Using index 是指 MySQL 扫描索引本身完成排序。index 效率高，filesort 效率低。order by 在满足下面这些情况下才会使用 indexorder by 语句使用索引最左前列。使用 where 子句与 order by 子句条件列组合满足索引最左前列。优化嵌套查询嵌套查询是我们经常使用的一种查询方式，这种查询方式可以使用 SELECT 语句来创建一个单独的查询结果，然后把这个结果当作嵌套语句的查询范围用在另一个查询语句中。使用时子查询可以将一个复杂的查询拆分成一个个独立的部分，逻辑上更易于理解以及代码的维护和重复使用。但是某些情况下，子查询的效率不高，一般使用 join 来替代子查询。使用嵌套查询的 SQL 语句进行 explain 分析如下explain select c05.id from cxuan005 c05 where id not in (select id from cxuan003);从 explain 的结果可以看出，主表的查询是 index ，子查询是 index_subquery ，这两个执行效率都不高。我们使用 join 来优化后的分析计划如下。explain select c05.id from cxuan005 c05 left join cxuan003 c03 on c05.id = c03.id;从 explain 分析结果可以看到，主表查询和子查询分别是 index 和 ref，而 ref 的执行效率相对较高，一般 type 的效率由高到低是 System-->const-->eq_ref-->ref--> fulltext-->ref_or_null-->index_merge-->unique_subquery-->index_subquery-->range-->index-->all 。count 的优化count 我们大家用的太多了，一般都用来统计某一列结果集的行数，当 MySQL 确认括号内的表达式不可能为空时，实际上就是在统计行数。其实 count 还有另一层统计方式：统计某个列值的数量，在统计列值数量的时候，它默认不会统计 NULL 值。我们经常犯的一个错误就是，在括号内指定一个列但是却希望统计结果集的行数。如果想要知道结果集行数的话，最好使用 count(*)。limit 分页的优化通常我们的系统会进行分页，一般情况下我们会使用 limit 加上偏移量来实现。同时还会加上 order by 语句进行排序。如果使用索引的情况下，效率一般不会有什么问题，如果没有使用索引的话，MySQL 就可能会做大量的文件排序操作。通常我们可能会遇到比如 limit 1000 , 50 这种情况，抛弃 1000 条，只取 50 条，这样的代价非常高，如果所有页面被访问的频率相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么限制分页的数量，要么优化大偏移量的性能。SQL 中 IN 包含的值不应该太多MySQL 中对 IN 做了相应的优化，MySQL 会将全部的常量存储在一个数组里面，如果数值较多，产生的消耗也会变大，比如select name from dual where num in(4,5,6)像这种 SQL 语句的话，能用 between 使用就不要再使用 in 了。只需要一条数据的情况如果只需要一条数据的情况下，推荐使用 limit 1，这样会使执行计划中的 type 变为 const。如果没有使用索引，就尽量减少排序尽量用 union all 来代替 unionunion 和 union all 的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。当然，union all 的前提条件是两个结果集没有重复数据。where 条件优化避免在 WHERE 字句中对字段进行 NULL 判断避免在 WHERE 中使用 != 或 <> 操作符不建议使用 % 前缀模糊查询，例如 LIKE “%name”或者LIKE “%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。避免在 where 中对字段进行表达式操作，比如 select user_id,user_project from table_name where age*2=36 就是一种表达式操作，建议改为 select user_id,user_project from table_name where age=36/2 建议在 where 子句中确定 column 的类型，避免 column 字段的类型和传入的参数类型不一致的时候发生的类型转换。查询时，尽量指定查询的字段名我们在日常使用 select 查询时，尽量使用 select 字段名 这种方式，避免直接 select * ，这样增加很多不必要的消耗（cpu、io、内存、网络带宽）；而且查询效率比较低。

最全 MySQL 优化方法，从此优化不再难 最全 MySQL 优化方法，从此优化不再难说起MySQL的查询优化，相信大家收藏了一堆奇技淫巧：不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧？是否理解其背后的工作原理？在实际场景下性能真有提升吗？我想未必。因而理解这些优化建议背后的原理就尤为重要，希望本文能让你重新审视这些优化建议，并在实际业务场景下合理的运用。MySQL逻辑架构如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图，有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。MySQL逻辑架构，来自：高性能MySQLMySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。MySQL查询过程我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。当向MySQL发送一个请求的时候，MySQL到底做了些什么呢？MySQL查询过程客户端/服务端通信协议MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。查询缓存在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果 都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：用多个小表代替一个大表，注意不要过度设计批量插入代替循环单条插入合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。当然查询缓存系统本身是非常复杂的，这里讨论的也只是很小的一部分，其他更深入的话题，比如：缓存是如何使用内存的？如何控制内存的碎片化？事务对查询缓存有何影响等等，读者可以自行阅读相关资料，这里权当抛砖引玉吧。语法解析和预处理MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。查询优化经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。mysql> select * from t_message limit 10; ...省略结果集 mysql> show status like 'last_query_cost'; +-----------------+-------------+ | Variable_name   | Value       | +-----------------+-------------+ | Last_query_cost | 6391.799000 | +-----------------+-------------+示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值，具体原理见下文）提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）随着MySQL的不断发展，优化器使用的优化策略也在不断的进化，这里仅仅介绍几个非常常用且容易理解的优化策略，其他的优化策略，大家自行查阅吧。查询执行引擎在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。返回结果给客户端查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等等。如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。回头总结一下MySQL整个查询执行过程，总的来说分为6个步骤：客户端向MySQL服务器发送一条查询请求服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段服务器进行SQL解析、预处理、再由优化器生成对应的执行计划MySQL根据执行计划，调用存储引擎的API来执行查询将结果返回给客户端，同时缓存查询结果性能优化建议看了这么多，你可能会期待给出一些优化手段，是的，下面会从3个不同方面给出一些优化建议。但请等等，还有一句忠告要先送给你：不要听信你看到的关于优化的“绝对真理”，包括本文所讨论的内容，而应该是在实际的业务场景下通过测试来验证你关于执行计划以及响应时间的假设。Scheme设计与数据类型优化选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。这里总结几个可能容易理解错误的技巧：通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。当然有一些奇技淫巧可以解决这个问题，有兴趣可自行查阅。创建高性能索引索引是提高MySQL查询性能的一个重要途径，但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用，从而影响应用程序的整体性能。应当尽量避免事后才想起添加索引，因为事后可能需要监控大量的SQL才能定位到问题所在，而且添加索引的时间肯定是远大于初始添加索引所需要的时间，可见索引的添加也是非常有技术含量的。接下来将向你展示一系列创建高性能索引的策略，以及每条策略其背后的工作原理。但在此之前，先了解与索引相关的一些算法和数据结构，将有助于更好的理解后文的内容。索引相关的数据结构和算法通常我们所说的索引是指B-Tree索引，它是目前关系型数据库中查找数据最为常用和有效的索引，大多数存储引擎都支持这种索引。使用B-Tree这个术语，是因为MySQL在CREATE TABLE或其它语句中使用了这个关键字，但实际上不同的存储引擎可能使用不同的数据结构，比如InnoDB就是使用的B+Tree。B+Tree中的B是指balance，意为平衡。需要注意的是，B+树索引并不能找到一个给定键值的具体行，它找到的只是被查找数据行所在的页，接着数据库会把页读入到内存，再在内存中进行查找，最后得到要查找的数据。在介绍B+Tree前，先了解一下二叉查找树，它是一种经典的数据结构，其左子树的值总是小于根的值，右子树的值总是大于根的值，如下图①。如果要在这课树中查找值为5的记录，其大致流程：先找到根，其值为6，大于5，所以查找左子树，找到3，而5大于3，接着找3的右子树，总共找了3次。同样的方法，如果查找值为8的记录，也需要查找3次。所以二叉查找树的平均查找次数为(3 + 3 + 3 + 2 + 2 + 1) / 6 = 2.3次，而顺序查找的话，查找值为2的记录，仅需要1次，但查找值为8的记录则需要6次，所以顺序查找的平均查找次数为：(1 + 2 + 3 + 4 + 5 + 6) / 6 = 3.3次，因此大多数情况下二叉查找树的平均查找速度比顺序查找要快。二叉查找树和平衡二叉树由于二叉查找树可以任意构造，同样的值，可以构造出如图②的二叉查找树，显然这棵二叉树的查询效率和顺序查找差不多。若想二叉查找数的查询性能最高，需要这棵二叉查找树是平衡的，也即平衡二叉树（AVL树）。平衡二叉树首先需要符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度差不能大于1。显然图②不满足平衡二叉树的定义，而图①是一课平衡二叉树。平衡二叉树的查找性能是比较高的（性能最好的是最优二叉树），查询性能越好，维护的成本就越大。比如图①的平衡二叉树，当用户需要插入一个新的值9的节点时，就需要做出如下变动。平衡二叉树旋转通过一次左旋操作就将插入后的树重新变为平衡二叉树是最简单的情况了，实际应用场景中可能需要旋转多次。至此我们可以考虑一个问题，平衡二叉树的查找效率还不错，实现也非常简单，相应的维护成本还能接受，为什么MySQL索引不直接使用平衡二叉树？随着数据库中数据的增加，索引本身大小随之增加，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级。可以想象一下一棵几百万节点的二叉树的深度是多少？如果将这么大深度的一颗二叉树放磁盘上，每读取一个节点，需要一次磁盘的I/O读取，整个查找的耗时显然是不能够接受的。那么如何减少查找过程中的I/O存取次数？一种行之有效的解决方法是减少树的深度，将二叉树变为m叉树（多路搜索树），而B+Tree就是一种多路搜索树。理解B+Tree时，只需要理解其最重要的两个特征即可：第一，所有的关键字（可以理解为数据）都存储在叶子节点（Leaf Page），非叶子节点（Index Page）并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。其次，所有的叶子节点由指针连接。如下图为高度为2的简化了的B+Tree。简化B+Tree怎么理解这两个特征？MySQL将每个节点的大小设置为一个页的整数倍（原因下文会介绍），也就是在节点空间大小一定的情况下，每个节点可以存储更多的内结点，这样每个结点能索引的范围更大更精确。所有的叶子节点使用指针链接的好处是可以进行区间访问，比如上图中，如果查找大于20而小于30的记录，只需要找到节点20，就可以遍历指针依次找到25、30。如果没有链接指针的话，就无法进行区间查找。这也是MySQL使用B+Tree作为索引存储结构的重要原因。MySQL为何将节点大小设置为页的整数倍，这就需要理解磁盘的存储原理。磁盘本身存取就比主存慢很多，在加上机械运动损耗（特别是普通的机械硬盘），磁盘的存取速度往往是主存的几百万分之一，为了尽量减少磁盘I/O，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存，预读的长度一般为页的整数倍。页是计算机管理存储器的逻辑块，硬件及OS往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（许多OS中，页的大小通常为4K）。主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后一起返回，程序继续运行。MySQL巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了读取一个节点只需一次I/O。假设B+Tree的高度为h，一次检索最多需要h-1次I/O（根节点常驻内存），复杂度O(h) = O(logmN)。实际应用场景中，M通常较大，常常超过100，因此树的高度一般都比较小，通常不超过3。最后简单了解下B+Tree节点的操作，在整体上对索引的维护有一个大概的了解，虽然索引可以大大提高查询效率，但维护索引仍要花费很大的代价，因此合理的创建索引也就尤为重要。仍以上面的树为例，我们假设每个节点只能存储4个内节点。首先要插入第一个节点28，如下图所示。leaf page和index page都没有满接着插入下一个节点70，在Index Page中查询后得知应该插入到50 - 70之间的叶子节点，但叶子节点已满，这时候就需要进行也分裂的操作，当前的叶子节点起点为50，所以根据中间值来拆分叶子节点，如下图所示。Leaf Page拆分最后插入一个节点95，这时候Index Page和Leaf Page都满了，就需要做两次拆分，如下图所示。Leaf Page与Index Page拆分拆分后最终形成了这样一颗树。最终树B+Tree为了保持平衡，对于新插入的值需要做大量的拆分页操作，而页的拆分需要I/O操作，为了尽可能的减少页的拆分操作，B+Tree也提供了类似于平衡二叉树的旋转功能。当Leaf Page已满但其左右兄弟节点没有满的情况下，B+Tree并不急于去做拆分操作，而是将记录移到当前所在页的兄弟节点上。通常情况下，左兄弟会被先检查用来做旋转操作。就比如上面第二个示例，当插入70的时候，并不会去做页拆分，而是左旋操作。左旋操作通过旋转操作可以最大限度的减少页分裂，从而减少索引维护过程中的磁盘的I/O操作，也提高索引维护效率。需要注意的是，删除节点跟插入节点类似，仍然需要旋转和拆分操作，这里就不再说明。高性能策略通过上文，相信你对B+Tree的数据结构已经有了大致的了解，但MySQL中索引是如何组织数据的存储呢？以一个简单的示例来说明，假如有如下数据表：CREATE TABLE People(    last_name varchar(50) not null,    first_name varchar(50) not null,    dob date not null,    gender enum(\`m\`,\`f\`) not null,    key(last_name,first_name,dob) );对于表中每一行数据，索引中包含了last_name、first_name、dob列的值，下图展示了索引是如何组织数据存储的。索引如何组织数据存储，来自：高性能MySQL可以看到，索引首先根据第一个字段来排列顺序，当名字相同时，则根据第三个字段，即出生日期来排序，正是因为这个原因，才有了索引的“最左原则”。1、MySQL不会使用索引的情况：非独立的列“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。比如：select * from where id + 1 = 5我们很容易看出其等价于 id = 4，但是MySQL无法自动解析这个表达式，使用函数是同样的道理。2、前缀索引如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率。3、多列索引和索引顺序在多数情况下，在多个列上建立独立的索引并不能提高查询性能。理由非常简单，MySQL不知道选择哪个索引的查询效率更好，所以在老版本，比如MySQL5.0之前就会随便选择一个列的索引，而新的版本会采用合并索引的策略。举个简单的例子，在一张电影演员表中，在actor_id和film_id两个列上都建立了独立的索引，然后有如下查询：select film_id,actor_id from film_actor where actor_id = 1 or film_id = 1老版本的MySQL会随机选择一个索引，但新版本做如下的优化：select film_id,actor_id from film_actor where actor_id = 1   union all  select film_id,actor_id from film_actor where film_id = 1 and actor_id <> 1当出现多个索引做相交操作时（多个AND条件），通常来说一个包含所有相关列的索引要优于多个独立索引。当出现多个索引做联合操作时（多个OR条件），对结果集的合并、排序等操作需要耗费大量的CPU和内存资源，特别是当其中的某些索引的选择性不高，需要返回合并大量数据时，查询成本更高。所以这种情况下还不如走全表扫描。因此explain时如果发现有索引合并（Extra字段出现Using union），应该好好检查一下查询和表结构是不是已经是最优的，如果查询和表都没有问题，那只能说明索引建的非常糟糕，应当慎重考虑索引是否合适，有可能一个包含所有相关列的多列索引更适合。前面我们提到过索引如何组织数据存储的，从图中可以看到多列索引时，索引的顺序对于查询是至关重要的，很明显应该把选择性更高的字段放到索引的前面，这样通过第一个字段就可以过滤掉大多数不符合条件的数据。索引选择性是指不重复的索引值和数据表的总记录数的比值，选择性越高查询效率越高，因为选择性越高的索引可以让MySQL在查询时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。理解索引选择性的概念后，就不难确定哪个字段的选择性较高了，查一下就知道了，比如：SELECT * FROM payment where staff_id = 2 and customer_id = 584是应该创建(staff_id,customer_id)的索引还是应该颠倒一下顺序？执行下面的查询，哪个字段的选择性更接近1就把哪个字段索引前面就好。select count(distinct staff_id)/count(*) as staff_id_selectivity,       count(distinct customer_id)/count(*) as customer_id_selectivity,       count(*) from payment多数情况下使用这个原则没有任何问题，但仍然注意你的数据中是否存在一些特殊情况。举个简单的例子，比如要查询某个用户组下有过交易的用户信息：select user_id from trade where user_group_id = 1 and trade_amount > 0MySQL为这个查询选择了索引(user_group_id,trade_amount)，如果不考虑特殊情况，这看起来没有任何问题，但实际情况是这张表的大多数数据都是从老系统中迁移过来的，由于新老系统的数据不兼容，所以就给老系统迁移过来的数据赋予了一个默认的用户组。这种情况下，通过索引扫描的行数跟全表扫描基本没什么区别，索引也就起不到任何作用。推广开来说，经验法则和推论在多数情况下是有用的，可以指导我们开发和设计，但实际情况往往会更复杂，实际业务场景下的某些特殊情况可能会摧毁你的整个设计。4、避免多个范围条件实际开发中，我们会经常使用多个范围条件，比如想查询某个时间段内登录过的用户：select user.* from user where login_time > '2017-04-01' and age between 18 and 30;这个查询有一个问题：它有两个范围条件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但无法同时使用它们。5、覆盖索引如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处：索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多6、使用索引扫描来排序MySQL有两种方式可以生产有序的结果集，其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的。如果explain的结果中type列的值为index表示使用了索引扫描来做排序。扫描索引本身很快，因为只需要从一条索引记录移动到相邻的下一条记录。但如果索引本身不能覆盖所有需要查询的列，那么就不得不每扫描一条索引记录就回表查询一次对应的行。这个读取操作基本上是随机I/O，因此按照索引顺序读取数据的速度通常要比顺序地全表扫描要慢。在设计索引时，如果一个索引既能够满足排序，又满足查询，是最好的。只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向也一样时，才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有ORDER BY子句引用的字段全部为第一张表时，才能使用索引做排序。ORDER BY子句和查询的限制是一样的，都要满足最左前缀的要求（有一种情况例外，就是最左的列被指定为常数，下面是一个简单的示例），其他情况下都需要执行排序操作，而无法利用索引排序。// 最左列为常数，索引：(date,staff_id,customer_id) select  staff_id,customer_id from demo where date = '2015-06-01' order by staff_id,customer_id7、冗余和重复索引冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应当尽量避免这种索引，发现后立即删除。比如有一个索引(A,B)，再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时，比如有人新建了索引(A,B)，但这个索引不是扩展已有的索引(A)。大多数情况下都应该尽量扩展已有的索引而不是创建新索引。但有极少情况下出现性能方面的考虑需要冗余索引，比如扩展已有索引而导致其变得过大，从而影响到其他使用该索引的查询。8、删除长期未使用的索引定期删除一些长时间未使用过的索引是一个非常好的习惯。关于索引这个话题打算就此打住，最后要说一句，索引并不总是最好的工具，只有当索引帮助提高查询速度带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，简单的全表扫描更高效。对于中到大型的表，索引就非常有效。对于超大型的表，建立和维护索引的代价随之增长，这时候其他技术也许更有效，比如分区表。最后的最后，explain后再提测是一种美德。特定类型查询优化优化COUNT()查询COUNT()可能是被大家误解最多的函数了，它有两种不同的作用，其一是统计某个列值的数量，其二是统计行数。统计列值时，要求列值是非空的，它不会统计NULL。如果确认括号中的表达式不可能为空时，实际上就是在统计行数。最简单的就是当使用COUNT(*)时，并不是我们所想象的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计行数。我们最常见的误解也就在这儿，在括号内指定了一列却希望统计结果是行数，而且还常常误以为前者的性能会更好。但实际并非这样，如果要统计行数，直接使用COUNT(*)，意义清晰，且性能更好。有时候某些业务场景并不需要完全精确的COUNT值，可以用近似值来代替，EXPLAIN出来的行数就是一个不错的近似值，而且执行EXPLAIN并不需要真正地去执行查询，所以成本非常低。通常来说，执行COUNT()都需要扫描大量的行才能获取到精确的数据，因此很难优化，MySQL层面还能做得也就只有覆盖索引了。如果不还能解决问题，只有从架构层面解决了，比如添加汇总表，或者使用redis这样的外部缓存系统。优化关联查询在大数据场景下，表与表之间通过一个冗余字段来关联，要比直接使用JOIN有更好的性能。如果确实需要使用关联查询的情况下，需要特别注意的是：确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器关联的顺序是A、B，那么就不需要在A表的对应列上创建索引。没有用到的索引会带来额外的负担，一般来说，除非有其他理由，只需要在关联顺序中的第二张表的相应列上创建索引（具体原因下文分析）。确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化。要理解优化关联查询的第一个技巧，就需要理解MySQL是如何执行关联查询的。当前MySQL关联执行的策略非常简单，它对任何的关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后在嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止。然后根据各个表匹配的行，返回查询中需要的各个列。太抽象了？以上面的示例来说明，比如有这样的一个查询：SELECT A.xx,B.yy  FROM A INNER JOIN B USING(c) WHERE A.xx IN (5,6)假设MySQL按照查询中的关联顺序A、B来进行关联操作，那么可以用下面的伪代码表示MySQL如何完成这个查询：outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6); outer_row = outer_iterator.next; while(outer_row) {    inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;    inner_row = inner_iterator.next;    while(inner_row) {        output\[inner_row.yy,outer_row.xx\];        inner_row = inner_iterator.next;    }    outer_row = outer_iterator.next; }可以看到，最外层的查询是根据A.xx列来查询的，A.c上如果有索引的话，整个关联查询也不会使用。再看内层的查询，很明显B.c上如果有索引的话，能够加速查询，因此只需要在关联顺序中的第二张表的相应列上创建索引即可。优化LIMIT分页当需要分页操作时，通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY字句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。一个常见的问题是当偏移量非常大的时候，比如：LIMIT 10000 20这样的查询，MySQL需要查询10020条记录然后只返回20条记录，前面的10000条都将被抛弃，这样的代价非常高。优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。对于偏移量很大时，这样做的效率会提升非常大。考虑下面的查询：SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;如果这张表非常大，那么这个查询最好改成下面的样子：SELECT film.film_id,film.description FROM film INNER JOIN (    SELECT film_id FROM film ORDER BY title LIMIT 50,5 ) AS tmp USING(film_id);这里的延迟关联将大大提升查询效率，让MySQL扫描尽可能少的页面，获取需要访问的记录后在根据关联列回原表查询所需要的列。有时候如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET，比如下面的查询：SELECT id FROM t LIMIT 10000, 10; 改为： SELECT id FROM t WHERE id > 10000 LIMIT 10;其他优化的办法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表中只包含主键列和需要做排序的列。优化UNIONMySQL处理UNION的策略是先创建临时表，然后再把各个查询结果插入到临时表中，最后再来做查询。因此很多优化策略在UNION查询中都没有办法很好的时候。经常需要手动将WHERE、LIMIT、ORDER BY等字句“下推”到各个子查询中，以便优化器可以充分利用这些条件先优化。除非确实需要服务器去重，否则就一定要使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高。当然即使使用ALL关键字，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。虽然很多时候没有这个必要，比如有时候可以直接把每个子查询的结果返回给客户端。结语理解查询是如何执行以及时间都消耗在哪些地方，再加上一些优化过程的知识，可以帮助大家更好的理解MySQL，理解常见优化技巧背后的原理。希望本文中的原理、示例能够帮助大家更好的将理论和实践联系起来，更多的将理论知识运用到实践中。其他也没啥说的了，给大家留两个思考题吧，可以在脑袋里想想答案，这也是大家经常挂在嘴边的，但很少有人会思考为什么？有非常多的程序员在分享时都会抛出这样一个观点：尽可能不要使用存储过程，存储过程非常不容易维护，也会增加使用成本，应该把业务逻辑放到客户端。既然客户端都能干这些事，那为什么还要存储过程？JOIN本身也挺方便的，直接查询就好了，为什么还需要视图呢？作者：CHEN川 链接：https://www.jianshu.com/p/d7665192aaaf 來源：简书

一个慢查询引发的思考之MySQL索引原理及慢查询优化 一个慢查询引发的思考之MySQL索引原理及慢查询优化背景MySQL凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。本人从2013年7月份起，一直在美团核心业务系统部做慢查询的优化工作，共计十余个系统，累计解决和积累了上百个慢查询案例。随着业务的复杂性提升，遇到的问题千奇百怪，五花八门，匪夷所思。本文旨在以开发工程师的角度来解释数据库索引的原理和如何优化慢查询。一个慢查询引发的思考select    count(*)  from    task  where    status=2     and operator_id=20839     and operate_time>1371169729     and operate_time<1371174603     and type=2;系统使用者反应有一个功能越来越慢，于是工程师找到了上面的SQL。并且兴致冲冲的找到了我，“这个SQL需要优化，给我把每个字段都加上索引”。我很惊讶，问道：“为什么需要每个字段都加上索引？”“把查询的字段都加上索引会更快”，工程师信心满满。“这种情况完全可以建一个联合索引，因为是最左前缀匹配，所以operate_time需要放到最后，而且还需要把其他相关的查询都拿来，需要做一个综合评估。”“联合索引？最左前缀匹配？综合评估？”工程师不禁陷入了沉思。多数情况下，我们知道索引能够提高查询效率，但应该如何建立索引？索引的顺序如何？许多人却只知道大概。其实理解这些概念并不难，而且索引的原理远没有想象的那么复杂。MySQL索引原理索引目的索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？索引原理除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。磁盘IO与预读前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：various-system-software-hardware-latencies考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。索引的数据结构前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。详解b+树b+树如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。b+树的查找过程如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。b+树性质1.通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。2.当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。慢查询优化关于MySQL索引原理是比较枯燥的东西，大家只需要有一个感性的认识，并不需要理解得非常透彻和深入。我们回头来看看一开始我们说的慢查询，了解完索引原理之后，大家是不是有什么想法呢？先总结一下索引的几大基本原则：建索引的几大原则1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。回到开始的慢查询根据最左匹配原则，最开始的sql语句的索引应该是status、operator_id、type、operate_time的联合索引；其中status、operator_id、type的顺序可以颠倒，所以我才会说，把这个表的所有相关查询都找到，会综合分析；比如还有如下查询：select * from task where status = 0 and type = 12 limit 10; `````` select count(*) from task where status = 0 ;那么索引建立成(status,type,operator_id,operate_time)就是非常正确的，因为可以覆盖到所有情况。这个就是利用了索引的最左匹配的原则查询优化神器 - explain命令关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。慢查询优化基本步骤0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）3.order by limit 形式的sql语句让排序的表优先查4.了解业务方使用场景5.加索引时参照建索引的几大原则6.观察结果，不符合预期继续从0分析几个慢查询案例下面几个例子详细解释了如何分析和优化慢查询。复杂语句写法很多情况下，我们写SQL只是为了实现功能，这只是第一步，不同的语句书写方式对于效率往往有本质的差别，这要求我们对mysql的执行计划和索引原则有非常清楚的认识，请看下面的语句：select    distinct cert.emp_id  from    cm_log cl  inner join    (      select          emp.id as emp_id,          emp_cert.id as cert_id        from          employee emp        left join          emp_certificate emp_cert              on emp.id = emp_cert.emp_id        where          emp.is_deleted=0    ) cert        on (          cl.ref_table='Employee'           and cl.ref_oid= cert.emp_id       )        or (          cl.ref_table='EmpCertificate'           and cl.ref_oid= cert.cert_id       )  where    cl.last_upd_date >='2013-11-07 15:03:00'     and cl.last_upd_date<='2013-11-08 16:00:00';0.先运行一下，53条记录 1.87秒，又没有用聚合语句，比较慢53 rows in set (1.87 sec)1.explain+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+| id | select_type | table      | type  | possible_keys                   | key                   | key_len | ref               | rows  | Extra                          | +----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+|  1 | PRIMARY     | cl         | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date     | 8       | NULL              |   379 | Using where; Using temporary   | |  1 | PRIMARY     | <derived2> | ALL   | NULL                            | NULL                  | NULL    | NULL              | 63727 | Using where; Using join buffer | |  2 | DERIVED     | emp        | ALL   | NULL                            | NULL                  | NULL    | NULL              | 13317 | Using where                    | |  2 | DERIVED     | emp_cert   | ref   | emp_certificate_empid           | emp_certificate_empid | 4       | meituanorg.emp.id |     1 | Using index                    | +----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+简述一下执行计划，首先mysql根据idx_last_upd_date索引扫描cm_log表获得379条记录；然后查表扫描了63727条记录，分为两部分，derived表示构造表，也就是不存在的表，可以简单理解成是一个语句形成的结果集，后面的数字表示语句的ID。derived2表示的是ID = 2的查询构造了虚拟表，并且返回了63727条记录。我们再来看看ID = 2的语句究竟做了写什么返回了这么大量的数据，首先全表扫描employee表13317条记录，然后根据索引emp_certificate_empid关联emp_certificate表，rows = 1表示，每个关联都只锁定了一条记录，效率比较高。获得后，再和cm_log的379条记录根据规则关联。从执行过程上可以看出返回了太多的数据，返回的数据绝大部分cm_log都用不到，因为cm_log只锁定了379条记录。如何优化呢？可以看到我们在运行完后还是要和cm_log做join,那么我们能不能之前和cm_log做join呢？仔细分析语句不难发现，其基本思想是如果cm_log的ref_table是EmpCertificate就关联emp_certificate表，如果ref_table是Employee就关联employee表，我们完全可以拆成两部分，并用union连接起来，注意这里用union，而不用union all是因为原语句有“distinct”来得到唯一的记录，而union恰好具备了这种功能。如果原语句中没有distinct不需要去重，我们就可以直接使用union all了，因为使用union需要去重的动作，会影响SQL性能。优化过的语句如下：select    emp.id  from    cm_log cl  inner join    employee emp        on cl.ref_table = 'Employee'        and cl.ref_oid = emp.id   where    cl.last_upd_date >='2013-11-07 15:03:00'     and cl.last_upd_date<='2013-11-08 16:00:00'     and emp.is_deleted = 0  unionselect    emp.id  from    cm_log cl  inner join    emp_certificate ec        on cl.ref_table = 'EmpCertificate'        and cl.ref_oid = ec.id   inner join    employee emp        on emp.id = ec.emp_id   where    cl.last_upd_date >='2013-11-07 15:03:00'     and cl.last_upd_date<='2013-11-08 16:00:00'     and emp.is_deleted = 04.不需要了解业务场景，只需要改造的语句和改造之前的语句保持结果一致5.现有索引可以满足，不需要建索引6.用改造后的语句实验一下，只需要10ms 降低了近200倍！+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+| id | select_type  | table      | type   | possible_keys                   | key               | key_len | ref                   | rows | Extra       | +----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+|  1 | PRIMARY      | cl         | range  | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8       | NULL                  |  379 | Using where | |  1 | PRIMARY      | emp        | eq_ref | PRIMARY                         | PRIMARY           | 4       | meituanorg.cl.ref_oid |    1 | Using where | |  2 | UNION        | cl         | range  | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8       | NULL                  |  379 | Using where | |  2 | UNION        | ec         | eq_ref | PRIMARY,emp_certificate_empid   | PRIMARY           | 4       | meituanorg.cl.ref_oid |    1 |             | |  2 | UNION        | emp        | eq_ref | PRIMARY                         | PRIMARY           | 4       | meituanorg.ec.emp_id  |    1 | Using where | | NULL | UNION RESULT | <union1,2> | ALL    | NULL                            | NULL              | NULL    | NULL                  | NULL |             | +----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+53 rows in set (0.01 sec)明确应用场景举这个例子的目的在于颠覆我们对列的区分度的认知，一般上我们认为区分度越高的列，越容易锁定更少的记录，但在一些特殊的情况下，这种理论是有局限性的。select    *  from    stage_poi sp  where    sp.accurate_result=1     and (       sp.sync_status=0        or sp.sync_status=2        or sp.sync_status=4    );0.先看看运行多长时间,951条数据6.22秒，真的很慢。951 rows in set (6.22 sec)1.先explain，rows达到了361万，type = ALL表明是全表扫描。+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+|  1 | SIMPLE      | sp    | ALL  | NULL          | NULL | NULL    | NULL | 3613155 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+2.所有字段都应用查询返回记录数，因为是单表查询 0已经做过了951条。3.让explain的rows 尽量逼近951。看一下accurate_result = 1的记录数：select count(*),accurate_result from stage_poi  group by accurate_result; +----------+-----------------+| count(*) | accurate_result | +----------+-----------------+|     1023 |              -1 | |  2114655 |               0 | |   972815 |               1 | +----------+-----------------+我们看到accurate_result这个字段的区分度非常低，整个表只有-1,0,1三个值，加上索引也无法锁定特别少量的数据。再看一下sync_status字段的情况：select count(*),sync_status from stage_poi  group by sync_status; +----------+-------------+| count(*) | sync_status | +----------+-------------+|     3080 |           0 | |  3085413 |           3 | +----------+-------------+同样的区分度也很低，根据理论，也不适合建立索引。问题分析到这，好像得出了这个表无法优化的结论，两个列的区分度都很低，即便加上索引也只能适应这种情况，很难做普遍性的优化，比如当sync_status 0、3分布的很平均，那么锁定记录也是百万级别的。4.找业务方去沟通，看看使用场景。业务方是这么来使用这个SQL语句的，每隔五分钟会扫描符合条件的数据，处理完成后把sync_status这个字段变成1,五分钟符合条件的记录数并不会太多，1000个左右。了解了业务方的使用场景后，优化这个SQL就变得简单了，因为业务方保证了数据的不平衡，如果加上索引可以过滤掉绝大部分不需要的数据。5.根据建立索引规则，使用如下语句建立索引alter table stage_poi add index idx_acc_status(accurate_result,sync_status);6.观察预期结果,发现只需要200ms，快了30多倍。952 rows in set (0.20 sec)我们再来回顾一下分析问题的过程，单表查询相对来说比较好优化，大部分时候只需要把where条件里面的字段依照规则加上索引就好，如果只是这种“无脑”优化的话，显然一些区分度非常低的列，不应该加索引的列也会被加上索引，这样会对插入、更新性能造成严重的影响，同时也有可能影响其它的查询语句。所以我们第4步调差SQL的使用场景非常关键，我们只有知道这个业务场景，才能更好地辅助我们更好的分析和优化查询语句。无法优化的语句select    c.id,    c.name,    c.position,    c.sex,    c.phone,    c.office_phone,    c.feature_info,    c.birthday,    c.creator_id,    c.is_keyperson,    c.giveup_reason,    c.status,    c.data_source,    from_unixtime(c.created_time) as created_time,    from_unixtime(c.last_modified) as last_modified,    c.last_modified_user_id   from    contact c   inner join    contact_branch cb        on  c.id = cb.contact_id   inner join    branch_user bu        on  cb.branch_id = bu.branch_id        and bu.status in (         1,      2)      inner join       org_emp_info oei           on  oei.data_id = bu.user_id           and oei.node_left >= 2875           and oei.node_right <= 10802           and oei.org_category = - 1      order by       c.created_time desc  limit 0 ,      10;还是几个步骤。0.先看语句运行多长时间，10条记录用了13秒，已经不可忍受。10 rows in set (13.06 sec)1.explain+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+| id | select_type | table | type   | possible_keys                       | key                     | key_len | ref                      | rows | Extra                                        | +----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+|  1 | SIMPLE      | oei   | ref    | idx_category_left_right,idx_data_id | idx_category_left_right | 5       | const                    | 8849 | Using where; Using temporary; Using filesort | |  1 | SIMPLE      | bu    | ref    | PRIMARY,idx_userid_status           | idx_userid_status       | 4       | meituancrm.oei.data_id   |   76 | Using where; Using index                     | |  1 | SIMPLE      | cb    | ref    | idx_branch_id,idx_contact_branch_id | idx_branch_id           | 4       | meituancrm.bu.branch_id  |    1 |                                              | |  1 | SIMPLE      | c     | eq_ref | PRIMARY                             | PRIMARY                 | 108     | meituancrm.cb.contact_id |    1 |                                              | +----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+从执行计划上看，mysql先查org_emp_info表扫描8849记录，再用索引idx_userid_status关联branch_user表，再用索引idx_branch_id关联contact_branch表，最后主键关联contact表。rows返回的都非常少，看不到有什么异常情况。我们在看一下语句，发现后面有order by + limit组合，会不会是排序量太大搞的？于是我们简化SQL，去掉后面的order by 和 limit，看看到底用了多少记录来排序。select   count(*)from    contact c   inner join    contact_branch cb        on  c.id = cb.contact_id   inner join    branch_user bu        on  cb.branch_id = bu.branch_id        and bu.status in (         1,      2)      inner join       org_emp_info oei           on  oei.data_id = bu.user_id           and oei.node_left >= 2875           and oei.node_right <= 10802           and oei.org_category = - 1  +----------+| count(*) | +----------+|   778878 | +----------+1 row in set (5.19 sec)发现排序之前居然锁定了778878条记录，如果针对70万的结果集排序，将是灾难性的，怪不得这么慢，那我们能不能换个思路，先根据contact的created_time排序，再来join会不会比较快呢？于是改造成下面的语句，也可以用straight_join来优化：select   c.id,   c.name,   c.position,   c.sex,   c.phone,   c.office_phone,   c.feature_info,   c.birthday,   c.creator_id,   c.is_keyperson,   c.giveup_reason,   c.status,   c.data_source,    from_unixtime(c.created_time) as created_time,    from_unixtime(c.last_modified) as last_modified,   c.last_modified_user_id    from    contact c  where    exists (       select         1        from          contact_branch cb         inner join          branch_user bu                     on  cb.branch_id = bu.branch_id                     and bu.status in (               1,            2)                inner join             org_emp_info oei                           on  oei.data_id = bu.user_id                           and oei.node_left >= 2875                           and oei.node_right <= 10802                           and oei.org_category = - 1                where             c.id = cb.contact_id           )        order by      c.created_time desc  limit 0 ,      10;验证一下效果 预计在1ms内，提升了13000多倍！10 rows in set (0.00 sec)本以为至此大工告成，但我们在前面的分析中漏了一个细节，先排序再join和先join再排序理论上开销是一样的，为何提升这么多是因为有一个limit！大致执行过程是：mysql先按索引排序得到前10条记录，然后再去join过滤，当发现不够10条的时候，再次去10条，再次join，这显然在内层join过滤的数据非常多的时候，将是灾难的，极端情况，内层一条数据都找不到，mysql还傻乎乎的每次取10条，几乎遍历了这个数据表！用不同参数的SQL试验下：select    sql_no_cache   c.id,    c.name,    c.position,    c.sex,    c.phone,    c.office_phone,    c.feature_info,    c.birthday,    c.creator_id,    c.is_keyperson,    c.giveup_reason,    c.status,    c.data_source,    from_unixtime(c.created_time) as created_time,    from_unixtime(c.last_modified) as last_modified,    c.last_modified_user_id     from    contact c    where    exists (      select          1               from          contact_branch cb                inner join          branch_user bu                                  on  cb.branch_id = bu.branch_id                                  and bu.status in (               1,            2)                          inner join             org_emp_info oei                                           on  oei.data_id = bu.user_id                                           and oei.node_left >= 2875                                           and oei.node_right <= 2875                                           and oei.org_category = - 1                          where             c.id = cb.contact_id                  )            order by       c.created_time desc  limit 0 ,      10; Empty set (2 min 18.99 sec)2 min 18.99 sec！比之前的情况还糟糕很多。由于mysql的nested loop机制，遇到这种情况，基本是无法优化的。这条语句最终也只能交给应用系统去优化自己的逻辑了。通过这个例子我们可以看到，并不是所有语句都能优化，而往往我们优化时，由于SQL用例回归时落掉一些极端情况，会造成比原来还严重的后果。所以，第一：不要指望所有语句都能通过SQL优化，第二：不要过于自信，只针对具体case来优化，而忽略了更复杂的情况。慢查询的案例就分析到这儿，以上只是一些比较典型的案例。我们在优化过程中遇到过超过1000行，涉及到16个表join的“垃圾SQL”，也遇到过线上线下数据库差异导致应用直接被慢查询拖死，也遇到过varchar等值比较没有写单引号，还遇到过笛卡尔积查询直接把从库搞死。再多的案例其实也只是一些经验的积累，如果我们熟悉查询优化器、索引的内部原理，那么分析这些案例就变得特别简单了。写在后面的话本文以一个慢查询案例引入了MySQL索引原理、优化慢查询的一些方法论;并针对遇到的典型案例做了详细的分析。其实做了这么长时间的语句优化后才发现，任何数据库层面的优化都抵不上应用系统的优化，同样是MySQL，可以用来支撑Google/FaceBook/Taobao应用，但可能连你的个人网站都撑不住。套用最近比较流行的话：“查询容易，优化不易，且写且珍惜！”参考文献：1.《高性能MySQL》 2.《数据结构与算法分析》

MySQL优化的五个维度，你学会了吗？ MySQL优化的五个维度，你学会了吗？面试官如果问你：你会从哪些维度进行MySQL性能优化?你会怎么回答?所谓的性能优化，一般针对的是MySQL查询的优化。既然是优化查询，我们自然要先知道查询操作要经过哪些环节，然后思考可以在哪些环节进行优化。SQL查询的环节下面从5个角度介绍一下MySQL优化的一些策略。1. 连接配置优化处理连接是MySQL客户端和MySQL服务端亲热的第一步，第一步都迈不好，也就别谈后来的故事了。既然连接是双方的事情，我们自然从服务端和客户端两个方面来进行优化喽。1.1 服务端配置服务端需要做的就是尽可能地多接受客户端的连接，或许你遇到过error 1040: Too many connections的错误?就是服务端的胸怀不够宽广导致的，格局太小!我们可以从两个方面解决连接数不够的问题：增加可用连接数，修改环境变量max_connections，默认情况下服务端的最大连接数为151个。mysql> show variables like 'max_connections';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 151   |+-----------------+-------+1 row in set (0.01 sec)1.2.3.4.5.6.7.及时释放不活动的连接，系统默认的客户端超时时间是28800秒(8小时)，我们可以把这个值调小一点。mysql> show variables like 'wait_timeout';+---------------+-------+| Variable_name | Value |+---------------+-------+| wait_timeout  | 28800 |+---------------+-------+1 row in set (0.01 sec)1.2.3.4.5.6.7.MySQL有非常多的配置参数，并且大部分参数都提供了默认值，默认值是MySQL作者经过精心设计的，完全可以满足大部分情况的需求，不建议在不清楚参数含义的情况下贸然修改。1.2 客户端优化客户端能做的就是尽量减少和服务端建立连接的次数，已经建立的连接能凑合用就凑合用，别每次执行个SQL语句都创建个新连接，服务端和客户端的资源都吃不消啊。解决的方案就是使用连接池来复用连接。常见的数据库连接池有DBCP、C3P0、阿里的Druid、Hikari，前两者用得很少了，后两者目前如日中天。但是需要注意的是连接池并不是越大越好，比如Druid的默认最大连接池大小是8，Hikari默认最大连接池大小是10，盲目地加大连接池的大小，系统执行效率反而有可能降低。为什么?对于每一个连接，服务端会创建一个单独的线程去处理，连接数越多，服务端创建的线程自然也就越多。而线程数超过CPU个数的情况下，CPU势必要通过分配时间片的方式进行线程的上下文切换，频繁的上下文切换会造成很大的性能开销。Hikari官方给出了一个PostgreSQL数据库连接池大小的建议值公式，CPU核心数*2+1。假设服务器的CPU核心数是4，把连接池设置成9就可以了。这种公式在一定程度上对其他数据库也是适用的，大家面试的时候可以吹一吹。2. 架构优化2.1 使用缓存系统中难免会出现一些比较慢的查询，这些查询要么是数据量大，要么是查询复杂(关联的表多或者是计算复杂)，使得查询会长时间占用连接。如果这种数据的实效性不是特别强(不是每时每刻都会变化，例如每日报表)，我们可以把此类数据放入缓存系统中，在数据的缓存有效期内，直接从缓存系统中获取数据，这样就可以减轻数据库的压力并提升查询效率。缓存的使用2.2 读写分离(集群、主从)项目的初期，数据库通常都是运行在一台服务器上的，用户的所有读写请求会直接作用到这台数据库服务器，单台服务器承担的并发量毕竟是有限的。针对这个问题，我们可以同时使用多台数据库服务器，将其中一台设置为为小组长，称之为master节点，其余节点作为组员，叫做slave。用户写数据只往master节点写，而读的请求分摊到各个slave节点上。这个方案叫做读写分离。给组长加上组员组成的小团体起个名字，叫集群。这就是集群注：很多开发者不满master-slave这种具有侵犯性的词汇(因为他们认为会联想到种族歧视、黑人奴隶等)，所以发起了一项更名运动。受此影响MySQL也会逐渐停用master、slave等术语，转而用source和replica替代，大家碰到的时候明白即可。使用集群必然面临一个问题，就是多个节点之间怎么保持数据的一致性。毕竟写请求只往master节点上发送了，只有master节点的数据是最新数据，怎么把对master节点的写操作也同步到各个slave节点上呢?主从技术来了!我在一条SQL更新语句是如何执行的?中粗浅地介绍了一下binlog日志，我直接搬过来了。binlog是实现MySQL主从功能的核心组件。master节点会将所有的写操作记录到binlog中，slave节点会有专门的I/O线程读取master节点的binlog，将写操作同步到当前所在的slave节点。主从这种集群的架构对减轻主数据库服务器的压力有非常好的效果，但是随着业务数据越来越多，如果某张表的数据量急剧增加，单表的查询性能就会大幅下降，而这个问题是读写分离也无法解决的，毕竟所有节点存放的是一模一样的数据啊，单表查询性能差，说的自然也是所有节点性能都差。这时我们可以把单个节点的数据分散到多个节点上进行存储，这就是分库分表。2.3 分库分表分库分表中的节点的含义比较宽泛，要是把数据库作为节点，那就是分库;如果把单张表作为节点，那就是分表。大家都知道分库分表分成垂直分库、垂直分表、水平分库和水平分表，但是每次都记不住这些概念，我就给大家详细说一说，帮助大家理解。2.3.1 垂直分库垂直分库在单体数据库的基础上垂直切几刀，按照业务逻辑拆分成不同的数据库，这就是垂直分库啦。垂直分库2.3.2 垂直分表垂直分表垂直分表就是在单表的基础上垂直切一刀(或几刀)，将一个表的多个字短拆成若干个小表，这种操作需要根据具体业务来进行判断，通常会把经常使用的字段(热字段)分成一个表，不经常使用或者不立即使用的字段(冷字段)分成一个表，提升查询速度。垂直分表拿上图举例：通常情况下商品的详情信息都比较长，而且查看商品列表时往往不需要立即展示商品详情(一般都是点击详情按钮才会进行显示)，而是会将商品更重要的信息(价格等)展示出来，按照这个业务逻辑，我们将原来的商品表做了垂直分表。2.3.3 水平分表把单张表的数据按照一定的规则(行话叫分片规则)保存到多个数据表上，横着给数据表来一刀(或几刀)，就是水平分表了。水平分表水平分表2.3.4 水平分库水平分库就是对单个数据库水平切一刀，往往伴随着水平分表。水平分库水平分库2.3.5 总结水平分，主要是为了解决存储的瓶颈;垂直分，主要是为了减轻并发压力。2.4 消息队列削峰通常情况下，用户的请求会直接访问数据库，如果同一时刻在线用户数量非常庞大，极有可能压垮数据库(参考明星出轨或公布恋情时微博的状态)。这种情况下可以通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，然后系统有条不紊地从消息队列中消费请求。队列削峰3. 优化器——SQL分析与优化处理完连接、优化完缓存等架构的事情，SQL查询语句来到了解析器和优化器的地盘了。在这一步如果出了任何问题，那就只能是SQL语句的问题了。只要你的语法不出问题，解析器就不会有问题。此外，为了防止你写的SQL运行效率低，优化器会自动做一些优化，但如果实在是太烂，优化器也救不了你了，只能眼睁睁地看着你的SQL查询沦为慢查询。3.1 慢查询慢查询就是执行地很慢的查询(这句话说得跟废话似的。。。)，只有知道MySQL中有哪些慢查询我们才能针对性地进行优化。因为开启慢查询日志是有性能代价的，因此MySQL默认是关闭慢查询日志功能，使用以下命令查看当前慢查询状态。mysql> show variables like 'slow_query%';+---------------------+--------------------------------------+| Variable_name       | Value                                |+---------------------+--------------------------------------+| slow_query_log      | OFF                                  || slow_query_log_file | /var/lib/mysql/9e74f9251f6c-slow.log |+---------------------+--------------------------------------+2 rows in set (0.00 sec)1.2.3.4.5.6.7.8.slow_query_log表示当前慢查询日志是否开启，slow_query_log_file表示慢查询日志的保存位置。除了上面两个变量，我们还需要确定“慢”的指标是什么，即执行超过多长时间才算是慢查询，默认是10S，如果改成0的话就是记录所有的SQL。mysql> show variables like '%long_query%';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)1.2.3.4.5.6.7.3.1.1 打开慢日志有两种打开慢日志的方式。修改配置文件my.cnf。此种修改方式系统重启后依然有效。# 是否开启慢查询日志 slow_query_log=ON#  long_query_time=2slow_query_log_file=/var/lib/mysql/slow.log1.2.3.4.5.动态修改参数(重启后失效)。mysql> set @@global.slow_query_log=1;Query OK, 0 rows affected (0.06 sec)mysql> set @@global.long_query_time=2;Query OK, 0 rows affected (0.00 sec)1.2.3.4.5.3.1.2 慢日志分析MySQL不仅为我们保存了慢日志文件，还为我们提供了慢日志查询的工具mysqldumpslow，为了演示这个工具，我们先构造一条慢查询：mysql> SELECT sleep(5);1.然后我们查询用时最多的1条慢查询：[root@iZ2zejfuakcnnq2pgqyzowZ ~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.logReading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.logCount: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost  SELECT sleep(N)1.2.3.4.5.其中，Count：表示这个SQL执行的次数。Time：表示执行的时间，括号中的是累积时间。Locks：表示锁定的时间，括号中的是累积时间。Rows：表示返回的记录数，括号中的是累积数。更多关于mysqldumpslow的使用方式，可以查阅官方文档，或者执行mysqldumpslow --help寻求帮助。3.2 查看运行中的线程我们可以运行show full processlist查看MySQL中运行的所有线程，查看其状态和运行时间，找到不顺眼的，直接kill。其中，Id：线程的唯一标志，可以使用Id杀死指定线程。User：启动这个线程的用户，普通账户只能查看自己的线程。Host：哪个ip和端口发起的连接。db：线程操作的数据库。Command：线程的命令。Time：操作持续时间，单位秒。State：线程的状态。Info：SQL语句的前100个字符。3.3 查看服务器运行状态使用SHOW STATUS查看MySQL服务器的运行状态，有session和global两种作用域，一般使用like+通配符进行过滤。-- 查看select的次数mysql> SHOW GLOBAL STATUS LIKE 'com_select';+---------------+--------+| Variable_name | Value  |+---------------+--------+| Com_select    | 168241 |+---------------+--------+1 row in set (0.05 sec)1.2.3.4.5.6.7.8.3.4 查看存储引擎运行信息SHOW ENGINE用来展示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息;事务的锁等待情况;线程信号量等待;文件IO请求;Buffer pool统计信息等等数据。例如：SHOW ENGINE INNODB STATUS;1.上面这条语句可以展示innodb存储引擎的当前运行的各种信息，大家可以据此找到MySQL当前的问题，限于篇幅不在此意义说明其中信息的含义，大家只要知道MySQL提供了这样一个监控工具就行了，等到需要的时候再来用就好。3.5 EXPLAIN执行计划通过慢查询日志我们可以知道哪些SQL语句执行慢了，可是为什么慢?慢在哪里呢?MySQL提供了一个执行计划的查询命令EXPLAIN，通过此命令我们可以查看SQL执行的计划，所谓执行计划就是：优化器会不会优化我们自己书写的SQL语句(比如外连接改内连接查询，子查询优化为连接查询...)、优化器针对此条SQL的执行对哪些索引进行了成本估算，并最终决定采用哪个索引(或者最终选择不用索引，而是全表扫描)、优化器对单表执行的策略是什么，等等等等。EXPLAIN在MySQL5.6.3之后也可以针对UPDATE、DELETE和INSERT语句进行分析，但是通常情况下我们还是用在SELECT查询上。这篇文章主要是从宏观上多个角度介绍MySQL的优化策略，因此这里不详细说明EXPLAIN的细节，之后单独成篇。3.6 SQL与索引优化3.6.1 SQL优化SQL优化指的是SQL本身语法没有问题，但是有实现相同目的的更好的写法。比如：使用小表驱动大表;用join改写子查询;or改成union。连接查询中，尽量减少驱动表的扇出(记录数)，访问被驱动表的成本要尽量低，尽量在被驱动表的连接列上建立索引，降低访问成本;被驱动表的连接列最好是该表的主键或者是唯一二级索引列，这样被驱动表的成本会降到更低。大偏移量的limit，先过滤再排序。针对最后一条举个简单的例子，下面两条语句能实现同样的目的，但是第二条的执行效率比第一条执行效率要高得多(存储引擎使用的是InnoDB)，大家感受一下：-- 1. 大偏移量的查询mysql> SELECT * FROM user_innodb LIMIT 9000000,10;Empty set (8.18 sec)-- 2.先过滤ID（因为ID使用的是索引），再limitmysql> SELECT * FROM user_innodb WHERE id > 9000000 LIMIT 10;Empty set (0.02 sec)1.2.3.4.5.6.7.3.6.2 索引优化为慢查询创建适当的索引是个非常常见并且非常有效的方法，但是索引是否会被高效使用又是另一门学问了。4. 存储引擎与表结构4.1 选择存储引擎一般情况下，我们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库性能要求精益求精的时候，存储引擎的选择也成为一个关键的影响因素。建议根据不同的业务选择不同的存储引擎，例如：查询操作、插入操作多的业务表，推荐使用MyISAM;临时表使用Memory;并发数量大、更新多的业务选择使用InnoDB;不知道选啥直接默认。4.2 优化字段字段优化的最终原则是：使用可以正确存储数据的最小的数据类型。4.2.1 整数类型MySQL提供了6种整数类型，分别是tinyintsmallintmediumintintintegerbigint不同的存储类型的最大存储范围不同，占用的存储的空间自然也不同。例如，是否被删除的标识，建议选用tinyint，而不是bigint。4.2.2 字符类型你是不是直接把所有字符串的字段都设置为varchar格式了?甚至怕不够，还会直接设置成varchar(1024)的长度?如果不确定字段的长度，肯定是要选择varchar，但是varchar需要额外的空间来记录该字段目前占用的长度;因此如果字段的长度是固定的，尽量选用char，这会给你节约不少的内存空间。4.2.3 非空非空字段尽量设置成NOT NULL，并提供默认值，或者使用特殊值代替NULL。因为NULL类型的存储和优化都会存在性能不佳的问题，具体原因在这里就不展开了。4.2.4 不要用外键、触发器和视图功能这也是「阿里巴巴开发手册」中提到的原则。原因有三个：降低了可读性，检查代码的同时还得查看数据库的代码;把计算的工作交给程序，数据库只做好存储的工作，并把这件事情做好;数据的完整性校验的工作应该由开发者完成，而不是依赖于外键，一旦用了外键，你会发现测试的时候随便删点垃圾数据都变得异常艰难。4.2.5 图片、音频、视频存储不要直接存储大文件，而是要存储大文件的访问地址。4.2.6 大字段拆分和数据冗余大字段拆分其实就是前面说过的垂直分表，把不常用的字段或者数据量较大的字段拆分出去，避免列数过多和数据量过大，尤其是习惯编写SELECT *的情况下，列数多和数据量大导致的问题会被严重放大!字段冗余原则上不符合数据库设计范式，但是却非常有利于快速检索。比如，合同表中存储客户id的同时可以冗余存储客户姓名，这样查询时就不需要再根据客户id获取用户姓名了。因此针对业务逻辑适当做一定程度的冗余也是一种比较好的优化技巧。5. 业务优化严格来说，业务方面的优化已经不算是MySQL调优的手段了，但是业务的优化却能非常有效地减轻数据库访问压力，这方面一个典型例子就是淘宝，下面举几个简单例子给大家提供一下思路：以往都是双11当晚开始买买买的模式，最近几年双11的预售战线越拉越长，提前半个多月就开始了，而且各种定金红包模式丛出不穷，这种方式叫做预售分流。这样做可以分流客户的服务请求，不必等到双十一的凌晨一股脑地集体下单;双十一的凌晨你或许想查询当天之外的订单，但是却查询失败;甚至支付宝里的小鸡的口粮都被延迟发放了，这是一种降级策略，集结不重要的服务的计算资源，用来保证当前最核心的业务;双十一的时候支付宝极力推荐使用花呗支付，而不是银行卡支付，虽然一部分考量是提高软件粘性，但是另一方面，使用余额宝实际使用的阿里内部服务器，访问速度快，而使用银行卡，需要调用银行接口，相比之下操作要慢了许多。MySQL优化的总结写到此就结束了，其中有不少细节没有提及，多少让我感觉这篇文章不完美。但是有些知识点掰开讲又太多了，不可能一下子全部写下，之后再好好写吧。

史上最全的MySQL高性能优化实战总结！ 史上最全的MySQL高性能优化实战总结！1.1 前言MySQL对于很多Linux从业者而言，是一个非常棘手的问题，多数情况都是因为对数据库出现问题的情况和处理思路不清晰。在进行MySQL的优化之前必须要了解的就是MySQL的查询过程，很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。今天给大家体验MySQL的优化实战，助你高薪之路顺畅。图 - MySQL查询过程1.2 优化的哲学优化有风险，涉足需谨慎1.2.1 优化可能带来的问题优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统。优化手段本来就有很大的风险，只不过你没能力意识到和预见到！任何的技术可以解决一个问题，但必然存在带来一个问题的风险！对于优化来说解决问题而带来的问题,控制在可接受的范围内才是有成果。保持现状或出现更差的情况都是失败！1.2.2 优化的需求稳定性和业务可持续性,通常比性能更重要！优化不可避免涉及到变更，变更就有风险！优化使性能变好，维持和变差是等概率事件！切记优化,应该是各部门协同，共同参与的工作，任何单一部门都不能对数据库进行优化！所以优化工作,是由业务需要驱使的！！！1.2.3 优化由谁参与 在进行数据库优化时，应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等，业务相关人员共同参与。1.3 优化思路1.3.1 优化什么 在数据库优化上有两个主要方面：即安全与性能。安全 ---\> 数据可持续性性能 ---\> 数据的高性能访问1.3.2 优化的范围有哪些存储、主机和操作系统方面:主机架构稳定性I/O规划及配置Swap交换分区OS内核参数和网络问题应用程序方面:应用程序稳定性SQL语句性能串行访问资源性能欠佳会话管理这个应用适不适合用MySQL数据库优化方面:内存数据库结构(物理&逻辑)实例配置说明：不管是在，设计系统，定位问题还是优化，都可以按照这个顺序执行。1.3.3 优化维度数据库优化维度有四个:硬件、系统配置、数据库表结构、SQL及索引优化选择优化成本:硬件>系统配置>数据库表结构>SQL及索引优化效果:硬件<系统配置<数据库表结构<SQL及索引1.4 优化工具有啥？1.4.1 数据库层面检查问题常用工具msyqladmin       #mysql客户端，可进行管理操作 mysqlshow        #功能强大的查看shell命令 show [SESSION | GLOBAL] variables  #查看数据库参数信息 SHOW [SESSION | GLOBAL] STATUS     #查看数据库的状态信息 information_schema                 #获取元数据的方法S SHOW ENGINE INNODB STATUS Innodb   #引擎的所有状态 SHOW PROCESSLIST    #查看当前所有连接session状态 explain             #获取查询语句的执行计划s how index           #查看表的索引信息 slow-log            #记录慢查询语句 mysqldumpslow       #分析slowlog文件的 不常用但好用的工具zabbix            #监控主机、系统、数据库（部署zabbix监控平台） pt-query-digest   #分析慢日志 mysqlslap         #分析慢日志 sysbench          #压力测试工具 mysql profiling             #统计数据库整体状态工具  Performance Schema mysql    #性能状态统计的数据 workbench      #管理、备份、监控、分析、优化工具（比较费资源） 1.4.2 数据库层面问题解决思路一般应急调优的思路：针对突然的业务办理卡顿，无法进行正常的业务处理！需要立马解决的场景！1、show processlist2、explain select id ,name from stu where name='clsn'; # ALL id name age sex select id,name from stu where id=2-1 函数 结果集>30; show index from table;3、通过执行计划判断，索引问题（有没有、合不合理）或者语句本身问题4、show status like '%lock%'; # 查询锁状态 kill SESSION_ID; # 杀掉有问题的session常规调优思路：针对业务周期性的卡顿，例如在每天10-11点业务特别慢，但是还能够使用，过了这段时间就好了。1、查看slowlog，分析slowlog，分析出查询慢的语句。2、按照一定优先级，进行一个一个的排查所有慢语句。3、分析top sql，进行explain调试，查看语句执行时间。4、调整索引或语句本身。1.4.3 系统层面cpu方面vmstat、sar top、htop、nmon、mpstat 内存free 、ps -aux 、 IO设备（磁盘、网络）iostat 、 ss 、 netstat 、 iptraf、iftop、lsof、 vmstat 命令说明：Procs：r显示有多少进程正在等待CPU时间。b显示处于不可中断的休眠的进程数量。在等待I/OMemory：swpd显示被交换到磁盘的数据块的数量。未被使用的数据块，用户缓冲数据块，用于操作系统的数据块的数量Swap：操作系统每秒从磁盘上交换到内存和从内存交换到磁盘的数据块的数量。s1和s0最好是0Io：每秒从设备中读入b1的写入到设备b0的数据块的数量。反映了磁盘I/OSystem：显示了每秒发生中断的数量(in)和上下文交换(cs)的数量Cpu：显示用于运行用户代码，系统代码，空闲，等待I/O的CPU时间iostat命令说明实例命令： iostat -dk 1 5iostat -d -k -x 5 （查看设备使用率（%util）和响应时间（await））tps：该设备每秒的传输次数。“一次传输”意思是“一次I/O请求”。多个逻辑请求可能会被合并为“一次I/O请求”。iops ：硬件出厂的时候，厂家定义的一个每秒最大的IO次数,"一次传输"请求的大小是未知的。kB_read/s：每秒从设备（drive expressed）读取的数据量；KB_wrtn/s：每秒向设备（drive expressed）写入的数据量；kB_read：读取的总数据量；kB_wrtn：写入的总数量数据量；这些单位都为Kilobytes。1.4.4 系统层面问题解决办法你认为到底负载高好，还是低好呢？在实际的生产中，一般认为 cpu只要不超过90%都没什么问题 。当然不排除下面这些特殊情况：问题一：cpu负载高，IO负载低内存不够磁盘性能差SQL问题 ------>去数据库层，进一步排查sql问题IO出问题了（磁盘到临界了、raid设计不好、raid降级、锁、在单位时间内tps过高）tps过高: 大量的小数据IO、大量的全表扫描问题二：IO负载高，cpu负载低大量小的IO 写操作：autocommit ，产生大量小IOIO/PS,磁盘的一个定值，硬件出厂的时候，厂家定义的一个每秒最大的IO次数。大量大的IO 写操作SQL问题的几率比较大问题三：IO和cpu负载都很高硬件不够了或sql存在问题1.5 基础优化1.5.1 优化思路定位问题点:硬件 --> 系统 --> 应用 --> 数据库 --\> 架构（高可用、读写分离、分库分表）处理方向明确优化目标、性能和安全的折中、防患未然1.5.2 硬件优化主机方面：根据数据库类型，主机CPU选择、内存容量选择、磁盘选择平衡内存和磁盘资源随机的I/O和顺序的I/O主机 RAID卡的BBU(Battery Backup Unit)关闭cpu的选择：cpu的两个关键因素：核数、主频根据不同的业务类型进行选择：cpu密集型：计算比较多，OLTP 主频很高的cpu、核数还要多IO密集型：查询比较，OLAP 核数要多，主频不一定高的内存的选择：OLAP类型数据库，需要更多内存，和数据获取量级有关。OLTP类型数据一般内存是cpu核心数量的2倍到4倍，没有最佳实践。存储方面：根据存储数据种类的不同，选择不同的存储设备配置合理的RAID级别(raid5、raid10、热备盘)对与操作系统来讲，不需要太特殊的选择，最好做好冗余（raid1）（ssd、sas 、sata）raid卡：主机raid卡选择：实现操作系统磁盘的冗余（raid1）平衡内存和磁盘资源随机的I/O和顺序的I/O主机 RAID卡的BBU(Battery Backup Unit)要关闭。网络设备方面：使用流量支持更高的网络设备（交换机、路由器、网线、网卡、HBA卡）注意：以上这些规划应该在初始设计系统时就应该考虑好。1.5.3 服务器硬件优化1、物理状态灯：2、自带管理设备：远程控制卡（FENCE设备：ipmi ilo idarc），开关机、硬件监控。3、第三方的监控软件、设备（snmp、agent）对物理设施进行监控4、存储设备：自带的监控平台。EMC2（hp收购了）， 日立（hds），IBM低端OEM hds，高端存储是自己技术，华为存储1.5.4 系统优化Cpu：基本不需要调整，在硬件选择方面下功夫即可。内存：基本不需要调整，在硬件选择方面下功夫即可。SWAP：MySQL尽量避免使用swap。阿里云的服务器中默认swap为0IO ：raid、no lvm、 ext4或xfs、ssd、IO调度策略Swap调整(不使用swap分区)/proc/sys/vm/swappiness的内容改成0（临时） /etc/sysctl.conf上添加vm.swappiness=0（永久） 这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。 修改MySQL的配置参数innodb\_flush\_method，开启O_DIRECT模式。这种情况下，InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，但是redo log依旧会使用文件系统cache。值得注意的是，Redo log是覆写模式的，即使使用了文件系统的cache，也不会占用太多IO调度策略#echo deadline>/sys/block/sda/queue/scheduler #临时修改为deadline 永久修改如下： vi /boot/grub/grub.conf 更改到如下内容: kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet 1.5.5 系统参数调整Linux系统内核参数优化vim /etc/sysctl.conf   net.ipv4.ip_local_port_range = 1024 65535 # 用户端口范围   net.ipv4.tcp_max_syn_backlog = 4096   net.ipv4.tcp_fin_timeout = 30   fs.file-max=65535 # 系统最大文件句柄，控制的是能打开文件最大数量 用户限制参数（mysql可以不设置以下配置）vim /etc/security/limits.conf   * soft nproc 65535   * hard nproc 65535   * soft nofile 65535   * hard nofile 65535 1.5.6 应用优化业务应用和数据库应用独立,防火墙：iptables、selinux等其他无用服务(关闭)：chkconfig --level 23456 acpid off  chkconfig --level 23456 anacron off  chkconfig --level 23456 autofs off  chkconfig --level 23456 avahi-daemon off  chkconfig --level 23456 bluetooth off  chkconfig --level 23456 cups off  chkconfig --level 23456 firstboot off  chkconfig --level 23456 haldaemon off  chkconfig --level 23456 hplip off  chkconfig --level 23456 ip6tables off  chkconfig --level 23456 iptables off  chkconfig --level 23456 isdn off  chkconfig --level 23456 pcscd off  chkconfig --level 23456 sendmail off  chkconfig --level 23456 yum-updatesd off 安装图形界面的服务器不要启动图形界面 runlevel 3,另外，思考将来我们的业务是否真的需要MySQL，还是使用其他种类的数据库。用数据库的最高境界就是不用数据库。1.6 数据库优化SQL优化方向：执行计划、索引、SQL改写架构优化方向：高可用架构、高性能架构、分库分表1.6.1 数据库参数优化调整：实例整体（高级优化，扩展）thread_concurrency   #并发线程数量个数  sort_buffer_size     #排序缓存  read_buffer_size     #顺序读取缓存  read_rnd_buffer_size #随机读取缓存  key_buffer_size      #索引缓存  thread_cache_size    #线程缓存(1G—>8, 2G—>16, 3G—>32, >3G—>64) 连接层（基础优化）设置合理的连接客户和连接方式max_connections        #最大连接数，看交易笔数设置  max_connect_errors     #最大错误连接数，能大则大  connect_timeout        #连接超时  max_user_connections   #最大用户连接数  skip-name-resolve      #跳过域名解析  wait_timeout           #等待超时  back_log               #可以在堆栈中的连接数量 SQL层（基础优化）query\_cache\_size： 查询缓存OLAP类型数据库,需要重点加大此内存缓存.但是一般不会超过GB.对于经常被修改的数据，缓存会立马失效。我们可以实用内存数据库（redis、memecache），替代他的功能。1.6.2 存储引擎层（innodb基础优化参数）default-storage-engine  innodb_buffer_pool_size # 没有固定大小，50%测试值，看看情况再微调。但是尽量设置不要超过物理内存70% innodb_file_per_table=(1,0)  innodb_flush_log_at_trx_commit=(0,1,2) #1是最安全的，0是性能最高，2折中  binlog_sync  Innodb_flush_method=(O_DIRECT, fdatasync)  innodb_log_buffer_size      #100M以下  innodb_log_file_size        #100M 以下  innodb_log_files_in_group   #5个成员以下,一般2-3个够用（iblogfile0-N）  innodb_max_dirty_pages_pct  #达到百分之75的时候刷写 内存脏页到磁盘。 log_bin  max_binlog_cache_size       #可以不设置  max_binlog_size             #可以不设置  innodb_additional_mem_pool_size     #小于2G内存的机器，推荐值是20M。32G内存以上100M