MySQL索引规范及优化
MySQL索引规范及优化
欢迎来到 来到大浪涛天的博客 ！
一、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
