[toc]

数据库

1. mysql

1.1 存储引擎

InnoDB, MyISAM, Memory等. 从5.5.5开始, InnoDB为表的默认存储引擎

mysql> show engines;   // 查看所有存储引擎
mysql> show variables like '%storage_engine%'; // 查询默认存储引擎
mysql> show table status like "table_name" ; // 查看表存储引擎

建表时直接指定存储引擎

create table student(
 id int primary key auto_increment,
 username varchar(120),
 age int
) ENGINE=Memory

InnoDB与MyISAM比较:

1. 是否支持行级锁:
   MyISAM: 只有表级锁(table-level locking)
   InnoDB: 支持行级锁(row-level locking)和表级锁, 默认为表级锁
   在支持行级锁下, 并发写时, InnoDB性能更高
2. 是否支持事务:
   MyISAM: 不提供事务支持
   InnoDB: 提供事务支持, 具有提交(commit)和回滚(rollback)事务的能力
3. 是否支持外键
   MyISAM: 不支持
   InnoDB: 支持
4. 是否支持数据库异常崩溃后安全恢复
   MyISAM: 不支持
   InnoDB: 支持, 数据库重启后保证恢复到崩溃前状态. 依赖redo-log
5. 是否支持MVCC
   MyISAM: 不支持
   InnoDB: 支持
   MVCC有效减少加锁操作, 提供性能
6. 是否支持全文索引
   MyISAM: 支持FULLTEXT类型的全文索引
   InnoDB: 不支持FULLTEXT类型的全文索引, 但可使用sphinx插件支持全文索引,且效果更好

内存表: 使用Memory引擎的表, 建表语句为create table ... engine=memory. 表数据存在内存中,系统重启后会被清空, 但表结构仍在.
临时表: 可以使用各种引擎存储, 如果使用InnoDB或者MyISAM, 数据会写到磁盘

1.1.1 Memory存储引擎

使用存储在内存中的内容来创建表, 数据全部放到内存中. 每个基于MEMORY存储引擎的表实际对应一个磁盘文件.该文件的文件名与表名相同.类型为frm类型.该文件中只存储表的结构, 数据文件存储在内存中, 有利于数据的快速处理, 提高整个表的效率.

默认使用Hash索引. 极少使用.

1.2 InnoDB

1.2.1 特性

• 插入缓冲(insert buffer): 对非聚集索引的插入更新, 不是每次都直接插入索引页, 而是首先判断插入的非聚集索引页是否在缓冲池: 如果在, 直接插入; 不在则插入一个缓冲区中. 让数据库认为这个非聚集索引已插入叶子节点, 然后再以一定频率执行插入缓冲和非聚集索引页子节点的合并操作, 这时能将多个插入合并到一个操作, 大大提高了对非聚集索引执行插入和修改操作的性能
• 两次写(double write): 带来可靠性. 主要用来解决部分写失败(partial page write). 有2部分组成:

1. 内存中的doublewrite buffer, 大小2M
2. 物理磁盘上的共享表空间中连续的128页(16k一个页), 即2个区, 大小同样2M
   当缓冲池刷新时, 并不直接写硬盘, 而是通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer, 只会doublewrite buffer再分两次写, 每次写入1M到共享表空间的物理磁盘, 然后马上调用fsync函数,同步磁盘
   执行update后, 数据文件未更新: 只保证update写到了redo log, 内存中, 可能还因为二次写没有写入磁盘

• 自适应哈希索引(adpative hash index): InnoDB不支持hash索引,而某些清空下hash索引效率更高, InnoDB会健康表上索引的查找, 当观察到建立hash索引可以提高性能时, 会自动建立hash索引

1.2.2 表级锁与行级锁

• 表级锁: 锁粒度最大的锁, 对当前操作的整个表加锁, 实现简单,资源消耗也比较少, 加锁快, 不会出现死锁. 锁粒度最大, 触发锁的概率也最高, 并发度最低.
• 行级锁: 粒度最小的锁, 只针对当前操作的行加锁, 大大减少数据库操作的冲突. 加锁粒度最小, 并发度最高, 加锁的开销也最大, 加锁慢, 会出现死锁.

InnoDB锁算法:

• Record lock: 记录锁, 单个行记录上的锁
• Gap lock: 间隙锁, 锁定一个范围, 不包括记录本身
• Next-key lock: record + gap 临键锁, 锁定一个范围, 包含记录本身

1.2.3 事务实现原理

通过redo log(重做日志)保证事务的持久性, 使用undo log(回滚日志)保证事务原子性
通过锁机制, MVCC等手段保证事务的隔离性(默认支持事务隔离级别REPEATABLE-READ)

1.2.4 主键删除

主键删除后, 会自己生成一个长度为6个字节的rowid作为主键

1.3 表空间

1.3.1 表空间区别

• 共享表空间: 所有表数据, 索引文件都放在一个文件中, 默认这个共享表空间文件路径再data目录下, 表如果放到共享表空间, 表删除了空间也不会删除
• 独立表空间: 每个表都会是以独立的文件来存储. 删除表, 自动清除空间. 由参数 innodbfileper_table控制, 设置为ON就是独立表空间. 5.6.6后, 默认就是开启独立空间了

1.3.2 表空间收缩

• alter table t engine=InnoDB // 修改存储引擎
• optmize table t // 重建表, 更新索引统计数据并释放簇索引未使用的空间, 会锁表.
• truncate table t // 重置表

1.3.4 重建表流程

1. 建立临时文件, 扫描表主键的所有数据页
2. 数据页中表的记录生成B+树, 并存储到临时文件中
3. 生成临时文件的过程, 所有对标的操作会记录到一个日志文件中(row log)
4. 临时文件生成后, 日志文件操作应用到临时文件, 得到一个逻辑数据与原表相同的数据文件
5. 用临时文件替换原表数据文件

1.4 索引

1.4.1 优缺点与注意事项

索引最多可以创建16个索引列

优点:

• 快速访问数据表特点信息, 提供检索速度
• 创建唯一性索引, 保证数据表每一行数据的唯一性
• 加速表与表的连接
• 使用分组和排序进行检索时, 显著减少查询中分组和排序时间

缺点:

• 降低了更新表速度, update, insert因为更新数据, 不仅要更新数据, 还要更新索引文件
• 建立索引会占用磁盘创建索引文件

注意事项:

• 使用短索引, 短索引不仅可以提供查询速度, 更能节省磁盘空间和IO操作
• 索引列排序, 查询只使用一个索引, 如果where子句以使用索引,那么 order by中的列不会使用索引. 默认排序符合要求的话,就不要进行排序操作;尽量不要包含多个列的排序, 如果需要那么最后给列创建复合索引
• like语句, 不鼓励使用like操作, 非用不可, '%aa%'不走索引, 'aa%'走索引
• 不要在列上进行运算
• 不用NOT IN 和 <> 操作

列为NULL值, 是否会用索引:

• 5.6以上正常触发索引
• 为了兼容低版本和其他数据库存储引擎, 不建议使用NULL值来存储和查询数据, 建议设置列为NOT NULL, 并设置默认值.

1.4.2 分类

逻辑分类:

• 主键索引: 一个表只有一个主键索引, 不允许重复, 不允许为null
• 唯一索引: 数据列不允许重复, 允许null; 一张表可有多个唯一索引, 但一个唯一索引只能包含一列.
• 普通索引: 一张表可创建多个普通索引, 一个普通索引可以包含多个字段, 允许数据重复, 允许null
• 全文索引: 让搜索关键词更高效的一种索引

物理分类:

• 聚集索引: 一般为主键索引; 如果没有指定主键,则会选择表的第一个不允许为null的唯一索引; 如果还是没有,则采用InnoDB每行数据内置的6字节ROWID作为聚合索引. 每张表只有一个聚合索引, 因为聚合索引的键值的逻辑顺序决定了表中相应行的物理顺序. 聚集索引再精确查找和范围查找上有良好的性能表现(相比于普通索引和全表扫描), 聚集索引选择还要慎重(一般不会让没有语义的自增id充当聚集索引)
• 非聚集索引: 索引的逻辑顺序与磁盘的物理存储顺序不一致(非主键那一列), 一个表可以有多个非聚集索引

主键索引与唯一索引区别:

• 主键索引不能重复不能为空, 唯一索引不能重复可以为空
• 一张表只能有一个主键索引, 可以有多个唯一索引
• 主键索引查询性能高于唯一索引: 主键索引查询只需要查询一次就可以获取数据, 唯一索引先根据唯一字段查询唯一索引树, 获取到对应的主键后, 再根据主键去查询主键索引获取数据, 所以多了一次查询(回表查询).

唯一索引与普通索引性能:

• 查询: 普通索引与唯一索引性能相近, 都是索引树中查询
• 更新: 唯一索引比普通索引执行慢, 唯一索引需要先将数据读取到内存中,再在内存中进行数据唯一校验, 所以执行比普通索引慢

模糊查询:

1. 通配符开头不走索引.
2. %abc要走索引那么要使用reverse: select * from t where reverse(f) like reverse('%abc');

1.4.3 联合索引

又称复合索引, 遵循最左匹配原则

作用:

• 多字段查询, 如: 建立联合索引key(a,b,c). 相当于建立了key(a), key(a,b), key(a,b,c)等三个索引. 每建立一个索引,就会多一些写操作和占用磁盘空间开销, 建立联合索引后, 对于大数据量的表来说, 可以减少一部分不必要的开销
• 覆盖索引, 如: 建立联合索引key(a,b,c), 查询select a, b, c from t where a=1 and b=1;,(只查询联合索引字段, 且根据联合索引字段作为查询条件) 直接遍历索引获取数据, 无需回表查询,减少了IO操作, 提升数据库查询性能.
• 索引列越多, 通过索引筛选出的数据越少

最左匹配原则(最左前缀原则), 索引以最左边的为起点任何连续的索引都能匹配上, 当遇到范围查询(>,<, between, like>)就会停止匹配. 例如: 对于联合索引index(a,b,c),

• where a=1 只使用索引a
• where a=1 and b=2 只使用索引a,b
• where a=1 and b=2 and c=3 使用a,b,c
• where b=2 or where c=2 不使用索引
• where a=1 and c=3 只使用索引a
• where a=3 and b like 'xx%' and c=3 只使用索引a,b

1.4.4 前缀索引

又称局部索引, 方法:

alter table t add index index_phone(phone(6));
create index index_phone on t(phone(6));

优点: 减少索引文件大小, 每个索引页可以保存更多的索引值, 提高索引查询速度
缺点: 不能在order by或者group by中触发前缀索引, 页不能用于覆盖索引

适用场景: 字符串本身可能比较长,且前几个字符就开始不同.

1.4.5 自增主键

为何建议自增主键作为表主键(主要是性能是最高的):

1. 自增主键连续, 插入过程尽量减少页的分裂, 即使页分裂,也只会分裂很少一部分
2. 也能减少数据的移动, 每次都插入到最后

优点:

• 数据存储空间小
• 性能最好
• 减少页分裂
  缺点:
• 数据量过大, 可能会超过自增取值范围
• 无法满足分布式存储, 分库分表下无法合并表
• 自增有规律, 容易被破解

自增主键不连续原因:

• 唯一主键冲突
• 事务回滚

自增主键持久化:
InnoDB 8.0之前没有持久化能力, 重启或者对表OPTIMIZE操作, 都会使最大ID丢失, InnoDB8.0会把自增主键保存到redo log中, 重启后会从redo log中恢复

1.4.6 索引存储

页: 计算机管理存储器的逻辑块, 硬件和操作系统往往将内存和磁盘存储区分割为连续的大小相等的块, 每个存储块称为一页. 主存和磁盘以页为单位交换数据. 数据库系统巧妙利用磁盘预读原理, 将每个节点大小设为一个页, 每个节点只需要一次磁盘IO就可以完全载入

存储算法:

• 哈希存储法: 以key, value方式存储, 把值存入数组中使用hash值确定数据未知, 如果发生哈希冲突, 使用链表存储数据

缺点:

1. Hash冲突
2. 不支持顺序和范围查询

• 有序数组存储法: 顺序存储, 优点是可以使用二分法快速找到数据, 缺点是更新效率,适合静态数据存储
• 搜索树: 以树的方式存储, 查询性能好, 更新速度快

为何InnoDB使用B+树

• B树: 不管叶子节点还是非叶子节点, 都会保存数据, 导致在非叶子节点中保存的指针数量变少(扇出), 指针少的情况下要保存大量数据, 只能增加树的高度, 导致IO操作变多, 查询性能变低
• Hash: 虽可快速定位, 但没有顺序, IO复杂度高.
• 二叉树: 树高度不均匀, 不能自平衡, 查询效率跟数据有关(树的高度), IO代价高
• 红黑树: 树的高度随数据量增加而增加, IO代价高

B+树: 1. 平衡树，在经典B Tree基础上优化,增加顺序方位指针, 在B+Tree每个子节点都增加一个指向相邻叶子节点的指针, 就形成了带有顺序访问指针的B+tree. 提高了区间访问性能. 如要查询key从18到49的所有数据记录, 找到18后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点, 极大提高了区间查询效率(无需返回上层父节点重复遍历查找减少IO操作). 2. 索引本身也很大, 不可能全部存储到内存中, 因此索引往往以索引文件的形式存储到磁盘上,索引查找过程中就要产生磁盘IO消耗(磁盘相比于内存存取,消耗要高几个数量级),所有索引的结构组织要尽量减少查找过程中磁盘IO的存取次数, 从而提高索引效率.
所以InnoDB只有采取B+树存储所有,才能提高数据库整体操作性能.

B树(B-树, 多路平衡查找树)和B+树区别

• B树所有节点既保存key, 也保存data, 而B+树只有叶子节点才保存key和data, 其他内节点只存放key
• B树的叶子节点独立, 而B+树叶子节点存放一个指向相邻节点的指针
• B树查找相当于对范围内每个节点二分查找, 可能没到叶子节点,就查询到了.而B+树每个查询都会从跟节点到叶子节点, 检索效率稳定, 叶子节点顺序检索效率也很明显
  

InnoDB索引存储
MyISAM也使用B+树作为索引结构, data域存放的数据记录地址. 索引检索时, 根据B+树搜索算法搜索索引. 如果指定key存在, 取出data值, 然后已data域的值为地址读取相应的数据记录, 被称为非聚簇索引
InnoDB中, 数据文件即为索引文件. 叶子节点data域存放的完整数据记录, key就是数据表的主键. 因此InnoDB的表数据文件本身就是主索引. 被称为聚簇索引(或聚集索引). 其他索引被称为辅助索引, data域记录的主键值而不是地址.辅助索引查找后, 先取出主键值, 再走一遍主索引(回表查询).

1.4.7 聚集索引域非聚集索引

聚集索引: 索引结构和数据一起存放的索引, 对InnoDB来说, 主键索引属于聚集索引

优点: 查询速度非常快. B+树是一颗多叉平衡树, 叶子节点也是有序的. 定位到索引的节点, 就相当于定位到数据
缺点: 1. 依赖有序数据: 如果索引数据不是有序,插入时就需要排序. 对于字符串或者UUID这种难以比较的数据, 插入和查询速度比较慢. 2. 更新代价大: 如果索引列的数据被修改, 对应索引也会被修改. 修改代价大, 所以一般主键都不可被修改.
非聚集索引: 索引结构和数据分开存放. MyISAM都是非聚集索引. InnoDB的二级索引都是非聚集索引.
优点: 更新代价比聚集索引小. 因为叶子节点不存放数据
缺点: 1. 依赖有序数据: 如果索引数据不是有序,插入时就需要排序. 对于字符串或者UUID这种难以比较的数据, 插入和查询速度比较慢. 2. 可能会回表查询. 查询到对应指针或者主键后, 可能还需要根据指针或主键再到数据文件或者聚集索引中查询

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

1. 查询条件字段和返回字段正好为索引字段. 直接返回
2. MyISAM时, 直接查询返回主键, 也直接返回

1.5 查询

执行过程:

1. 通过连接器连接到Mysql服务器: 身份认证,权限认证
2. 权限验证通过后, 先查询是否由查询缓存, 如果由缓存,则直接返回缓存数据, 如果没有则进入分析器 : 查询结果缓存
3. 分析器对查询语句进行词法分析和语法分析, 判断SQL是否正确, 如果语法错误则直接返回客户端错误信息, 如果语法正确则进入优化器: 词法分析, 语法分析
4. 优化器对查询语句进行优化处理, 如一个表由多个索引, 优化器会判别哪个索引性能更好: 查询优化
5. 优化器执行完后进入执行器, 执行器开始执行语句进行查询对比, 直到查询到满足条件的所有数据, 然后进行返回: 权限校验, 执行

查询缓存优缺点:
优点: 效率高, 有缓存则会直接返回结果
缺点: 1. 查询上字符不同, 缓存失效, 2. 查询自定义函数,存储过程,用户变量, 临时表, mysql系统表也不会缓存, 3.失效太频繁导致缓存命中率比较低, 任何表操作都会清空查询缓存. 导致查询缓存非常容易失效
query_cache_type参数为DEMAND(按需使用)关闭查询缓存. 8.0后直接删除了查询缓存.

查询关键词执行顺序:

1. from: 从哪个表检索数据
2. where: 过滤表中数据条件
3. group by: 过滤出数据分组
4. having: 对以分组数据进行过滤条件
5. select: 查看结果集中哪个列, 或者列的计算结果
6. order by: 按照什么顺序查看返回结果

from后的表关联, 是自右向左解析
where条件解析顺序是自下向上.
尽量把数据量小的表放到最右边进行关联(小表匹配大表), 筛选出少量数据的条件放到预警最左边(优先过滤大量数据)

1.5.1 删除数据

• delete: 条件删除部分数据, 记录到日志中, 信息可找回
• truncate: 删除整个表, 不记录到日志, 无法找回, 比delete 更快

1.5.2 模糊查询

• regexp: 正则匹配, 更多匹配方式
• like: 多字符匹配或任意单字符模糊匹配

1.5.3 count统计

count()与count(column)区别:
count(column): 不会统计值未null的数据
count(): 统计所有信息,所以与count(column)不一致

查询select count(*) from t在没有where条件时:

• count是InnoDB中一行一行读取的, 然后累积计数的, InnoDB实现了事务, 使用了多版本并发控制, 所以同一时间查询, 结果可能不同. 所以总条数不能直接保存. 但是InnoDB会进行优化: 当count()操作时, 遍历任意索引树得到的结果逻辑上应该时一样的. 所以会找到最小的那颗索引树来遍历, 在保证逻辑正确的前提下, 尽量少扫描数据量, 从而优化执行效率
• MyISAM内部维护了计数器,直接返回总条数.执行效率更高

InnoDB 引擎count(*), count(1), count(主键), count(字段)性能:

• count(*) : 专门优化,查找最小索引树遍历, 按行累加
• count(1) : 遍历整个表, 不取值. server对于返回每一行, 放数字1进去, 判断是不可能为空的, 按行累加
• count(主键): 遍历整个表, 每一行id都取出来, 返回给server, 拿到id后,判断是不可能为空的. 就按行累加
• count(字段): 如果字段不为空, 遍历整个表, 每一行都取出来, 判断不能为null, 按行累加; 如果可以为空, 每一行都取出来, 不是null的才累加
  所以: count(字段) < count(主键) < count(1) ≈ count(*)

1.5.3 视图

优点:

• 获取数据更容易, 对于多表查询来说
• 对机密数据提供安全保护
• 视图修改不影响基本表, 提供了独立的操作单元, 比较轻量

mysql视图有2个概念:

1. 普通视图: create view... 查询与普通表一样
2. InnoDB实现了MVCC(Multi-Version Concurrency Control) 多版本并发控制时用到的一致性视图, 没有物理结构, 作用是事务执行期间定于可以看到的数据

1.5.3 索引查询

1. mysql如何判断索引扫描行数
   通过索引统计列(cardinality)大致得到并判断的, 而索引统计列(cardinality)通过查询show index得到, 索引扫描行数多少就是通过这个值进行判断
2. 索引基数
   索引基数通过采样统计得到, 并不准确. 方法: InnoDB有N个数据页,采样统计会统计这些页面上不同值得到一个平均值,然后初一索引页面数就得到索引基数
3. 强行执行索引
   使用select * from t force index(index_t).
   不一定生效: mysql根据优化器选择索引, 如果force index在候选索引上, 就直接使用指定索引; 不在候选索引上, 则判断索引的行数, 选择最小扫描索引
4. 错选索引
   索引选择是mysql优化器自动选择, 复杂情况下也会有缺陷
   如何解决:

• 删除错选索引, 只留下对的索引
• 使用force index 指定索引
• 修改查询语句引导mysql使用期望的索引

5. 优化身份证索引
   身份证前6为代表地区, 很多相同, 如果使用前缀索引6为, 性能提升不明显. 如果设置位数过长,占用磁盘空间也很大,数据页能放下的索引值就越少,搜索效率也越低.

• 使用身份证倒序存储,设置前6位索引
• 使用hash值字段, 存储身份证hash值.对hash值索引

1.6 事务

1.6.1 MVCC

InnoDB和Falcon存储引擎通过多版本并发控制(MVCC, Multiversion Concurrency Control)

• 并发读-写时: 可做到读操作不阻塞写操作, 写操作不阻塞读操作
• 解决脏读,幻读,不可重复读等事务隔离问题, 但不能解决 写-写更新丢失
• MVCC + 悲观锁: MVCC解决读写冲突, 悲观锁解决写写冲突
• MVCC + 乐观锁: MVCC解决读写冲突, 乐观锁解决写写冲突

工作原理:
主要通过版本链, undo日志, ReadView来实现

1. 每行记录后保存4个隐藏列:

   • db_trx_id: 6byte, 最近修改(修改/插入)事务ID, 记录创建这条记录/最后一次修改该记录的事务ID
   • db_roll_pointer: 7byte, 版本链, 回滚指针, 用于配合undo日志, 指向这条记录的上个版本(存储于rollback segment里)
   • db_row_id: 6byte, 隐含的自增ID(隐藏主键), 如果数据表没有主键, InnoDB会自动以db_row_id产生一个聚簇索引
   • deleted_flag: 记录被更新或者删除, 不代表真的删除, 而是删除flag变了

   每次数据库改动, 都会记录一条undo日志, 每个undo日志也有一个roll_pointer(insert 操作对应undo日志没有, 因为该记录没有更早版本), 指向上一次改动, 最终就可以形成一个修改记录的链表(按修改时间倒序链接的链表, 称为版本链).

2. undo日志: 记录数据被修改之前的日志, 表信息修改之前会把数据拷贝到undo log, 当事务回滚就可以通过undo log里的日志进行数据还原,
   用途:

   • 保证事务进行rollback的原子性和一致性,当事务进行回滚时可以用undo log进行恢复
   • 用于MVCC快照读数据, 在MVCC多版本控制中, 通过undo log的历史版本数据, 可以实现不同事务版本号都拥有自己独立的快照版本数据
     种类:
   • insert undo log: insert新纪录产生的undo log, 只在事务回滚时需要, 并且事务提交后立即丢弃
   • update undo log: 事务update或者delete时产生的undo log; 不仅事务回滚需要, 快照读时也需要.索引不能随便删除, 只有快速读或者事务回滚不涉及该日志时, 对应日志才会被purge线程统一清除

3. Read View: 读视图, 事务进行快照读时生成的读视图, 在该事务执行快照读那一刻, 会生成数据库系统当前的一个快照. 记录并维护系统当前活跃事务ID(没有commit, 当事务开启时, 都会分配一个ID, 这个ID是递增的, 所有越新的事务, ID值越大), 是系统中当前不应该被本事务看到的其他事务id列表. 主要做可读性判断, 当某个事务执行快照读时, 对该记录创建一个读视图, 判断当前事务能够看到哪个版本的数据, 既可能时当前最新数据, 也可能时该行记录undo log中某个版本的数据, 用于支持RC(Read Commited, 读提交)和RR(Repeatable Read, 可重复读)隔离级别的实现
   属性:

   • m_ids: 当前活跃(未提交)事务版本号集合
   • m_low_limit_id: 创建读视图时, 当前系统最大事务版本号+1, 大于这个ID的事务均不可见
   • m_up_limit_id: 创建读视图时, 活跃事务最小版本号, 如果m_ids为空, 则m_up_limit_id为m_low_limit_id, 小于这个ID的事务均可见
   • m_creator_trx_id: 当前事务版本号, 创建读事务id
   • m_low_limit_no: 事务no, 小于这个事务no的undo logs均可被清理
   • m_closed : 读视图是否关闭
     可见性判断:
   • db_trx_id < m_up_limit_id || db_trx_id == m_creator_trx_id (行修改事务id小于或与版本号id或者行修改事务id等于当前id): 行修改事务id小于或与版本号id表示该数据在开启事务之前已存在, 就可以显示. 行修改事务id等于当前id表示当前行时当前事务生成的, 自己生成的数据自己肯定可以看到, 所有也应该显示
   • db_trx_id >= m_low_limit_id (行修改事务id大于最大活跃事务id): 该行在创建读视图后才产生的, 不显示.
   • 如果 m_ids 为空,表明创建读视图之前,所以该行事务都已提交, 当前行指对当前事务都可见
   • m_up_limit_id <= db_trx_id < m_low_limit_id && m_ids.contains(db_trx_id) (行修改事务id小于最大活跃事务id, 并且在活跃事务中): 1. 当前读视图生成时, 有其他事务修改数据但还没commit, 2. 或者创建读视图后,该行被其他事务修改了; 所以都应该看不见该行
   • m_up_limit_id <= db_trx_id < m_low_limit_id && m_ids.notcontains(db_trx_id) (行修改事务id小于最大活跃事务id, 并且不在活跃事务中): 当前读视图生成时, 已经被commit了, 就可以展示
   • 如果记录行的 db_roll_pointer 指向了 undo log, 取出快照记录重写开始判断, 一直找到满足的记录版本或者为空.
     生成时机:

• RC(Read Committed,读提交)每次快照读都会生成并获取最新的读视图
• RR(Repeatable Read, 可重复读)同一事务只有第一个快照读才会创建读视图,之后快照读获取的都是同一读视图, 之后的查询就不会重复生成了, 同一事务每次查询结果一致

1.6.1 MVCC+Next-Key-Lock防止幻读

InnoDB通过Repeat Read级别和Next-key lock来解决幻读:

1. 普通查询, 通过MVCC快照读的方式读取数据
   在事务开启时生成Read View, 直到事务提交. 其他事务所作的更新,插入记录对当前版本不可见. 实现了可重复读和防止快照读下的"幻读"
2. 指向select ... for update, lock in share mode, insert, update, delete等当前读
   当前读下, 读取的都是最新的数据, 如果其他事务有插入新的记录而刚好在当前事务查询范围内, 则产生幻读.
   innodb使用Next-key lock来解决: 执行当前读时, 会锁定当前读取到的记录的同时, 锁定它们的间隙, 防止其他事务在查询范围内插入数据. 就不会发生幻读

1.7 锁

1.7.1 死锁

处理死锁

• 通过innodb_lock_wait_timeout来设置超时实际, 一直等待直到超时, 默认为50s
• 发起死锁检测, 发现死锁后, 主动回滚死锁中某个事务, 让其他事务继续执行, 设置innodb_dead_lock_detect设置on可以自动检测死锁
  查看死锁
• 通过show engine innodb status 查看最近一次死锁
• InnoDB Lock Monitor打开锁监控, 每15秒输出一次日志. 使用完毕后关闭, 否则影响数据库性能
  避免死锁
• 单个Innodb表并发写入操作时避免死锁, 在事务开始时使用select ... from update来获取必要的锁, 即使这些行的更改语句之后执行
• 事务中, 如果要更新记录, 应直接申请足够级别的锁(如排他锁), 而不应先申请共享锁,更新时再申请排他锁(这时用户生气排他锁时, 其他事务可能已获取相同记录共享锁, 从而造成锁冲突, 甚至死锁)
• 如果需要修改或锁定多个表, 应在每个事务中以相同顺序使用加锁语句. 不同程序并发存取多个表, 尽量约定已相同顺序访问表, 大大降低产生死锁的机会
• select ... lock in share mode获取行读锁, 再需要对该记录更新操作,很有可能造成死锁
• 改变事务隔离级别

1.7.2 锁类型

全局锁: 对整个数据库实例加锁,使用场景:做全库逻辑备份. 使整个库处于只读状态, 数据更新语句, 数据定义语句, 更新类事务的提交语句等都会被阻塞.

共享锁: 又称读锁(read lock),读取操作创建的锁.其他用户可并发读取数据, 但任何事务都不能对数据进行修改(获取数据上的排他锁), 直到已释放所有共享锁. 当事务对读锁进行修改时, 很容易造成死锁

排他锁: 又称写锁(exclusive lock/write lock). 对某行加上排他锁, 只能这个事务对其读写, 在事务结束前, 其他事务不能对其进加任何锁, 其他进程可以读取, 不能进行写操作, 需等待其释放. 悲观锁的一种实现.

1.7.2.1 全局锁

导致的问题:

• 主库备份, 备份期间不能进行更新, 业务停摆, 所有更新操作处于等待状态
• 从库备份, 备份期不能执行主库同步binlog, 导致主从延迟

逻辑备份时, 使用逻辑备份工具MySQLdump使用-single-transaction, 会在导入数据之前启动一个事务来保证数据的一致性, 并且整个过程支持数据更新操作

设置全局只读锁: flush tables with read lock (FTWRL).

FTWRL于只读区别

• FTWRL设置数据库只读, 客户端断开后, 整个数据库取消只读
• 数据库只读(set global readonly=true), 设置数据库只读, 会一直让数据库处于只读状态

1.7.2.2 表锁

表级锁: lock tables t read/write加锁, unlock tables释放锁或者客户端断开时自动释放. 除了限制别的线程读写外, 也限定了本线程接下来的操作对象. InnoDB一般不通过表锁来控制并发, 影响太大
元数据锁: (meta data lock/MDL)不需要显式使用, 访问一个表会被自动加上, 事务提交自动释放. 保证读写的正确性. 对标做增删改查时, 加MDL读锁; 对表做结构变更操作时, 加MDL写锁. 读锁不互斥,写锁之间互斥; 保证变更表结构操作的安全性.

意向锁:(Intention Locks), 又分为IS(Intention Shared Lock)共享意向锁,IX(Intention Exclusive Lock)独占意向锁. 当需要对表某条记录加上共享锁时, 先对表加上IS锁, 表示当前表含有共享锁; 当需要对表某条记录加上X锁时, 对表加上IX锁, 表示当前表含有X锁. 作用: 就是在上表级锁的时候, 可以快速判断是否可以上锁, 不需要遍历表中所有记录, IS, IX不冲突.

自增锁:(Auto-Inc Lock). 特殊的表级锁, 自增列插入数据时给表加上自增锁,语句插入完成后释放(MyISAM没有自增锁, 因为它本身就是表锁).5.1.22之后, 又有一个互斥量来进行自增减的累计, 互斥量性能高于自增锁(语句插入时, 获得递增值后, 就释放锁). 但是主从情况下, 通过binlog复制,自增值顺序无法把控,导致主从数据不一致, 使用innodb_autoinc_lock_mode来配置: 0) 只是用自增锁; 1)插入前已知插入行数的插入, 使用互斥量,不知插入行数, 使用自增锁; 2)只用互斥量

1.7.2.3 行锁

行锁算法:

• Record Lock 单行记录锁

锁当前记录, 作用在索引上. 如果当前执行select * from tab where name='xxx' for update时, 执行insert into tab(name) values('xxx')肯定会被阻塞, 但是执行insert into tab(name) values('yyy')就要看情况,
name没有索引, 只能去找聚簇索引,而聚簇索引为主键, 所以只能都锁了, 事务就会被阻塞.
name有索引, 则锁指定行数据, 事务不会被阻塞

• Gap Lock 间隙锁, 锁定一个范围, 不包括记录本身

给为存在的记录加锁. 比如有1,3,5,10的几条记录, 把3,5的间隙锁了, 那么插入id=4的事务就会被阻塞,避免了幻读的产生.间隙锁唯一目的是为了防止其他事务插入数据到间隙中, 如果多个间隙锁要锁住同一间隙也没关系. 间隙锁只在事务隔离级别为可重复读时生效.对于搜索和索引扫描是禁用的.

• Next-Key Lock 锁定一个范围, 包括记录本身

就是记录锁加间隙锁, 比如有1,3,5,10的几条记录, 会锁住(3,5]这个区间, 防止查询id=5的幻读

插入意向锁: 也是一类间隙锁, 不是锁定间隙而是等待某个间隙. 比如上面id=4的事务, 由于被间隙锁阻塞, 事务就会生成一条插入意向锁,表明等待间隙锁的释放.

InnoDB行锁:
共享锁(S lock): select ... lock in share mode, 对结果集每行添加共享锁, 前提是该结果集中没有任何行使用排他锁, 否则申请阻塞. 允许事务读改行记录, 不允许任何线程对该行记录进行修改.
排他锁(X lock): select * from t where id=1 for update, id字段必须有索引, 对结果集中每一行加排他锁, 任何记录更新, 删除操作都会自动加上排他锁. 前提是当前没有线程对该结果集中的任何行使用排他锁或者共享锁, 否则申请阻塞. 排他锁允许当前事务删除或更新一行记录, 其他线程不能操作该记录.

1.7.2.4 优化

• 尽量使用较低的隔离级别
• 精心优化索引, 尽量使用索引访问数据, 使加锁更加精确, 从而减少锁冲突机会
• 选择合理事务大小, 小事务锁冲突几率更低
• 给记录集加锁使, 最好一次请求足够级别的锁. 如: 修改数据,最好直接申请排他锁, 而不是先申请共享锁,修改时再请求排他锁, 容易引起死锁
• 不同程序访问一组表, 应尽量约定以相同顺序访问各表, 对一个表而言, 尽可能以固定顺序读取表中行, 这样可以大大减少死锁机会
• 尽量用相等条件访问数据, 避免间隙锁对并发插入的影响
• 不要申请超过实际需要的锁级别
• 除非必须, 查询时不要显示加锁. MVCC可实现事务中查询不用加锁, 优化事务性能; MVCC只在都提交(Committed Read)和可重复读(Repeatable Read)两种隔离级别下工作
• 特定事务, 可以使用表锁来提高处理速度或减少死锁的可能.

1.7.2 在线DDL

DDL(修改表结构等)方案:

• 尽量在业务小的时间段进行
• 查看官方文档, 确认要做的表修改可以和DML并发, 不会阻塞线上业务
• 使用percona公司的pt-online-schema-change工具. 如要对表A变更, 主要流程为:
  • 创建新的表 A_new
  • 在表上创建触发器, 包括增删改触发器
  • 通过insert...select...limit N语句分片拷贝数据到目的表
  • 拷贝完成, A_new重命名为A表

1.8 日志

• 错误日志: 服务器运行过程中的错误信息(如无法加载数据文件, 权限不正确, 复制环境下, 服务器进程信息也会记录到错误日志). 默认开启, 无法禁止, 存储到数据文件目录中, 名称为<hostname>.err. 5.5.7后, 手动冲洗新的日志文件

mv <hostname>.err <hostname>.err.old && mysqladmin flush-logs

• 查询日志: 被称为通用日志(general log), 记录数据库执行的所有命令, 不管语句释放正确. 并发下, 查询信息会非常多, IO非常大, 所有默认时关闭的. 但是查询日志有助于分析语句执行密集, 执行密集的select语句能否缓冲, 也可以帮助分析问题. 所以可以手动开启查询日志.

查询日志为何记录所有语句:

• insert: 为了避免数据冲突, 如果此前插入过数据, 当前插入的数据如果主键或唯一键数据重复就会报错
• update: 查询需要更新的数据
• delete: 只删除符合条件的数据

set global general_log=1; // 1开启日志, 0关闭日志. 语句即时生效
set global logoutput='table'

• 慢日志: 慢查询会导致CPU, IOPS, 内存消耗过高, 当数据库遇到性能瓶颈时, 大部分时间都是由慢查询导致. 开启慢查询日志, 可让Mysql记录查询超过指定时间的语句, 之后通过定位分析, 优化数据库性能. 默认不开启.需手动开启.

set global slow_query_log='ON'; // 开启慢查询日志, 只对当前数据库有效, 数据库重启后失效. 如果要永久生效, 修改my.cnf, 设置slow_query_log=1并重启服务

• redo log(重做日志): 为了最大程度必满数据写入时, IO瓶颈造成的性能问题, Mysql先将数据写入内存, 再批量将内存中的数据统一刷回磁盘. 为了避免数据刷回磁盘过程中, 掉电或者系统异常带来的数据丢失, 将操作记录记录到redo log.
• undo log(回滚日志): 用于存储日志被修改前的值, 从而保证如果修改出现异常, 使用undo log实现回滚操作.undo log为逻辑日志, 记录回滚逻辑.当执行rollback时,从undo log中逻辑记录读取到相应内容进行回滚.可通过innodb_undo_directory来自定义存放目录. 默认为数据文件目录
• bin log(二进制日志): 记录所有数据库表结构变更(create/alter table)等, 以及表数据修改(insert/update/delete)的所有操作, bin log记录了对Mysql数据库执行更改的所有操作, 并记录了语句发生时间,执行时长,操作数据等其他额外信息. 不记录select, show等不修改数据的sql语句.

作用:

• 恢复(recovery): 某些数据恢复需要二进制日志. 如数据库全备文件恢复后, 可通过point-in-time恢复
• 复制(replication): 原理与恢复类似, 通过复制和执行二进制日志, 使远程数据库与当前数据库实时同步
• 审计(audit): 通过二进制日志信息来审计, 判断是否有对数据库进行注入攻击
• 崩溃恢复: 开启binglog下, 为了保证binlog与redo一致性, Mysql采用事务两阶段提交协议. 当发生崩溃, 事务在存储引擎内部状态可能为 prepared(准备状态)和commit(提交状态). 对于prepared状态事务, 如果事务在binglog中存在, 那么将其提交. 如果不再binlog中不存在. 将其回滚. 保证主从一致性.

关联:
redo log与binlog共有字段XID. 当崩溃时会按顺序扫描redo log

• 碰到既有prepare, 又有commit的redo log, 就直接提交
• 碰到只有prepare, 没有commit的redo log, 就拿XID去binlog找对应事务
  binlog默认关闭. 设置 log-bin=[base-name], 如果不指定base-name, 则默认二进制日志文件名作为主机名, 并以自增数字作为后缀. 开启binlog日志所在目录为数据文件目录.
  binlog格式:

• STATEMENT: 记录的使数据库执行的原生SQL语句.
  • 优点: 1. 简单, 简单记录和执行语句.能够主备保持同步. 2. 二进制日志里的时间更加紧凑, 相对而言基于语句的复制模式不会占用太多带宽, 也节约磁盘空间. 并且mysqlbinlog工具容易读懂其中内容
  • 缺点: 1. 同一条sql在主库和从库执行时间不同,因此在传输二进制日志中, 除了查询语句, 还包括元数据信息, 如当前时间戳. 还是存在无法被正确复制的SQL(如insert into t value(current_date())在从库上会发生变化). 存储过程和触发器基于语句的复制模式也可能存在问题. 2. 语句复制必须是串行话, 如Next-key锁等. 并不是所有存储引擎都支持基于语句复制
  • 完整性: 完整性标识是最后都有commit关键字

  begin;    -- 事务开始
  user test;   -- 记录内容
  update t set
  update_time = now()
  where id = 1
  commit xid = xxx   -- 提交事务和事务id

• ROW: 基于行的复制(基于数据的复制, 基于行的更改). 实际数据记录到二进制日志中.
  • 优点: 正确复制每一行数据, 一些语句可以被更加有效复制,几乎没有基于行复制无法处理的场景, 对于所有SQL构造, 触发器, 存储过程等都能正确执行
  • 缺点: 二进制文件很大, 不直观. 不能用mysqlbinlog查看, 也无法通过二进制日志判断当前执行到哪一条SQL语句.
  • 完整性: 完整性标识是最后都有XID event关键字
• MIXED: Mysql默认二进制记录方式, 基于语句复制, 一旦发现基于语句无法精确复制时, 采用基于行复制, 如UUID(),USER(), CURRENTUSER(),ROWCOUNT()等无法确定的函数.

两阶段提交:
当提交事务时, 写入redo log成功, binlog失败,宕机恢复就会异常, 所以引入两阶段提交, 流程为:

1. 开始事务
2. 更新数据
3. 写入redo log(prepare阶段), 记录事务id,redo log记录内容,prepare阶段信息
4. 提交事务
5. 写入binlog日志, 记录事务id, binlog日志内容
6. 写入redo log(commit阶段), 记录事务id,redo log记录内容,commit阶段信息

a) 如果binlog写入失败, 发现redo log处于prepare阶段, 并没有对应binlog, 就会回滚事务
b) 如果redo log(commit阶段)写入失败, redo log 处于prepare阶段,发现由对应binlog, 认为事务完整执行, 提交事务恢复数据

1.8.1 redo log与binlog

区别:

• redo log:
  1. 物理日志, 记录在某个数据页上做了什么修改.
  2. inndb才有, 保证发生宕机等意外情况下, 服务器重启数据不丢失. 循环写,
  3. 空间固定会用完.
  4. redo log buffer全局公用: redo log buffer很小，一个事务中间生成的日志可以写到redo log buffer中, 其他事务提交的时候可以被一起写到磁盘, 加快redo log文件写入
  5. 在事务执行过程中可以不断写入
• binlog:
  1. 逻辑日志, 记录语句原始逻辑(如修改id为2的行字段c设置值).
  2. server层实现, 所有引擎都可使用.
  3. 追加写(文件到一定大小切换到下一个, 并不会覆盖以前的日志).
  4. binlog cache: 每个线程自己维护, 一个事务必须连续写, 整个事务完成后, 再一起写到文件里.
  5. 只有在提交事务时才写入

只有binlog没有redo log.那存储引擎没有崩溃恢复能力
只有redo log没有binlog: redo log循环写, 不能保证所有历史数据, 历史数据只能再binlog中找到. binlog是高可用基础. 主从就是基于binlog复制

binlog event写入流程

1. 事务开启
2. 执行DML语句, DML语句第一次执行会分配内存空间binlog cache。 每个线程都会分配一块内存作为binlog cache(可通过binlog_cache_size参数控制单个线程binlog_cache大小).
3. 执行DML语句期间生成的event不断写入到binlog cache
4. 如果binlog cache满了.将binlogcahe写到binlog临时文件, 同时清空binlogcache. 如果binlog临时文件大小大于max_binlog_cache_size则抛错
5. 事务提交, 整个binlog cache和binlog临时文件数据写入binlog file中,同时释放binlog cache和binlog临时文件, binlog cache内存空间会被保留以供同一session的下一个事务使用, binlog临时文件截断, 保留文件地址(就是保留物理文件描述符和分配内存空间,但下次写就是从头开始覆写了)
6. 客户端断开, 删除binlog cache 和binlog 临时文件.
7. binlog cache写入到binlog file时, 先将binlog cache写入到page cache, 再同步到文件binlog file. 可以通过sync_binlog来控制:

• 0: 每次提交事务都只是binlog cache写入到page cache, 由操作系统字节判断fsync到binlog file(宕机时, page cache可能丢失)
• 1: 每次提交事务都binlog cache写入到page cache, 必须fsync到文件
• N(N>1): 每次提交都写入到binlog cache, N次后才fsync到文件

1.8.2 redo log刷盘

mysql数据以页为单位, 查询数据时,会把硬盘一页的数据加载出来(数据页), 放到buffer pool中. 后续查找先到buffer pool中找, 没命中就再到硬盘中加载. 减少IO消耗,提高性能.
更新表时, 如果数据在buffer pool里存在更新数据, 就直接buffer pool更新, 然后把某个数据页做了什么更新记录到redo log缓存(redo log buffer) 中, 接着刷盘到redo log中.

redo记录: 表空间号 + 数据页号 + 偏移量 + 修改数据长度 + 具体修改的数据
刷盘时机
innodb刷盘策略由innodb_flush_log_at_trx_commit参数控制:

• 0: 每次事务提交不进行刷盘

每隔1秒刷盘, 宕机会丢失1s数据

• 1: 每次事务提交都进行刷盘

只要事务提交成功, redo log会记录到硬盘, 不会有数据丢失

• 2: 每次事务提交先把redo log buffer内容写入page cache

redo log buffer写入文件系统缓存, 如果只是mysql挂了, 数据不丢失. 如果宕机, 会丢失1s数据

默认innodb有一个后台线程, 每隔1秒, 会将redo log buffer内容写入文件系统缓存(page cache), 然后调用fsync刷盘

文件日志组
redo log以一个日志文件组形式出现, 才有环形数组形式, 从头开始写, 写到末尾又回到头循环写

数据页大小为16KB, 而修改了时可能只修改几个字节. 加上数据页刷盘为随机读写(一个数据页对应的硬盘文件位置随机), 所以刷盘比较耗时, 性能很差.
如果写redo log, 一行只有几十字节,加上是顺序写, 刷盘速度很快.

1.8.3 WAL

脏页与干净页: 为了操作性能优化, 先把数据更新放入内存中, 再统一更新到磁盘. 如果内存数据与磁盘数据内容不一致, 称内存页为脏页; 内存数据写到磁盘后, 内存数据和磁盘内容一致了, 就称为干净页. 主要为了降低IO.

触发脏页flush操作:

• 内存写满了, 对应InnoDB的redo log写满了
• 系统内存不足, 当需要新的内存页时, 就会淘汰一些内存页, 如果淘汰的是脏页就会触发flush
• 系统空闲, 同步内存中数据到磁盘也会触发flush
• 服务关闭也会触发刷脏页, 触发flush操作

刷脏页慢:
当刷脏页发现相邻数据页也是脏页也会一起刷掉. 这个动作一致蔓延辖区. 就导致刷脏页慢

1.9 性能优化

1.9.1 性能指标

1. TPS(Transaction Per Second)每秒事务数, 即数据库每秒执行的事务数
   没有提供TPS参数值, 计算方法:

• com_commit: 提交次数,通过show global status like 'Com_commit';获取
• com_rollback: 回滚次数,通过show global status like 'Com_rollback'获取
  第一次获取时间t1, 提交次数cc1, 回滚次数cr1
  第二次获取时间t2, 提交次数cc2, 回滚次数cr2;
  那么平均TPS=(cc1+cr1-cc2-cr2)/(t2-t1)

2. Qps(Query Per Second)每秒请求次数, 也就是每秒执行SQL数(包含CRUD等)

• queries: 总请求数, 通过show status like 'queries'获取
  第一次获取时间t1, 总请求数q1
  第二次获取时间t2, 提交次数q2
  那么平均QPS=(q2-q1)/(t2-t1)

3. IOPS(Input/Output Operations per Second)每秒处理I/O请求次数
   只要系统实际请求数低于IOPS的能力, 相当于每个请求都能得到即时响应, IO就不是瓶颈, 通过命令iostat -dx 1 10, 需要安装(yum install sysstat)
   IOPS = r/s(每秒读取多少次) + w/s(每秒写入多少次);

1.9.2 慢查询

慢查询日志用来记录运行时间超过long_query_time(默认10秒)的SQL. 默认不开启, 开启会对给服务器带来一定的性能影响.

1.9.2.1 开启

1. 查询是否开启

show variables like '%slow_query_log%';
-- 查看结果slow_query_log为OFF就是未开启, ON就是开启

2. 开启慢查询日志
   a) 只对当前数据库生效, 重启失效

set global slowquerylog=1;

b) 修改my.cnf

slow_query_log=1
slow_query_log_file=/tmp/mysqlslow.log

1.9.2.2 慢查询定位

定位: 执行explain, 如explain select * from t where id = 5;
结果:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
选择标识符,id越大优先级越高,越先被执行	查询类型	输出结果集表	匹配的分区	表的连接类型	查询时, 可能使用的索引	实际使用的索引	索引字段的长度	列与索引的比较	大概估算的行数	按表条件过滤的行百分比	执行情况的描述和说明

type字段值类型:

类型	说明
all	扫描全表数据
index	扫描索引
range	索引范围查找
index_subquery	子查询中使用ref
unique_subquery	子查询中使用eq_ref
refornull	对null进行索引的优化ref
fulltext	全文索引
ref	使用非唯一索引查找数据
eq_ref	join查询中使用主键或唯一索引关联
cost	将一个主键放置到where作为条件查询, 优化器能把这次查询优化转为一个常量, 如何/何时转换取决于优化器, 比eq_ref效率高一点

1.9.3 优化手段

(1) 查询优化

• 避免select *, 只查询需要的字段
• 小表驱动大表
• 一些情况下, 使用连接代替子查询, 因为使用join时, 不会再内存创建临时表
  (2) 优化索引使用
• 尽量使用主键查询而非其他索引(其他索引会触发回表查询)
• 不做列运算, 所有计算都放到业务系统实现
• 查询语句尽可能简单, 大语句拆成小语句, 减少锁时间
• 不使用select * 查询
• or查询改为in查询
• 不用函数和触发器
• 避免%aa查询
• 少用join查询
• 使用同类型比较, 如123与'123'就是不同类型
• 尽量避免在where子句使用 != 或者<>, 哈希引用会放弃索引而进行全表扫描
• 列表数据使用分页查询, 每页数据量不要太大
• 用exists 替换 in查询
• 避免在索引列上使用 is null 和 is not null;
• 避免在where子句对字段进行表达式操作
• 尽量使用数字字段
  (3) 表结构设计优化
• 使用可存下数据最小的数据类型
• 使用简单数据类型, int比varchar类型在mysql处理简单
• 尽量使用tinyint, smallint, mediumint作为整数类型而非int
• 尽可能使用not null定义字段, 因为null占用4字节空间
• 少用text类型, 非用不可最好考虑分表
• 尽量使用timestamp, 而非datetime.
• 单表不要有太多字段, 建议20个以内
  (4) 表拆分
  表数据非常大, 查询优化方案也不能解决查询速度慢的问题时, 拆分表, 让每张表数据量变小, 从而提高查询效率
• 垂直拆分: 数据表列的拆分, 把一张列比较多的表拆分未多张表, 常用字段一张表, 不常用字段另外一张表, 插入数据时, 事务保证两张表一致性, 原子
  • 不常用字段单独放一张表
  • text, blob 大字段拆分出来放附表中
  • 常用组合查询列放入一张表中
• 水平拆分: 表行数超过200万行, 查询变慢, 一张表拆成多张表来存放. 通常取模的方式来进行表的拆分
  (5) 读写分离
  数据库集群方案, 一个库作为主库, 负责写入数据, 其他库作为从库, 负责读取数据, 缓解数据库访问压力
  (6) 超级大表分页查询优化
• 数据库层面优化: 使用SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id,通过对id主键索引进行分页查询, 会比直接查询所有数据快很多
• 程序层面优化: 结果缓存

1.9.4 读写分离

• 应用层: 应用层对数据源做路由来实现读写分离, 优点:路由策略扩展性和可控性较强, 缺点: 代码中耦合控制代码
• 中间件: 中间件做主从集群, 优点: 应用层解耦, 缺点: 增加服务维护风险点,性能和稳定性待测试, 需要支持代码强制主从和事务

Sharding-JDBC: 对数据库进行水平分区常用解决方案, 保持表结构不变, 根据策略存储数据分片, 每一片数据分散到不同表和库中, 提供: 分库分表,读写分离, 分布式主键生成功能.
执行流程: 调用数据库执行时, 对SQL解析,改写, 对改写的SQL进行执行和结果归并, 返回给调用层

保证主备无延迟解决办法:

• 每次从库执行查询请求前,先判断seconds_behind_master是否等于0, 不等于0则等待参数变为0才能执行查询请求. seconds_behind_master用来衡量主备延迟时间的长短
• 对比位点确保主备无延迟. master_log_file和read_master_log_pos表示是读到的主库的最新位点, relay_master_log_file和exec_master_log_pos表示备库执行的最新位点
• 对比GTID集合确保主备无延迟. Auto_position=1, 表示这对主备关系使用了GTID协议, Retrieved_gtid_set是备库收到的所有日志的GTID集合, executed_gtid_set是备库所有已执行完成的GTID集合.

主从延迟原因:

• 主库有大事务处理
• 主库在做大量的增删改操作
• 主库对大表进行字段的增删和添加索引操作
• 主库从库太多, 导致复制延迟, 从库一般3-5个为宜
• 从库硬件配置比主库差, 导致执行时间长,从此导致从库复制延迟时间长
• 主库读写压力大, 导致复制延迟
• 主从之间网络延迟.