MySQL存储引擎和索引

二猛子

于 2025-04-04 11:03:33 发布

阅读量714

点赞数 29

分类专栏： SQL 文章标签： mysql sql 数据库

本文链接：https://blog.youkuaiyun.com/qq_45855355/article/details/146981699

版权

SQL 专栏收录该内容

11 篇文章

订阅专栏

文章目录

前言
一、MySQL存储引擎
二、MySQL索引
- 1.索引的分类
- 2.索引的基本用法
总结

前言

本章内容会介绍两种常用的MySQL存储引擎InnoDB和MyISAM的差异，以及MySQL索引的基本概念和用法。

一、MySQL存储引擎

MySQL中常用的存储引擎有InnoDB和MyISAM，不同存储引擎的数据存储方式不一样，一张表需要存储的内容包括表数据、表结构、表的索引。

通过以下指令可以查看MySQL中的存储引擎;

show engines;

1、通过在root权限下查看InnoDB和MyISAM的存储文件，可以看出来两种存储引擎的数据存储方式不同；
2、在InnoDB存储引擎下表数据和索引是放在同一个文件中，而在MyISAM存储引擎中表数据和索引是单独存放的；
3、在使用InnoDB时，即使不给表加主键，表也会自动生成主键，因为表数据和索引是放在同一个文件中；在使用MyISAM存储引擎时，不会自动生成主键。

su root
cd /var/lib/mysql
cd mysql

在回答”MySQL不同的存储引擎有哪些区别？“这样的问题时，主要从以下几点来回答就可以：

在这里插入图片描述

锁机制：表示数据库在并发请求访问的时候，多个事务在操作时，并发操作的粒度。
B-树索引和哈希索引：主要是加速SQL的查询速度。
外键：子表的字段依赖父表的主键，设置两张表的依赖关系。
事务：多个SQL语句，保证它们共同执行的原子操作，要么成功，要么失败，不能只成功一部分，失败需要回滚事务。
索引缓存和数据缓存：和MySQL Server的查询缓存相关，在没有对数据和索引做修改之前，重复查询可以不用进行磁盘I/O（数据库的性能提升，目的是为了减少磁盘I/O操作来提升数据库访问效率），读取上一次内存中查询的缓存就可以了。

二、MySQL索引

当表中的数据量到达几十万甚至上百万的时候，SQL查询所花费的时间会很长，导致业务超时出错，此时就需要用索引来加速SQL查询。由于索引也是需要存储成索引文件的，因此对索引的使用也会涉及磁盘I/O操作。如果索引创建过多，使用不当，会造成SQL查询时，进行大量无用的磁盘I/O操作，降低了SQL的查询效率，适得其反，因此掌握良好的索引创建原则非常重要！

1.索引的分类

索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结果。索引的核心是提高查询的速度。索引的分类，在物理层面分为聚集索引、非聚集索引；在逻辑层面分为：
1、普通索引：没有任何限制条件，可以给任何类型的字段创建普通索引(创建新表&已创建表，数量是不限的，一张表的一次sql查询只能用一个索引 where a=1 and b=‘M’)
2、唯一性索引：使用UNIQUE修饰的字段，值不能够重复，主键索引就隶属于唯一性索引
3、主键索引：使用Primary Key修饰的字段会自动创建索引(MyISAM, InnoDB)
4、单列索引：在一个字段上创建索引
5、多列索引：在表的多个字段上创建索引 (uid+cid，多列索引必须使用到第一个列，才能用到多列索引，否则索引用不上)
6、全文索引：使用FULLTEXT参数可以设置全文索引，只支持CHAR，VARCHAR和TEXT类型的字段上，常用于数据量较大的字符串类型上，可以提高查询速度(线上项目支持专门的搜索功能，给后台服务器增加专门的搜索引擎支持快速高校的搜索 elasticsearch 简称es C++开源的搜索引擎搜狗的workflow)

2.索引的基本用法

索引的创建有两种情况：
1、创建表的时候指定索引字段；
2、在已经创建的表上添加新的索引： CREATE [UNIQUE] INDEX 索引名 ON 表名（属性名（length） [ASC | DESC]);

//创建表的时候指定索引字段 给id name 添加索引
CREATE TABLE index1(
 id INT,
 name VARCHAR(20),
 sex ENUM('M', 'W'),
 INDEX(id,name));
 //在已经创建的表上添加新的索引
create index sexindex on index1(sex);