MySQL调优大师班-1

1. 性能监控

• show profiles: 性能剖析工具，可以指定具体 type，mysql 5-8 可用

• MySQL performance scheme: 用于取代 show profiles，使用更加简单，存放在内存中，重启后清空

• show processlist: 查看连接的线程个数，用于观察线程状态

2. scheme 和数据类型优化

数据类型优化

• 更小的通常更好
• 简单就好
  简单数据类型的操作通常需要更少的 CPU 周期
  IP 地址可使用 INET_ATON 转换成整型，占用空间小，但是可读性较差
• 尽量避免 null
  如果查询中包含可为 NULL 的列，对 MySQL 来说难以优化，可为 NULL 的列使得索引、索引统计和值比较都更加复杂，通常情况下 NULL 的列改为 NOT NULL 带来的性能提升比较小，但是设计时应尽量避免

实际细则

• 整数类型
  可使用的整数类型: TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 分别使用 8、16、24、32、64 位存储空间，尽量使用满足需求的最小数据类型

• 字符和字符串类型

  • varchar

    1. 根据实际内容长度保存数据
    2. varchar(n) 中 n <= 255 使用额外一个字节保存长度，n > 255 使用额外两个字节保存长度
    3. varchar(5) 和 varchar(255) 保存同样内容时磁盘存储空间相同，但内存占用空间不同(为指定大小)
    4. varchar 在 MySQL 5.6 前变更长度或者从 n < 255 => n > 255 时会导致锁表，5.7 以上则不会

    应用场景

    1. 存储长度波动较大的数据，比如文章
    2. 字符串很少更新的场景，每次更新完都会重算并使用额外存储空间保存长度
    3. 适合保存多字节字符，如汉字、特殊字符等

  • char:

    1. 最大长度 255
    2. 会自动删除末尾的空格
    3. 检索效率、写效率会比 varchar 高，用空间换时间

    应用场景

    1. 存储长度波动不大数据，如 md5 摘要
    2. 存储短字符串、经常更新的字符串

• BLOB 和 TEXT 类型
  MySQL 把每个 BLOB 和 TEXT 值都当成一个独立的对象处理，两个都是为了存储很大的数据而设计的字符串类型，分别采用二进制和字符方式存储

• datetime 和 timestamp

  • datetime
    • 占用 8 个字节
    • 与时区无关，数据库底层时区配置对 datetime 无效
    • 可保存到毫秒
    • 可保存时间范围大
    • 不要使用字符串类型存储日期类型，占用空间大，损失日期类型函数的便捷性
  • timestamp
    • 占用 4 个字节
    • 时间范围：1970-01-01 ～ 2038-01-19
    • 精确到秒
    • 采用整型存储
    • 依赖数据库设置的时区
    • 自动更新 timestamp 列的值
  • date
    • 占用字节数比使用字符串、timestamp、int 存储更少，只需要 3 个字节
    • 可以利用日期时间函数进行日期之间的计算
    • 用于保存 1000-01-01 ～ 9999-12-31 之间的日期

• 使用枚举代替字符串类型
  有时可以使用枚举类型替代常用字符串，MySQL 存储枚举类型会非常紧凑，会根据列表值的数据压缩到 1/2 个字节中，MySQL 在内部会将每个值在列表中的位置保存为整数，并且在表的 .frm 文件中保存 数字 - 字符串 映射关系

• 特殊数据类型
  经常使用 varchar(15) 存储 IP 地址，本质是 32 位无符号整数，而不是字符串，可以使用 INET_ATON() 和 INET_NTOA() 函数进行转换

合理利用范式和反范式

• 范式
  优点:

  • 规范化的更新通常比反范式快
  • 使用比较好的范式时，重复的数据会较少甚至没有
  • 范式化的数据比较小，可以放在内存中，操作较快

  缺点: 通常需要进行关联

• 反范式
  优点:

  • 所有数据都在同一张表中，可以避免关联
  • 可以设计有效的索引

  缺点: 表格内冗余数据较多，删除数据时可能造成某些有用的信息丢失

• 企业中一般范式和反范式混合使用

主键的选择

• 代理主键: 与业务无关的、无意义的数字序列
• 自然主键: 事物属性中的自然唯一标识
• 推荐使用代理主键
  • 不与业务耦合，更易维护
  • 通用的键策略能够减少需要编写的源码数量，减少系统的总体维护成本

字符集的选择

1. 纯拉丁字符能表示的内容没必要选择 latin1 之外的字符编码，因为能节省大量的存储空间
2. 如果可以确定不需要存放多种语言，没必要使用 UTF8 或其他 UNICODE 字符类型，会造成大量存储空间浪费
3. MySQL 的数据类型可以精确到字段，当需要在大型数据库中存放多字节数据的时候，可以通过对不同表的不同字段使用不同数据类型来减少数据存储量，进而降低 IO 操作次数并提高缓存命中率

存储引擎的选择

MyISAM 和 InnoDB

适当的数据冗余

1. 被频繁引用且只能通过 join (2 张甚至更多) 大表的方式才能得到的独立小字段
2. 由于每次 join 仅仅是为了取得某个小字段的值，join 得到的记录又很大，会造成大量不必要的 IO，完全可以通过空间换时间的方式进行优化，但是冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新

适当拆分

当表中存储在类似于 TEXT 或很大的 varchar 类型的大字段的时候，如果对这张表的大部分查询都不需要这个字段，那就应该将其拆分到另外的独立表中，以减少常用数据所占的存储空间，这样做的明显好处就是每个数据块中可以存储的数据条数大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率

3. 执行计划

• 使用 explain 模拟优化器执行 SQL 查询语句，从而了解 mysql 如何处理 SQL 语句

执行计划中包含的信息

• id: select 的序列号，表示查询中执行 select 子句或者操作表的顺序

• select_type: 用于分辨查询类型，普通查询、联合查询、子查询

• table: 表示访问的表名或表的别名，也可能是临时表或者 union 合并结果集

• type: 表示访问类型，代表以什么方式访问数据，比如全表扫描，访问类型效率排序如下:
  system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
  一般情况下得保证查询达到 range 级别，最好能达到 ref

  --all:全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
  explain select * from emp;
  
  --index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，
  --即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序
  explain  select empno from emp;
  
  --range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，
  --适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 
  explain select * from emp where empno between 7000 and 7500;
  
  --index_subquery：利用索引来关联子查询，不再扫描全表
  explain select * from emp where emp.job in (select job from t_job);
  
  --unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引
  explain select * from emp e where e.deptno in (select distinct deptno from dept);
  
  --index_merge：在查询过程中需要多个索引组合使用，没有模拟出来
  
  --ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式
  explain select * from emp e where  e.mgr is null or e.mgr=7369;
  
  --ref：使用了非唯一性索引进行数据的查找
  create index idx_3 on emp(deptno);
  explain select * from emp e,dept d where e.deptno =d.deptno;
  
  --eq_ref ：使用唯一性索引进行数据查找
  explain select * from emp,emp2 where emp.empno = emp2.empno;
  
  --const：这个表至多有一个匹配行，
  explain select * from emp where empno = 7369;
  
  --system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

• possible_keys: 表示可能应用在这条查询中的索引，一个或多个，查询涉及到的字段上若存在索引，则索引将被列出，但不一定被查询实际使用

• key: 实际使用的索引，如果为 null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的字段重叠

• key_len: 表示索引中使用的字节数，可以通过 key_len 计算查询中使用的索引长度，在不损失精度的情况下长度越短越好

• ref: 表示索引的哪一列被使用了

• rows: 根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的函数，此参数直接反映了 SQL 找了多少数据，在完成目的的情况下越少越好

• extra: 额外信息

4. 通过索引进行优化

索引

• 索引的优点

  1. 大大减少服务器所需要扫描的数据量
  2. 帮助服务器避免排序和临时表
  3. 将随机 IO 变成顺序 IO

• 索引的用处

  1. 快速查找匹配 WHERE 子句的行
  2. 从 consideration 中消除行，如果可以在多个索引之间进行选择，MySQL 通常会使用找到最少行的索引
  3. 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
  4. 当有表连接的时候，从其他表检索行数据
  5. 查找特定索引列的 min 或 max 值
  6. 如果排序或分组是在可用索引的最左前缀上完成的，则对表进行排序和分组
  7. 在某些情况下，可以优化查询以检索值而无需查询数据行

• 索引的分类
  主键索引、唯一索引、普通索引、全文索引、组合索引

• 索引采用的数据结构
  哈希表、B+ 树

• 面试技术名词

  • 回表
    为普通列创建的索引的叶子节点存储的不是整行数据，而是主键，使用这个索引查询会先取到主键，然后根据主键在主键的索引中查询到整行数据，就被称为回表
  • 索引覆盖
    查询的列在索引中已经存在，不需要进行回表，就叫索引覆盖
  • 最左匹配
    有时会使用多个列创建组合索引，查询时必须从最左边的字段开始匹配，如果查询条件跳过了最左边的字段，则无法触发索引
  • 索引下推

• 索引匹配方式

  • 全值匹配: 指的是和索引中的所有列进行匹配
  • 匹配最左前缀: 只匹配前面几列
  • 匹配列前缀: 可以匹配某一列的值的开头部分
  • 匹配范围值: 可以查找某一个范围的数据
  • 精确匹配某一列并范围匹配另外一列: 可以查询第一列的全部和第二列的部分
  • 只访问索引的查询: 查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引