MySQL性能调优

MySQL调优的理解

首先分析SQL的执行计划,主要关注执行计划的列有：
id列:它决定了SQL语句各部分的子句的执行顺序;
type列:表示MySQL执行查询时的访问类型,该值是一个重要指标,它的级别代表着SQL执行性能的好坏,常见的级别有const,eq_ref,ref,range,index,ALL;越靠后的级别代表SQL性能越差;
possible_key列:表示SQL语句可能用到的索引;
key列:代表SQL实际用到的索引;
key_len列:表示使用到的索引的最大长度;
rows列:当全表扫描时表示预计需要扫描的行数,如果使用索引查询时,rows代表预计扫描的索引记录行数;
Extra列:用来说明一些额外信息,可以通过额外信息来准确理解MySQL到底如何执行给定的查询语句;常见额外信息有:Using index：覆盖索引;Using filesort：使用了文件排序;Using temporary：使用了临时表;

通过这些列信息可以了解到SQL语句的实际执行状况,然后找出问题,做出优化;但是除了这些SQL还可能在其他情况下出现性能不佳的情况,SQL语句的响应时间 = 执行SQL语句的时间 + 排队时间;这里的排队时间可能也会过长,比如等锁,等IO时间过长
都是有可能的,所以也可以使用show profile来查看SQL的执行成本或者查看binlog日志也可以查看SQL执行时间和排队时间;

范式和反范式的选择

字段数据类型优化

字段优化基本原则：
1. 更小的通常更好：一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU的高速缓存，并且处理时需要的CPU周期也更少(cpu高速缓存能够放更多的数据)。
2. 简单就好：整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。
比如通常使用时间戳存储日期和时间而不使用字符串;
3. 需要建立索引的列尽量避免NULL：对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。当可为NULL的列被索引时，每个索引记录需要一个额外的字节。

具体类型优化:
整数类型:
tinyint(8位，1字节), smallint(16位, 2字节), mediumint(24位, 3字节), int(32位, 4字节), bigint(64位, 8字节),整数类型当勾选了无符号(unsinged)时只能为整数,且存储正数范围可以扩大一倍;
比如业务中类型，状态这些都可以列举出来它有几种;这种时候就用Tinyint;
tinyint(1)与tinyint(4)是存储的数据长度是一样的;只有字段指定zerofill是有用;
如tinyint(4)如果实际值是2，如果列指定了zerofill，查询结果就是0002，左边用0来填充;

实数类型:
float(4个字节),double(8个字节),decimal(65个数字,用于存储精确的小数);
CPU直接支持原生浮点数的计算，而不支持decimal的计算, decimal的计算由MySQL自己实现,所以浮点运算明显更快;
但是在实际业务中比如存储金额即想考虑精度，又想考虑效率的做法是,将小数扩大10万倍,然后使用bigint进行存储，然后在程序中进行精度处理,这样既保证了效率,也保证了精度;

字符串类型:
char(定长), varchar(不定长), blob, text

索引是什么

官方定义：索引是帮助MySQL高效获取数据的数据结构;(索引是一种排好序的数据结构);
InnoDB存储引擎支持以下几种常见的索引：B+树索引、全文索引、哈希索引，其中比较关键的是B+树索引;

树

二叉树
二叉平衡树
AVL树(多叉平衡树),左树右树高度差不超过1;
红黑树:最长路径不超过最短路径的2倍,它的添加，删除，查找节点的效率比较平均;

为什么要尽量使用自增主键做索引

如果主键不自增,当向数据库中插入已有数据的一个中间值(例如库中有48，51，没有50，插入50),如果在B+树中构建索引他们在同一个叶子节点,但是此时当前叶子节点的磁盘页已经存满16kb,这时就会产生页分裂;

如果没有创建主键，mysql也会创建一个rowid的列来构建聚簇索引;

聚簇索引/聚集索引

InnoDB所有的数据列都存放与叶子节点,数据与索引分开存放,每张表会有三个文件,表结构文件,表数据文件和索引文件
非聚簇索引非叶子节点也会存放数据,数据文和索引存放在一起,每张表有两个文件,表结构文件,表数据文件;

二级索引/辅助索引/普通索引

普通的索引列，也会构建一棵B+树,但是叶子节点没有存储整行数据，而是除了存储索引列的数据外还存储了一个bookmark,内部存放的是主键id;

联合索引/复合索引

将表上的多个列组合起来构建的索引;

回表

通过普通索引获取到了叶子节点的主键id,然后再通过主键索引(聚簇索引)找到完整的整行数据,这个过程称之为回表;

覆盖索引

并不是一种索引类型,而是利用索引的一种方式;
直接从联合索引中就能获取到需要查询的数据列，而不需要回表去查询聚簇索引获取整行数据;

自适应Hash索引

MySQL内部会监控那些索引是热点数据，如果是热点数据的话就会将其添加到Hash索引中,用于快速获取热点数据;
MySQL 5.7之后是默认开启的;

索引下推

在MySql5.6之前,我们建立联合索引,是无法使用到索引下推的,比如说我们建立name和age的组合索引,当我们的查询语句是where name = ‘’ AND age = ‘’;Mysql首先是会通过索引找到所有name的匹配的叶子节点对应的主键id,然后通过主键id回表查询到叶子节点对应的整行数据，然后交给mysql的server层处理，server层会将age匹配行筛选出来;
在MySql5.6之后,我们通过索引找到name匹配的数据中直接对组合索引里匹配的列进行筛选得到对应的主键id,减少了回表次数,也就是减少了IO操作;

MySQL架构的调优

首先需要明确那些数据放在那种数据库,比如有些业务数据是否需要冗余一份到ES,那些数据需要放到缓存等操作;

慢查询优化

  1. 优化数据访问
    不要总是取出全部的列:不容易实现索引覆盖,IO开销增大,如果表增加新列,查询的结果会增加新列,可能会带来未知的影响
    不要过多的查询相同的数据：可以将这种重复使用的数据放入缓存;
  2. 响应时间
    服务时间(MySQL处理SQL查询数据的时间) + 排队时间(等锁,等IO这些等待所花费的时间)

UNION和UNION ALL的差别

UNION会使用临时表进行去重而UNION ALL不会;

MYISAM与INNODB的差别

  1. MyISAM使用的是非聚簇索引,InnoDB使用的是聚簇索引;
  2. InnoDB支持事务,MyISAM不支持;
  3. InnoDB支持行锁和表锁,MyISAM只支持表锁;
  4. InnoDB记录redo_log日志,拥有更快的数据恢复能力,但记录redo_log同时也会带来一定的性能损失,MyISAM则没有;