Mysql(Innodb) 索引、事务、查询优化

最新推荐文章于 2022-05-18 17:52:40 发布
最新推荐文章于 2022-05-18 17:52:40 发布
阅读量1.3k 收藏 13
点赞数 5
分类专栏： 数据库/消息/其他 文章标签： mysql mysql优化 索引 innodb 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/RaulHapply/article/details/114873336
版权

Mysql 索引、事务、查询优化

一、Mysql 索引

1. 索引的定义

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
索引的一个主要目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

2. 索引的类型

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

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

3) index 普通索引
这是最基本的索引，它没有任何限制。

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串行，而不并发
隔离级别脏读取不可重复读幻想读
读未提交可能可能可能
读已提交不可能可能可能
可重复读不可能不可能InnoDB 不可能
串行化不可能不可能不可能
码农蝈蝈
关注
专栏目录
5. MySQL InnoDB的索引与优化
萝卜头柯克船长的博客
09-17 1110
系列文章目录 提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加 例如：第一章 Python 机器学习入门之pandas的使用 提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录系列文章目录1. 索引与表的维护2. 使用自增主键3. 使用覆盖索引3.1 回表3.2 利用覆盖索引减少回表3.3 InnoDB对回表的优化3.3.1 MRR（Mutil-Range Read）3.3.2 索引下推（Index Condition Pushdown，ICP）4. 使用联合索引5
谈一谈mysql innoDB索引与优化
qq_44793993的博客
08-01 831
文章目录理解索引索引的几种实现实现对比B树和B+树对比mysql innoDB的索引索引的物理存储结构聚簇索引和辅助索引回表索引覆盖最左前缀索引失效索引的选择change buffer索引与约束自增索引设计原则 理解索引 索引的几种实现 实现对比 B树和B+树对比 mysql innoDB的索引 索引的物理存储结构 聚簇索引和辅助索引 回表 索引覆盖 最左前缀 索引失效 索引的选择 change buffer 索引与约束 自增 索引设计原则 ...
MySQL优化总结 (索引 + 存储结构 + 事务 + 锁机制)
Pole丶逐
06-04 221
MySQL优化 1. 分层 连接层: 提供与客户端连接的服务 服务层: 提供各种用户使用接口 (CRUD) Sql优化器 会自动优化语句,写的语句A,执行的是优化B 引擎层: 提供了各种存储数据的方式 InnoDB: 事务优先,行锁 MySQL默认存储引擎 存储数据时,主键自动排序,聚集 MyISAM: 性能优先,表锁 按照写入方式进行呢存储,堆表 存储层: 存储数据 2. 索引 帮助MySQL高效获取数据的数据结构(默认: B树) 优势: 提高查询的
Mysql数据库中的索引
while false
03-24 1万+
1.什么是索引 索引是对数据库表中的一列或者多列的值进行排序的一种数据结构，如果把数据库中的表比作一本书，索引就是这本书的目录，通过目录可以快速查找到书中指定内容的位置 索引也是一张表，该表中存储着索引的值和这个值的数据所在行的物理地址，使用索引后可以不用扫描全表来定位某行的数据，而是通过索引表来找到该行数据对应的物理地址 2.索引的优缺点 优点： 建立索引的列可以保证行的唯一性，生成唯...
一问三不知之数据库引擎Innodb是如何实现事务处理的
lumingzhu111的博客
08-26 258
一问三不知之数据库引擎Innodb是如何实现事务处理的 Innodb实现事务处理主要用到 Buffer Pool（缓冲器池），Log Buffer，Redo Buffer(用于事务执行)，Undo Buffer（用于事务回滚） 以一个update事务为例 1：Innodb接收到事务处理请求后，根据条件找到该数据所在的页 2：创建一个Buffer Pool，并将该页数据缓存到Buffer Pool当中 3：执行sql语句，修改在Buffer Pool中的数据 4：针对Update语句创建一个Redo Log对
从理论到实践，Mysql查询优化剖析（联表查询）
daxues_的博客
03-05 492
转自：从理论到实践，Mysql查询优化剖析 1.关于Mysql 一旦涉及到查询优化，就离不开索引的应用，本文选取mysql常用的引擎InnoDB作为研究对象，针对InnoDB引擎利用的索引结构B+树做个简单说明。 1.1 InnoDB的B+树 假设我们创建表Student，主键为id： CREATE TABLE `Student` ( `id` int(16) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KE
mysql查询和优化_MySQL查询优化
weixin_35662493的博客
01-19 156
Mysql存储引擎最常使用的2种存储引擎:Myisam是Mysql的默认存储引擎，当create创建新表时，未指定新表的存储引擎时，默认使用Myisam。每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm(存储表定义)、.MYD (MYData，存储数据)、.MYI (MYIndex，存储索引)。数据文件和索引文件可以放置在不同的目录，获得更快的速度。InnoDB存储...
MySQL Innodb 索引原理详解
02-28
在深入探讨MySQL Innodb索引之前，我们先了解几种基本的树形数据结构，包括二叉搜索树、B树、B+树以及B*树。 ##### 1.1 搜索二叉树（Binary Search Tree） 搜索二叉树是一种特殊的二叉树，每个节点至多有两个子...
Mysql InnoDB引擎的索引与存储结构详解
12-16
MySQL默认的存储引擎是MyISAM，其他常用的就是InnoDB，另外还有MERGE、MEMORY(HEAP)等。 主要的几个存储引擎 MyISAM管理非事务表，提供高速存储和检索，以及全文搜索能力。 MyISAM是Mysql的默认存储引擎。当create...
MySQL优化之InnoDB优化
09-09
总而言之，MySQL的InnoDB优化是一个多维度的过程，涉及到缓存、索引、事务、日志等多个层面。通过对这些核心组件的调优，可以显著提高MySQL在大数据量下的处理能力和响应速度，从而满足高并发、高性能的应用需求。在...
MySQL（一）、InnoDB索引原理及优化
王义凯 的博客
08-11 1144
MySQL的索引可以大大提高MySQL的检索速度，在大厂的面试中也多会问关于索引和事务相关的问题，本篇我们来详细介绍MySQL中的索引及其使用和优化。
浅谈innodb的锁与事物
li775085737的博客
08-03 287
序言 本篇博客，想简单地阐述对innodb的锁与事物的理解，不喜勿喷。 曾经向某位同学解释，所有数据库的通用原理时，他问我：“这他妈和file system”有何区别？下面列了一些，但不局限于此。 ...
彻底了解InnoDB的各种读这篇文章就够了
liushangzaibeijing的博客
05-18 2万+
本篇文件介绍了事务实现的基础（MVVC模式）：插入数据时候undo log链表变化和readView快照，并基于上述概念深入分析了脏读、不可重复读，幻读的根本原因以及幻读的解决方式间隙锁，深度好文，值得收藏学习。
mysql innodb事务的实现
oBuShanQing的博客
05-04 1663
一.什么是事务 首先简单说下什么是事务，事务就是具有原子性，一致性，隔离性，持久性的一组数据库操作。 如下图的sql语句即为事务。mysql默认自动提交事务，所以一条语句就是一个事务。也可以手动写begin和commit，在这之间的sql语句同属于一个事务。 二.原子性的实现 原子性比较容易理解，同一个事务的所有操作，要么都成功执行，要么都不执行，如下图。 要实...
MySQL查询优化
JavaEdge全是干货的技术号
06-02 4144
1 SQL优化 show status 了解各种SQL的执行效率： # 查看本session的sql执行效率 show status like 'Com_%'; # 查看全局的统计结果 SHOW GLOBAL STATUS LIKE 'Com_%' # 查看服务器的状态 show global status; 结果： Com_select:执行select操作的次数，一次查询只累加1 Com_insert:执行insert操作的次数，对于批量插入的insert操作，只累加一次 Com_updat
MySQL进阶(二)——索引优化分析(join查询，索引简介)
止步听风的博客
03-02 1570
本篇文章主要是对MySQL学习时的一些总结，作为学习笔记记录。 性能下降原因 对于某些sql语句来说，会导致执行时间长，等待时间长的后果，从而造成整个sql语句的性能下降。导致sql性能下降的原因主要有： 查询数据过多：能不能拆分，降低过滤条件 关联了太多的表，太多join：使用之前尽量先过滤 没有利用到索引(单值，复合) 索引是针对列建立的，但并不可能对所有的数据列都建立索引 同时索引也并不是越多越好，数据更新的同时，索引也会进行调整，因此过多的索引会降低性能 而mysql也不会使用到
MySQL性能优化 - 别再只会说加索引了
m0_52789121的博客
05-15 1154
?? 优质资源分享 ?? 学习路线指引（点击解锁） 知识定位 人群定位 ?? Python实战微信订餐小程序 ?? 进阶级 本课程是python flask+微信小程序的完美结合，从项目搭建到腾讯云部署上线，打造一个全栈订餐系统。 ??Python量化交易实战?? 入门级 手把手带你打造一个易扩展、更安全、效率更高的量化交易系统 MySQL性能优化 MySQL性能优化我们可以从以下四个维度考虑：硬件升级、系统配置、表结构设计、SQL语句和索引。 从成本上来说：硬件升级>系统配置>表结构设计&g
mysql 查询正在执行的事务以及等待锁 常用的sql语句
热门推荐
菜菜
05-16 7万+
mysql 查询正在执行的事务以及等待锁 常用的sql语句
mysql 事务、索引、锁、分区/分表、sql优化、查询优化
あずにゃん梓喵的博客
05-12 4264
mysql 安装、sql语法 一条sql语句完成MySQL去重留一 mysql 事务、索引、锁、分区/分表、sql优化、查询优化 mysql 安装配置使用、sql语法 MySQL主键、创建索引、UNION 和 UNION ALL mysql 索引类型以及创建 一、简介 MySQL目前主要有以下几种索引类型： 1.普通索引 2.唯一索引 3.主键索引 4.组合索引 5.全文索引 二、...
深入解析MySQL InnoDB索引机制
本文将深入解析InnoDB索引的工作原理，以及与MyISAM引擎的对比。 1. 索引概述 索引是数据库管理系统中用于提高查询速度的数据结构。InnoDB支持多种类型的索引，包括主键索引（Primary Key）、唯一索引（Unique ...
评论 7
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值