日常学习

高性能Mysql

June 26, 2017
Mysql

Mysql 性能优化

mysql 架构

  客户端 ---> 连接/线程处理 ----> 查询缓存
                |               ^
                |               |
                |               |
                |------------> 解析器 ---------> 优化器 ------> 存储引擎
                ( 第一层)      (第二层)          (     第三层          )
  1. 使用用户名，密码，安全套接字ssl，方式连接
  2. 优化执行： 优化器，会解析查询。包括，创建内部解析树，决定表的查询顺序，选择合适的索引等，explaiin，优化器并不关心表的存储引擎，而是隔离！，但是存储引擎对于查询优化是有影响的。优化器会请求存储引擎提供容量、操作的开销信息等，进行优化.
  3. 并发控制， 读写锁（共享锁、排它锁），锁的粒度（表锁、行级锁、）, 事务： 原子性、一致性、 隔离性、持久性，事务日志，通过日志恢复错误的崩溃. 在innoDb中显示的使用所，没有必要，还会严重影响性能。
  4. 存储引擎： Engine: InnoDB, MyISAM(不支持事务、行级锁) 、Archive, Blackhole, CSV, Memory
  5. 引擎选择： 除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以代替，否则都应该优先选择InnoDB引擎，例如使用全文索引，有限使用InnoDB+Sphinx，而不是支持全文所以的MyISAM, 考虑因素： 事务、备份、崩溃恢复
  6. 大数据量： InnoDB 数据量在 3-5TB，10TB以上，需要建立数据仓库， Infobright是比较成功的mysql数据仓库应用， TokuDB也可以
  7. 存储引擎的转换： alter table, 导出导入， 创建与查询， create table innodb_table like myisam_table; alter table innodb_table ENGINE=InnoDB; insert into innodb_table select * from myisam_table

基准测试

todo

服务器性能剖析

  1. 通常为三个问题： 确认服务器是否达到了性能最佳的状态、找出某条语句为什么执行不够快、诊断造成用户卡顿的某些间歇性 性能故障
  2. 性能剖析， 测量服务器的时间花费在那里
  3. 性能定义为： 完成某件任务所需要的时间而不是资源来测量性能。数据库服务器的目的是执行sql，所以关注的任务是查询或者语句。一个陷阱是：资源是用来消耗并且用来工作的。所以有时候消耗更多的资源能够加快查询速度，查询的响应速度更能够提现升级后的性能是不是更好。
  4. 所以如果目标是降低响应时间，那么就需要先搞清楚时间花费在哪里，无法测量就无法有效的优化，所以第一步是： 测量时间花费在什么地方。
  5. 完成任务所需要的时间： 执行时间+等待时间。 而等待时间则的测量比较复杂，可能由其他系统间接导致。基于执行时间的分析研究的是什么任务的执行时间最长，等待的分析：判断任务在什么地方被阻塞的时间最长.
  6. 性能剖析： 1： 测量任务所花费的时间， 2：统计排序， 性能剖析报告会列出所有的任务列表，列出，任务名、任务的执行时间，任务的消耗时间，占比等。
  7. show status, 大部分结果只是一个计数器， FLUSH STATUS; select * from users; SHOW STATUS WHERE Variable_name LIKE ‘Handler%’ OR Variable_name LIKE ‘Create%’
  8. 慢查询日志:

Schema 与数据类型优化

  1. schema 原则：
    • 更小的通常更好（更小的通常意味着占用更小的磁盘、内存）
    • 简单就好, 简单的数据类型耗费更少的CPU周期，（应该使用 mysql内置的数据类型而不是字符串保存日期和时间， 另一个则是使用整数，保存ip）
    • 避免NULL，通常最好指定列为NOT NULL，查询中包含null对于mysql来说更难优化，尽量不要在NUll的列上建立索引。
  2. Int(11) 与 Int(20) 在存储上没有任何区别， 仅仅限制 于 界面交互工具（用于展示位数？） TinyInt(8) SmallInt(16) MediceInt(24) Int(32) BigInt(64)
  3. Float(4), Double(8)： 在对精度要求特别高时候，可以使用 BigInt 来标示（乘以 对应的倍数，无精度损耗）
  4. varchar（变长） char (固定长度), 最好的策略是，只分配真正需要的空间。
    • varchar(1) 占用两个字节（一个字节为len） varchar(10) 与 varchar(200) 存储 ‘hello’ 占用相同的空间， 但是在内存中使用 定长分配空间（即varchar（xx） 中的xx）。所以 即便varchar 变长，其中的数字 也应该尽量满足空间的小。
    • char：定长存储， 自动delete 末尾的空格，
  5. 使用枚举代替 字符串类型，枚举类型的数据结构非常紧凑，可以节省大量的空间。
    • 缺点: 不能够随意改变，对于未来会改变的字符串，不太适合。不建议选用。
    • 排序可以使用FIELD函数进行。

  6. 日期和时间类型： DATETIME， 与时区无关，使用8个字节的存储空间。 TIMESTAMP： 格林尼治标准时间， 处理时区，使用4个字节。默认not NULL

  7. 选择标志符: 标识符可能与其他值进行比较，例如关联操作，标识列可能在另在的表中作为外键使用。所以为标识列选择数据类型时，应该选择跟关联表中对应的列一样的类型。所以不仅需要考虑存储类型，还需要考虑mysql对这种类型怎样执行计算和比较。在可满足的范围的需求，并且预留未来增长空间的前提下，应该选用最小的数据类型
    • 整数类型： 通常是最好的选择

    • 字符串类型： 如果可能，应该避免使用字符串作为标识列， 存在如下问题：

      1. 消耗空间，比数字类型慢
      2. 对于md5， sha1,uuid类型产生的字符串，会导致insert以及select语句变得慢。因为插入值会随机的写入到索引的不同位置，所以insert慢，select慢，是因为逻辑上相邻的行，会分不在磁盘、内存的不同地方，随机值导致缓存的效果也会很差，因为会导致缓存赖以工作的局部性原理失效。UUID，应该移除「-」符号, 对比sha1，虽然分布也不均匀，但是还有有一定顺序。
    • 所以按照这样的说法，一些，需要处理id的地方，比如用户的主键，直接使用integer，可以另外设定一个字段展示。
    • 特殊数据类型： IP v4地址， 人们经常使用 varchar(15)来保存，然而它是32位无符号正数，不是字符串， INET_ATON, INET_NTOA 函数 可以在 ip integer 与 8 位地址表示法之间转换
    • 为了提升读查询的速度，需要建立一些额外的索引，增加冗余列，增加了写的负担，需要额外的维护任务，但是读操提升了。这是常见的技巧
  8. alert table 执行大部分修改表结构的方法是： 使用新的数据结构创建一个空表，从旧的表中查询所有数据插入到新表，然后删除旧表。这样的操作可能需要花费很长时间，如果内存不足而且表又很大，很多索引的情况下尤其如此。
    • 下面的一些操作，是可能不需要重建表的， 而是直接修改.frm文件
    • 移除 一个列的 auto_increment操作
    • 增加、移除、或者更改 ENUM， SET
    • 修改 列的默认数值

创建高性能的索引

  1. 索引对于良好的性能的关键，尤其是当表中的数据量越来越大时。索引优化应该是针对查询性能优化最有效的手段了，索引能够情谊的讲查询性能提高到几个数量级
  2. 索引类型：索引是在存储引擎而不是在服务层实现的，所以并没有统一的索引标准，
    • B-Tree, 人们多半讨论的是B-Tree索引，但是存储引擎依然使用的是不同的结构， InnoDB使用的为B+Tree， B-Tree对索引列是顺序组织的，适合范围查找，限制： 如果不是按照 声明顺序的最左列开始查找，则无法使用索引，不能跳过中间索引，所以：可能需要使用相同的列，但是不同顺序的索引满足查询要求。
    • 哈希索引： 将所有索引生成哈希码
  3. 索引的优点：
    • 大大减少了服务器需要扫描的数据量
    • 可以帮助服务器避免排序和临时表
    • 可以将随机io 变为 顺序io
  4. 高性能索引策略, 正确的创建和使用索引是实现高性能查询的基础。

查询性能优化

  1. 大部分性能底下的查询都可以通过减少访问的数据量的方式进行
  2. 是否扫描额外的数据：1：响应时间， 2： 扫描的行数， 3：返回的行数
  3. explain 类型，有： 全表扫描(All)， 索引扫描(index)， 范围扫描(range )， 唯一索引查询(primary)，常数引用(const)
  4. 发现扫描大量数据但是返回很少的行的情况的优化方法：
    • 使用索引， 将需要的列，放入到索引中
    • 改变库表的结构，例如使用单独的汇总表
  5. 重构查询：
    • 切分查询，例如，删除数据，分散的删除数据， 而不是一次性删除
    • 分解关联查询： 简单的， 可以对没一个表进行查询，然后将结果在应用程序中，进行关联， 几个有点： 1：缓存的效果v更高， 2：查询分解后，减少锁的竞争。 3：查询效率提升（单个查询）， 4： 减少冗余记录的查询
  6. 查询执行基础：
    • 客户端、服务器通信协议为半双工，任何时刻都一端发送数据。一旦客户端发送了请求，能做的事情，就只有等待结果。
    • mysql的连接（线程）的状态有： sleep, query, locked, analyzing and statistics, cpoying to tmp table, sorting result, sending data
    • 排序优化：加索引
    • 关联查询： 确保 join on 中的列上有索引， 只需要在关联顺序中的第二个表的对应列上创建索引
    • 优化limit：一般会加上合适的order by语句， 如果有索引，效率会非常不错。 2： 大偏移量问题： 限制

索引：

  1. 索引目的：加速查询， 帮助建立强制约束(unique, foreign key), 查询可以没有index，但是会浪费很多时间
  2. 索引类型：
    • Btree indexes
    • rtree indexes(myisam only)
    • hash indexes(memory NDB)
    • bitmap indexes(not support mysql)
    • full text indexes(mysql 5.6 after)
  3. 聚簇索引（B+Tree）： 并非单独的Index类型，而是一种数据的存粗放式，叶子结点为 ID + row. 二级索引： 叶子节点 仅为： ID（所以使用二级索引查询数据时，需要先从 二级索引中找到 row的ID，从 主索引中 二次查询 数据(回表)）。 聚簇为： 将相关的row聚集到同一物理页面中。
  4. 前缀索引: varchar类型的列，使用varchar(7) 前缀字符作为索引. order by, group 不能 使用 前缀索引
  5. prefix LIKE 是一种特殊的范围查询, ‘ABC%’ 可以使用索引, ‘%ABC’ can’t use
  6. 索引是有代价的，不要加太多的索引，在大部分的情况下， 拓展一个索引比加一个新的索引要好。在写入的时候，是索引的主要代价， 读取，会耗费多余的空间
  7. 索引应用： data lookups, sorting, avoiding reading data, special optimizations
  8. Index 有效的情况：
    • 全值匹配
    • 最左 prefix (prefix like)
    • range 查询：1th range查询
    • 1th 全值查询， 2th 范围查询
    • 使用Id查询
  9. Index 的限制：
    • 非最左列开始 不起作用： Index(A,B,C), where B = b 不能使用 Index
    • 不能 skip middle 列： 如 where A = a and C = c, 只能使用 A上的Index，不能使用 A，C 列上的索引
    • 中间使用range 查询之后， 后面的列 不能使用索引： where A = a and B like ‘B%’ and C = c 只能使用A 列上的索引
    • 所以Index中的 列的order 非常重要
  10. 覆盖查询： select index中的列，并命中index， 则 不需要 select * 造成的 二级索引 查询 再返回到 聚簇索引 中的二次查询（称为回表）
  11. 多个字段索引的技巧:
    • index (A, B, C) 建立索引的顺序是很重要的
    • 可以使用索引的查询：
      • A > 5
      • A = 5 and B > 6
      • A = 5 and B = 6 AND C = 7
      • A = 5 and B IN (2, 3) AND C > 5
    • 不能使用索引的情况：
      • B > 5
      • B = 6 and C = 7
    • 使用部分索引的：
      • A > 5 and B = 2
      • A = 5 and B > 6 and C = 2
    • sorting
    • index(A, B)
    • sort 使用索引: 情况
      • order by A (使用1th 列进行排序)
      • A = 5 and order by B (1th equal filter 2th order)
      • order by A desc, B desc (sort in same order)
      • A > 5 order by A (range on the column, sorting on the same)
    • 不会使用索引:
      • order by B (sorting by 2th column)
      • A > 5 order by B (range on the 1th column, sorting by second)
      • A in(1, 2) order by B(In-rang on the 1th column, 2th order)
      • order by A asc, B desc(sorting in different order)
    • 应用规则： 列的 排序方向 相同, 不在order by中的字段， 只能使用 =， In 都不行 （结合上面的示例情况）
    • 避免加载数据：索引比数据要小很多， 获取数据可能是非常不连续的IO操作，尽可能使用 覆盖索引
    • 索引帮助 mix/ max 函数加快，但是只有这些。
    • 选择索引前缀长度： 1. 保证尽量少的row共享同样的前缀 （即 选择性高）
    • explain 101:
      • type good -> bad: system, const, eq_ref, ref, range, index, ALL
      • rows 数值越大，意味着越慢
      • key_len 展示　多少key真正的使用到了
      • extra good -> bad: index, filesort, temporary
  12. 索引策略：
    • 前缀索引: varchar类型的列，使用varchar(7) 前缀字符作为索引
    • 多个多独立列索引 大部分情况下 都不能提高查询性能 把where条件中的列都加上索引是错误的！
    • 选择合适的索引顺序。 B-Tree中索引的顺序意味着， 首先按照最左列进行排序， 其次是第二列，将选择性 最高的列，放在索引前面
    • 聚簇索引：主键中为B-Tree， 其他索引中，为B-tree，但是叶子节点中存储的为主键， 通过主键在次查询到具体数据.
    • 在InnoDB中按照主键顺序的插入行，最好避免随机，会带来没必要的分页存储( 数据存储 达到一定的阈值（占页的百分比）就会产生新页进行存储)。大量的数据插入可能会导致AUTO_INCREMENT锁竞争，可以Innodb_autoin_lock_mode中的配置
    • ? 使用索引扫描来做排序， 通过排序操作，按照索引顺序排序, explain出来 type为index， 则说明使用了索引排序
    • 删除冗余和重复索引
    • 删除未使用的索引
    • 索引会有效的减少需要lock的数据，
  13. 一些常见误解 情况：
    1. count 查询：
      • count(*)返回结果集的行数
      • count(A) 返回列A value != null 的行数。
      • 非常难以优化（mysql 不可避免的需要全部遍历所有行来确定 返回数值） 可以使用 count(A) A 为不为0 的覆盖索引 来优化查询(？) 或 增加统计表
    2. limit: 分页： offset 非常大时，严重影响性能。可以使用 order 利用Index 来加快查询。优化方法：
      • 尽可能使用 覆盖索引 查询， 然后在做一次 对应列的 查询
      • 使用ID记录 上次数据的查询位置，通过 where id > ID limit 避免offset

        Explain 中的字段解读：

  14. type 字段： 从 坏到好 排序
    • all： 全表扫描，
    • index： 索引扫描，同样是 耗时操作（在Extra column中 出现Using Index 则为 使用 覆盖索引， 要明显好于 二次回表操作）
    • range： 在索引内部范围查找， 好于Index（全部的索引扫描）
    • ref： 使用索引 返回 单个row， ref_or_null 是同样的ref 类型，
    • eq_ref： 使用素音 返回最多一个row， 使用 unique index 或者 primary key 时，经常出现该种情况
    • const: 使用 primary key 时 经常出现。
    • NULL: mysql 不需要使用 索引或表 就能够解决的问题， 比如 select index 上min value
  15. Extra
    • Using index: 使用 覆盖索引，避免回表操作
    • Using where: 在 store engine 返回之后 mysql server 回再次 filter
    • Using temporary： 使用临时表 对 返回结果进行 排序操作
    • Using filesort: 需要在 disk 或者 ram上对结果进行排序
    • Range checked for each record (index map:N). This value means there’s no good index, and the indexes will be reevaluated for each row in a join. N is a bitmap of the indexes shown in possible_keys and is redundant.
    • Using index condition: Tables are read by accessing index tuples and testing them first to determine whether to read full table rows.
  16. possible_keys： 该查询中 可能 使用的 index 名称
  17. key: 优化器决定使用的 索引名称（可能基于其他原因导致与 possible_keys不同
  18. ref: 该字段显示： 使用该字段数值 在 key 字段中（优化器决定使用的） 进行查找 或者 constant

    Mvcc & Lock:

    • 情况测试： repeatable read level:
      id, b
      2 6
      4 6
      1 7
      6 7
      3 8

TA: start transaction;
select * from t2 WHERE b in (6, 7) for update;

4 lock struct(s), heap size 1248, 10 row lock(s)
MySQL thread id 263, OS thread handle 0x7fecb0b48700, query id 40915 localhost 127.0.0.1 root

TB: start transaction;
insert into t2 (id, b) values (8, 3); //block

LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 270, OS thread handle 0x7fecc953d700, query id 40917 localhost 127.0.0.1 root update
insert into t2 (id, b) values (8, 3)
——- TRX HAS BEEN WAITING 33 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 18123 n bits 72 index b_index of table test.t2 trx id E5C6 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 4; hex 80000002; asc ;;

第二次：

TA: start transaction

select * from t2 WHERE b in (6, 7) for update;

TB: start transaction;
insert into t2 (id, b) values (10, 10);
insert into t2 (id, b) values (11, 20);
insert into t2 (id, b) values (12, 3); // block

TA: start transaction;
update t2 set b = 9 WHERE b in (6, 7);

—TRANSACTION E5CD, ACTIVE 12 sec
2 lock struct(s), heap size 376, 6 row lock(s), undo log entries 4
MySQL thread id 263, OS thread handle 0x7fecb0b48700, query id 40961 localhost 127.0.0.1 root

TB: start transaction;
insert into t2 (id, b) values (10, 10); // block

mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 270, OS thread handle 0x7fecc953d700, query id 40963 localhost 127.0.0.1 root update
insert into t2 (id, b) values (10, 10)
——- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 18122 n bits 80 index PRIMARY of table test.t2 trx id E5CE lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

TA: start transaction;
update t2 set b = 9 WHERE b in (6, 7);

—TRANSACTION E5D0, ACTIVE 104 sec
2 lock struct(s), heap size 376, 6 row lock(s), undo log entries 4
MySQL thread id 263, OS thread handle 0x7fecb0b48700, query id 40970 localhost 127.0.0.1 root

TB: start transaction;
insert into t2 (id, b) values (20, 0); // block

LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 270, OS thread handle 0x7fecc953d700, query id 40985 localhost 127.0.0.1 root update
insert into t2 (id, b) values (20, 0)
——- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 18122 n bits 80 index PRIMARY of table test.t2 trx id E5D3 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

TA: start transaction;
update t2 set b = 9 WHERE b = 6;
TB: start transaction;
insert into t2 (id, b) values (20, 4); // block

TA: start transaction;
update t2 set b = 9 WHERE b = 6;
TB: start transaction;
insert into t2 (id, b) values (20, 20);
insert into t2 (id, b) values (20, 4); // block

TA: start transaction;
select * from t2 WHERE b = 6 for update;
TB: start transaction;
insert into t2 (id, b) values (20, 4); //block

TA: start transaction;
select * from t2 WHERE b = 6 for update;
TB: start transaction;
insert into t2 (id, b) values (21, 9);
insert into t2 (id, b) values (20, 4); // block

lock 实验 总结的大概结论为：
explain type 为ref 时候， 可以导致 gap外 可以insert， 如果gap上 为S锁， 则 可以被其他的 Transaction 获取S锁，
非ref/ const， 时， 加 record lock asc - supernum, 导致 前后都无法insert， update/share

以表为例： 表结构为： id, b (这里select for update 与 update delete 的lock 策略应该相同)
sql1: update set b = 3 where b = x, 则在 < x 的第一个row (x - 2) — x 设定 gap lock（X锁）， x 设定 record lock。 所以不能在 (x-2)..x 上insert数据， 可以在 0…(x-2) (x..supernum) 上 insert new row 。 所以任何 试图在 (x-2)..x 范围内进行 update, insert 都会被阻塞。(这里面存在一个问题即 b = 3 上是如何处理的？ 这里面可能涉及到 聚簇索引 与 二级索引 的关系问题， ？ 在insert b = 3 同样回阻塞， update where b = 3 同样)
sql: update set b = 3 where b in (x, y) type 为 index 导致 在 asc - supernum 范围内，全部上锁，即 类似锁表效果， 在范围内的insert， update 都会被 阻塞。
sql: update set b = 10 where id = x 与 sql1 相同，但是 需要综合 考虑 二级索引 与 聚簇索引。
sql: 任何其他的sql 都会锁住全部的 rows，导致 insert update 无法有效并发进行

参考资料为： http://mysql.taobao.org/monthly/2016/01/01/

这里面提到了， 聚簇索引 + 二级索引，也就是说 不同的sql 使用索引不同 将导致加锁 顺序不同， 即： 存在两种情况， 1）先lock 聚簇（Record lock） 然后 更新 二级索引， 2) 先lock 二级索引， 再获取 聚簇上的lock 。也就是说存在 两种加锁路径： 1） 使用lock 进行搜索，然后 修改二级索引 树， 2） 使用二级索引搜索，然后 修改 聚簇数据，二级索引树。