搭建示例数据库
如何查看建表语句
覆盖索引
- 覆盖索引的优点和注意事项
如何确定用哪条索引
- 索引基数
- 索引基数不准确的处理办法
COUNT
ORDER BY
- ORDER BY步骤原理
- ORDER BY 优化
ORDER BY RAND()
索引下推
松散索引扫描
可以走索引，结果没走索引的情况
分页语句优化

搭建示例数据库

使用Sakila 数据库，Sakila 是 mysql 中的一个示例数据库（sample database），提供了一个标准的方案，可用于自学，写书，教程，文章以及示例等等。

数据库下载

下载地址：
页面点击下载：https://dev.mysql.com/doc/index-other.html

或直接下载：http://downloads.mysql.com/docs/sakila-db.zip
下载的安装包中包含三个文件：sakila-schema.sql，sakila-data.sql 和 sakila.mwb。其中：

sakila-schema.sql 是数据库的建表语句等， sakila-data.sql 是表数据， sakila.mwb 是 mysql Workbench 数据结构模型文件。（可忽略）

导入数据库

假如把上述文件放到了 C:/temp/sakila-db/ 下，则先通过

shell> mysql -u root –p

进入数据库，再通过以下语句导入 schema 定义：

mysql> SOURCE C:/temp/sakila-db/sakila-schema.sql;

再通过以下语句导入数据：

mysql> SOURCE C:/temp/sakila-db/sakila-data.sql;

你也可以通过一些数据库管理工具导入。

Sakila 数据库业务与结构

它是一个关于 DVD（电影）租赁店业务的数据库。包含的一些表比如有：

actor（演员），film（电影），category（类别），inventory（库存），customer（顾客），rental（租赁信息）等。

应该说这个业务也是较为常见，也较容易理解的。一个完整的表关系图如下：
MySQL查询优化 - 图2

此图即来自上述文件 sakila.mwb。

如何查看建表语句

SHOW CREATE TABLE table_name;

覆盖索引

覆盖索引指的是：查询语句从执行到返回结果均使用同一个索引，也就是where条件的列和select字段的列在一个索引中包含。

覆盖索引的优点和注意事项

覆盖索引可以有效减少回表
覆盖索引通过取消回表操作，提升查询效率
若数据的查询不只使用了一个索引，则不是覆盖索引
可以通过优化SQL语句或优化联合索引，来使用覆盖索引
尽量使用覆盖索引，减少不必要的字段查询，禁止使用SELECT
EXPLAIN查看语句执行计划，Extra列中如果出现*Using Index信息即代表走了覆盖索引。

如何确定用哪条索引

索引基数

MySQL在选取索引时，会参考索引的基数(Cardinality)
基数是MySQL估算的，反映这个字段有多少种不同的取值
选取几个页算出取值的平均值，再乘以页数，即为基数
查看索引基数：

SHOW INDEX FROM index_name;

查询结果中的Cardinality列即为索引的基数，数值越大区分度越高。
区分度越大越有可能选用这条索引，但是区分度是抽样估计的，可能不是很准确。

索引基数不准确的处理办法

在区分度不准确的情况下，我们可以在from table语句后增加force index语句强制使用索引。

例如：

SELECT
    *
FROM
    table_name FORCE INDEX (index_name);

如果不想这样写，既然区分度估计严重不准确，那么可以让MySQL重新估计区分度。

使用analyze table [table_name]可以重新统计索引信息，重新统计索引信息时，会重新计算索引的基数。

COUNT

MySQL COUNT()函数用来统计结果集中不为null的数据个数
首先存储引擎查询出结果集
Server层逐个判断结果是否为null，不为null则加1

COUNT(非索引字段)

COUNT(非索引字段)时，Server层需要判断每个数据是否为null
MySQL行记录是有格式的，需要Server层一行行解析，然后判断对应的非索引字段是否为null，效率极低，而且查询本身无法使用覆盖索引，理论上最慢

COUNT(索引字段)

COUNT(索引字段)，可以走覆盖索引，不需要解析行记录，但是依然需要每次判断字段是否为null。
COUNT(主健)，同理，MySQL并没有对主键这种非空索引做特殊处理，而是走一般流程。
COUNT(索引字段)比COUNT(非索引字段)的效率高，但是还是有提升空间

COUNT(1)

COUNT(1)只有扫描索引树，没有解析数据行的过程，理论更快
和COUNT(主键)相比，COUNT(1)少了一个取值的动作，减少的这个取值的动作使得COUNT(1)的效率高于COUNT(主键)。
但Server层依然每次需要判断“1是否为null”

COUNT(*)

对于COUNT()来说，COUNT()的用法在早期的数据库和现在的数据库中有较大的差距，在早期的数据库中，COUNT()有可能会导致全表的扫描，因此效率会低。
COUNT()一般用来返回数据表行数
MylSAM的COUNT()直接返回数据库中记录的数据表行数，效率最高。
由于InnoDB支持事务，数据库中不记录数据表行数，因为并行的事务会插入和删除行，导致行数不准。
MySQL InnoDB专门优化了COUNT()函数直接返回索引树中数据的个数。
这些优化的前提都是没有进行where和group的条件查询。

COUNT函数总结

COUNT(1) 和 COUNT()表示的是直接查询符合条件的数据库表的行数。
而COUNT(列名)表示的是查询符合条件的列的值不为NULL的行数。
COUNT函数以下的区别：
COUNT(非索引字段)：无法使用覆盖索引，最慢
COUNT(索引字段)：可以使用覆盖索引但依然要取出数据判空
COUNT(1)：不需要取出数据，但需要判断“1是否为null”
COUNT()：经过专门优化，不需要判空，理论最快
Alibaba开发规范提到:

MySQL官方文档提到：
在 MySQL 5.7.18 之前，通过扫描聚集索引来InnoDB处理 SELECT COUNT()语句。从 MySQL 5.7.18 开始，除非索引或优化器提示指示优化器使用不同的索引，否则通过遍历最小的可用二级索引来InnoDB处理SELECT COUNT()语句。如果二级索引不存在，则扫描聚集索引。
SELECT COUNT()如果索引记录不完全在缓冲池中，则处理语句需要一些时间。为了更快地计数，创建一个计数器表并让您的应用程序根据它所做的插入和删除来更新它。但是，在数千个并发事务启动对同一计数器表的更新的情况下，此方法可能无法很好地扩展。如果近似行数就足够了，请使用 SHOW TABLE STATUS
InnoDB处理SELECT COUNT()和SELECT COUNT(1) 操作方式相同。没有性能差异。
MySQL文档地址:
https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html#function_count
我们在设计上也需要尽量的规避全表count。
通常情况我们针对可预见的COUNT查询会做适当的缓存，可以是 Redis，也可以是独立的 MySQL COUNT表。

ORDER BY

ORDER BY步骤原理

根据WHERE等条件查询将查询结果放入sort_buffer
对中间结果集按照ORDER字段排序
回表生成完整结果集（若需要)

ORDER BY 优化

优化查询条件
1. 给查询字段加索引，可以改善条件查询速度
优化中间结果集
1. 中间表比较小时，直接放在内存中，中间表大于sort_buffer_size时，放在硬盘中
2. 若需要优化内存占用，减小sort_buffer_size，若需要优化排序查询时间，增大sort_buffer_size
优化回表生成完整结果集
1. 当行小于max_length_for_sort_data时，生成全字段中间表
2. 大于阈值时，只生成排序字段+主键中间表，需要回表
3. 调大阈值并不一定改善效率，因为太大的结果集排序效率低
优化索引覆盖
1. 索引覆盖可以跳过生成中间结果集，直接输出查询结果
2. ORDER字段需要有索引(或在联合索引左侧)
3. 其他相关字段（条件、输出）均在上述的索引中
MySQL排序一般需要生成中间结果集、排序、回表的过程索引覆盖是最高效的处理排序的方式

ORDER BY RAND()
RAND()函数随机生成一个0-1之间的随机值。
例：随机查询一部电影，需要查询电影的信息字段有：标题和描述，SQL如下：
```
SELECT title, description FROM `film` ORDER BY RAND() LIMIT 1;
```
执行过程如下：
创建一个临时表，临时表的字段为rand 、 title、description
从表中取出一行，调用RAND(),将结果和数据放入临时表，以此类推
针对临时表，将rand字段+行位置（或主键）放入sort buffer（如果是内存表，有行位置没有主键，如果是磁盘，有主键没有行位置，这个主键是临时的行主键，不是原表的主键）。
对sort_buffer排序，取出第一个的行位置（主键)，查询临时表。

虽然需求简单，但是这个SQL执行过程是很慢的，原因如下：

SQL过程中出现了两次中间结果（临时表），都是全长度的。
仅需要一个随机结果，缺经历了不必要的排序（虽然优化过）
调用了多次RAND();

解决办法：

临时方案，不需要修改业务逻辑，只需要修改SQL语句。

变量M和变量N为最大的id和最小的id，变量X是从M到N随机选取的一个值，
id可能不是连续的，所以where条件要>=X,然后取第一条。

select max(film_id),min(film_id) into @M,@N from film ;set @X= floor((@M-@N+1)*rand() + @N);
select title, description from film where film_id >= @X limit 1;

业务方案，需要修改业务代码

查询数据表总数total，
然后在total范围内，随机选取一个数字r执行以下SQL:

select title, description from film limit r, 1;

用分页的查询语法巧妙的实现了这个功能，效率最高。

索引下推

索引下推和松散索引扫描数据表如下：

CREATE TABLE `inventory_3`(
`inventory_id` mediumint unsigned NOT NULL AUTO_INCREMENT,`film_id` smallint unsigned NOT NULL,
`store_id` tinyint unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`inventory_id`),
KEY `idx_store_id_film_id` ( `store_id` , `film_id`)
)ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;
INSERT into inventory_3 SELECT * from inventory;

现在使用如下SQL语句查询：

SELECT * FROM inventory_3 WHERE store_id in (1,2) and film_id = 3;

当主要排序手段有序后，次要排序手段变得无序。

idx_store_id_film_id索引部分数据如下图：

如果film_id条件是in，没法直接走索引，如果store_id和film_id都是等值查询，是可以直接走索引。
这里store_id in (1,2) ，首先可以查出四条记录，然后根据inventory_id回表4次，拿到film_id进行条件匹配是否等于3才行。
索引下推就是在回表之前，如果你where的字段在索引中，索引可以进行判断，先根据索引的字段进行条件过滤，最后再进行回表操作，性能得到了提升。上述语句，索引下推后，只会得到一条记录，再进行回表，拿到所有的字段信息，此时回表次数只需要1次。
通过查看执行计划，Extra中信息为Using index condition即表示索引下推
索引下推在MySQL5.6+版本自动开启，不需要手动设置。

松散索引扫描

SELECT film_id FROM `inventory_3` WHERE film_id = 3;

film_id为联合索引的第二个字段，没法走索引，只能全表扫描。
但是在MySQL8.0下，结果却不同，扫描的行数会大大降低，执行计划Extra列中会出现这样的信息，Using index for skip scan，即使用了 skip scan(松散索引扫描)。
原理是走了联合索引，虽然查询条件没有第一个字段，但是全表扫描时，它根据索引的第一列进行扫描，这样索引的第二个字段就是有序的，如果发现film_id>3的记录，就可以跳过这个索引值的其他行，走到下一个索引值，即skip scan。
松散索引扫描可以打破“左侧原则”，解决带头大哥丢失的问题。

可以走索引，结果没走索引的情况

索引字段上使用了函数操作

MySQL中，对索引字段做函数或计算操作，优化器会放弃索引
例1：查询电影id +1 = 100的电影，film_id为索引列

select * from film WHERE film_id + 1 = 100;

对film_id进行了计算，无法使用索引，查询执行计划，type为ALL扫描全表，Extra为Using where
处理方法：

select * from film WHERE film_id = 99;

例2：使用month()函数后，无法使用索引，rental_date为索引列

SELECT * FROM `rental` WHERE month(rental_date)= 5;

查询执行计划，type为ALL扫描全表，Extra为Using where
处理办法：时间函数转区间，需要去掉month()函数，换成区间函数between

SELECT * FROM `rental` 
WHERE rental_date BETWEEN '2005-5-1'AND '2005-6-1'
OR rental_date BETWEEN '2006-5-1'AND '2006-6-1';

第2条和第3条用到的表结构和数据如下：

-- ----------------------------
-- Table structure for t1
-- ----------------------------
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `f1` varchar(32) NOT NULL,
  `f2` int(11) NOT NULL,
  KEY `idx_f1` (`f1`),
  KEY `idx_f2` (`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of t1
-- ----------------------------
INSERT INTO `t1` VALUES ('4', '6');
INSERT INTO `t1` VALUES ('6', '3');
INSERT INTO `t1` VALUES ('7', '1');
-- ----------------------------
-- Table structure for t2
-- ----------------------------
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `f1` varchar(32) NOT NULL,
  `f2` int(11) NOT NULL,
  KEY `idx_f1` (`f1`),
  KEY `idx_f2` (`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t2
-- ----------------------------
INSERT INTO `t2` VALUES ('4', '5');
INSERT INTO `t2` VALUES ('2', '4');
INSERT INTO `t2` VALUES ('2', '4');

出现字符串与数字的比较

MySQL中若出现字符串与数字比较，会将字符串转换为数字
例：

select * from t1 where f1=6;

如果t1表中f1字段为varchar类型，则此SQL相当于

select * from t1 where CAST(f1 AS signed int)=6;

处理方法：数字强转为字符串，将第一个SQL中的6改为 ‘6’

发生隐式字符编码转换

发生隐式字符编码转换，MySQL中,utf8与utf8mb4字段比较时，会把utf8转为utf8mb4
例：t1表字符编码为utf8mb4，t2表字符编码为utf8，当发生如下查询语句时：

select t2.* from t1, t2 where t1.f1 = t2.f1 and t1.f2=6;

此时会发生隐式字符编码转换，无法走t2表的索引，这条SQL相当于

select t2.* from t1, t2 where t1.f1=CONVERT(t2.f1 USING utf8mb4) and t1.f2=6;

处理方法：高级编码转低级，将查询条件转换为索引字段的编码

select t2.* from t1, t2 where CONVERT(t1.f1 USING utf8)= t2.f1 and t1.f2=6;

分页语句优化

排序偏移量大时，会丢弃大量无用数据，导致效率低下
```
SELECT film_id, title, description FROM `film` ORDER BY title LIMIT 900,10;
```
先执行ORDER BY 排序，再执行分页LIMIT，丢弃很多无用数据，效率低下。
且查询字段没有覆盖索引，需要进行回表操作。

title字段是有索引的，但是没有title和的description的联合索引，所以同时查询title和description需要进行回表。

优化思路：

先想办法走覆盖索引
得到所需数据的ID
根据所需数据的ID，得到最终结果集

优化步骤：
得到所需数据的主键，可以先走覆盖索引，查询最终ID

SELECT film_id FROM`film`ORDER BY title LIMIT 900,10;

再使用最终ID回表，原表与上面的结果连表，获取最终结果，优化效率

SELECT f.film_id, f.title, f.description FROM `film` f 
INNER JOIN (SELECT film_id FROM `film` ORDER BY title LIMIT 900,10 ) m 
ON f.film_id = m.film_id;

MySQL查询优化