高性能 MySql 笔记

高性能 MySql 笔记

补充

• mysql 5.0 和更新版本引入了一种叫“索引合并”（index merge）的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的 MySQL 只能使用其中某一个单列索引，然而这种情况下没有哪一个独立的单列索引是非常有效的
• 那么，如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、 age的多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。

文件

• .frm 一个表对应一个
• .MYD （myISAM）数据文件
• .MYI （myISAM）索引文件

引擎

• innoDb 行锁 repeatAble read 聚簇索引 btree
• myISAM 表锁 全文索引、压缩（不能修改的数据适合）、空间函数，崩溃后无法安全恢复，支持并发插入（select 时可以插入不加锁）,BLOB TEXT 前 500 字索引，分为未压缩表和压缩表（适合只读数据类型），按数据插入顺序存储
• Archive 行锁 insert select（每次都全表扫描）
• blackhole 丢弃所有 insert 但会有日志（不推荐）
• csv 不支持索引，可以作为数据交换，默认禁用
• federated 代理到其它 mysql
• memory btree （默认）hash索引 表锁 不支持 blob text，内存固定所以 varchar 变 char 内存大小限制会临时转成 myISAM
• NDB 集群

索引

• 主键索引：一级索引，非主键索引：二级索引，但只有 InnoDB 中有聚簇非聚簇之分（叫法）
• （一个表只能有一个）（只有 InnoDB 有）聚簇索引（将索引和数据保存在同一个B-Tree中）：并不是一种单独的索引类型，而是一种数据存储方式
  • 主键索引
  • 如果没有主键用唯一的非空索引代替
  • 如果都没有 InnoDB 会隐式的定义一个主键来作为聚簇索引
• 一级索引（聚簇索引、主键索引）
• 二级索引（非聚簇索引、非主键索引，查找 2 次，通过主键（一级索引）定位行数据），存储的是主键值，而不是行指针，这样的策略可以减少当出现行移动或数据页分裂时二级索引的维护工作，当会占用更多空间
• B-Tree（ndb t-tree,innodb b+tree）对索引列是顺序组织存储（从小到达，从左到右），很适合查找范围数据，可以排序
  • 与全文检索差别：b-tree 基于值，一个列可以有 B-Tree 和 全文检索共同存在
• Hash 只有 memory 引擎支持，Memory 支持非唯一哈希索引，无法用于排序，hash(a,b) 则无法单独用 a 查询，只能精确查询，crc32() fnv64()
  • alter table mytable add key(hash(fname));
• 空间数据索引（R-Tree） MyISAM 支持
• 全文索引 full_text(ca,cb)
• 分型树索引
• 覆盖索引（可以模拟多个聚簇索引的情况）：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们称之为“覆盖索引”；这样就不需要回表查询了；MySQL 只能使用 B-Tree 索引做覆盖索引；哈希、空间、全文索引等都不存储索引列都值，所以不行；
• 普通索引
• 唯一索引（可以 null）
• 前缀索引
  • alter table mytable add key(city(7));
  • 无法 order by group by
  • 无法做覆盖扫描
• 后缀索引：mysql 原生不支持，但可以通过把 abc@xyz.com 存储成 moc.zyx@cba 建立后缀索引
• 多列索引=联合索引
  • alter table mytable add key(name,age);
• 压缩索引（MyISAM）：默认压缩字符串，也可以设置整数；压缩块使用更少都空间，代价是某些操作可能更慢（如 二分查找、 order by）

> show indexes from t_name;
> show keys from t_name;

索引2

• MyISAM 默认对字符串（也可以设置整数）使用压缩索引，这会导致查询慢很多
• MyISAM 可以压缩索引
• MySQL 会在索引中存储 NULL 值，而 Oracle 不会
• Blob, Text, 很长的 varchar，必须使用前缀索引，因为 Mysql 不允许索引这些列的完整长度
• InnoDB 按主键顺序插入，所以 uuid 作为主键则写入是乱序的，产生大量随机 IO，继而产生页分裂，最终产生更多碎片
• 如果 InnoDB 二级索引可以覆盖查询，则可以避免对主键索引对二次查询

索引3

• 不重复的索引值（基数）cardinality
• 数据表的记录总数（#T）
• 索引的选择性：cardinality/#T=(1/#T<=x<=1)，最好是 1
• 唯一索引或主键索引选择性=1
• 索引合并：index_merge（往往是不好的情况才出现）
• 查询的顺序不影响索引的顺序，但是索引的字段顺序会影响查询的性能，选择性最好的字段应该放在前面
• InnoDB 中二级索引 (A)=(A,ID)
• a<100 范围条件查询，a in (1,3,5) 多个等值条件查询：对于范围条件查询，MySQL 无法再使用范围列后面的其它索引列，但是对于“多个等值条件查询”则没有这个限制
• 延迟关联：通过覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行
• MySQL 的优化器是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行
  • show status like ‘Last_query_cost’;
• 数据碎片：MyISAM 通过排序算法重建索引的方式来消除碎片；InnoDB 先删除，再创建索引来消除碎片；其它可以 alter table mytable engine=? 来消除碎片

锁

• record lock：索引项加锁
• gap lock：索引之间的间隙加锁
• next-key lock：前面2个的组合
• innoDB 2 阶段提交
• 行级锁只在存储引擎层实现
• ALTER TABLE 等使用表锁，不管是存储引擎

显示锁定

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

MVCC 多版本并发控制

• 使大多数读操作都可以不用加锁
• 支持：READ COMMITED & REPEATABLE READ
• 行创建时间列
• 行过期时间列（或删除时间）
• SELECT 行创建时间 <= 当前事务系统版本号 且 行当删除版本号要么未定义，要么大于当前事务系统版本号
• INSERT 添加行创建时间
• DELETE 添加行过期时间
• UPDATE = INSERT + DELETE（2 个版本号是一样定）

锁和索引

• 如果根据主键更新，行锁
• 如果根据唯一索引更新，行锁
• 如果根据普通索引更新，行锁、表锁
• 即使使用索引，innodb 也可能锁住不必要都行
• InnoDb 在二级索引上使用共享（读）锁，但访问主键索引需要排它（写）锁

数据类型

• 浮点数：建议只指定类型，不要指定精度
• MySQL 使用 Double 作为内部浮点计算的类型
• Varchar（memory 引擎不支持）：最大长度比平均长度大很多；列更新少，所以碎片不是问题；使用 UTF-8 这样的字符集，每个字符都使用不同的字节数存储；同时需要而外的 1 个（<=255）或 2 个字节存储长度
• Char 末尾空格（服务层）会去掉，不会有碎片问题，不需要额外的字节存储长度
• binary, varBinary：不会去空格，二进制比较比字符串简单，所以更快
• blob（memory 引擎不支持）：二进制方式，过大时使用“外部”存储
• text（memory 引擎不支持）：字符方式，过大时使用“外部”存储
• 查询 Blob Text 需要使用隐时临时表会使用 MyISAM 磁盘临时表，可以使用 substring 就可以转换成内存临时表
• enum：其实是整型，顺序也有问题，但是体积小，虽然可以用字符串匹配，但是速度有问题
• MySQL：最小时间粒度为秒
• datetime：yyyymmddhhmmss，与时区无关，8字节
• timestamp：推荐使用，因为体积小效率高，默认 not null，4字节，from_unixtime() unix_timestap()，依赖时区，如果（插入、更新）第一个 timestamp 没指定则默认是当前时间
• 位数据类型（谨慎使用）：底层是字符串类型，但 memory 和 innodb 使用最小整数类型，所以无法节省空间
• set：改变列代价高，需要 alter table，内部使用数字类型

加快 Alter table 操作对速度

• alter column：设置、删除默认值
• modify column：类型变更、位置变更
• change column：重命名、类型变更、位置变更
• 不是所有对 alter table 操作都会引起表重建
• 所有 modify column 操作都会引起表重建
• alter column 直接修改 .frm 文件而不涉及表数据

6 章

• MySQL 查询优化器能处理的优化类型
  • 重新定义关联表顺序
  • 将外连接转化成内连接
  • 使用等价变换规则
  • 优化 count(), min(), max()
  • 预估并转化为常数表达式
  • 覆盖索引扫描
  • 子查询优化
  • 提前终止查询
  • 等值传播
  • 列表 IN() 的比较
• 统计信息有存储引擎实现，Memory 没有统计信息
• MySQL 认为任何一个查询都是一次“关联”，采用“嵌套循环关联”（全外连接除外），即“嵌套循环、回溯”
• MySQL 临时表没有任何索引，包括子查询、关联查询、UNION
• 右外连接 MySQL 会改写成左外连接
• 搜索空间：超过 n 个表的关联，就需要检查 n 的阶乘种关联顺序，当表超过 optimizer_search_depth 的限制时开启“贪婪”搜索
• 文件排序（filesort）：如果数据量小则在内存中排序，如果数据量大则需要使用磁盘
• 如果排序大数据量小于“排序缓冲区”，则使用“快速排序”，否则先将数据分块，再对每块“快速排序”，并将结果存放在磁盘上，然后再合并（merge），最后返回排序结果
• MySQL 返回结果集是一个增量、逐步返回对过程
• 终止算法（early-termination algorithm）：体现在explain 中 Extra 包含 “Not exists”，等于同 not exists 子查询
• distinct 和 group by 通常需要产生临时中间表
• explain extra:Using index for group-by（使用松散索引扫描）
• 查询优化器提示 hit
  • HIGH_PRIORITY 和 LOW_PRIORITY
    • 只对表锁引擎有效，innodb 最好不要用
    • 会导致并发插入被禁用
    • High_priority: select, insert
    • low_priority: select, insert, update, delete
  • for update 和 lock in share mode
    • 只对行级锁有效
    • 导致某些优化无法使用，如覆盖扫描
  • use index, ignore index, force index
  • 只需要在关联顺序对第二个表对相应列上创建索引（关联顺序 B, A，则在 A 上创建索引）
  • group by order by 后面的表达式只涉及到一个表中到列
  • 子查询尽可能用关联代替
  • limit
    • 覆盖索引，延迟关联

7

• innodb 是唯一支持外键的存储引擎
• innodb 强制外键使用索引
• 外键的表写入的时候要锁主键的表的数据，有死锁隐患，很难排查这种死锁
• MySQL 记录的二进制日志操作可以看作一个独立的“存储引擎”
• 二进制日志操作可以关闭，但这样非常不安全
• MySQL 不管单个还是跨存储引擎事务都要 XA 事务
• 缓存
  • 执行计划缓存
  • 查询缓存（默认应关闭此功能，影响服务器扩展性、成为资源竞争单点）：缓存完整的 SELECT 查询结果（跳过解析、优化和执行阶段）
    • 当查询相关表变更时失效；当查询的表被 LOCK TABLES 锁住时，查询缓存仍能返回结果
    • 查询缓存完全使用内存
    • 缓存失效需要竞争一个全局锁
    • 缓存未命中
    • 缓存失效：缓存碎片、内存不足、数据修改
    • Qcache_hits:Qcache_inserts 经验 3:1 最好 10:1，达不到最好就可以考虑禁用查询缓存
    • 最大缓存空间
    • 如果查询缓存达到几十兆这样的级别，是潜在危险的

innode 表计数器

innodb 将每个表的计数器设置成某个事务 ID，而这个事务 ID 就代表了当前存在的且修改了该表的最大的事务 ID

锁的调试

• 锁等待可能发生在服务器级别或存储引擎级别
• 隐式锁和显示锁有相同的结构，由相同的 MySQL 服务器代码控制
• 锁
  • 表锁
  • 全局（读）锁：与任何表锁都冲突
  • 命名锁
  • 字符锁
  • 行锁：for update 锁必须锁住整行，而不仅仅锁索引