MySQL性能调优
索引的本质
定义
索引是帮助MySQL高效获取数据的排好序的数据结构
索引数据结构
1.二叉树
二叉树特性：
右边元素大于等于父元素，左边元素小于父元素
key存储的是表中索引的值，value存储的是索引所在行的整行的地址。
通过索引找到索引所在行的地址，拿到地址之后可以获取到索引所在行的整行的数据。
如果二叉树的数据是有序的话，那么就会变成一个链表。
2.红黑树
在数据有序(单边增长)的时候会做一次平衡，红黑树也叫做平衡二叉树。
红黑树的弊端：
树的高度不确定。
3.Hash索引
对索引的key进行一次hash（例如MD5就是一种hash算法，MySLQ底层实现有 自己的Hash算法）计算就可以定位出数据存储的位置
很多时候hash索引要比B+Tree索引更高效
仅能满足‘=’ ，‘in’，不支持范围查询
hash冲突问题
MySQL在对索引的key做hash之后会丢到一个hash桶里面去
4.B-Tree
- 叶节点具有相同的深度，叶节点的指针为空
- 所有索引元素不重复
- 节点中的数据索引从左到由递增排列
data元素还是key，value，key是索引的值，value是索引所在行的磁盘文件地址。
5.B+Tree(B-Tree变种)
- 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
- 叶子节点包含所有索引字段
- 叶子节点用指针连接，提高区间访问的性能
非叶子节点两个索引之间存储的值是下一个非叶子节点所在的磁盘文件的地址。
查找过程：
把根节点的元素全部加载到内存中， 然后在内存中找要查找的索引所在的位置区间，然后拿到下一个非叶子节点或叶子结点的磁盘文件地址去比对。
查看MySQL文件页大小:
-- 查看MySQL文件页大小
show GLOBAL STATUS like '%innodb_page_size%'
MySQL一个节点页的大小大概是16kb。
一张表如果用int或bigint类型做主键，那么索引大小为8个字节，中间存储的下一个磁盘文件地址大概6个字节，16kb大小大概可以存储16kb/(8+6)个字节。即大概1170个元素。叶子节点根据存储类型不同，可能data存储的是索引所在行的地址，也可能存储的是索引所在行的其他列的数据，就算叶子节点数据存储为1kb，那么三层高度的B+Tree可以存储1170X1170X16大概是一千多万数据。
B+Tree在叶子节点有一个双向指针，它的节点元素是从左到右依次排好序的，那么就可以进行范围查找。
MySQL存储引擎索引实现
MySQL的data目录下的一个文件就对应一个数据库实例
其中MyISAM和Innodb存储引擎对应的表数据文件是不一样的
MyISAM存储引擎对应有三个数据文件：
.frm和.MYD和.MYI文件
.MYD存储数据
.MYI存储B+Tree组织的索引
Innodb对应两个数据文件，
.frm和.ibd文件
.ibd存储数据 和索引
存储引擎是形容数据库表的。
1.MyISAM存储引擎实现
MyISAM索引文件和数据文件是分离的(非聚集，也叫稀疏索引)
2.InnoDB存储引擎实现
InnoDB索引文件实现(聚集)
表数据文件本身就是按B+Tree组织的一个索引结构文件
聚集索引---叶子节点包含了完整的数据记录
为什么建议InnoDB表必须建主键?并且推荐使用整型的自增主键？
1. 因为这些数据必须要用B+Tree来组织，如果自带主键索引，那么就可以使用主键来组织B+Tree索引。如果不建立主键索引，那么MySQL会从第一列开始，选择一列所有数据都不相等的来建立B+Tree索引。如果都没有选到，MySQL会建立一列隐藏列，类似于RowId，MySQL会维护这个RowId的唯一性，然后用这个RowId来组织整个表的B+Tree索引。
所以推荐建立主键，因为MySQL的数据库资源是非常宝贵的，这些简单的事情完全可以自己完成，不必交给MySQL完成。
- 索引如果使用字符串类型的话存储占用的内存要大一点，还有在查找索引的时候需要比对，如果不是整型的话，比对的开销比较大。
- 如果索引是自增的话，那么在指定元素插满之后之后平衡之后再开一个节点存放下一个元素。如果是非自增的话，那么会导致分裂之后平衡然后在开一个节点，开销相对于自增的要大。
为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)
InnoDB整张表只会有一个聚集索引，如果你键了主键，那么它就会用主键来组织这个聚集索引，如果你没建，那么它就会选择全部不相等的那一列作为聚集索引，如果找不到，那么就会创建一个隐藏列作为聚集索引。
二级索引叶子节点存储的是主键，如果是根据二级索引查找数据的话，那么在二级索引构建的B+Tree中找到主键索引的值，然后拿这个主键索引的值去一级索引(即主键索引)构建的B+Tree上去查找数据。这个操作也成为回表操作。
二级索引也是非聚集索引。
索引最左前缀原则
联合索引的底层存储结构长什么样？
Explain详解和索引最佳实践
使用Explain关键字可以模拟优化器执行SQL语句，分析你的查询语句或者是结构的性能瓶颈。
在select语句之前增加explain关键字，MySQL会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。
注意：如果from中包含子查询，仍会执行该子查询，然后将结果放入临时表中。
Explain分析示例
-- EXPLAIN 分析示例
-- 示例表
DROP TABLE
IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` INT (11) NOT NULL,
`name` VARCHAR (45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO `actor` (`id`, `name`, `update_time`)
VALUES
(
1,
'a',
'2017-12-22 15:27:18'
),
(
2,
'b',
'2017-12-22 15:27:18'
),
(
3,
'c',
'2017-12-22 15:27:18'
);
DROP TABLE
IF EXISTS `film`;
CREATE TABLE `film` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR (10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO `film` (`id`, `name`)
VALUES
(3, 'film0'),
(1, 'film1'),
(2, 'film2');
DROP TABLE
IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
`id` INT (11) NOT NULL,
`film_id` INT (11) NOT NULL,
`actor_id` INT (11) NOT NULL,
`remark` VARCHAR (255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_film_actor_id` (`film_id`, `actor_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`)
VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 1);
EXPLAIN select * from actor;
在查询中的每个表都会输出一行，如果有两个join连接，那么会输出两行。
Explain两个变种
-
explain extended：
会在explain的基础上额外提供一些查询优化的信息。紧随其后通过show warnings命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有filtered列，是一个半分比的值，rowsXfiltered/100可以估算出将要和explain中前一个表进行连接的行数。（前一个表指explain中的id值比当前表id值小的表）
explain extended select * from film where id = 1;
explain extended select * from film where id = 1; show warnings;
/* select#1 */ select '1' AS `id`,'film1' AS `name` from `mysql_optimize`.`film` where 1 -
explain partitions：相比explain多了partitions字段，如果查询是基于分区表的话，会显示查询将要访问的分区。
explain中的列
-
id列
id列的编号就是select的序列号，有几个select就有几个id。并且id的顺序是按select出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行，id为NULL则最后执行
-
select_type列
select_type表示对应行是简单还是复杂的查询。
-
simple
简单查询。查询不包含子查询和union
-- 简单查询（simple） explain select * from film where id = 2;
-
primary
复杂查询中最外层的select
-
subquey
包含在select中的子查询(不再from字句中)
-
derived
包含在from字句中的子查询。MySQL会将结果存放在一个临时表中，也成为派生表(derived的英文含义)
set session optimizer_switch='derived_merge=off'; -- 关闭mysql5.7新特性对衍生条的合并优化 explain select (select 1 from actor where id = 1) from (select * from film where id = 2) der
-
-
table列
这一列表示explain的一行正在访问哪个表。
当from字句中有子查询时，table列是
格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询。 当有union时，union result的table列的值为<union1,2>,1和2表示参数union的select行的id。
-
type列
这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为:system>const>eq_ref>range>index>all
一般来说，得保证查询达到range级别，最好达到ref级别
- NULL:mysql能够在优化阶段分解查询语句，在执行阶段永不再再访问表或索引。例如:在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。
explain select min(id) from film
-
const,system
MySQL能对查询的部分进行优化并将其转化为一个常量(可以看show warnings的结果)。用于primary key或unique key的所有列与常数比较时，所以表最多有一个匹配行，读取一次，速度比较快。system是const的特例，表里只有一条元组匹配时为system
set session optimizer_switch='derived_merge=off'; -- 关闭mysql5.7新特性对衍生条的合并优化 EXPLAIN extended select * from (select * from film where id = 1) tmp;
-
eq_ref
primary key 或uninque key索引的所有部分被连接使用，最多只返回一条符合条件的记录。这可能是在const之外最好的连接类型了，简单的select查询不会出现这种type
set session optimizer_switch='derived_merge=off'; -- 关闭mysql5.7新特性对衍生条的合并优化 EXPLAIN select * from film_actor left join film on film_actor.film_id = film.id
-
ref
相比eq_ref,不使用唯一索引，而是使用普通索引或唯一索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行
-
简单select查询，name是普通索引(非唯一索引)
EXPLAIN select * from film where name = 'film1';
-
关联表查询,idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了fil_actor的左边前缀film_id部分
EXPLAIN select film_id from film LEFT JOIN film_actor on film.id = film_actor.film_id;
-
-
range
范围扫描通常出现在in,between,>,<.>=,<=等操作中。使用一个索引来检索给定范围的行
explain select * from actor where id > 1;
-
index
扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，索引这种通常比ALL要快一些。
EXPLAIN select * from film
-
ALL
即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。
EXPLAIN select * from actor;
-
-
possible_keys列
这一列显示查询可能使用哪些索引来查找
explain时可能出现possible_keys有列，而key显示NULL的情况，这种情况是因为表中数据不多，mysql认为索引对查询帮助不大，选择了全表查询
如果该列是NULL，则没有相关的所有。在这种情况下，可以通过检查where子句看是否可以创造一个适当的所有来提高查询性能，然后用explain查看效果。
-
key列
这一列显示的是MySQL实际采用哪一个索引来优化对该表的访问。
如果没有使用索引，则该列是NULL。如果想强制mysql使用或忽略possible_keys列中的索引，在查询中使用force_index,ignore index.
-
key_len列
这一列显示了mysql在索引中使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
举例来说，film_actor的联合索引idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int都是4字节。通过结果中的key_len=4可以推断出查询使用了第一个列:film_id来执行索引查找。
explain select * from film_actor where film_id = 2;
key_len的计算规则如下:
-
字符串
字符串，char(n)和varchar(n)，5.0.3以后版本中，**n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节。
char(n)：如果存汉字长度就是 3n 字节
varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为
varchar是变长字符串
-
数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
-
时间类型
date：3字节
timestamp：4字节
datetime：8字节
如果字段运行为NULL，需要一个字节记录是否为NULL
索引的最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
-
-
ref列
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有:const(常量)，字段名(例:file.id)
-
row列
这一列是mysql估计要读取并检测的行数，注意，这个不是结果集里的行数
-
extra列
这一列展示的是额外信息，常见的重要值如下：
-
useing index
使用覆盖索引.
覆盖索引定义:mysql执行计划explain结果里面的key有使用索引，如果select后面要查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index，覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其他字段值的过程
-- using index explain select film_id from film_actor where film_id = 1;
-
useing where
使用where语句来处理结果，并且查询的列未被索引覆盖
explain select * from actor where name = 'a';
-
useing index condition
查询的列不完全被索引覆盖，where条件中是一个前导列的范围。
explain select * from film_actor where film_id > 1;
-
useing temporary
mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先想到的是使用索引来优化。
actor.name没有索引，此时创建了临时表来distinct
explain select distinct name from actor;
file.name建立了索引idx_name,此时查询时extra是using index,没有使用临时表
EXPLAIN select distinct name from film;
-
use filesort
将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序，这种情况一般也是要考虑使用索引来优化的。
actor.name未创建索引，会浏览actor整个表，保存排序关键字那么和对应的id，然后排序name并检索行记录。
EXPLAIN select * from actor order by name
film.name建立了索引idx_name索引，此时查询时extra是using index
EXPLAIN select * from film order by name
-
select tables optimized away
使用某些聚合函数(比如max,min)来访问存在索引的某个字段
explain select min(id) from film;
-
索引最佳实践
示例表
-- 建表语句
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());
1.全值匹配
explain select * from employees where name = 'LiLei';
explain select * from employees where name = 'LiLei' and age ='22';
explain select * from employees where name = 'LiLei' and age ='22' and position = 'manage';
2.最左前缀法则
如果索引了多个列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
explain select * from employees where name = 'Bill' and age =31;
explain select * from employees where age = 30 and position ='dev';
explain select * from employees where position ='manager';
3.不要在索引列上做任何操作(计算，函数，（自动or手动）类型转换)，它会导致索引失效而转向全表扫描
explain select * from employees where left(name,5) = 'LiLei';
给hire_time增加一个普通索引：
ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
explain select * from employees where date(hire_time) = '2021-12-29'
转化为日期范围查询，有可能会走索引：
explain select * from employees where hire_time >= '2021-12-28' and hire_time <= '2021-12-29'
还原最初索引状态
alter table `employees` drop index `idx_hire_time`;
4.存储索引不能使用索引范围条件右边的列
explain select * from employees where name = 'LiLei' and age = 22 and position ='manager';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';
5.尽量使用覆盖索引(只访问索引的查询(索引列包含查询列))，减少select * 语句
explain select name,age from employees where name= 'LiLei' AND age = 23 AND position ='manager';
explain select name,age from employees where name= 'LiLei' AND age = 23 AND position ='manager';
6.mysql在使用不等于(!=或者<>)，not in,not exists的时候无法使用索引会导致全表扫描。<,>,<=,>=这些，mysql内部优化器会根据检索比例，表大小等多个因素整体评估是否使用索引
EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';
7.is null,is not null一般情况下也无法使用索引
EXPLAIN SELECT * FROM employees WHERE name is null
8.like以通配符开头('$abc...')mysql索引失效会变成全表扫描操作
EXPLAIN SELECT * FROM employees WHERE name like '%Lei'
EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'
问题:解决like'%字符串%'索引不被使用的方法？
-
使用覆盖索引，查询字段必须是建立覆盖索引的字段。
EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';也可以只查对应的字段
EXPLAIN SELECT name FROM employees WHERE name like '%Lei%';
-
如果不能使用覆盖索引，则可以需要借助搜索引擎
9.字符串不加单引号索引失效
EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;
10.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例，表大小等多个因素整体评估是否使用索引。
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei'
11.范围查询优化
给年龄添加单值索引
alter table `employees` add index `idx_age` (`age`) using btree;
explain select * from employees where age >=1 and age <=2000;
没走索引的原因:
mysql内部优化器会根据索引比例，表大小等多个因素整体评估是否使用索引。可能是由于单词数据量查询过大而导致优化器不走索引。
explain select * from employees where age >=1 and age <=22;
还原最初索引状态:
alter table employees drop index `idx_age`;
索引使用总结:
一条SQL在MySQL中的执行过程
MySQL内部组件结构
大体来说，MySQL可以分为Service层和存储引擎两部分
Service层
主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数 （如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
Store层
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在 最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。也就是说如果我们在create table时不指定 表的存储引擎类型,默认会给你设置存储引擎为InnoDB。
实例表的DDL语句
-- 示例表的DDL语句
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
下面我们来重点分析一些连接器，查询缓存，分析器，优化器，执行器分别主要干了哪些事情
连接器
我们知道由于MySQL是开源的，它有非常多的客户端，如navicat，mysql front，jdbc，SQLyog等非常丰富的客户端，这些客户端要向mysql发起通信都必须先跟Service端建立连接，而建立连接的工作就是由连接器完成的。
第一步：你会先连接到这个数据库上，这时接待你的就是连接器。连接器负责跟客户端建立连接，获取权限，维持和管理连接。连接命令一般是这么写的；
[root@192 ~]# mysql ‐h host[数据库地址] ‐u root[用户] ‐p root[密码] ‐P 3306[端口]
连接命令中的mysql是客户端工具，用来跟服务端建立连接。在完成经典的TCP握手之后，连接器就要开始认证你的身份了，这个时候用的就是你输入的用户名和密码。
- 如果你的用户名或密码不对，你会收到一个
Access denied for user的错误，然后客户端程序结束执行 - 如果你的用户名密码认证通过，连接器会到权限表里查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖此时读到的权限。
这就意味着，一个用户成功建立连接之后，即使你用管理员账号对这个用户的权限进行了修改，也不会影响已经存在连接的权限，修改完成之后，只有再新建的连接才会使用新的权限设置。用户的权限表在系统表空间的mysql的user表中。
修改user密码
CREATE USER 'username'@'host' IDENTIFIED BY 'password'; //创建新用户
grant all privileges on *.* to 'username'@'%'; //赋权限,%表示所有(host)
flush privileges //刷新数据库
update user set password=password(”123456″) where user=’root’;(设置用户名密码)
show grants for root@"%"; 查看当前用户的权限
连接完成之后，如果你没有后续的动作，这个连接就会处于空闲状态，你可以在show processlist命令中看到它。文本中的这个图就是show processlist的结果，其中Command列显示为Sleep的这一行，就表示系统里面有一个空闲的连接。
客户端如果长时间不发送command到Service端，连接器就会自动将它断开。这个时间是由参数wait_timeout控制的，默认值是8小时。
查看wait_timeout
show global variables like 'wait_timeout';
-- 设置全局服务器关闭非交互连接之前等待活动的秒数
set global wait_timeout=28800;
如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒:Lost Connection to MySQL Service during query.这个时候如果你要继续操作，就需要重新连接，然后再执行请求。
数据库里面，长连接是指，成功连接后，如果客户端持有请求，则一直使用同一个连接。端连接是指，每次执行完很少的几次查询之后就断开连接，下次查询再新建一个连接。
在开发当中我们大多数用的都是长连接，把连接放在Pool内进行管理，但是长连接有时候会导致MySQL占用内存涨得特别快，这是因为MySQL在执行的过程中临时使用的内存是管理在连接对象里面的，这些资源会在连接断开的时候才释放。所以，如果长连接累计下来，可能会导致内存占用太大，被系统强杀掉(OOM),从现象来看就是MySQL异常重启了。
怎么解决这类问题呢？
1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2. 如果你使用的是MySQL5.7或者更新的版本，可以在每次执行完一次比较大的操作后，通过执行mysql_reset_connection来重新初始化资源。这个过程不需要重连和重做权限验证，但是会将连接恢复到刚创建完时的状态。
查询缓存
常用的一些操作
show databases; -- 显示所有数据库
use dbname； -- 打开数据库：
show tables; -- 显示数据库mysql中所有的表；
describe user; -- 显示表mysql数据库中user表的列信息；
连接建立完成之后，你就可以执行select语句了。执行逻辑会来到第二步：查询缓存。
MySQL拿到一个查询请求后，会先到查询缓存里看看，之前是不是执行过这条语句。之前执行过的语句及结果会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。
如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被放入查询缓存中。你可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。
大多数情况下，查询缓存就是一个鸡肋，为什么呢？
因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要对一个表有更新，这个表上的所有查询缓存都会被清空。因此很有可能你费劲地把结果缓存起来，还没使用，就被一个更新清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。
一般建立大家在静态表里使用查询缓存。什么叫静态表呢？就是一般我们极少的更新表。比如，一个系统配置表，字典表，那这张表的查询才使用使用查询缓存。好在MySQL也提供了这种按需使用的方式。你可以将my.cnf参数query_cache_type设置为DEMAND.
这样对于默认的SQL语句都不会使用查询缓存。而对于你确定要使用查询缓存的语句，可以使用SQL_CACHE显示指定，像下面这个语句一样。
select SQL_CACHE * from test where ID=5；
查看当前mysql实例释放开启缓存机制
show global variables like "%query_cache_type%";
监控查询缓存的命中率
show status like'%Qcache%'; -- 查看运行的缓存信息
- Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，该值显示比较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整体
- Qcache_free_memory：查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整
- Qcache_hits:表示还有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想
- Qcache_inserts:表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚系统时，查询缓存是空的，这很正常。
- Qcache_lowmem_prunes：该参数记录有多少条查询是因为内存不足而被移除查询缓存。通过这个值，用户可以适当调整缓存大小。
- Qcache_not_cached：表示因为query_cache_type的设置而没有被缓存的查询数量
- Qcache_queries_in_cache：当前缓存中缓存的查询数量
- Qcache_total_blocks：当前缓存的block数量
MySQL 8.0已经移除了查询缓存功能。
分析器
如果缓存没有命中查询缓存，就要开始真正的执行语句了。首先，MySQL需要知道你要做什么，因此需要对SQL语句做解析。分析器先会做词法分析。你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。
MySQL从你输入的SELECT这个关键字识别出来，这时一个查询语句。它也要把字符串T识别成表名T，把字符串ID识别为列ID.
做完这些识别以后，就要做语法分析。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。
如果你的语句不对，就会收到You have an error in your SQL syntax的错误提醒。
词法分析器原理
词法分析器分成6个主要步骤完成对SQL语句的分析
- 词法分析
- 语法分析
- 语义分析
- 构造执行树
- 生成执行计划
- 计划的执行
下图SQL词法分析的过程步骤：
SQL语句的分析为词法分析和语法分析，MySQL的词法分析由MySQLLex【MySQL自己实现的】完成，语法分析由Bison生成。那么除了Bison之外，Java当中也有开源的词法结果分析工具，例如Antlr4,ANTLR词法生成一个解析器，可以构建和遍历解析树，可以在IDEA经过bison词法分析后，生成这样的语法树。
优化器
经过分析器，MySQL就知道你要做什么了。在开始执行之前，还要经过优化器的处理。
优化器是在表里面有多个索引的时候，决定使用哪个索引。或者在一个语句有多表关联(join)的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句时执行两个表的join。
select * from test1 join test2 using(ID) where test1.name=yangguo and test2.name=xiaol ongnv;
既可以先从表test1里面取出name=yangguo的记录的ID值，再根据ID值关联到表test2，再判断test2里面的name的值是否等于yangguo。
也可以先从表test2里面取出name=yangguo的记录的ID值，再根据ID值关联到表test1，再判断tes1里面的name的值是否等于yangguo。
这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定使用哪一个方案。优化器阶段完成之后，这个语句的执行方案就确定下来了，然后进入执行器阶段。
执行器
开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限。
如果没有，就会返回没有权限的错误(在工程实现上，如果命中缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用precheck验证权限)；
如果有权限，就打开表继续执行。打开表的时候，执行器会根据表的引擎定义，去使用这个引擎提供的接口。
例如以下SLQ
select * from test where id=1;
这里例子中的ID是没有索引的，那么执行器的执行流程是这样的:
- 调用InnoDB引擎接口取这个表的第一行，判断ID值是否等于10，如果不是则跳过，如果是则将这行的结果存在结果集中。
- 调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行。
- 执行器上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
至此，这个语句的执行就完成了。对于有索引的表，执行的逻辑也差不多。第一次调用的是取满足条件的第一行这个接口，之后循环取满足条件的下一行这个接口，这些接口都是在引擎中已经定义好的。你会在数据库的慢查询日志中看到一个rows_examined的字段，表示这个语句执行的过程中扫描了多少行，这个值就是在执行器每次调用引擎获取数据行时累加的。在有些场景下，执行器调用一次，在引擎内部扫描多行，因此引擎扫描行数跟rows_examined并不是完全相同的。
bin-log归档
SQL执行时，会将SQL语句的执行逻辑记录在我们的bin-log当中，那么什么是bin-log呢？
bin-log是Service层实现的二进制日志，它会记录我们的cud操作，bin-log有以下几个特点
- Binlog在MySLQ的Service层实现(引擎共用)
- Binlog为逻辑日志，记录的是一条语句的原始逻辑
- Binlog不限大小，追加写入，不会覆盖以前的日志
如果我们误删了数据库，可以使用binlog进行归档。要使用binlog归档，首先我们要记录binlog，因此需要开启MySQL的binlog功能。
配置my.cnf
#配置开启binlog
log‐bin=/usr/local/mysql/data/binlog/mysql‐bin
# 注意5.7以及更高版本需要配置本项：server‐id=123454（自定义,保证唯一性）;
# binlog格式，有3种statement,row,mixed
binlog‐format=ROW
#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定
sync‐binlog=1
binglog命令
show variables like '%log_bin%';-- 查看bin‐log是否开启
flush logs; -- 会多一个最新的bin‐log日志
show master status;-- 查看最后一个bin‐log日志的相关信息
reset master; -- 清空所有的bin‐log日志
查看binlog内容
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin. 000001 -- 查看binlog内容
binlog里的内容不具备可读性，所以需要我们去判断恢复的逻辑点位，怎么观察呢？看重点信息，比如begin,commit这种关键词信息，只要在binlog当中看到了，你就可以理解为begin-commit之间的信息是一个完整的事务逻辑，然后再根据位置position判断恢复即可。
binlog内如如下:
数据归档操作
-- 从bin‐log恢复数据
-- 恢复全部数据
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p tuling(数据库名)
-- 恢复指定位置数据
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults ‐‐start‐position="408" ‐‐stop‐position="731" /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p tuling(数据库)
-- 恢复指定时间段数据
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 ‐‐stop‐date= "2018‐03‐02 12:00:00" ‐‐start‐date= "2019‐03‐02 11:55:00"|mysql ‐uroot ‐p test(数 据库)
归档测试
-
定义一个存储过程，写入数据
drop procedure if exists tproc; delimiter $$ create procedure tproc(i int) begin declare s int default 1; declare c char(50) default repeat('a',50); while s<=i do start transaction; insert into test values(null,c); commit; set s=s+1; end while; end$$ delimiter ; -
删除数据
truncate test; -
利用binlog归档
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐ bin.000001 |mysql ‐uroot ‐p tuling(数据库名) -
归档完毕，数据恢复
MySQL索引实战
示例表
-- 示例表
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());
-- 插入一些示例数据
drop procedure if exists insert_emp;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();
举一个大家都不容易理解的例子
-
联合索引第一个字段用范围不会走索引
EXPLAIN select * from employees where name > 'LiLei' AND age = 22 AND position ='manager';
结论:联合索引的第一个字段就用范围查找是不会走索引的，MySQL内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描。
-
强制走索引
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 and position ='manager';
结论:虽然使用了强制走索引让联合索引的第一个字段范围查找也走了索引，扫描的行看上去也少了点，但是最终的查找效率并不一定比全表扫描高。
做一个小实验
-- 关闭查询缓存 set global query_cache_size=0; set global query_cache_type=0; -- 执行时间0.817 SELECT * FROM employees WHERE name > 'LiLei'; -- 执行时间2.2771 SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei'; -
索引覆盖优化
explain select name,age,position from employees where name > 'LiLei' AND age = 22 AND position ='manag er';
-
in和or在表数据量比较大的情况下会走索引，在表记录不多的情况下会全表扫描
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';
做一个小实验，创建和employees相同表结构的employees_copy表，插入三条数据，然后执行上面的sql的执行计划。
-- 做一个小实验 -- 示例表 DROP TABLE IF EXISTS `employees_copy`; CREATE TABLE `employees_copy` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位', `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间', PRIMARY KEY (`id`), KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表'; INSERT INTO employees_copy(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW()); INSERT INTO employees_copy(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW()); INSERT INTO employees_copy(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());EXPLAIN SELECT * FROM employees_copy WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees_copy WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';
-
like kk%一般情况都会走索引
EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees_copy WHERE name like 'LiLei%' AND age = 22 AND position ='manager';
索引下推(Index Condition Pushdown,ICP)
上面例子中的like kk%其实就是用到了索引下推。
对于辅助的联合索引(name,age,position)，正常情况下按照最左前缀原则，select * from employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager,这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。
在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到的名字是'LiLei'开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出响应的记录，再对比age和position这两个字段是否符合。
MySQL5.6引入了索引下推，可以在索引遍历的过程中，对索引中所包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效减少回表的次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是‘LiLei’开头的索引后，同时还会在索引里过滤age和position这两个字段，拿着过滤完的索引到对应的主键id再回表查整行数据。
索引下推回减少回表的次数，对于InnoDB引擎的表索引下推只能用于二级索引，InnoDB的主键索引(聚簇索引)树子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。
为什么范围查找MySQL没有用索引下推优化?
估计是MySQL认为范围查找过滤的结果集过大，like KK%在绝大多数情况来看，过滤后的结果集是比较小的，所以这里MySQL选择给like kk%用了索引下推，当然这也不是绝对的，有时 like kk%也不一定就会走索引下推。
MySQL如何选择合适的索引
EXPLAIN select * from employees where name > 'a';
这里会走全表扫描。因为如果用name索引需要遍历name字段联合索引树，然后还要根据遍历出来的主键值去主键索引里再查出最终的数据，成本比全表扫描还高。如果想用到索引，可以使用覆盖索引，这样只需要遍历name字段的联合索引树就能拿到所有的结果。如下:
EXPLAIN select name,age,position from employees where name > 'a' ;
EXPLAIN select * from employees where name > 'zzz' ;
对于上面这两种name>'a'和name>'z'的执行结果，mysql最终是否选择走索引或者一张表涉及多个索引，mysql最终如何选择索引，我们可以用trace工具来一探究竟，开启trace工具会影响mysql的性能，所以只能临时分析sql使用，用完后立即关闭。
trace工具用法：
set session optimizer_trace="enabled=on",end_markers_in_json=on; -- 开启trace
select * from employees where name > 'a' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看查看trace字段：
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'a')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`employees`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`employees`",
"range_analysis": {
"table_scan": {
"rows": 100261,
"cost": 20343
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position",
"usable": true,
"key_parts": [
"name",
"age",
"position",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_name_age_position",
"ranges": [
"a < name"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 50130,
"cost": 60157,
"chosen": false,
"cause": "cost"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`employees`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 100261,
"access_type": "scan",
"resulting_rows": 100261,
"cost": 20341,
"chosen": true,
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 100261,
"cost_for_plan": 20341,
"sort_cost": 100261,
"new_cost_for_plan": 120602,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'a')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'a')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
] /* steps */,
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`employees`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 100003,
"examined_rows": 100003,
"number_of_tmp_files": 30,
"sort_buffer_size": 262056,
"sort_mode": "<sort_key, packed_additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
-- 关闭trace
set session optimizer_trace="enabled=off";
常见SQL深入优化
order by 和group by 优化
-
Case1
explain select * from employees where name = 'LiLei' and position ='dev' order by age;
分析:利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列在排序过程中，因为Extra字段里没有using filesort
-
Case2
explain select * from employees where name = 'LiLei' order by position;
分析：从explain的执行结果来看：key_len=74,查询使用了name索引，由于用了position进行排序，跳过了age，出现了using filesort.
-
Case3
explain select * from employees where name = 'LiLei' order by age,position;
分析:查找只用到了name,age和position用于排序，无Using filesort
-
Case4
explain select * from employees where name = 'LiLei' order by position,age;
分析:和Case3中explain的执行结果是一样的，但是出现了Using filesort，因为索引的创建顺序为name,age,position,但是排序的时候age和position颠倒位置了。
-
Case5
explain select * from employees where name = 'LiLei' and age = 18 order by position,age;
分析:和Case4对比，在Extra中并未出现Using filesort，因为age是常量，在排序中会被优化，所以索引未颠倒，不会出现Using filesort
-
Case6
explain select * from employees where name = 'zuge' order by age asc,position desc;
分析:虽然排序的字段与索引顺序一眼个，且order by默认升序，但是这里position desc变成了降序，导致索引里的排序方式不同，从而产生Using filesort。MySQL8以上的版本有降序索引可以支持这种查询方式。
-
Case7
explain select * from employees where name in( 'LiLei','zuge') order by age ,position ;
分析:对于排序来说，多个条件也是范围查询
-
Case8
explain select * from employees where name > 'a' order by name ;
可以使用覆盖索引优化
explain select name,age,position from employees where name > 'a' order by name ;
优化总结
- MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成的排序。index效率高，filesort效率低。
- Order by满足两种情况会使用Using index
- order by语句使用索引最左前列
- 使用where字句与order by字句条件列组合满足索引最左前列
- 尽量在索引列上完成排序，遵循索引建立(索引创建的顺序)时的最左前缀法则
- 如果order by的条件不在索引列上，就会产生Using filesort
- 能用覆盖索引尽量用覆盖索引
- group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可用加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。
Using filesort文件排序原理详解
filesort文件排序方式
单路排序
是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。用trace工具可以看到sort_mode信息里显示sort_key,additional_fileds或者sort_key,packed_additional_fields
双路排序
又叫回表排序模式，是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在sort_buffer中进行排序，排序完后需要再次取回其它需要的字段。用trace工具可以看到sort_mode信息里显示< sort_key, rowid >
MySQL是通过比较系统变量max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断使用那种排序模式。
- 如果字段的总长度小于max_length_for_sort_data，那么使用单路排序模式
- 如果字段的总长度大于max_length_for_sort_data，那么使用双路排序模式
示例验证各种排序方式
explain select * from employees where name = 'zhuge' order by position ;
查看下这条sql对应trace结果如下(只展示排序部分)：
-- 开启trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
select * from employees where name = 'zhuge' order by position;
select * from information_schema.OPTIMIZER_TRACE;
-- 关闭trace
set session optimizer_trace="enabled=off";
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` = 'zhuge') order by `employees`.`position`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`employees`.`name` = 'zhuge')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` = 'zhuge')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` = 'zhuge')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` = 'zhuge')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`employees`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`employees`",
"field": "name",
"equals": "'zhuge'",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`employees`",
"range_analysis": {
"table_scan": {
"rows": 100261,
"cost": 20343
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position",
"usable": true,
"key_parts": [
"name",
"age",
"position",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_name_age_position",
"ranges": [
"zhuge <= name <= zhuge"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 1,
"cost": 2.21,
"chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_name_age_position",
"rows": 1,
"ranges": [
"zhuge <= name <= zhuge"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 1,
"cost_for_plan": 2.21,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`employees`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_name_age_position",
"rows": 1,
"cost": 1.2,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "idx_name_age_position"
} /* range_details */,
"chosen": false,
"cause": "heuristic_index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 1.2,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` = 'zhuge')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": null
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
} /* clause_processing */
},
{
"added_back_ref_condition": "((`employees`.`name` <=> 'zhuge'))"
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
] /* steps */,
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "idx_name_age_position",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`employees`",
"pushed_index_condition": "(`employees`.`name` <=> 'zhuge')",
"table_condition_attached": null
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 0,
"examined_rows": 0,
"number_of_tmp_files": 0,
"sort_buffer_size": 262056,
"sort_mode": "<sort_key, packed_additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
单路排序的详细过程
1. 从索引name找到第一个满足name='zhuge'条件的主键id
2. 根据主键id取出整行，取出所有字段的值，存放sort_buffer中
3. 从索引name找到下一个满足name='zhuge'条件的主键id
4. 重复步骤2,3直到不满足name='zhuge'
5. 对sort_buffer中的数据按照字段position进行排序
6. 返回结果给客户端
双路排序的详细过程
1. 从索引name找到第一个满足name='zhuge'条件的主键id
2. 根据主键id取出整行，把排序字段position和主键id这两个字段放到sort_buffer中
3. 从索引name找到下一个满足name='zhuge'条件的主键id
4. 重复步骤2,3直到不满足name='zhuge'
5. 对sort_buffer中的字段position和主键字段，按position进行排序
6. 遍历排好序的id字段和position字段，按id的值回到原表中取出所有字段的值返回给客户端
其实对比这两个排序模式，单路排序会把所有需要查询的字段都放到sort_buffer中，而双路排序只会把主键字段和需要排序的字段放到sort_buffer中进行排序，然后再通过主键回到原表查询需要的字段。
如果MySQL排序内存sort_buffer配置的比较小并且没有条件继续增加了，可以适当把max_length_for_sort_data 配置小点，让优化器选择使用双路排序算法，可以在sort_buffer中一次排序更多的列，只是需要再根据主键回到原表取数据。
如果MySQL排序内存有条件可以配置得比较大，可以适当增大max_length_for_sort_data的值，让优化器优先选择字段排序(单路排序)，把需要的字段放到sort_buffer中，这样排序后就会直接从内存里返回查询结果了。
所以，MySQL通过max_length_for_sort_data这个参数来控制排序，在不同的场景使用不同的排序模式，从而提高排序效率。
注意：如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，MySQL很多参数设置都是做过优化的，不要轻易调整。
索引设计原则
-
代码先行，索引后上
不知大家一般是怎么给数据表建立索引的，是建完表立马就建立索引吗？
这其实是不对的，一般是等到主体业务功能开发完毕，把涉及到该表相关的SQL都要拿出来分析之后再建立索引
-
联合索引尽量覆盖条件
比如可以设计一个或两三个联合索引(建立少建单值索引)，让每一个联合索引都尽量去包含sql语句里面的where，order by，group by的字段，还要确保这些联合索引的字段顺序尽量满足SQL查询的最左前缀原则。
-
不要在小基数字段上建立索引
索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共有100万行记录，其中有个性别的字段，其值不是男就是女，那么该字段的基数就是2,。
如果对这种小基数的字段建立索引的话，还不如全表扫描了，因为你的索引树里面就包含男和女两种值，根本没法进行快速排序的二分查找，那用索引就没有太大的意义了。
一般建立索引，尽量使用哪些基数比较大的字段，就是指比较多的字段，那么才能发挥B+树快速二分查找的优势来。
-
长字符串我们可以采用前缀索引
尽量对字段类型比较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。
当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕占用一些磁盘空间也是有必要的。
对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化一下，比如针对这个字段的前20个字符建立索引，也就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于key index(name(20),age,position),此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引里提取出来完整的name字段值进行比对。
但是加入你是要order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没办法用上索引的，group by也是同理。所以这里大家要对前缀索引有一个了解。
-
where和order by冲突时优先使用where
在where和order by出现索引设计冲突时，到底是针对where去设计索引还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引？
一般这种时候，往往是让where条件去使用索引来快速筛选出一部分指定的数据，接着再进行排序。因为大多数情况下基于索引进行where条件筛选往往可以最快速地筛选出你要的少部分数据，然后做排序的成本可能会小很多。
-
基于慢SQL查询做优化
可以根据监控后台的一些慢SQL，针对这些慢SQL做特定的索引优化。
关于慢sql查询不清楚的可以参考这篇文章：https://blog.csdn.net/qq_40884473/article/details/89455740
索引设计实战
以社交场景APP来举例，我们一般会去搜索一些好友，这里面就涉及到堆用户信息的筛选，这里肯定就是对用户user表进行搜索了，这个表一般来说数据量会比较大，我们先不考虑分库分表的情况。比如，我们一般会筛选地区(省市)，性别，年龄，身高，爱好之类的，有的APP可能用户还有评分，比如按用户的受欢迎度评分，我们可能还会根据评分来筛选等待。
对于后台程序来说除了过滤用户的各种条件，还要进行分页之类的处理，可能会产生类似SQL语句执行：
select xx from user where xx=xx and xx=xx order by xx limit xx,xx,对于这种情况如何合理设计索引，比如用户可能经常会根据省市优先筛选同城的用户，还有根据性别去筛选，那我们是否应该设计一个联合索引(province,city,sex)?这些字段好像基数都不大，其实是应该的，因为这些字段的查询太频繁了。
假设又有用户根据年龄范围去筛选了，比如where province=xx and city=xx and age>=xx and age<=xx ,我们尝试把age字段加入到联合索引(province,city,sex,age),注意，一般这种范围查找的条件都要放到最后，因为联合索引范围之后的条件是不能使用索引的，但是对于当前的这种情况依然用不到age这个索引字段，因为用户没有筛选sex字段，那么要如何优化呢？其实我们可以这么来优化SQL写法:where province=xx and city=xx and sex in ('female','male') and age>=xx and age<=xx、。
对于爱好之类的字段也可以类似SEX字段的处理，所以可以把爱好字段也加入到索引(province,city,sex,hobby,age)
假设可能还有一个筛选条件，比如要筛选最近一周登录过的用户，一般大家肯定希望跟活跃用户交友，这样能尽快收到反馈，对于后台的SQL可能是这样的:where province=xx and city=xx and sex in ('female','male') and age>=xx and age<=xx and latest_login_time>= xx 。那么我们是否能把latest_login_time 字段也加入索引了？比如(province,city,sex,hobby,age,latest_login_time)，显然是不行的，那么怎么来优化这种情况呢？其实我们可以再设计一个字段段is_login_in_latest_7_days，如果用户一周内有登录就为1，否则为0，那么我们就可以把索引设计为(province,city,sex,hobby,is_login_in_latest_7_days,age)来满足上面的这种场景了。
一般来说，通过这么一个多字段的索引是能过滤掉大部分数据的，就保留小部分数据下来基于磁盘文件进行order by语句的排序，最后基于limit进行分页，一般性能还是比较高的。不同有时用户可能还会这么来查询，查询受欢迎度比较高的女生，比如:where sex = 'female' order by score limit xx,xx,那么上面的那个索引是很难用上的，不能把太多的字段以及太多的值都用in语句凭借到SQL里，那么该怎么办？其实我们可以再设计一个辅助的联合索引，比如(age,score)，这样就能满足查询的要求了。
以上就是一些索引设计的思路，核心思想就是，尽量利用一两个复杂的多字段联合索引，抗下80%以上的查询，然后用一两个辅助索引尽量抗下剩下的一些非典型的查询，保证这种大数据量表的查询尽可能多地充分利用索引，这样就能保证查询速度和性能了。
分页查询优化
很多时候我们业务系统实现分页功能可能会用如下sql实现
select * from employees limit 10000,10;
表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010
条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率
是非常低的。
常见的分页场景优化技巧：
1、根据自增且连续的主键排序的分页查询
首先来看一个根据自增且连续主键排序的分页查询的例子：
select * from employees limit 90000,5;
该 SQL 表示查询从第 90001开始的五行数据，没添加单独 order by，表示通过主键排序。我们再看表 employees ，因
为主键是自增并且连续的，所以可以改写成按照主键去查询从第 90001开始的五行数据，如下：
select * from employees where id > 90000 limit 5;
查询的结果是一致的。我们再对比一下执行计划：
EXPLAIN select * from employees limit 90000,5;
EXPLAIN select * from employees where id > 90000 limit 5;
显然改写后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高
但是，这条改写的SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致，如下图试验
所示（先删除一条前面的记录，然后再测试原 SQL 和优化后的 SQL）：
两条 SQL 的结果并不一样，因此，如果主键不连续，不能使用上面描述的优化方法。
另外如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写会导致两条 SQL 的结果不一致。所以这种改写得满足以下两个条件：
-
主键自增且连续
-
结果是按照主键排序的
2、根据非主键字段排序的分页查询
再看一个根据非主键字段排序的分页查询，SQL 如下：
select * from employees ORDER BY name limit 90000,5;
EXPLAIN select * from employees ORDER BY name limit 90000,5;
发现并没有使用 name 字段的索引（key 字段对应的值为 null），具体：扫描整个索引并查找到没索引
的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。知道不走索引的原因，那么怎么优化呢？
其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL
改写如下
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
需要的结果与原 SQL 一致，执行时间减少了一半以上，我们再对比优化前后sql的执行计划：
原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。
Join关联查询优化
-- 插入一些示例数据
-- 往t1表插入1万行记录
DROP PROCEDURE
IF EXISTS insert_t1;
delimiter ;;
CREATE PROCEDURE insert_t1 ()
BEGIN
DECLARE i INT ;
SET i = 1 ;
WHILE (i <= 10000) DO
INSERT INTO t1 (a, b)
VALUES
(i, i) ;
SET i = i + 1 ;
END
WHILE ;
END;;
delimiter ;
CALL insert_t1 ();
-- 往t2表插入100行记录
DROP PROCEDURE
IF EXISTS insert_t2;
delimiter ;;
CREATE PROCEDURE insert_t2 ()
BEGIN
DECLARE i INT ;
SET i = 1 ;
WHILE (i <= 100) DO
INSERT INTO t2 (a, b)
VALUES
(i, i) ;
SET i = i + 1 ;
END
WHILE ;
END;;
delimiter ;
CALL insert_t2 ();
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
-- 1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法
EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;
mysql的表关联常见有两种算法
- Nested-Loop Join 算法
- Block Nested-Loop Join 算法
1、嵌套循环连接 Nested-Loop Join(NLJ) 算法
一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动
表）里取出满足条件的行，然后取出两张表的结果合集。
EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
从执行计划中可以看到这些信息：
驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优
化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表，
当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。
使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算
法是 NLJ。
上面sql的大致流程如下：
-
从表 t2 中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据）；
-
从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
-
取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
-
重复上面 3 步。
整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表
中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100
行)。因此整个过程扫描了 200 行。
如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低(下面有详细解释)，mysql会选择Block Nested-Loop Join
算法。
2、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。
EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;
Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。
上面sql的大致流程如下：
-
把 t2 的所有数据放入到 join_buffer 中
-
把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
-
返回满足 join 条件的数据
整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) =
10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是
100 * 10000= 100 万次。
这个例子里表 t2 才 100 行，要是表 t2 是一个大表，
join_buffer 放不下怎么办呢？
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，
就是分段放。
比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然
后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再
次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表
对于关联sql的优化
-
关联字段加索引，让mysql做join操作时尽量选择NLJ算法
-
小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去
mysql优化器自己判断的时间
straight_join解释：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执
行顺序。
比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。
1. straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指
定了表的执行顺序）
2. 尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因
为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。
对于小表定义的明确
在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据
量，数据量小的那个表，就是“小表”，应该作为驱动表
in和exsits优化
原则：小表驱动大表，即小的数据集驱动大的数据集
in：当B表的数据集小于A表的数据集时，in优于exists
select * from A where id in (select id from B)
#等价于
for(select id from B){
select * from A where A.id = B.id
}
exists：当A表的数据集小于B表的数据集时，exists优于in
将主查询A的数据，放到子查询B中做条件验证，根据验证结果（
true或false）来决定主查询的数据是否保留
select * from A where exists (select 1 from B where B.id = A.id)
#等价于:
for(select * from A){
select * from B where B.id = A.id
}
#A表与B表的ID字段应建立索引
1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会
忽略SELECT清单,因此没有区别
2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析
count(*)查询优化
-- count(*)查询优化
-- 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
set global query_cache_size=0;
set global query_cache_type=0;
EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;
注意：以上4条sql只有根据某个字段count不会统计字段为null值的数据行
四个sql的执行计划一样，说明这四个sql执行效率应该差不多
字段有索引：count(*)≈count(1)>count(字段)>count(主键 id) //字段有索引，count(字段)统计走二级索引，二
级索引存储数据比主键索引少，所以count(字段)>count(主键 id)
字段无索引：count(*)≈count(1)>count(主键 id)>count(字段) //字段没有索引count(字段)统计走不了索引，
count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)
count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出
字段，所以理论上count(1)比count(字段)会快一点。
count(*) 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用
count(列名)或count(常量)来替代 count(*)。
为什么对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索
性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)。
常见优化方法
-
查询mysql自己维护的总行数
对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被
mysql存储在磁盘上，查询不需要计算
对于innodb存储引擎的表mysql不会存储表的总记录行数(因为有MVCC机制，后面会讲)，查询count需要实时计算
-
show table status
如果只需要知道表总行数的估计值可以用如下sql查询，性能很高
-
将总数维护到Redis里
插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难
保证表操作和redis操作的事务一致性
-
增加数据库计数表
插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作
阿里巴巴Mysql规范解读
MySQL数据类型选择
在MySQL中，选择正确的数据类型，对于性能至关重要。一般应该遵循下面两步：
-
确定合适的大类型：数字、字符串、时间、二进制；
-
确定具体的类型：有无符号、取值范围、变长定长等。
在MySQL数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量
把字段定义为NOT NULL，避免使用NULL。
1.数值类型
优化建议
-
如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
-
建议使用TINYINT代替ENUM、BITENUM、SET。
-
避免使用整数的显示宽度(参看文档最后)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用
INT。
- DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意
长度设置。
-
建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
-
整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。
2、日期和时间
优化建议
-
MySQL能存储的最小时间粒度为秒。
-
建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
-
用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。
-
当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），
MySQL会自动返回记录插入的确切时间。
-
TIMESTAMP是UTC时间戳，与时区相关。
-
DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。
-
除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般
会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。
-
有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐
它
3、字符串
优化建议
-
字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。
-
CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些
要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计
算的准确性和完整性。
-
尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。
-
BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。
-
BLOB和TEXT都不能有默认值。
PS：INT显示宽度
我们经常会使用命令来创建数据表，而且同时会指定一个长度，如下。但是，这里的长度并非是TINYINT类型存储的最大
长度，而是显示的最大长度。
CREATE TABLE `user`(
`id` TINYINT(2) UNSIGNED
);
这里表示user表的id字段的类型是TINYINT，可以存储的最大数值是255。所以，在存储数据时，如果存入值小于等于
255，如200，虽然超过2位，但是没有超出TINYINT类型长度，所以可以正常保存；如果存入值大于255，如500，那么
MySQL会自动保存为TINYINT类型的最大值255。
在查询数据时，不管查询结果为何值，都按实际输出。这里TINYINT(2)中2的作用就是，当需要在查询结果前填充0时，
命令中加上ZEROFILL就可以实现，如：
`id` TINYINT(2) UNSIGNED ZEROFILL
这样，查询结果如果是5，那输出就是05。如果指定TINYINT(5)，那输出就是00005，其实实际存储的值还是5，而且存
储的数据不会超过255，只是MySQL输出数据时在前面填充了0。
换句话说，在MySQL命令中，字段的类型长度TINYINT(2)、INT(11)不会影响数据的插入，只会在使用ZEROFILL时有
用，让查询结果前填充0。
