Mysql千万级别数据优化方案总结

目录

目录 ....................................................................................................................................... 1 一、

目的与意义 ................................................................................................................ 2

1) 说明 ............................................................................................................................... 2 二、

解决思路与根据（本测试表中数据在千万级别） ................................................ 2

1) 建立索引 ....................................................................................................................... 2 2) 数据体现（主键非索引，实际测试结果 其中fid建立索引） ............................ 2 3) MySQL分页原理 ........................................................................................................... 2 4) 经过实际测试当对表所有列查询时 ............................................................................ 3 三、

总结 ............................................................................................................................ 3

1) 获得分页数据 ............................................................................................................... 3 2) 获得总页数：创建表 记录大数据表中总数 通过触发器来维护 ......................... 3

一、 目的与意义

1) 说明

在MySql单表中数据达到千万级别时数据的分页查询结果时间过长，对此进行优 达到最优效果，也就是时间最短；（此统计利用的jdbc连接，其中fid为该表的主键；)

二、 解决思路与根据（本测试表中数据在千万级别）

1) 建立索引

优点:当表中有大量记录时，若要对表进行查询,第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码)快速找到表中对应的记录。

缺点：当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度.

2) 数据体现（主键非索引,实际测试结果 其中fid建立索引）

未创建索引：SELECT fid from t_history_data LIMIT 8000000,10 结果：13。396s 创建索引：SELECT fid from t_history_data LIMIT 8000000，10 结果:2。896s select ＊ from t_history_data where fid in （ 任意十条数据的id ） 结果：0。141s 首先通过分页得到分页的数据的ID，将ID拼接成字符串利用SQL语句

select * from table where ID in （ID字符串）此语句受数据量大小的影响比较小（如上测试）； 3) MySQL分页原理

MySQL的limit工作原理就是先读取n条记录，然后抛弃前n条,读m条想要的，所以n越大，性能会越差。 优化前SQL： SELECT * FROM v_history_data LIMIT 5000000, 10 10.961s 优化后SQL： SELECT * FROM v_history_data INNER JOIN （SELECT fid FROM t_history_data LIMIT 5000000, 10) a USING (fid） 1。943s 分别在于，优化前的SQL需要更多I/O浪费，因为先读索引,再读数据，然后抛弃无需的行。而优化后的SQL（子查询那条）只读索引（Cover index)就可以了，然后通过member_id读取需要的列

4) 经过实际测试当对表所有列查询时

select * from table 会比 select （所有列名） from table 快些（以查询8000000处数据分页实验)。

select ＊ from t_history_data LIMIT 8000000,10 结果:10。735s

select （总共14列）from t_history_data LIMIT 8000000,10 结果：11。594s

三、 总结

1) 获得分页数据

创建索引：CREATE UNIQUE INDEX index_name ON t_history_data (fid） 相应的查询语句：SELECT ＊ FROM v_history_data INNER JOIN （SELECT fid FROM t_history_data LIMIT 5000000， 10） a USING （fid) (原理位于上方红色标记处，该方法查询速度将近提升10倍） 相对应的有条件查询根据需要建立索引

2) 获得总页数：创建表 记录大数据表中总数 通过触发器来维护

创建表： CREATE TABLE `t_total` （ `Id` int(11) NOT NULL AUTO_INCREMENT,

`tableName` char（25） DEFAULT NULL， `sum` int(11) DEFAULT NULL, PRIMARY KEY （`Id`）

） ENGINE=InnoDB DEFAULT CHARSET=utf8； 表初始化 写触发器

CREATE TRIGGER t1 AFTER INSERT ON t_history_data FOR EACH ROW BEGIN

DECLARE i int；

SELECT sum INTO i from t_total WHERE tablename = ’t_history_data' ; set i = i+1；

UPDATE t_total set sum=i where tablename = 't_history_data’ ； END

CREATE TRIGGER t2 AFTER DELETE ON t_history_data FOR EACH ROW BEGIN

DECLARE i int;

SELECT sum INTO i from t_total WHERE tablename = 't_history_data' ; set i = i-1；

UPDATE t_total set sum=i where tablename = ’t_history_data' ; END