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

Mysql千万级别数据优化方案
目录
目录 (1)
一、目的与意义 (2)
1)说明 (2)
二、解决思路与根据（本测试表中数据在千万级别） (2)
1)建立索引 (2)
2)数据体现（主键非索引，实际测试结果其中fid建立索引） (2)
3)MySQL分页原理 (2)
4)经过实际测试当对表所有列查询时 (2)
三、总结 (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*fromt_history_datawherefidin (任意十条数据的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, 1010.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_dataLIMIT 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。