索引优化于查询优化

都有那些维度可以进行数据库调优？简而言之：

~ 索引失效，没有充分利用到索引 —— 索引建立

~ 关联查询太多join（设计缺陷或不得已的需求） —— sql优化

~ 服务器调优及各个参数设置 （缓冲，线程数等）—— 调整my.cnf

~ 数据过多——分库分表

关于数据库调优的只是点非常分散。不同的DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致分析。

虽然sql查询优化的技术有很多，但是大方向上完全可以分成 物理查询优化 和 逻辑查询优化 两大块。

~ 物理查询优化就是通过 索引 和 表连接方式 等技术来进行优化，这里重点需要掌握索引的使用。

~ 逻辑查询优化就是通过sql 等级变换 提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高

---

1. 数据准备

学生表插入50W数据，班级表插入1W数据。

步骤1：建表

# 班级表
create table class (
    `id` int(11) not null auto_increment,
    `className` varchar(30) default null,
    `address` varchar(40) default null,
    `monitor` int null,
    primary key (`id`)
) engine= innodb auto_increment=1 default charset=utf8;

# 学生表
create table student(
    `id` int(11) not null auto_increment,
    `stuno` int not null,
    `name` varchar(20) default null,
    `age` int(3) default null,
    `classId` int(11) default null,
    primary key (`id`)
) engine= innodb auto_increment=1 default charset=utf8;

步骤2：设置参数

命令开启：允许创建函数设置：
 

SET GLOBAL log_bin_trust_function_creators=1; 

步骤3：构建函数

# 随机产生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO  
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));  
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

# 用于随机产生多少到多少的编号
DELIMITER //
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
DECLARE i INT DEFAULT 0;  
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))   ;
RETURN i;  
END //
DELIMITER ;

步骤4：创建存储过程

#创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE  insert_stu(  START INT ,  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    #设置手动提交事务
 REPEAT  #循环
 SET i = i + 1;  #赋值
 INSERT INTO student (stuno, NAME ,age ,classId ) VALUES ((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  #提交事务
END //
DELIMITER ;


#执行存储过程，往class表添加随机数据
DELIMITER //
CREATE PROCEDURE `insert_class`(  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO class ( classname,address,monitor ) VALUES (rand_string(8),rand_string(10),rand_num(1,100000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END //
DELIMITER ;


#执行存储过程，往class表添加1万条数据  
CALL insert_class(10000);

#执行存储过程，往stu表添加50万条数据  
CALL insert_stu(100000,500000);

2. 索引失效案例

mysql 中 提高性能 的一个最有效的方式时对数据表 设计合理的索引。索引提供了高效访问数据的方法，并且加快查询速度，因此索引对查询的速度有着至关重要的影响。

~ 使用索引可以 快速的定位 表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。

~ 如果查询时没有使用索引，查询语句就会 扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

大多数情况下默认采用 B+树 来构建索引。只是空间类型的索引使用R-树，并且memory表还支持 hash索引。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？基于cost开销，它不是基于 规则，也不是基于 语义。怎么样开销小就怎么来。另外，sql语句是否使用索引，跟数据库版本，数据量，数据选择度都有关系。

2.1 全值匹配

系统中经常出现的sql语句如下：

explain select sql_no_cache * from student where age = 30;
explain select sql_no_cache * from student where age = 30 and classId=4;
explain select sql_no_cache * from student where age = 30 and classId=4 and name='abcd';

建立索引前执行：

建立索引:

create index idx_age on student(age);
create index idx_age_classid on student(age, classId);
create index idx_age_classid_name on student(age, classId, name);

建立索引后执行:

2.2 最佳左前缀法则

在mysql建立联合索引时会遵循最佳做前缀匹配原则，及最左优先，在检索数据时从联合索引的最左边开始匹配。

举例1（使用上普通索引）：

举例2（没使用上索引）：

举例3（使用上联合索引）：索引idx_age_classid_name 还能否正常使用？

 
如果索引多列，要遵循最左前缀法则。指的是查询从索引的最左边列开始并且不跳过索引中的列。

结论: mysql 可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立是的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法使用。如果查询条件中没有使用这些字段中第一个字段时，多列索引不会被使用。

拓展：

索引文件具有 b-tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

2.3 主键插入顺序

对于一个使用InnoDB存储引擎的表来说，在我们没有显示的创建索引时，表中的数据实际上都是存储在 聚簇索引的叶子节点上。而记录又是存储在数据页中，数据页和记录又是按照记录 主键值从小到大的顺序进行排序，所以如果我们 插入 记录的 主键值时依次增大 的话，那我们每插满一个数据页就换到下一个数据页继续查，而如果我们插入的 主键值忽大忽小 的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在 1-100 之间：

 如果此时再插入一条主键值为9的记录，那它插入的位置就如下图：

 可这个数据页已经满了，在插进来就需要将当前的数据页进行分裂，把本页中的一些记录移动到新创建的页中。页面分裂的记录以为意味着什么？意味着： 性能损耗！ 所以如果我们想避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增，这样就不会发生性能损耗了。所以我们简易：让主键具有 auto_increment, 让存储引擎自己为表生成主键，而不是我们手动插入，比如：person_info表：

create table person_info(
    id int unsigned not null auto_increment,
    name varchar(100) not null,
    birthday date not null,
    phone_number char(11) not null,
    country varchar(100) not null,
    primary key (id),
    key idx_name_birthday_phone_number (name(10),birthday,phone_number)
);

我们自定义的主键列 id 拥有 auto_increment 属性，再插入记录时存储引擎会自动为我们填入自增的主键值，这样的主键值占用空间小，顺序写入，减少页分裂。

2.4 计算，函数，类型转换（自动或手动）导致索引失效

1. 这两条sql那种写法更好

# 此语句比下一条要好！可以用上索引
explain select sql_no_cache * from student where name like 'abc%';
# 此语句进行全表扫描
explain select sql_no_cache * from student where left(name,3) = 'abc';

type 为 “range”，表示使用到索引，查询时间仅为 0.01秒。

第二种索引优化失效

type 为 “ALL”，表示使用到索引，查询时间为3秒。

在举例：

~ student表的字段stuno上设置有索引

create index idx_sno on student(stuno);

索引失效

你能看到如果对索引进行了表达式计算，索引就失效了。这是因为我们需要把索引字段的值都取出来，然后依次进行表达式计算来进行条件判断，因此采用的就是 全表扫描 的方式，运行时间也会慢很多，最终运行时间为 2.538s。
 

索引生效

 运行时间为 0.039s。

在举例：

~ student表的字段name上设置索引

create index idx_name on student(name);

我们想要对name的前三位abc的内容进行条件筛选，这里我们来查看下执行计划：

~ 索引优化失效:

~ 索引生效：

2.5 类型转换导致索引失效

下列哪个sql语句可以用到索引。
类型不同导致索引失效(name 是 varchar   where是int)

 类型相同，索引生效

2.6 范围条件右边的列索引失效

1. 如果系统经常出现的sql如下：

alter table student drop index idx_name;
alter table student drop index idx_age;
alter table student drop index idx_age_classid;

explain select sql_no_cache * from student
where age = 30 and classId>20 and name = 'abc';

2. 那么索引 idx_age_classid_name 这个索引还能正常使用么?

查询优化器会将范围查询放到最后，例如：金额查询 ，日期查询往往都是范围查询。应将查询条件放置where语句最后。

2.7 不等于（!= 或者 <>）索引失效

~ 为name字段创建索引

create index idx_name on student(name);

~ 查看索引是否失效

2.8 is null 可以使用索引，is not null无法使用索引

结论：最好在设计数据表的时候就将 字段设置为 not null 约束，比如你可以将int类型的字段，默认值设置为0，将字符类型的默认值设置为空字符串('')。

拓展：同理，在查询中使用 not  like 也无法使用索引，导致全表扫描。

2.9 like 以通配符 % 开头索引失效

在使用like关键字进行查询的查询语句中，如果匹配字符串的第一个字符为'%',索引就不会起作用。只有'%'不在第一个位置，索引才会起作用。

~ 使用到索引

~ 失效索引

 
拓展： 页面搜索严禁左模糊或全模糊，如果需要请走搜索引擎来解决。

2.10 or 前后存在非索引的列，索引失效

在 where 子句中，如果在 or 前的条件列进行了索引，而在or后的条件列没有进行索引，那么索引会失效。也就是说， or前后的两个条件中的列都是索引是，查询中才使用索引。

因为 OR 的含义就是两个只要满足一个即可，因此 只有一个条件列进行了索引是没有意义的，只要有条件列没有索引，就会进行全表扫描，因此索引的条件列也会失效。

 
~ 给classid字段加上索引后

2.11 数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。

2.12 练习及一般性建议

假设 index(a,b,c)

where语句	索引是否被使用
where a = 3	Y
where a= 3 and b= 5	Y
where a= 3 and b=5 and c=4	Y
where b= 3 或者 where B=3 and c=4 或者 where c=4	N
where a=3 and c=5	使用到a, c无法使用，b中断了
where a=3 and b>4 and c=5	使用到了 a和b，c不能在范围之后，b断了
where a is null and b is not null	is null 支持索引，is not null不支持索引。所以a可以使用索引，b不可以使用
where a<>3	不能使用索引
where abs(a)=3	不能使用索引
where a=3 and b like "ll%" and c=4	Y，使用到了a,b,c
where a=3 and b like '%aa' and c=4	Y，只用到了a
where a=3 and b like '%aa%' and c=4	Y，只用到了a
where a = 3 and b like 'k%kk%' and c=4	Y，使用到了a,b,c

一般性建议：
~ 对于单列索引，尽量选择针对当前query过滤性更好的索引
~ 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置与靠前越好

~ 在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。

~ 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。

总之，写sql语句时，尽量避免造成索引失效的情况。

3. 关联查询优化

3.1 数据准备

# 分类
create table if not exists `type` (
	`id` int(10) unsigned not null auto_increment,
	`card` int(10) unsigned not null,
	primary key (`id`)
);
# 图书
create table if not exists `book` (
	`bookid` int(10) unsigned not null auto_increment,
	`card` int(10) unsigned not null,
	primary key (`bookid`)
);

#向分类表中添加20条记录
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));

#向图书表中添加20条记录
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

3.2 采用左外连接

 

目前这两个字段都没有索引，所以查询是按照全表扫描。 

添加索引后：

alter table book add index y(card); # 被驱动表可以避免全表扫描

explain select sql_no_cache * from `type` left join on type.card = book.card:

 
将两个字段都添加上索引:

alter table `type` add index x(card); # 被驱动表可以避免全表扫描

explain select sql_no_cache * from `type` left join on type.card = book.card:

这里的连接查询，索引生效的前提是 type.card 和 book.card 的类型必须一样，且两个字段都添加上了索引，这样索引才生效（类型不同，mysql底层会做隐式转换，只要做了隐式转换，就无法使用索引了）。

3.3 内连接

两个索引都存在的时候

 
当删除被驱动表的索引时：


结论： 对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的。（如果都有索引或都没有索引，则小表驱动大表）

3.4 join 语句原理

join方式连接多个表，本质就是各个表之间数据的循环匹配。mysql5.5版本之前，mysql只支持一种表间关联方式，就是嵌套循环 (Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会非常长。在mysql 5.5以后的版本中，mysql通过引入BNLJ算法来优化嵌套执行。

1. 驱动表和被驱动表

驱动表就是主表，被驱动表就是从表。
~ 对于内连接来说：
select * from A join B on .....

A 一定是驱动表吗？不一定，优化器会根据你查询的语句做优化，决定先查那张表。先车讯的那张表就是驱动表，繁殖就是被驱动表。通过explain关键字可以查看。
~ 对于外连接来说

select * from A left join B on...

或

select * from B Right join A on ...

通过大家会认为A就是驱动表，B就是被驱动表。但也未必。测试如下：

# 创建测试表
create table a(f1 int, f2 int, index(f1))engine=innodb;
create table b(f1 int, f2 int)engine=innodb;

# 插入数据
insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
insert into b values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

# 查看数据
select * from a;
select * from b;

# 测试1
explain select * from a left join b on a.f1=b.f1 where a.f2=b.f2;
# 测试2
explain select * from a left join b on a.f1=b.f1 and a.f2=b.f2;

第一种查询方式，将B表作为驱动表，A是被驱动表

 第二种查询方式，将A作为驱动表，B作为被驱动表。

2. Simple Nested-Loop Join (简单嵌套循环连接)

算法相当简单，从表A中取出一条数据1，遍历表B，将匹配到的数据放到result.....依次类推，驱动表A中的每一条记录与被驱动表B的记录进行判断：

 可以看到这种方式效率是非常低的，以上树表A数据100条，表B数据1000条计算的话，则 A*B=10万次。开销统计如下：

开销统计	SNLJ
外表扫描次数	1
内标扫描次数	A
读取记录数	A+B*A
join比较次数	B*A
回表读取记录次数	0

当然mysql肯定不会这么粗暴的去进行表的连接，所以就出现了后面的两种对Nested-Loop Join优化算法。
 

3. Index Nested-Loop Join (索引嵌套循环连接)

Index Nested-Loop Join 其优化的思路主要是为了 减少内层表数据的匹配次数，所以要求被驱动表上必须有 索引 才行。通过外层表匹配条件直接于内层表索引进行匹配，避免了和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。

 驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，顾mysql优化器都倾向于使用记录少数的表作为驱动表。

4. BLOCK  Nested-Loop Join (索引嵌套循环连接)

如果存在索引, 那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后在从驱动表中去一条与其匹配，匹配结束后清理内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了io的次数。为了较少被驱动表的io次数，就出现了Block Bested-Loop Join 的方式。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了 join buffer 缓冲区，将驱动表的join相关的部分数据列缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配，将简单嵌套循环中的多次比较合并称一次，将跌了被驱动表的访问频率。
注意：
这里缓存的不只是关联表的列，select后面的列也会缓存起来。
在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。



  参数设置：
~ block_nested_loop
通过 show varriables like '%optimizer_switch%' 查看 block_nested_loop状态。默认是开启的。
~ join_buffer_size
驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下 join_buffer_size=256k。


join_buffer_size 的最大值在32位系统可以申请4G，而在64为系统下可以申请大于4G的空间（windows除外），其大值会被截断为4GB并发出警告。

5. join 小结
1. 整体效率比较：INLJ > BNLJ > SNLJ
2. 永远用小结果驱动大结果集（其本质就是减少外层循环的数据量）。
3. 为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）
4. 增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫描表次数就越少）
5. 减少驱动表不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）

6. hash join
从mysql的8.0版本开始将废弃 BNLJ，因为从mysql8.0版本开始假如了 hash join默认都会使用hash join。
~ nested loop:
对于被连接的数据子集较小的情况下，nested loop 是个较好的选择。
~ hash join是做 大数据集连接 时常用的方式，优化器使用两个表中较小的表利用 join key 在内存中建立 散列表，然后扫描较大的表并探测散列表，找出于hash表匹配的行。
1. 这种方式适用于较小的表完全可以防御内存中的情况，这样总成本就是两个表的成本之和
2. 在表很大的情况下并不能完全放入内存，这是优化器会将它分割成 若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高io的性能。
3. 它能够很好的工作于没有索引的大表和并查询的环境中，并提供最好的性能。大多数人都说她是join的重型升降机。hash join 只能应用于等职连接（如 where A.clo1 = B.clo2） ，这是由hash的特点决定的。
 

类别	nested loop	hash loop
使用条件	任何条件	等值连接
相关资源	cpu ，磁盘io	内存，临时空间
特点	当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果。	当缺乏索引或者索引条件模糊时，hash join 比较nested loop 有效。在数据仓库环境下，如果表的记录数多，效率高。
缺点	当索引丢失或者查询条件限制不够时，效率很低；当表的记录多时，效率低。	为建立hash表，需要大量内存。第一次的结果返回较慢。

4. 子查询优化

---

        mysql 从4.1开始支持子查询，使用子查询可以进行select语句的嵌套查询，即一个select查询的结果作为另一个select语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的sql操作。

子查询时mysql的意向重要的功能，可以帮助我们通过一个sql语句实现比较复杂的查询。但是，子查询的执行效率不高。原因：
 

1. 执行子查询时，mysql需要为内层查询语句的查询结果 建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，在 撤销这些临时表。这样会消耗过多的cpu和io资源，产生大量的慢查询。

2.子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引，索引查询性能会受到一定的影响。
3. 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在mysql中，可以使用连接查询来代替子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

举例1：查询学生表中是班长的学生信息

~ 使用子查询

 ~ 改造多表查询

 
举例2：查询部位班长的学生信息

 
~ 左连接查询

 
效率提升还是挺明显的（尽量不要使用 not in 或者 not exists, 用 left join xxx on xx where xx is null 替代）

5. 排序优化

---

5.1 排序优化

问题: 在 where 条件字段上加索引，但是为什么在order by字段上还要加索引呢？

回答:

在mysql中，支持两种排序方式，分别是 fileSort 和 Index 排序。

~ index排序中，索引可以保证数据的有序性，不需要在进行排序，效率更高。

~ fileSort 排序则一般在 内存中 进行排序，占用 cpu 较多。如果待排结果较大，会产生临时文件io到磁盘进行排序的情况，效率较低。

优化建议：
1. sql中，可以在where子句和order by子句中使用索引，目的是在where子句中 避免全表扫描，在 order by 子句 避免使用 fileSort 排序。当然，某些情况下全表扫描，或者 fileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
2. 尽量使用index完成order by排序。如果where 和 order by 后面是相同的列就是用单列索引；如果不同就是用联合索引。

3. 无法使用index时，需要对 FileSort 方式进行调优。

5.2 测试

删除student表和class表中已创建的索引。
 

# 方式1：
DROP INDEX idx_monitor on class;
DROP INDEX idx_age on class;
DROP INDEX idx_name on class;
DROP INDEX idx_age_name_classid on class;
DROP INDEX idx_age_class_name on class;

以下查询是否用到索引，能否去掉 using filesort。
过程一：

 分页后

 过程二：order by 时不 limit，索引失效

# 创建索引
create index idx_age_classid_name on student(age,classid,name);
# 不限制，索引失效
explain select SQL_NO_CACHE * from student order by age,classid;

查询所有字段，但是进行了分页，也会触发索引（不进行分页则默认查询所有数据，然后在进行回表查数据，mysql判断这样的操作还不如直接在聚簇索引查数据然后进行排序来的快，所以没有使用上索引）

 加上查询的数据为联合索引的字段，不需要回表操作，则使用上索引

 
有额外的字段需要进行查询（不是联合索引的字段），则索引失效

 
过程三：下面那些索引会失效

# 构建联合索引
create index idx_age_classid_stuno on student(age, classid, stuno);
# 索引失效，最左前缀原则
explain select * from student order by classid limit 10;
# 索引失效
explain select * from student order by classid,name limit 10;
# 索引生效
explain select * from student order by age,classid,stuno limit 10;
# 索引生效
explain select * from student order by age,classid limit 10;
# 索引生效
explain select * from student order by age limit 10;

过程四：order by 时规则不一致，索引失效(顺序错，不索引；方向反，不索引)

# 失效 age 和 classid 排序方向相反
explain select * from student order by age desc, classid asc limit 10;
# 失效，最左前缀原则
explain select * from student order by classid desc, name desc limit 10;
# 失效 age 和 classid 排序方向相反
explain select * from student order by age asc, classid desc limit 10;
# 有效，方向相同
explain select * from student order by age desc, classid desc limit 10;

过程五：无过滤，不索引

# 有效
explain select * from student where age = 45 order by classid;
# 有效
explain select * from student where age = 45 order by classid,name;
# 失效，需要回表
explain select * from student where classid = 45 order by age;
# 有效，做了分页
explain select * from student where classid = 45 order by age limit 10;

小结：

index a_b_c(a,b,c);

order by # 能使用索引最前端
- order by a
- order by a, b
- order by a, b, c
- order by a desc, b desc, c desc

# 如果 where 使用索引的最左前缀定义为常量，则order by 能使用索引
- where a = const order by b, c
- where a = const and b = const order by c
- where a = const and b > const order by b, c

# 不能使用索引进行排序
- order by a asc, b desc, c desc # 排序不一致
- where g = const order by b, c # 丢失最左边的a
- where a = const order by c # 丢失b
- where a = const order by a, d # d 不是索引的一部分
- where a in (...) order by b,c # in 语句也是范围查询

5.3 案例实战

order by 子句，尽量使用index方式排序，避免使用filesort方式排序。

执行案例前先清除student上的索引，只保留主键

场景：查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序 (耗时 0.123s)

为了优化filesort，我们可以设置索引, 根据上述的查询条件可以设置 age 和name 的联合索引， stuno是范围查询所以不在我们考虑的范围内。 

方案一：（根据age 和 name 建立联合索引）(耗时 0.039s)

 
方案二：（根据age ，stuno 和 name 建立联合索引）(耗时 0.002s)

这个方案虽然有 filesort 排序，但是查询效率是三种最高效的。

原因：

所有排序都是在条件过滤之后才执行的。所以，如果条件过滤掉部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升的性能很有限。相对的 stuno < 101000 这个条件，如果没有用到索引的话，要对几万条数据进行扫描，这个是非常消耗性能的，所以索引放在这个字段上性价比是最高的选择。
结论：

1. 两个索引同时存在时，mysql会自动选择最优的方案。但是，随着数据量的变化，选择的索引也会随之变化的。

2. 当范围条件和 group by 或者 order by 字段出现二选一时，优先观察条件字段的过滤数量，如果过滤数量足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之亦然。

思考：这里我们使用如下索引，是否可行？

drop index idx_age_stuno_name on student;
create index idx_age_stuno on student(age,stuno);

5.4 filesort算法:双路排序和单路排序

排序的字段若如果不在索引上，则filesort会有两种算法：双路排序和单路排序

双路排序（慢）

~ mysql 4.1 之前使用的是双路排序，字面意思是就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出

~ 从磁盘读取排序字段，在buffer进行排序，再从磁盘取其他字段。
取一批数据，要对磁盘进行两次扫描，众所周知，io时很耗时的，所以在mysql 4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序（快）

从磁盘读取查询需要得所有列，按照order by列在buffer对它们进行排序，然后扫描排序后得列表进行输出，它的效率更快一些，必面了第二次读取数据。并且把随机io变成了顺序io，但是他会使用更多空间，因为他把每一行都保存在内存中了。

结论及引申出的问题

~ 由于单路是后出得，总体而言好过双路

~ 但是用单路有问题

1. 在sort_buffer中，单路比多路药多占用很多空间，因为单路是吧所有字段都去除，所以有可能取出得数据的总体大小超出了 sort_buffer 得容量，导致每次只能取sort_buffer容量大小得数据，进行排序（创建tmp文件，多路合并），排完序再去sort_buffer容量大小，再排。。。从而多次io。

2. 单路本来想省一次io操作，反而导致了大量的io操作，得不偿失。

优化策略

1. 尝试提高 sort_buffer_size

~ 不管使用那种算法，提高这个参数都会提高效率，要根据系统得能力去提高，因为这个参数是针对每个进程得1M-8M之间调整。mysq5.7，innodb存储引擎默认值是1048576字节，1MB.

show variables like '%sort_buffer_size%';

2. 尝试提高max_length_for_sort_data

~ 提高这个参数，会增加用改进算法得概率。

show variables like '%max_length_for_sort_data%';

3.  order by 时 select * 是一个大忌。最好只query需要的字段。原因：

~ 当query得字段大小综合小于max_length_for_sort_data，而排序字段不是text|blob类型是，会改用单路排序算法，否则会用多路排序。
~ 两种算法得数据都有可能超出sotr_buffer_size得容量，超出之后，会创建tmp文件进行合并排序，导致多次io，但是用单路排序算法得风险会更大一些，所以要提高 sort_buffer_size了。

6. group by 优化

~ group by 使用索引得原则几乎跟order by一直，group by 即使没有过滤条件用到索引，也可以直接使用索引。

~ group by先排序在分组，遵照索引间的最佳做前缀法则

~ 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数得设置

~ where效率高于having，能写在where限定得条件就不要卸载having中了

~ 减少使用order by，和业务沟通能不排序就不排序，获奖排序放到程序段去做。order by，group by， distinct 这些语句较为耗费cpu，数据库的cpu资源是及其宝贵的。

~ 包含了order by，group by ， distinct 这些查询语句，where 条件过滤出来的结果集请保持在1000以内，否则sql会很慢。

7. 优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好的提高性能。一个常见又非常头疼的问题就是limit 2000000,10 此时需要mysql排序前2000010记录，静静返回200000-2000010的记录，其他记录丢弃，查询排序的代价非常大。
 

explain select * from student limit 2000000,10;

优化思路一
在索引上完成排序分页操作，最后根据逐渐管来奶回原表查询所需要的其他列内容。

explain select * from student t ,(select id from student order by id limit 2000000,10) a where t.id=a.id;

优化思路二

该方案适用于主键索引自增的表，可以把limit查询转换成某个位置的查询。

explain select * from student where id> 2000000 limit 10;

8. 优先覆盖索引

8.1 什么是覆盖索引？

理解方式一：索引是高校找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此他不必读取整个行。毕竟索引叶子节点存储了他们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

理解方式二：非聚簇符合索引的一种形式，它包括在查询里的select，join和where子句用到的所有列（即建索引的字段正好是覆盖查询条件中所设计的字段）。

简单说就是，索引列+主键 包含 select 到 from 之间查询的列。

举例一：

 前边说过，  <> （一般情况下）条件是会进行全表扫描的, 不会走索引。

特殊情况 select 查询的字段刚好是某个联合索引的字段，则优化器会选择联合索引进行查询（省去了回表的操作，查询的字段联合索引都覆盖了）。

举例二：

模糊匹配查询百分号在最左边也不走索引，进行全表扫描。

同上二级索引覆盖了查询的字段，省去了回表操作，优化器决定使用二级索引。

8.2 覆盖索引的利弊

好处：

1. 避免innodb表进行索引的二次查询（回表）

innodb是以聚集索引的顺序来存储的，对于innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果使用二级索引查询数据，在查找到相应的键值后，还需要通过主键进行二次查询才能获取我们真实所需要的数据。

再覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询减少了io操作，提升了查询效率。

2. 可以随机io变成顺序io加快查询效率
由于覆盖索引是按照键值的顺序存储的，对于io密集型的范围查找来说，对比随机从磁盘读取每一行的数据io要少的多，因此利用覆盖索引在访问同时也可以把磁盘的 随机读取的io 转变成索引查找的 顺序io。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化。

如何理解随机io变化成顺序io？

当使用二级索引查询到数据后，需要进行回表操作，这个时候就是随机io，因为你回表的过程中，数据不一定是连续的，也就是说数据被分配在不同的数据页，甚至更糟糕分配在不同的数据区，这个时候查询就很耗时（随机io 会有寻道和半圈旋转的时间），如果说你只是在二级索引查询（索引本就是排好序的，也就是顺序io），那就会少上述的操作，所有数据都能在二级索引拿到，就不需要回表了。

由于覆盖索引可以减少树的搜索次数，显著提高查询性能，所以使用覆盖索引是一个常用的性能优化数段。

弊端：

索引字段维护 总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作。

10. 索引下推

---

10.1 使用前后对比

index condition pushdown 是mysql 5.6 新特性，是一种在存储引擎层使用索引过滤数据的优化方式。

~ 如果没有icp，存储引擎会遍历索引以定位基表中的行，并将它们返回给mysql服务器，由mysql服务器评估where后面的条件是否保留行。

~ 启用ipc后，如果部分where条件可以仅使用索引中的列进行筛选，则mysql服务器会把这部分where条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛数据，并且只有满足这一条件时才从表中读取行。

~ 好处：ipc可以减少存储引擎必须访问基表的次数和mysql服务器必须访问存储引擎的次数。

~ 但是，icp的加速效果取决于在存储引擎内通过ipc筛选掉数据的比例。

10.2 ipc的开启/关闭

~ 默认情况下启用索引条件下推。可以通过设置系统变量optimizer_switch控制：

index_condition_pushdown

# 打开索引下推
set optimizer_switch = 'index_condition_pushdown=off';

# 关闭索引下推
set optimizer_switch = 'index_condition_pushdown=on';

当使用索引条件下推时，explain 语句输出的结果时 extra 列内容为 using index condition。

10.3 icp使用案例

建表

# 建表
create table people(
    id int not null auto_increment,
    zipcode varchar(20) collate utf8_bin default null,
    firstname varchar(20) collate utf8_bin default null,
    lastname varchar(20) collate utf8_bin default null,
    address varchar(50) collate utf8_bin default null,
    primary key (id),
    key zip_list_first (zipcode,lastname,firstname) 
) engine = innodb auto_increment=5 default charset=utf8mb3 collate=utf8_bin;

# 插入数据
insert into people values
(1, '000001', '三', '张', '北京市'),
(2, '000002', '四', '李', '南京市'),
(3, '000003', '五', '王', '伤害市'),
(4, '000001', '六', '赵', '天津市');

为该表定义联合索引zip_last_first(zipcode,last_name,firstname) 。如果我们知道了一个人的邮编，但是不确定这个人的姓氏，我们可以如下检索:

select * from people
where zipcode = '000001' 
and lastname like '%张%' 
and address like '%北京市%';

执行查看sql的查询计划，extra中显示了using index condition，这表示使用了索引下推。另外，using where 表示条件中包含需要过滤的非索引列的数据，即address like '%北京市%' 这个条件并不是索引列，需要在服务端过滤掉。

 如果不想出现 using where ，把 address like '%北京市%' 去掉即可

 这个表存在两个索引，分别是：

 二级索引 zip_last_first 

下面我们关闭icp查看执行计划

set optimizer_switch = 'index_condition_pushdown=off';

10.4 开启和关闭icp的性能对比

创建存储过程，主要目的是插入很多000001的数据，这样查询的时候为了在存储引擎层做过滤，减少io，也为了减少缓冲池的作用。

使用icp查询优化效率会好很多，使用和不使用的区别 分别为 0.17s 和 1.6s。

10.5 icp的使用条件

1. 如果表访问的类型为 range，ref，eq_ref 和 ref_or_null可以使用icp

2. icp可以用于innodb和myisam表，包括分区表 innodb 和myisam表

3. 对于 innodb 表，icp仅用于 二级索引。icp的目标是减少全行读取次数，从而减少io操作

4. 当sql使用覆盖索引时，不持支icp。因此为这种情况下使用icp不会减少io

5. 相关子查询的条件不能使用icp

12. 其他查询优化策略

12.1 exists 和 in 的区分

问题：

不太理解那种情况下应该使用exists，那种情况下该用in。选择的标准是看能否使用表的索引吗？

回答：

索引是个前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为小表驱动大表。在这种方式下效率时最高的。

比如下面这样的：

select * from A where cc in (select cc from B);

select * from A where exists (select cc from B where B.cc=A.cc); 

当A小于B时，用exists。因为exists的实现，相当于外表循环，实现逻辑类似于：

for i in A

        for j in B

                if j.cc == i.cc then

当B小于A时用IN，实现逻辑：

for i in B

        for j in A

                if j.cc == i.cc then 

那个表小就用那个表来驱动，A表小就用exists，B表小就用IN。

12.2 count(*)与count(具体字段)效率

问：在mysql种统计数据表的行数，可以使用三种方式：select count(*), select count(1), select count(具体字段), 使用这三者之间的查询效率是怎么样的？

答：

前提：如果你要统计的是某个字段的非空数据行数，则另当别论，毕竟比较执行效率的前提是结果一样才可以。

环节1：count(*)和count(1) 都是对所有结果进行count，count(*) 和count(1)本质上没有区别。如果有where子句，则是对所有符合筛选条件的数据进行统计；如果没有where子句，则是对数据表的数据进行统计。

环节2：如果时myisam存储引擎，统计数据表的行数只需要O(1)的复杂度，这是因为每张表myisam的数据表都是有一个meta信息存储了row_count值，而一致性则由表级锁来保证。

如果是innodb存储引擎，因为innodb支持事务，采用行级锁和mvcc机制，所以无法想myisam一样，维护一个row_count变量，因此需要采用 全表扫描，是O(n)的复杂度，进行循环+计数的方式来完成统计。

环节3：在innodb引擎种，如果采用count(具体字段)来统计数据行数，要尽量采用二级索引。因为主键采用的时聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于count(*)和count(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。

如果有多个二级索引，会使用key_len 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

12.3 关于select(*)

在表查询种，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用select <字段列表> 查询。原因：

1. mysql在解析的过程中，会通过 查询数据字典 将 * 按序转化成所有的列名，这回大大的耗费资源和时间。

2. 无法使用覆盖索引。

12.4 limit 1 对优化的影响

针对的是会扫描全表的sql语句，如果你可以确定结果只有一条，那么加上limit 1 的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。

如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上limit 1 了。

12.5 多使用commit

只要有可能，在程序中尽量多使用commit，这样程序的性能得到提高，需求也会因为commit所释放的资源而减少。

commit 所释放的资源：

~ 回滚段上用于回复数据的信息

~ 被程序语句获得的锁

~ redo / undo log buffer 中的空间

~ 管理上述3种资源中的内部花费

13. 淘宝数据库，主键如何设计的？

聊一个实际问题：淘宝数据库，主键是如何设计的？

某些错的离谱的答案还在网上年复一年的流传着，甚至还成为了所谓的mysql军规。其中，一个最明显的错误就是关于mysql主键设计。

大部分人的回答如此自信：用8字节的bigint作主键，而不要用int，错！

这样的回答，只站在了数据库这一层面，而没有从业务的角度思考主键。主键就是一个自增id吗？用自增做主键，架构设计上可能 连及格都达不到。

13.1 自增id的问题

自增id做主键，简单易懂，几乎所有的数据库都支持自增类型，只是实现上各自有所不同而已。自增id除了简单，其他都是缺点，总体来看存在以下几方面的问题。

1. 可靠性不高

存在自增id回溯的问题，这个问题知道最新版本的mysql8.0才修复

2. 安全性不高

对外暴露的接口可以非常容易猜测对应的信息。比如：user/1 这样的接口，可以非常容易猜测用的的id值为多少，总用户数量有多少，也可以非常容易的通过接口进行数据的爬取。

3. 性能差

自增id的性能较差，需要在数据库服务器段生成。

4. 交互多

业务还需要额外执行一次类型 last_insert_id() 的函数才能知道刚才插入的自增值，这需要多义词的网络交互。在海量并发的系统中，多一条sql，就多一次性能上的开销。

5. 局部唯一性

最重要的一点，自增id是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器中都是唯一。对于目前的分布式系统来说，这简直就是噩梦。

 13.2 业务字段做主键

为了能够唯一地标识一个会员的信息，需要为 会员信息表 设置一个主键。那么，怎么为这个表设置主键，才能达到我们理想的目标呢？这里我们考虑业务字段做主键。

表数据如下：

cardno （卡号）	membername （名称）	memberphone （电话）	memberpid （身份证）	address （地址）	sex （性别）	birthday （生日）
10000001	张三	13812345678	110123200001017890	北京	男	2000-01-01
10000002	李四	13812345678	110123200001017820	上海	女	1900-01-01

在这个表里，那个字段比较合适呢？

~ 选择卡号(cardno)

会员卡号看起来比较合适，因为会员卡号不能为空，而且具有唯一性，可以用来表示一条会员记录。

create table demo.membermaster(
    cardno char(8) primary key, # 卡号作为主键
    memebername TEXT,
    memberphone TEXT,
    memberpid TEXT,
    address TEXT,
    sex TEXT,
    birthday DATETIME
);

不同的会员卡号对应不同的会员，字段 “ cardno” 唯一的标识某一个会员。如果都是这样，会员卡号与会员一一对应，系统是可以正常与性的。

但实际情况是，会员卡号可能存在重复使用 的情况。比如，张三因为工作变动搬离了原来的地址，不在到商家的门店消费了（退还了会员卡），于是张三就不再是这个商家门店的会员了。但是，商家不想让这个会员卡空着，就把卡号是 “10000001”的会员卡发给了王五。

从系统设计的角度看，这个变化只是修改了会员信息表中的卡号是“10000001”这个会员信息，并不会影响到数据一致性。也就是说，修改会员卡号是“10000001”的会员信息，系统的各个模块，都会获取到修改后的会员信息，不会出现“有的模块获取到修改之前的会员信息，有的模块获取到修改之后的会员信息，而导致系统内部数据不一致”的情况。因此从 系统层面 上来看没问题。

但是从使用 系统的业务层面 来看，就有很大问题了，会对商家造成影响。

比如，我们有一个销售流水表，记录了所有的销售流水明细。2020年12月01日，张三在门店购买了一本书，消费89元。那么，系统种就有了张三买书的流水记录，如下所示：

transactionno (流水单号)	itemnumber （商品编号）	quantity （销售数量）	price （价格）	salesvalue （销售金额）	cardno （会员卡号）	transdate （交易时间）
1	1	1	89	89	10000001	2020-12-01

select b.mambername, c.goodsname, a.quantity, a.salesvalue, a.transdate
from demo.trans as a
join demo.membermaster as b
join demo.goodsmaster as c
on (a.cardno = b.cardno and a.itemnumber=c.itemnumber);

如果会员卡 “10000001” 又发给了王五，我们会更改会员信息表。导致查询时：

select b.membername, c.goodsname, a.quantity, a.salesvalue, a.transdate
from demo.trans as a
join demo.membermaster as b
join demo.goodsmaser as c
on (a.cardno = b.cardno and a.itemnumber=c.itemnumber);

这次得到的结果是：王五在2020年12月01日，买了一本书，消费89元。显然是错误的！结论：千万不能把会员卡号当作主键。
 

~ 选择会员电话或身份证号

会员电话可以做主键吗？不行的。在实际操作中，手机号也存在被运营商收回，重新发给别人用的情况。

拿身份证行不行呢？好像可以，因为身份证绝不会重复，身份证号与一个人存在一一对应的关系。可问题是，身份证号属于 个人隐私，顾客不一定愿意给你。要是强制要求会员必须等级身份证号，会把很多客人赶跑。其实，客户电话也有这个问题，这也是我们在设计会员信息表的时候，允许身份证和电话都为空原因。

所以，建议尽量不要用跟业务相关字段做逐渐。毕竟，作为项目设计的技术人员，我们谁也无法预测在项目的整个周期，那个业务字段会因为项目的业务需求而有重复，或者重用之类的情况出现。
经验：

刚开始使用mysql时，很多人都很容易犯得错误是喜欢用业务字段作为主键，想当然的认为了解业务需求，但实际情况往往出乎意料，而更改逐渐设置的成本非常高。

13.3 淘宝的主键设计

在淘宝的电商业务中，订单服务是一个核心的业务。请问，订单表的主键 淘宝是如何设计的呢？是自增id吗？

打开淘宝，看一下订单信息：

 从上图可以发现，订单号不是自增id！我们详情看下上述4个订单号：

1550672064762308113

1481195847180308113

1431156171142308113

1431146631521308113

订单号时19为长度，且订单的最后5位都是一样的，且订单号的前面14位部分都是单调递增的。大胆猜测，淘宝的订单id设计应该是：

订单id=时间+去重字段+用户id后6位尾号

这样设计能做到全局唯一，且对分布式系统查询及其友好。

13.4 推荐的主键设计

非核心业务：对应表的主键自增id，如告警，日志，监控等信息。

核心业务：主键设计至少应该是全局唯一且是单调递增。全局唯一保证在各系统之间都是唯一的，单调递增是希望插入时不影响数据库性能。

这里推荐最简单的主键设计：UUID。

UUID的特点：

全局唯一，占用36个字节，数据无需，插入性能差。

认识UUID：

~ 为什么UUID是全局唯一的？

~ 为什么UUID占用36个字节？

~ 为什么UUID是无序的？

mysql数据库的UUID组成如下所示：

UUID = 时间 + UUID版本（16字节）- 时钟序列（4字节）- MAC地址（12字节）

我们以UUID值e0ea12d4-6473-11eb-943c-00155dbaa39d举例:

 

为什么UUID是全局唯一的？

在UUID中时间部分占用60位，存储的类似TIMESTAMP的时间戳，但表示的是从1582-10-15 00:00:00 00.00 到现在的100ns的计数。可以看到UUID存储的时间精确度比TIMESTAMPE更高，时间维度发生重复的概率降低到1/100ns。

时钟序列是为了避免时钟被回拨导致产生时间重复的可能性。mac地址用户全局唯一。
为什么UUID占用36个字节？

UUID根据字符串进行存储，设计时还带有无用 - 字符串，因此总共需要36个字节。

为什么UUID时随机无序的呢？

因为UUID的设计中，将时间低位放在最前面，而这部分的数据是一只在变化的，并且是无序的。

改造UUID

若将时间高低位互换，则时间就是单调递增的了，也就变得单调递增了。mysql8.0可以更换时间低位和时间高位的存储方式，这样UUID就是有序的UUID了。

mysql8.0还解决了UUID存在的空间占用问题，出去了UUID字符串种无意义的-字符串，并且将字符串用二进制类型保存，这样存储空间降低为了16字节。

可以通过mysql8.0提供的uuid_to_bin函数实现上述共嗯那个，同样的，mysql也提供了bin_to_uuid函数及进行转化：

set @uuid=UUID();
select @uuid, uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

通过函数uuid_to_bin(@uuid,true)将UUID转化为了有序UUID。全局唯一+单调递增，这不就是我们想要的主键！
 

4.有序UUID性能测试

16字节的有序UUID，相比之前8字节的自增id，性能和存储空间对比究竟如何呢？

我们来做个测试，插入1亿条数据，每条数据占用500字节，含有3个二级索引，最终的结果如下所示：

 从上图可以看到插入1亿条数据的有序UUID是最快的，而且在实际业务中有序UUID在业务端就可以生成。还可以进一步的减少sql的交互次数。

另外，虽然有序UUID相比自增id多了8个字节，但实际只增大了3G的存储空间，还可以接受。

在当今的互联网环境种，非常不推荐自增id作为主键的数据库设计。更推荐类似有序UUID的全局唯一的实现。

另外在真实的业务系统种，主键还可以加入业务和系统属性，如用户的尾号，机房的信息等。这样的主键设计就更为考研架构师的水平了。
 

如果不是mysql8.0怎么办？

手动复制字段做主键！

比如，设计各个分店的会员表的主键，因为如果每台机器各自产生的数据需要合并，就可能会出现主键重复的问题。

可以在总部mysql数据库种，有一个管理信息表，在这个表中添加一个字段，专门用来记录当前会员编号的最大值。