MySQL存储引擎，索引

Xc_.xC

已于 2022-09-30 14:19:39 修改

阅读量354

点赞数

文章标签： mysql oracle 数据库

于 2022-09-27 11:00:00 首次发布

本文链接：https://blog.csdn.net/qq_52215881/article/details/127056159

版权

存储引擎：

什么是存储引擎，有什么用？

存储引擎是MySQL中特有的一个术语，其它数据库中没有。

（oracle中有，但是不叫这个名字）

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引、锁定等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

存储引擎实际上是一个表存储/组织数据的方式。

不同的存储引擎，表存储数据的方式不同。

怎么给表添加/指定“存储引擎”？

通过：show create table 表名; 查看信息存储引擎信息，

可以在建表的时候，在最后面的“）”的右边通过下面的关键字指定：

ENGINE：用来指定存储引擎

CHARSET ：用来指定这张表的字符编码方式

mysql默认的存储引擎是InnoDB

mysql默认的字符编码方式是utf8mb4

查看MySQL支持的存储引擎

select version () //查看版本

show engines \G //查看MySQL支持的存储引擎

MyISAM存储引擎：

它管理的表具有以下特征：

1，使用三个文件表示每个表：

格式文件----存储表结构的定义（mytable.frm）

数据文件----存储表行的内容（mytable.MYD)

索引文件----存储表上的索引（mytable.MYI):

索引是一本书的目录，缩小扫面范围，提高查询效率，

可被转换成压缩、只读表来节省空间。

对一张表来说，只要有主键。

或者加有unique约束的字段上面会自动创建索引。

MyISAM存储引擎的特点：

可被转换为压缩、只读表来节省空间，

这是这种存储引擎的优势。

InnoDB存储引擎

这是 mysql默认的存储引擎，同时也是一个重量级的存储引擎。

InnoDB支持事务，支持数据库崩溃之后自动恢复机制。

InnoDB存储引擎最主要的特点是：非常安全。

它管理的表具有下列主要特征 :

-每个InnoDB表在数据库目录中以.frm格式文件表示。

-InnoDB表空间tablespace被用于存储表的内容

- 提高一组用来记录事务性活动的日志文件

- 用COMMIT（提交）、SAVEPOINT及ROLLBACK（回滚）支持事务处理

- 提供ACID兼容

- 在MySQL服务器崩溃后提供自动恢复

- 多版本（MVCC）和行级锁定

- 支持外键及引用的完整性，包括级联删除和更新

InnoDB最大的特点就是支持事务：

以保证数据的安全。效率不是很高，并且不能压缩，不能转换为只读

不能很好的节省存储空间

MEMORY存储引擎

使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定。

这两个特点使得MEMORY存储引擎非常快。

MEMORY存储引擎管理表具有下列特征：

- 在数据目录内，每个表均以.frm格式的文件表示

- 表数据及索引被存储在内存中。（目的就是快，查询快）

- 表级锁机制

- 不能包含TEXT 或BLOB字段

MEMORY引擎的优点：查询效率最高。

MEMORY引擎缺点：不安全，关机之后数据消失，因为数据和索引都是在内存中

索引：

推荐文章（非常详细！！！）：http://t.csdn.cn/5om4r

什么是索引？

维基百科上的定义：数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询，更新数据库表中数据。

索引是在数据库表的字段上添加的，是为了提高查询效率的一种机制。

一张表的一个字段可以添加一个索引，当然多个字段联合起来也可以添加索引。

索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，我们需要在大量数据里面检索一条数据，只能依次遍历这张表的全部数据，直到找到这条数据。通过索引去寻找，可以快速找到数据对应的磁盘地址。

MySQL在查询方面主要就是两种方式：

1，全表扫描

2，根据索引检索

在mysql数据库当中索引也是需要排序的，并且这个索引的排序和TreeSet数据结构相同。在MySQL中式B-Tree的数据结构。

注意：

1，在任何数据库当中主键都会自动添加索引对象，在MySQL当中，一个字段如果有unique约束的话，也会自动创建索引对象。

2，在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。

3，在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎当中索引储存在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存中。不管索引存储在哪，索引在MySQL当中都是一个数的形式存在。（自平衡二叉树：B-Tree)

什么条件下，我们会考虑给字段添加索引？

1，数据量庞大（到底多大，要根据测试，硬件环境不同）

2，在经常需要搜索的列上，可以加快搜索的速度
3，在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
4，在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
5，在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，

因为索引已经排序，其指定的范围是连续的
6，在经常需要排序（order by）的列上创建索引，因为索引已经排序，

这样查询可以利用索引的排序，加快排序查询时间；
7，在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

优点

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点

1，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
2，索引需要占物理空间，除了数据表占用数据空间之外，

每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大
3，对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
4，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
5，对于非常小的表，大部分情况下简单的全表扫描更高效；

2，索引的分类

MySQL的索引包括：

普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

从 功能逻辑上说，索引主要有 4 种，分别是：
        普通索引、唯一索引、主键索引、全文索引
按照 物理实现方式 ，索引可以分为 2 种：
        聚簇索引和非聚簇索引。

非聚簇索引也可叫做 “辅助索引或二级索引”
按照 作用字段个数 进行划分，分成：
        单列索引和联合索引。

聚簇索引和非聚簇索引

（聚簇索引就是按照每张表的主键构造一棵B+tree，同时叶子节点中存放的就是整张表的记录数据，也将聚簇索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引）

聚簇索引：将数据和索引一起储存，索引结构的叶子节点存的是数据行

非聚簇索引：数据和索引分开存储，索引结构中的叶子节点存的是主键值

一个表中只能存在一个聚簇索引（主键索引），但可以存在多个非聚簇索引。

聚簇索引结构：

非聚簇索引的数据结构：

联合索引

两个字段，或者更多字段联合起来添加一个索引，叫做联合索引

从左到右使用索引中的字段，一个索引只能使用索引中的一部分，但是只能是最左侧部分

索引失效的情况

1，不满足最左前缀匹配原则

2，对索引列进行前模糊查询

3，对索引的列进行计算，函数，转换类型，索引都会失效

4，对索引列进行or（或者）的操作

5，is not null不走索引 is null走索引

最左匹配原则：

在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先（查询条件精确匹配索引的左边连续一列或几列，则构建对应列的组合索引树），在检索数据时也从联合索引的最左边开始匹配。

索引的数据结构

Mysql数据库中的常见索引结构有多种，常用Hash，B-树，B+树等数据结构来进行数据存储。树的深度加深一层，意味着多一次查询，对于数据库磁盘而言，就是多一次IO操作，导致查询效率低下。

不同的存储引擎采用的索引结构不同，MySQL默认的InnoDB默认使用的索引结构式B+Tree树。

B+树

B+树的特点：

B+树是B-树的变体，也是一种多路搜索树：（❀ 表示两者间的不同点）

树中每个结点至多有m个孩子

根结点的儿子数为[2, M]；

除根结点以外的非叶子结点的儿子数为[M/2, M]；

每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）

非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；

❀ 非叶子结点的子树指针与关键字个数相同；

❀ 非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树；（B树是开区间）；

❀ 为所有叶子结点增加一个链指针；

❀ 所有关键字都在叶子结点出现；

创建索引：

索引名称是可以省略的，省略后，索引的名称和索引列名相同

--创建主键索引（只需要创建一个主键约束，系统会自动创建一个主键索引）

alter table 表名称 add primary key(字段名);

-- 创建普通索引

         create index 索引名称 on 表名（表字段名称）；

-- 创建唯一索引

        create unique index 索引名称 on 表名（表字段名称）；

-- 创建普通组合索引

        create index 索引名称 on 表名（表字段名称1,表的字段名称2...）；

-- 创建唯一组合索引
         create unique index 索引名称 on 表名（表字段名称1,表的字段名称2...）；

修改表结构创建索引

ALTER TABLE 表名 ADD INDEX 索引名称(字段名);

创建表时指定索引：

CREATE TABLE table_name (
    ID INT NOT NULL,
    col_name VARCHAR (16) NOT NULL,
    INDEX index_name (col_name)
);

删除索引：

-- 直接删除索引
DROP INDEX index_name ON table_name;

-- 修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;

EXPLAIN：

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句。不展开讲解，大家可自行百度这块知识点。

使用格式：EXPLAIN SQL...;

mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

查看表索引的信息

show index from 表名;

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | fktid    |            1 | s_tid       | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.04 sec)