文章目录
一、存储引擎
二、索引
1)连接层
最上层是一些客户端和链接服务,包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2)服务层
第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化,部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如过程、函数等。在该层,服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化如确定表的查询的顺序,是否利用索引等,最后生成相应的执行操作。如果是select语句,服务器还会查询内部的缓存,如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能。
3)引擎层
存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能,这样我们可以根据自己的需要,来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。
4)存储层
数据存储层,主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上,并完成与存储引擎的交互。
和其他数据库相比,MySQL有点与众不同,它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上,插件式的存储引擎架构,将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。
我们可以在创建表的时候,来指定选择的存储引擎,如果没有指定将自动选择默认的存储引擎。
1)建表时指定存储引擎
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;
show create table 表名; #查看建表语句
2)查询当前数据库支持的存储引擎
show engines;
上面我们介绍了什么是存储引擎,以及如何在建表时如何指定存储引擎,接下来我们就来重点介绍三种存储引擎 InnoDB、MyISAM、Memory的特点。
1)介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在 MySQL 5.5 之后,InnoDB是默认的 MySQL 存储引擎。
2)特点
3)文件
xxx.ibd:xxx代表的是表名,innoDB引擎的每张表都会对应这样一个表空间文件,存储该表的表结构(frm-早期的 、sdi-新版的)、数据和索引。
参数:innodb_file_per_table
show variables like 'innodb_file_per_table';
如果该参数开启,代表对于InnoDB引擎的表,每一张表都对应一个ibd文件。 我们直接打开MySQL的数据存放目录: C:\ProgramData\MySQL\MySQL Server 8.0\Data , 这个目录下有很多文件夹,不同的文件夹代表不同的数据库,我们直接打开jw(对应某个数据库)文件夹。
可以看到里面有很多的ibd文件,每一个ibd文件就对应一张表,比如:我们有一张表 account,就有这样的一个account.ibd文件,而在这个ibd文件中不仅存放表结构、数据,还会存放该表对应的索引信息。 而该文件是基于二进制存储的,不能直接基于记事本打开,我们可以使用mysql提供的一个指令 ibd2sdi ,通过该指令就可以从ibd文件中提取sdi信息,而sdi数据字典信息中就包含该表的表结构。
4)逻辑存储结构
1)介绍
MyISAM是MySQL早期的默认存储引擎。
2)特点
3)文件
xxx.sdi:存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引
1)介绍
Memory引擎的表数据时存储在内存中的,由于受到硬件问题、或断电问题的影响,只能将这些表作为临时表或缓存使用。
2)特点
3)文件
xxx.sdi:存储表结构信息【数据存放在内存中,xxx.sdi存放在D:\SoftwareInstall\mysql-5.7.42-winx64\data\databaseName\xxx.sdi】
特点 | InnoDB | MyISAM | Memory |
---|---|---|---|
存储限制 | 64TB | 有 | 有 |
事务安全 | 支持 | ||
锁机制 | 行锁 | 表锁 | 表锁 |
B+tree索引 | 支持 | 支持 | 支持 |
Hash索引 | 支持 | ||
全文索引 | 支持(5.6版本之后) | 支持 | |
空间使用 | 高 | 低 | N/A |
内存使用 | 高 | 低 | 中等 |
批量插入速度 | 低 | 高 | 高 |
支持外键 | 支持 |
①InnoDB引擎, 支持事务, 而MyISAM不支持。
②InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。
③InnoDB引擎, 支持外键, 而MyISAM是不支持的。
主要是上述三点区别,当然也可以从索引结构、存储限制等方面,更加深入的回答,具体参 考如下官方文档:
https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html
https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html
在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。
索引概述:索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
(红黑树是一颗自平衡二叉树,那这样即使是顺序插入数据,最终形成的数据结构也是一颗平衡的二叉树,解决顺序插入形成链表的问题。但红黑树仍存在”大数据量情况下,层级较深,检索速度慢“)
所以,在MySQL的索引结构中,并没有选择二叉树或者红黑树,而选择的是B+Tree,那么什么是B+Tree呢?在详解B+Tree之前,先来介绍一个B-Tree。
~~
二叉树、红黑树:
B-Tree(B树,多路平衡查找树):
我们可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~galles/visualization/BTree.html
B+Tree
B+Tree是B-Tree的变种
我们可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
上述我们所看到的结构是标准的B+Tree的数据结构,接下来,我们再来看看MySQL中优化之后的B+Tree。
Hash索引
MySQL中除了支持B+Tree索引,还支持一种索引类型---Hash索引。
哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。
Hash索引特点:
存储引擎支持:在MySQL中,支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能,hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。
表结构及其数据如下,针对无索引、有索引两种情况
备注: 这里我们只是假设索引的结构是二叉树,介绍一下索引的大概原理,只是一个示意图,并不是索引的真实结构,索引的真实结构,后面会详细介绍。
优势 | 劣势 |
---|---|
提高数据检索的效率,降低数据库的IO成本 | 索引列也是要占用空间的。 |
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗。 | 索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行INSERT、UPDATE、DELETE时,效率降低。 |
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的索引结构,主要包含以下几种【我们平常所说的索引,如果没有特别指明,都是指B+树结构组织的索引】:
O(logn)
,擅长范围查询。R-Tree
索引: 空间索引是MyISAM引擎的一个特殊索引类型,属于地理空间数据类型查询,通常使用较少。簇 cù
在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
InnoDB 引擎
要求必须有聚簇索引,也就是在主键字段建立聚簇索引。InnoDB 引擎
分类 | 含义 | 特点 |
---|---|---|
聚集索引(Clustered Index) | 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
二级索引(Secondary Index) | 也叫辅助索引,将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引之所以必须有是因为它是用来存储数据的,而二级索引更多地用来 更快更高效地查询数据。
聚集索引选取规则:
聚集索引的叶子节点下挂的是这一行的数据 。
二级索引的叶子节点下挂的是该字段值对应的主键值
分析执行SQL语句时,具体的查找过程是什么样子的:select * from user where name='Arm'; id为主键,name字段创建的有索引
具体过程如下:
①由于是根据name字段进行查询,所以先根据name='Arm'到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
②由于查询返回的数据是*,所以此时,还需要根据主键值10,到聚集索引中查找10对应的记录,最终找到10对应的行row。
③最终拿到这一行的数据,直接返回即可。
回表查询:这种先到二级索引中查找数据,找到主键值,然后再到聚集索引中根据主键值,获取数据的方式,就称之为回表查询。(先走二级索引找到主键值,再根据主键值到聚集索引中找到对应的行数据)
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 针对于表中主键创建的索引 | 默认自动创建 | PRIMARY |
唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的是文本中的关键词,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
上述是MySQL中所支持的所有的索引结构,接下来,我们再来看看不同的存储引擎对于索引结构的支持情况。
索引 | InnoDB | MyISAM | Memory |
---|---|---|---|
B+tree索引 | 支持 | 支持 | 支持 |
Hash索引 | 不支持 | 不支持 | 支持 |
R-tree索引 | 不支持 | 支持 | 不支持 |
Full-text | 5.6版本之后支持 | 支持 | 不支持 |
注意:我们平常所说的索引,如果没有特别指明,都是指B+树结构组织的索引
聚簇索引是将表的数据按照索引顺序存储在磁盘上,聚簇索引的叶子节点直接存储了实际的数据行,而不是指向数据的指针。所以在查询的时候减少了磁盘的随机读取,无需进行多次磁盘I/O效率很高。
非聚簇索引是一种基于指针的索引,有时也叫它二级索引。非聚簇索引不直接存储实际的数据,seelec 语句在执行查询时,会先根据二级索引定位到数据所在的磁盘位置,然后再进行一次磁盘I/O操作,读取实际的数据行。
当然具体如何选择需要看查询需求、数据分布和性能要求。
哈希索引:
B+树索引
所以在选择上:
从这三个维度可以很好的应用在你的开发工作中,如果是小数据量的 web 网站查询、直接用 B+ 树就可以了。对于数据量的大小评估,后面单开一篇讲解。
不是。索引是建立在原数据上的数据结构,所以不论在查询还是更新维护、一定会带来开销。
比如一本书有 100 页,我构建了 50 页的目录,你觉查询起来还会方便吗?
索引并非银弹,正确使用才能发挥奇效。
慢 SQL 是数据库使用中最长遇见的问题,当遇到慢 SQL 时,首先我们就要去看是不是索引失效。一般会有以下几种常见的情况:
1.Where 条件中包含 OR: 当查询条件中包含 OR,即使其中某些条件带有索引,也会全表扫描。下例中 username 没有索引,就算 id 走了索引也需要全表扫描,所以引擎大概率不会走索引。
失效索引: id 有索引, username 没有索引。
explain select * from t_user where id = 2 or username = 'jw';
2.多列索引没有最左匹配: 对于复合索引,如果查询条件没有从索引的第一部分匹配,则不会使用索引。也就是我们在使用联合索引时,要正确使用最左匹配。
例如,如果你有一个(id, name)的多列索引,但查询条件只使用了name,那么索引不会被使用。
3.LIKE 查询以%开头: 当使用LIKE操作符进行模糊查询,并且模式以%开头时,索引将不会生效。这是因为以%开头的模式匹配意味着匹配的字符串可以在任何位置,这使得索引无法有效定位数据。
4.索引列参与计算: 当我们在查询条件中对索引列进行表达式计算,也是无法走索引的。比如:
select * from t_user where id > age;
5.类型不匹配导致隐式转换: 当表里存的是 varchar 类型的字段时,用 int 类型去查询,导致全表扫描。如下例子中:
explain select * from t_user where id_no = 1002;
表里的 id_no 是 varchar 类型。
出了这几种情况还有一些导致索引失效。 例如:
在考虑建立索引时,也需要考虑以下因素:
总的来说,B+树在数据库索引中提供了更稳定的查询性能、优化的磁盘I/O操作、高效的范围查询和排序,以及较低的维护成本。
回表定义: MySQL回表查询是指在使用索引进行查询时,MySQL数据库引擎在通过索引定位到数据行后,发现需要访问表中的其他列数据,而不是直接通过索引就能获取到所需的数据。这种情况下,MySQL需要再次访问表中的数据行,这个过程就称为回表查询(Referring to the table)。
当然,不是所有情况都不允许回表,有时候,适当的回表是必要的,因为索引的设计需要平衡查询性能和存储空间的利用。
位图索引是一种将数据列的所有可能值映射到二进制位上的索引。每个位表示某个值是否存在于该列中,从而帮助我们快速定位符合某个条件的行。与其他类型的索引相比,位图索引通常在低基数列(即列中有限的不同值)上表现更好。
可以参考 bitmap 数据结构来理解
例子:
在该示例中,我们为 age 和 country 列分别创建了位图索引。由于使用了位图索引,查询性能将大大提高。
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
country VARCHAR(50)
);
CREATE BITMAP INDEX idx_age ON users(age);
CREATE BITMAP INDEX idx_country ON users(country);
SELECT * FROM users WHERE age = 20 AND country = 'China';
两种方式:
SHOW INDEX FROM your_table_name;
SELECT
TABLE_SCHEMA,
TABLE_NAME,
NON_UNIQUE,
INDEX_NAME,
INDEX_TYPE,
INDEX_COMMENT,
SEQ_IN_INDEX,
COLUMN_NAME,
CARDINALITY,
SUB_PART,
PACKED,
NULLABLE,
INDEX_DIR,
INDEX_DISC
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = 'your_database_name' AND
TABLE_NAME = 'your_table_name';
正例:
CREATE TABLE articles (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
PRIMARY KEY (id),
FULLTEXT INDEX (title, content) -- 创建联合全文索引
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
当已经建好表结构,使用 ALTER TABLE 创建:
ALTER TABLE articles
ADD FULLTEXT INDEX ft_index (title, content);
全文索引一般用于内容管理平台(CMS),问答社区等检索场景,然而,全文索引也有一些限制,比如它只能用于MyISAM或InnoDB存储引擎(在MySQL 5.6及以上版本中),并且全文索引的列不能是NULL值。
实际应用中其实很少会使用到,现在多数使用 ElasticSearch 来搭建全文搜索引擎。
索引主要是为了优化查询性能而设计的。如果一个字段的查询频率远低于更新频率,那么为该字段创建索引可能不会带来预期的性能提升,反而可能因为维护索引而降低整体性能。
当你尝试为一个已经存在大量数据的表添加索引时,可能会遇到什么问题?如何解决这些问题?
首先:如果是亿级大表,在建表时就要添加必要的索引,否则存入过多数据可能会出现加不成功的现象。
当你遇到查询性能问题时,如何分析和优化索引的使用?开放性问题。
如何检测索引碎片化?两个方法
SHOW TABLE STATUS LIKE 'table_name';
可以获取表的状态信息,其中包括 Data_free 字段,它表示表中未使用的空间百分比。如果这个值相对较高,可能表明表存在碎片化问题。INFORMATION_SCHEMA.TABLES
可以获取表的碎片化信息。例如:SELECT table_name, table_schema, Data_free / Data_length * 100 AS碎片化百分比
FROM information_schema.TABLES
WHERE table_schema = 'your_database_name' AND Data_free > 0;
如何修复索引碎片化?
对于 MyISAM 存储引擎,可以使用 OPTIMIZE TABLE
命令来重新组织表的数据,减少碎片化。对于 InnoDB 存储引擎,这个命令也会尝试优化表,但效果可能不如 MyISAM 明显。
OPTIMIZE TABLE table_name;
对于 InnoDB 存储引擎,可以通过 ALTER TABLE
命令来重建表的索引,这通常比 OPTIMIZE TABLE 更有效。
ALTER TABLE table_name ENGINE=InnoDB;
定期执行 OPTIMIZE TABLE
或 ALTER TABLE
命令可以帮助维持索引的健康状况,减少碎片化。
需要注意的是,优化表的操作可能会消耗大量的系统资源,并且可能需要较长的时间来完成,特别是对于大型表。因此,在执行这些操作之前,最好在测试环境中进行评估,并在业务低峰时段进行。此外,确保在执行优化操作之前备份数据,以防万一出现问题。
参考黑马程序员mysql相关视频、MySQL索引18连问,谁能顶住
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。