这周收到一个 sentry 报警，如下 SQL 查询超时了。
代码语言：javascript复制select * from order_info where uid = 5837661 order by id asc limit 1执行show create table order_info发现这个表其实是有加索引的
代码语言：javascript复制CREATE TABLE `order_info` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`uid` int(11) unsigned,
`order_status` tinyint(3) DEFAULT NULL,
... 省略其它字段和索引
PRIMARY KEY (`id`),
KEY `idx_uid_stat` (`uid`,`order_status`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8理论上执行上述 SQL 会命中idx_uid_stat这个索引，但实际执行explain查看
代码语言：javascript复制explain select * from order_info where uid = 5837661 order by id asc limit 1可以看到它的 possible_keys（此 SQL 可能涉及到的索引） 是 idx_uid_stat，但实际上（key）用的却是全表扫描
我们知道 MySQL 是基于成本来选择是基于全表扫描还是选择某个索引来执行最终的执行计划的，所以看起来是全表扫描的成本小于基于 idx_uid_stat 索引执行的成本，不过我的第一感觉很奇怪，这条 SQL 虽然是回表，但它的 limit 是 1，也就是说只选择了满足 uid = 5837661 中的其中一条语句，就算回表也只回一条记录，这种成本几乎可以忽略不计，优化器怎么会选择全表扫描呢。
为了查看 MySQL 优化器为啥选择了全表扫描，我打开了 optimizer_trace 来一探究竟
画外音：在MySQL 5.6 及之后的版本中，我们可以使用 optimizer trace 功能查看优化器生成执行计划的整个过程
使用 optimizer_trace 的具体过程如下：
代码语言：javascript复制SET optimizer_trace="enabled=on"; // 打开 optimizer_trace
SELECT * FROM order_info where uid = 5837661 order by id asc limit 1
SELECT * FROM information_schema.OPTIMIZER_TRACE; // 查看执行计划表
SET optimizer_trace="enabled=off"; // 关闭 optimizer_traceMySQL 优化器首先会计算出全表扫描的成本，然后选出该 SQL 可能涉及到的所有索引并且计算索引的成本，然后选出所有成本最小的那个来执行，来看下optimizer trace给出的关键信息
代码语言：javascript复制{
"rows_estimation": [
{
"table": "`rebate_order_info`",
"range_analysis": {
"table_scan": {
"rows": 21155996,
"cost": 4.45e6 // 全表扫描成本
}
},
...
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_uid_stat",
"ranges": [
"5837661 <= uid <= 5837661"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 255918,
"cost": 307103, // 使用idx_uid_stat索引的成本
"chosen": true
}
],
"chosen_range_access_summary": { // 经过上面的各个成本比较后选择的最终结果
"range_access_plan": {
"type": "range_scan",
"index": "idx_uid_stat", // 可以看到最终选择了idx_uid_stat这个索引来执行
"rows": 255918,
"ranges": [
"58376617 <= uid <= 58376617"
]
},
"rows_for_plan": 255918,
"cost_for_plan": 307103,
"chosen": true
}
}
...可以看到全表扫描的成本是 4.45e6，而选择索引 idx_uid_stat 的成本是 307103，远小于全表扫描的成本，而且从最终的选择结果（chosen_range_access_summary）来看，确实也是选择了 idx_uid_stat 这个索引，但为啥从 explain 看到的选择是执行 PRIMARY 也就是全表扫描呢，难道这个执行计划有误？
常见的几种MySQL数据优化方式。。。。。。。
选取最适合的字段属性
1、MySQL可以很好的支持数据量的很大的存取，但是一般说来，数据库中的表越小其查询的速度就也快。所以，可以在建表的时候，为了获取更好的性能，将表中的字段长度设的尽可能的小。
2、尽可能的把字段设置成NOT NULL，这样在执行查询的时候，数据库不用去比较NULL值。
3、对于部分的文本字段，例如“性别”或者“民族”，我们就可以用enum来定义。MySQL会把enum类型的数据当作数值型来处理，而数值型数据被处理起来的速度要比文本类型快的多。这样我们就可以提高数据库的性能。
使用连接（JOIN）来代替子查询是（sub-Queries）
MySQL从4.1开始就支持SQL的子查询。这个技术可以使用select语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。
例：将客户基本信息表中没有任何订单的客户删除掉
DELETE FROM customerinfo WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo)
利用子查询先从销售信息表中将所有发出订单的客户ID取出，然后将结果传递给主查询。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，在某些情况下，子查询可以被更有效率的连接（JOIN）替代。
例：将所有没有订单记录的用户取出来
SELECT * FROM customerinfo WHERE CustomerID NOT IN (SELECTC ustomerID FROM salesinfo)
如果使用连接（JOIN）来完成这个查询工作，速度将会很快。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好：
SELECT * FROM customerinfo LEFT JOIN salesinfo ON customerinfo.CustomerID = salesinfo.CustomerID WHERE salesinfo.CustomerID ISNULL
连接（JOIN）之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需求两个步骤的查询工作。
使用联合（union）来代替手动创建的临时表
Union查询可以把需要使用临时表的两条或者更多的select查询合并成一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库的整齐、高效。使用union来创建查询的时候，只需要用union作为关键字把多个select语句连接起来就可以了（所有的select语句中的字段数目相同）
SELECT Name,Phone FROM client UNION
SELECT Name,BirthDate FROM author UNION
SELECT Name,Supplier FROM product
事务
尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。
BEGIN; INSERT INTO salesinfo SET CustomerID=14; UPDATE inventory SET Quantity=11 WHERE item='book'; COMMIT;
事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。
锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独立性，有时会影响数据库的性能，尤其是在很大的应用系统中，由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，就会产生比较严重的相应延迟。其实，有些情况下可以通过锁定表的方法来获得更好的性能。
LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';
...
UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES
这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。
使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候就可以使用外键。
例如，外键可以保证每一条销售记录都指向某一个存在的客户。外键可以把customerinfo表中的CustomerID映射到salesinfo表中的CustomerID，任何的一条没有合法CustomerID的记录都不会被更新或者插入到salesinfo中。
CREATE TABLE customerinfo( CustomerIDINT NOT
NULL,PRIMARYKEY(CustomerID))TYPE=INNODB;
CREATE TABLE salesinfo( SalesIDNT NOT NULL,CustomerIDINT NOT NULL, PRIMARYKEY(CustomerID,SalesID),
FOREIGNKEY(CustomerID) REFERENCES customerinfo(CustomerID) ON DELETE CASCADE)TYPE=INNODB;
注意例子中的参数“ON DELETE CASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATETABLE语句中加上TYPE=INNODB
使用索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。
一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况
例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。
优化查询语句
绝大多数的情况下，使用索引可以提高查询的速度，但是SQL语句使用不恰当的话，索引将无法发挥它应有的作用。
在相同类型的字段间进行比较的操作。
在建有索引的字段上尽量不要使用函数进行操作。
例如：在一个date类型的字段上使用yeae()函数时，将会使索引不能发挥应有的作用。
3.在搜索字符型字段时，我们有时会使用like关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。后者的查询速度明显比前者快得多。
SELECT * FROM books WHERE name like"MySQL%"
SELECT * FROM books WHERE name＞="MySQL" andname ＜"MySQM"
最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。
仔细再看了一下这个执行计划，果然发现了猫腻，执行计划中有一个reconsidering_access_paths_for_index_ordering选择引起了我的注意
代码语言：javascript复制{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`rebate_order_info`",
"index_provides_order": true,
"order_direction": "asc",
"index": "PRIMARY", // 可以看到选择了主键索引
"plan_changed": true,
"access_type": "index_scan"
}
}
}这个选择表示由于排序的原因再进行了一次索引选择优化，由于我们的 SQL 使用了 id 排序（order by id asc limit 1）,优化器最终选择了 PRIMARY 也就是全表扫描来执行，也就是说这个选择会无视之前的基于索引成本的选择，为什么会有这样的一个选项呢，主要原因如下：
The short explanation is that the optimizer thinks — or should I say hopes — that scanning the whole table (which is already sorted by the id field) will find the limited rows quick enough, and that this will avoid a sort operation. So by trying to avoid a sort, the optimizer ends-up losing time scanning the table.
从这段解释可以看出主要原因是由于我们使用了order by id asc这种基于 id 的排序写法，优化器认为排序是个昂贵的操作，所以为了避免排序，并且它认为 limit n 的 n 如果很小的话即使使用全表扫描也能很快执行完，所以它选择了全表扫描，也就避免了 id 的排序（全表扫描其实就是基于 id 主键的聚簇索引的扫描，本身就是基于 id 排好序的）
另外，关注公号“终码一生”，回复关键词“资料”，获取视频教程和最新的面试资料！
如果这个选择是对的那也罢了，然而实际上这个优化却是有 bug 的！实际选择 idx_uid_stat 执行会快得多（只要 28 ms）！网上有不少人反馈这个问题，而且出现这个问题基本只与 SQL 中出现order by id asc limit n这种写法有关，如果 n 比较小很大概率会走全表扫描，如果 n 比较大则会选择正确的索引。
这个 bug 最早追溯到 2014 年，不少人都呼吁官方及时修正这个bug，可能是实现比较困难，直到 MySQL 5.7，8.0 都还没解决，所以在官方修复前我们要尽量避免这种写法，如果一定要用这种写法，怎么办呢，主要有两种方案
1.使用 force index 来强制使用指定的索引，如下：
代码语言：javascript复制select * from order_info force index(idx_uid_stat) where uid = 5837661 order by id asc limit 1这种写法虽然可以，但不够优雅，如果这个索引被废弃了咋办？于是有了第二种比较优雅的方案
2.使用 order by (id 0) 方案，如下：
代码语言：javascript复制select * from order_info where uid = 5837661 order by (id 0) asc limit 1这种方案也可以让优化器选择正确的索引，更推荐！为什么这个 trick 可以呢，因为此 SQL 虽然是按 id 排序的，但在 id 上作了加法这样耗时的操作(虽然只是加个无用的 0，但足以骗过优化器)，优化器认为此时基于全表扫描会更耗性能，于是会选择基于成本大小的方式来选择索引
