1. 四大特性
- 原子性
- 一致性
- 隔离性
- 持久性
2. MySQl事务并发问题?
- 脏读:事务A读取,事务B更新,然后事务B回滚,则事务A读取到的数据为脏数据。
- 不可重复读:
- 幻读:
3.MySQL事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交(read unCommitted) | 是 | 是 | 是 |
不可重复读(read committed) | 否 | 是 | 是 |
可重复读(repeatable read) | 否 | 否 | 是 |
串行化(serializable) | 否 | 否 | 否 |
备注:默认级别(不可重复读)
原因是因为使用innerDB索引,解决了幻读的问题
级别越高,并发效率越低。
3.1. 读未提交(read-unCommitted)
案例:
两个客户端A,B 。共同复现该情景。步骤如下:
客户端A:执行设置事务级别,并且开启事务
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 200 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
客户端B:同客户端A执行设置事务级别,并且开启事务,同时执行一个更新操作
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 200 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
mysql> update group_table set salary=salary-50 where id=1000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 150 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
客户端A:执行查询操作
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 200 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 150 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
客户端B:执行回滚操作
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 150 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 200 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
客户端A:执行更新操作
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 150 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
mysql> update group_table set salary=salary-50 where id=1000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 150 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
通过以上步骤,可以得知,在最后一步中,客户端A并未更新数据,始终为未回滚前的数据。 从而造成了脏数据。
解决方案: 设置隔离级别为 read-committed
3.2. 不可重复读(read-committed)
客户端A:设置隔离级别,开启事务
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 200 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
客户端B:设置隔离级别,开启事务,执行更新,但未提交事务
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 200 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
mysql> update group_table set salary = salary - 50 where id = 1000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 150 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
客户端A:查询数据,发现数据并未修改,这就保证了数据库中数据还是原来的数据。
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 200 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
客户端B:提交事务
mysql> commit;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 150 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
客户端A:查询数据,发现数据库中的数据发生了变更,两次数据不一致。因而造成了不可以重复读。
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 150 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
解决方案:设置隔离级别为可重复读
3.3. 可重复读(repeatable read)-默认级别
设置客户端A,B 的事务级别均为可重复读:
set session transaction isolation level repeatable read;
start transaction;
客户端B执行更行操作:
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update group_table set salary = salary - 50 where id = 1001;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 150 |
| 1001 | 2 | 450 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
客户端A执行查询操作:
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 150 |
| 1001 | 2 | 500 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
客户端A执行更新操作:
mysql> update group_table set salary = salary - 50 where id = 1001;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 150 |
| 1001 | 2 | 400 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
4 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
此时,可以很明显的知道,客户端B执行更新操作后,salary数据从500变为450,但是在客户端A查询时,并不是450,而是500。这就说明了在隔离级别下可重复读使用的时MVCC机制,因而是的select操作不会更新version,是读取快照的历史version,如果时insert,update,delete会更新version吗?
客户端B执行insert操作:
mysql> insert into group_table values(null,4,3000);
Query OK, 1 row affected (0.03 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 150 |
| 1001 | 2 | 400 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
| 1004 | 4 | 3000 |
+------+---------+--------+
5 rows in set (0.00 sec)
客户端A执行select操作:
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 150 |
| 1001 | 2 | 400 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
+------+---------+--------+
5 rows in set (0.00 sec)
通过两个客户端的操作,可以清晰的知道,insert操作确实是更新了version信息,但是客户端A并没有查询到新增的这一条记录,是因为读取的是历史version版本。同时也并未出现幻读的情况。
未出现幻读的原因?
其实是因为我们使用的数据库索引类型为 innerDB ,这中索引帮我们解决了 幻读 的 情况!
那么什么是幻读呢?
幻读针对select操作,指在一个事务的任意一个时间片段内, 查询到了 做了 insert 操作的数据。 这个叫做幻读。
3.4. 串行化(serializable)
客户端A,B都设置隔离级别为串行化 serializable
set session transaction isolation level serializable;
start transaction;
客户端A执行查询操作:
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from group_table;
+------+---------+--------+
| id | user_id | salary |
+------+---------+--------+
| 1000 | 1 | 150 |
| 1001 | 2 | 400 |
| 1002 | 1 | 600 |
| 1003 | 3 | 700 |
| 1004 | 4 | 3000 |
| 1005 | 4 | 3000 |
+------+---------+--------+
6 rows in set (0.00 sec)
客户端B执行新增操作:
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert group_table values (null,10,50000);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
会发现,串行化的目的是解决了脏读,不可重复读,幻读。 但是出现了一次只能执行一条SQL操作,如果A操作了select,B执行insert操作,就会报错。 并发性极低, 开发中不适用。
4. 慢查询优化
4.1. 什么是慢查询?
慢查询是指执行select语句,花费的时间很长,会造成CPU,内存的利用率升高。
4.2. 启动及设置慢查询日志收集
-
设置慢查询日志存储路径
log-slow-queries=D:\log_select.log // 指定存储的目录
-
设置慢查询时间,寓意是指 超过 设置的时间,即为慢查询SQL语句
long_query_time = n // n 表示秒,一般为 1s
-
启动慢查询: set global show_query_log = ON;
set global show_query_log = ON; // 启动慢查询 OFF关闭 set global long_query_time = 1; // 秒为单位
-
查询是否开启慢日志,设置慢日志时间
show variable 'slow_query%'; show variable 'long_query_time';
-
查看慢日志内容
# Time: 2020-11-03T03:13:28.046777Z # User@Host: root[root] @ localhost [::1] Id: 8 # Query_time: 2.991988 #查询消耗时间 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 use boot; # 数据库 SET timestamp=1604373208; ## SQL语句 select sleep(3);
4.3. Linux 开启 和 设置慢查询日志
-
搜索my.cnf , 一般在etc 目录下
find / -name my.cnf
-
编辑 my.cnf
vim my.cnf
-
向my.cnf 添加配置信息
slow_query_log = 1 # 开启日志 slow_query_log_file = /mysql/data/localhost-slow.log # 慢查询日志存储路径 long_query_time = 1 # 设置慢查询临界时间 (默认10S)
-
重启mysql服务器
4.4. 日志分析工具
mysqldupslow、explain
5. SQL 优化
5.1. Like 优化
mysql> explain select * from test where test_username like '%三';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where test_username like '%三%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where test_username like '张%';
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test | NULL | range | test_username | test_username | 93 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
根据上面查询的SQL可以知道, Like 模糊查询中如果使用了 ‘%三’或者 ‘%三%’都将导致查询时进行全表扫描,因而在写模糊查询时,需要注意,只能: ‘张%’
5.2. where 条件查询时
in , not in , having 变成 exists , not exists ,不要以字符格式声明数字,要以数字格式声明字符。
mysql> explain select * from test where test_username in ('张三','李四');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ALL | test_username | NULL | NULL | NULL | 4 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
由图可以知道,使用in 或者 not in 会让索引失效。
5.3. from 多表查询
多表查询,选择记录较少的表作为基础表,超过三张表,选择交叉表进行关联查询。
5.4. select * 优化
5.5. 排序优化
查询中带有 distinct , order by ,union 的SQL语句会启动SQL引擎执行,需要排序,耗费资源。
5.6. 索引问题优化
- 避免对索引字段进行计算
- 避免对索引字段使用 not , <> ,=,!=
- 避免在索引字段上使用 is null 或者 is not null
- 避免在索引字段上使用函数
- 避免在索引字段上使用 空值
5.7. 表关联
left join , right join ,inner join