MySQL

《高性能MySQL》阅读笔记三

1. schema与数据类型优化

1.1 数据类型选择

更小: 选择不超过需求范围的最小类型

更简单

避免使用Null: 含有Null列会使索引,索引统计和值更为复杂

分配空间: 根据实际需要分配.使用内存临时表或操作时会比较糟糕

特殊类型:书中举例,ip地址应该用无符号整数存储,MySQL提供inet_aton, inet_ntoa方法转换

1.2 schema 设计陷阱

  1. 太多列,存储引擎api需要在服务器层和存储引擎层通过行缓冲格式拷贝数据,然后解码.转换代价依赖于列的数量

  2. 太多的关联,单个查询最好在12个表以内

  3. 最好不用枚举enum

  4. 可以使用其他”空值”替代Null

1.3 范式和反范式

  1. 范式化: 每个事实数据只出现一次

  2. 反范式化: 信息是冗余的

  3. 第一范式1NF: 关系中的每个属性都不可再分

  4. 第二范式2NF: 每个表中的非主属性完全依赖于码(例如主键, 可以唯一决定属性集合)

  5. 第三范式3NF: 消除非主属性之间的依赖关系，只保留非主属性与码的依赖关系

  6. 范式化优点: 更新操作更快,占用空间更小

  7. 范式化缺点: 表的关联查询更多

  8. 混用范式和反范式: 从父表冗余一些数据到子表有利于排序, 缓存衍生值减少子查询计算

1.4 缓存表和汇总表

业务上有时需要一张完全独立的汇总表或缓存表主要用于满足检索的需求

  1. Flexviews实现物化视图,可以增量重新计算物化视图的内容

  2. 计数器表: 如果需要在表中保存计数器,更新计数器时,会有全局的互斥锁.要获的高并发更新的性能,可以将计数器保存在多行,每次随机选择一个进行更新

1.5 修改表结构

  1. 大部分的alter table操作将导致服务中断

  2. 所有的modify column 操作都将导致表重建

  3. 可以新建一个.frm文件为修改后的表结构,替换原来的.frm文件避免表重建

2. 创建高性能的索引

索引是存储引擎用于快速查找记录的数据结构

2.1 索引基础

MySQL中,存储引擎先在索引中找到对应值根据匹配的索引记录找到对应的数据行.索引在存储引擎层实现

2.1.1 B-Tree索引

  1. 目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构.

  2. InnoDB使用B+Tree, NDB集群存储引擎实际使用T-Tree. 索引对多个值的排序一句是根据表定义索引时列的顺序.

  3. 索引对如下类型的查询有效

  4. 全值匹配

  5. 匹配最左前缀,只使用索引第一列

  6. 匹配列前缀: 只匹配某一列值的开头部分

  7. 匹配范围值

  8. 精确匹配某一列并范围匹配另外一列

  9. 只访问索引的查询: 查询只需要访问索引无需访问数据行(覆盖扫描)

  10. 限制

  11. 如果不是按照索引的最左列开始,则无法使用索引

  12. 不能跳过索引中的列,即定义索引(a,b,c),则使用a,c查询条件时,只能使用索引第一列

  13. 如果查询中有某个列的范围查询,则右边所有的列都无法使用索引优化查找

2.1.2 哈希索引

  1. 对于每一行数据,存储引擎会对索引列计算一个哈希码.索引包含哈希码和行指针

  2. MySQL中只有Memory存储引擎支持

  3. 限制

  4. 不能使用索引中的值避免读取行

  5. 无法用于排序

  6. 不支持部分索引列匹配查找

  7. 不支持范围查询

  8. 出现哈希冲突时会扫表

2.1.3 伪哈希索引

  1. 应用: 当存储引擎不支持哈希索引,在B-Tree基础上使用哈希值索引查找

  2. 场景: 需要存储大量的url并根据url进行搜索查找

  3. 问题: 如果使用B-Tree存储,内容很大

  4. 方案: 删除url列索引,新增一个被索引的url_crc列,使用CRC32做哈希.

  5. sql: select id from t_url where url=”https://www.google.as/" and url_crc=CRC32(“https://www.google.as/")

  6. 结果: MySQL优化器会使用选择性很高体积很小的基于url_crc索引完成查找

  7. 缺陷: 需要维护哈希值, crc32表大时会出现大量冲突

  8. 注意: 不要使用SHA1和MD5做哈希函数,因为计算出来的哈希值比较长; 必须在where子句中包含常量

  9. 插件: 移植自Percona Server的FNV64可以在MySQL中作为哈希函数使用

2.1.4 空间数据索引(R-Tree)

  1. MyISAM表支持空间索引,可以用作地理数据存储

2.1.5 全文索引

  1. 全文索引是一种特殊类型索引,查找文本中的关键词,后续讨论

2.1.6 其他

  1. TokuDB 使用分形树索引, 对于InnoDB的讨论也适用于TokuDB

  2. ScaleDB 使用Patricia tries

  3. InfiniDB和Infobright使用一些特殊的数据结构优化某些特殊的查询

2.2索引的优点

  1. 减少扫描数据量

  2. 避免排序和临时表

  3. 将随机I/O变成顺序I/O

  4. 对于TB级别的数据,经常会使用块级别的数据技术来替代索引

2.3 高性能的索引策略

2.3.1 独立的列

  1. 索引列不能是表达式一部分或函数参数,否则不能使用索引

2.3.2 前缀索引

  1. 场景: 需要索引很长的字符列,这会让索引变得大且慢

  2. 方案: 根据业务找到最适合的前缀长度,创建前缀索引

  3. 优点: 索引更小,更快

  4. 缺点: MySQL无法使用前缀索引order by 和group by,也无法使用前缀索引做覆盖扫描

  5. 常见: 用16进制唯一id存储session id,如果采用长度为8的前缀索引能显著提升性能

2.3.3 多列索引

  1. MySQL的索引合并(index merge)策略,一定程度上可以使用多个单列索引定位

  2. where查询条件的列最好使用多列索引而不是单独列单独索引

2.3.4 选择合适的索引顺序

  1. 将选择性最高的列放在索引最前列

  2. 性能不只依赖于所有索引列的选择性,也和查询条件的具体值分布有关,可能需要根据运行频率最高的查询调整索引列顺序

  3. 有时需要根据排序,分组和范围条件综合考虑

2.3.5 聚簇索引

  1. 聚簇索引并不是单独的索引类型,而是一种数据存储方式.叶子页包含行的全部数据,节点页只包含索引列

  2. InnoDB默认使用主键聚集数据,如果没有会选择一个唯一的非空索引作为聚簇索引

  3. 可以把相关数据保存一起,减少磁盘I/O

  4. 数据访问更快

  5. 插入速度严重依赖插入顺序

  6. 更新聚簇索引代价高

  7. 可能面临”页分裂”问题,导致占用更多磁盘空间

  8. 可能导致全表扫描变慢,由于行比较稀疏或页分裂导致数据存储不连续

  9. 二级索引变大,二级索引访问需要两次索引查找

2.3.6 覆盖索引

  1. 如果一个索引包含所有需要查询的字段的值

  2. 覆盖索引必须要存储索引列的值

  3. MySQL不能在索引中执行like 操作,但能在索引中做最左前缀匹配的like比较,因为可以转换为简单的比较操作

  4. 延迟关联: 先通过覆盖索引返回需要的主键再通过这些主键关联原表获得需要的行

2.3.7 使用索引扫描做排序

  1. 只有索引列的顺序和order by 子句的顺序一致时才能使用索引对结果做排序

  2. 如果关联多表,则只有当order by子句引用的字段全部为第一个表时才能使用索引排序

2.3.8 压缩索引

  1. MyISAM使用前缀压缩减少索引的大小

  2. create table 时通过指定pack_keys控制压缩方式

2.3.9 未使用的索引

  1. 通过查询INFORMATION_SCHEMA.INDEX_STATISTICES能查到每个索引的使用频率

  2. 冗余和重复的索引会降低性能

2.3.10 索引和锁

  1. InnoDB在二级索引上使用共享(读)锁,但访问主键索引需要排他(写)锁,消除了使用覆盖索引的可能

2.4 维护索引和表

  1. check table 通常能够找出大多数的表和索引的错误

  2. MyISAM表易损坏,InnoDB不容易,损坏可能是硬件或人为错误

  3. MySQL查询优化器通过两个api,一个获取范围大概数据量,一个返回各种类型的数据包括索引基数

  4. 减少索引和数据的碎片,B-TREE索引可能碎片化,这会降低效率

MySQL

《高性能MySQL》阅读笔记二

1. 基准测试

1.1 应用

验证系统的一些假设

重现系统异常

测试系统当前的运行情况

模拟更高的负载

规划业务增长

测试应用适应可变环境的能力

测试不同硬件,软件和操作系统的配置

1.2 策略

针对整个系统的整体测试, 集成式

单独测试MySQL, 单组件式

1.3 测试指标

  1. 吞吐量: 在线事务处理(OLTP),单位为每秒事务数TPS

  2. 响应时间或延迟: 百分比响应时间较为代表性

  3. 并发性: 任意时间有多少同时发生的并发请求. 关注的是正在工作中的并发操作; 通常是为了测试应用在不同并发下的性能(吞吐量和响应时间)

  4. 可扩展性

1.4 方法

避免常见错误

设计和规划基准测试

基准测试的运行时间设置

获取系统的性能和状态

收集分析绘图

1.5 工具

集成式测试工具

  1. ab: Apache http服务器基准测试工具,测试服务器qps, 针对单个url进行尽可能快的压力测试

  2. http_load: 对Web服务器,可以通过输入文件对多个url进行随机测试

  3. JMeter: 测试Web应用和其他入FTP服务器或通过JDBC进行数据库查询测试

单组件式测试工具

  1. mysqlslap: 模拟服务器负载并输出计时信息

  2. sqlbench: 测试服务器执行查询的速度

  3. super smack: mysql 和postgresql的基准测试工具

  4. database test suite

  5. sysbench: 多线程系统压测工具, 支持mysql,操作系统,和硬件测试

2. 服务器性能剖析

2.1 原则

  1. 性能即响应时间无法;

  2. 无法测量就无法有效优化

2.2 书中推荐工具

  1. New Relic: 收集分析数据,提供web界面

  2. xhprof: Facebook开源的轻量级PHP性能分析工具

  3. Percona Toolkit: pt-query-digest 生成慢查询日志分析报告

2.3 剖析查询

  1. SHOW PROFILE: 将profiling开启后,执行的sql的剖析信息会被记录到临时表, 通过 show profile from query (index)命令即可以获取到执行的过程信息

  2. SHOW STATUS: 非剖析工具,主要用在对于执行完后观察某些计数器的值

  3. 使用慢查询日志

2.4 诊断间歇性问题

  1. show global status: 通过某些计数器的”尖刺”或”凹陷”分析问题

  2. show processlist: 观察是否有大量线程处于不正常的状态或其他不正常特征

  3. 使用innotop: 实时地展示服务器正在发生的事情，监控innodb,监控多个MySQL实例

  4. 使用慢查询日志

2.5 其他剖析工具

  1. 自带的一些INFORMATION_SCHEMA统计表

  2. 使用strace工具,pt-iopfofile工具生成I/O活动报告

MySQL

《高性能MySQL》阅读笔记一

1. MySQL服务器逻辑架构

连接/线程处理: 基于C/S的工具类似,实现连接处理,授权认证,安全等.

查询缓存/解析器: 实现查询解析,分析,优化,缓存.内置函数和跨存储引擎如存储过程,触发器,视图等.

存储引擎: 数据的存储和提取.不会解析sql,独立与上层服务器通过api进行通信.

2. 并发控制

每种存储引擎有不同的锁策略和锁粒度

表锁是MySQL中最基本,开销最小的策略,阻塞其他用户对该表的读写操作.写锁比读锁有更高的优先级.

行级锁: InnoDB和[XtraDB][1],行级锁只在存储引擎实现

事务: ACID, 原子性,一致性,隔离性(isolation),持久性(durability).

MySQL默认的[事务隔离级别][2]为可重复读(REPEATABLE READ)

死锁: 不同引擎处理方式不同.InnoDB 处理死锁方法,将持有最少行级排他锁的事务进行回滚

多版本并发控制(MVCC): 如InnoDB, 通过在每行记录后面保存两个列存储创建和删除时的系统版本号.

3. 存储引擎

创建表时,MySQL会在数据库子目录下创建一个同名.frm文件保存表的定义

InnoDB: 处理大量的短期事务, 通过间隙锁锁定查询涉及的行和索引中的间隙进行锁定,防止幻影行的插入, 基于聚簇索引创建表.通过一些机制和工具支持真正的热备份.**一般优先考虑InnoDB存储引擎**

MyISAM: 包括全文索引,压缩,空间函数,延迟更新索引键等特性,不支持事务和行级锁,崩溃后无法安全恢复.对于只读数据,表小可以忍受修复操作可以使用该引擎.

如果要使用全文索引,建议用InnoDB加上Sphinx组合替换MyISAM

使用引擎考虑:事务,备份,崩溃恢复,特有特性

日志型应用: MyISAM 或者Archive,因为开销低插入速度快更为适合

对于大数据量需要建立数据仓库,[Infobright][3]和[TokuDB][4]是比较好的解决方案

[1]: https://mariadb.com/kb/zh-cn/about-xtradb/

[2]: https://zh.wikipedia.org/wiki/事務隔離

[3]: http://www.csdn.net/article/2014-08-26/2821379

[4]: http://blog.jobbole.com/109104/