[黑马程序员]进阶篇-存储引擎+索引

进阶篇 存储引擎+索引

存储引擎

• 

连接层

• 主要完成一些类似于连接处理、授权认证、及相关的安全方案
• 在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程

服务层

• 并完成缓存的查询，SQL的分析和优化
• 部分内置函数的执行
• 所有跨存储引擎的功能也在这一层实现
• 服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等
• 是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能

引擎层

• 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信

存储层

• 数据存储层， 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互

存储引擎介绍

• mysql数据库的核心
• 是存储数据、建立索引、更新/查询数据等技术的实现方式
• 存储引擎是基于表,不是基于库
• 建立表时指定存储引擎

CREATE TABLE 表名( 字段1 字段1类型 [ COMMENT 字段1注释 ] , ...... 字段n 字段n类型 [COMMENT 字段n注释 ] ) ENGINE = INNODB [ COMMENT 表注释 ] ;

• 查询当前数据库支持的存储引擎

show engines;

• 查询建表语句 — 默认存储引擎: InnoDB

show create table account;

存储引擎特点

InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的MySQL 存储引擎

• 特点

  • DML操作遵循ACID模型，支持事务；
  • 行级锁，提高并发访问性能；
  • 支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

• 文件

  • xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的 、sdi-新版的）、数据和索引。

  show variables like 'innodb_file_per_table'; # 如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件
  

  • ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息

• 逻辑存储结构

  • 
  • 表空间: InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段
  • 段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区
  • 区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页
  • 页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区
  • 行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)

MyISAM

• 特点

  • 不支持事务，不支持外键
  • 支持表锁，不支持行锁
  • 访问速度快

• 文件

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

Memory

• 特点
  • 内存存放
  • hash索引（默认）
• 文件
  • xxx.sdi：存储表结构信息

对比

• 

存储引擎选择

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

索引

• 索引（index）是帮助MySQL高效获取数据的数据结构(有序)。

特点

• 

索引结构

• B+Tree索引
• Hash索引
• R-tree(空间索引）
• Full-text(全文索引)

不同引擎支持的索引结构

• 

二叉树

• 顺序插入时，会形成一个链表，查询性能大大降低
• 大数据量情况下，层级较深，检索速度慢

红黑树

• 红黑树是一颗自平衡二叉树，那这样即使是顺序插入数
  据，最终形成的数据结构也是一颗平衡的二叉树
• 缺点
  • 大数据量情况下，层级较深，检索速度慢

B-Tree

• B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉
• 5阶的B树，每一个节点最多存储4个key，对应5个指针
• 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂
• 在B树中，非叶子节点和叶子节点都会存放数据

B+Tree

• 特点

  • 所有的数据都会出现在叶子节点。
  • 叶子节点形成一个单向链表
  • 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的

• mysql中的b+tree

  • 原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序

Hash

• 特点

  • Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
  • 无法利用索引完成排序操作
  • 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常高于B+tree索 引

• 存储引擎支持

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

innodb 为什么使用b+tree

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

索引分类

索引分类

• 

聚集索引&二级索引

• 
• 聚集索引选取规则
  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引
• 回表查询:这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询
• InnoDB主键索引的B+tree高度为多高呢
  • 如果树的高度为2，则可以存储 18000 多条记录
  • 如果树的高度为3，则可以存储 2200w 左右的记录

索引语法

• 创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;

• 查看

SHOW INDEX FROM table_name ;

• 删除

DROP INDEX index_name ON table_name ;