Mysql 经典知识点

注意:这是个人笔记,如果有不正确的地方可以在评论指出,大神勿喷

锁

锁策略

表锁(table lock)

对整张表加读写锁,读锁可以之间不会相互阻塞,但是当有写锁时,任何用户都不可以获取读锁
并且写锁比读锁的优先级高,一个写的命令可能被插入读命令之前.

行级锁(row lock)

行级锁对每一行添加读写锁,这样可以实现最大程度地支持并发处理,但是也有最大的锁开销.

事务

原子性(atomicity)

一个事务时最小单元,并且要么提交成功,否则失败回滚,不可能只执行其中一部分操作

一致性(consistency)

关注数据的可见性，中间状态的数据对外部不可见，只有最初状态和最终状态的数据对外可见

隔离性(isolation)

在一个事务对数据操作时,别的事务不能干扰这个操作

持久性(durability)

一旦事务提交,则其做的修改就永久保存到数据库

隔离级别

READ UNCOMMITTED(未提交读)

事务中的修改,即使没有提交,对其他事务也是可见的,也被称为脏读

READ COMMITTED(提交读)

一个事务从开始修改到提交所做的修改其他事务都看不见,也成为不可重复读

REPEATABLE READ(可重复读)

可重复读解决了脏读的问题,该级别保证了一个事务多次读取同样的数据返回结果相同,但也会产生另一个问题幻读

SEIALIZABLE(可串行化)

可串行化是最高的隔离级别.该级别会在每一行都加锁,所以会导致大量的的超时和锁争用问题.

多版本并发控制(MVCC)

可以当作是行级锁的一个变种,MVCC是通过保存数据时的快照来实现的,所以不管需要执行多长时间,每个事务看到的数据都是相同的.根据事务开始的时间不同,每个事务对应一张表,同一时刻看到的数据可能是不同的.
不同的数据库引擎实现不同的MVCC,典型的有乐观并发控制和悲观并发控制
MVCC只在READ COMMITTED 和 REPEATABLE READ这两个级别下工作

InnoDB

通过为每一行后面增加两个隐藏列来实现MVCC,分别是创建时间和过期时间
**注意:**这里的时间指的是系统的版号,可以说就是每个事务有不同的版号.
在可重复读级别下,不同命令下,MVCC具体的操作:

select

InnoDB会根据下面两点检查行:

• 只会查找版本号早于当前版本号的行,这样可以确保读到的行要么是之前的数据行,要么是自身事务修改后的行
• 行的删除版本要么未定义,要么大于当前版本号,确保读到的数据行是在事务开始前未删除的.

insert

InnoDB为新插入的行的创建时间中保存当前版本号

delete

InnoDB为删除的每行的删除时间中保存当前版本号

update

InnoDB为修改的行保存当前版本号,并且修改该行原来的过期时间为当前版本号

优缺点

**优点:**这样查询起来基本不需要添加锁.
**缺点:**每一行都添加了两个列,增加额外的存储空间.

存储引擎

InnoDB引擎

Mysql默认的事务型引擎.
InnoDB的数据存储在表空间(tablespace)中,表空间由InnoDB管理的一系列数据文件组成,
InnoDB采用MVCC来实现高并发,并且使用四个隔离级别.
InnoDB表是基于聚簇索引建立的.
InnoDB内部还有很多优化,包括从可预测性预读,自适应哈希索引,插入缓存区等.

MyISAM引擎

MyISAM提供大量特性,包括全文索引,压缩,空间函数等,不过不支持事务和行级锁.有一个缺陷是崩溃后无法安全恢复.
MyISAM会将表存储在数据文件和索引文件,分别以.MYD和.MYI为扩展名.可以包含动态和静态的行.

特性

加锁与并发

MyISAM对表加锁,而不是行.读取时加共享锁,写入时加排他锁.在表有读取查询的同时,也可以往表中插入数据,这也叫并发插入.

修复

可以手工和自动执行检查和修复操作. 修复与事务恢复以及崩溃恢复是不同的.

索引特性

即使是BLOB和TEXT字段,也支持基于其前400个字符创建索引.同时也支持全文索引.

延迟更新索引键

可以通过指定DELAY_KEY_WRITE选项使用该特性.
每次修改后,不会立即将修改的索引数据写入磁盘,而是放入内存中的键缓冲区.只有在清理键缓存区或者关闭表的时候才会写入.
如果数据库崩溃会造成索引损坏,需要执行修复操作. 可以全局设置也可以对单表设置.

压缩表

如果在表创建和导入数据后,不会做数据的修改操作,可以使用MyISAM压缩表.
可以使用myisampack对MyISAM表进行压缩,如果要修改数据,可以解压->修改数据->压缩.
压缩表可以极大地减少磁盘空间占用,因此可以减少磁盘I/O.压缩表也支持索引,不过索引只是可读的.

其他引擎

Archive 引擎

Archive只支持Select和Insert操作.
Archive引擎会缓存所有写并利用zlib对插入进行压缩,所以会有更低的磁盘I/O,但每次select就会全盘扫描.
Archive引擎支持行级锁和专用的缓存区,所以可以实现高并发的插入.在查询时,会阻止其他select语句进行查询,避免幻读.该引擎在插入时也会对读操作是不可见的,但这只是模仿MVCC模式,并不时事务型引擎.
适用场景:
适合用作日志和数据采集应用

Blackhole 引擎

Blackhole引擎并没有存储机制,会丢弃所有数据,但是服务器会记录Blackhole表的日志.
适用场景:
可以用于复制数据到备库,或者简单地记录到日志

CSV 引擎

CSV引擎可以将普通的CSV文件作为MYSQL表来处理.但这种表不支持索引.
适用场景:
可以作为数据转换的中间表.提供将CSV文件中的数据转为MYSQL表中数据.

Federated 引擎

访问其他Mysql服务的一个代理,会创建一个到远程Mysql服务器的客户端连接,并将查询传输到远程服务器执行,然后提取或者发送需要的数据.

Memory 引擎

Memory表所有的数据都存储在内存中,所以不需要进行磁盘I/O,因此比MyISAM引擎快一个数量级.
如果你需要快速访问数据,并且这些数据不需要修改,重启后丢失也没有关系,那么你可以选择使用Memory引擎.
Memory引擎在重启后,表结构并不丢失,只会丢失数据.
特性:

• 支持Hash索引,查找速度快.
• 表级锁,并发写入性能较低.
• 不支持BLOB和TEXT类型,并且每行长度固定.
  适用场景:
• 用于查找和映射表,例如邮编和区映射的表.
• 用于缓存周期性聚合数据的结果.
• 用于保存数据分析中产生的中间数据.

NDB 集群引擎

MySQL服务器 NDB集群存储引擎 以及分布式 share-nothing的 容灾的 高可用的 NDB数据库的组合,被称为Mysql集群.

第三方存储引擎

OLTP类引擎

• XtraDB引擎 基于InnoDB引擎改进版本,改进点在性能,可测量性和操作灵活性方面
• PBXT引擎 与InnoDB引擎类似.支持引擎级别的复制,外键约束,并且以一种独特的复杂的架构对SSD提供支持,对较大的值比如BLOB和TEXT类型优化.
• TokuDB引擎 使用一种叫分形树的新型索引数据结构.该结构缓存无关,所以大小超过内存性能也不会降低,也没有内存生命周期和碎片的问题.TokuDB是一种大数据存储引擎,因为其拥有很高的压缩比.

面向列的存储引擎

Infobright引擎是最有名的面向列的存储引擎.该引擎是为了数据分析和数据仓库应用设计的.

索引

索引分为聚簇索引和非聚簇索引,聚簇索引的索引顺序就是物理顺序,非聚簇索引的索引顺序与物理顺序无关.就好像一本字典,你去找"刘"这个字,你通过L查找就是聚簇索引.如果你直接通过"刘"的页码找到这个字就是非聚簇索引.

下面是聚簇索引和非聚簇索引需要设置的列的区别:

注意:
聚簇索引在一个表中是唯一的.如果你创建了主键,那么会默认将聚簇索引设置为主键,如果你不想让主键是聚簇索引,那么就在主键设置之前分配好聚簇索引.

B-Tree索引

存储引擎以B-Tree数据结构存储索引,B-Tree索引列是顺序存储的,所以很适合范围查找.B-Tree索引适用于全值匹配 匹配最左前缀 匹配列前缀 匹配范围值 精确匹配一列并范围匹配另一列 只访问索引的查询
限制:

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

哈希索引

基于哈希表实现索引功能.对于每一行数据,索引列都会计算出一个哈希码,哈希码是一个比较小的数值,并且不同的键值对应的哈希码不同.
Mysql中只有Memory引擎显示支持哈希索引,也是默认索引类型.
限制:

• 哈希索引只包含哈希值和行指针,不存储字段,所以不能使用索引中的值避免访问行数据.
• 哈希索引并不是按照索引值顺序存储,所以不能用于排序.
• 哈希索引并不支持部分索引列进行查找数据,因为哈希索引是用所有索引列计算哈希值.比如哈希索引(A,B),你只拿A是无法进行索引查询的.
• 哈希索引只支持等值查询 = IN( ) <=> .也不支持范围查询.
• 访问哈希索引的速度非常快,除非出现了哈希冲突,存储引擎必须遍历链表中所有行指针,找到符合条件的行

InnoDB引擎有一个功能叫"自适应哈希索引",当InnoDB发现某些索引列经常使用,那么会基于B-Tree索引基础上再建立一个哈希索引.

空间数据索引(R-Tree)

MyISAM表支持空间索引,可以用作地理数据存储.空间索引会根据所有维度进行查找.

全文索引

全文索引是一种特殊的索引类型,更像是在文本中匹配关键词.有很多需要注意的细节,比如停用词,词干和复数,布尔搜索等.

视图

介绍

视图是虚拟的表,与包含数据的表不同,视图只包含对数据的查询,不包含任何列或数据.视图可以直接当作表操作.

个人理解,视图就相当于把操作封装起来,如果经常使用一个查询,就可以设置为视图.

对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

---

本书参考<<高性能MySQL>> (第三版)