MySQL学习【第八篇索引优化】

一.建立索引的原则（规范）

1.选择唯一性索引

只要可以创建唯一性索引的，一律创建唯一索引（因为速度快呀）

判断是否能创建唯一索引，用count（列名），count（distinct(列名)）一样就能创建

 

2.如果该列重复值较多，则采用联合索引

 

3.为经常需要排序，分组，联合操作的字段建立索引，例如京东，淘宝那些产品就需要建立索引

 

4.为常作为查询字段的建立索引，比如你搜索的商品，它们基本就是做了索引的，而且不仅做了，还肯定加了缓存

 

5.如果是经常查询字段，重复值还特别多的，使用联合索引

 

6.字段值很长的，尽量使用前缀索引

 

7.限制索引的数目，索引不是每一列都要创建的，每个索引都要占用磁盘空间，创的越多，磁盘占用越大，修改表的时候，对索引的重构和更新非常麻烦还很慢，每次update都很麻烦，而且还浪费磁盘I/O

 

8.删除不常用的索引和不再使用的索引，减少索引对更新操作的影响，节省资源

 

二.优化sql语句

1.没有查询条件，或者查询条件没有建立索引（没查询条件就是全表扫描）

优化：添加查询条件，为查询条件创建索引

（在业务数据中，是没有全表扫描这种需求的，而且用户也不可能有权限去全表扫描，对用户体验是痛苦的，对服务器性能更是毁灭性打击，如果有人提出这种要求，拉出去祭天就是了！！！）

 

2.查询结果集原表中的大部分数据时（大概在25%以上时就将不走索引），添加limit限定

 

3.索引本身失效，数据不真实，对于表的内容更新过于频繁，有可能导致索引失效

怎么看是否失效呢，写个一定会走索引的语句的脚本，然后监控它

如果得到结果是失效的，删除索引，重新建立

 

4.查询条件使用函数在索引列上进行运算例如加减乘除，则不走索引（错误示范），如果你硬要秀操作则看（正确示范）

错误示范
select * from student where id-1=5;
 
正确示范
select * from student where id=5+1;

或者

select * from student where id=6;

 

 

5.隐式转化导致的索引失效（一般都是开发犯的错误，祭天祭天！！！）

mysql> create table test (id int ,name varchar(20),telnum varchar(10));
mysql> insert into test values(1,'zs','110'),(2,'l4',120),(3,'w5',119),(4,'z4',112);
mysql> explain select * from test where telnum=120;
mysql> alter table test add index idx_tel(telnum);
mysql> explain select * from test where telnum=120;
mysql> explain select * from test where telnum=120;
mysql> explain select * from test where telnum='120';

 

 

6. <> ，not in 不走索引（单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limitor或in尽量改成union）

mysql> select * from tab where telnum <> '1555555';
mysql> explain select * from tab where telnum <> '1555555';

 

7.like "%_" 百分号在最前面不走索引

#走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%';
#不走索引
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110';

 

 

8.单独引用联合索引里非第一位置的索引列

CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);
ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
DESC t1
SHOW INDEX FROM t1
#走索引的情况测试
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  AND sex='m';
#部分走索引
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30  AND sex='m'; 
#不走索引
EXPLAIN SELECT  NAME,age,sex,money FROM t1 WHERE age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';