关于mysql:如何搞懂-MySQL-InnoDB-索引与事务

48次阅读

共计 9465 个字符，预计需要花费 24 分钟才能阅读完成。

一、Mysql 索引

  1. 索引的定义

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

索引的一个次要目标就是放慢检索表中数据，亦即能帮助信息搜寻者尽快的找到合乎限度条件的记录 ID 的辅助数据结构。

  1. 索引的类型

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) 组合索引

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

  1. 索引的构造

mysql 中广泛应用 B +Tree 做索引，但在实现上又依据聚簇索引和非聚簇索引而不同，在这儿不做具体介绍，后续篇章中再开展具体阐明。

  1. 测试数据

咱们当初有一个订单表，表构造如下：

  1. 最左前缀匹配准则
    十分重要的准则，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

  1. 抉择区分度高的列作为索引

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

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

比方表中曾经有 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 |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

能够看到第一条查问语句应用了索引而第二条并没有。

  1. 查问 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）：事务处理完结后，对数据的批改就是永恒的；

  1. 隔离级别

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

  1. Read Uncommitted

事物 A 和事物 B，事物 A 未提交的数据，事物 B 能够读取到
这里读取到的数据叫做“脏数据”
这种隔离级别最低，这种级别个别是在实践上存在，数据库隔离级别个别都高于该级别

  1. Read Committed

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

  1. Repeatable Read

事务 A 和事务 B，事务 A 提交之后的数据，事务 B 读取不到
事务 B 是可反复读取数据
这种隔离级别高于读已提交
换句话说，对方提交之后的数据，我还是读取不到
这种隔离级别能够防止“不可反复读取”，达到可反复读取
比方 1 点和 2 点读到数据是同一个
MySQL 默认级别
尽管能够达到可反复读取，然而会导致“幻像读”

  1. Serializable

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

正文完
mysql
发表至： mysql
2021-05-31
 0
关于mysql:MySQL数据库的表中-NULL-和-空值-到底有什么区别呢
关于mysql:万答11MySQL中char与varchar有什么区别
关于mysql:ERROR-2002-HY000-Cant-connect-to-local-MySQL-server
关于mysql:Oracle-和-Mysql-的索引在Null字段上处理的异同