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

1、Mysql 千万级别数据优化方案目录目录1一、目的与意义21)说明2二、解决思路与根据（本测试表中数据在千万级别）21)建立索引22)数据体现 （主键非索引，实际测试结果其中 fid 建立索引）23)MySQL分页原理24)经过实际测试当对表所有列查询时2三、总结31)获得分页数据32)获得总页数：创建表记录大数据表中总数通过触发器来维护3一、目的与意义1) 说明在 MySql 单表中数据达到千万级别时数据的分页查询结果时间过长，对此进行优达到最优效果，也就是时间最短；(此统计利用的 jdbc 连接，其中 fid 为该表的主键;)二、解决思路与根据（本测试表中数据在千万级别）1) 建立索引优点

2、： 当表中有大量记录时， 若要对表进行查询， 第一种搜索信息方式是全表搜索，是将所有记录一一取出， 和查询条件进行一一对比， 然后返回满足条件的记录，这样做会消耗大量 数据库系统 时间，并造成大量磁盘 I/O 操作；第二种就是在表中建立索引， 然后在索引中找到符合查询条件的索引值， 最后通过保存在索引中的 ROWID（相当于 页码 ）快速找到表中对应的记录。缺点： 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。2)数据体现 （主键非索引，实际测试结果其中 fid 建立索引）未创建索引： SELECT fid from t_history_dataLIMIT

3、 8000000,10 结果： 13.396s创建索引： SELECT fid from t_history_dataLIMIT 8000000,10结果： 2.896sselect * 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 条，读

4、m 条想要的，所以 n 越大，性能会越差。优化前 SQL: SELECT * FROMv_history_data LIMIT 5000000, 1010.961s优化后 SQL: SELECT * FROM v_history_data INNER JOIN (SELECT fidFROM t_history_data LIMIT 5000000, 10) a USING (fid)1.943s分别在于，优化前的SQL 需要更多 I/O 浪费，因为先读索引，再读数据，然后抛弃无需的行。而优化后的SQL( 子查询那条 )只读索引 (Cover index)就可以了，然后通过 member_id

5、读取需要的列4) 经过实际测试当对表所有列查询时select * from table会比select（所有列名）from table快些（以查询8000000处数据分页实验） 。selectselect*from t_history_data LIMIT 8000000,10（总共 14 列） from t_history_data LIMIT 8000000,10结果：10.735s结果： 11.594s三、总结1)获得分页数据创建索引： CREATE UNIQUE INDEX index_name ON t_history_data(fid)相应的查询语句： SELECT * FROM

6、v_history_data INNER JOIN (SELECT fidFROM 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) E

7、NGINE=InnoDB DEFAULT CHARSET=utf8;表初始化写触发器CREATE TRIGGER t1 AFTER INSERTON t_history_data FOR EACH ROWBEGINDECLARE 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' ; ENDCREATE TRIGGER t2 AFTER DELETEON t_history_data FOR EACH ROWBEGINDECLARE i int;SELECT sum INTO i from t_total WHERE tablena