如何搞懂 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等）

image

4) fulltext 全文索引

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

5) 组合索引

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

3. 索引的结构

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

4. 测试数据

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

image

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串行，而不并发

image.png

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 202,406评论 5赞 475
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 84,976评论 2赞 379
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 149,302评论 0赞 335
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,366评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,372评论 5赞 363
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,457评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,872评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,521评论 0赞 256
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,717评论 1赞 295
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,523评论 2赞 319
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,590评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,299评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,859评论 3赞 306
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,883评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,127评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,760评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,290评论 2赞 342

如何搞懂 MySQL InnoDB 索引与事务

5. 最左前缀匹配原则

推荐阅读更多精彩内容