mysql优化

基础

  • 使用 InnoDB 存储引擎，UTF8 字符集
  • 禁止使用存储过程、视图、触发器、Event
  • 通过 Host 连接，而不是 IP
  • 主从通过 master、slave 区分
  • 库名、表名、字段名：snake_case，不超过32个字符，见名知意，禁止拼音英文混用

表结构

  • 表数量少于 500，字段数少于 30

  • 必须有主键，如自增主键

主键递增，数据行写入可以提高插入性能，可以减少表碎片
主键要选择较短的数据类型，减少索引的磁盘空间，提高索引的缓存效率
无主键的表删除在 row 模式的主从架构中会导致备库夯住

  • 禁止使用外键

外键会导致表与表之间耦合，UPDATE 与 DELETE 操作都会涉及相关联的表，影响性能

字段

  • 必须把字段定义为 NOT NULL 并且提供默认值

使 索引/索引统计/值比较 都更加复杂，更难优化
内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，性能会降低很多
需要更多的存储空间，无论是表还是索引中都需要额外的空间来标识
处理的时候只能采用 IS NULL 或 IS NOT NULL

  • 禁止使用 TEXT、BLOB 类型

BLOB 即 SMALLBLOB，TEXT 即 SMALLTEXT，都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符串的方式来存储
非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

  • 禁止使用 ENUM，可使用 TINYINT 代替

ENUM 的内部实际存储就是整数
MySQL 在存储枚举的时候非常的紧凑，会根据列表值的数量压缩到一个或者两个字节中
MySQL 在内部会将每个值在列表中的位置保存为整数，并且在表的 .frm 文件中保存”数字-字符串”映射关系的”查找表”

  • 禁止存储大文件或者大照片

放文件系统如 FastDFS，数据库存 URI

  • 关于数字类型

MySQL 可以为整数类型指定宽度，如 INT(1) INT(20)，但是这对大多数应用是没有意义的
MySQL 所指定的整数类型的宽度只是用于设置一些 MySQL 客户端显示字符的个数，对于存储和计算而言， INT(1) 和 INT(20) 不会限制值的合法范围，这两种类型都是相同的
存货币时要么使用高精度的 DECIMAL(14, 3)，要么直接乘 10 转成整数

索引

InnoDB 引擎支持 B+ 树索引、全文索引（5.6 之后）和哈希索引

传统意义上的索引就是指的 B+ 树索引，这是目前关系型数据库系统中查找最为常用和有效的索引，其构造就是采用了二叉树的思想，根据键值对快速找到数据

通过 B+ 树索引找到被查找数据行所在的页，然后数据库把页读入到内存，再在内存中进行查找，找到对应的数据

InnoDB 使用的是行锁，只有在修改行时，才会对行进行加锁

使用索引能够使得数据在查询过程中锁定更少的行，增加了数据处理的并发性，提高了数据库的性能

索引设计规范

  • 单表索引建议控制在 5 个以内

  • 单索引字段数不允许超过 5 个

超过 5 个时已经很难有效过滤数据了

  • 禁止在更新十分频繁、区分度不高的属性上建立索引

更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能
“性别”这种区分度不大的索引是没有意义的，不能有效过滤数据，性能与全表扫描类似

  • 建立组合索引，必须把区分度高的字段放在前面

能够更加有效的过滤数据

SQL 使用规范

优化核心是 EXPLAIN 语句

  • 禁止使用 SELECT *，只获取必要的字段，需要显示说明列属性

读取不需要的列会增加 CPU、IO、NET 消耗
不能有效的利用覆盖索引
容易在增加或者删除字段后出现程序 BUG

  • 禁止在 WHERE 条件的属性上使用函数或者表达式

  • 禁止负向查询，以及 % 开头的模糊查询

  • 禁止大表使用 JOIN 查询和子查询

会产生临时表，消耗较多内存与 CPU，极大影响数据库性能

MySQL 连接状态

MySQL 客户端和服务器之间的通信协议是“半双工”的，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器端发送数据，两个动作不能同时发生

对于每一个时刻，可以通过命令 show full processlist 来查看当前连接的状态（Command 列就代表当前的状态）：

  • Sleep：线程正在等待客户端发送新的请求
  • Query：线程正在执行查询或者正在将结果发送给客户端
  • Locked：线程正在等待表锁
  • Sorting result：线程正在对结果集进行排序
  • Copying to tmp table [on disk]：线程正在执行查询，并且将其结果集都复制到一个临时表中

这种状态要么是在做 GROUP BY 操作，要么是文件排序操作，或者是 UNION 操作
如果状态上有 on disk 的标记，那么表示 MySQL 正在将一个内存临时表放到磁盘上

  • Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
  • Sending data：这表示线程或者在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据

哈希索引的弊端

  • 哈希索引只支持等值比较查询 = IN 和 <=> 查询，不能使用范围查询

由于哈希索引比较的是进行哈希运算之后的哈希值，所以它只能用于等值的过滤，不能用于基于范围的过滤
因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和 Hash 运算前完全一样

  • 哈希索引无法被用来避免数据的排序操作

同上，哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序

  • 哈希索引不能利用部分索引键查询

因为哈希索引始终是使用索引列的全部内容来计算哈希值的

  • 哈希索引在任何时候都不能避免表扫描

出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行

  • 哈希冲突很多的话，索引维护操作的代价也会很高

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联
从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大