MySql--存储引擎以及索引

MySQL体系结构

在这里插入图片描述
连接层:最上层是一些客户端和链接服务,包含本地socket 通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层:第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化,部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如 过程、函数等。在该层,服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化如确定表的查询的顺序,是否利用索引等,最后生成相应的执行操作。如果是select语句,服务器还会查询内部的缓存,如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能。
引擎层:存储引擎层, 存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能,这样我们可以根据自己的需要,来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。
存储层:数据存储层, 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上,并完成与存储引擎的交互。

存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。可以在创建表的时候,来指定选择的存储引擎,如果没有指定将自动选择默认的存储引擎。

#建表时指定存储引擎
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;

#查询当前数据库支持的存储引擎
show engines; 

create table my_myisam(
id int,
name varchar(10)
) engine = MyISAM ;

InnoDB存储引擎

  • 特点:
  • DML操作遵循ACID模型,支持事务;
  • 行级锁,提高并发访问性能;
  • 支持外键FOREIGN KEY约束,保证数据的完整性和正确性;
    逻辑存储结构:
    在这里插入图片描述
  • 表空间 : InnoDB存储引擎逻辑结构的最高层,ibd文件其实就是表空间文件,在表空间中可以包含多个Segment段。
  • 段 : 表空间是由各个段组成的, 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理,都是引擎自身完成,不需要人为对其控制,一个段中包含多个区。
  • 区 : 区是表空间的单元结构,每个区的大小为1M。 默认情况下, InnoDB存储引擎页大小为16K, 即一个区中一共有64个连续的页。
  • 页 : 页是组成区的最小单元,页也是InnoDB 存储引擎磁盘管理的最小单元,每个页的大小默认为 16KB。为了保证页的连续性,InnoDB 存储引擎每次从磁盘申请 4-5 个区。
  • 行 : InnoDB 存储引擎是面向行的,也就是说数据是按行进行存放的,在每一行中除了定义表时所指定的字段以外,还包含两个隐藏字段

MyISAM引擎

  • 特点:
  • 不支持事务,不支持外键
  • 支持表锁,不支持行锁
  • 访问速度快

Memory引擎

Memory引擎的表数据时存储在内存中的,由于受到硬件问题、或断电问题的影响,只能将这些表作为
临时表或缓存使用。
在这里插入图片描述存储引擎的选择

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

索引

  • 特点:
  • 优点:1、提高检索的效率,降低数据库的IO成本。
    2、通过索引对数据的排序,降低数据排序的成本以及对cpu的消耗。
  • 缺点:1、索引的存储童谣占用空间。
    2、索引提高了查询的速度,但降低了更新的速度。对表进行增删改时,效率降低。

索引结构

1、B+树索引结构
首先,如果将二叉树作为索引结构会存在以下缺点:顺序插入时,会形成一个链表,查询性能大大降低。大数据量情况下,层级较深,检索速度慢。而B-Tree,B树是一种多叉路衡查找树,相对于二叉树,B树每个节点可以有多个分支,即多叉。
在这里插入图片描述* 特点:
1、5阶的B树,每一个节点最多存储4个key,对应5个指针。
2、一旦节点存储的key数量到达5,就会裂变,中间元素向上分裂。
3、在B树中,非叶子节点和叶子节点都会存放数据
B+树:
在这里插入图片描述特点:
1、所有的数据都会出现在叶子节点。
2、叶子节点形成一个单向链表。
3、非叶子节点仅仅起到索引数据作用,具体的数据都是在叶子节点存放的。
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能,利于排序。
在这里插入图片描述2、Hash索引
哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决。
特点:
A. Hash索引只能用于对等比较(=,in),不支持范围查询(between,>,< ,…)
B. 无法利用索引完成排序操作
C. 查询效率高,通常(不存在hash冲突的情况)只需要一次检索就可以了,效率通常要高于B+tree索引
为什么InnoDB存储引擎选择使用B+tree索引结构?
A. 相对于二叉树,层级更少,搜索效率高;
B. 对于B-tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;
C. 相对Hash索引,B+tree支持范围匹配及排序操作;

常用索引

在这里插入图片描述在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:聚簇索引和非聚簇索引(二级索引)
在这里插入图片描述聚集索引选取规则:

  • 如果存在主键,主键索引就是聚集索引。
  • 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
  • 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
    回表查询: 这种先到二级索引中查找数据,找到主键值,然后再到聚集索引中根据主键值,获取数据的方式,就称之为回表查询。

索引语法

#创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;
#查看索引
SHOW INDEX FROM table_name ;
#删除索引
DROP INDEX index_name ON table_name ; 
#常规索引
CREATE INDEX idx_user_name ON tb_user(name); 
#唯一索引
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone); 
#联合索引
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status); 

索引的使用

1、最左前缀法则
如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。如果跳跃某一列,索引将会部分失效(后面的字段索引失效)。
2、范围查询
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。

  • 索引失效情况:
  • 1、 索引列运算:不要在索引列上进行运算操作, 索引将失效。
  • 2、字符串不加引号:字符串类型字段使用时,不加引号,索引将失效。
  • 3、模糊查询:如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
  • 4、用or分割开的条件: 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
  • ==覆盖索引:==尽量使用覆盖索引,减少select *。 覆盖索引是指 查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到 。
  • ==前缀索引:==当字段类型为字符串(varchar,text,longtext等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO, 影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
#前缀索引的建立
create index idx_xxxx on table_name(column(n)) ; 

前缀长度:可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,
索引选择性越高则查询效率越高, 唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。

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

索引设计原则

索引设计原则

  • 1). 针对于数据量较大,且查询比较频繁的表建立索引。
  • 2). 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
  • 3). 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
  • 4). 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
  • 5). 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,
    避免回表,提高查询效率。
  • 6). 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
  • 7). 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。

视频学习网址: 黑马程序员的Mysql教程

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值