【MySQL初阶】索引

1. 索引

1.1 索引基本概念

1.1.1 索引介绍

索引(index)：是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或者多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。（具体细节在MySQL进阶章节详细讨论）

在数据库中，使用条件查询时需要遍历表，相当于O(N)的时间复杂度，但是数据库中的数据是存储在硬盘上的，而数据结构中的O(N)是基于内存的角度谈论的，所以硬盘I/O操作遍历操作更加缓慢！

而索引的目的就是提高 “查询效率” ，考虑有一本书，如何能够快速的找到某个章节的页码，这个时候就需要借助目录了 ，而索引的功能就类似于目录，能够提高查询效率。
索引特点：

1. 索引能够加快查询效率
2. 索引自身也是一定的数据结构组成，也要占据相应的存储空间
3. 当我们进行新增、删除、修改操作时，不仅需要修改数据，同时也要对索引进行维护管理

总结：因此索引适用的场景需要具备如下两大条件：1、对于存储空间要求不高（存储空间充裕）；2、应用场景以查询居多，增加、修改、删除等频率不高

1.1.2 索引相关SQL操作

1.1.2.1 查看索引

语法格式：show index from 表名;

create database blog_mysql_index;
create table t_user(username varchar(20) primary key,`password` varchar(20) not null);
show index from t_user;
1
2
3

结果：

从中我们可以发现表中具有哪些索引，以及哪些列上加上了索引，并且我们可以发现某些约束例如 primary key、unique 等频繁用于查询操作，所以会自动加上一些索引

1.1.2.2 新增索引

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

create index index_password on t_user(`password`);
show index from t_user;
1
2

结果：

从中我们可以发现列password也加上了索引。需要注意的是，创建索引这个操作是一个危险操作！如果表中数据量比较少不会产生问题，但是如果数据量非常庞大，这个时候添加索引就会导致数据结构重新组织，此时就会触发大量的硬盘I/O，数据库服务器很容易就宕机了！

1.1.2.3 删除索引

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

drop index index_password on t_user;
show index from t_user;
1
2

结果：

同理，删除索引也是一个十分危险的操作！所以尽量在建表之初就确定好哪些字段需要加上索引，而不要等到生产环境上线数据量庞大之后再考虑索引的问题。

但是实际情况由于需求的不断变化，因此很难项目之初就确定好，所以事实上程序员可以通过"曲线救国"的方式处理索引问题，那就是使用新的机器部署数据库，然后建表建索引，逐步将原环境上的数据拷贝到新数据库中，最后用新的机器代替旧的机器就可以了！

1.2 索引底层数据结构

我们需要关心索引底层数据结构的实现，但是我们此处也是简单介绍（详细内容在数据库进阶或者高阶数据结构部分介绍）

前面提到索引本身实际上就是通过额外的数据结构来对表中的数据进行重新组织，那么我们需要考虑使用什么样的数据结构才能在时间、空间上占据优势呢？回忆下我们之前学习的常见数据结构有顺序表、链表、栈、队列、二叉树、哈希表，其中顺序表、链表、栈、队列显然是不适合用于"查找"功能的！而哈希表通过一定时间扩容是可以实现平均时间复杂度为O(1)的，但是哈希表的原理是通过哈希函数计算得出存储下标进行查找，而数据库中很多操作并不是单纯比较相等，例如between and范围比较就无法使用哈希表！所以此处最适合的还是平衡二叉树这样的数据结构（时间复杂度为O(logN)）

1.2.1 Java中的ArrayList与LinkedList的区别（常见面试题）

个人整理：

• 针对ArrayList来说，它的底层实现是数组，具有随机访问特性，随机访问的时间复杂度是O(1)，ArrayList尾插/尾删较快（时间复杂度都是O(1)），而在头插/头删/中间节点插入/中间节点删除的平均时间复杂度是O(N)
• 针对LinkedList来说，它的底层实现是基于链表的，不支持随机访问，进行头插/头删/尾插/尾删的时间复杂度是O(1)，中间节点插入/删除的时间复杂度是O(N)
• LinkedList相较于ArrayList的唯一优势就是可以支持头插头删，因此可以用来轻松实现队列这样的数据结构，除此以外，其他方面大多数都是ArrayList占据优势

常见错误说法：

1. 使用LinkedList占用空间比ArrayList少，因为ArrayList需要预分配好空间大小，容易浪费

   LinkedList中的节点不止存放数据，也需要其他空间用于存放指针域，所以具体的存储空间大小不一定！

2. LinkedList的遍历速度快于ArrayList

   这也是错误的，链表访问下一个元素需要通过next指针引用，相比于ArrayList的i++操作要多一次访存操作，而i++通常会优化到寄存器

3. LinkedList的中间节点插入的时间复杂度是O(1)

   正确答案应该是O(N)，这是Java的接口设计失误导致的！事实上C++通过迭代器的方式可以实现O(1)时间复杂度，但是Java的插入就是通过遍历找节点的方式

1.2.2 B+树

数据库的索引使用了B+树这样的数据结构，可以说B+树像是专门为数据库这样的场景量身定制的数据结构了，要想理解B+树，我们需要先了解它的前身——B树（也被写做B-树）

1.2.2.1 B-树

B-树：是一颗N叉搜索树（有序），是对于二叉搜索树的扩展，一个节点可以包含N个key值，这N个值又划分出了N+1个区间，如下图所示：

因此相较于二叉搜索树来说，相同高度的B树可以表示的数据个数更多了，使用B树来查询的时候，如果单论比较次数确定比二叉搜索树多很多，但是关键在于同一个节点的多个key值可以通过一次硬盘IO读取，即使总的比较次数增加了，但是硬盘IO次数少了，显著提高了效率！

1.2.2.2 B+树

B+树：B+树就是在B-树的基础上又做出了改进，同样也是N叉搜索树，每个节点可以包含多个key值，N个key可以划分出N个区间，B+树如图所示：

B+树的特点：

1. N叉搜索树，一个节点包含N个key值，N个key值划分出N个区间
2. 每个节点的N个key中，设定一个最大值（或最小值）
3. 每个节点的N个key值都会在子树中重复出现
4. 把叶子节点通过 链式结构 相连

B+树的好处：

1. 针对范围查询比较有利，例如查询21 <= age <= 37的所有人群，只需要在B+树中找到叶子节点中的21然后沿着链表往后一直遍历到37即可，得益于该链式结构，就省去了对树进行回溯查找的麻烦了
2. 查询时间以及IO次数更加稳定，查询所有元素都需要经过根节点直到叶子节点的过程，所以途经的硬盘IO次数是固定的，更加稳定可预测！
3. 由于叶子节点是数据的全集，因此非叶子节点为了节省内存空间可以只存储对应的key值缓存而不存储具体元素内容，大幅度减少了硬盘IO的次数

2. 事务

2.1 事务的相关概念

事务：事务是一组逻辑操作，这些操作要么全都执行成功，要么全都不执行，事务可以在不同环境中使用，在数据库中就称之为"数据库事务"

2.2 事务基本特性（经典面试题）

事务具有以下四大特性（简称ACID）：

1. 原子性：通过事务将多个操作打包在一起，要么全都执行，要么都不执行
2. 一致性：相当于原子性的延申，数据库状态总是从一个一致性状态向另一个一致性状态转变的，不可能存在例如转账业务中钱凭空消失这样不科学的情况
3. 持久性：事务的任何修改操作，都是会持久化存在的（存入硬盘）
4. 隔离性：多个事务并发执行时，可能会引入一些问题，通过隔离性以及隔离级别的设置可以有效权衡其中的问题

由于数据库是一个基于客户端/服务器（C/S架构）的程序，因此如果多个客户端向服务器提交事务请求，就会出现事务并发执行的情况，如果处理的是同一个数据，就可能会引入问题，下面就来讨论几种常见的问题：
典型bug1：脏读问题
考虑这样一个场景，当前有存在两个事务：事务t1，事务t2，其中事务t1对成绩表中姓名为张三的同学成绩修改为59，此时事务t2并发执行读取同一张成绩表中名为张三的成绩为59，但是此时事务t1发现有一个分数忘记统计了，因此再次修改张三同学的成绩为60，那么现在问题来了，如果事务t2需要根据成绩登记是否挂科，那就出大问题了！即存在两个事务，事务t1修改数据，事务t2读取数据，可能事务t1后续仍需要再次修改数据，t2此时读取的就是一个"脏数据"，这就被称为 “脏读问题” ，而解决脏读问题就要对"写操作"加锁，即事务t1在修改数据的过程中，其他事务无法进行读取操作
典型bug2：不可重复读问题
此时我们规定对"写操作"加锁，因此不会出现脏读问题，再次考虑这样一个场景，事务t1对张三的成绩修改为59（此时其他事务无法读取或修改），当事务t1执行完毕后，事务t2读取张三成绩为59，由于t2事务为读取操作，其他事务可以并发执行，此时事务t3修改张三成绩为60，此时问题又出现了！这样的情况就是**“不可重复读”** ，解决"不可重复读"问题的关键就在于给"读操作"加锁，当事务t2读取数据时，其他事务无法修改同样的数据
典型bug3：幻读问题
此时我们规定对"写操作"加锁、对"读操作"加锁，因此不会出现"脏读"、"不可重复读"问题，再次考虑这样一个场景，事务t1读取张三成绩时，事务t2新增了一个学生的成绩，此时由于修改的不是同一个数据所以不会触发"不可重复读"这样的问题，但是对于事务t2来说，整体的数据集合仍然发生了变化，这种情况就叫做 “幻读” ，解决"幻读"问题就需要让事务完全"串行化"执行，即不采取并发执行操作，严格按照执行事务t1，然后再执行事务t2，最后执行事务t3的顺序
总结：上述这些情况是否真的算是"bug"是要针对不同的业务场景进行定义的，归根到底需要根据实际情况判断是追求数据的可靠性还是效率！MySQL针对不同情况为我们提供了不同的隔离级别：

1. read uncommited：读未提交，并行程度最高、隔离程度最低、数据最不可靠、效率最高，此时可能出现脏读+幻读+不可重复读问题
2. read commited：读已提交，相当于给"写操作"加锁，此时并行程度降低、隔离级别变高、数据更加可靠、效率降低一些，可能会出现不可重复读+幻读问题
3. repeatable read：可重复读，相当于给"读操作"加锁，此时并行程度进一步降低、隔离级别进一步升高、数据可靠性进一步提升、效率进一步降低，可能会出现幻读问题
4. serializable：串行化执行，让所有的事务串行顺序执行，并行程度最低、隔离级别最高、数据最可靠、效率最低

其中MySQL的默认级别为repeatable read可重复读

2.3 事务的基本使用

事务在数据库中可以通过如下几个命令进行操作：

1. 开启事务

   语法格式：start transaction;

2. 事务回滚

   语法格式：rollback;

3. 事务提交

   语法格式：commit;

代码示例：

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

mysql> insert into t_user values ('qwe','123');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user values ('abc','456');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----------+----------+
| username | password |
+----------+----------+
| abc      | 456      |
| qwe      | 123      |
+----------+----------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t_user;
Empty set (0.00 sec)

mysql> insert into t_user values ('qwe','123');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user values ('abc','456');
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from t_user;
+----------+----------+
| username | password |
+----------+----------+
| abc      | 456      |
| qwe      | 123      |
+----------+----------+
2 rows in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

总结：事务在数据库中是一大核心机制，是用来保证数据可靠性的，但是我们往往不会在数据库命令行中开启事务，往往是搭配某一门编程语言在业务中使用的，例如Java的Spring框架就封装了事务管理，当程序抛出异常时可以考虑回滚机制来确保数据的可靠性！事务回滚机制实现方式既跟undo log、redo log有关，也跟背后的存储引擎有关，会在后续数据库进阶章节进行讨论！