MySQL数据库

本文总阅读量次

[TOC]

数据库

MySQL事务

事务的概念、隔离级别、死锁

见「数据库系统原理」

事务日志

事务日志可以帮助提高事务的效率，使用事务日志，在储存引擎上修改表时数据只需要修改其内存拷贝，再把修改行为记录到硬盘上的持久事务中，而不用每次都将修改的数据本身持久到磁盘

事务日志采用追加方式，因此日志操作是顺序I/O。采用事务日志速度快。事务日志持久后，内存中被修改的数据主键被刷回磁盘，大多数储存引擎都是这么实现的。这被称为预写日志，修改数据两次写磁盘

如果数据的修改已经持久化，而数据本身没被写入磁盘，系统崩溃后储存引擎在重启时自动恢复修改的数据。

MySQL自动提交

MySQL默认采用自动提交，不是显示的开始一个事务，每个查询都被当成一个事务提交。

有一些命令会强制提交当前的活动事务，如Alter

MySQL可以设置所有的四个隔离级别

1
set session transaction isolation level read committed

事务中混用储存引擎

MySQL不管理事务，事务由引擎实现，所以在一个中混用引擎是不可靠的

事务回滚对于非事务型表（如MyISAM）无法撤销，数据库会失去一致性

隐式和显示锁定

InnoDB采用两阶段锁定协议，事务执行过程中随时可以执行锁定，只有在commit和rollback时释放，并且所有的锁在同一时刻释放，这些属于隐式锁，InnoDB在需要时自动加锁

同样InnoDB支持显示加锁，一般来说显示使用LOCK TABLES语句不但没有必要还会严重影响性能，实际上InnoDB行锁性能更好

MySQL并发控制

读写锁

详见「数据库系统原理」

锁粒度

一种提高资源并发的方式是让锁的对象更有选择性。尽量只锁需要修改的部分数据，而不是所有的资源。任何时候，互相之间不发生冲突，锁的数量越少并发程度越高。

加锁也需要消耗资源，获得锁、检查锁、释放锁都会增大开销。所谓锁策略，就是在所的开销和安全性之间寻求一种平衡。大多数商业数据库没有提供更多的选择，一般是在表上施加行级锁。而MySQL提供可多种选择，每种储存引擎都可以实现自己的锁策略和锁粒度

  • 表锁

表锁是MySQL中最基本的锁策略，并且是开销最小的锁。它锁定整张表，用户对表的写操作都需要获得整个锁

尽管储存引擎管理自己的锁，MySQL本身还是会使用有效的表锁，例如Alter table语句使用表锁

  • 行级锁

行级锁可以最大程度的支持并发处理（最大的锁开销），InnoDB及XtraDB实现了行级锁，行级锁只在引擎层面实现，而服务器层面并不了解锁的情况

MySQL多版本并发控制（MVCC）

MySQL实现了自己的多版本并发控制（MVCC），可以认为MVCC是行级锁的一个变种，它在很多情况下避免了加锁操作，因此开销更低。

MVCC的实现是通过保存数据在某个时间点的快照来实现的。不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，事务对同一张表，同一时刻数据可能是不一样的

InnoDB的MVCC是通过在每个记录后面保存两个隐藏的列来实现的。这两个列一个保存了行的创建时间，一个保存了行的过期时间。当然储存的是系统版本号。每开始一个事务，系统版本号都会递增，事务开始时刻的系统版本号作为事务的版本号。

  • SELECT

    InnoDB会根据以下两个条件检查记录

    A. InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行要么在开始前存在，要么自身插入或修改

    B. 行的删除版本要么未定义，要么大于当前事务版本号。这样可以确保事务读到的行在事务开始前未被删除

  • INSERT

    InnoDB为新插入的每一行保存当前系统的版本号

  • DELETE

    InnoDB为每一行保存当前系统的版本号作为删除标识

  • UPDATE

    InnoDB插入一行新的记录，保存当前系统的版本号，同时保存当前系统到原来的行作为删除标识

这样通过版本号的方法使得大多数读操作不需要加锁。

MVCC只在可重复读和已提交读两个隔离级别工作，其他的隔离级别与MVCC不兼容

MySQL引擎

InnoDB 储存引擎

InnoDB是MySQL默认的事务型引擎，也是最重要，最广泛使用的储存引擎。它被设计用来处理大量的短期事务，短期事务大部分情况下都是正常提交的，很少被回滚。InnoDB的性能和崩溃自动回复的特性使其在非事务型储存的需求中也很流行。

InnoDB数据储存在表空间中，表空间是InnoDB管理的一个黑盒子，由一系列数据文件组成。

InnoDB通过MVCC来支持高并发，并且实现了四个标准的隔离级别，其默认级别是可重复读，并且通过间隙锁策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引的间隙进行锁定。

InnoDB表基于聚簇索引建立。InnoDB和MySQL其他引擎有很大不同，聚簇索引对主键的查询有很高的性能。不过他的二级索引必须包含主键列，所以如果主键很大的话，其他的二级索引就会很大。所以索引较多的话主键应尽可能的小

InnoDB内部有很多优化，从磁盘读数据的可预测性读，自动创建hash索引，插入缓冲器操作。

MyISAM引擎

在MySQL5.1之前的版本，MyISAM都是默认的储存引擎，MySQL有几个致命的缺点

  1. 不支持事务和行级锁
  2. 崩溃后无法安全恢复

但其优点在于对于只读数据，速度较快

MyISAM将表储存在两个文件中，数据文件和索引文件，采用非聚簇索引（详见索引部分）

比较

  • 事务： I 是事务型的，支持提交和回滚
  • 并发：M 只支持表级锁， I 还支持行级锁
  • 外键： I 支持外键
  • 备份： I支持在线热备份
  • 崩溃恢复： M崩溃后损坏的几率较高，I 有完善的日志恢复机制

MySQL索引

索引（在MySQL中也称键）是储存引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键。

索引优化是对查询性能优化最有效的手段，能够轻易将查询性能提升若干个数量级。

索引基础

B-树索引

B-树索引是人们经过长期探索发展出目前最适合数据库系统的数据结构，它高效的利用了索引以及机械磁盘的空间。在大多数情况下爱，其不需要全表扫描来获取需要的数据，取而代之的是从根节点进行搜索，依次根据指针向下查找。B树索引有以下特点：

  • 全值匹配 与所有列匹配
  • 匹配最左前缀 索引查找性为“Allen”的人
  • 匹配列前缀 like “J%”
  • 匹配范围值
  • 精确匹配某一列与并范围匹配某一列
  • 只访问索引查询（覆盖索引）

B树索引的不足

  • 如果不是从最左列开始，则无法使用查找
  • 不能跳过索引中的列
  • 范围列的右侧无法再使用索引

可以发现，索引列的顺序十分重要

hash索引

hash索引基于哈希表实现，只有精确匹配所有列的查询才有效。每一行数据索引都会计算一个hash码

hash查找速度很快，但也有以下限制：

  • hash索引只包含hash值和行指针，而不储存字段的值
  • hash索引并不是按照索引值储存，无法排序
  • hash索引不支持部分索引的匹配查找，只支持等值查找

值得注意的是，InnoDB有一个特殊的功能叫自适应哈希索引，当InnoDB某些值使用非常频繁时，他会在内存中基于B树再创建一个hash索引

索引的优点
  • 索引大大减小了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机IO变为顺序IO

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据储存方式。当表有聚簇索引时，表示数据行和相邻的键值紧凑的储存在一起。一个表只能有一个聚簇索引。

InnoDB默认通过主键来聚集数据

如果没有定义主键、InnoDB会选择一个唯一的非空索引，如果没有这样的索引，InnoDB会隐式的定义一个主键作为聚簇索引的主键

聚簇索引的优点
  • 可以把相关数据保存在一起，例如根据用户ID来聚集数据，这样可以最小化磁盘读取数据页
  • 数据访问更快，举措索引将索引和数据保存在统一个B树中，因此聚簇比非聚簇索引的查找更快
  • 覆盖索引世界使用主键值
聚簇索引的缺点
  • 聚簇索引最大限度提高IO密集应用的性能，但是数据如果都在内存中（例如缓存）访问顺序就不重要了
  • 插入速度依赖于插入顺序
  • 更新聚簇索引列的代价很高，强制每个更新移动到位
  • 页分裂问题
  • 聚簇索引可能导致全表扫描变慢（尤其是稀疏表时）
  • 二级索引可能比想象的大，因为二级索引叶子节点包含了主键列

InnoDB和MyISAM的数据分布对比

MyISAM 数据分布很简单，数据按照插入数据储存在磁盘上

它很容易创建索引，并且，索引没有什么不同

这是索引1号 Col1

这是索引2号Col2

而InnoDB支持聚簇数据，使用非常不同的方式储存同样的数据。

1544680914971

该图显示整个表，而非只有索引，InnoDB中聚簇索引就是表

聚簇索引的每一个指针都包含了主键值、事务ID、事务及MVCC的回滚指针及所有的剩余列。

还有一个重要的不同， InnoDb的二级索引和聚簇索引有很多不同，其叶子节点储存的不是行指针而是主键值。

覆盖索引

索引是一种查找数据的方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不需要读取数据的行。

  • 如果一个索引包含（或者说覆盖）所有要查询字段的值，我们就称之为“覆盖索引。！！覆盖索引从辅助索引中即可得到查询数据，简单的说就是辅助索引就包含了所要查询的值！！
  • 例如创建某个辅助索引（name、age）查询数据时，select username,age from user where username=’Java’ and age = 22 要查询的列叶子节点都存在，不需要回表索引若干问

索引若干问

使用索引的原因？

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

大大加快数据的检索速度（这是创建索引最重要的原因）；

帮助服务器避免排序和临时表，将随机IO变为顺序IO；

加速表与表之间的连接，在实现数据参考完整性方面有重要意义；

为什么不对每一列创建索引？

当数据增加、删除和修改时，索引也需要动态维护，这样就降低了数据的维护速度

除了数据表占数据空间之外，索引还要需要占据一定的物理空间，如果建立聚簇索引，空间占用更大

创建、维护索引耗费时间、这种耗费随数据量增加而增加

索引提高查询速度的原因？

将无序的数据变成相对有序的数据（像查目录一样）

常用索引使用的数据结构？

  • 哈希索引

    对于哈希索引来说，底层函数就是一个hash表，因此在大多数需求为单条记录的查询时，可以选择hash索引，查询性能较快

  • BTree索引

    Mysql使用的是B+树索引，但是对于两种引擎的实现不同

MyISAM和InnoDB实现B+树索引的区别

MyISAM: B+树叶节点data域存放的是数据记录的地址，在索引检索的时候，首先按照B+树 算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录，被称为”非聚簇索引”

InnoDB: 数据文件本身就是索引文件，相比于MyISAM 索引文件和数据文件是分离的，其表结构本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的整条数据记录，这个索引的key正是数据表的主键，因此InnoDB本身就是主索引，这被称为“聚簇索引（聚集索引）”，而其余的索引被作为辅助索引，辅助索引data域储存记录主键的值而不是地址。这样根据辅助索引查找时，先取出主键的值，再走一遍主索引。因此设计表时主键的选择十分重要，主键不宜过长也不宜非单调。

索引的注意事项

  • 在经常需要索引的列上加快搜索速度
  • 在经常使用where子句上索引加快条件判断速度
  • 在经常需要排序的列上创建索引，因为索引已经排序，这样加快排序的查询时间
  • 中、大型表的排序都是有效的，但是特大型表不适合建索引
  • 在经常用到的连接上建索引，这些外键索引加快速度
  • 避免where子句对字段加函数，这样无法命中索引
  • 在InnoDB中使用与业务无关的自增主键作为主键，使用逻辑主键，不使用业务主键
  • 索引列 NOT NULL，否者引擎将放弃索引
  • 删除长期不使用索引、不使用索引的存在会造成比必要的性能损耗

索引原则（高性能Mysql）

  • 单行访问很慢，特别是机械硬盘。如果从数据中读取的一个数据库只获取一行，浪费很多工作，最好读取尽可能多的行。
  • 顺序访问范围数据是很快的。第一，顺序I/O不需要多次的磁盘寻道，比随机I/O快的多。第二、服务器按顺序读取数据，不需要额外的排序操作，group by 也无需再次排序
  • 索引的覆盖查询时很快的，如果一个索引包含了查询所有的列，那么储存引擎就不需要再回表查询行，这避免了大量的单行访问