mysql索引规范_MySQL索引规范及优化

MySQL索引规范及优化

辅助索引联合索引执行计划key_len

欢迎来到 来到大浪涛天的博客 ！

一、MySQL索引规范及优化

1.B树功能分类

聚集索引

辅助索引

2.辅助索引分类

单列:单独对某列进行增加索引

联合：对需要的多个列进行索引，一般有多个子句查询功能的要用

唯一：选择唯一键建立索引，当唯一键索引的等值查询是所有辅助索引性能最高的。

前缀：对于选择索引列字段比较长，可以选择前缀索引。

3.执行计划分析

执行计划分析是获取到的优化器选择完成的,他认为代价最小的执行计划.

作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.

如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。

select 获取数据的方法

全表扫描(应当尽量避免,因为性能低)

索引扫描

获取不到数据

table索引类型分析

从左到右性能依次变好.

ALL :

全表扫描,不走索引

例子:

1. 查询条件列,没有索引

SELECT * FROM t_100w WHERE k2='780P';

2. 查询条件出现以下语句(辅助索引列)

USE world

DESC city;

DESC SELECT * FROM city WHERE countrycode <> 'CHN';

DESC SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');

DESC SELECT * FROM city WHERE countrycode LIKE '%CH%';

注意:对于聚集索引列,使用以上语句,依然会走索引

DESC SELECT * FROM city WHERE id <> 10;

INDEX :

全索引扫描

1. 查询需要获取整个索引树种的值时:

DESC SELECT countrycode FROM city;

2. 联合索引中,任何一个非最左列作为查询条件时:

idx_a_b_c(a,b,c) ---> a ab abc

SELECT * FROM t1 WHERE b

SELECT * FROM t1 WHERE c

RANGE :

索引范围扫描

辅助索引> < >= <= LIKE IN OR

主键 <> NOT IN

例子:

1.

DESC SELECT * FROM city WHERE id<5;

2.

DESC SELECT * FROM city WHERE countrycode LIKE 'CH%';

3.

DESC SELECT * FROM city WHERE countrycode IN ('CHN','USA');

注意:

1和2例子中,可以享受到B+树的优势,但是3例子中是不能享受的.

所以,我们可以将3号列子改写:

DESC SELECT * FROM city WHERE countrycode='CHN'

UNION ALL

SELECT * FROM city WHERE countrycode='USA';

ref:

非唯一性索引,等值查询

DESC SELECT * FROM city WHERE countrycode='CHN';

eq_ref:

在多表连接时,连接条件使用了唯一索引(uk pK)

DESC SELECT b.name,a.name FROM city AS a

JOIN country AS b

ON a.countrycode=b.code

WHERE a.population <100;

DESC country

system,const :

唯一索引的等值查询

DESC SELECT * FROM city WHERE id=10;

possible_key:可能会用到的索引，添加了几个索引都会列出来

key: 真正选择了哪个索引，可以通过key_len 索引覆盖长度来确认

utf8mb4 int(11)最大保留长度是4，4*2+1=9

char(2),2*4+1=9

varchar(2)=2*4+3=11

char(4)=4*4+1=17

varchar(4)= 4*4+3=19

key_len 索引覆盖长度

varchar(20) utf8mb4

能存20个任意字符

不管存储的时字符,数字,中文,都1个字符最大预留长度是4个字节

对于中文,1个占4个字节

对于数字和字母,1个实际占用大小是1个字节

select length() from test;(确认所占字节长度)

Extra: Using filesort

出现Using filesort,说明在查询中有关排序的条件列没有合理的应用索引

extra:

filesort ,文件排序.

SHOW INDEX FROM city;

ALTER TABLE city ADD INDEX CountryCode(CountryCode);

ALTER TABLE city DROP INDEX idx_c_p;

DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population

ALTER TABLE city ADD INDEX idx_(population);

DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population

ALTER TABLE city ADD INDEX idx_c_p(countrycode,population);

ALTER TABLE city DROP INDEX idx_;

ALTER TABLE city DROP INDEX CountryCode;

DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population

结论:

1.当我们看到执行计划extra位置出现filesort,说明由文件排序出现

2.观察需要排序(ORDER BY,GROUP BY ,DISTINCT，union all )的条件,有没有索引

3. 根据子句的执行顺序,去创建联合索引

4.联合索引应用细节

只要我们将来的查询,所有索引列都是查询条件下,无关排列顺序

唯一值多的列放在最左侧

abcd

acbd

adbc

acbd

等等

mysql> desc select * from test where k1='aa' and k2='中国' and k3='aaaa' and k4='中国你好';

mysql> desc select * from test where k2='中国' and k3='aaaa' and k4='中国你好' and k1='aa';

原因: 优化器,自动做查询条件的排列

不连续部分条件

如果查询语句条件是where c= d= a= 的话，那索引也最好按idx(c,d,a)来，

cda ----> acd ---> a -----> idx(c,d,a)

dba ----> abd ---> ab ----> idx(d,b,a)

在where查询中如果出现> < >= <= like

1. mysql> desc select * from test where k1='aa' and k3='aaaa' and k4='中国你好' and k2>'中国';

2. mysql> alter table test add index idx1(k1,k3,k4,k2);

多子句 查询,应用联合索引

mysql> desc select * from test where k1='aa' order by k2;

mysql> alter table test add index idx3(k1,k2);

5.索引应用规范

5-1.建立索引的原则(DBA运维规范)

(1) 建表必须要有主键,一般是无关列,自增长

(2) 经常做为where条件列 order by group by join on, distinct 的条件

(3) 最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做

(4) 列值长度较长的索引列,我们建议使用前缀索引.

(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)

(6) 索引维护要避开业务繁忙期

(7) 小表不建索引

5-2.不走索引的情况(开发规范)

(1) 没有查询条件，或者查询条件没有建立索引

select * from city;

select * from city where 1=1;

(2) 查询结果集是原表中的大部分数据，应该是25％以上。

(3) 索引本身失效，统计数据不真实,如同一个语句突然变慢?原因是统计信息过旧,导致的索引失效

(4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

mysql> desc select * from city where id-99=1;

(5) 隐式转换导致索引失效，如字段是varchar类型的，但是你检索的时候确实数字，且不带引号，这样也是不走索引的，因为要多一次转换。

(6) <> ，not in 不走索引(辅助索引)

(7) like "%aa" 百分号在最前面不走

(8) 联合索引的细节规范，多多关注key_len