mysql性能优化简述

最新推荐文章于 2021-12-27 13:31:36 发布

风花雪月无情王

最新推荐文章于 2021-12-27 13:31:36 发布

阅读量485

点赞数

分类专栏：运维数据库文章标签： mysql 运维 mysql优化

本文链接：https://blog.csdn.net/u010230971/article/details/80335594

版权

运维同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

数据库

2 篇文章 0 订阅

订阅专栏

数据库性能调优主要分硬件，网络，软件三方面。硬件，网络较为简单，提高硬件配置就行，软件又分为数据库结构，SQL语句，操作系统，Mysql配置，文件系统等各个部分。本文主要针对软件优化进行说明。

表结构设计

规范化表

设计表结构时，遵从三范式，确保表结构合理。

第一范式：表中的字段都是单一属性的，不可再分

第二范式：实体的发生完全依赖于主键字段

第三范式：不存在非关键字段对其他非主字段的间接依赖

简单来说：

字段具有原子性，不可再分割
每条记录，必须有一个属性为做实体唯一标识，每个字段都要跟主键有关系
每个字段跟主键是直接关系，不能是间接关系，避免冗余，减小数据

适度冗余

遵从规范化的好处是避免冗余，减小数据，从而减少I/O操作，但也会造成使用很多的Join，降低性能。所以适度的冗余数据，用空间换时间，也是有必要的。

字段类型选择

字段选择的原则是最小满足，小字段能满足的就不用大字段。

整数类型

如很多开发性别用int，手机号用varchar来存储。性别可用1和0代替，tinyint就可以满足。手机号长度为11位，用varchar（11）类型，utf8会占用11*3=33字节大小，而用bigint只占用8字节大小。

字符类型

char和varchar日常用的最多。char来表示固定长度的字符串，长度过大的会截断，长度不足的，Mysql则用空格来补全。

varchar表示可变长度的字符串，实际占用空间大小取决于字符串的长度，最大长度是65535。

text文本类型，可存储很长的文章，占用空间较大，应避免使用。

时间类型

timestamp和datetime，两者都可以表示日期时间，但timestamp占用空间更小，是datetime的一半，应尽量使用timestamp。

mysql允许timestamp字段的自动更新，使业务中的ctime（创建时间）, mtime（更新时间）用起来很方便。

ctime: DEFAULT CURRENT_TIMESTAMP

mtime: ON UPDATE CURRENT_TIMEDTAMP

如：

create table t(
id int,
ctime timestamp DEFAULT CURRENT_TIMESTAMP,  # 插入时的默认值来表示ctime
mtime timestamp ON UPDATE CURRENT_TIMESTAMP`); #update时会自动更新时间，表示mtime

合适的锁机制

表级锁

开销小，加锁快，不会出现死锁；锁表会造成冲突，并发性能最低。如MyIsam
行级锁

开销大，加锁慢，会出现死锁；锁冲突较少，并发性能高。如InnoDB。
页面锁

介于表锁和行锁之间，如NDB。

SQL优化及索引

定位慢SQL

要找到慢SQL，需开启记录慢SQL的功能，在my.cnf中加入配置

slow_query_log=1
slow_query_log_fle=slow.log
long_query_time=2

慢SQL优化案例

not in 子查询

子查询的效率很低，尽量避免使用子查询，用join代替

like ‘%abc%’

like ‘abc%’可以用到索引，但like ‘%abc’不会用到索引。

limit 分页

limit m,n，当m过大时，mysql需要定位很多行数据，相当于全表查询。可以排序后，通过m行的主键过滤查询后面行的数据。

count(distinct)

先把去重的记录查找出来，再进行count，要快于count(distinct)。

or条件

or条件不会用到索引。将or语句改写为union all

合理利用索引

mysql一条语句只能用到一个索引，如存在多个索引，mysql只会选择它认为最优的索引。则根据实际情况，建立联合索引提高效率。mysql 5.6以后支持多个索引合并了
字段使用函数，不能利用索引。也就是说，条件运算符左边的字段不能带有函数。
字段类型不匹配，无法使用函数。如字段类型为varchar，输入数据为不带引号的数据，则无法使用到索引。所以应注意 where条件必须加引号。
字段数据分布较均匀，重复值较多时，mysql不会使用索引。比如，性别列，不需要建立索引。
当使用索引取出的数据超过全表20%时，mysql优化器会直接使用全表扫描。因为数据过多时，mysql认为全表比索引要来得快。
using filesort。当执行计划中出现using filesort时，说明排序没有用到索引，应该考虑为排序建立联合索引，有多个排序条件时，排序顺序要一致。

配置文件优化

mysql默认的配置文件，通常性能都很差。因此生产环境中，要对一些参数进行调整。

per_thread_buffers

read_buffer_size。表的顺序读取，每个线程分配的缓冲大小。128K~256K。
read_rnd_buffer_size。表的随机读取，每个线程分配的缓冲大小。
sort_buffer_size。order_by 和group_by，如果没有索引，会出现using filesort，此时每个线程会增加使用的缓冲区大小。
thred_stack。线程堆栈大小。
join_buffer_size。关联的字段没有索引，则时每个线程会增加使用的缓冲区大小。
binlog_cache_size。日志缓存大小。1~2M，并发较大时，提高2~4M。
max_connection。最大连接数。

global_buffers

innodb_buffer_size。Innodb引擎使用的内存大小，通常占用主机内存的70~80%.
innodb_additional_mem_pool_size。存储数据字典元数据和其他结构数据。
innodb_log_buffer_size。事务日志缓冲区大小。一般设置为16~64M。默认为8M。
query_cache_size。对查询SQL和其结果的缓存。一般用在读操作占多数的数据库。必须是相同的SQL，才能用到缓存结果。如果写操作较多时，表数据变更，会引起缓存的刷新，造成多余的性能开销，则关闭query_cache
```
query_cache_size=0
query_cache_type=0
query_cache_limit=0
```