MySQL一些基础知识及存储引擎比较

2018-01-09

1. 一些基础知识

1.1 索引类型

由存储引擎层实现

索引的优点：

大大减少了服务器需要扫描的数据量
可以帮助服务器避免排序和临时表
可以将随机I/O变成顺序I/O

1.1.1 B-Tree索引

一般没有特定指明类型时，多半是B-Tree索引；除了Archive引擎，大多数引擎都支持B-Tree

NDB集群存储引擎内部实际使用T-Tree结构存储，InnoDB则使用B+Tree

MyISAM使用前缀压缩技术使索引更小，InnoDB则按原数据格式进行存储

B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据，适合于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找

适用查询类型：全值匹配，匹配最左前缀，匹配列前缀，匹配范围值，精确匹配某一列并范围匹配另一列，只访问索引的查询

一些限制，MySQL优化器和存储引擎使用索引方式导致的：

如果不是按照索引的最左列开始查找，则无法使用索引
不能跳过索引的列
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找

1.1.2 哈希索引

在MySQL中，只有Memory引擎显示支持哈希索引，且支持非唯一哈希索引

NDB集群引擎也支持唯一哈希索引（#TODO 待了解）

基于哈希表实现，只有精确匹配索引所有列的查询才有效

CREATE TABLE testhash (
	fname VARCHAR(50) NOT NULL,
  	lname VARCHAR(50) NOT NULL,
  	KEY USING HASH(fname)
)

对每一行数据，存储引擎都会对所有的索引列计算一个哈希码

哈希索引的限制：

哈希索引只包含哈希值和行指针，不存储字段值，不能使用索引中的值来避免读取行
不是按照索引值顺序存储的，无法用于排序
不支持部分索引列匹配查找
只支持等值比较查询，也不支持范围查询
访问哈希索引的数据非常快，除非有很多哈希冲突
如果哈希冲突很多的话，一些索引维护操作的代价也会很高

数据仓库应用中，有一种经典的“星型“schema，需要关联很多查询表，哈希索引就非常适合查找表的需求

1.1.3 空间数据索引（R-Tree）

1.1.4 全文索引

1.1.5 其他索引类别

1.2 最左前缀列

如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列

1.3 ACID含义

事务就是一组原子性的SQL查询，或者说一个独立的工作单元

1.3.1 A Atomicity 原子性

一个事务必须被视为一个不可分割的最小工作单位，整个事务中所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中一部分

1.3.2 C Consistency 一致性

事务总是从一个一致性状态转移到另一个一致性状态

1.3.3 I Isolation 隔离性

一个事务所做的修改在最终提交之前，对其他事务是不可见的

1.3.4 D Durability 持久性

一旦事务提交，则其所做的修改就会永远保存到数据库中

Qian Hu