SQL复习（四）-- 存储引擎 & 索引

最新推荐文章于 2023-06-16 16:07:42 发布

wuyanwenyun

最新推荐文章于 2023-06-16 16:07:42 发布

阅读量319

点赞数

分类专栏： MySQL MySQL进阶

本文链接：https://blog.csdn.net/wuyanwenyun/article/details/105575807

版权

MySQL 同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

MySQL进阶

3 篇文章 0 订阅

订阅专栏

1 存储引擎

1.1 概念介绍

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体功能。以常见的关系型数据库来说，只有MySQL才有存储引擎的概念，其他的都是唯一的存储结构，只不过支持不同数据结构的表，比如Oracle支持堆表和iot表，但是没有存储引擎的概念。

1.2 存储引擎的分类

使用 show engines; 可以查询 MySQL 中的存储引擎，查询结果（MySQL5.7）：

Engine	Support	Comment	Transactions	XA	Savepoints
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys 支持事务，行级锁定和外键	YES	YES	YES
MRG_MYISAM	YES	Collection of identical MyISAM tables 相同的MyISAM表的集合	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables 基于Hash，存储在内存中，对临时表很有用	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears) / dev / null存储引擎（写入其中的所有内容都会消失）	NO	NO	NO
MyISAM	YES	MyISAM storage engine MyISAM存储引擎	NO	NO	NO
CSV	YES	CSV storage engine CSV存储引擎	NO	NO	NO
ARCHIVE	YES	Archive storage engine 存档存储引擎	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema 性能架构	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine 联合MySQL存储引擎

InnoDB ：MySQL 5.7中的默认存储引擎。行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。

MyISAM ：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，表级锁定限制了读/写工作负载中的性能，因此在Web和数据仓库配置中，它通常用于 read-only 或 read-mostly 为主的工作负载。

Memory(HEAP) : 全表锁，存储在RAM中，速度快，但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表。InnoDB及其缓冲池内存区域提供了一种通用且持久的方法来将大部分或所有数据保存在内存中，因此使用减少。

Feature	MyISAM	Memory	InnoDB	Archive	NDB
B-tree indexes	Yes	Yes	Yes	No	No
Backup/point-in-time recovery (在服务器中实现,而不是在存储引擎。)	Yes	Yes	Yes	Yes	Yes
Cluster database support 集群数据库支持	No	No	No	No	Yes
Clustered indexes 聚集索引	No	No	Yes	No	No
Compressed data 压缩数据	Yes (仅当使用压缩行格式时)	No	Yes	Yes	No
Data caches 数据缓存	No	N/A	Yes	No	Yes
Encrypted data 加密的数据	Yes (在服务器中实现)	Yes (在服务器中实现)	Yes (在服务器中实现； MySQL 5.7+ 支持静态数据表空间加密。)	Yes (在服务器中实现)	Yes (在服务器中实现)
Foreign key support 外键的支持	No	No	Yes	No	Yes (MySQL Cluster NDB 7.3+ )
Full-text search indexes 全文搜索索引	Yes	No	Yes (MySQL5.6 and later)	No	No
Geospatial data type support 地理空间数据类型支持	Yes	No	Yes	Yes	Yes
Geospatial indexing support 地理空间索引支持	Yes	No	Yes ( MySQL 5.7 and later)	No	No
Hash indexes 散列索引	No	Yes	No (内部使用散列索引)	No	Yes
Index caches 索引缓存	Yes	N/A	Yes	No	Yes
Locking granularity 锁的粒度	Table	Table	Row	Row	Row
MVCC	No	No	Yes	No	No
Replication support (note 1) 复制支持	Yes	Limited	Yes	Yes	Yes
Storage limits 存储限制	256TB	RAM	64TB	None	384EB
T-tree indexes	No	No	No	No	Yes
Transactions 事务	No	No	Yes	No	Yes
Update statistics for data dictionary	Yes	Yes	Yes	Yes	Yes

1.3 InnoDB与MyISAM的区别

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

如何选择引擎？
如果没有特别的需求，使用默认的 InnoDB 即可。
MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。
Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如 OA自动化办公系统。

2 索引

2.1 索引的概念

索引是对数据库表中一个或多个列的值进行排序的结构，建立索引有助于快速获取信息。简单理解就是书的目录，字典前边的拼音列表或部首列表，加快检索表中数据的方法。在添加完索引之后，MySQL 一般通过BTREE算法生成一个二叉树类型的索引文件，在查询数据库时，找到索引文件进行遍历(折半查找大幅查询效率)，找到相应的键从而获取数据。

同如在书中，利用索引不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

2.2 索引的分类

mysql 有4种不同的索引：
主键索引（primary）
数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引（unique）
数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

普通索引（index）

B-Tree 索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历 node，获取 leaf。是MySQL里默认和最常用的索引类型。

全文索引（FULLTEXT）

目前只有 char、varchar，text 列上可以创建全文索引。为了解决 where name like “%word%" ,针对文本的模糊查询效率较低的问题。

HSAH 索引

由于 HASH 的唯一及其键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

创建索引

<直接创建索引>
-- 创建普通索引
create index 索引名 on 表名(列名);
-- 创建唯一索引
create unique index 索引名 on 表名(列名);

-- 创建普通组合索引
create index 索引名 on 表名(列名1,列名2);

-- 创建唯一组合索引
create unique index 索引名 on 表名(列名1,列名2);

<通过修改表结构创建索引>
-- 创建普通索引
alter table 表名 add index 索引名 (列名); 
-- 创建组合索引
alter table 表名 add index 索引名(列名1,列名2); 

-- 创建唯一索引
alter table 表名 add unique (列名); 
-- 创建唯一组合索引
alter table 表名 add unique (列名1,列名2); 

-- 创建全文索引
alter table 表名 add fulltext (列名);

删除索引

alter table 表名 drop index 索引名;
drop index 索引名 on 表名;

查看索引

-- 查看:
show index from `表名`;
-- 或
show keys from `表名`;

2.3 索引的利弊

索引并非是越多越好，创建索引也需要耗费资源，一是增加了数据库的存储空间，二是在插入和删除时要花费较多的时间维护索引.

优点

大加快数据的检索速度
使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间
创建唯一索引，能够保证数据库表中每一行数据的唯一性
在实现数据的参考完整性方面，可以加速表和表之间的连接
通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能

缺点

创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加
索引需要占据磁盘空间
对数据表中的数据进行增加，修改，删除时，索引也要动态的维护，降低了维护的速度

2.4 使用索引的注意事项

由于索引本身很大，占用磁盘空间，对增删改操作有影响，变慢，满足以下条件的字段，才建议创建索引。

适合使用索引的情况

1) 主键自动创建唯一索引

2) 频繁作为查询条件的字段

3) 查询中与其他表关联的字段

4) 查询中排序的字段

5) 查询中统计或分组字段

不适合使用索引的情况

1) 频繁更新的字段，MySQL 中索引是在存储引擎层而不是服务器层实现的

2) where 条件中用不到的字段，数据量小的表不要使用索引，表中的数据越多，索引的优越性越明显

3) 表记录太少

4) 经常增删改的表

5) 字段的值的差异性不大或重复性高，重复数据多的字段不应设为索引（比如性别，只有男和女，一般来说：重复的数据超过15%就不该建索引）

索引使用建议

1) 单表查询：哪个列作查询条件，就在该列创建索引

2) 多表查询：left join 时，索引添加到右表关联字段；right join 时，索引添加到左表关联字段

3) 不要对索引列进行任何操作（计算、函数、类型转换）

4) 索引列中不要使用 !=，< > 非等于

5) 索引列不要为空，且不要使用 is null 或 is not null 判断

6) 索引字段是字符串类型，查询条件的值要加 ' ' 单引号,避免底层类型自动转换

7) 首先应该考虑对where 和 order by 涉及的列上建立索引

违背上述原则可能会导致索引失效，具体情况需要使用 explain 命令进行查看

索引失效情况

除了违背索引创建和使用原则外，如下情况也会导致索引失效：

1) 模糊查询时，以 % 开头,一般情况下不鼓励使用like操作，如果非使用不可，要注意 like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。

2) 使用 or 时，如：字段1（非索引）or 字段2（索引）会导致索引失效。

3) 使用复合索引时，不使用第一个索引列会导致失效。

index(a,b,c) ，以字段 a,b,c 作为复合索引为例：

SQL语句索引是否生效
where a = 1 是，字段a索引生效
where a = 1 and b = 2 是，字段a和b索引生效
where a = 1 and b = 2 and c = 3 是，全部生效
where b = 2 or where c = 3 否
where a = 1 and c = 3 字段a生效，字段c失效
where a = 1 and b > 2 and c = 3 字段a, b生效，字段c失效
where a = 1 and b like 'xxx%' and c = 3 字段a, b生效，字段c失效

select * from users where YEAR(adddate) < 2007; 
-- 将导致索引失效而进行全表扫描，因此我们可以改成:
select * from users where adddate < ‘2007-01-01';

2.5 explain 查看索引是否生效

explain 语句用来分析sql语句

参数：

id：查询的序列号。id相同，执行顺序由上至下；id不同，id值越大优先级越高，越先被执行。
select_type：查询类型。
simple：简单查询，不包含子查询或 union

primary:包含复杂的子查询，最外层查询标记为该值

subquery：在 select 或 where 包含子查询，被标记为该值

derived：在 from 列表中包含的子查询被标记为该值，MySQL 会递归执行这些子查询，把结果放在临时表

union：若第二个 select 出现在 union 之后，则被标记为该值。若 union 包含在 from 的子查询中，外层 select 被标记为 derived

union result：从 union 表获取结果的 select
table：查询表名。
type：扫描方式，引擎在表中找到所需行的方式
由差到好为：（最好能优化到 ref）

all（全表扫描）

index（只遍历索引树）

range（索引范围扫描，常见于between，>，< 等查询中）

ref（非唯一性索引扫描）返回匹配某个单独值的所有行。用于=、< 或 > 操作符带索引的列

eq_ref（唯一性索引扫描）对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引扫描

const 通过索引一次就找到，只匹配一行数据

system（当MySQL对查询某部分进行优化，并转换为一个常量时使用），表只有一行记录，相当于系统表

null（MySQL在优化过程中分解语句，执行时甚至不用访问表或索引）
possible_keys：可供选择的索引，如果该值为 NULL，说明没有使用索引，可以建立索引提高性能
key：实际使用到的索引。
key_len 索引字节数的长度，数值越小，运行速度越快，显示的是索引字段的最大长度，并非实际使用长度
ref 连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows：返回的数据行数，数值越小越好
filtered 被表条件过滤的行数的百分比
Extra：sql语句额外的信息
using index（表示select操作中使用了覆盖索引），避免了访问表的数据行，效率不错

using where（mysql服务器在存储引擎受到记录后进行“后过滤“），where 子句用于限制哪一行

using temporary（表示mysql需要使用临时表来存储结果集，常见于排序和分组查询），使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。出现该值，应该优化 SQL

using filesort（mysql中无法使用索引完成的排序操作，成为“文件排序”）说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。出现该值，应该优化 SQL

using join buffer：使用连接缓存

distinct：发现第一个匹配后，停止为当前的行组合搜索更多的行

-- emp表，新建索引 命名为 name_index
create unique index name_index on emp (ename);

explain select * from emp where ename like 'JON%';

4月20日更新

B-Tree 和 Hash比较：

B树索引可用于使用=、>、>=、<、<=、或运算符之间的表达式中的列比较。如果LIKE的参数是不以通配符开头的常量字符串，则该索引也可用于LIKE比较。

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

如果是 ... like '%string%' 而且 string 过三个字符的语句，MySQL使用 Turbo Boyer-Moore 算法初始化字符串的模式，然后使用此模式更快地执行搜索。

2. hash 索引

仅用于使用 = 或 <=> 运算符的等式比较（但速度非常快）。不用于查找值范围的比较运算符，如 < 。依赖这种单值查找的系统被称为“键值存储（ key-value stores ）”；要将MySQL用于此类应用程序，尽可能使用散列索引。
优化器不能使用哈希索引来加速 ORDER BY 操作。（此类型的索引不能用于按顺序搜索下一个条目。）
MySQL无法确定两个值之间大约有多少行（范围优化器使用这来决定要使用哪个索引）。如果将MyISAM或InnoDB表更改为哈希索引 MEMORY 表，这可能会影响某些查询。
只能使用整个键来搜索行。（对于B树索引，可以使用键的最左前缀来查找行。）