MySQL 索引设计原则

MySQL 索引设计原则：

（1）对于经常查询的字段，建议创建索引。

（2）索引不是越多越好，一个表如果有大量索引，不仅占用磁盘空间，而且会影响INSERT，DELETE，UPDATE等语句的性能。

（3）避免对经常更新的表进行过多的索引，因为当表中数据更改的同时，索引也会进行调整和更新，十分消耗系统资源。

（4）数据量小的表建议不要创建索引，数据量小时索引不仅起不到明显的优化效果，对于索引结构的维护反而消耗系统资源。

（5）不要在区分度低的字段建立索引。比如性别字段，只有 “男” 和 “女” ，建索引完全起不到优化效果。

（6）当唯一性是某字段本身的特征时，指定唯一索引能提高查询速度。

（7）在频繁进行跑排列分组（即进行 group by 或 order by操作）的列上建立索引，如果待排序有多个，可以在这些列上建立组合索引。

MySQL 索引未命中情况：

（1）范围问题，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like等，如果查询范围不明确，达不到优化效果。

没有指定范围不会用到索引：

select name from s1;

大于号、小于号范围过大：

不等于！=

between ...and...

like，如果以%开头，不会用到索引：

（2） 索引列不能参与计算，保持列“干净”。

比如from_unixtime(create_time) = '2014-05-29'就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp('2014-05-29')

（3）最左前缀匹配原则，非常重要的原则。不符合规范则不会命中索引。

对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)。

比如：a = 1 and b = 2 and c > 3 and d = 4， 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

（4）使用函数不能命中索引

select * from tb1 where reverse(email) = 'duoduo'; -

（5）类型不一致 如果列是字符串类型，传入条件是必须用引号引起来，不然...

select * from tb1 where email = 999;

（6） order by ：

当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢

select email from s1 order by email desc;

特别的：如果对主键排序，则还是速度很快：

select * from tb1 order by nid desc;

（7）text类型，必须制定长度，否则无法命中：

create index xxxx on tb(title(19))

其他注意事项

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致

and 和 or 的索引匹配逻辑：

（1）and的逻辑，例子：

条件：a = 10 and b = 'xxx' and c > 3 and d =4

索引：制作联合索引(d,a,b,c)

工作原理:：对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照 d -> a -> b -> c 的顺序

（2）or的逻辑，例子：

条件：a = 10 or b = 'xxx' or c > 3 or d =4

索引：制作联合索引(d,a,b,c)

工作原理：对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即 a-> b -> c -> d

参考文章：https://m.jb51.net/article/140749.htm

参考书籍：

《MySQL5.7 从入门到精通》刘增杰 编著

《Java Web 轻量级开发 面试教程》孟宪福 胡书敏 金华 编著