如何搞懂 MySQL InnoDB 索引与事务

一、Mysql 索引

1. 索引的定义

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

索引的一个主要目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

2. 索引的类型

1) primary key 主键索引

它是一种特殊的唯一索引，不允许有空值。一张表只能有一个主键

2) unique 唯一索引

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

3) index 普通索引

这是最基本的索引，它没有任何限制。

【文章福利】需要C/C++ Linux服务器架构师学习资料加群1106747042（资料包括C/C++，Linux，golang技术，Nginx，ZeroMQ，MySQL，Redis，fastdfs，MongoDB，ZK，流媒体，CDN，P2P，K8S，Docker，TCP/IP，协程，DPDK，ffmpeg等）
4) fulltext 全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

5) 组合索引

组合索引，即一个索引包含多个列。

3. 索引的结构

mysql中普遍使用B+Tree做索引，但在实现上又根据聚簇索引和非聚簇索引而不同，在这儿不做详细介绍，后续篇章中再展开详细说明。

4. 测试数据

我们现在有一个订单表，表结构如下：

5. 最左前缀匹配原则
   非常重要的原则，mysql会从左向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
   创建一个索引

CREATE INDEX IDX_T_1 USING BTREE ON xa87_v2.t_xa87_order_info (store,delivery_msg,food_fee,delivery_fee);

我们执行一个sql

explain select * from t_xa87_order_info where delivery_msg='高玥静,17836031' and food_fee>1000;
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_xa87_order_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 62017 |     3.33 | Using where |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

在where中并没有store这个字段，所以不会使用IDX_T_1这个索引。

我们把store这个查询条件加上，看一下效果：

explain select * from t_xa87_order_info where store='f9fd2705ad1d740a4bef42833b487cea' and delivery_msg='高玥静,17836031' and food_fee=1000;
+----+-------------+-------------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table             | partitions | type | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_xa87_order_info | NULL       | ref  | IDX_T_1       | IDX_T_1 | 267     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+

explain select * from t_xa87_order_info where store='f9fd2705ad1d740a4bef42833b487cea' and delivery_msg like '%17836031' and food_fee=1000;
+----+-------------+-------------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table             | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_xa87_order_info | NULL       | ref  | IDX_T_1       | IDX_T_1 | 131     | const |  633 |     1.11 | Using index condition |
+----+-------------+-------------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+

上面两个查询都用了IDX_T_1这个索引， 第一个效率更高， 因为第二个查询在delivery_msg条件上使用了like所以不再继续匹配food_fee

6. 选择区分度高的列作为索引

所谓区分度高就是指相同的值少。比如性别这个字段，只有男、女两个值，区分度很低，就不适合作为索引。

7. 拓展索引，尽量不要新建索引

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

二、查询优化

1. 不让索引失效

1) Like的参数不以通配符开头

explain select * from t_xa87_order_info where order_no like '2022%';
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_xa87_order_info | NULL       | range | PRIMARY       | PRIMARY | 130     | NULL |    1 |   100.00 | Using where |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

explain select * from t_xa87_order_info where order_no like '%2022';
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_xa87_order_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 62017 |    11.11 | Using where |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

2) Where 条件要符合最左前缀匹配原则
上文中已经有过说明不再赘述

3) 不要使用!=和<>

explain select * from t_xa87_order_info where created_date<>'2020-08-24';
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_xa87_order_info | NULL       | ALL  | IDX_T_2       | NULL | NULL    | NULL | 62017 |    50.00 | Using where |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

4) 不要和null进行判断

explain select * from t_xa87_order_info where created_date is not null;
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_xa87_order_info | NULL       | ALL  | IDX_T_2       | NULL | NULL    | NULL | 62017 |    90.00 | Using where |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

5) 不要使用or

6) =、in 中的列可以乱序

explain select * from t_xa87_order_info where delivery_msg='高玥静,17836031' and food_fee=1000 and store='f9fd2705ad1d740a4bef42833b487cea';
+----+-------------+-------------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table             | partitions | type | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_xa87_order_info | NULL       | ref  | IDX_T_1       | IDX_T_1 | 267     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+

我们把查询条件中的store放到最后，发现依然可以匹配到IDX_T_1这个索引，且效率和store在最前面的时候是一样的。

7) 索引列不参与计算
在created_date上创建一个索引

CREATE INDEX IDX_T_2 USING BTREE ON xa87_v2.t_xa87_order_info (created_date);

explain select * from t_xa87_order_info where created_date='2020-08-24';
+----+-------------+-------------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table             | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_xa87_order_info | NULL       | ref  | IDX_T_2       | IDX_T_2 | 3       | const |  123 |   100.00 | NULL  |
+----+-------------+-------------------+------------+------+---------------+---------+---------+-------+------+----------+-------+

explain select * from t_xa87_order_info where DATE_FORMAT(created_date,'%Y-%m-%d')='2020-08-24';
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_xa87_order_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 62017 |   100.00 | Using where |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

可以看到第一条查询语句使用了索引而第二条并没有。

2. 查询sql优化

1) 避免select '*'

在sql解析的过程中，会将’*’ 依次转换成所有的列名，这个是通过查询数据字典完成的，这意味着将耗费更多的时间。所以，应该养成一个需要什么就取什么的好习惯。

2) order by 优化

重写order by语句以使用索引；

为所使用的列建立另外一个索引；

绝对避免在order by子句中使用表达式；

3) group by 优化

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉

4) exists 代替 in

5) 使用 varchar/nvarchar 代替 char/nchar

6) 能用DISTINCT的就不用GROUP BY

7) 能用UNION ALL就不要用UNION

UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源。

8) 在Join表的时候使用相当类型的例，并将其索引

如果有很多Join 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。

这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）

三、事务（Transaction）

1. 事务的特性

原子性（A）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样；

一致性（C）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作；

隔离性（I）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致；

持久性（D）：事务处理结束后，对数据的修改就是永久的；

2. 隔离级别

读未提交：read uncommitted
读已提交：read committed
可重复读：repeatable read
串行化：serializable

1. Read Uncommitted

事物A和事物B，事物A未提交的数据，事物B可以读取到
这里读取到的数据叫做“脏数据”
这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

2. Read Committed

事物A和事物B，事物A提交的数据，事物B才能读取到
这种隔离级别高于读未提交
换句话说，对方事物提交之后的数据，我当前事物才能读取到
这种级别可以避免“脏数据”
这种隔离级别会导致“不可重复读取”
Oracle默认隔离级别

3. Repeatable Read

事务A和事务B，事务A提交之后的数据，事务B读取不到
事务B是可重复读取数据
这种隔离级别高于读已提交
换句话说，对方提交之后的数据，我还是读取不到
这种隔离级别可以避免“不可重复读取”，达到可重复读取
比如1点和2点读到数据是同一个
MySQL默认级别
虽然可以达到可重复读取，但是会导致“幻像读”

4. Serializable

事务A和事务B，事务A在操作数据库时，事务B只能排队等待
这种隔离级别很少使用，吞吐量太低，用户体验差
这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发