文章目录
一、数据库设计方面
1、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引；
2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num = 0；
3、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用；
4、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要；
5、应尽可能的避免更新索引数据列，因为索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新索引数据列，那么需要考虑是否应将该索引建为索引；
6、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了；
7、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些；
8、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）；
9、避免频繁创建和删除临时表，以减少系统表资源的消耗；
10、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表;
11、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert；
12、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
二、SQL语句方面
1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；
2、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20;
3、in 和 not in 也要慎用，否则会导致全表扫描，如： select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3；
4、下面的查询也将导致全表扫描： select id from t where name like ‘%abc%’
5、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： select id from t where num=@num 可以改为强制查询使用索引： select id from t with(index(索引名)) where num=@num；
6、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where num/2=100 应改为: select id from t where num=100*2；
7、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where substring(name,1,3)=’abc’–name以abc开头的id，select id from t where datediff(day,createdate,’2005-11-30′)=0–‘2005-11-30’生成的id 应改为: select id from t where name like ‘abc%’ select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′
8、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
9、不要写一些没有意义的查询，如需要生成一个空表结构： select col1,col2 into #t from t where 1=0 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： create table #t(…)
10、很多时候用 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)
11、任何地方都不要使用 select * from t ，用具体的字段列表代替 “*” ，不要返回用不到的任何字段。
12、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
13、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
14、尽量避免大事务操作，提高系统并发能力。
三、Java方面（重点内容）
1、尽可能的少造对象；
2、合理摆正系统设计的位置。大量数据操作，和少量数据操作一定是分开的。大量的数据操作，肯定不是ORM框架搞定的；
3、使用JDBC链接数据库操作数据；
4、控制好内存，让数据流起来，而不是全部读到内存再处理，而是边读取边处理；
5、合理利用内存，有的数据要缓存
四、如何优化数据库，如何提高数据库的性能?
1、硬件调整性能 最有可能影响性能的是磁盘和网络吞吐量,解决办法扩大虚拟内存，并保证有足够可以扩充的空间；把数据库服务器上的不必要服务关闭掉；把数据库服务器和主域服务器分开；把SQL数据库服务器的吞吐量调为最大；在具有一个以上处理器的机器上运行SQL。
2、调整数据库 若对该表的查询频率比较高，则建立索引；建立索引时，想尽对该表的所有查询搜索操作， 按照where选择条件建立索引，尽量为整型键建立为有且只有一个簇集索引，数据在物理上按顺序在数据页上，缩短查找范围，为在查询经常使用的全部列建立非簇集索引，能最大地覆盖查询；但是索引不可太多，执行UPDATE DELETE INSERT语句需要用于维护这些索引的开销量急剧增加；避免在索引中有太多的索引键；避免使用大型数据类型的列为索引；保证每个索引键值有少数行。
3、使用存储过程（注意：阿里巴巴开发规范中已经明确禁止使用存储过程了，这里只是列出，不作为优化方法！） 应用程序的实现过程中，能够采用存储过程实现的对数据库的操作尽量通过存储过程来实现，因为存储过程是存放在数据库服务器上的一次性被设计、编码、测试，并被再次使用，需要执行该任务的应用可以简单地执行存储过程，并且只返回结果集或者数值，这样不仅可以使程序模块化，同时提高响应速度，减少网络流量，并且通过输入参数接受输入，使得在应用中完成逻辑的一致性实现。
4、应用程序结构和算法 建立查询条件索引仅仅是提高速度的前提条件，响应速度的提高还依赖于对索引的使用。因为人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，特别是对数据量不是特别大的数据库操作时，是否建立索引和使用索引的好坏对程序的响应速度并不大，因此程序员在书写程序时就忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在数据量特别大时或者大型的或是复杂的数据库环境中（如联机事务处理OLTP或决策支持系统DSS）中表现得尤为明显。在工作实践中发现，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显地提高！
MySQL的索引是什么？怎么优化？
索引类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降，虽然官方文档说500~800w记录，所以大数据量建立索引是非常有必要的。MySQL提供了Explain，用于显示SQL执行的详细信息，可以进行索引的优化。
一、导致SQL执行慢的原因：
- 硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。
- 没有索引或者索引失效。（一般在互联网公司，DBA会在半夜把表锁了，重新建立一遍索引，因为当你删除某个数据的时候，索引的树结构就不完整了。所以互联网公司的数据做的是假删除.一是为了做数据分析,二是为了不破坏索引 ）
- 数据过多（分库分表）
- 服务器调优及各个参数设置（调整my.cnf）
二、分析原因时，一定要找切入点：
- 先观察，开启慢查询日志，设置相应的阈值（比如超过3秒就是慢SQL），在生产环境跑上个一天过后，看看哪些SQL比较慢。
- Explain和慢SQL分析。比如SQL语句写的烂，索引没有或失效，关联查询太多（有时候是设计缺陷或者不得以的需求）等等。
- Show Profile是比Explain更近一步的执行细节，可以查询到执行每一个SQL都干了什么事，这些事分别花了多少秒。
- 找DBA或者运维对MySQL进行服务器的参数调优。
三、什么是索引？
MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为：快速查找排好序的一种数据结构。Mysql索引主要有两种结构：B+Tree索引和Hash索引。我们平常所说的索引，如果没有特别指明，一般都是指B树结构组织的索引(B+Tree索引)。索引如图所示：
最外层浅蓝色磁盘块1里有数据17、35（深蓝色）和指针P1、P2、P3（黄色）。P1指针表示小于17的磁盘块，P2是在17-35之间，P3指向大于35的磁盘块。真实数据存在于子叶节点也就是最底下的一层3、5、9、10、13…非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35。 查找过程：例如搜索28数据项，首先加载磁盘块1到内存中，发生一次I/O，用二分查找确定在P2指针。接着发现28在26和30之间，通过P2指针的地址加载磁盘块3到内存，发生第二次I/O。用同样的方式找到磁盘块8，发生第三次I/O。 真实的情况是，上面3层的B+Tree可以表示上百万的数据，上百万的数据只发生了三次I/O而不是上百万次I/O，时间提升是巨大的。
四、Explain分析
CREATE TABLE `user_info` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL DEFAULT '',
`age` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_index` (`name`)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERT INTO user_info (name, age) VALUES ('xys', 20);
INSERT INTO user_info (name, age) VALUES ('a', 21);
INSERT INTO user_info (name, age) VALUES ('b', 23);
INSERT INTO user_info (name, age) VALUES ('c', 50);
INSERT INTO user_info (name, age) VALUES ('d', 15);
INSERT INTO user_info (name, age) VALUES ('e', 20);
INSERT INTO user_info (name, age) VALUES ('f', 21);
INSERT INTO user_info (name, age) VALUES ('g', 23);
INSERT INTO user_info (name, age) VALUES ('h', 50);
INSERT INTO user_info (name, age) VALUES ('i', 15);
CREATE TABLE `order_info` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`user_id` BIGINT(20) DEFAULT NULL,
`product_name` VARCHAR(50) NOT NULL DEFAULT '',
`productor` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');
初体验
EXPLAIN SELECT id,user_id,product_name, productor FROM order_info;
索引使用情况在possible_keys、key和key_len三列，接下来我们先从左到右依次讲解。
1.id
id相同,执行顺序由上而下
explain select u.*,o.* from user_info u,order_info o where u.id=o.user_id;
id不同,值越大越先被执行
explain select * from user_info where id=(select user_id from order_info where product_name ='p8');
2.select_type
可以看id的执行实例，总共有以下几种类型：
- SIMPLE： 表示此查询不包含 UNION 查询或子查询
- PRIMARY： 表示此查询是最外层的查询
- SUBQUERY： 子查询中的第一个 SELECT
- UNION： 表示此查询是 UNION 的第二或随后的查询
- DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询
- UNION RESULT, UNION 的结果
- DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
- DERIVED：衍生，表示导出表的SELECT（FROM子句的子查询）
3.table
table表示查询涉及的表或衍生的表：
explain select tt.* from (select u.* from user_info u,order_info o where u.id=o.user_id and u.id=1) tt;
id为1的的表示id为2的u和o表衍生出来的。
4.type
type 字段比较重要，它提供了判断查询是否高效的重要依据依据。 通过 type 字段，我们判断此次查询是 全表扫描 还是 索引扫描等。
type 常用的取值有:
system: 表中只有一条数据， 这个类型是特殊的 const 类型。
const: 针对主键或唯一索引的等值查询扫描，最多只返回一行数据。 const 查询速度非常快， 因为它仅仅读取一次即可。例如下面的这个查询，它使用了主键索引，因此 type 就是 const 类型的：explain select * from user_info where id = 2；
eq_ref: 此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。并且查询的比较操作通常是 =，查询效率较高。例如：explain select * from user_info, order_info where user_info.id = order_info.user_id;
ref: 此类型通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了 最左前缀 规则索引的查询。例如下面这个例子中， 就使用到了 ref 类型的查询：explain select * from user_info, order_info where user_info.id = order_info.user_id AND order_info.user_id = 5
range: 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。例如下面的例子就是一个范围查询：explain select * from user_info where id between 2 and 8；
index: 表示全索引扫描(full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引， 而不扫描数据。index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据。当是这种情况时，Extra 字段 会显示 Using index。
ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。通常来说， 我们的查询不应该出现 ALL 类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。 如一个查询是 ALL 类型查询， 那么一般来说可以对相应的字段添加索引来避免。
通常来说, 不同的 type 类型的性能关系如下：ALL < index < range ~ index_merge < ref < eq_ref < const < system ALL 类型因为是全表扫描， 因此在相同的查询条件下，它是速度最慢的。而 index 类型的查询虽然不是全表扫描，但是它扫描了所有的索引，因此比 ALL 类型的稍快.后面的几种类型都是利用了索引来查询数据，因此可以过滤部分或大部分数据，因此查询效率就比较高了。
5.possible_keys
它表示 mysql 在查询时，可能使用到的索引。 注意，即使有些索引在 possible_keys 中出现，但是并不表示此索引会真正地被 mysql 使用到。 mysql 在查询时具体使用了哪些索引，由 key 字段决定。
6.key
此字段是 mysql 在当前查询时所真正使用到的索引。比如请客吃饭,possible_keys是应到多少人，key是实到多少人。当我们没有建立索引
explain select o.* from order_info o where o.product_name= 'p1' and o.productor='whh';
create index idx_name_productor on order_info(productor);
drop index idx_name_productor on order_info;
7.key_len
表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用。
8.ref
这个表示显示索引的哪一列被使用了，如果可能的话,是一个常量。前文的type属性里也有ref，注意区别。
9.rows
rows 也是一个重要的字段，mysql 查询优化器根据统计信息，估算 sql 要查找到结果集需要扫描读取的数据行数，这个值非常直观的显示 sql 效率好坏， 原则上 rows 越少越好。可以对比key中的例子，一个没建立索引钱，rows是9，建立索引后，rows是4。
10.extra
explain 中的很多额外的信息会在 extra 字段显示, 常见的有以下几种内容:
- using filesort ：表示 mysql 需额外的排序操作，不能通过索引顺序达到排序效果。一般有 using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大。
- using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。
- using temporary：查询有使用临时表, 一般出现于排序， 分组和多表 join 的情况， 查询效率不高，建议优化。
- using where ：表名使用了where过滤。
五、优化案例
explain select u.*,o.* from user_info u LEFT JOIN order_info o on u.id=o.user_id;
开始优化，在关联列上创建索引，明显看到type列的ALL变成ref，并且用到了索引，rows也从扫描9行变成了1行：
create index idx_user_id order_info(user_id);
这里面一般有个规律是：左链接索引加在右表上面，右链接索引加在左表上面。
六、是否需要创建索引？
索引虽然能非常高效的提高查询速度，同时却会降低更新表的速度。实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
| 拿些情况情况需要建立索引 | 拿些情况情况不需要建立索引 |
|---|---|
| 逐渐自动建立索引 | 表记录太少 |
| 频繁作为查询条件的字段应该建立索引 | 经常增删改的表或者字段 |
| 查询中与其他表关联字段建立索引 | where过滤性不好的字段 |
| 单键索引/组合索引的选择问题，组合索引性价比高 | 大字符类型的字段 |
| 查询中排序的段、统计、分组字段 |
数据库优化的几个阶段
第一阶段 优化sql和索引
这才是调优的第一阶段啊，为什么呢？ 因为这一步成本最低啊，不需要加什么中间件。你没经过索引优化和SQL优化，就来什么水平拆分，这不是坑人么。 那步骤是什么样呢?我说个大概
- 用慢查询日志定位执行效率低的
SQL语句 - 用
explain分析SQL的执行计划 - 确定问题，采取相应的优化措施，建立索引啊，等
第二阶段 搭建缓存
在优化sql无法解决问题的情况下，才考虑搭建缓存。毕竟你使用缓存的目的，就是将复杂的、耗时的、不常变的执行结果缓存起来，降低数据库的资源消耗。 这里需要注意的是:搭建缓存后，系统的复杂性增加了。你需要考虑很多问题，比如:
-
缓存和数据库一致性问题？(比如是更缓存，还是删缓存),这点可以看我的一篇文章《数据库和缓存双写一致性方案解析》。
-
缓存击穿、缓存穿透、缓存雪崩问题如何解决？是否有做缓存预热的必要。不过我猜，大部分中小公司应该都没考虑。这点可以看我的另一篇《分布式之redis复习精讲》
第三阶段 读写分离
缓存也搞不定的情况下，搞主从复制，上读写分离。在应用层，区分读写请求。或者利用现成的中间件mycat或者altas等做读写分离。 需要注意的是,只要你敢说你用了主从架构，有三个问题，你要准备: 1.主从的好处？ 回答:实现数据库备份，实现数据库负载均衡，提高数据库可用性 2.主从的原理? 回答:如图所示（图片不是自己画的，偷懒了）
主库有一个log dump线程，将binlog传给从库 从库有两个线程，一个I/O线程，一个SQL线程，I/O线程读取主库传过来的binlog内容并写入到relay log,SQL线程从relay log里面读取内容，写入从库的数据库。 3.如何解决主从一致性? 回答:这个问题，我不建议在数据库层面解决该问题。根据 CAP 定理，主从架构本来就是一种高可用架构，是无法满足一致性的。 哪怕你采用同步复制模式或者半同步复制模式，都是弱一致性，并不是强一致性。所以，推荐还是利用缓存，来解决该问题。 步骤如下:
- 自己通过测试，计算主从延迟时间，建议mysql版本为5.7以后，因为mysql自5.7开始，多线程复制功能比较完善，一般能保证延迟在1s内。不过话说回来，mysql现在都出到8.x了，还有人用5.x的版本么。
- 数据库的写操作，先写数据库，再写cache，但是有效期很短，就比主从延时的时间稍微长一点。
- 读请求的时候，先读缓存，缓存存在则直接返回。如果缓存不存在(这时主从同步已经完成)，再读数据库。
第四阶段 利用分区表
说句实在话，你们面试的时候，其实可以略过这个阶段。因为很多互联网公司都不建议用分区表，我自己也不太建议用分区表，采用这个分区表，坑太多。 这里引用一下其他文章的回答: 什么是mysql的分区表？ 回答：所有数据还在一个表中，但物理存储根据一定的规则放在不同的文件中。这个是mysql支持的功能，业务代码不需要改动，但是sql语句需要改动，sql条件需要带上分区的列。 缺点
- 分区键设计不太灵活，如果不走分区键，很容易出现全表锁
- 在分区表使用
ALTER TABLE…ORDER BY，只能在每个分区内进行order by。 - 分区表的分区键创建索引，那么这个索引也将被分区。分区键没有全局索引一说。
- 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定该去哪个分区查，不太可控。 …不列举了，不推荐
第五阶段 垂直拆分
上面四个阶段都没搞定，就来垂直拆分了。垂直拆分的复杂度还是比水平拆分小的。将你的表，按模块拆分为不同的小表。大家应该都看过《大型网站架构演变之路》，这种类型的文章或者书籍，基本都有提到这一阶段。 如果你有幸能够在什么运营商、银行等公司上班，你会发现他们一个表，几百个字段都是很常见的事情。所以，应该要进行拆分，拆分原则一般是如下三点:
- 把不常用的字段单独放在一张表。
- 把常用的字段单独放一张表。
- 经常组合查询的列放在一张表中（联合索引）。
第六阶段 水平拆分
OK,水平拆分是最麻烦的一个阶段，拆分后会有很多的问题，我再强调一次，水平拆分一定是最最最最后的选择。从某种意义上，我觉得还不如垂直拆分。因为你用垂直拆分，分成不同模块后，发现单模块的压力过大，你完全可以给该模块单独做优化，例如提高该模块的机器配置等。如果是水平拆分，拆成两张表，代码需要变动，然后发现两张表还不行，再变代码，再拆成三张表的？水平拆分后，各模块间耦合性太强，成本太大，慎重。
8种常被忽视的SQL错误用法
sql语句的执行顺序
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>
1. LIMIT 语句
分页查询是最常用的场景之一，但也通常也是最容易出问题的地方。比如对于下面简单的语句，一般 DBA 想到的办法是在 type, name, create_time 字段上加组合索引。这样条件排序都能有效的利用到索引，性能迅速提升。
SELECT * FROM operation WHERE type = 'SQLStats'
AND name = 'SlowLog' ORDER BY create_time LIMIT 1000, 10;
好吧，可能90%以上的 DBA 解决该问题就到此为止。但当 LIMIT 子句变成 “LIMIT 1000000,10” 时，程序员仍然会抱怨：我只取10条记录为什么还是慢？
要知道数据库也并不知道第1000000条记录从什么地方开始，即使有索引也需要从头计算一次。出现这种性能问题，多数情形下是程序员偷懒了。
在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的。SQL 重新设计如下：
SELECT * FROM operation WHERE type = 'SQLStats'
AND name = 'SlowLog' AND create_time > '2017-03-16 14:00:00'
ORDER BY create_time limit 10;
2. 隐式转换
SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误。比如下面的语句：
name字段是varchar类型，并且建立索引，不加引号隐式转换导致索引失效。
MySQL 的策略是将字符串转换为数字之后再比较。函数作用于表字段，索引失效。
3. 关联更新、删除
比如下面 UPDATE 语句，MySQL 实际执行的是循环/嵌套子查询（DEPENDENT SUBQUERY)，其执行时间可想而知。
UPDATE operation o SET status = 'applying' WHERE o.id
IN (SELECT id FROM (SELECT o.id,o.status FROM operation o
WHERE o.group = 123 AND o.status NOT IN ( 'done' )
ORDER BY o.parent, o.id LIMIT 1) t);
执行计划：
重写为 JOIN 之后，子查询的选择模式从 DEPENDENT SUBQUERY 变成 DERIVED，执行速度大大加快，从7秒降低到2毫秒。
UPDATE operation o JOIN (SELECT o.id, o.status FROM operation o WHERE o.group = 123
AND o.status NOT IN ( 'done' ) ORDER BY o.parent,o.id LIMIT 1) t
ON o.id = t.id SET status = 'applying'
执行计划：
避免关联更新/删除/子查询等。
4. 混合排序
MySQL 不能利用索引进行混合排序。但在某些场景，还是有机会使用特殊方法提升性能的。
SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id
ORDER BY a.is_reply ASC, a.appraise_time DESC LIMIT 0, 20
执行计划显示为全表扫描：
由于 is_reply 只有0和1两种状态，我们按照下面的方法重写后，执行时间从1.58秒降低到2毫秒。
SELECT * FROM (
(SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id
AND is_reply = 0 ORDER BY appraise_time DESC LIMIT 0, 20)
UNION ALL
(SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id
AND is_reply = 1 ORDER BY appraise_time DESC LIMIT 0, 20)) t
ORDER BY is_reply ASC, appraisetime DESC LIMIT 20;
5. EXISTS语句
MySQL 对待 EXISTS 子句时，仍然采用嵌套子查询的执行方式。如下面的 SQL 语句：
SELECT *
FROM my_neighbor n
LEFT JOIN my_neighbor_apply sra
ON n.id = sra.neighbor_id
AND sra.user_id = 'xxx'
WHERE n.topic_status < 4
AND EXISTS(SELECT 1
FROM message_info m
WHERE n.id = m.neighbor_id
AND m.inuser = 'xxx')
AND n.topic_type <> 5
执行计划为：
去掉 exists 更改为 join，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。
SELECT *
FROM my_neighbor n
INNER JOIN message_info m
ON n.id = m.neighbor_id
AND m.inuser = 'xxx'
LEFT JOIN my_neighbor_apply sra
ON n.id = sra.neighbor_id
AND sra.user_id = 'xxx'
WHERE n.topic_status < 4
AND n.topic_type <> 5
新的执行计划：
5274
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
