Mysql数据库基础

数据库四大特性

1. 原子性（Atomicity）事务包含的所有操作要么全部成功，要么全部失败回滚。失败回滚的操作事务，将不能对事物有任何影响。
2. 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态。
3. 隔离性（Isolation）：将多个事务隔离开，每个事务都不能访问到其他事务操作过程中的状态；只有一个事务做完了，另外一个事务才能执行。
4. 持久性（Durability）：事务的操作，一旦提交，对于数据库中数据的改变是永久性的，即使数据库发生故障也不能丢失已提交事务所完成的改变。

注：MySQL 使用 redo log 来保证事务的持久性。

事务的隔离级别

1. READ-UNCOMMITTED（读未提交）
   脏读：读到其他事务未提交的数据。

2. READ-COMMITED（读提交）
   不可重复读：一个事务中多次同一查询中间其他事务将数据进行了修改，导致多次查询结果不同。

3. 可重复读（REPEATABLE-READ）：
   在同一个事务里， select 的结果是事务开始时时间点的状态。

   幻读：一个事务插入或更新数据，另一个事务根据其未操作前的数据进行修改，导致失败。

   MySQL 的 InnoDB 引擎通过 next-key locks 机制来避免幻读。对查询的数据加gap lock，不能被插入数据。

4. SERIALIZABLE（序列化）
   在该隔离级别下事务都是串行顺序执行的，MySQL 数据库的 InnoDB 引擎会给读操作隐式加一把读共享锁，从而避免了脏读、不可重读复读和幻读问题。

InnoDB 锁

InnoDB 锁

行锁的算法

参考
InnoDB 存储引擎使用三种行锁的算法用来满足相关事务隔离级别的要求。

Record Locks

该锁为索引记录上的锁，如果表中没有定义索引，InnoDB 会默认为该表创建一个隐藏的聚簇索引，并使用该索引锁定记录。

Gap Locks

该锁会锁定一个index records范围，但是不括记录本身。可以通过修改隔离级别为 READ COMMITTED 或者配置 innodb_locks_unsafe_for_binlog 参数为 ON 。
gap的范围为单个的索引值，多个索引值或者为空的。
目的是防止其他事务在gap范围中插入数据。

Next-key Locks

该锁就是 Record Locks 和 Gap Locks 的组合，即锁定一个范围并且锁定该记录本身。InnoDB 使用 Next-key Locks 解决幻读问题。需要注意的是，如果索引有唯一属性，则 InnnoDB 会自动将 Next-key Locks 降级为 Record Locks。举个例子，如果一个索引有 1, 3, 5 三个值，则该索引锁定的区间为 (-∞,1], (1,3], (3,5], (5,+ ∞) 。

索引

最左匹配

使用

• 多个索引可用时，使用最小影响行数的索引。
• 默认开启扩展索引，即索引字段自动扩展上主键（文档）。

聚簇索引

文档

• 表里定义主键，InnoDB会使用这个主键作为聚簇索引。
• 表中没定义主键，MySQL找到所有键列都不为空的第一个UNIQUE索引，InnoDB将其用作聚集索引。
• 没有主键，也没有合适的UNIQUE索引，InnoDB生成一个名字为 GEN_CLUST_INDEX 的隐藏聚簇索引，该合成的聚簇索引包含行ID。排序是按照InnoDB分配的行ID进行的。行ID是一个6字节的字段，随着插入新行而单调增加。因此，按行ID排序实际上与插入顺序一样。

索引查找直接找的是全行数据的页，如果一个表很大，与索引记录使用不同页存储相比，聚簇索引的结构能节省磁盘I/O操作。

InnoDB使用此主键值在聚集索引中搜索行。

二级索引

除了聚簇索引，其他索引都是二级索引。

在InnoDB中，二级索引的每条记录中都有主键列，和二级索引用的其他列。如果主键长，二级索引就会用更多空间，所以用短的主键更有利。

Hash索引

• 只能用于等于比较，= 或 <=>操作符。不能用于范围查找。
• 不能用于order by。
• 不能知道两个值之间大约有多少行。
• 只能进行索引字段全匹配查询。

如何创建索引

索引占用空间，选择使用哪个索引时也占用时间；insert、update、delete操作时也会更新索引，必须找到正确的平衡实现快速查询。

表操作

快速删除一张表

TRUNCATE [TABLE] tbl_name

• 执行的是drop and re-create the table，比delete删数据要快。
• 表锁被占用时不能执行。
• 对InnoDB 或 NDB的表，foreign key关联到其他表时不能执行，关联同一表时可以。
• 隐含了commit，所以不能回滚。
• 不会返回删除的数据行数
• 重置AUTO_INCREMENT的值为初始值。
• 分表时，其他分表不受影响
• 不会调用delete触发器。

表设计

1. 字段数目不要太多，较少使用的字段放到另一张表中。这样小表能用主键快速查询。更少的IO使用，更少的缓存。

缓存机制

InnoDB缓存池

文档

• 使用最近最少使用算法（ least recently used (LRU) ）

InnoDB引擎与MyISAM引擎的区别

官方比对
InnoDB引擎支持数据缓存、外键、行级锁、事务、MVCC（Multi-Version Concurrency Control）。

MyISAM引擎用表级锁，所以适用于只读或大多数读操作中。

备份

• 热备份
  使用mysqlbackup命令。在MYSQL运行实例时进行备份，不影响读写，结合MYSQL二进制日志，可进行时间点恢复。
• 冷备份
  MYSQL服务器关闭后手动拷贝数据、日志、配置文件。
• 逻辑备份
  使用mysqldump命令。存储在可读的文本文件中。