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

存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

1. 在创建表时，指定存储引擎

create table 表名(
	...
)engine = innodb
#在最后指定

2. 查看当前数据库支持的存储引擎

show engines;

3. innodb存储引擎

innodb是一种兼顾高可靠性和高性能的通用存储引擎，在mysql 5.5之后，innodb是默认的mysql存储引擎，在此之前的默认引擎是myisam。

它具有以下几个特点：

dml操作遵循acid模型，支持事务。
行级锁，提高并发访问性能。
支持外键foreign key约束，保证数据的完整性和正确性。

innodb引擎的每张表都会有一个这样的表空间文件（xxx.ibd），存储该表的表结构（frm、sdi）、数据和索引。

参数：innodb_file_per_table 该参数打开表示每一张表对应一个表空间文件而不是共享一个。

4. myisam存储引擎

具有以下几个特点：

不支持事务，不支持外键。
支持表锁，不支持行锁。
访问速度快。

具有三个文件：

xxx.sdi：存储表结构信息。

xxx.myd：存储数据。

xxx.myi：存储索引。

5. memory存储引擎

memory引擎的表数据是存储在内存中的，由于受到硬件问题、或断电问题，只能将这些表作为临时表或缓存使用。

具有以下几个特点：

内存存放
hash索引（默认）

文件：

xxx.sdi：存储表结构信息。

6. 区别

事务安全、行级锁、外键。

7. 存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎，还可以根据实际情况选择多种存储引擎进行组合。

**innodb：**是mysql的默认存储引擎，支持事务、外键。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么innodb存储引擎是比较合适的选择。（绝大多数）

**myisam：**如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

**memory：**将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。memory的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。（被redis替代）

索引基础

索引是帮助mysql高效获取数据的数据结构（有序）

1. 优缺点

优点：

提高数据检索的效率，降低了数据库的i/o成本
通过索引列对数据进行排序，降低数据排序的成本，降低cpu的消耗。

缺点：

索引列也是需要占空间的。
索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行insert、update\delete时，效率降低。

2. 索引结构

索引结构	描述	innodb	myisam	memory
b+tree索引（默认）	最常见的索引类型，大部分引擎都支持b+树索引	支持	支持	支持
hash索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询	不支持	不支持	支持
r-tree（空间索引）	空间索引是myisam引擎的一种特殊索引类型，主要用于地理空间数据类型，通常使用较少	不支持	支持	不支持
full-text（全文索引）	是一种通过建立倒排索引，快速匹配文档的方式。类似于lucene，solr，es	5.6版本之后才支持	支持	不支持

ps：我们平常所说的索引，如果没有特别指明，都是指b+树结构组织的索引。

3. 索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	primary
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	unique
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	fulltext

4. innodb索引分类

分类	含义	特点
聚集索引	将数据存储与索引放到了一块，索引结构的叶子结点保存了行数据	必须有，而且只有一个
二级索引	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引的选取规则：

如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一（unique）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则innodb会自动生成一个rowid作为隐藏的聚集索引。

5. 索引语法

1. 创建索引

create [unique|fulltext] index 索引名 on 表名 (字段1,...);

ps：一个索引是可以关联多个字段的。

2.查看索引

show index from 表名;

3.删除索引

drop index 索引名 on 表名;

sql优化

步骤：

通过慢查询日志来查找需要优化的sql。
通过explain来分析sql。
sql语句的优化原则。

sql查询性能下降的原因

查询性能变低的最基础的原因，就是访问的数据太多了。

对于低效的查询，可以通过下面两个步骤分析：

确认是否在检索大量超过需要的数据。可能是访问了很多的行，也有可能是访问了很多的列。
确认mysql服务层是否分析大量超过需要的数据行。

1. 慢查询日志

记录查询话费大量时间的sql的日志，就是慢查询日志。
long_query_time采数：该参数会设定一个阈值，超过该值的sql，就是慢查询sql。

# 查看mysql的环境变量
show variables like '%query%';

# 设置慢sql的时间及开启慢sql功能
set global long_query_time = 10;
set global slow_query_log = on;

2. 执行计划

# 要执行一个sql时，查询优化器会基于成本和规则对查询语句进行优化，从而生成一个执行计划；
# 通过查询计划，我们可以看到，查询走了哪个索引，查询的具体方式，多表链接的顺序等等；
# 执行计划的语法：
explain sql语句
# sql语句可以是insert，update，delete，select等

示例：

id: 在一个大的查询中，每一个select都对应一个唯一的id
select_type: select的查询类型
table: 表名
partitions: 分区信息
type: 针对单表的访问方法
possible_keys: 可能用到的索引
key: 实际用到的索引
key_len: 实际使用的索引长度
ref: 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows: 预估要读取的记录的条数
filtered: 搜索条件过滤后剩余的百分比
extra: 一些额外的信息

id列

# 查询的唯一标识
# 一个查询语句只有一个标识；比如简单查询或表连接
# 当查询语句涉及子查询时，有两个id

select_type

# 查询类型
simple:简单查询
primary:如果查询中包含union，union all，子查询时，左边的查询的select_type就是primary
union:查询中包含union时，右边的查询的select_type就是primary
union result:选择使用临时表来完成union查询的去重工作
subquery:子查询，非关联子查询，该查询会物化，只查询一次
dependent subquery:关联子查询，子查询执行多次
derived:from后面跟子查询，物化表，只执行一次

type

#访问类型
#一共有12个，有7个最常用的
#从上到下性能越来越好
性能: system>const>eq_ref>ref>range>index>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)

extra

#extra提供了一些额外的信息
using index:使用索引，不需要回表（意思是该二级索引中字段包括你要查的所有字段）
using where:使用索引，需要回表（意思是用索引定位行，但还必须回表取完整数据）
using filesort:排序时
using temporary:查询时可能会借助临时表完成一些功能，例如去重、排序、分组等等

到此这篇关于mysql深入之索引、存储引擎和sql优化的文章就介绍到这了,更多相关mysql索引、存储引擎和sql优化内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

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

2025年12月21日 • Mysql •我要评论