mysql 主要引擎

1
2
3
4
5
6
7
8
9
CSV	YES	CSV storage engine	NO	NO	NO
MRG_MYISAM YES Collection of identical MyISAM tables NO NO NO
MyISAM YES MyISAM storage engine NO NO NO
BLACKHOLE YES /dev/null storage engine (anything you write to it disappears) NO NO NO
InnoDB DEFAULT Supports transactions, row-level locking, and foreign keys YES YES YES
PERFORMANCE_SCHEMA YES Performance Schema NO NO NO
ARCHIVE YES Archive storage engine NO NO NO
MEMORY YES Hash based, stored in memory, useful for temporary tables NO NO NO
FEDERATED NO Federated MySQL storage engine
  • 常用的
    1
    2
    3
    4
    5
    myisam <=5.5 默认 
    innodb >5.5 默认
    blackhole 主从同步时候 常用blackhole 做代理binlog分发减少master压力
    memory 默认hash 索引
    BDB

摘自<<数据库开发、优化与管理维护>>

image

  • myisam 优缺点
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
优点
1. 5.5以前默认存储引擎
2. 访问速度快
3. 对事务完整性要求不高,或者以select /insert 为主的应用 (文章、帖子、日志类)都可使用
4. 物理存储分为 .frm 存储定义(一种数据结构) .myd 存储数据 .myi 存储索引( 数据与索引分开存储 可以分别指定目录 平均分布磁盘IO,获得更快的速度)
5. 支持静态表(固定长度)、动态表、压缩表 存储格式
6. 支持全文索引
7. 并发读写互斥的, 默认写请求优先级高
8. 支持空间数据对象
9. 数据拷贝恢复容易且快速
10. 组合索引可以不是第一列
11. 支持表级锁,为CUAD给表自动加锁,不需额外干预
12. 保存现有总记录行数
13. 非聚簇索引,且叶子节点指向对应数据块的指针
14. 主健索引与二级索引无区别都指向物理行号,叶子节点存放的是列值和行号,对应数据的物理地址
缺点
1. 不支持事务、不支持行级锁
2. 不支持外键
3. 不支持数据缓存
  • Innodb 优缺点

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    1. 支持事务 ACID 
    2. 支持全文索引 >=5.6
    3. 外键约束
    4. 提供共享表与多表存储方式 .frm .ibd 物理文件
    5. 支持数据缓存
    6. 支持索引缓存
    7. 支持行级锁
    8. 支持sphinx插件全文索引
    9. 主键索引叶子节点直接存储数据记录,key为主键,称为聚簇索引
    10. 二级索引包叶子节点含主键和key字段(所以key不能太长,且保证唯一)而不是行指针,即二级索引先通过找到主键值,然后通过主键值才能找到完整记录 ,走两个B树(可以通过索引覆盖,让二级索引覆盖要查询的字段)

    缺点
    1. 单表限制64TB,一般受制于操作系统文件大小2G
    2. 组合索引,自动增长列必须是组合索引第一列
    3. 不保存现有记录行数
  • 聚簇索引

1
2
3
4
5
6
7
8
9
10
11
12
13
Innodb 数据文件本身也是索引文件,
索引的顺序就是数据存放的顺序,一张表就只能一个聚簇索引
一般来讲,聚簇索引查询要比非聚簇索引要高效很多,特别是范围查询时候

优点:
1. 数据行和索引保存在同一B-Tree中,通过聚簇索引可以直接获取数据而非聚簇索引要多次IO
2. 聚簇索引对主键范围查询的效率很高,因为存放时候按主键顺序存放
3. 二级索引使用索引覆盖可以直接使用叶节点的主键值
缺点:
1. 插入速度依赖插入顺序,顺序导入Innodb引擎表是最快的
2. 插入新行或更新主键导致聚集索引“页分裂”问题,占用更多磁盘空间
3. 导致全表扫描
4. 二级索引两次B-Tree查找问题
  • 非聚簇索引
1
2
3
4
myisam 数据文件与索引文件分离
非聚簇索引指的就是数据行和对应的键不存在一起,
非聚簇索引中叶子结点一般存的是数据行的引用,
mysql myisam 引擎主键索引与二级索引叶子节点无任何区别,存储主键值对应物理地址位置
mysql获取数据的数据结构 Btree索引和Hash索引

Innodb和MyISAM默认的索引是Btree索引;而Mermory默认的索引是Hash索引

  • Hash索引

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    本质上是对要进行hash的列就行散列运算,维护hash数组

    优点:
    一次定位,精确匹配数据,效率高 ,适合key-value简单查询
    缺点:
    1. 每次全部扫表
    2. hash比较的是计算值,对范围查询无解
    3. hash值安装顺序排序,但是不能保证hash值映射的真正数据也是按此顺序排序,无法加速排序操作
    4. 不能用于部分索引健搜索数据,因为组合索引在计算hash值是一起计算的
    5. hash值大量重复且数据量大时,其检索效率没有Btree高
  • Btree 索引(mysql 实际是B+Tree 改良结构)(Blanced Tree 不是二叉树)

    注:B+树 在myisam和innodb实现的存储结构区别

平衡二叉树是基于二分法的策略提高数据的查找速度的二叉树的数据结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
查询算法:
1. 顺序查找 (linear search ) O(n)时间复杂度
2. 二分查找 (binary search ) 将数据有序排列,数据分半执行数据查找
3. 二叉树查找(binary tree search)O(log2N) 既有链表的好处又有数组的好处,是综合的折中方案
4. 链表 删除与插入元素很快,但查找很慢
5. 数组的搜索比较方便,但删除和插入元素较麻烦


在数据之外,数据库系统还维护着满足特定查找算法的数据结构,
这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。
这种数据结构,就是索引

大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构,主要为排序和检索的效率


二叉树: 综合了数组和链表的优点和缺点(数组插入效率低,链表查找效率低),
支持动态的插入和查找,保证操作在O(height)时间,如果数据有序(倾斜)发生退化为线性查找 O(N)时间复杂度,平均时间复杂度O(H)

红黑树: 属于二叉树,是平衡检索树,为避免不平衡,增加颜色属性来保持平衡 旋转、高度有限,查询和普通二叉树耗时可以忽略,插入和删除操作和普通的二叉搜索树时间上的开销略有增加,因为要执行颜色变换和旋转

B-Tree:(是一种平衡的多路查找树) 有序数组+平衡多叉树 ,基于范围查询效率低
B+Tree: (B-树的改良升级)序数组链表+平衡多叉树, 叶子节点包含了全部关键字的信息,且叶子节点本身依关键字自小而大顺序链接。所以任何关键字的查找都必须走一条从根节点到叶子节点的路,支持基于范围的查询。

对比B-Tree /B+Tree
(1)B+跟B树不同B+树的非叶子节点不保存关键字记录的指针,这样使得B+树每个节点所能保存的关键字大大增加,树的层级更少所以查询数据更快;

(2)B+树叶子节点保存了父节点的所有关键字和关键字记录的指针,每个叶子节点的关键字从小到大链接;

(3)B+树的根节点关键字数量和其子节点个数相等;

(4)B+的非叶子节点只进行数据索引,不会存实际的关键字记录的指针,所有数据地址必须要到叶子节点才能获取到,所以每次数据查询的次数都一样,查询速度更稳定;

综述 vs 优化策略

  1. 数据库性能瓶颈大部分来自于磁盘IO
  2. 大部分情况下磁盘顺序读写比随机读写的性能要高
  3. 基本存储结构:磁盘被分为许多块(block、page),块与块之间以链表关联,数据是以行为单位存放在块中,磁盘一次IO一般至少读出或写入一个完整的块
  4. 基础索引算法无外乎 B+Tree /Hash/RTree/Full-text
  5. 常用引擎 myisam innodb
  6. 操作访问 CURD
  • 了解以上有助于了解sql原理及执行流程为管理优化sql提供不同策略保障