【MySQL】索引和事物

目录

♫索引

♪什么是索引

♪索引的数据结构

♪索引的使用

 ♫事务

♪什么是事务

♪事务的特性

♪事务的使用

♪事务的隔离级别

---

♫索引

♪什么是索引

索引是存储在磁盘上的一个数据结构，通过索引可以快速地定位到存储在磁盘上的数据。

索引在提高查询速度的同时，还提高了增删改（增删改的同时还需要额外调整索引）和空间的开销（构建索引需要额外的磁盘空间）。

♪索引的数据结构

索引的目的是为了加快查询速度，在我们已知的数据结构中，虽然哈希表查询的时间复杂度（O(1)）最低，但哈希表并不能查询指定范围的数据，故并不适合构成MySQL里的索引。而二叉搜索树虽然能查询指定范围，但由于是二叉，当数据量大时，树的高度就会比较高，元素之间的比较次数就多，读硬盘的次数也就会变多，从而速度就会降低，故也不是很适合构成MySQL里的索引。那MySQL里的索引底层到底是什么呢？其实它的底层是一种名叫B+树的数据结构。

B+树具有以下特点：

①.B+树是一颗N插搜索树，每个节点有N个key，N个key划分出N个区间，最后一个key为所在区间的最大值

②.父节点的key会在子节点中重复出现，并且是该子节点的最大值，这样叶子节点就包含所有数据的全集

③.叶子结点会以类似链表的形式互相连接

B+树由于是N叉搜索树，故高度会比二叉搜索树低，从而读取硬盘的次数就比较低；B+树的所有的查询都是落在叶子节点上，中间比较次数差不多，故查询操作比较均衡；由于B+数的叶子节点包含所有数据，故只需在叶子节点存放具体数据，非叶子节点只需存放索引值即可，这样极大节省了内存空间，就有可能将非叶子节点放进内存中缓存，进一步降低硬盘IO的次数。

♪索引的使用

♩查看索引

创建主键约束（ PRIMARY KEY ）、唯一约束（ UNIQUE ）、外键约束（ FOREIGN KEY ）时，会自动创建对应列的索引：

语法： show index from 表名;

mysql> create table student(id int primary key auto_increment, name varchar(20));
Query OK, 0 rows affected (0.06 sec)

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

♩创建索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引：

语法： create index 索引名 on 表名(列名);

mysql> create index idx_student_name on student(name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from student;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY          |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | idx_student_name |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

♩删除索引

语法： drop index 索引名 on 表名;

mysql> drop index idx_student_name on student;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

注：

①.创建索引最好是在表创建之初执行，如果对已有大量数据的表创建索引会吃掉大量磁盘IO，花费很长时间

②.可以使用explain关键字显示出查询过程中具体的索引使用情况

③.每个索引列都有对应的B+树，对于非主键列的索引会先查询该索引列的B+树，再根据查询结果查询主键列的B+树

 ♫事务

♪什么是事务

事务是指在数据库中进行的一组相关的操作，被当作一个单独的工作单元处理，要么全部执行，要么全部回滚。

♪事务的特性

事务具有以下四个特性：

♩ Atomicity（原子性）：一个事务中的所有操作要么全部成功，要么全部失败，不能只执行其中部分操作。

♩ Consistency（一致性）：事务执行前后数据库的状态必须保持一致，也就是满足数据库的约束和规则。

♩ Isolation（隔离性）：当多个事务同时执行时，每个事务都应该被视为独立的，不能相互影响。每个事务必须独立执行，并发执行时不能出现干扰。

♩ Durability（持久性）：事务完成后，其所作的修改应该永久保存在数据库中，即使系统出现故障也不应该丢失。

♪事务的使用

MySQL中开启事务的语句为start transaction，提交事务的语句为commit，回滚事务的语句为rollback。事务的使用可以保证数据的一致性和完整性，避免数据损坏和丢失。

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

mysql> insert into student values(1,"张三");
Query OK, 1 row affected (0.02 sec)

mysql> update student set name="李四" where name="张三";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

♪事务的隔离级别

在了解事务的隔离级别前，我们先来分析个事件：

事件①.博主的同学在旁边看博主写博客，当同学看过博主前面的内容后走了后，博主突然觉得前面写的不好，回过头去把前面的内容改了，博主提交后的博客是与博主的同学看的不一样，这就是脏读问题（事务A读取了事务B中尚未提交的数据）；为了避免这样的情况发生，博主就规定博主写的时候博主的同学不能看，得等博主写好了再观看（给写操作加锁，避免了脏读问题）；

事件②.博主的同学在看博主提交后的博客，可博主觉得之前提交的博客有点问题，于是就修改了博客后再次提交，这就导致博主的同学再往回看之前看过的内容时发现内容不同了，这就是不可重复读（一个事务范围内多次查询却返回了不同的数据值），这样子在博主的同学看博客的时候博主也不能对该博客进行修改（对读操作加锁）；

事件③.博主的同学在读博客时，博主虽然不能对这篇博客进行修改，但是博主可以提交新写的博客啊，这样子博主的同学在读的时候就会发现未读的博客数量变多了，这就是幻读问题（事务A多次读取构成中，事务B对数据进行了新增操作），要避免幻读问题当博主的同学在读博客的时候，博主就不能动博客了。

mysql中给我们提供了以下四种事务的隔离级别：

read uncommitted:存在脏读，不可重复读，幻读问题

read committed：对写操作进行加锁，避免了脏读问题

repeatable read（默认挡位）：对读和写操作加锁，避免了脏读，不可重复读问题

serializable：严格执行串行，避免了脏读，不可重复读，幻读问题