存储引擎和索引（三）

MySQL
notes
Publish Date:   2023-08-06
Word Count:   3.8k
Read Count:  

存储引擎和索引（三）

mysql体系结构

  • 连接层：最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
  • 服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。
  • 引擎层：存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能,这样我们可以根据自己的需要，来选取合适的存储引擎。
  • 存储层主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

存储引擎

存储引擎就是存储数据，建立索引，更新/查询数据等技术的实现方式。

存储引擎是基于表的，不是基于库的，存储引擎也被称为表类型。

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

1
show engines;

创建表时，指定存储引擎（默认是InnoDB）：

1
2
3
4
create table 表名(
	字段1 类型 [comment 字段1注释],
	字段2 类型 [comment 字段2注释]
)engine=引擎 [comment 表注释];

特点

InnoDB

InnoDB是一种高可靠性和高性能的通用存储引擎，在mysql5.5之后，InnoDB是默认的mysql存储引擎。

特点：

  1. DML操作遵循ACID模型，支持事物
  2. 行级锁，提高并发访问性能
  3. 支持外键foreign key约束，保证数据的完整性和正确性

文件：xxx.ibd

xxx代表的是表名，InnoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm,sdi），数据和索引（参数：innodb_file_per_table）

逻辑结构：

  • TableSpace:表空间
  • Segment：段
  • Extent：区
  • Page：页
  • Row：行

MyISAM

介绍：MyISAM是mysql早期默认存储引擎。

特点：

  1. 不支持事务，不支持外键
  2. 支持表锁，不支持行锁
  3. 访问速度快

文件：

  • xxx.sdi：存储表结构信息
  • xxx.MYD：存储数据
  • xxx.MYI：存储索引

Memory

介绍：Memory引擎的表数据时存储在内存中，由于受到硬件文件，或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点：

  1. 内存存放
  2. hash索引（默认）

文件：

xxx.sdi：存储表结构信息

选择

根据应用系统的特点选择合适的存储引擎，对于复杂的应用的系统，可根据实际情况选择多种存储引擎进行组合。

  • InnoDB：绝大部分情况下的选择，是mysql的默认存储引擎，支持事务，外键，若应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多的更新，删除操作，那么InnoDB存储引擎是比较合适的选择
  • MyISAM：若应用是以读操作和插入操作为主，只有很少的更新和删除操作，并对事务的完整性，并发性要求不是很高，那选择这个存储引擎时非常合适的
  • MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

索引

MySQL中的索引（index）是一种数据结构，用于加快数据库的查询操作。索引可以理解为一个快速访问数据的目录，类似于书籍的目录，它可以通过指向存储数据的物理位置，使得数据库的查询操作更加高效和快速。

优缺点

优点：

  • 提高数据检索的效率，降低数据库IO成本
  • 通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点：

  • 索引列也是要占用空间的
  • 索引降低更新表的速度，例如对表进行增删改时，效率降低

索引结构

mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

  • B+Tree索引
    • 最常见的索引类型，大部分引擎都支持B+树索引
  • Hash索引
    • 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
  • R-tree
    • 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
  • Full-text
    • 全文索引是一种建立倒排索引，快速匹配文档的方式，类似于Lucene，Solr，ES

引擎对索引的支持情况：

  • B+tree
    • InnoDB：支持
    • MyISAM：支持
    • Memory：支持
  • Hash
    • InnoDB：不支持
    • MyISAM：不支持
    • Memory：支持
  • R-tree
    • InnoDB：不支持
    • MyISAM：支持
    • Memory：不支持
  • Full-text
    • InnoDB：5.6版本之后支持
    • MyISAM：支持
    • Memory：不支持

BTree

多路平衡查找树，以一个最大度数（树的度数是一个节点的子节点个数）为5的BTree为例（每个节点最多存储4个key，5个指针）

B+Tree

相对于BTree的区别：

  • 所有数据都会出现在叶子节点
  • 叶子节点形成一个单链表

MySQL索引数据结构对经典的B+Tree进行优化，在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

Hash

哈希索引就是采用一定的Hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

Hash索引特点：

  1. Hash索引只能用于对等标胶（=,in），不支持范围查询（between,>,<,…）
  2. 无法利用索引完成排序操作
  3. 查询效率高，通常只需要一次检索就可以了（若是出现哈希碰撞，便不止一次），效率通常要高于B+Tree索引

存储引擎支持：在mysql中，支持hash索引的是Memory引擎，而InnoDB具有自适应的hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

InnoDB选择B+tree索引原因：

  • 相对于二叉树，层级更少，搜索效率高
  • 相对图BTree，无论是叶子节点或是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
  • 相对Hash索引，B+Tree支持范围匹配及排序操作

索引分类

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

在InnoDB存储索引中，根据索引的存储形式，又可分为以下两种：

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

索引语法

创建索引：

create [unique|fulltext] index 索引名 on 表名（字段1,字段2…）

查看索引：

show index from 表名

删除索引：

drop index 索引名 on 表名

SQL性能分析

查看执行频次

查看当前数据库的insert，delete，update，select访问频次：

show global status like ‘Com七个下划线’;

慢查询日志

慢查询日志记录所有执行时间超过指定参数（long_query_time，单位秒，默认10秒）的所有SQL语句的日志。

mysql的慢查询日志默认没有开启(可用该语句查询是否开启：show variables like ‘slow_query_log’)，需在mysql的配置文件（/etc/my.cnf）中配置如下信息：

  • 开启MySQL慢日志查询开关
    • slow_query_log=1
  • 设置慢日志的时间为2秒，sql语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
    • long_query_time=2

配置完毕之后，通过以下执行重新启动mysql服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log

profile

  • show profiles能够在sql优化时帮助我们了解时间都耗费到哪里去了，通过have_profiling参数，都能够看到当前mysql是否支持profile操作：
    • select @@have_profiling;
  • 默认profiling是关闭的（可用该指令查询：select @@profiling），可以通过set语句在session/global级别开启profiling：
    • set profiling=1;
  • 查看每一条sql的耗时基本情况
    • show profiles;
  • 查看指定query_id的sql语句各个阶段的耗时情况
    • show profile for query query_id
  • 查看指定query_id的sql语句CPU的使用情况
    • show profile cpu for query query_id;

explain执行计划

explain或desc命令获取mysql如何执行select语句的信息，包括select语句执行过程中表如何连接和连接的顺序。

语法：

explain select 字段列表 from 表名 where 条件;

explain执行计划各字段含义：

  • id
    • select查询的序列号，表示查询中执行select子句或是操作表的顺序（id相同，执行顺序从上到下;id不同，值越大，越先执行）
  • select_type
    • 表示select的类型，常见的取值有simple（简单表，即不使用表连接或子查询），primary（主查询，即外层的查询），union（union中的第二个或后面的查询语句），subquery（select/where之后包含子查询）等
  • type
    • 表示连接类型，性能由好到差的连接类型为null，system，const，eq_ref，ref，range，index，all
  • possible_key
    • 显示可能应用在这张表上的索引，一个或多个
  • key
    • 实际使用的索引，若为null，则没有使用索引
  • key_len
    • 表示索引中使用的字节数，该值为索引字段的最大可能长度，而非实际使用长度，在不损失精确性的前提下，长度越短越好
  • rows
    • mysql认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
  • filtered
    • 表示返回结果的行数需读取行的百分比，filtered的值越大越好

索引使用

最左前缀法则：

若索引多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，且不跳过索引中的列。

若跳过某一列，索引将部分失效（后面的字段索引失效）

范围查询：

联合索引中，出现范围查询（>，<）范围查询右侧的列索引失效。

索引列运算：

不要在索引列上进行运算操作，索引将失效。

字符串不加引号：

字符串类型字段使用时，不加引号，索引将失效。

模糊查询：

若仅仅是尾部模糊匹配，索引不会失效，若是头部模糊匹配，索引失效。

or连接的条件：

用or分割开的条件，若or前的条件中的列有索引，而后面的列中无索引，那涉及的索引都不会被用到。

数据分布影响：

若mysql评估使用索引比全表更慢，则不使用索引。

SQL提示：

是优化数据库的一个重要手段，就是在sql语句中加入一些人为的提示来达到优化操作的目的。

  • use index();

    • 建议数据库用某个索引
    • explain select * from tb_user use index(tb_pro) where profession=”软件工程”;

  • ignore index();

    • 使数据库忽视某个索引
    • explain select * from tb_user ignore index(tb_pro) where profession=”软件工程”;

  • force index();

    • 强迫数据库用某个索引
    • explain select * from tb_user force index(tb_pro) where profession=”软件工程”;

覆盖索引：

尽量使用覆盖索引（查询使用了索引，并需要返回的列，在该索引中已经全部能够找到），减少select *

  • using index condition
    • 查找使用了索引，但需要回表查询数据
  • using where;using index
    • 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

前缀索引：

当字段类型为字符串（varchar，text等）时，有时需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以只将字符串的一部分前缀，建立索引，可节约索引空间，从而提高索引效率。

语法：create index idx_xxxx on table_name(字段(n));

选择性是指不重复的索引值（基数）和数据表的记录总数的比值。

前缀长度可根据索引的选择性来决定。 索引选择性越高则查询效率越高，唯一索引的选择性是1.是最好的索引选择性，性能也是最好的。 

1
2
3
select count(distinct email)/count(*) from tb_user;  

select count(distinct substring(email,1,5))/count(*) from tb_user;

单列索引与联合索引：

  • 单列索引即一个索引只包含单个列
  • 联合索引即一个索引包含了多个列
  • 在业务场景中，若存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引

多条件联合查询时，mysql优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

索引设计原则

  • 针对于数据量较大,且查询比较频繁的表建立索引。
  • 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
  • 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  • 若是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  • 尽量使用联合索引，减少单列索引,查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  • 要控制索引的数量,索引并不是多多益善,索引越多,雄护索引结构的代价也就越大,会影响增删改的效率。
  • 若索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。
Author: ljs
Link: http://example.com/2023/08/06/mysql3/
Reprint policy: All articles in this blog are used except for special statements CC BY 4.0 reprint polocy. If reproduced, please indicate source ljs !
MySQL
赏
评论
  TOC