Mysql的单张表的最大数据存储量尚没有定论，一般情况下mysql单表记录超过千万以后性能会变得很差。因此，总结一些相关的Mysql千万级大表的优化策略。

 

 

1、有索引但未被用到的情况（不建议）

(1)避免like的参数以通配符开头时

尽量避免Like的参数以通配符开头，否则数据库引擎会放弃使用索引而进行全表扫描。

以通配符开头的sql语句，例如：select * from t_credit_detail where Flistid like '%0'G

这是全表扫描，没有使用到索引，不建议使用。

不以通配符开头的sql语句，例如：select * from t_credit_detail where Flistid like '2%'G

很明显，这使用到了索引，是有范围的查找了，比以通配符开头的sql语句效率提高不少。

(2) 避免where条件不符合最左前缀原则。最左前缀原则：mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整（IN和=可以乱序）。

(3) 使用！= 或 <> 操作符时

尽量避免使用！= 或 <>操作符，否则数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效。

select * from t_credit_detail where Flistid != '2000000608201108010831508721'G

(4) 避免索引列参与计算

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

select * from t_credit_detail where Flistid +1 > '2000000608201108010831508722'G

(5) 避免对字段进行null值判断

应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： 低效：select * from t_credit_detail where Flistid is null ;

可以在Flistid上设置默认值0，确保表中Flistid列没有null值，然后这样查询： 高效：select * from t_credit_detail where Flistid =0;

(6) 避免使用or来连接条件

应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： 低效：select * from t_credit_detail where Flistid = '2000000608201108010831508721' or Flistid = '10000200001';

可以用下面这样的查询代替上面的 or 查询： 高效：select from t_credit_detail where Flistid = '2000000608201108010831508721' union all select from t_credit_detail where Flistid = '10000200001';

2、避免select *

在解析的过程中，会将'*' 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

所以，应该养成一个需要什么就取什么的好习惯。

3、order by 语句优化

任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

方法：1.重写order by语句以使用索引；

4、GROUP BY语句优化

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉（如下例，先用where语句过滤掉一部分数据）

低效:

高效:

5、用 exists 代替 in

很多时候用 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)

6、使用 varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

7、能用DISTINCT的就不用GROUP BY

SELECt OrderID FROM Details WHERe UnitPrice > 10 GROUP BY OrderID

可改为：

SELECt DISTINCT OrderID FROM Details WHERe UnitPrice > 10

8、能用UNIOn ALL就不要用UNIOn

UNIOn ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源。（UNIOn ALL允许重复）

9、在Join表的时候使用相当类型的例，并将其索引

如果应用程序有很多JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。

而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）

 

1对出现在where子句中的字段加索引

1.      包含大量非重复值的列。

2.      使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。

3.      被连续访问的列。

4.      返回大型结果集的查询。

5.      经常被使用联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。

1.      频繁更改的列，这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值）。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的。 

 

MySQL主从复制是其最重要的功能之一。主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

主从复制的作用

1、做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。

2、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。（请求发到多部机子上）

3、读写分离，使数据库能支撑更大的并发。一主多从的部署方案，将涉及数据写的操作放在Master端操作，而将数据读的操作分散到众多的Slave当中。降低了Master的负载，提高数据写入的响应效率；多台从服务器提供读，分担了请求，提高了读的效率。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

 

主从复制的原理

1.数据库有个bin-log二进制文件，记录了所有sql语句。

2.我们的目标就是把主数据库的bin-log文件的sql语句复制过来。

3.让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可。

 

主从复制步骤以及涉及的线程：

一：主库db的更新事件(update、insert、delete)被写到binlog
二：从库发起连接，连接到主库
三：此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库
四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log.
五：还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始

1.binlog输出线程:每当有从库连接到主库的时候，主库都会创建一个线程输出binlog，然后发送binlog内容到从库。
2.从库I/O线程:当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。（从主库先传输下来）从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件（再更新relay log）。
3.从库的SQL线程:从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。

 

做主从后主服务器挂了怎么办

假设发生了突发事件，master宕机，现在的需求是要将从库提升为主库，另外一个为从库：

1.确保所有的从库的relay log全部更新完毕，在每个从库上执行stop slave io_thread; show processlist;直到看到Has read all relay log,则表示从库更新都执行完毕了
2.登陆所有从库，查看master.info文件，选择一个从库为新的主库
3.登陆该从库，执行stop slave; 并进入数据库目录，删除master.info和relay-log.info文件, 配置my.cnf文件，开启log-bin,如果有log-slaves-updates和read-only则要注释掉，执行reset master
4.创建用于同步的用户并授权slave，同第五大步骤
5.登录另外一台从库，执行stop slave停止同步
6.根据第七大步骤连接到新的主库
7.执行start slave;
8.修改新的master数据，测试slave是否同步更新

主从复制延迟

主库针对读写操作，顺序写 binlog，从库单线程去主库读"写操作的binlog",从库取到 binlog在本地原样执行(随机写),来保证主从数据逻辑上一致.mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生 binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率比较高，下一步问题来了，slave的 slave_sql_running线程将主库的 DDL和DML操作在 slave实施。DML，DDL的IO操作是随机的，不能顺序的，成本高很多，还有可能slave上的其他查询产生 lock，由于 slave_sql_running也是单线程的，所以 一个 DDL卡住了，需求需求执行一段时间，那么所有之后的DDL会等待这个 DDL执行完才会继续执行，这就导致了延迟.由于master可以并发，Slave_sql_running线程却不可以，所以主库执行 DDL需求一段时间，在slave执行相同的DDL时，就产生了延迟.

主从同步延迟产生原因
当主库的TPS并发较高时，产生的DDL数量超过Slave一个 sql线程所能承受的范围，那么延迟就产生了，当然还有就是可能与 slave的大型 query语句产生了锁等待
首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高
次要原因：读写 binlog带来的性能影响，网络传输延迟
主从同步延迟解决方案
架构方面：mysql压力变小，延迟自然会变小
1.业务的持久化层的实现采用分库架构，mysql服务可平行扩展分散压力
2.单个库读写分离，一主多从，主写从读，分散压力。
3.服务的基础架构在业务和mysql之间加放 cache层
4.不同业务的mysql放在不同的机器
5.使用比主加更好的硬件设备作slave

 MySQL的主从复制和MySQL的读写分离两者有着紧密联系，首先部署主从复制，只有主从复制完了，才能在此基础上进行数据的读写分离。读写分离就是只在主服务器上写，只在从服务器上读，基本的原理是让主数据库处理事务性查询，而从数据库处理select查询，数据库复制被用来把事务性查询导致的改变更新同步到集群中的从数据库，即主从复制。

1.基于程序代码内部实现
在代码中根据select,insert进行路由分类，这类方法也是目前生产环境应用最广泛的，优点是性能好，因为在程序代码中实现，不需要曾加额外的设备作为硬件开支，缺点是需要开发人员来实现，运维人员无从下手。
2.基于中间代理层实现
代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有两个代表性程序。
（1）mysql-proxy 为mysql开源项目，通过其自带的lua脚本进行SQL判断，虽然是mysql的官方产品，但是mysql官方不建议将其应用到生产环境
（2）Amoeba （变形虫）由陈思儒开发，曾就职与阿里巴巴，该程序由java语言进行开发，阿里巴巴将其应用于生成环境，它不支持事物和存储过程
通过程序代码实现mysql读写分离自然是一个不错的选择，但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的java应用，如果在程序代码中实现读写分离对代码改动就较大，像这种应用一般会考虑使用代理层来实现。

 

MySQL Proxy是一个处于你的client端和MySQL server端之间的简单程序，它可以监测、分析或改变它们的通信。它使用灵活，没有限制，常见的用途包括：负载平衡，故障、查询分析，查询过滤和修改等等。MySQL Proxy就是这么一个中间层代理，简单的说，MySQL Proxy就是一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过使用lua脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负载平衡。对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。当然，这样proxy机器可能单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多个proxy的连接参数即可。MySQL Proxy更强大的一项功能是实现“读写分离”，基本原理是让主数据库处理事务性查询，让从库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从库。

 

• 表分区

表分区其实就是将一张大数据量表中的数据按照不同的分区策略分配到不同的系统分区、硬盘或是不同的服务器设备上，实现数据的均衡分配，这样做的好处是均衡大数据量数据到不同的存储介子中，

1、相对于单个文件系统或是硬盘，分区可以存储更多的数据；

2、数据管理比较方便，比如要清理或废弃某年的数据，就可以直接删除该日期的分区数据即可；

3、精准定位分区查询数据，不需要全表扫描查询，大大提高数据检索效率；

4、可跨多个分区磁盘查询，来提高查询的吞吐量；

5、在涉及聚合函数查询时，可以很容易进行数据的合并；

表的分区的原理理解起来比较简单，其实就是把一张大数据量的表，根据分区策略进行分区，分区设置完成之后，由数据库自身的储存引擎来实现分发数据到指定的分区中去，正如上图所示，一张数据表被分成了n个分区，并且分区被放入到不同的介子disk中，每个disk中包含自少一个分区，这就实现了数据的均衡以及通过跨分区介子检索提高了整体的数据操作IO吞吐率。

表分区的策略：

目前在MySql中支持四种表分区的方式，分别为HASH、RANGE、LIST及KEY，当然在其它的类型数据库中，分区的实现方式略有不同，但是分区的思想原理是相同，具体如下。

Hash：HASH分区主要用来确保数据在预先确定数目的分区中平均分布，而在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中，而在HASH分区中，MySQL自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

 

Range：基于属于一个给定连续区间的列值，把多行分配给同一个分区，

 

List：类似于按RANGE分区，区别在于LIST分区是

 

Key：类似于按HASH分区，区别在于KEY分区只支持

表分区的注意：

1、引擎的统一
在对同一个表进行分区时，必须保证数据表的引擎相同，比如：不能对一个分区的表为InnoDB，而另一个分区的引擎为MySIAM。
2、分区关联性
在对数据表分区时，不能只对数据进行分区，需要连同其对应的索引等属性一同分区动作，某种程度上可以保持数据属性的完整。
3、分区的级别
对表进行分区之后，如果某个分区中的数据量依然很大或是增长迅速，那么你同样可以再进行子分区操作，将该数据再分区到其它分区中。另外，如果在一个分区中使用了子分区，那么其它的子分区也必须定义。
4、LIST分区
LIST分区没有类似如“VALUESLESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。
5、Linear线性
分区策略KEY和HASH都支持使用线性LINEAR的算法，也就是分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。

• 垂直分表、水平分表

垂直分表

1.减少记录的字段可使内存加载更多行数据，有利于查询。
2.受限于操作系统中的文件大小限制。
切分原则：把不常用或业务逻辑不紧密或存储内容比较多的字段分到新的表中可使表存储更多数据。另外垂直分割可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少I/O次数。其缺点是需要管理冗余列，查询所有数据需要join操作。

水平分表

1.随着数据量的增大，table行数巨大，查询的效率越来越低。表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。
2.同样受限于操作系统中的文件大小限制，数据量不能无限增加，当到达一定容量时，需要水平切分以降低单表（文件）的大小。

切分原则：
增量区间或散列或其他业务逻辑。使用哪种切分方法要根据实际业务逻辑判断：比如对表的访问多是近期产生的新数据，历史数据访问较少，可以考虑根据时间增量把数据按照一定时间段（比如每年）切分。如果对表的访问较均匀，没有明显的热点区域，则可以考虑用范围（比如每500w一个表）或普通Hash或一致性Hash来切分。

全局主键问题：原本依赖数据库生成主键（比如自增）的表在拆分后需要自己实现主键的生成，因为一般拆分规则是建立在主键上的（拆分后仍然要保证主键在全局的唯一性），所以在插入新数据时需要确定主键后才能找到存储的表。

 

    以上就是本篇文章【Mysql千万级大表优化】的全部内容了，欢迎阅览 ！ 文章地址：http://3jjewl.riyuangf.com/news/25141.html 
     资讯      企业新闻      行情      企业黄页      同类资讯      首页      网站地图      返回首页 迅易网资讯移动站 http://fjzhdcm.riyuangf.com/ , 查看更多