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

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

基本层面
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%';#查看缓存的使用情况

 

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 ‘%123%';
-- 优化为
select * from t form name LIKE ‘123%';

 

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很多，具体可以参见各官网报告。

 

3 .分库分表技术

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

 

4. MySQL主从复制

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

 

5. MySQL集群冗余

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

 

来自微信公众号:编程社

程序员日常进阶宝典，欢迎关注！