Mysql8.+学习笔记

本文主要学记录了Mysql8.+的一些知识。文章中的所涉及到的创建表的SQL脚本，如果感兴趣可留言或者私聊发送。

---

---

一、Mysql8.x安装

参考 ubuntu20.04.1下安装Mysql8.0.23

二、Mysql服务的常用命令

启动命令：

systemctl status mysqld    # 查看mysql状态                                                                         
systemctl start mysqld     # 启动mysql                                                                         
systemctl stop mysqld      # 停止mysql
systemctl restart mysqld     # 重新启动mysql

登录 MySQL

mysql服务启动后，在终端使用以下命令登录mysql自带的客户端：

mysql -h 主机名 -u 用户名 -p

参数说明：

• -h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
• -u : 登录的用户名;
• -p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。

例如登录本机mysql后台，使用以下命令回车后输入密码即可登录mysql客户端：

mysql -u root -p

三、Mysql组件介绍

索引

实际上，Mysql中的索引是一种数据结构，它存放了主键和索引字段以及指向实际数据的记录。在Mysql中，索引使用了一种高效查找算法（B+数），用于快速定位到具体数据的方法。但是索引也不是越多越好，因为Mysql不仅要维护实体表中的数据，也要维护索引的数据结构，过多的索引会造成mysql在操作数据之后，维护索引的成本过高，导致性能下降。

因为索引本身也是一种数据结构，实际上就是一张表，那么数据量也会随之增大，所以，索引也是以文件的形式存放在磁盘上。

• 索引的优劣势
  • 优势：
    1. 索引类似于字典，能提高查询效率。
    2. 如果要对数据进行排序，可以先对索引进行排序后，在根据索引获取数据，降低cpu的消耗。
  • 劣势：
    1. 索引也是一张表，数据量也会随之增大，所以会占用一定的空间。
    2. Mysql不仅要维护实体表中的数据，也要维护索引的数据结构，那么大量的INSERT、UPDATE、DELETE操作会触发mysql对索引表的调整维护，那么会大大降低这些操作的响应时间。

Mysql目前提供了以下四种索引类型：

• Btree索引：Btree是一种平衡搜索多叉树，一颗m叉的Btree有以下几个特点：

  1. 树中每个节点对多包含m个孩子。
  2. 除了根节点和叶子结点之外，其他节点上最少有 ceil(m/2) 个孩子。
  3. 若一个节点不是根节点，那么该节点上至少有两个孩子。
  4. 所有的叶子结点都在同一层。
  5. 每一个非叶子结点都有n个key和n+1个指针组成，其中 ceil(m/2) - 1 <= n <= m - 1。

  假如对[1，2，6，3，23，6，2]这组数据用Btree结构创建一个3叉Btree索引，当ceil(m/2) - 1 <= n <= m - 1 ，0 <= n <= 2，也就是当n大于2的时候，中间节点向上分类为父节点。演变过程如下：

  • 插入前两个节点：1，2

• 插入第三个节点：6，因为6大于2，应该放在2的右边，并且因为该层已经超过2，那么中间节点向上分裂为父节点。
  

• 插入第四个节点：3，因为3大于2，应该放在2的右孩子上，并且3小于6，那么3应该放在2的右孩子上的6的左边。

• 插入第五个节点：23，因为23大于2，应该放在2的右孩子上，并且23大于6，那么23应该放在2的右孩子上的6的右边，并且该分支上的节点数量已经超过2，那么中间节点向上分裂为父节点。
  

• 插入第六个节点：6，因为6大于2小于等于6，应该放在第二叉孩子上，并且6大于6，那么6应该放在第二叉孩子上的3的右边。

• 插入第七个节点：2，因为2小于等于2，应该放在第一叉孩子上，并且2大于1，那么2应该放在第一叉孩子上的1的右边。
  

总体演变过程：

查找23过程:

Mysql中默认使用的是B+tree索引，B+tree索引有以下几个特点：

• B+tree索引每一层最多有n个key，而Btree索引每一层最多有n-1个key。
• B+tree索引的叶结点保存的是所有的data的信息，而且data是按照顺序存储的，并且所有的各个data信息之间使用都有指向下一个区间data的指针，类似于链表结构，有助于提高区间访问性能。
• B+tree索引的非叶子结点保存的是key的索引信息，不保存data信息。

对[1，2，6，3，23，6，2]这组数据用B+tree结构创建一个3叉B+tree索引：

• Hash索引：哈希索引是一种以key-value为数据结构的索引，当只有精确查询匹配的列时才有效。哈希索引的思路是比较简单的，它将索引列的值通过hash函数，计算出一个hash码，将该hash码最为key，该行数据的地址作为value。

  优势： 哈希索引本身只需要存储hash值，占用空间比较少，结构紧凑，查找速度快。

  劣势：

  1. 哈希索引不是顺序的，所以hash索引无法进行排序。
  2. 哈希索引只支持精确查找，比如：=、in，不支持范围查找，比如：>，因为哈希索引是通过计算哈希值来创建索引，所以无法支持范围查找，若执行>等范围查找，只能全表扫描。
  3. 哈希索引容易出现哈希冲突，当出现哈希冲突的时候，就必须逐行比较，直到找到符合条件的行数据。同一样，当删除一条数据的时候，也必须逐行找到符合条件的索引删除，维护代价很高。

• R-tree索引（空间索引）：R-tree索引是MyISAM引擎的一种特殊的索引类型，相对于BTREE，RTREE的优势在于范围查找。

• Full-text索引（全文索引）：Full-text索引是MyISAM引擎的一种特殊的索引类型，主要用于解决 where keyword like ‘%word%’; 这种查询慢的问题。

分类：

（1）单值索引：一个索引只能包含单个列，但是一张表中可以包含多个单值索引。

（2）唯一索引：索引列的值必须是唯一的，但允许有空值。

（3）复合索引：一个索引中包含了多个列。

创建索引：

• 准备以下数据：

mysql> create database test_demo01 default charset=utf8mb4;
Query OK, 1 row affected (0.29 sec)

mysql> use test_demo01;
Database changed
mysql> select * from class;
+---------+-----------+
| classId | className |
+---------+-----------+
|       1 | 一班      |
|       2 | 二班      |
|       3 | 三班      |
+---------+-----------+
3 rows in set (0.00 sec)

mysql> select * from user;
+--------+----------+---------+-------------+---------+
| userId | userName | address | phone       | classId |
+--------+----------+---------+-------------+---------+
|      1 | zhangsan | 广州    | 18688610001 |       1 |
|      2 | lisi     | 广州    | 18688610002 |       1 |
|      3 | wangwu   | 东莞    | 18688610003 |       2 |
|      4 | qiqi     | 广州    | 18688610004 |       3 |
+--------+----------+---------+-------------+---------+
4 rows in set (0.00 sec)

mysql>

• 创建索引：

# 普通的索引创建
create [unique|fulltext|spatial] index index_name [usring index_type(默认BTREE索引)] on table_name(fild……);
# 给user表的name字段创建索引
create index index_user_name on user(userName);

# 创建主键索引，mysql默认为主键创建主键索引
alter table user add primary key(userId);
# 创建唯一索引
alter table class add unique index_class_name(className);
# 创建全文索引
alter table class add fulltext textindex_class_name(className);

# 查看索引
show index from user\G;

# 删除索引
drop index index_user_name on user;

• 索引的设计原则：

  1. 查询频率较高的字段建立索引。
  2. 尽量创建唯一索引，区分度越高，索引效率就越高。
  3. 索引不是多多益善，过多的索引只会增加维护索引的成本。
  4. 尽量创建复合索引，利用索引的最左原则，一个复合索引可以多个索引使用。例如：

  create index index_name_addr_phone on user(userName,address,phone);
  # 最左原则：当where条件中包含下边几种组合(and连接)，都会走index_name_addr_phone索引，即where条件中必须包含复合索引的最左边的一个字段时，就会走索引，但是不能跳过中间的字段，否则只会走第一个字段的索引，不会走最后一个字段的索引，与顺序无关。
  userName
  userName,address
  userName,phone # 虽然会走索引，但是只会用到userName的索引，不会用到phone的索引。
  userName,address,phone
  

查看索引使用情况（了解即可，各个指标可以自行百度）：

# 查看全局索引使用情况
mysql> show global status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 3391  |
| Handler_read_key      | 32087 |
| Handler_read_last     | 0     |
| Handler_read_next     | 32421 |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 636   |
| Handler_read_rnd_next | 19249 |
+-----------------------+-------+
7 rows in set (0.00 sec)

mysql>

视图

视图是一种虚拟存在的表，并不是实实在在存在的表，视图中的表的数据并不是真实存在于视图中的，而是存在于视图中涉及到的基表中。简单的来说，视图就是将一条select语句执行的结果集返回。

优势：

• 安全：视图可以做到对用户开放某个表的某个字段，比如，某个字段不想让用户看到，那么就可以不在视图中定义，这样，用户无论如何也不会知道这些字段的存在。
• 简单：当创建好了一个视图，我们可以直接像查询数据表一样查询视图，而不是每次都写复杂的select语句。
• 数据独立：假如表的结构变更了，视图会屏蔽这些变更，让用户无感知表结构变更。

创建视图：

# 创建一个user表和class表的视图
create or replace view view_user_class as
	select u.userId, u.userName, c.className from user u, class c where u.classId = c.classId;
	
mysql> select * from view_user_class;
+--------+----------+-----------+
| userId | userName | className |
+--------+----------+-----------+
|      1 | zhangsan | 一班      |
|      2 | lisi     | 一班      |
|      3 | wangwu   | 二班      |
|      4 | qiqi     | 三班      |
+--------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

# 查看视图可用show table即可。
# 查看视图创建时使用的select语句
mysql> show create view view_user_class;
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View            | Create View                                                                                                                                                                                                                                                               | character_set_client | collation_connection |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_user_class | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_user_class` AS select `u`.`userId` AS `userId`,`u`.`userName` AS `userName`,`c`.`className` AS `className` from (`user` `u` join `class` `c`) where (`u`.`classId` = `c`.`classId`) | utf8mb4              | utf8mb4_unicode_ci   |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql>

# 删除视图
drop view if exists view_user_class;

注意：因为视图也是一种表，所以视图实际也是可以update的，但是视图能否修改取决于创建视图时选择的模式，但是总体不建议去修改视图。

存储过程、函数

存储过程、函数是事先编译好存储在数据库中的一段SQL语句的集合，利用存储过程、函数可以减少数据库服务于web应用层之间的网络传输，大大提高数据处理效率。

存储过程和函数的区别在于：存储过程没有返回值，而函数必须由返回值。

存储过程：

# 创建一个存储过程，查询出用户信息，在命令行执行，修改下mysql的结束符为$
delimiter $;
create procedure p_select_user_info() 
begin
	select u.userId, u.userName, c.className from user u, class c where u.classId = c.classId;
end$;
# 调用存储过程
mysql> call p_select_user_info()$;
+--------+----------+-----------+
| userId | userName | className |
+--------+----------+-----------+
|      1 | zhangsan | 一班      |
|      2 | lisi     | 一班      |
|      3 | wangwu   | 二班      |
|      4 | qiqi     | 三班      |
+--------+----------+-----------+
4 rows in set (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

mysql>

# 删除存储过程
drop procedure if exists p_select_user_info$;

# 查询存储过程
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test_demo01';
show procedure status\G$;

# 查看存储过程创建时的语法
show create procedure p_select_user_info\G$;

# 根据给出的学生ID，获取学生信息
drop procedure if exists p_select_user_info2$;
create procedure p_select_user_info2(in userId int)
begin
	# 申明变量
	declare count int default 0;
	declare c_userId int;
	declare c_userName varchar(10);
	declare c_className varchar(10);
	declare c_chainese int default 0;
	declare c_math int default 0;
	declare no_data int default 0;

	# 申明游标，封装查询结果
	declare userInfo cursor for select u.userId, u.userName, c.className, s.chainese, s.math
									from user u, class c, score s
									where u.classId = c.classId
										and  u.scoreId = s.scoreId;
	# 利用mysql提供的退出游标的方式，当游标到找不到数据的时候，就退出								
	declare Exit handler for not found set no_data = 1;	

	# 查询所有学生成绩，并给出偏科结论：
	select u.userId, u.userName, c.className, s.chainese, s.math,getSuggest(s.chainese,s.math) as '是否偏科'
	from user u, class c, score s
	where u.classId = c.classId
		and  u.scoreId = s.scoreId;								

	# if判断
	if userId is null then 
		select '所有的学生信息如下';
		# ----------游标的使用规范开始---------
		# 打开游标
		open userInfo;
		# 循环遍历游标
		repeat 
			fetch userInfo into c_userId,c_userName,c_className,c_chainese,c_math;
			select c_userId,c_userName,c_className,c_chainese,c_math,getSuggest(c_chainese,c_math) as '是否偏科';
		    until no_data = 1
		end repeat;
		# 关闭游标
		close userInfo;
		# ----------游标的使用规范结束---------
		
	elseif userId is not null then 
	    # ----------游标的使用规范开始---------
		# 打开游标
		open userInfo;
		# 循环遍历游标
		repeat 
			fetch userInfo into c_userId,c_userName,c_className,c_chainese,c_math;
			if c_userId = userId then 
			    select c_userId,c_userName,c_className,c_chainese,c_math,getSuggest(c_chainese,c_math) as '是否偏科';
			end if;
		    until no_data = 1
		end repeat;
		# 关闭游标
		close userInfo;
		# ----------游标的使用规范结束---------
	else
		select '出错了！！！';
	end if;
end$;

函数：

函数和存储过成类似，只是函数必须带一个返回值，并且在定义的时候，就需要说明返回值类型。

# 根据传入的成绩，给出偏科结论
drop function if exists getSuggest$;
create function getSuggest(chainese int, math int)
returns varchar(100) DETERMINISTIC
begin
	declare suggest varchar(100);
	# ----------case when使用案例---------
	select  (
		case 
		when chainese > math then '偏科(语文)' 
		when chainese < math then '偏科(数学)' 
		else '不偏科' end
		) into suggest;
	return suggest;
end$;

触发器

触发器是于数据表有关的一个逻辑定义，一般用于insert、update、delete操作时出发，类似于事件监听，会在insert、update、delete操作执行前后出发一段逻辑的执行。mysql目前支持行级触发器。

触发器可以用来做日志记录、数据检验等。

在触发器中，有两个特殊的变量OLD/NEW，这两个变量就会就留记录一个操作前后的数据。

触发器类型	OLD/NEW变量含义
INSERT触发器	只有NEW变量，代表新插入的那行数据。
UPDTE触发器	NEW变量，代表更新后的那行数据。OLD变量，代表更新之前的那行数据
DELETE触发器	只有OLD变量，代表被删除的那行数据。

create table class_log(
	id int(10) not null auto_increment,
	type varchar(10) not null,
	classId int(10) not null,
	logTime varchar(20) not null,
	details varchar(2000),
	primary key (id)
	)engine=innodb default charset=utf8;
	
# 对class表update操作建立触发器，记录操作日志。
drop trigger if exists t_class_update$;
create trigger t_class_update
after update
on class
FOR EACH ROW
begin
	insert into class_log(id,type,classId,logTime,details) values(null, 'update', new.classId, now(),
		concat('更新前数据详情：[', old.classId,',', old.className,']','更新后数据详情：[', new.classId,',', new.className,']'));
end$;

四、Mysql的存储引擎

Mysql的存储引擎用来实现对数据的操作的实现技术，比如建立索引的方式，查询数据的方式。存储引擎是基于表的，也就是说，一个数据库中，每张表都可以使用不同的存储引擎实现，这样更具有灵活性，可基于实际场景来使用不同的存储引擎。

Mysql8默认支持的存储引擎是InnoDB，在创建表的时候，如果不指定存储引擎，那么Mysql默认使用InnonDB存储引擎，InnonDB是唯一事务、行级锁、外键三者都支持的存储引擎。使用show engines来查询Mysql支持的所有的存储引擎如下：

mysql> show engines$;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

mysql>

存储引擎是InnoDB，在data目录下会看到2类文件：.frm、.ibd

（1）*.frm–表结构的文件。

（2）*.ibd–表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

五、Mysql的锁机制

Myisam存储引擎：

Myisam存储引擎只支持表锁，在执行SELECT语句的时候，Myisam存储引擎自动会为表加上读锁，在执行INSERT、UPDATE、DELETE语句的时候，也会自动加上写锁。Myisam存储引擎也提供了手动加锁的命令。

create table myisam_lock(
	id int(10) not null auto_increment,
	lockName varchar(10),
	primary key (id)
	)engine=myisam default charset=utf8;

• 读锁（共享锁）

1. 在客户端1上给myisam_lock表加上读锁之后，客户端1和客户端2均可以查询myisam_lock表数据，但是在客户端1上无法查询其他的表，比如查询user表时，时，直接报错，但是在客户端2上查询其他的表正常，比如查询user表成功。
2. 在客户端1无法对myisam_lock表执行update、insert等操作，直接提示加了读锁，无法操作，但是在客户端2上仍然可以对myisam_lock表执行update、insert等操作，但是发现是阻塞的。直到在客户端1上执行unlock命令才能成功。

总结：read锁为读锁，也就是共享锁，不同客户端之间还是可以对加锁表进行读操作。

• 写锁（独占锁）

1. 在客户端1上给myisam_lock表加上写锁之后，客户端1可以对myisam_lock表数据进行增删改查等操作，但是无法处理其他的表，比如查询user表时，直接报错，但是在客户端2上无法对myisam_lock表进行的所有操作都会处理阻塞状态，直到在客户端1上执行unlock命令才能成功，但是处理其他的表正常，比如查询user表成功。

总结：write锁为写锁，也就是独占锁，不同客户端之间对相同的表进行的操作是排斥的。

查看表被锁占用情况：

• show open tables;

In_use：代表表是否正在被占用。

Name_locked：代表表是否被锁定。只有取消表或者重命名表才有效。

mysql> show open tables;
+--------------------+---------------------------+--------+-------------+
| Database           | Table                     | In_use | Name_locked |
+--------------------+---------------------------+--------+-------------+
| test_demo01        | myisam_lock               |      0 |           0 |
| performance_schema | session_variables         |      0 |           0 |
| test_demo01        | score                     |      0 |           0 |

• show status like ‘Table_lock%’

Table_locks_immediate：查看立即获取表锁的次数。

Table_locks_waited：查看表在获取表锁时等待的次数。

mysql> show status like 'Table_lock%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 28    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

mysql> 

InnonDB存储引擎：

InnonDB存储引擎支持行锁，这也是InnonDB存储引擎能支持事务的是基础。InoonDB存储引擎在执行SELECT语句时，不会加任何锁，但是在执行INSERT、UPDATE、DELETE操作时会自动给设计的数据增加排它锁。InoonDB存储引擎也提供了手动加锁的命令：

# 创建一张表，存储引擎选择innodb
create table innodb_lock(
	id int(10) not null auto_increment,
	lockName varchar(10),
	primary key (id)
	)engine=innodb default charset=utf8;
# 关闭测试客户端的自动提交功能	
set autocommit=0;

• 共享锁

1. 在客户端1上给innodb_lock表中的id=1的数据加上读锁之后，客户端1和客户端2均可以查询innodb_lock表数据，但是在客户端1和客户端2上查询其他的表正常，比如查询user表成功。
2. 在客户端1可以对innodb_lock表中的id=1的数据执行update操作，也可以执行insert等操作，在客户端2上也可以对innodb_lock表中的其他数据执行select、update、insert等操作，但是如果继续对id=1的数据执行操作的时候发现是阻塞的，直到在客户端1上执行commit命令才能成功。

总结：share锁为读锁，也就是共享锁，不同客户端之间对加锁表的对应数据不能进行除了SELECT以外的操作。

• 排他锁：

1. 在客户端1上给innodb_lock表中的id=2的数据加上排他锁之后，客户端1和客户端2均可以查询innodb_lock表中的id=2的数据，且在客户端1更新id=2的数据，以及插入其他的数据均成功，但是在客户端2上更新id=2的数据进入阻塞状态，直到在客户端1上执行commit命令才能成功。

总结：排它锁，不同客户端之间对加锁表的对应数据不能进行除了SELECT以外的操作。

• 行锁升级为表锁：

当where后边的字段索引失效的时候，行锁会升级为表锁。客户端1中对lockName = 1的数据进行更新操作，因为lockName为索引，且为字符串，这里没有加单引号，导致lockName索引失效，导致innondb_lock整张表被锁。

• 间隙锁

间隙锁是指where之后的条件为范围时，会导致范围内不存在的间隙也会被上锁。比如在客户端1上执行update语句，条件为id<10，那么在1~10这个范围内全部被锁，即使id=8的数据不存在，在客户端2插入一条主键为8的数据也会进入阻塞状态。

六、事务机制

概述

MySQL 事务是由一些sql语句的组成的逻辑处理单元，这些sql语句要么全部执行成功，要么全部执行失败。

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句。

特性

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

并发事务存在的问题

• 丢失更新：当多个事务同时对同一条数据进行更新操作时，最后执行执行成功的事务会将之前执行成功的事务的结果覆盖，即之前提交成功的事务会丢失自己的更新结果。
• 脏读：当一个事务对某一条数据进行操作时，还未最终提交，但是另外一个结果已经将该结果读取到，即一个事务读取了另外一个事务未提交的结果。
• 不可重复读：当一个事务多次访问同一条数据期间，另外一个事务刚好对该数据进行了修改，导致该事务前后读取的数据出现不一致的情况。
• 幻读：当一个事务对表中的所有数据进行修改后，而此时又有另外一个事务插入或者删除了一条未被该事务修改过的数据，导致该事务发现自己少修改了数据或者多修改了数据的现象。

总结：

• 幻读 与 不可重复读

​ 不同点 ：

​        脏读：读取了前一事务 未提交 的数据 。

       不可重复读：读取了前一事务已提交的数据。

• 幻读 与 不可重复读

相同点：都是读取了另一条已经提交的事务（这点与脏读不同）。

不同点 ：

       不可重复读 ：查询的都是同一个数据项

       幻读：针对的是一批数据整体（比如数据的个数）

事务的隔离级别

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）（默认）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

七、Mysql优化

数据库的性能指标：

通过查看数据库的性能指标，就能判断当前数据库是以查询为主还是增删改为主。对Mysql的优化由一个大致的方向。

# 查看当前数据库中Innodb存储引擎中增删改查的指标，判断当前数据库是以查询为主还是增删改为主
mysql> show global status like 'Innodb_rows_%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 0     |
| Innodb_rows_read     | 4     |
| Innodb_rows_updated  | 0     |
+----------------------+-------+
4 rows in set (0.01 sec)

mysql>
# 查看当前数据库中增删改查的指标，判断当前数据库是以查询为主还是增删改为主
mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_import    | 0     |
| Com_insert    | 0     |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 6     |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+
11 rows in set (0.00 sec)

mysql>

查询缓存优化：

内存优化：

Myisam内存优化：

Myisam存储引擎使用key_buffer缓存索引块，用来加快mysql的数据读取速度。key_buffer_size用来决定索引区缓存大小。

# 可直接在my.cnf中直接修改。
key_buffer_size=512M

InnoDB内存优化：

InnonDB存储引擎，使用一块内存区域做缓存池，缓存池中不仅缓存了索引信息，还缓存了数据信息。innodb_buffer_pool_size用来控制缓存池的大小，适当的增大该值，则会提高缓存池的命中率。

# 查看innodb_buffer_pool_size的默认值，128M。可直接在my.cnf中直接修改。
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.02 sec)

mysql>

SQL语句优化：

尽量使用覆盖索引查询，不要使用select * 查询字段，即将* 替换成索引字段查询，这样mysql直接在索引结构中就能查到数据。如果不是索引字段，那么mysql在索引结构查到数据后，还要通过这些索引字段反查数据结构获取整行数据。

sql语句定位分析

• 定位效率比较低的sql：

  1. 慢查询日志：慢查询日志需要当sql语句执行结束后才能看到。

  2. show processlist：show processlist可以实时的监控出一些sql语句的执行。show processlist可以显示出当前sql语句是哪个用户正在执行，当前sql语句执行到哪个阶段，当前已经花费多长时间（s），并且详细的sql语句都会在列表中显示。

mysql> show processlist;
+----+-----------------+-----------+-------------+---------+------+------------------------+------------------+
| Id | User            | Host      | db          | Command | Time | State                  | Info             |
+----+-----------------+-----------+-------------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL        | Daemon  | 1162 | Waiting on empty queue | NULL             |
|  8 | root            | localhost | test_demo01 | Query   |    0 | init                   | show processlist |
|  9 | root            | localhost | test_demo01 | Sleep   |  920 |                        | NULL             |
+----+-----------------+-----------+-------------+---------+------+------------------------+------------------+
3 rows in set (0.00 sec)

mysql>

• show profile查看sql语句在各个阶段耗时：

# 查看当前mysql是否支持show profile语句。
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

# 查看是否开启了profile开关，默认关闭。
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

# 在当前会话打开profile开关
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

# 查看执行的所有的sql语句的总体耗时情况。
mysql> show profiles;
+----------+------------+-----------------------------------------+
| Query_ID | Duration   | Query                                   |
+----------+------------+-----------------------------------------+
|        1 | 0.00109125 | select * from class where classId = '1' |
|        2 | 0.00076300 | select * from class                     |
+----------+------------+-----------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>

# 查看某一条具体的sql语句的耗时情况，即各个阶段的耗时情况。
mysql> show profile for query 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000168 |
| Executing hook on transaction  | 0.000025 |
| starting                       | 0.000029 |
| checking permissions           | 0.000021 |
| Opening tables                 | 0.000071 |
| init                           | 0.000019 |
| System lock                    | 0.000024 |
| optimizing                     | 0.000016 |
| statistics                     | 0.000054 |
| preparing                      | 0.000061 |
| executing                      | 0.000113 |
| end                            | 0.000018 |
| query end                      | 0.000014 |
| waiting for handler commit     | 0.000024 |
| closing tables                 | 0.000022 |
| freeing items                  | 0.000050 |
| cleaning up                    | 0.000036 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

mysql>

• 查看sql语句的执行计划：

​ 通过上述方式定位出执行缓慢的sql语句之后，就可以利用explain查看sql语句的执行计划，包括sql语句执行时候是否走了索引，查表的顺序等，根据执行计划，定位慢查询语句真正的问题所在。

mysql> explain select * from class where classId = '1';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | class | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql>
# 字段说明：
-id：sql语句执行的先后顺序，id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。  
-select_type：语句类型，SIMPLE(简单SELECT，不使用UNION或子查询等)，PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)，UNION(UNION中的第二个或后面的SELECT语句)，DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)，UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)，SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)，DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)，DERIVED(派生表的SELECT, FROM子句的子查询)，UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
-table：查询时所连接上的表名，有时候可能不是真实的表名，可能是会是mysql自动生成的一个临时表的名称。
-partitions：匹配的分区。
type：表示表的连接类型。常用的类型有： ALL（全表扫描）、index（根据索引查找，并且只查询索引类字段）、range（范围查找）、ref、eq_ref、const、system、NULL（从左到右，性能从差到好），一般达到ref即可。
-possible_keys：可能用到的索引。
-key：实际用到的索引，必然包含在possible_keys中。
-key_len：使用到的索引的长度。
-ref：列与索引的比较。即连接匹配条件中，哪些列或常量被用于查找索引列上的值。
-rows：扫描出的行数(估算的行数)，即估算找到所需的记录所需要读取的行数。
-filtered：按表条件过滤的行百分比。
-Extra：一些额外的说明信息。Using where，不用读取表中所有信息，仅通过索引就可以获取所需数据。Using temporary，表示MySQL需要使用临时表来存储结果集，一般出现在order by或者group by 语句中。Using filesort，使用文件排序，一般当语句中有order by且不能根据索引排序时，会使用文件排序。

• 使用trance查看mysql优化器优化结果：

在mysql的体系结构中，用户sql最终会经过mysql提供的优化器，优化器会对用户sql按照自己的规则进行优化。mysql中也提供了一个工具trance用来查看优化器优化器优化结构。

# 打开trance开关
set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

# 随便执行一个查询语句(select * from information_schema.optimizer_trace\G;)后，执行(select * from information_schema.optimizer_trace\G;),显示如下，可以看到sql语句最终被优化为"/* select#1 */ select `class`.`classId` AS `classId`,`class`.`className` AS `className` from `class` where (`class`.`classId` > 2)"
select * from class where classId > 2;
mysql> select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
                            QUERY: select * from class where classId > 2
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `class`.`classId` AS `classId`,`class`.`className` AS `className` from `class` where (`class`.`classId` > 2)"
          }
        ] /* steps */
……………省略……………

mysql>

insert语句优化

• 多条insert语句合并为一条insert语句插入数据，因为多条insert语句存在与数据库服务多次连接交互的问题。

# 多条insert语句
insert into user values(null, 'zhangsan', '广州', '18688610001', '1', '1');
insert into user values(null, 'lisi', '广州','18688610002', '1', '2');
# 一条insert语句
insert into user values(null, 'zhangsan', '广州', '18688610001', '1', '1'),(null, 'lisi', '广州','18688610002', '1', '2');

• 建议在事务中执行多条insert语句，并将事务自动提交更改为手动提交，并且大批量数据可以考虑使用分段提交的方式。

start transaction
insert into user values(null, 'zhangsan', '广州', '18688610001', '1', '1');
insert into user values(null, 'lisi', '广州','18688610002', '1', '2');
commit;

• 建议在插入数据时，将数据按照主键顺序排序后插入。

insert into user values(1, 'zhangsan', '广州', '18688610001', '1', '1');
insert into user values(2, 'lisi', '广州','18688610002', '1', '2');
insert into user values(3, 'wangwu', '广州', '18688610001', '1', '1');
insert into user values(4, 'qiqi', '广州','18688610002', '1', '2');

order by语句优化

order by有两种排序方式，一种是using filesort、一种是using index，而using index直接利用索引排序，是效率最高的一种排序。

# order by productName没有走索引，因为使用了select *导致。
mysql> explain select * from product_load_data order by productName;
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | product_load_data | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   79 |   100.00 | Using filesort |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.03 sec)

mysql>
# order by productName走索引，因为使用了覆盖索引。
mysql> explain select productName from product_load_data order by productName;
+----+-------------+-------------------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type  | possible_keys | key               | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product_load_data | NULL       | index | NULL          | index_name_status | 66      | NULL |   79 |   100.00 | Using index |
+----+-------------+-------------------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> 
# order by productName asc, status desc没走索引，因为虽然使用了覆盖索引，但是order by productName asc, status desc使用不同的排序手段，即productName为升序，status为降序。
mysql> explain select productName,status from product_load_data order by productName asc, status desc;
+----+-------------+-------------------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table             | partitions | type  | possible_keys | key               | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------------------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | product_load_data | NULL       | index | NULL          | index_name_status | 66      | NULL |   79 |   100.00 | Using index; Using filesort |
+----+-------------+-------------------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>
# order by productName asc, status asc走索引，因为使用了覆盖索引，并且order by productName asc, status asc使用了相同的排序手段，即productName、status均为升序。
mysql> explain select productName,status from product_load_data order by productName asc, status asc;
+----+-------------+-------------------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type  | possible_keys | key               | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product_load_data | NULL       | index | NULL          | index_name_status | 66      | NULL |   79 |   100.00 | Using index |
+----+-------------+-------------------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

group by语句优化

Mysql 5.6以下的版本group by 底层默认是排序的，即隐式排序，如果只是简单的分组，那么就可以取消排序。8.0以上版本底group by 底层已经不在支持排序，这一点官网有说明，版本不同，使用时注意区分即可。

# mysql5.6版本以下，取消group by隐式排序的方式
select status, count(*) from product_load_data01 group by status order by null;

# 当表中对productName没有建立索引时，分组时用的是Using temporary
mysql> explain select productName, count(*) from product_load_data01 group by productName;
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | product_load_data01 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   79 |   100.00 | Using temporary |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql>

# 当表中对productName建立索引时，使用了索引index_name_status，并且分组时用的是Using index
mysql> explain select productName, count(*) from product_load_data01 group by productName;
+----+-------------+---------------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-------------+
| id | select_type | table               | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product_load_data01 | NULL       | index | index_name_status | index_name_status | 66      | NULL |   79 |   100.00 | Using index |
+----+-------------+---------------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

子查询语句优化：

尽量使用关联查询代替子查询。

or查询语句优化：

• or连接的两个字段，必须得都有索引，且不能都是复合索引，必须是单列索引才能走索引。

# productTime有索引，但是num没有索引，所以整体都不走索引。
mysql> explain select * from product where productTime = '2022-03-23-12:00:10' or num = '1000';
+----+-------------+---------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys      | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+--------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product | NULL       | ALL  | index_product_time | NULL | NULL    | NULL |   79 |    19.00 | Using where |
+----+-------------+---------+------------+------+--------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
# productTime有索引，num有索引，所以整体都走索引。
mysql> explain select * from product where productTime = '2022-03-23-12:00:10' or num = '1000';
+----+-------------+---------+------------+-------------+--------------------------------------+--------------------------------------+---------+------+------+----------+----------------------------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys                        | key                                  | key_len | ref  | rows | filtered | Extra                                                          |
+----+-------------+---------+------------+-------------+--------------------------------------+--------------------------------------+---------+------+------+----------+----------------------------------------------------------------+
|  1 | SIMPLE      | product | NULL       | index_merge | index_product_time,index_product_num | index_product_time,index_product_num | 153,5   | NULL |   20 |   100.00 | Using union(index_product_time,index_product_num); Using where |
+----+-------------+---------+------------+-------------+--------------------------------------+--------------------------------------+---------+------+------+----------+----------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

• 官方建议用union代替or：

# 使用or查询，type为rang
mysql> explain select * from product where productId = '1'  or productId = '16';
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
# 使用union查询，type为const常量
mysql> explain select * from product where productId = '1' union select * from product where  productId = '16';
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | product    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
|  2 | UNION        | product    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

mysql>

limit分页查询语句优化：

当分页数据越来越大的时候，分页的效率就会越来越低。

• 可先在主键索引上排序后，再利用主键关联查询主表分页查询。

# 直接使用limit查询数据，花费时间0.02 sec，并且也可以看出，走的是全表扫描
mysql> explain select * from product limit 60,10;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | product | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   79 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

mysql>
# 先在主键索引上排序后，再利用主键关联查询主表分页查询，花费时间0.00 sec，并且也可以看出，走了索引
mysql> explain select * from product p, (select productId from product order by productId limit 60,10) a where p.productId = a.productId;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL        |   70 |   100.00 | NULL        |
|  1 | PRIMARY     | p          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | a.productId |    1 |   100.00 | NULL        |
|  2 | DERIVED     | product    | NULL       | index  | NULL          | PRIMARY | 4       | NULL        |   70 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

mysql>

• 先查询出某个范围的数据之后，再获取指定数量的数据，这种方式只适合于主键自增的表。

]]mysql> explain select * from product where productId > 60 limit 10;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   19 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

指定mysql走哪个索引：

• 指定查询语句走哪个索引，此时的索引是一个参考索引，mysql最终可能不会使用，语法use index(索引名称)：

# 给productName字段单独建立索引，加上之前创建的复合索引，相当于productName有两种索引
create index index_product_name on product(productName);
# 默认情况下，mysql选择的是index_product_name_status_addr这个复合索引
mysql> explain select * from product where productName = '小米K145';
+----+-------------+---------+------------+------+---------------------------------------------------+--------------------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                                     | key                            | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------------------------------------------+--------------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | product | NULL       | ref  | index_product_name_status_addr,index_product_name | index_product_name_status_addr | 33      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------------------------------------------+--------------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>
# 在指定使用index_product_name索引时，此时的索引是一个参考，mysql最终选择的是index_product_name这个单列索引
mysql> explain select * from product use index(index_product_name)  where productName = '小米K145';
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | product | NULL       | ref  | index_product_name | index_product_name | 33      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql>

• 忽略哪个索引，语法ignore index(索引名称)：

# 指定忽略index_product_name_status_addr索引时，mysql最终选择的是index_product_name这个单列索引
mysql> explain select * from product ignore index(index_product_name_status_addr)  where productName = '小米K145';
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | product | NULL       | ref  | index_product_name | index_product_name | 33      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> 

• 强制指定查询语句走哪个索引，语法force index(索引名称)：

# 给productAddress创建一个单列索引
create index index_product_addr on product(productAddress);
# 虽然productAddress有索引，但是mysql没有走索引，这是因为数据表中大多数数据都是西安的，索引mysql认为直接查表比走索引更加高效，所以没有走索引，那么我们可以使用force index(index_product_addr)命令，强行让期按照索引查询。
mysql> explain select * from product  where productAddress = '西安';
+----+-------------+---------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys      | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+--------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product | NULL       | ALL  | index_product_addr | NULL | NULL    | NULL |   79 |    98.73 | Using where |
+----+-------------+---------+------------+------+--------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql>
# 使用force index(index_product_addr)命令，强行让期按照索引查询。
mysql> explain select * from product force index(index_product_addr)  where productAddress = '西安';
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | product | NULL       | ref  | index_product_addr | index_product_addr | 33      | const |   78 |   100.00 | NULL  |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

索引优化：

准备环境，创建以下表，并创建如下索引。

create table product(
	productId int(10) not null auto_increment,
	productName varchar(10),
	productAddress varchar(10),
	productTime varchar(50),
	status varchar(10),
	num int(10),
	primary key (productId)
	)engine=innodb default charset=utf8;
	
create index index_product_name_addr_status on product(productName,productAddress,status);

• 索引失效的情况：

  1. 使用复合索引查询时，不遵守最左法则，索引失效：

mysql> explain select * from product where productAddress = '西安' and status = '1';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   19 |     5.26 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

2. 使用范围查找，范围右侧之后的字段不会走索引：

# productName = '小米K87' and status > '1'的索引长度为66
mysql> explain select * from product where productName = '小米K87' and status > '1';
+----+-------------+---------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys                  | key                            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | product | NULL       | range | index_product_name_status_addr | index_product_name_status_addr | 66      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

# productName = '小米K87' and status > '1' and productAddress = '西安' 的索引长度依旧为66，说明 productAddress = '西安' 没有走索引。
mysql> explain select * from product where productName = '小米K87' and status > '1' and productAddress = '西安';
+----+-------------+---------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys                  | key                            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | product | NULL       | range | index_product_name_status_addr | index_product_name_status_addr | 66      | NULL |    1 |    10.00 | Using index condition |
+----+-------------+---------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

3. 对某个列做了相关的操作（比如：使用substring()函数），在作为条件查询，索引失效。

mysql> explain select * from product where substring(productName,2) = '小米';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   19 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

4. 字符串不加单引号，索引失效，因为mysql检测到是字段是字符串类型，会在底层隐式转化，这样就对字段做了运算操作，导致索引失效。

# productName='小米K98'的索引长度为33
mysql> explain select * from product where productName='小米K98';
+----+-------------+---------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                  | key                            | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | product | NULL       | ref  | index_product_name_status_addr | index_product_name_status_addr | 33      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# productName='小米K98' and status = 1 的长度依旧为33，说明 status = 1 没有走索引。
mysql> explain select * from product where productName='小米K98' and status = 1;
+----+-------------+---------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys                  | key                            | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | product | NULL       | ref  | index_product_name_status_addr | index_product_name_status_addr | 33      | const |    1 |    10.00 | Using index condition |
+----+-------------+---------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)

mysql>

5. 使用or分割开的条件，如果or前边的条件有索引，or后边的字段没有索引，那么所有的索引都失效。

# productName有索引，但是num没有索引，所以整个索引都失效。
mysql> explain select * from product where productName = '小米K96' or num = 1000;
+----+-------------+---------+------------+------+--------------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys                  | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+--------------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product | NULL       | ALL  | index_product_name_status_addr | NULL | NULL    | NULL |   19 |    19.00 | Using where |
+----+-------------+---------+------------+------+--------------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

5. 使用like关键字，%开头的模糊匹配查询，索引失效，如果非要用这种方式，那么查询字段使用索引字段可以解决索引失效的问题。

# %加在前边索引失效
mysql> explain select * from product where productName like '%K87%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   19 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# %加在两边索引失效
mysql> explain select * from product where productName like '%K87';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   19 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

6. 如果建了索引，但是查询时发现全表几乎所有的数据都是符合条件的数据，那么mysql底层会认为直接全表扫描查询比走完索引在反过来查询数据表更快速，那么此时也不会走索引。此种情况下，直接全表扫描查询会比索引查询块。

# productTime有索引，但是没有走索引，而是全表查询，因为全表19条数据，有17条是符合条件的数据。
mysql> explain select * from product where productTime = '2022-03-23 12:00:10';
+----+-------------+---------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys      | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+--------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product | NULL       | ALL  | index_product_time | NULL | NULL    | NULL |   19 |    89.47 | Using where |
+----+-------------+---------+------------+------+--------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
# productTime有索引，此时走了索引，因为全表19条数据，只有2条符合条件的数据。
mysql> explain select * from product where productTime = '2022-03-23 12:00:20';
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | product | NULL       | ref  | index_product_time | index_product_time | 153     | const |    2 |   100.00 | NULL  |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

# 但是如果查询字段只查询productTime，那么还是会走索引，因为直接在索引机构中就可以查询到数据，而无需再去查找数据表了。
mysql> explain select productTime from product where productTime = '2022-03-23 12:00:10';
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | product | NULL       | ref  | index_product_time | index_product_time | 153     | const |   17 |   100.00 | Using index |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

7. IS NULL、IS NOT NULL有时索引会失效，这个要综合考虑数据量，与第6点原理相同。
8. IN或者NOT IN走索引视情况而定，一般情况下IN走索引，NOT IN索引失效，但是还要综合考虑数据量，与第6点原理相同。

# productId为主键索引，productId in ('1','2')走了主键索引。
mysql> explain select productTime from product where productId in ('1','2');
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
# productId为主键索引，productId not in ('1','2')也走了主键索引。
mysql> explain select * from product where productId not in ('8','12');
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   17 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> 
# productName not in ('小米K84','小米K85')没有走索引。
mysql> explain select * from product where productName not in ('小米K84','小米K85');
+----+-------------+---------+------------+------+--------------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys                  | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+--------------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | product | NULL       | ALL  | index_product_name_status_addr | NULL | NULL    | NULL |   19 |    94.74 | Using where |
+----+-------------+---------+------------+------+--------------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
# productName in ('小米K84','小米K85')走索引。
mysql> explain select * from product where productName in ('小米K84','小米K85');
+----+-------------+---------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys                  | key                            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | product | NULL       | range | index_product_name_status_addr | index_product_name_status_addr | 33      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

总结：

• 尽量创建复合索引，因为一个复合索引可以当多个索引使用。
• mysql底层会选择最优的索引，也就是辨识度最高的索引，所以有时候虽然使用了多个索引，但是也有可能只会走某个单独的索引。

大批量数据导入优化

• 使用load指令导入大批量数据时，使待导入的数据主键有序，也可提高导入效率。

# 以product表结构为基础，创建一张表
create table product_load_data as
	select * from product where 1 = 2;
# 开启本地文件加载模式
mysql> set global local_infile = 1;
Query OK, 0 rows affected (0.00 sec)

mysql>
# 使用load指令导入大批量数据，更多参数可参考官网[https://dev.mysql.com/doc/refman/8.0/en/load-data.html]。
mysql> load data local infile '/home/huanzi/Desktop/product.txt' into table product_load_data fields terminated by '\t' lines terminated by '\n';
Query OK, 79 rows affected (0.37 sec)
Records: 79  Deleted: 0  Skipped: 0  Warnings: 0

mysql>

• 使用mysqlimport指令导入大批量数据。

# 以product表结构为基础，创建一张表
create table product_load_data01 as
	select * from product where 1 = 2;
	
# 使用load指令导入大批量数据，更多参数可参考官网[https://dev.mysql.com/doc/refman/8.0/en/mysqlimport.html]。
mysqlimport -u root -p --local test_demo01 '/home/huanzi/Desktop/product_load_data01.txt' --fields-terminated-by='\t' --lines-terminated-by='\n'

• 导入数据时关闭唯一性校验，导入数据之后再开启，也可提高导入效率

# 关闭唯一性校验
mysql> set unique_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql>
# 开启唯一性校验
mysql> set unique_checks=1;
Query OK, 0 rows affected (0.01 sec)

mysql>

• 导入数据时关闭事务自动提交，导入数据之后再开启，也可提高导入效率

# 关闭事务自动提交
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql>
# 开启事务自动提交
mysql> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

mysql>

Mysql并发参数调整：

• max_connections

调整mysql的最大连接数参数，默认151，代表最多能由151个客户端同时链接mysql服务，那么在并发量比较大的时候，那么可以考虑调大max_connections。

mysql> show variables like '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.01 sec)

mysql>

• back_log

当客户端的请求连接数达到max_connections时，新的链接请求将会放在请求栈中等待，back_log就是用来控制该请求栈大小的参数。

mysql> show variables like 'back_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log      | 151   |
+---------------+-------+
1 row in set (0.01 sec)

mysql>

• table_open_cache

该参数用来控制sql执行线程能打开表的数量，而每一个sql执行线程至少要打开一张表，可以根据max_connections参数来调整该参数的值。

mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 4000  |
+------------------+-------+
1 row in set (0.01 sec)

mysql>

• thread_cache_size

为了加快客户端链接mysql服务的速度，mysql服务会缓存一定数量的客户端链接请求，该参数就是用来设置mysql服务可以缓存客户端链接的个数。

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |
+-------------------+-------+
1 row in set (0.01 sec)

mysql>

• innodb_lock_wait_timeout

设置mysql行锁等待过期时间，默认50ms，为了避免一个事务执行时间过长，而发生事务回滚，可以设置该值大点。

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql>

九、Mysql正则匹配：

# 匹配userName以l开头的数据
mysql> select * from user where userName regexp '^l';
+--------+----------+---------+-------------+---------+---------+
| userId | userName | address | phone       | classId | scoreId |
+--------+----------+---------+-------------+---------+---------+
|      2 | lisi     | 广州    | 18688610002 |       1 |       2 |
+--------+----------+---------+-------------+---------+---------+
1 row in set (0.00 sec)

mysql>

十、Mysql的内置函数

# 获取当前时间
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-03-25 23:02:42 |
+---------------------+
1 row in set (0.00 sec)

mysql>
# 获取当前时间
mysql> select CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2022-03-25 23:05:19 |
+---------------------+
1 row in set (0.00 sec)

mysql>
# 获取当前时间
mysql> select SYSDATE();
+---------------------+
| SYSDATE()           |
+---------------------+
| 2022-03-25 23:07:24 |
+---------------------+
1 row in set (0.00 sec)

mysql>
# 当前时间往前退4天的日期
mysql> select SUBDATE(SYSDATE(),4);
+----------------------+
| SUBDATE(SYSDATE(),4) |
+----------------------+
| 2022-03-21 23:08:32  |
+----------------------+
1 row in set (0.00 sec)

mysql>
# 截取字符串
mysql> select SUBSTR('qawsed', 1, 2);
+------------------------+
| SUBSTR('qawsed', 1, 2) |
+------------------------+
| qa                     |
+------------------------+
1 row in set (0.00 sec)

mysql>
# 去除字符串两边的空格
mysql> select TRIM('    fff   ');
+--------------------+
| TRIM('    fff   ') |
+--------------------+
| fff                |
+--------------------+
1 row in set (0.00 sec)

mysql> 
# 字符串翻转
mysql> select REVERSE('abc');
+----------------+
| REVERSE('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.01 sec)

mysql>
# 向上取整
mysql> select ceil(1.4);
+-----------+
| ceil(1.4) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

mysql>
# 向下取整
mysql> select FLOOR(1.4);
+------------+
| FLOOR(1.4) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql>

总结：

本文主要学记录了Mysql8.+的一些知识。文章中的所涉及到的创建表的SQL脚本，如果感兴趣可留言或者私聊发送。