验证各种隔离级别

查看隔离级别

Mysql8.0版本后,Mysqltx_isolation改为了transaction_isolation

  • Mysql8.0版本后查询命令
select @@global.transaction_isolation,@@transaction_isolation;
  • Mysql8.0版本前查询命令
select @@global.tx_isolation,@@tx_isolation;
验证脏读

首先将全局改为Read uncommitted

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

重启一下Mysql,再查看一下隔离级别

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-UNCOMMITTED               | READ-UNCOMMITTED        |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

创建一个表

mysql> create table test_table(name varchar(255));
Query OK, 0 rows affected (0.02 sec)

mysql> desc test_table;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

事务A与事务B都开启事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

在事务A中提交一个数据

mysql> insert into test_table values('YQHP-YuKi');
Query OK, 1 row affected (0.00 sec)

这时候我是没提交的,但在事务B中查看一下

mysql> select * from test_table;
+-----------+
| name      |
+-----------+
| YQHP-YuKi |
+-----------+
1 row in set (0.00 sec)

脏读现象.png

验证不可重复读取数据

还是一样的,将事务级别改到read committed,重新创建一个test_table

AB开启事务,在A事务中提交一项数据,只要不提交事务,事务B就不会发现,脏读这种现象并不会发生

读提交.png

验证幻读

更改事务级别,创建测速表

幻象.png

验证串行化

更改事务级别,创建测试表

串行化.png

索引(index)

概述

索引是在数据库表的字段上添加的,是为了提高查询效率存在的一种机制,一张表的一个字段可以添加一个索引,当然多个字段联合起来也可以添加索引,索引相当于一本书的目录,是为了缩小扫描范围而存在的一种机制

Mysql在查询方面主要是两种方式

  1. 全表扫描
  2. 根据索引检索

Mysql数据库当中索引也是需要排序的,并且这个索引的排序和TreeSet数据结构相同,TreeSet(TreeMap)底层是一个自平衡的二叉树,在Mysql当中索引是一个B-Tree数据结构,遵循左小右大原则存放,采用中序遍历方式遍历数据

实现原理

在任何数据库当中主键上都会自动添加索引对象,另外在Mysql当中,一个字段上如果有unique约束的话,也会自动创建索引对象

在任何数据库当中,任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号

Mysql当中,索引是一个单独的对象,不同的存储引擎以不同的形式存在,在MyISAM存储引擎中,索引存储在一个.MYI文件中,在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的文件当中,在MEMORY存储引擎当中索引被存储在内存当中,但不管索引存储在哪里,索引在Mysql当中就都是以自动平二叉树的形式存在

graph TD
A(100 0x1111)-->B1(99 0x8888)
A-->B2(120 0x2222)
B1-->C1(88 0x9999)
C1-->D1(55 0x5555)
B2-->C2(101 0x6666)
B2-->C3(130 0x7777)

一般在这些情况下,我们才会考虑给字段添加索引

  • 数据量庞大(到底有多么庞大,这个需要进行测试,因为每一个硬件环境不同)
  • 该字段经常出现在where的后面,以条件的形式存在,也就是说这个字段总是被扫描
  • 该字段很少DML操作,因为DML之后索引需要重新排序

注:建议不要随意添加索引,因为索引也是需要维护的.太多的话反而会降低系统的性能,建议通过主键查询,通过unique约束的字段进行查询,效率较高

索引的创建于删除

例:给emp表中的ename创建索引

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.01 sec)
mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除emp表中emp_ename_index索引

mysql> drop index emp_ename_index on emp;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

想要查看某个字段是否添加了索引,需要用到命令explain

例:

mysql> explain select * from emp where ename='KING';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到扫描了14条记录,是全表扫描,且type=ALL,说明没有使用索引

我们给ename字段创建索引试试

mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from emp where ename='KING';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_ename_index | emp_ename_index | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

type=ref和查询条数只有1条,说明此字段有索引了

索引失效

失效的第一种情况:当模糊查询以%开头时,索引就会失效

例:

mysql> explain select * from emp where ename like '%T';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

原因是因为模糊查询匹配当中以%开头,所以尽量避免模糊查询时以%开头

失效的第二种情况:使用or的时候会失效,如果使用or那么要求or两边的条件字段都要有索引,才会进行索引,如果其中有一边字段没有索引,那么索引就会失效,所以建议尽量少用or

例:

mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER';
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | emp_ename_index | NULL | NULL    | NULL |   14 |    16.43 | Using where |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

但是使用union却不会失效

例:

mysql> explain select * from emp where ename = 'KING' union select * from emp where ename = 'MANAGER';
+----+--------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | emp        | NULL       | ref  | emp_ename_index | emp_ename_index | 43      | const |    1 |   100.00 | NULL            |
|  2 | UNION        | emp        | NULL       | ref  | emp_ename_index | emp_ename_index | 43      | 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.01 sec)

失效的第三种情况:使用复合索引的时候,没有使用左侧的字段查找,索引失效

复合索引:两个字段,或者更多的字段联合起来添加一个索引

例:

创建一个jobsal的复合索引

mysql> create index emp_job_index on emp(job,sal);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

当对job进行查找时,索引成功

mysql> explain select * from emp where job = 'MANAGER';
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_job_index | emp_job_index | 39      | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

当对sal进行查找时,索引失效

mysql> explain select * from emp where sal = 800;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

失效的第四种情况:在where当中索引字段进行了数学运算,索引失效

例:

sal字段添加索引

mysql> create index emp_sal_index on emp(sal);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

没有参加数学运算时,成功索引

mysql> explain select * from emp where sal = 800;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

索引字段进行了数学运算,索引失效

mysql> explain select * from emp where sal + 1 = 800;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

失效的第五种情况:在where当中索引字段使用了函数

例:

ename字段添加索引

mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

ename字段不使用函数,索引成功

mysql> explain select * from emp where ename = 'SMITH';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_ename_index | emp_ename_index | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

ename使用函数,索引失效

mysql> explain select * from emp where lower(ename) = 'smith';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Last modification:March 11th, 2021 at 05:30 pm