MySQL数据库大流量环境下调优的系统解决方案

小灰灰 2022-06-02 03:15 146阅读 0赞

![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy81QUVuNE9SM1ZWVFdXMWNuM243WExNazhBR0xIak9id2lhdVl2cnlPemlhMWNVOHF3MWFvRVQ5dU11cVRXOWtDQWRjSVlkWWxscHhva3JFQmhUbWZCcjlRLzY0MD93eF9mbXQ9cG5nJnRwPXdlYnAmd3hmcm9tPTUmd3hfbGF6eT0xJnd4X2NvPTE][]

> 大家都清楚，MySQL早已成为最流行的关系数据库之一，但是想用好这个数据库，尤其是尽最大可能地发挥MySQL的性能，却不是一件容易的事情。一般来说，会根据不同的业务场景调整不同的方案，今天本文就对MySQL的优化做一些基本的探索。

**![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi95MmZoZ1A0bGVUaWFjZmV2VElLa200clNEWXN2MlNWVVF2cHhuMXNPbTVQdDE4RUk1ZmVGVGp5UWhLeUswQ3BPeTA0cXJsaEd0dUZJTkhOZkVkanNoTkEvNjQwP3d4X2ZtdD1naWYmdHA9d2VicCZ3eGZyb209NSZ3eF9sYXp5PTE][]**

## 基本层面 ##

1、表设计的时候字段的格式要设置的合适。例如能用enum的不用int，比如常见的订单发货状态，已付款、已发货、已完成等状态。能用int、datetime之内的不用varchar，能使用smallint、tinyint不使用int，这样每个字段的格式固定清楚，数据库引擎在检索的时候就会更加精确快速。

2、选用合适的数据库引擎，Innodb或者MyIASM，其中Innodb引擎最大的特点是支持事务和行锁，应用于大数据量存储，事务就是一系列SQL操作的集合，所有命令执行成功的时候，事务才算成功，否则事务将失败进行回滚，这样最大程度上保证了操作逻辑的一致性和完整性，如果实际开发需要基于这样的场景，一般都会选择Innodb数据库引擎，比如电子商务网站之内的。在有高并发的场景中，Innodb也是也会是一个不错的选择，因为支持到行锁不会锁全表，一定程度上提升了不少效率。MyIASM不支持事务和行锁，update和insert都要锁定整个表，不过MyIASM的读取效率很高，如果读表的需求高于写表的需求，那么MyIASM是一个不错的选择。

3、开启MySQL查询缓存，使用MySQL的查询缓存可以很大程度上的降低服务器的CPU负载。

> vi /etc/my.cnf
    
    query_cache_size = 100M  #缓存大小为100M
    
    query_cache_type = ON    #设置缓存为开启
    
    mysqk> show variables like '%query_cache%'; #查询缓存的状况 
    
    query_cache_type=ON #表示缓存开启
    
    mysql> show status like 'qcache%';#查看缓存的使用情况

![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy81QUVuNE9SM1ZWVFdXMWNuM243WExNazhBR0xIak9idzhwNk44SVlmVkRiOGNqYlNqbExwVllVblJ4RmFvSE44NHlPMFZmSHFpYkVyUWJJZlg1MElyVHcvNjQwP3d4X2ZtdD1wbmcmdHA9d2VicCZ3eGZyb209NSZ3eF9sYXp5PTEmd3hfY289MQ][]

4、为数据表添加合适的索引，合适的索引可以很大程度上地提高系统的检索速度，通过创建唯一性索引，可以保证每一行数据的唯一性，大大加快数据的检索，这便是添加索引的最主要的原因。在条件查询的时候，能够加速表与表之间的链接，分组排序的时候也能够缩短时间。不过索引也不是随意创建的，不合适的索引创建反而会降低系统性能。一般为经常进行搜索的字段创建索引，where、join两边链接的字段同时设为索引。

5、只有一条结果的时候使用limit 1，很多时候我们知道系统只返回一条数据，这个时候使用limit 1，系统会只查找一个后停止，否则系统会查找\*，这样会更加费时。

select name,gender from user where name="小明";
    
    select name,gender from user where name="小明" limit 1;

6、尽量不适用select \*，一般需要什么字段，才取什么字段，全部取出的话，效率肯定是很低的，尤其是在大流量的网站下，每一个细节都不容忽视。

7、尽量为每一张表添加一个逻辑主键ID，无符号(UNSIGNED INT)，自动递增(AUTO\_INCREMENT)，这是MySQL数据引擎的设计原理决定的，这样设计是最科学的。

8、建立表视图，表的视图是由真实表连接而成的虚拟表，操作视图和操作表类似，但是视图可以直接读取数据，避免频繁的join等操作。

## SQL语句优化 ##

1、尽量不使用子查询。

select * from  t1 where id (select id from t2 wherename='张三’);
    
    #优化为
    
    select t1.* from t1 join t2 on t1.id = t2.id;

2、MySQL函数不要作用在索引上，这样会导致索引失效而全表扫描，MySQL不支持函数索引。

select * from date where year(year) >= 2018;
    
    #优化为
    
    select * from date where year >= ‘2018-01-01;

3、用in替代or。

select * from student where score= 10 or score = 20 or score = 30;
    
    #优化为
    
    select * from student  where score in (10,20,30);

4、like不使用双百分号，否则导致索引失效。

select * from t form name like ‘%张三%';
    
    #优化为
    
    select * from t form name LIKE ‘张三%';

5、一条语句批量插入。

insert into t (name,score) values('张三',99);
    
    insert into t (name,score) values('李四',87);
    
    #优化为
    
    insert into t（name,score) values('张三',99),('李四',87);

6、不使用随机函数读取数据。

select * from t where order by rand() limit 10;#不推荐这样使用

7、慎用in、not in，以及where 子句中尽量不使用!=或<>操作符。

select id from t where num in(1,2,3)
    
    #优化为
    
    select id from t where num between 1 and 3

8、查询过程中，字段不要参与表达式运算。

select id from t where num*2=4
    
    #优化为
    
    select id from t where num=4/2

9、用between代替limit。

select * from a order by id limit100000,10;
    
    #优化为
    
    select * from a where id between 100000 and 100010;

## 方案层面 ##

1、避免大量的数据导入导出

很多的网站都有数据的导入导出，例如几十万条甚至几千万条数据想导入数据库，这将是一个很费时的工作，如果导入的过程中占据的大量的时间，例如50秒，倘若同时引起的数据表锁，当系统面对大流量访问的时候，WEB服务器可能会积累很多的进程等待，可能会导致服务器崩掉或者MySQL死锁，这个时候应该考虑分批导入，例如创建crontab定时任务进行分批导入导出，降低服务器的压力。

2、启用中间件缓存

这种场景一般放在大流量，大并发环境在才会存在，关系型数据库都有着自身的性能瓶颈，在一些需要高速访问的需求下，例如商品秒杀，抢购，用户的体验要做好，MySQL已经不太能够满足要求，这个时候需要引入类似于Redis，Memcached之内的高速缓存系统来辅助MySQL进行数据读取，这些非关系型数据库的读写性能会高于MySQL很多，具体可以参见各官网报告。

![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy81QUVuNE9SM1ZWVFdXMWNuM243WExNazhBR0xIak9idzFnTG5xWG9DR0l6U2NkWDhFSmhvV040b3luek5zMTk2aWNRaDdpYU9CRm5HSFVvNkdOSWhtSUF3LzY0MD93eF9mbXQ9cG5nJnRwPXdlYnAmd3hmcm9tPTUmd3hfbGF6eT0xJnd4X2NvPTE][]

3、分库分表技术

MySQL当单表数量达到1000万以上，查询写入速度上会显著降低，在设计表的时候，通常需要注意，如果数据量确实很大，需要在系统运行过程中，动态创建多个表，来保证网站的运行流畅，不过高版本的MySQL已经能够实现逻辑水平分表，请参见具体情况。

4、MySQL主从复制

主从复制一般是用来进行读写分析，这是负载均衡的一种实现思路。将MySQL的数据分布到多个系统上去，这种分布的机制，是通过将MySQL的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。

5、MySQL集群冗余

防止MySQL单点故障，备份数据，尽最大程度保证区块内的服务器处于激活状态，以高效提供服务。

![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy81QUVuNE9SM1ZWVFdXMWNuM243WExNazhBR0xIak9id0VhaWFjaGljeGRQbGxNWURDU25SODRWVWUyaWF3bjlWeFZvTHZjZXUxVXNSaWFrTEU3NGZxZ0l2RWcvNjQwP3d4X2ZtdD1wbmcmdHA9d2VicCZ3eGZyb209NSZ3eF9sYXp5PTEmd3hfY289MQ][]

每日进步一点，欢迎关注公众号 "编程社"！！！

![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy81QUVuNE9SM1ZWVFdXMWNuM243WExNazhBR0xIak9idzNwcjcyRGNTaWFNdHVpYjFYejZvMWhNbjg2am9nUTY1NGRxaHMwUkVHWTRESU5sem5PWEdhRm5nLzY0MD93eF9mbXQ9cG5nJnRwPXdlYnAmd3hmcm9tPTUmd3hfbGF6eT0xJnd4X2NvPTE][]

*作者：**kite.wang*

*编辑：Jana*

[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy81QUVuNE9SM1ZWVFdXMWNuM243WExNazhBR0xIak9id2lhdVl2cnlPemlhMWNVOHF3MWFvRVQ5dU11cVRXOWtDQWRjSVlkWWxscHhva3JFQmhUbWZCcjlRLzY0MD93eF9mbXQ9cG5nJnRwPXdlYnAmd3hmcm9tPTUmd3hfbGF6eT0xJnd4X2NvPTE]: /images/20220602/1e9353fdf05c4c4cab33f2b5fb2431f5.png
[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi95MmZoZ1A0bGVUaWFjZmV2VElLa200clNEWXN2MlNWVVF2cHhuMXNPbTVQdDE4RUk1ZmVGVGp5UWhLeUswQ3BPeTA0cXJsaEd0dUZJTkhOZkVkanNoTkEvNjQwP3d4X2ZtdD1naWYmdHA9d2VicCZ3eGZyb209NSZ3eF9sYXp5PTE]: /images/20220602/20d1e8a1583b47da945b9d628eadb14f.png
[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy81QUVuNE9SM1ZWVFdXMWNuM243WExNazhBR0xIak9idzhwNk44SVlmVkRiOGNqYlNqbExwVllVblJ4RmFvSE44NHlPMFZmSHFpYkVyUWJJZlg1MElyVHcvNjQwP3d4X2ZtdD1wbmcmdHA9d2VicCZ3eGZyb209NSZ3eF9sYXp5PTEmd3hfY289MQ]: /images/20220602/ce752a461b344703b8ec5f2bb5795691.png
[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy81QUVuNE9SM1ZWVFdXMWNuM243WExNazhBR0xIak9idzFnTG5xWG9DR0l6U2NkWDhFSmhvV040b3luek5zMTk2aWNRaDdpYU9CRm5HSFVvNkdOSWhtSUF3LzY0MD93eF9mbXQ9cG5nJnRwPXdlYnAmd3hmcm9tPTUmd3hfbGF6eT0xJnd4X2NvPTE]: /images/20220602/e0b4a3fab5a443bdac2083de3a7d1af8.png
[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy81QUVuNE9SM1ZWVFdXMWNuM243WExNazhBR0xIak9id0VhaWFjaGljeGRQbGxNWURDU25SODRWVWUyaWF3bjlWeFZvTHZjZXUxVXNSaWFrTEU3NGZxZ0l2RWcvNjQwP3d4X2ZtdD1wbmcmdHA9d2VicCZ3eGZyb209NSZ3eF9sYXp5PTEmd3hfY289MQ]: /images/20220602/3f2b6a0291294b359aa820df5c25833c.png
[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy81QUVuNE9SM1ZWVFdXMWNuM243WExNazhBR0xIak9idzNwcjcyRGNTaWFNdHVpYjFYejZvMWhNbjg2am9nUTY1NGRxaHMwUkVHWTRESU5sem5PWEdhRm5nLzY0MD93eF9mbXQ9cG5nJnRwPXdlYnAmd3hmcm9tPTUmd3hfbGF6eT0xJnd4X2NvPTE]: /images/20220602/cd27f76e3bb64c8e8000742f75867e28.png