Mysql数据库最佳性能优化

作者	邮箱	时间
潘顾昌	guchang.pan@hand-china.com	2020/05/16

一、数据库代码层次优化

1、选取最适用的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，**数据库中的表越小，在它上面执行的查询也就会越快。**因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGINT来定义整型字段。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

2.在只需要一条记录的情况下使用limit 1

我们在前文中提到了使用limit的种种好处,但是要注意这些好处是对于limit没有偏移量的情况,也就是只返回特定的行数的情况下.在添加了偏移量之后引擎依旧会去遍历直到到达偏移量的位置.我会在下文中提到如有优化这一类语句.
通常在框架中会提供get方法来获取一行,我们直接使用它就好.

3.使用连接（JOIN）来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）…替代。如果使用连接（JOIN）…来完成这个查询工作，速度将会快很多，尤其是当索引存在的时候，连接（JOIN）…之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

4.使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

那该对哪些字段建立索引呢？

一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况

5.最好是在相同类型的字段间进行比较的操作。

在MySQL3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。

6.尽量避免使用LIKE关键字和通配符

在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。

例如

SELECT *
FROM books
WHERE name like "MySQL%"

但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：

SELECT *
FROM books
WHERE name ＞= "MySQL"
   and name ＜ "MySQM"

7.应尽量避免在 where 子句中使用!=或<>操作符，

where 子句中使用!=或<>操作符将使得引擎放弃使用索引而进行全表扫描。

8.应尽量避免在 where 子句中使用 or 来连接条件

where 子句中使用 or 来连接条件将导致引擎放弃使用索引而进行全表扫描

select id from t where num=10 or num=20

可以改成下面的查询

select id from t where num=10 union all select id from t where num=20

9.in 和 not in 也要慎用

in 和 not in 会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

10.应尽量避免在 where 子句中对字段进行表达式操作

在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。 如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

11.exists 代替 in

select num from a where num in(select num from b)

应该为:

select num from a where exists(select 1 from b where num=a.num)

12.避免使用select *

任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

13.谨慎使用order by 进行排序

• 从性能上考虑,应该尽量避免排序,或者尽可能避免对大量数据进行排序.
• 如果不行也请尽量在主键上进行排序,因为主键会使用簇集索引
• 没有主键上排序,也请尽量在索引上排序,如果有多条排序,可以考虑组合索引,但是要注意索引的左匹配原则
• 如果没有索引,引擎会自己排序,我们称之为文件排序(如果数据小,会在内存中进行,但是大多数情况下依旧会使用磁盘).总之,即便引擎进行大量优化,文件排序生成的临时存储空间和资源也要比你想象中的大,因此尽量避免这种情况

14.尽量不要使用BY RAND()命令

如果您真需要随机显示你的结果，有很多更好的途径实现。而这个函数可能会为表中每一个独立的行执行BY RAND()命令—这个会消耗处理器的处理能力，然后给你仅仅返回一行。

15.尽量用union all 代替union

union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union.

16.避免类型转换

这里所说的“类型转换”是指where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换。人为的上通过转换函数进行转换，直接导致mysql无法使用索引。如果非要转型，应该在传入参数上进行转换。

17.使用批量插入节省交互（最好是使用存储过程）

尽量使用insert intousers(username,password) values(‘test1’,’pass1’), (‘test2’,’pass2’), (‘test3’,’pass3’);

18.在插入大批量的数据时，建议归类、有序的插入数据。

insert into st(name,id) values('zhangsana',10);
insert into st(name,id) values('lisi',3);
insert into st(name,id) values('wangwu',2);
insert into st(name,id) values('wangwub',8);
insert into st(name,id) values('wangwua',34);

优化后：（进行排序，按主键的顺序）

insert into st(name,id) values(2,'wangwu');
insert into st(name,id) values(3,'lisi');
insert into st(name,id) values(8,'wangwub');
insert into st(name,id) values(10,'zhangsana');
insert into st(name,id) values(34,'wangwua');

二、数据库其他层次优化（非代码）

1.优化硬件、操作系统

-CPU，内存，硬盘

Linux操作系统的内核优化

内核相关参数(/etc/sysctl.conf)

-网络TCP连接

-加快资源回收效率

-增加资源限制

-改变磁盘调度策略

2.优化MySQL服务器

修改最大连接数：

max_connections=2000

默认：max_connections=151

3.指定MySQL可能的连接数量

指定MySQL可能的连接数量。当MySQL主线程在很短的时间内得到非常多的连接请求，该参数就起作用，之后主线程花些时间（尽管很短）检查连接并且启动一个新线程。

back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。

back_log=1024

默认：back_log=80

4.索引块的缓冲区大小

key_buffer_size = 32M
索引块的缓冲区大小，对MyISAM表性能影响最大的一个参数.决定索引处理的速度，尤其是索引读的速度。默认值是8M，通过检查状态值Key_read_requests
和Key_reads，可以知道key_buffer_size设置是否合理

默认：key_buffer_size=8M

5.MySQL执行排序使用的缓冲大小

sort_buffer_size = 16M

是MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。

如果不能，可以尝试增加sort_buffer_size变量的大小。

默认：sort_buffer_size=256K

6.MYSQL读入缓冲区大小

read_buffer_size = 64M

是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。

如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

默认：read_buffer_size=64K

7.Join操作缓存大小

join_buffer_size = 16M

应用程序经常会出现一些两表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的时候（all/index join），为了减少参与Join的“被驱动表”的读取次数以提高性能，需要使用到 Join Buffer 来协助完成 Join操作。当 Join Buffer 太小，MySQL 不会将该 Buffer 存入磁盘文件，而是先将Join Buffer中的结果集与需要 Join 的表进行 Join 操作，然后清空 Join Buffer 中的数据，继续将剩余的结果集写入此 Buffer 中，如此往复。这势必会造成被驱动表需要被多次读取，成倍增加 IO 访问，降低效率。

默认：join_buffer_size=256K

8.MySQL的随机读缓冲区大小

read_rnd_buffer_size = 32M

是MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，

提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

默认：read_rnd_buffer_size=256K

9.缓存排序索引大小

myisam_sort_buffer_size = 256M

当对MyISAM表执行repair table或创建索引时，用以缓存排序索引；设置太小时可能会遇到” myisam_sort_buffer_size is too small”

myisam_sort_buffer_size=102M

10.缓存空闲的线程以便不被销毁

thread_cache_size = 384

thread_cahe_size线程池，线程缓存。用来缓存空闲的线程，以至于不被销毁，如果线程缓存在的空闲线程，需要重新建立新连接，则会优先调用线程池中的缓存，很快就能响应连接请求。每建立一个连接，都需要一个线程与之匹配。

默认：thread_cache_size=10

汇总：

set global  max_connections=2000;#设置最大连接数
set global key_buffer_size=512*1024*1024;#设置索引块缓冲区大小
set global sort_buffer_size=128*1024*1024;#MySQL执行排序使用的缓冲大小
set global read_buffer_size=64*1024*1024;#MYSQL读入缓冲区大小
set global join_buffer_size=128*1024*1024;#Join操作缓存大小
set global read_rnd_buffer_size=32*1024*1024;#MySQL的随机读缓冲区大小
set global myisam_sort_buffer_size=256*1024*1024;#缓存排序索引大小
set global thread_cache_size=384;#缓存空闲的线程以便不被销毁
set global innodb_buffer_pool_size=1000*1024*1024;#内存

#查询
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'

三、优化DB设计

1.参照范式进行设计

1NF
　　　　包含分隔符类字符的字符串数据。
　　　　名字尾端有数字的属性。
　　　　没有定义键或键定义不好的表。
　2NF
　　　　多个属性有同样的前缀。
　　　　重复的数据组。
　　　　汇总的数据，所引用的数据在一个完全不同的实体中。
　　　　BCNF- “每个键必须唯一标识实体，每个非键熟悉必须描述实体。
　4NF
　　　　三元关系（实体:实体:实体）。
　　　　潜伏的多值属性。（如多个手机号。）
　　　　临时数据或历史值。（需要将历史数据的主体提出，否则将存在大量冗余。）

-建立合适的索引

2.建索引

加快查询速度。
减少I/O操作，通过索引的路径来检索数据，不是在磁盘中随机检索。
消除磁盘排序，索引是排序的，走完索引就排序完成

1）B-Tree 索引
B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型

2）Hash 索引
Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引。

3）什么时候可以建索引
1）列无重复值，可以建索引：唯一索引和普通索引

2）聚集索引和非聚集索引都可以是唯一的。因此，只要列中的数据是唯一的，就可以在同一个表上创建一个唯一的聚集索引和多个唯一的非聚集索引。

3）建了索引性能得到提高

4）区分度高的列可以建索引，比如表示男和女的列区分度就不高，就不能建索引

4）什么时候不可以建索引
1.频繁更新的字段不适合建立索引

2.where条件中用不到的字段不适合建立索引

3.表数据可以确定比较少的不需要建索引

4.数据重复且发布比较均匀的的字段不适合建索引（唯一性太差的字段不适合建立索引），例如性别，真假值

5.参与列计算的列不适合建索引，如select * from where amount+1>10

6.查询返回的记录数不适合建立索引

7.查询的排序表记录小于40%不适合建立索引

8.查询非排序表的记录小于 7%不适合建立索引

9.表的碎片较多（频繁增加、删除）不适合建立索引