MySQL性能调优参考

MySQL
98
0
0
2024-04-29

前言

说起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. 控制单表索引个数。

我正在参与2024腾讯技术创作特训营第五期有奖征文,快来和我瓜分大奖!