Mysql索引优化

准备数据

建立一个测试用表

技术图片

往表中插入10w条随机数据的存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `insert_tbl`(in max_num int(10))
BEGIN

declare i int default 0;
set autocommit=0;

repeat
set i=i+1;
insert into test_tbl (id,user_name,access_time,ip_addr,user_group)values(null,rand_string(rand()*7+3),rand_datetime(2015,5),rand_ip(),rand_num());
until i = max_num
end repeat;
commit;
END

1）随机字符串函数(param表示生成字符串的长度)

CREATE DEFINER=`root`@`%` FUNCTION `rand_string`(n int) RETURNS varchar(255) CHARSET latin1
BEGIN
	
declare chars_str varchar(52) default ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ‘;
declare return_str varchar(255) default ‘‘;
declare i int default 0;

while i < n do
set return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i=i+1;
end while;
return return_str;
END

2）随机日期函数（param1表示最小，param2表示年份波动）

CREATE DEFINER=`root`@`%` FUNCTION `rand_datetime`(n year, num int) RETURNS varchar(255) CHARSET latin1
BEGIN
	
 declare aDatetime varchar(255) default ‘‘;
    set aDatetime=concat(concat(n+floor((rand()*num)),‘-‘,
                    lpad(floor(2 + (rand() * 11)),2,0),‘-‘,
                    lpad(floor(3 + (rand() * 25)),2,0)),  #最多到每月27号
                    ‘ ‘,
                    concat(lpad(floor(0 + (rand() * 23)),2,0),‘:‘,
                    lpad(floor(0 + (rand() * 60)),2,0),‘:‘,
                    lpad(floor(0 + (rand() * 60)),2,0)));
    return aDatetime;
END

3）随机ip函数

CREATE DEFINER=`root`@`%` FUNCTION `rand_ip`() RETURNS varchar(255) CHARSET latin1
BEGIN
	
declare ip varchar(255) default ‘‘;
set ip = concat(FLOOR(100 + RAND() * 26), ‘.‘,
                         FLOOR(0 + RAND() * 256), ‘.‘,
                         FLOOR(0 + RAND() * 256), ‘.‘,
                         FLOOR(0 + RAND() * 256));
 return ip;
END

4）随机数字函数（已指定范围在0-127，可以修改为传参指定）

CREATE DEFINER=`root`@`%` FUNCTION `rand_num`() RETURNS int(8)
BEGIN
	
declare i int default 0;
set i = floor(0+rand()*128);
return i;
END

索引类型

查看表的索引

技术图片

增加索引

-- primary key每个表只允许存在一个，而且不重复不为空
alter table test_tbl add unique unique_index(ip_addr)  #唯一索引，不许重复，允许为空
alter table test_tbl add index normal_index(user_name)  #普通索引，没有限制
alter table test_tbl add index union_index(user_name,access_time,user_group)  #组合索引,包含多列避免回表

删除某个索引

drop index index_name on test_tbl;

Explain执行计划

explain列的含义

列项	含义
id	每个select关键字对应一个唯一id
select_type	查询类型
partitions	匹配的分区信息
type	单表的访问方法
possible_keys	可能用到的索引
key	实际使用的索引
key_len	实际使用的索引长度
ref	索引的哪一列被使用
rows	预估需要读取的记录数目
filtered	经过滤剩余的记录条数百分比
extra	额外信息

说明：

select_type字段	含义
SIMPLE	简单查询，不包含子查询或union查询
PRIMARY	查询中包含复杂的子部份，最外层标记为主查询
SUBQUERY	在select或where中包含子查询
DERIVED	在from中的子查询标记为衍生，查询结果存放在临时表中
UNION	select出现在union之后
UNION RESULT	从union结果表中获取查询结果

type字段	含义
null	优化阶段分解查询语句，执行阶段不访问表或索引
system	表中只有一条记录（等同于系统表）
const	通过索引一次命中（如where后跟主键条件）
eq_ref	唯一性索引扫描
ref	非唯一性索引扫描，可能会找到符合条件的索引行
ref_or_null	类似ref但可以搜索null
index_merge	索引合并
range	只检索给定范围的行（between,><,in）
index	遍历索引树
all	从硬盘中遍历全表已找到匹配行

extra字段：

Extra字段	含义
Using filesort	对数据作外部排序，而不是按表内索引顺序读取
Using temporary	使用临时表保存中间结果（排序，分组）
Using index	使用覆盖索引（同时有using where表示索引不是读取数据而是查找键值）
Using where	条件查询
Using join buffer	使用连接缓存
impossible where	where子句总为false
distinct	一旦找到匹配行就不再搜索
select tables optimized away	没有遍历就返回数据