首页 › MySQL论坛 › MySQL索引、存储引擎和SQL优化深入解析

老缪 發表於 2025-12-19 16:20:57

MySQL索引、存储引擎和SQL优化深入解析

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">存储引擎</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1. 在创建表时，指定存储引擎</a></li><li><a href="#_lab2_0_1">2. 查看当前数据库支持的存储引擎</a></li><li><a href="#_lab2_0_2">3. innoDB存储引擎</a></li><li><a href="#_lab2_0_3">4. MyISAM存储引擎</a></li><li><a href="#_lab2_0_4">5. Memory存储引擎</a></li><li><a href="#_lab2_0_5">6. 区别</a></li><li><a href="#_lab2_0_6">7. 存储引擎的选择</a></li></ul><li><a href="#_label1">索引基础</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_7">1. 优缺点</a></li><li><a href="#_lab2_1_8">2. 索引结构</a></li><li><a href="#_lab2_1_9">3. 索引分类</a></li><li><a href="#_lab2_1_10">4. innoDB索引分类</a></li><li><a href="#_lab2_1_11">5. 索引语法</a></li><ul class="third_class_ul"><li><a href="#_label3_1_11_0">1. 创建索引</a></li><li><a href="#_label3_1_11_1">2.查看索引</a></li><li><a href="#_label3_1_11_2">3.删除索引</a></li></ul></ul><li><a href="#_label2">SQL优化</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_12">SQL查询性能下降的原因</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_2_13">1. 慢查询日志</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_2_14">2. 执行计划</a></li><ul class="third_class_ul"><li><a href="#_label3_2_14_3">id列</a></li><li><a href="#_label3_2_14_4">select_type</a></li><li><a href="#_label3_2_14_5">type</a></li><li><a href="#_label3_2_14_6">extra</a></li></ul></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>存储引擎</h2>
<p>存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是<strong>基于表</strong>的，而不是基于库的，所以存储引擎也可被称为表类型。</p>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>1. 在创建表时，指定存储引擎</h3>
<div class="jb51code"><pre class="brush:sql;">create table 表名(
        ...
)engine = innodb
#在最后指定</pre></div>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>2. 查看当前数据库支持的存储引擎</h3>
<div class="jb51code"><pre class="brush:sql;">show engines;
</pre></div>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>3. innoDB存储引擎</h3>
<p>innoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL 5.5之后，innoDB是默认的MySQL存储引擎，在此之前的默认引擎是MyISAM。</p>
<p>它具有以下几个特点：</p>
<ul><li>DML操作遵循ACID模型，支持<strong>事务</strong>。</li><li><strong>行级锁</strong>，提高并发访问性能。</li><li>支持<strong>外键</strong>FOREIGN KEY约束，保证数据的完整性和正确性。</li></ul>
<p>innoDB引擎的每张表都会有一个这样的表空间文件（xxx.ibd），存储该表的表结构（frm、sdi）、数据和索引。</p>
<p>参数：innodb_file_per_table 该参数打开表示每一张表对应一个表空间文件而不是共享一个。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121916200789.png" /></p>
<p class="maodian"><a name="_lab2_0_3"></a></p><h3>4. MyISAM存储引擎</h3>
<p>具有以下几个特点：</p>
<ul><li>不支持事务，不支持外键。</li><li>支持表锁，不支持行锁。</li><li>访问速度快。</li></ul>
<p>具有三个文件：</p>
<p>xxx.sdi：存储表结构信息。</p>
<p>xxx.MYD：存储数据。</p>
<p>xxx.MYI：存储索引。</p>
<p class="maodian"><a name="_lab2_0_4"></a></p><h3>5. Memory存储引擎</h3>
<p>Memory引擎的表数据是存储在内存中的，由于受到硬件问题、或断电问题，只能将这些表作为临时表或缓存使用。</p>
<p>具有以下几个特点：</p>
<ul><li>内存存放</li><li>hash索引（默认）</li></ul>
<p>文件：</p>
<p>xxx.sdi：存储表结构信息。</p>
<p class="maodian"><a name="_lab2_0_5"></a></p><h3>6. 区别</h3>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121916200796.png" /></p>
<p>事务安全、行级锁、外键。</p>
<p class="maodian"><a name="_lab2_0_6"></a></p><h3>7. 存储引擎的选择</h3>
<p>在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎，还可以根据实际情况选择多种存储引擎进行组合。</p>
<p>**innoDB：**是Mysql的默认存储引擎，支持事务、外键。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么innoDB存储引擎是比较合适的选择。（绝大多数）</p>
<p>**MyISAM：**如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。</p>
<p>**MEMORY：**将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。（被redis替代）</p>
<p class="maodian"><a name="_label1"></a></p><h2>索引基础</h2>
<p>索引是帮助MySQL高效获取数据的数据结构（有序）</p>
<p class="maodian"><a name="_lab2_1_7"></a></p><h3>1. 优缺点</h3>
<p>优点：</p>
<ul><li>提高数据检索的效率，降低了数据库的I/O成本</li><li>通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。</li></ul>
<p>缺点：</p>
<ul><li>索引列也是需要占空间的。</li><li>索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行insert、update\delete时，效率降低。</li></ul>
<p class="maodian"><a name="_lab2_1_8"></a></p><h3>2. 索引结构</h3>
索引结构	描述	innoDB	MyISAM	Memory
B+Tree索引（默认）	最常见的索引类型，大部分引擎都支持B+树索引	支持	支持	支持
Hash索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询	不支持	不支持	支持
R-tree（空间索引）	空间索引是MyISAM引擎的一种特殊索引类型，主要用于地理空间数据类型，通常使用较少	不支持	支持	不支持
Full-text（全文索引）	是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr，ES	5.6版本之后才支持	支持	不支持
<p>PS：我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。</p>
<p class="maodian"><a name="_lab2_1_9"></a></p><h3>3. 索引分类</h3>
分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT
<p class="maodian"><a name="_lab2_1_10"></a></p><h3>4. innoDB索引分类</h3>
分类	含义	特点
聚集索引	将数据存储与索引放到了一块，索引结构的叶子结点保存了行数据	必须有，而且只有一个
二级索引	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个
<p>聚集索引的选取规则：</p>
<ul><li>如果存在主键，主键索引就是聚集索引。</li><li>如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。</li><li>如果表没有主键，或没有合适的唯一索引，则innoDB会自动生成一个rowid作为隐藏的聚集索引。</li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121916200796.png" /></p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121916200771.png" /></p>
<p class="maodian"><a name="_lab2_1_11"></a></p><h3>5. 索引语法</h3>
<p class="maodian"><a name="_label3_1_11_0"></a></p><h4>1. 创建索引</h4>
<div class="jb51code"><pre class="brush:sql;">create index 索引名 on 表名 (字段1,...);
</pre></div>
<p>PS：一个索引是可以关联多个字段的。</p>
<p class="maodian"><a name="_label3_1_11_1"></a></p><h4>2.查看索引</h4>
<div class="jb51code"><pre class="brush:sql;">show index from 表名;
</pre></div>
<p class="maodian"><a name="_label3_1_11_2"></a></p><h4>3.删除索引</h4>
<div class="jb51code"><pre class="brush:sql;">drop index 索引名 on 表名;
</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>SQL优化</h2>
<p><strong>步骤：</strong></p>
<ol><li>通过慢查询日志来查找需要优化的SQL。</li><li>通过explain来分析SQL。</li><li>SQL语句的优化原则。</li></ol>
<p class="maodian"><a name="_lab2_2_12"></a></p><h3>SQL查询性能下降的原因</h3>
<p>查询性能变低的最基础的原因，就是<strong>访问的数据太多了</strong>。</p>
<p>对于低效的查询，可以通过下面两个步骤分析：</p>
<ol><li>确认是否在检索大量超过需要的数据。可能是访问了很多的行，也有可能是访问了很多的列。</li><li>确认MySQL服务层是否分析大量超过需要的数据行。</li></ol>
<p class="maodian"><a name="_lab2_2_13"></a></p><h3>1. 慢查询日志</h3>
<p>记录查询话费大量时间的SQL的日志，就是<strong>慢查询日志</strong>。<br />long_query_time采数：该参数会设定一个阈值，超过该值的SQL，就是慢查询SQL。</p>
<div class="jb51code"><pre class="brush:sql;"># 查看mysql的环境变量
show variables like '%query%';
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121916200756.png" /></p>
<div class="jb51code"><pre class="brush:sql;"># 设置慢SQL的时间及开启慢SQL功能
set global long_query_time = 10;
set global slow_query_log = on;
</pre></div>
<p class="maodian"><a name="_lab2_2_14"></a></p><h3>2. 执行计划</h3>
<div class="jb51code"><pre class="brush:sql;"># 要执行一个SQL时，查询优化器会基于成本和规则对查询语句进行优化，从而生成一个执行计划；
# 通过查询计划，我们可以看到，查询走了哪个索引，查询的具体方式，多表链接的顺序等等；
# 执行计划的语法：
explain SQL语句
# SQL语句可以是insert，update，delete，select等</pre></div>
<p>示例：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025121916200748.png" /></p>
<div class="jb51code"><pre class="brush:sql;">id: 在一个大的查询中，每一个select都对应一个唯一的ID
select_type: select的查询类型
table: 表名
partitions: 分区信息
type: 针对单表的访问方法
possible_keys: 可能用到的索引
key: 实际用到的索引
key_len: 实际使用的索引长度
ref: 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows: 预估要读取的记录的条数
filtered: 搜索条件过滤后剩余的百分比
extra: 一些额外的信息
</pre></div>
<p class="maodian"><a name="_label3_2_14_3"></a></p><h4>id列</h4>
<div class="jb51code"><pre class="brush:sql;"># 查询的唯一标识
# 一个查询语句只有一个标识；比如简单查询或表连接
# 当查询语句涉及子查询时，有两个id</pre></div>
<p class="maodian"><a name="_label3_2_14_4"></a></p><h4>select_type</h4>
<div class="jb51code"><pre class="brush:sql;"># 查询类型
simple:简单查询
primary:如果查询中包含union，union all，子查询时，左边的查询的select_type就是primary
union:查询中包含union时，右边的查询的select_type就是primary
union result:选择使用临时表来完成union查询的去重工作
subquery:子查询，非关联子查询，该查询会物化，只查询一次
dependent subquery:关联子查询，子查询执行多次
derived:from后面跟子查询，物化表，只执行一次
</pre></div>
<p class="maodian"><a name="_label3_2_14_5"></a></p><h4>type</h4>
<div class="jb51code"><pre class="brush:sql;">#访问类型
#一共有12个，有7个最常用的
#从上到下性能越来越好
性能: system&gt;const&gt;eq_ref&gt;ref&gt;range&gt;index&gt;all
all:全表扫描
    explain select * from emp;
    explain select * from emp where id = 3;
index:当可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法是index
        explain select id from emp;
range:如果使用索引获取某些单点扫描区间的记录
        explain select * from emp where id in (1,4,53,23);
        explain select * from emp where id between 10 and 20;
ref:当通过普通的二级索引与常量进行等值匹配时
        explain select * from emp where name = 'mark';
eq_ref:执行连接查询时，被驱动的表是通过主键或者不允许存储NULL值的唯一二级索引列等值匹配时
const:根据主键或者唯一的二级索引列与常量行等值匹配时，就是const
        explain select * from emp where id = 3;
system:表中只有一条记录，且表引擎使用的存储引擎的统计是精确的（例如myisam，memory)</pre></div>
<p class="maodian"><a name="_label3_2_14_6"></a></p><h4>extra</h4>
<div class="jb51code"><pre class="brush:sql;">#extra提供了一些额外的信息
using index:使用索引，不需要回表（意思是该二级索引中字段包括你要查的所有字段）
using where:使用索引，需要回表（意思是用索引定位行，但还必须回表取完整数据）
using filesort:排序时
using temporary:查询时可能会借助临时表完成一些功能，例如去重、排序、分组等等</pre></div>

查看完整版本: MySQL索引、存储引擎和SQL优化深入解析