mysql单表数据操作优化(一)

系统环境

window7+8g内存+250g硬盘+i5处理器+5000转

1.创建表结构

CREATE TABLE `test` (
  `uid` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `zid` char(32) DEFAULT '' COMMENT '32位随机主键',
  `name` varchar(50) DEFAULT '' COMMENT '名称',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=8000001 DEFAULT CHARSET=utf8 COMMENT='测试表';

2.用存储过程+事务插入800万条数据

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `proc_insert`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_insert`()
BEGIN
DECLARE i INT;
SET i = 0;
START TRANSACTION;
WHILE(i<8000000) DO
INSERT INTO test(`zid`,`name`) VALUES(REPLACE (UUID(), '-', ''), CONCAT('abc',i));
SET i=i+1;
END WHILE;
COMMIT;
    END$$
DELIMITER ;

3.调用存储过程

call proc_insert();

测试过程如下：

1.插入800万数据消耗时间30分钟;

2.select count(*) from test;平均消耗时间13s;

使用explain进行解释如下图：

type = index 跟all相同都是全表扫描，不同是index比all快，因为all从硬盘读取，index从索引读取，索引文件要比数据文件小；

possible_key = null 没有使用索引

key = primary 主键索引

key_len = 4 索引长度

ref = null 没有列参与索引

rows = 7632456 查询的行数，数字越大说明查询的时间越长

extra 包含mysql查询的详细信息

3.select * from test where name = 'abc0';平均消耗时间22s;

使用explain进行解释如下图：

type = all 全表扫描

key = null 没有索引

rows = 7632456 查询的行数，数字越大说明查询的时间越长

4.select * from test where name = 'abc7999999';平均消耗时间22s;

使用explain进行解释如下图：

type = all 全表扫描

key =null 没有索引

rows = 7632456 查询的行数，数字越大说明查询的时间越长

使 name = ’abc0‘ 是查询第一条数据，name='abc7999999'查询最后一条，查询的效率是一样的，都是全表扫描；我们可以为这两条sql语句加上limit限制，只查一条：

select * from test where name = 'abc0' limit 1;

select * from test where name = 'abc7999999' limit 1;

使用explain进行解释如下：

type = all 全表扫描

key =null 没有索引

rows = 7632456 查询的行数，数字越大说明查询的时间越长

extra = Using where 表示我们使用了条件查询即 limit 1;

通过以上查询可以看出即使我们加了限制，无论是查第一条还是最后一条，查询的效率还是一样；查询非常慢；

单表优化解决方案:  添加索引

create index test_name_index on test(`name`);

测试过程如下：

SELECT * FROM test WHERE `name` = 'abc0'; 查询第一条

SELECT * FROM test WHERE `name` = 'abc7999999';  查询最后一条

添加索引之后，查询时间平均为0.001s 查询速度巨大的提升，惊讶；

我们在看看用explain解释的情况:

type = ref 对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取，不知道什么意思

possible_keys = test_name_index 使用了索引，名称为test_name_index

key = test_name_index 显示MySQL实际决定使用的键(索引)

key_len = 153 索引的长度153

rows = 1 这是重点,使用索引之后只查询一行，没有进行全表扫描

extra: Using index condition 使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息

接下来我们查询count数量

select count(*) from test; 该查询没有条件，我们是用explain解释如下：

type = index

key = test_name_index

key_len = 153

rows = 7632456

extra = Using index

说明即使使用了索引，在没有条件的情况下查询count的效率依然不好，查询时间依然很慢；

现在我们在查询count的基础上添加条件

SELECT COUNT(*) FROM test WHERE `name` LIKE '%abc79999%'; 

SELECT COUNT(*) FROM test WHERE `name` LIKE 'abc79999%';

使用like进行模糊匹配的时候,'%%'全模糊查询在索引上不起作用(如第一条sql)；使用右模糊查询才起作用(如第二条sql);

type = range 只检索给定范围的行，使用一个索引来选择行。

rows = 110 这是重点，只查询110行

extra: Using where;Using index； where子句用于限制哪一个行匹配下一个表或发送到客户

结论说明

1.单表中有大数据的时候，无论查询第一条还是最后一条，无论加limit 1限制还是不加这个限制，没有使用索引的情况下都是进行的全表扫描，并且效率特别低；

2.加入索引之后，查询效率有飞跃般的提升，查询不再进行全表扫描，而是从索引中直接查询；

3.索引必须在where条件下才起作用；

4.索引在 like 查询的时候，全模糊匹配所以不起作用，右模糊才起作用；

5.无论加不加索引，在没有where条件的select count(*) 查询中，查询也是全表扫描，查询效率非常低；

6.加入索引之后，只有加入where条件select count(*) 才能真正使索引起作用；