Mysql进阶总结

MyISAM vs InnoDB

	MyISAM	InnoDB
存在时间	< 5.5	>=5.5
对锁的支持	表级别(table-level locking)	行级别(row-level locking)&表级别(table-level locking)
事务	不支持	支持
安全恢复	不可以	可以
外键	不支持	支持
MVCC多版本并发控制	不支持	支持
索引	非聚簇索引	聚簇索引

事务

并发事务带来的问题

• 脏读（Dirty read）
• 丢失修改（Lost to modify）
• 不可重复读（Unrepeatableread）
• 幻读（Phantom read）

不可重复读 vs 幻读

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

事务隔离级别

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ

大表优化

• 限定数据的范围, 避使用 select * from xxxx
• 读/写分离，主从库
• 垂直分区
  • 垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
  • 垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；
• 水平分区：通过某种策略存储数据分片

参考大表优化方案

Id 生成方案

• UUID
• 数据库自增 id
• 利用 redis 生成 id
• Twitter的snowflake算法
• 美团的Leaf分布式ID生成系统

一条SQL语句执行得很慢的原因有哪些？

一个 SQL 执行的很慢，我们要分两种情况讨论：

1、大多数情况下很正常，偶尔很慢，则有如下原因

(1)、数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。

(2)、执行的时候，遇到锁，如表锁、行锁。

2、这条 SQL 语句一直执行的很慢，则有如下原因。

(1)、没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。

(2)、数据库选错了索引。

参考sql执行慢的原因