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
查看数据库连接的线程个数，来观察是否有线程处于不正常的状态占用连接。
编辑
优化总结
- 使用索引列查询时尽量不使用表达式。
- 尽量使用主键查询。
- 尽量使用索引扫描进行排序，避免文件排序。
- union all 、 in、or都能使用索引，推荐in。
- 范围列可以使用索引，但是范围列后面的列无法用到索引，所以最多一个范围列索引。
- 类型强制转换会导致全表扫描。
- 更新频繁、数据区分度不高的字段不宜建立索引。频繁的更新字段会提高维护索引树的成本，区分度可以使用count(distinct(列))/cnout(*)计算。
- 索引的列尽量不允许为null。
- 控制单表索引个数。
- 点赞
- 收藏
- 关注作者
评论（0）