批量插入300W数据,由于Innodb默认支持事物导致插入速度缓慢,先修改为MyISAM
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_auto_insertdata`()
BEGIN
DECLARE init_data INTEGER DEFAULT 1;
WHILE init_data <= 3000000 DO
INSERT INTO test_big_data VALUES(init_data, CONCAT('姓名', init_data), '男','中国','1');
SET init_data = init_data + 1;
END WHILE;
END
分页查询
SELECT * FROM xmall.test_big_data limit 10,30;
SELECT * FROM xmall.test_big_data limit 2000000,2000020;
从中我们也能总结出两件事情:
1)limit语句的查询时间与起始记录的位置成正比
2)mysql的limit语句是很方便,但是对记录很多的表并不适合直接使用。
针对分页进行优化:
利用覆盖索引优化sql
SELECT id FROM xmall.test_big_data limit 2000000,2000020;
可以看到速度有明显的提高
查询非ID主键数据
SELECT t.* FROM xmall.test_big_data t where t.name = '姓名1578888';
配置索引后
模糊查询
explain SELECT t.* FROM xmall.test_big_data t where t.name like '%姓名5788%' ;
explain SELECT t.* FROM xmall.test_big_data t where t.name like '姓名5788%' ;
如果%在前则会走全表扫描
但是如果使用主键索引查询的话,就算开头不适用% ,依然会走全表扫描
组合索引
需要满足最左原则
查询条件放到子查询中,子查询只查主键ID,然后使用子查询中确定的主键关联查询其他的属性字段;
-- 优化前SQL
SELECT 各种字段
FROM `table_name`
WHERE 各种条件
LIMIT 0,10;
-- 优化后SQL
SELECT 各种字段
FROM `table_name` main_tale
RIGHT JOIN
(
SELECT 子查询只查主键
FROM `table_name`
WHERE 各种条件
LIMIT 0,10;
) temp_table ON temp_table.主键 = main_table.主键
InnoDB中有buffer pool。里面存有最近访问过的数据页,包括数据页和索引页。所以我们需要运行两个sql,来比较buffer pool中的数据页的数量。预测结果是运行select * from test a inner join (select id from test where val=4 limit 300000,5);
之后,buffer pool中的数据页的数量远远少于select * from test where val=4 limit 300000,5;
对应的数量,因为前一个sql只访问5次数据页,而后一个sql访问300005次数据页。