sql优化

1.  创建一个临时内存表, 做数据插入的时候会比较快些

DROP TABLE IF EXISTS `vote_record_memory`;
CREATE TABLE `vote_record_memory` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1',
    `create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 

2.  创建一个普通表，用作模拟大数据的测试用例

DROP TABLE IF EXISTS `vote_record`;
CREATE TABLE `vote_record` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '' COMMENT '用户Id',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '投票数',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户组id 0-未激活用户 1-普通用户 2-vip用户 3-管理员用户',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '状态 1-正常 2-已删除',
    `create_time` int(10) unsigned NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH COMMENT '用户ID哈希索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='投票记录表';

3.  为了数据的随机性和真实性，我们需要创建一个可生成长度为n的随机字符串的函数
DELIMITER // -- 修改MySQL delimiter：'//'
DROP FUNCTION IF EXISTS `rand_string` //
SET NAMES utf8 //
CREATE FUNCTION `rand_string` (n INT) RETURNS VARCHAR(255) CHARSET 'utf8'
BEGIN 
    DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1));
        SET i = i+1;
    END WHILE;
    RETURN return_str;
END //

4.   为了操作方便，我们再创建一个插入数据的存储过程

-- 创建插入数据的存储过程
DROP PROCEDURE IF EXISTS `add_vote_record_memory` //
CREATE PROCEDURE `add_vote_record_memory`(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE vote_num INT DEFAULT 0;
    DECLARE group_id INT DEFAULT 0;
    DECLARE status TINYINT DEFAULT 1;
    WHILE i < n DO
        SET vote_num = FLOOR(1 + RAND() * 10000);
        SET group_id = FLOOR(0 + RAND()*3);
        SET status = FLOOR(1 + RAND()*2);
        INSERT INTO `vote_record_memory` VALUES (NULL, rand_string(20), vote_num, group_id, status, NOW());
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;  -- 改回默认的 MySQL delimiter：';'

 

5. 开始执行存储过程，等待生成数据(10W条生成大约需要40分钟)

CALL add_vote_record_memory(100000);

6. 查询内存表已生成记录(为了下步测试，目前仅生成了105645条)

SELECT count(*) FROM `vote_record_memory`;

7. 把数据从内存表插入到普通表中(10w条数据13s就插入完了)     

INSERT INTO vote_record SELECT * FROM `vote_record_memory`;

8.  查询普通表已的生成记录    SELECT count(*) FROM `vote_record`;

 

测试下面的sql语句

select * from vote_record_memory where vote_num > 1000

时间比较长基本上好几分钟  如果添加相应字段的索引会怎么样

create index vot on vote_record_memory(vote_num)

再次使用select * from vote_record_memory where vote_num > 1000 然并卵 因为这不是主键索引 数据页也不是连续得不会给你进行范围查询

 

如果加上id 看看主键

EXPLAIN SELECT * FROM vote_record_memory  WHERE id > 0  AND vote_num > 1000;

 

 

如果加上id  改变num = 号就可以用上了

EXPLAIN SELECT * FROM vote_record_memory  WHERE id > 0  AND vote_num = 1000;

用上索引之后速度飞起

 

 

explain分析

id越高 优先级越高

 

总结：

1.mysql嵌套子查询效率确实比较低

2.可以将其优化成连接查询

3.建立合适的索引

4.学会分析sql执行计划，mysql会对sql进行优化，所以分析执行计划很重要

 

三、海量数据分页查找时如何使用主键索引进行优化

对应实现

type为all是最垃圾的

select * from vote_record_memory where id > (select id from vote_record_memory where id > 10000 litmit 1) limit 20;

这个就是加上了主键索引 大大提高了速度

分析一波 两个查询 先执行子查询 子查询用到了where和索引 这样效率高得多

 

再看看直接用的话速度差距