MySQL性能调优参考

前言

说起mysql的调优一般都会想到sql优化、索引、看执行计划，除了这些在创建表时也要下功夫，比如说字段的类型、数据的冗余度，以及sql运行过程中性能的监控都很重要。

一、数据类型的优化

数据类型介绍

数值：tinyint、smallint、mediumint、int、bigint，分别使用8、16、24、32、64位存储空间。

字符：char、varchar、text、blob。

• char最大长度255，属于固定长度的字符串类型，实际存储值比指定长度小时会用空格填补，指定过长浪费空间。适用于长度固定的值，比如MD5、身份证等。
• varchar最大长度65535，属于可变长度的字符串类型。适用于存储长度波动较大的数据。
• text、blob一般不用。

时间：date、datetime、timestamp

• date：占用3个字节，精确到毫秒，时间范围大。
• datetime：占用8个字节，精确到毫秒，时间范围大。
• timestamp：占用8个字节，精确到秒，时间范围在1970-01-01到2038-01-19

枚举：enum

使用原则

更小的通常更好：尽量使用正确存储类型的最小数据类型，更小的数据类型通常更快，因为它们占用的更少的磁盘、内存和cpu缓存。例如能使用tinyint就不使用smallint。

简单就好：简单数据类型的操作通常需要更少的CPU周期。例如整形比字符的操作代价更低。

尽量避免使用NULL：为null的列使得索引、索引统计和值比较都更加复杂。

二、合理使用范式和反范式

范式的目的是为了减少数据的冗余，范式化后的数据很少或者没有重复的数据，通常需要关联表查询额外的数据，一定程度上影响查询效率。反范式化的数据一般都在同一张表，可以避免关联，以空间换取时间。所以要结合业务来合理的使用范式和反范式。

三、合理使用索引

索引的建立可以大大提高mysql的检索效率，但是在insert、update、delete会降低更新表的速度，因为更新表时不仅要保存数据还要维护索引文件。

索引分类

主键、唯一、普通、全文、组合

物理存储分类

聚簇索引：索引和数据保存同一个树上。

非聚簇索引：索引和数据分开存放。

索引数据结构分类

hash：存储索引值及行指针。由于hash的特性，适合精确匹配、无法排序。

b+ tree：非叶子结点存储索引，叶子结点存储行数据。所有叶子结点形成链表适合范围查找。

特殊名词

回表：mysql默认给主键创建索引，其叶子节点存放行数据。普通索引叶子节点存放主键，当使用普通索引查询到主键时会再一次根据主键查询一次索引树，会有两次的树的操作，这个行为是回表。

覆盖索引：sql语句中查询的字段存在索引时就会覆盖索引，避免回表操作。

匹配方式

全值匹配、最左匹配、列前缀匹配、范围值匹配、覆盖索引

四、执行计划的查看

在执行sql时使用explain关键字查看执行计划，通过执行计划可以看到sql语句在数据库中如何让扫描表、如何使用索引的。看执行计划时一般关注以下几个内容：

以下图sql为例子

id：select的序列号，表示执行顺序，值越大越先执行，若果顺序相同，从上往下执行。

select_type：sql语句的查询类型

type：访问类型表示以何种方式访问数据，效率从最好到最坏依次是：system>const>ref>fulltext>ref_or_null>index_merge>unique_subquery>range>index>all

possible_key：可能应用在这边张表的索引

key：实际应用到的索引

key_len：索引的长度

ref：显示索引的哪一列被使用了

rows：根据表的统计信息和索引使用情况大致估算出所需读取的行数

Extra：包含额外的信息，常见的几个值：

using filesort：说明无法用索引排序，此时要检查sql语句中索引匹配方式

using temporary：建立临时表保存中间结果

using indexing：表示查询时覆盖索引

using where：使用where条件过滤

五、sql的性能监控

show profiles

可以监控sql语句的执行时长。默认是关闭的，可以通过set profiling=on开启。

performance schema

本身是一个数据库，有80+张表，存储mysql运行过程中的性能相关的数据。

show processlist

查看数据库连接的线程个数，来观察是否有线程处于不正常的状态占用连接。

优化总结

1. 使用索引列查询时尽量不使用表达式。
2. 尽量使用主键查询。
3. 尽量使用索引扫描进行排序，避免文件排序。
4. union all 、 in、or都能使用索引，推荐in。
5. 范围列可以使用索引，但是范围列后面的列无法用到索引，所以最多一个范围列索引。
6. 类型强制转换会导致全表扫描。
7. 更新频繁、数据区分度不高的字段不宜建立索引。频繁的更新字段会提高维护索引树的成本，区分度可以使用count(distinct(列))/cnout(*)计算。
8. 索引的列尽量不允许为null。
9. 控制单表索引个数。