MySql优化

Mysql版本 5.7.26

一. Mysql概述

1. Mysql四层结构

网络连接层：包含本地socket通信，和基于客户端/服务端工具实现的tcp/ip通信。
Connectors:负责与其他编程语言的sql语言进行交互

服务层：负责查询处理和其他系统任务

Management Serveices & Utilities：系统管理与控制组件
Connection Pool (连接池)
SQL Interface (SQL接口)
Parser （解析器）
Optimizer (查询优化器)
Cache和Buffer (查询缓存)

插件式的存储引擎层：负责数据的存储与提取
常用的有innoDB和MyISAM
数据存储层：将数据存放与在运行的文件系统上

2. innoDB和MyISAM

对比项	MyISAM	innoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁	行锁
缓存	只缓存索引，不缓存真实数据	不仅缓存索引，还缓存真实数据
表空间	小	大
关注点	性能	事务

3. Mysql数据文件

存储文件
- frm文件：存放表结构
- myd文件：存放表数据
- myi文件：存放表索引
日志文件
- 二进制日志log-bin: 主从复制
- 错误日志log-error
- 查询日志log：默认关闭，记录查询的sql语句

4. 索引概述

索引是帮助MySql高效获取数据的数据结构。
换句话来说，索引是排好序的快速查找数据结构

4.1 索引实现原理（B+树）

其实就是树的数据结构，真实的数据存储在叶子节点中，非叶子节点只存储搜索方向的数据项和下一层的指针

3层的b+树可以表示上百万的数据，只需要3层IO就可以查找到所需要的数据

4.2 索引分类

单值索引：主键
唯一索引：唯一的值，允许为null
复合索引：多个字段作为索引

4.3 索引语法

创建索引

CREATE [UNIQUE] INDEX index_name ON myTable(coloumnName(length));
ALTER myTable ADD [UNIQUE] INDEX [index_name] on myTable(coloumnName(length));

删除索引
```
DROP INDEX [index_name] ON myTable;
```
查看索引
```
SHOW INDEX FROM myTable;
```

4.4 索引优缺点

优点：
- 提高了数据检索的效率，降低了数据库的IO成本
- 降低了数据排序的成本，降低了CPU消耗
缺点：
- 索引其实也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以也是占用空间的
- 会降低数据更新的效率。对有索引的字段更新时，也需要一起更新对应的索引信息

4.5 索引的应用场景

主键自动创建索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引
Where条件里用不到的字段不创建索引
高并发下尽量创建组合索引
查询中排序的字段，排序字段若通过索引去访问将大大提高速度
查询中统计或者分组字段适合创建索引

二. 性能分析

1. 7种join查询

mysql不支持full join。因此需要去用其他方式实现（union会组合并去重）

# 全连接，对应右1
SELECT * FROM tbl_emp t1 left join tbl_dept t2 on t1.dept_id = t2.id
UNION
SELECT * FROM tbl_emp t1 right join tbl_dept t2 on t1.dept_id = t2.id;

# 对应最下面的图
SELECT * FROM tbl_emp t1 left join tbl_dept t2 on t1.dept_id = t2.id where t2.id i s null
UNION
SELECT * FROM tbl_emp t1 right join tbl_dept t2 on t1.dept_id = t2.id where t1.dept_id is null;

2. explain分析

2.1 id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
即表的读取顺序

id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

2.2 select_type

查询类型，主要是区分普通查询，联合查询，子查询等复杂查询
即数据读取操作的操作类型

SIMPLE：简单的select查询，查询中不包含子查询或UNION
PRIMARY：查询中若包含任何复杂的子部分，最外层被标记
SUBQUERY：在select或者where中包含子查询
DERIVED：在from列表中包含的子查询被标记为此种。mysql会递归执行这些子查询，把结果放在临时表里
UNION：若第二个select出现在UNION之后，则被标记为此种。若UNION包含在FROM子句的子查询中，外层select被标记为：DERIVED
UNION RESULT：从UNION表获取结果的SELECT

2.3 table

查询的是哪张表的数据

2.4 type

显示查询使用了何种类型，从最好到最差依次是：
system>const>eq_ref>ref>range>index>all

system：表中只有一行数据(等于系统表)，这是const类型的特例，平时不会出现
const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where中，mysql就能将该查询转换为一个常量
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或者唯一索引扫描
ref：非唯一性索引扫描。返回所有匹配某个单值的行，然而，它可能会找到多个符合条件的行，所以属于查找和扫描的混合体。
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是where后面使用了between，<，>，in等查询。
index：index只遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。
all：全表扫描

2.5 possible_keys

可能用到的索引。一个或多个，但不一定实际被使用
即哪些索引可以被使用

2.6 key

此条查询实际用到的索引。若为null，说明没用到索引。查询中如果使用了覆盖索引，则该索引仅出现在key列表
即哪些索引实际被使用

2.7 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精度的前提下，长度越小越好

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，则key_len是根据表定义计算来的，不是通过表检索出的

2.8 ref

显示索引用到了哪一列，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
即表之间的引用

查询中与其他表关联的字段，外键关系建立索引

2.9 rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
即每张表有多少行被优化器查询

越小越好

2.10 extra

其他一些重要的额外信息

Using filesort(不好)：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行查询。mysql无法利用索引完成的排序操作成为“文件排序”
Using temporary(不好)：使用了临时表保存结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
Using index(好)：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有出现using where，说明索引用来读取数据而非执行查找动作
覆盖索引：
select的数据列只用从索引中就能获得，而不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件。
即：查询列要被所建的索引覆盖
Using where：表明使用了where过滤
using join buffer：使用了连接缓存
impossible where：表明where的条件为false，不可能查到数据
select tables optimized away：在没有group子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

三. 索引优化法则

测试表如下

CREATE TABLE `product` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `price` int(11) NOT NULL,
  `sub_title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `detail` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_price` (`name`(191),`price`,`sub_title`(191)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

索引建立如下：

CREATE INDEX `idx_name_price_sub` ON product(name, price, sub_title);

1. 全值匹配我最爱

使用索引时遵从复合索引的顺序，尽量全值匹配

 EXPLAIN SELECT `name`, price, sub_title from product WHERE name = '1' and price = 1 and sub_title = '1';

2. 最佳左前缀法则

查询从索引的最左列开始并且不跳过索引中的列

EXPLAIN SELECT `name`, price, sub_title from product WHERE price = 1 and sub_title = '1';

如上，由于索引的name_price_sub_title，但是查询的时候跳过了name，就会导致索引失效。

EXPLAIN SELECT `name`, price, sub_title from product WHERE name = `1` and sub_title = '1';

这句sql则只能用到name一个索引，剩下的会失效

3. 索引列上少计算

请不要再索引列上做任何操作(计算，函数，类型转换)，这些都会导致索引失效，转而全表扫描

EXPLAIN SELECT `name`, price, sub_title from product WHERE left(name,7) = '1';

如上，由于索引列name使用了left函数，因此导致了索引失效

4. 范围之后全失效

存储引擎无法使用索引中范围条件右边的列

EXPLAIN SELECT `name`, price, sub_title from product WHERE name = '1' and price > 1 and sub_title = '1';

如上，索引只用到了name和price，并且type为range，sub_title失效了

5. 覆盖索引最为佳

select后面的查询尽量使用覆盖索引，少用*

6. Like百分写最右

like后面的内容，如果%在最左边，则会导致索引失效

EXPLAIN SELECT * from product WHERE name = '1' and price = 1 and sub_title like '%1';

如上，只用到了两个索引键

7. is not null失效

EXPLAIN SELECT name from product WHERE name is not null;

8. 字符单引不能少

字符串单引号不加会导致索引失效

9. or会使索引失效

EXPLAIN SELECT name from product WHERE name = '1' or price = 1;

四. 索引案例

如果搞不清楚该让谁做驱动表、谁 join 谁，请让 MySQL 运行时自行判断，少写left join和right join
考虑到查询效率,能用join就不要用left\right join 使用外连接非常影响查询效率,就算要用也要用数据量最小的表作为驱动表来驱动大表
大表查询的时候，where 的条件千万记得加上索引

join查询，采用的是Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。

例: user表10000条数据，class表20条数据

select * from user u left join class c u.userid=c.userid

这样则需要用user表循环10000次才能查询出来，而如果用class表驱动user表则只需要循环20次就能查询出来

例：

select * from class c left join user u c.userid=u.userid

直接使用join的时候,mysql查询优化器会自动选择数据量最小的那张表作为驱动表。

用left join的时候,左边的是驱动表。right join则相反

Nest Loop Join 是一种基于嵌套循环的连接算法，也叫做嵌套循环连接。它是一种简单而有效的连接算法，在处理小数据集时表现良好。
Nest Loop Join 的基本思路是，对于两个需要连接的表，以其中一个表为驱动表，遍历驱动表的每一行记录，然后再遍历另一个表的每一行记录，找到符合连接条件的记录组合起来。这个过程就像一个嵌套循环，所以叫做 Nest Loop Join。
具体来说，假设有两个表 A 和 B，需要连接的条件是 A.x = B.y。那么 Nest Loop Join 的执行过程如下：
对于表 A 的每一行记录，都遍历一遍表 B 的所有记录，找到符合条件的记录组合起来。
如果表 A 中有 m 行记录，表 B 中有 n 行记录，那么 Nest Loop Join 的时间复杂度为 O(mn)。在最坏情况下，也就是两个表的记录数相等时，时间复杂度为 O(n^2)。
在实际应用中，通常会对驱动表进行排序或者建立索引，以提高 Nest Loop Join 的执行效率。
Nest Loop Join 还有一些变体，例如 Index Nested Loop Join、Block Nested Loop Join 等，可以更好地利用索引和缓存，提高连接的效率。
总的来说，Nest Loop Join 是一种简单而有效的连接算法，适用于小数据集和简单连接条件的情况。在实际应用中，需要根据具体情况选择合适的连接算法，以提高查询效率。

永远用小结果集驱动大结果集（Important）！

1. 两表关联

CREATE TABLE `order` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `user_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_productId` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

create index `idx_productId` on `order`(productId)

左连接left join
```
explain SELECT t1.* FROM product t1 LEFT JOIN `order` t2 on t1.id = t2.product_id;
```
如上，left join时，会使用到右表的索引
可以这样理解，left join查找的是左表的全部数据，因此左边索引不影响（左边肯定是要全扫描的），然后根据左表的数据对右表索引查找
相当于左表是驱动表，左表全表扫描的数据又会作为循环条件去查右表，因此右表加上索引，就能使用左表的数据作为索引条件，是生效的
- 特殊情况
```
explain SELECT * FROM product t1 LEFT JOIN `order` t2 on t1.id = t2.product_id;
```
  没有用到索引，不知道为啥，先记录一下。？？？
右连接right join
右和左是相反的，不再赘述

2. 三表关联

保证join语句中被驱动表上join条件字段已经被索引
被驱动的表：left join为右表，right join为左表

3. in和exists

小表驱动大表原则

exists
将主查询的数据放到子查询中条件验证，根据验证结果(true或者false)决定主查询的数据是否保留
即先查主查询(驱动表)，然后拿主查询的结果去子查询做条件(被驱动表)，因此当A表数据小于B表时，用exists
```
select * from A where exists (select 1 from B where A.id = B.id)
等价于
for select * from A
for select * from B where B.id = A.id
```

将子查询的结果作为条件(驱动表)，去查主查询的数据，因此，B表数据小于A表时，用in

select * from A where A.id in (select id from B);
等价于
for select id from B
for select * from A where A.id = B.id

4. order by索引相关

Mysql两种排序方式：文件排序或扫描有序索引排序
MySQL能为排序和查询使用相同的索引

Key a_b_c(a, b, c)

order by能使用索引最左前缀法则

order by a;
order by a,b;
order by a,b,c;

如果where使用的索引的最左前缀定义为常亮，则order by可以使用索引
```
where a = const order by b,c;
where a = const and b = const order by c;
```
不能使用索引排序的情况‘
- order by a asc, b desc, c asc; //排序不一致
- where g = const order by b, c; //丢失a索引
- where a = const order by c; //丢失b索引
- where a = const oder by a, d; //d不是索引
- where a in () order by b, c; //对于排序来说，多个相等条件也是范围查询，范围之后索引就失效了

五. 数据库锁相关

show open tables;

查看数据库加锁的表

show open tables from db;

查看指定数据库加锁的表

show status like "table%";

Table_locks_immediate: 产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值+1
Table_locks_waited：出现表级锁定争用发生等待的次数(不能立即获取锁的次数，每等待一次锁值+1)，此值高说明存在着较严重的表级锁争用情况

1. 表读锁

读锁是共享锁

lock table product read;

对product表加读锁，会造成以下情况

当前session对该表可以读，但是不能写
当前session对其余表无法读写
其他session可以对该表和其他表读，可以对其他表写，但是写该表时会被阻塞

lock table product read;

# 可以查
SELECT * from product;

# 不可以查 1100 - Table 'order' was not locked with LOCK TABLES
SELECT * from `order`;

# 不可修改 1100 - Table 'order' was not locked with LOCK TABLES
UPDATE `order` set user_name = '马皓楠' where id = 1; 

# 不可以修改 1099 - Table 'product' was locked with a READ lock and can't be updated
UPDATE product set name = 1;

UNLOCK tables;

session1的情况

SELECT * from product;

SELECT * from `order`;

# product加锁其他无法修改，会被阻塞，直到释放读锁
UPDATE product set name = 1 where id  =1;

# order未加锁可以修改
UPDATE `order` set user_name = '马皓楠' where id = 1;

session2的情况

2. 表写锁

写锁是独占锁

lock table product write;

对product表加写锁，会造成以下情况

Mysql版本 5.7.26

当前session对该表可以写，读操作会被阻塞，对其余表无法操作
其余session对该表无法读写，会被阻塞。对其余表可以操作

锁类型	当前session	其余session
表读锁	对该表可以读，但是不能写(报错)；对其余表无法操作	对该表可读无法写(阻塞)；对其余表任意操作
表写锁	对该表可以写，读操作会被阻塞，对其余表无法操作	对该表无法读写(阻塞)。对其余表任意操作

简而言之：
读锁会阻塞写，不会阻塞读。写锁会阻塞读写

3. 并发事务的问题

更新丢失
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生更新丢失问题
如果一个事务提交前，其他事务不能访问，则会避免此问题
脏读
事务A读取到了事务B已修改但尚未提交的数据，还在这个数据的基础上修改了。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求
不可重复读
事务A读取到了事务B已经提交的修改数据，不符合隔离性
幻读
事务A读取到了事务B新增的数据，不符合隔离性

4. 事务的隔离级别

mysql默认的隔离级别为可重复读

读数据一致性及允许的并发副作用隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读(Read Uncommited)	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交读(Read Commited)	语句级	否	是	是
可重复读(Repeatable read)	事务级	否	否	是
可序列化(Serializable)	最高级别，事务级	否	否	否

Read Uncommited(RU)：读未提交，一个事务可以读到另一个事务未提交的数据！
Read Committed (RC)：读已提交，一个事务可以读到另一个事务已提交的数据!
Repeatable Read (RR):可重复读，加入间隙锁，一定程度上避免了幻读的产生！注意了，只是一定程度上，并没有完全避免!另外就是记住从该级别才开始加入间隙锁
Serializable：串行化，该级别下读写串行化，且所有的select语句后都自动加上lock in share mode，即使用了共享锁。因此在该隔离级别下，使用的是当前读，而不是快照读。

隔离级别越严格，并发副作用越小，但是付出的代价也越大。因此事务隔离实际上就是使事务在一定程度上串行化。

查看mysql事务隔离级别

show VARIABLES like 'tx_isolation';

设置mysql的事务隔离级别

set session transaction isolation level repeatable read;

5. 行锁

对于innodb的表select 是不会锁表的。其实这里使用到了快照(Serializable事务级别下，都是当前读，会加共享锁)
SELECT * from product;
但是如果指定了共享锁（S锁）或者是排它锁(X锁)，还是会加锁的，并且是当前读
# 可读不可写，如果该列(price)没有加索引，因此由于间隙锁存在，会造成锁全表的效果，对于该表的所有写操作均不可（事务提交前）
select * from product where price = 300 lock in share mode;
# 不可读也不可写
select * from product where price = 300 for update;

InnoDB行锁是通过给索引上的索引项加锁来实现的

开销大，加锁慢；会出现死锁；锁定力度小，发生锁冲突的概率最低，并发度也最高

索引失效会导致行锁失效，变为表锁

之所以能够锁表，是通过行锁+间隙锁来实现的。那么，由于RU和RC都不存在间隙锁
因此，该说法只在RR和Serializable中是成立的。如果隔离级别为RU和RC，无论条件列上是否有索引，都不会锁表，只锁行！

RR/Serializable+条件列是聚簇索引
该情况的加锁特征在于，如果where后的条件为精确查询(=的情况)，那么只存在record lock。如果where后的条件为范围查询(>或<的情况)，那么存在的是record lock+gap lock
RR/Serializable+条件列非索引
RR级别需要多考虑的就是gap lock，他的加锁特征在于，无论你怎么查都是锁全表
RR/Serializable+条件列是非聚簇索引
如果是唯一索引，情况和RR/Serializable+条件列是聚簇索引类似，唯一有区别的是:这个时候有两棵索引树，加锁是加在对应的非聚簇索引树和聚簇索引树上
非聚簇索引是非唯一索引的情况，他和唯一索引的区别就是通过索引进行精确查询以后，不仅存在record lock，还存在gap lock。而通过唯一索引进行精确查询后，只存在record lock，不存在gap lock。

6. 间隙锁的危害

当我们用范围条件而不是相等条件检索数据，并请求共享或者排他锁时，Innodb会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙

InnoDb也会对这个间隙加锁，这就叫做间隙锁。

缺点：在RR和S级别下，由于间隙锁的存在，当锁定一个范围键值后，即时某些不存在的键值也会被无辜锁定，而造成在锁定时无法插入锁定范围内的任何数据。在某些场景下可能会对性能有较大的损失。

7. 性能优化

show status like 'innodb_row_lock%'

Innodb_row_lock_current_waits 当前正在等待锁定的数量
Innodb_row_lock_time 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg 每次锁定等待平均时长
Innodb_row_lock_time_max 从系统启动到现在锁定时间最长的一个
Innodb_row_lock_waits 系统启动后到现在总共等待的次数

show profile分析

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能较少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能低级别事务隔离

六. 其他性能优化

1. 大数据分页

当mysql数据量很大(过千万时)，limit分页会出现问题

例如select from test limit 0,10这个很快，毫秒级别的，但是select from test limit 866613,10这个就很慢，需要几秒钟

造成这样的原因是，当查询后者时，mysql需要扫描866623行数据，然后丢弃前面的86613行数据，只保留10行，自然会慢。

那么该如何解决这个问题呢？

利用表的覆盖索引来加速分页查询
根据上面的索引优化法则，覆盖索引最为佳，当select后面的查询条件只有索引列是，会直接从索引树中读取数据，而不会进行全表数据查询，自然速度就加快了

join+覆盖索引

select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)

如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！

select id from test where pid = 1 limit 100000,10;

创建索引：

alter table test add index idx_pid_id(pid, id)

2. group by 而不是distinct

使用GROUP BY代替DISTINCT：DISTINCT会对结果集进行去重，而GROUP BY在分组时也会去重。在这个查询中，使用GROUP BY可以更好地利用索引，因为它可以结合where查询列进行聚合，而不会导致全表扫描。

3. 大数量经验总结

in和exists使用场景注意，主表数据量小时用exists，反之用in。注意在查询条件加索引
exists是将主表的数据带入从表，去判断是否存在，找到一个1或者0就返回结果。主表会先根据其他条件查询，然后再一条条带进去判断。
如果两表数据量相同，这两个都慢，特别是count操作，对于分页查询来说，考虑重新设计表结构，避免关联查询

七. 细节知识

1. 锁

情况一

两个线程分别开启事务，一个事务执行insert语句，一个事务根据这个insert操作插入的唯一索引当做where条件进行更新。此时，如果insert语句先执行，事务未提交之前，另一个线程的update会等待锁。

即，insert操作给这行加了锁，无论事务是否提交，mysql都会记录这个锁。如果此时有其他线程对这行更新，那么都会造成阻塞。

示例如下：

CREATE TABLE `mq_msg` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tag` varchar(50) NOT NULL COMMENT '消息tag',
  `message_id` varchar(50) NOT NULL COMMENT '消息ID',
  `message` text NOT NULL COMMENT '消息JSON',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_message_id` (`message_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

A事务先执行，但不提交，即执行到第三行

BEGIN;
INSERT INTO `mq_msg` (`id`, `tag`, `message_id`, `message`, `create_time`, `update_time`) VALUES
(NULL, 'MQ_MSG_TAH_TRANSACTION', 'asd', 'xxx', '2020-12-31 15:33:59', '2020-12-31 15:33:59');
COMMIT;

B事务后执行

UPDATE mq_msg SET create_time = now() WHERE tag = 'MQ_MSG_TAH_TRANSACTION' and message_id = 'asd';

此时，B事务会等待，直到A事务commit或者rollback。

注意：只有B事务更新的条件中有唯一索引才会如此。

八. 函数

locate

MySQL 的 LOCATE() 函数用于查找子字符串在字符串中第一次出现的位置，如果找到则返回该位置，否则返回 0。

LOCATE() 函数的语法如下：

LOCATE(substring, string, start_position)

其中：

substring：要查找的子字符串。
string：要在其中查找子字符串的字符串。
start_position：开始搜索的位置，默认为 1，表示从字符串的第一个字符开始搜索。

举个例子，假设有一个字符串为 Hello World!，我们想要查找子字符串 World 在该字符串中第一次出现的位置，可以使用以下查询语句：

SELECT LOCATE('World', 'Hello World!'); -- 返回：7

在这个查询语句中，LOCATE() 函数会在字符串 'Hello World!' 中查找子字符串 'World'，并返回其第一次出现的位置，即 7。

需要注意的是，LOCATE() 函数区分大小写。如果要进行不区分大小写的查找，可以使用 LOWER() 或 UPPER() 函数转换为小写或大写字母后再进行查找。

九. json类型

9.1 索引

创建Json数据的索引

实战分析

实战中用过json数组的索引，效果很明显，例如["11","222","333"]这种形式的

表结构如下:

CREATE TABLE `tag_data_carrier_2` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tag_id` bigint NOT NULL COMMENT '标签id',
  `tag_key` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '标签唯一key',
  `tag_value` json NOT NULL COMMENT '标签值id,json存储,["15","16"]',
  `data_id` bigint NOT NULL COMMENT '数据id',
  `data_unique_key` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '数据唯一key',
  `data_origin` tinyint NOT NULL COMMENT '数据来源,1：SYSTEM-系统录入,2：OPERATION-手动录入',
  `data_type` tinyint NOT NULL COMMENT '数据类型,1：LINE_REGION-区域线路标签,2：LINE_DISTRICT-区县线路标签,3：LINE_DETAIL-明细线路标签,4: CARRIER-司机,5: VEHICLE-车辆',
  `platform_no` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '平台码',
  `create_user_id` bigint NOT NULL DEFAULT '0' COMMENT '创建人',
  `create_user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人名称',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_user_id` bigint NOT NULL DEFAULT '0' COMMENT '更新人',
  `update_user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人名称',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作更新时间',
  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_data` (`data_id`,`tag_id`,`data_type`) USING BTREE,
  KEY `idx_search` (`tag_key`,`data_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1754067 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

其中tag_value是一个json数组。

查询sql如下：

SELECT *
FROM tag_data_carrier_2
where tag_key = 'gender'
  and data_type = 4
and xxx

xxx就是对tag_value字段的检索，也就是需要某个值是否存在于tagValue中。如果不使用索引，这个sql查询在百万数据以上的时候耗时2s-10s之间。此时，需要在json字段上建立索引

第一种方案：

alter table tag_data_carrier_2
    add index `idx_tag_value` ((cast(`tag_value` as char(32) array)));

这种建立索引配合查询语句如下：select data_id

select data_id
from tag_data_carrier_2
where tag_key = 'gender'
  and data_type = 4
  AND '39861' member of (tag_value);

第二种方案

ALTER Table tag_data_carrier_2
    ADD INDEX `idx_tag_value` ((CAST(`tag_value` -> "$[*]" as CHAR(32) array)));

查询语句如下：

SELECT *
FROM tag_data_carrier_2
where tag_key = 'gender'
  and data_type = 4
and '102629' MEMBER OF (tag_value -> '$[*]');

需要注意的是，无论哪种方式，都得用字符串去搜索，不然无法使用索引。

如果觉得文章对你有用，请随意赞赏

mysql 性能优化