大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说Mysql索引优化,希望您对编程的造诣更进一步.
![Mysql索引优化[数据库教程]](/www.yht7.com/upload/image/images/imgsql/4.jpg "Mysql索引优化插图")
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 | 没有遍历就返回数据 |
索引优化原则
- 不以通配符开头
- 数据无隐式转化
所以字符串要用引号
- 索引列上作计算，函数，类型转化等将使索引失效
-
覆盖索引
select值取出需要的列，尽量避免select *。另外可将查询列加入形成组合索引避免回表
-
最佳左前缀法则
where条件从索引的最左前列开始且不跳过索引列使用，下列情况索引失效
-
范围列右边的索引列失效
关注key_len长度
Mysql索引优化
原文地址：https://www.cnblogs.com/kite97/p/13462314.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/6793.html
