1.数据准备
学员表 插 50万 条， 班级表 插 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`)#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
步骤2：设置参数
- 命令开启：允许创建函数设置：
:::tips
set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。
:::
步骤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 ; #假如要删除 #drop function rand_string;
步骤4：创建存储过程 ```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 ; #假如要删除 #drop function rand_num;创建往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 ;假如要删除
drop PROCEDURE insert_stu;
创建往class表中插入数据的存储过程
执行存储过程，往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 ;
假如要删除
drop PROCEDURE insert_class;
**步骤5：调用存储过程**<br />class
:::tips
#执行存储过程，往class表添加1万条数据 <br />CALL insert_class(10000);
:::
stu
:::tips
#执行存储过程，往stu表添加50万条数据 <br />CALL insert_stu(100000,500000);
:::
**步骤6：删除某表上的索引**
DELIMITER //
CREATE PROCEDURE proc_drop_index(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT ‘’;
DECLARE _cur CURSOR FOR SELECT index_name FROM
information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND
seq_in_index=1 AND index_name <>’PRIMARY’ ;
每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
若没有数据返回,程序继续,并将变量done设为2
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index " , _index , " on " , tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur; END // DELIMITER ;
执行存储过程
:::tips
CALL proc_drop_index("dbname","tablename");
:::
<a name="vq1Tb"></a>
# 2.索引失效案例
MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。
- 使用索引可以 快速地定位 表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。
- 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。
大多数情况下都（默认）采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持 hash索引。<br />其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器?基于cost开销(CostBaseOptimizer)，它不是基于规则(Rule-BasedOptimizer )，也不是基于语义。怎么样开销小就怎么来。另外，**SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。
<a name="Qd8DL"></a>
## 2.1 全值匹配我最爱
系统中经常出现的sql语句如下:
:::tips
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;<br />EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;<br />EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
:::
建立索引前执行：（关注执行时间）
:::tips
mysql >SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';<br />Empty set, 1 waring ( 0.28 sec)
:::
**建立索引**
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);
建立索引后执行
:::tips
mysql >SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';<br />Empty set, 1 waring ( 0.01 sec)
:::
可以看到，创建索引前的查询时间是0.28秒，创建索引后的查询时间是0.01秒，索引帮助我们极大的提高了查询效率。
<a name="itiNE"></a>
## 2.2 最佳左前缀法则
在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = ‘abcd’ ;
eg1: 根据在2.1中所建立的索引，存在age的普通索引，则此处使用的ide_age的索引进行检索<br />
---
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name = ‘abcd’;
eg2:此时classId和name的索引并没有创建。并且根据最左前缀原则，第二三个联合索引由于age字段并未在此处存在，所以这两个索引无法使用。<br />
---
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid=4 AND student.age=30 AND student.name = ‘abcd’;
eg3: 此时的三个字段均存在于第三个联合索引中，优化器会为这些字段按照索引中字段的顺序排列执行，所以此处检索使用的是这三个字段的联合索引<br />
---
DROP INDEX idx_age ON student; DROP INDEX idx_age_classid ON student;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = ‘abcd’;
eg4:这里我们先将前两个索引删除，排除其他因素。执行sql后发现**key**中 此处检索是按照（age,classId,name） 三个字段的联合索引进行的，但是**key_len**的值却只有**5。**<br />原因：由于根据最左前缀法则可以判断出此sql中name前并没有classId字段，所以name也就无法运用，只能按照第一个age字段索引进行检索，其他两个字段索引失效，并且因为age是int类型，占4个字节且可为null，又占1个字节，所以此处**key_len**的长度是5。<br />
> **拓展：Alibaba《Java开发手册》**
> 索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
<a name="bempg"></a>
## 2.3 主键插入顺序
对于一个使用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1~180之间:<br /><br />如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：<br /><br />可这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有 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 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。
<a name="LkQGm"></a>
## 2.4 计算、函数、类型转换(自动或手动)导致索引失效
**假设name字段上设置有索引**<br />**索引优化生效**
:::tips
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
:::
- **索引优化失效**
:::tips
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
:::
**student表的字段stuno上设置有索引**
:::tips
CREATE INDEX idx_sno ON student(stuno);
:::
- **索引优化失效**
:::tips
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
:::
- **索引优化生效**
:::tips
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;
:::
<a name="LfRtB"></a>
## 2.5 类型转换导致索引失效
下列哪个sql语句可以用到索引。（假设name字段上设置有索引）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
- 由于name是VARCHAR类型，但此处 使用 name=123,会存在类型转换导致索引失效。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=’123’;
<a name="F1Nzl"></a>
## 2.6 范围条件右边的列索引失效
**1）此时假设只存在以这三个顺序字段构建的联合索引(age,classId,NAME)**，将范围条件放在中间
:::tips
name varchar(20)<br />EXPLAIN SELECT SQL_NO_CACHE * FROM student <br />WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
:::
- 则classId右侧的索引name失效，由key_len长度为10可以解析:
10=age(4字节)+1(null) + classId(4字节)+1(null)=5+5<br /><br />此时将范围条件放在最右边
:::tips
EXPLAIN SELECT SQL_NO_CACHE * FROM student <br />WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;
:::
- 此时右侧的索引name依旧失效，因为索引联合中顺序依旧是(age,classId,NAME)
<br />2）**此时假设只存在以这三个顺序字段构建的联合索引(age,NAME，classId)**，将范围条件放在中间
:::tips
EXPLAIN SELECT SQL_NO_CACHE * FROM student <br />WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
EXPLAIN SELECT SQL_NO_CACHE * FROM student <br />WHERE student.age=30 AND student.name = 'abc' AND student.classId>20 ;
:::
- 此时无论classId在哪个位置索引都生效，key_len长度是：age(4字节)+1(null)+ classId(4字节)+1(null) + name(20*3)+1(null)+2(变长字符)=5+5+60=73；
> **应用开发中范围查询，例如:金额查询，日期查询往往都是范围查询，创建的联合索引中，务必把范围涉及到的字段写在最后。**
<a name="vGSEB"></a>
## 2.7 不等于(!= 或 <>)索引失效
- **索引失效**
CREATE INDEX idx_name ON student(NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> ‘abc’ ;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != ‘abc’ ;
<a name="QBJ4i"></a>
## 2.8 is null可以使用索引，is not null无法使用索引
可以使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
不能使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
> **结论:最好在设计数据表的时候就将字段设置为NOT NULL 约束，比如你可以将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串()。**
> **拓展:同理，在查询中使用not like 也无法使用索引，导致全表扫描。**
<a name="vTLQE"></a>
## 2.9 like以通配符%开头索引失效
- 因为以%开头搜索，底层结构B+Tree无法一开始就确定如何搜索
可以使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE ‘ab%’;
不能使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE ‘%ab%’;
> **拓展:Alibaba 《Java开发手册》**
> 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
<a name="bYyxe"></a>
## 2.10 OR 前后存在非索引的列，索引失效
在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，**OR前后的两个条件中的列都是索引时，查询中才使用索引**。<br />因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。<br />假设当前存在age字段的普通索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
- 此时由于classId没有索引，所以必然会进行一次全文扫描，从而导致classId索引失效
解决办法：给 classId字段也加上索引就可以避免索引失效
- 此时从执行结果来看，会结合age和classId的索引进行搜索，type类型是：index_merge
<a name="EEvWD"></a>
## 2.11 数据库和表的字符集统一使用utf8mb4
统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。
<a name="GrSwG"></a>
## 2.12 练习及一般性建议
练习：假设: index(a,b,c)<br /><br />
<a name="X0UTV"></a>
# 3.关联查询优化
- **对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的**
- **对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。**
- **对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表(数据量小)作为驱动表。“小表驱动大表”**
<a name="eMqxm"></a>
## 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)
);
DROP TABLE TYPE;
图书
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)));
- **情况一:左外连接(右外连接同理)**
首先我们给card添加索引
:::tips
CREATE INDEX Y ON book(**card**);
:::
然后我们执行左外连接查询
:::tips
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
:::
<a name="rV8JS"></a>
## 3.1 JOIN语句原理
join方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会非常长。在MySQL5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。
<a name="h0don"></a>
### 1.Simple Nested-Loop Join(简单嵌套循环连接)
算法相当简单，从表A中取出一条数据1，遍历表B，将匹配到的数据放到result..以此类推，驱动表A中的每一条记录与被驱动表B的记录进行判断:<br /><br />可以看到这种方式效率是非常低的，以上述表A数据100条，表B数据1000条计算，则A*B= 10万次。开销统计如下:<br /><br />当然mysql肯定不会这么粗暴的去进行表的连接，所以就出现了后面的两种对Nested-Loop Join优化算法。
<a name="orFwM"></a>
### 2.Index Nested-Loop Join(索引嵌套循环连接)
Index Nested-Loop Join其优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。<br /><br />驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故mysql优化器都倾向于使用记录数少的表作为驱动表(外表)。<br /><br />如果被驱动表加索引，效率是非常高的，但如果索引不是主键索引，所以还得进行一次回表查询。相比，被驱动表的索引是主键索引，效率会更高。
<a name="BCfcR"></a>
### 3.Block Nested-Loop Join(块嵌套循环连接)
如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了lo的次数。为了减少被驱动表的Io次数，就出现了Block Nested-Loop Join的方式。<br />不再是逐条获取驱动表的数据，而是一块一块的获取，引入了 ** join buffer缓冲区**，将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和joinbuffer中的所有驱动表记录进行匹配(内存中操作)，将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。
> **注意:**
> **这里缓存的不只是关联表的列，select后面的列也会缓存起来。**
> **在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。**
<br /><br />参数设置:
- block_nested_loop
通过show variables like ‘%optimizer_switch%’查看block_nested_loop状态。默认是开启的。
- join_buffer_size
驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况join_buffer_size=256k
<a name="xal3C"></a>
### 4.Join小结
1、整体效率比较:**INLJ > BNLJ > SNLJ**<br />2、永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量) (小的度量单位指的是表行数*每行大小)
:::tips
select t1.b,t2.* from t1 straight.join t2 on (t1.b=t2.b) where t2.id<=100;#推荐<br />select t1.b, t2.* from t2 straight.join t1 on (t1.b=t2.b) where t2.id<=100;#不推荐
:::
3、为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数)<br />4、增大join buffer size的大小(一次缓存的数据越多，那么内层包的扫表次数就越少)<br />5、减少驱动表不必要的字段查询（(字段越少，join buffer所缓存的数据就越多)
<a name="QjQxo"></a>
### 5.Hash Join
从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join
- Nested Loop:
对于被连接的数据子集较小的情况，Nested Loop是个较好的选择。
- Hash Join
是做大数据集连接时的常用方式，优化器使用两个表中较小(相对较小）的表利用Join Key在内存 中建立散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行。
- 这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。
- 在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/o的性能。
- 能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join只能应用于等值连接(如WHERE A.COL1=B.COL2)，这是由Hash的特点决定的。
<a name="WYzAr"></a>
### 6.小结
保证被驱动表的JOIN字段已经创建了索引
- 需要JOIN 的字段，数据类型保持绝对一致。
- LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。
- INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
- 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
- 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。
- 衍生表建不了索引
<a name="xbQ4O"></a>
# 4.子查询优化
MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作 。**子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高**。原因：<br />① 执行子查询时，MySQL需要为内层查询语句的查询结果 建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再 撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。<br />② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会受到一定的影响。<br />③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。<br />**在MySQL中，可以使用连接（JOIN）查询来替代子查询**。连接查询 不需要建立临时表 ，其 速度比子查询要快 ，如果查询中使用索引的话，性能就会更好。
<a name="ZcyUr"></a>
# 5.排序优化
**问题：**在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？<br />**回答:**<br />在MySQL中，支持两种排序方式，分别是FileSort和Index排序。
- Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
- FileSort排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/o到磁盘进行排序的情况，效率较低。
**优化建议：**
1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
1. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
1. 无法使用 Index 时，需要对 FileSort 方式进行调优。
:::tips
INDEX a_b_c(a,b,c)<br />order by 能使用索引最左前缀<br />- ORDER BY a<br />- ORDER BY a,b<br />- ORDER BY a,b,c<br />- ORDER BY a DESC,b DESC,c DESC<br />如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引<br />- WHERE a = const ORDER BY b,c<br />- WHERE a = const AND b = const ORDER BY c<br />- WHERE a = const ORDER BY b,c<br />- WHERE a = const AND b > const ORDER BY b,c<br />不能使用索引进行排序<br />- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */<br />- WHERE g = const ORDER BY b,c /*丢失a索引*/<br />- WHERE a = const ORDER BY c /*丢失b索引*/<br />- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/<br />- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/
:::
<a name="rtbtr"></a>
# 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会很慢。
<a name="GiITo"></a>
# 7.优先考虑覆盖索引
<a name="HMa50"></a>
## 7.1什么是覆盖索引
**理解方式一**︰索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。**一个索引包含了满足查询结果的数据就叫做覆盖索引**。<br />**理解方式二**︰非聚簇复合索引的一种形式，它包括在查询里的SEIECT、JOIN和WHERE子句用到的所有列(即建索引的字段正好是覆盖查询条件中所涉及的字段)。<br />简单说就是，索引列+主键包含SELECT到FROM之间查询的列。<br />**好处:**<br />**1.避免Innodb表进行索引的二次查询(回表)**<br />Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。在覆盖索引中，二级索引的键值中可以获取所要的数据，**避免了对主键的二次查询，减少了IO操作**，提升了查询效率。<br />**2.可以把随机lO变成顺序lO加快查询效率**<br />由于覆盖索引是按键值的顺序存储的，对于i0密集型的范围查找来说，对比随机从磁盘读取每一行的数据io要少的多，因此利用覆盖索引在访问时也可以把磁盘的**随机读取的iO**转变成索引查找的顺序iO。<br />**由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。**<br />**弊端:**<br />索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作。
<a name="DkEqp"></a>
# 8.索引下推
<a name="yfF2M"></a>
## 8.1 使用前后对比
Index Condition Pushdown(ICP)是MysQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的优化方式。
- 如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给MysQL服务器，由MySQL服务器评估**WHERE**后面的条件是否保留行。
- 启用ICP后，如果部分**WHERE **条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分**WHERE**条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。
- 好处:ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。
- 但是，ICP的**加速效果**取决于在存储引擎内通过ICP筛选掉的数据的比例。
<a name="yL1Kp"></a>
## 8.2 ICP的开启/关闭
- 默认情况下启用索引条件下推。可以通过设置系统变量optimizer_switch控制:index_condition_pushdown
:::info
#关闭索引下推<br />SET optimizer_switch = 'index_condition_pushdown=off' ;<br />#打开索引下推<br />SET optimizer_switch = 'index_condition_pushdown=on ' ;
:::
- 当使用索引条件下推时，EXPLAIN语句输出结果中**Extra**列内容显示为 **Using index condition**
<a name="sjHka"></a>
## 8.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_last_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，lastname，firstname)。如果我们知道了一个人的邮编，但是不确定这个人的姓氏，我们可以进行如下检索:
:::info
EXPLAIN SELECT * FROM people
WHERE zipcode=’000001’
AND lastname LIKE ‘%张%’
AND address LIKE ‘%北京市%’;
:::
执行查看SQL的查询计划，Extra中显示了Using index condition，这表示使用了索引下推。倘若该执行计划按照正常执行,应该是使用到zipcode这个索引，但是lastname因为是模糊查询，所以索引失效。但是又由于索引下推的存在，优化器会将通过对lastname进行判断，虽然此时模糊查询导致失效，但是优化器先将数据用lastname筛选过后，而后再去查找对应的数据。另外，Using where表示条件中包含需要过滤的非索引列的数据，即address LIKEY%北京市%’这个条件并不是索引列，需要在服务端过滤掉。
8.4 ICP的使用条件
ICP的使用条件：
① 只能用于二级索引(secondary index)
②explain显示的执行计划中type值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。
③ 并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。
④ ICP可以用于MyISAM和InnnoDB存储引擎
⑤ MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持。
⑥ 当SQL使用覆盖索引时，不支持ICP优化方法。
9.其它查询优化策略
9.1 EXISTS 和IN的区分
问题:
不太理解哪种情况下应该使用EXISTS，哪种情况应该用IN。选择的标准是看能否使用表的索引吗?
回答:
索引是个前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为 小表驱动大表。在这种方式下效率是最高的。
比如下面这样:
:::tips
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的实现，相当于外表循环，实现的逻辑类似于:
当B小于A时用IN，因为实现的逻辑类似于:
哪个表小就用哪个表来驱动，A表小就用EXISTS，B表小就用IN。
9.2 COUNT(*)与COUNT(具体字段)效率
问:在MysQL中统计数据表的行数，可以使用三种方式: SELECT COUNT(*)、SELECT COUNT(1)和SELECTCOUNT(具体字段)，使用这三者之间的查询效率是怎样的?
前提:如果你要统计的是某个字段的非空数据行数，则另当别论，毕竟比较执行效率的前提是结果一样才可以。
环节1:COUNT()和COUNT(1)都是对所有结果进行COUNT,COUNT()和COUNT(1)本质上并没有区别(二者执行时间可能略有差别，不过你还是可以把它俩的执行效率看成是相等的)。如果有WHERE子句，则是对所有符合筛选条件的数据行进行统计;如果没有WHERE子句，则是对数据表的数据行数进行统计。
环节2:如果是MyISAM存储引擎，统计数据表的行数只需要0(1)的复杂度，这是因为每张MyISAM的数据表都有一个meta信息存储了row_count值，而一致性则由表级锁来保证。
如果是InnoDB存储引擎，因为InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MyISAM一样，维护一个row_count变量，因此需要采用扫描全表，是o(n)的复杂度，进行循环＋计数的方式来完成统计。
环节3:在InnoDB引擎中，如果采用COUNT(具体字段)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引(非聚簇索引)。对于COUNT(*)和COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。
如果有多个二级索引，会使用key_len 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。
9.3 关于SELECT(*)
在表查询中，建议明确字段，不要使用作为查询的字段列表，推荐使用SELECT <字段列表>查询。原因:
① MySQL 在解析的过程中，会通过 查询数据字典 将” “按序转换成所有列名，这会大大的耗费资源和时间。
②无法使用 覆盖索引
9.4 LIMIT 1 对优化的影响
针对的是会扫描全表的SQL语句，如果你可以确定结果集只有一条，那么加上LIMIT 1的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。
如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上LIMIT1了。
9.5 所使用COMMIT
只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少。
- COMMIT所释放的资源:
- 回滚段上用于恢复数据的信息·被程序语句获得的锁
- redo /undo log buffer中的空间
-
10.淘宝数据库，主键如何设计?
聊一个实际问题：淘宝的数据库，主键是如何设计的？
某些错的离谱的答案还在网上年复一年的流传着，甚至还成为了所谓的MySQL军规。其中，一个最明显的错误就是关于MySQL的主键设计。
大部分人的回答如此自信：用8字节的 BIGINT 做主键，而不要用INT。 错 ！
这样的回答，只站在了数据库这一层，而没有 从业务的角度 思考主键。主键就是一个自增ID吗？站在2022年的新年档口，用自增做主键，架构设计上可能 连及格都拿不到 。10.1 自增ID的问题
自增ID做主键，简单易懂，几乎所有数据库都支持自增类型，只是实现上各自有所不同而已。自增ID除了简单，其他都是缺点，总体来看存在以下几方面的问题：
1. 可靠性不高
存在自增ID回溯的问题，这个问题直到最新版本的MySQL 8.0才修复。
2. 安全性不高
对外暴露的接口可以非常容易猜测对应的信息。比如：/User/1/这样的接口，可以非常容易猜测用户ID的值为多少，总用户数量有多少，也可以非常容易地通过接口进行数据的爬取。
3. 性能差
自增ID的性能较差，需要在数据库服务器端生成。
4. 交互多
业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，这需要多一次的
网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。
5. 局部唯一性
最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。对于目前分布式系统来说，这简直就是噩梦。10.2 业务字段做主键
为了能够唯一地标识一个会员的信息，需要为 会员信息表 设置一个主键。那么，怎么为这个表设置主
键，才能达到我们理想的目标呢？ 这里我们考虑业务字段做主键。
表数据如下：
在这个表里，哪个字段比较合适呢？ 选择卡号（cardno）
会员卡号（cardno）看起来比较合适，因为会员卡号不能为空，而且有唯一性，可以用来 标识一条会员记录。
:::tips
mysql> CREATE TABLE demo.membermaster
-> (
-> cardno CHAR(8) PRIMARY KEY, — 会员卡号为主键
-> membername TEXT,
-> memberphone TEXT,
-> memberpid TEXT,
-> memberaddress TEXT,
-> sex TEXT,
-> birthday DATETIME
-> );
Query OK, 0 rows affected (0.06 sec)
:::
不同的会员卡号对应不同的会员，字段“cardno”唯一地标识某一个会员。如果都是这样，会员卡号与会员一一对应，系统是可以正常运行的。
但实际情况是， 会员卡号可能存在重复使用 的情况。比如，张三因为工作变动搬离了原来的地址，不再到商家的门店消费了 （退还了会员卡），于是张三就不再是这个商家门店的会员了。但是，商家不想让这个会 员卡空着，就把卡号是“10000001”的会员卡发给了王五。
从系统设计的角度看，这个变化只是修改了会员信息表中的卡号是“10000001”这个会员 信息，并不会影响到数据一致性。也就是说，修改会员卡号是“10000001”的会员信息， 系统的各个模块，都会获取到修改后的会员信息，不会出现“有的模块获取到修改之前的会员信息，有的模块获取到修改后的会员信息，而导致系统内部数据不一致”的情况。因此，从 信息系统层面 上看是没问题的。
但是从使用 系统的业务层面 来看，就有很大的问题 了，会对商家造成影响。
比如，我们有一个销售流水表（trans），记录了所有的销售流水明细。2020 年 12 月 01 日，张三在门店购买了一本书，消费了 89 元。那么，系统中就有了张三买书的流水记录，如下所示：
接着，我们查询一下 2020 年 12 月 01 日的会员销售记录：
:::tips
mysql> SELECT b.membername,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);
+——————+—————-+—————+——————+——————————-+
| membername | goodsname | quantity | salesvalue | transdate |
+——————+—————-+—————+——————+——————————-+
| 张三 | 书 | 1.000 | 89.00 | 2020-12-01 00:00:00 |
+——————+—————-+—————+——————+——————————-+
1 row in set (0.00 sec)
:::
如果会员卡“10000001”又发给了王五，我们会更改会员信息表。导致查询时：
:::tips
mysql> SELECT b.membername,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);
+——————+—————-+—————+——————+——————————-+
| membername | goodsname | quantity | salesvalue | transdate |
+——————+—————-+—————+——————+——————————-+
| 王五 | 书 | 1.000 | 89.00 | 2020-12-01 00:00:00 |
+——————+—————-+—————+——————+——————————-+
1 row in set (0.01 sec)
:::
这次得到的结果是：王五在 2020 年 12 月 01 日，买了一本书，消费 89 元。显然是错误的！结论：千万不能把会员卡号当做主键。
- 选择会员电话 或 身份证号
会员电话可以做主键吗？不行的。在实际操作中，手机号也存在 被运营商收回 ，重新发给别人用的情况。
那身份证号行不行呢？好像可以。因为身份证决不会重复，身份证号与一个人存在一一对 应的关系。可问题是，身份证号属于 个人隐私 ，顾客不一定愿意给你。要是强制要求会员必须登记身份证号，会把很多客人赶跑的。其实，客户电话也有这个问题，这也是我们在设计会员信息表的时候，允许身份证号和电话都为空的原因。
所以，建议尽量不要用跟业务有关的字段做主键。毕竟，作为项目设计的技术人员，我们谁也无法预测在项目的整个生命周期中，哪个业务字段会因为项目的业务需求而有重复，或者重用之类的情况出现。
经验： 刚开始使用 MySQL 时，很多人都很容易犯的错误是喜欢用业务字段做主键，想当然地认为了解业 务需求，但实际情况往往出乎意料，而更改主键设置的成本非常高。
10.3 淘宝的主键设计
在淘宝的电商业务中，订单服务是一个核心业务。请问， 订单表的主键 淘宝是如何设计的呢？是自增ID吗？
打开淘宝，看一下订单信息：
从上图可以发现，订单号不是自增ID！我们详细看下上述4个订单号：
:::tips
1550672064762308113
1481195847180308113
1431156171142308113
1431146631521308113
:::
订单号是19位的长度，且订单的最后5位都是一样的，都是08113。且订单号的前面14位部分是单调递增的。
大胆猜测，淘宝的订单ID设计应该是：
:::tips
订单ID = 时间 + 去重字段 + 用户ID后6位尾号
:::
这样的设计能做到全局唯一，且对分布式系统查询及其友好。
10.4 推荐的主键设计
非核心业务 ：对应表的主键自增ID，如告警、日志、监控等信息。
核心业务 ：主键设计至少应该是全局唯一且是单调递增。全局唯一保证在各系统之间都是唯一的，单调
递增是希望插入时不影响数据库性能。
这里推荐最简单的一种主键设计：UUID。
UUID的特点：
全局唯一，占用36字节，数据无序，插入性能差。
认识UUID：
- 为什么UUID是全局唯一的？
- 为什么UUID占用36个字节？
- 为什么UUID是无序的？
MySQL数据库的UUID组成如下所示：
:::tips
UUID = 时间+UUID版本（16字节）- 时钟序列（4字节） - MAC地址（12字节）
:::
我们以UUID值e0ea12d4-6473-11eb-943c-00155dbaa39d举例：
为什么UUID是全局唯一的？
在UUID中时间部分占用60位，存储的类似TIMESTAMP的时间戳，但表示的是从1582-10-15 00：00：00.00到现在的100ns的计数。可以看到UUID存储的时间精度比TIMESTAMPE更高，时间维度发生重复的概率降低到1/100ns。
时钟序列是为了避免时钟被回拨导致产生时间重复的可能性。MAC地址用于全局唯一。
为什么UUID占用36个字节？
UUID根据字符串进行存储，设计时还带有无用”-“字符串，因此总共需要36个字节。
为什么UUID是随机无序的呢？
因为UUID的设计中，将时间低位放在最前面，而这部分的数据是一直在变化的，并且是无序。
改造UUID
若将时间高低位互换，则时间就是单调递增的了，也就变得单调递增了。MySQL 8.0可以更换时间低位和时间高位的存储方式，这样UUID就是有序的UUID了。
MySQL 8.0还解决了UUID存在的空间占用的问题，除去了UUID字符串中无意义的”-“字符串，并且将字符串用二进制类型保存，这样存储空间降低为了16字节。
可以通过MySQL8.0提供的uuid_to_bin函数实现上述功能，同样的，MySQL也提供了bin_to_uuid函数进行转化：
:::tips
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的全局 唯一的实现。 另外在真实的业务系统中，主键还可以加入业务和系统属性，如用户的尾号，机房的信息等。这样 的主键设计就更为考验架构师的水平了。
手动赋值字段做主键！
比如，设计各个分店的会员表的主键，因为如果每台机器各自产生的数据需要合并，就可能会出现主键重复的问题。
可以在总部 MySQL 数据库中，有一个管理信息表，在这个表中添加一个字段，专门用来记录当前会员编号的最大值。
门店在添加会员的时候，先到总部 MySQL 数据库中获取这个最大值，在这个基础上加 1，然后用这个值作为新会员的“id”，同时，更新总部 MySQL 数据库管理信息表中的当 前会员编号的最大值。
这样一来，各个门店添加会员的时候，都对同一个总部 MySQL 数据库中的数据表字段进 行操作，就解决了各门店添加会员时会员编号冲突的问题。
若有收获，就点个赞吧
0 人点赞
