Mysql索引优化 [数据库教程]

  • Z时代
  • 2024-01-10
  • 分类：综合

database

Mysql索引优化

准备数据

  1. 建立一个测试用表

  1. 往表中插入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

没有遍历就返回数据

索引优化原则

  1. 不以通配符开头

  1. 数据无隐式转化

所以字符串要用引号

  1. 索引列上作计算，函数，类型转化等将使索引失效

  1. 覆盖索引

    select值取出需要的列，尽量避免select *。另外可将查询列加入形成组合索引避免回表

  2. 最佳左前缀法则

    where条件从索引的最左前列开始且不跳过索引列使用，下列情况索引失效

  1. 范围列右边的索引列失效

    关注key_len长度

Mysql索引优化

以上是 Mysql索引优化 [数据库教程] 的全部内容， 来源链接： utcz.com/z/535126.html

回到顶部