MySQL索引如何优化？二十条铁则送给你！

2、union、in、or 都能够命中索引，建议使用 in

1. union能够命中索引，并且MySQL 耗费的 CPU 最少。

select * from doc where status=1

union all

select * from doc where status=2;

复制代码

1. in能够命中索引，查询优化耗费的 CPU 比 union all 多，但可以忽略不计，一般情况下建议使用 in。

select * from doc where status in (1, 2);

复制代码

1. or 新版的 MySQL 能够命中索引，查询优化耗费的 CPU 比 in多，不建议频繁用or。

select * from doc where status = 1 or status = 2

复制代码

1. 补充：有些地方说在where条件中使用or，索引会失效，造成全表扫描，这是个误区：

• ①要求where子句使用的所有字段，都必须建立索引；

• ②如果数据量太少，mysql制定执行计划时发现全表扫描比索引查找更快，所以会不使用索引；

• ③确保mysql版本5.0以上，且查询优化器开启了index_merge_union=on, 也就是变量optimizer_switch里存在index_merge_union且为on。

3、负向条件查询不能使用索引

• 负向条件有：!=、<>、not in、not exists、not like 等。

• 例如下面SQL语句：

select * from doc where status != 1 and status != 2;

复制代码

• 可以优化为 in 查询：

select * from doc where status in (0,3,4);

复制代码

4、联合索引最左前缀原则

• 如果在(a,b,c)三个字段上建立联合索引，那么他会自动建立 a| (a,b) | (a,b,c)组索引。

• 登录业务需求，SQL语句如下：

select uid, login_time from user where login_name=? andpasswd=?

复制代码

• 可以建立`(login 需要zi料+ 绿色徽【vip1024b】

_name, passwd)的联合索引。因为业务上几乎没有passwd 的单条件查询需求，而有很多login_name 的单条件查询需求，所以可以建立(login_name, passwd)的联合索引，而不是(passwd, login_name`)。

1. 建立联合索引的时候，区分度最高的字段在最左边

1. 存在非等号和等号混合判断条件时，在建立索引时，把等号条件的列前置。如 where a>? and b=?，那么即使a 的区分度更高，也必须把 b 放在索引的最前列。

1. 最左前缀查询时，并不是指SQL语句的where顺序要和联合索引一致。

• 下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引：

select uid, login_time from user where passwd=? andlogin_name=?

复制代码

• 但还是建议 where 后的顺序和联合索引一致，养成好习惯。

1. 假如index(a,b,c), where a=3 and b like 'abc%' and c=4，a能用，b能用，c不能用。

5、不能使用索引中范围条件右边的列（范围列可以用到索引），范围列之后列的索引全失效

• 范围条件有：<、<=、>、>=、between等。

• 索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

• 假如有联合索引 (empno、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而title 和 from_date 则使用不到索引。

select * from employees.titles where emp_no < 10010’ and title='Senior Engineer’and from_date between ‘1986-01-01’ and ‘1986-12-31’

复制代码

6、不要在索引列上面做任何操作（计算、函数），否则会导致索引失效而转向全表扫描

• 例如下面的 SQL 语句，即使 date 上建立了索引，也会全表扫描：

select * from doc where YEAR(create_time) <= ‘2016’;

复制代码

• 可优化为值计算，如下：

select * from doc where create_time <= ‘2016-01-01’;

复制代码

• 比如下面的 SQL 语句：

select * from order where date < = CURDATE()；

复制代码

• 可以优化为：

select * from order where date < = ‘2018-01-2412:00:00’;

复制代码

7、强制类型转换会全表扫描

• 字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。

• 如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引。

select * from user where phone=13800001234

复制代码

• 可以优化为：

select * from user where phone=‘13800001234’;

复制代码

8、更新十分频繁、数据区分度不高的列不宜建立索引

• 更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。

• “性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

• 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。

9、利用覆盖索引来进行查询操作，避免回表，减少select * 的使用

• 覆盖索引：查询的列和所建立的索引的列个数相同，字段相同。

• 被查询的列，数据能从索引中取得，而不用通过行定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。

• 例如登录业务需求，SQL语句如下。

Select uid, login_time from user where login_name=? and passwd=?

复制代码

• 可以建立(login_name, passwd, login_time)的联合索引，由于 login_time 已经建立在索引中了，被查询的 uid 和 login_time 就不用去 row 上获取数据了，从而加速查询。

10、索引不会包含有NULL值的列

• 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时，尽量使用not null 约束以及默认值。

11、is null, is not null无法使用索引

12、如果有order by、group by的场景，请注意利用索引的有序性

1. order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort 的情况，影响查询性能。

• 例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。

1. 如果索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b;，索引(a,b)无法排序。

13、使用短索引（前缀索引）

• 对列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果该列在前10个或20个字符内，可以做到既使得前缀索引的区分度接近全列索引，那么就不要对整个列进行索引。因为短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作，减少索引文件的维护开销。可以使用count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度。

• 但缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引。

• 不过很多时候没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

14、利用延迟关联或者子查询优化超多分页场景

• MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

• 示例如下，先快速定位需要获取的id段，然后再关联:

总结

其他的内容都可以按照路线图里面整理出来的知识点逐一去熟悉，学习，消化，不建议你去看书学习，最好是多看一些视频，把不懂地方反复看，学习了一节视频内容第二天一定要去复习，并总结成思维导图，形成树状知识网络结构，方便日后复习。

这里还有一份很不错的《Java基础核心总结笔记》，特意跟大家分享出来

目录：

部分内容截图：

的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

• 示例如下，先快速定位需要获取的id段，然后再关联:

总结

其他的内容都可以按照路线图里面整理出来的知识点逐一去熟悉，学习，消化，不建议你去看书学习，最好是多看一些视频，把不懂地方反复看，学习了一节视频内容第二天一定要去复习，并总结成思维导图，形成树状知识网络结构，方便日后复习。

这里还有一份很不错的《Java基础核心总结笔记》，特意跟大家分享出来

目录：

[外链图片转存中…(img-0MOjOUC3-1710363040620)]

部分内容截图：

[外链图片转存中…(img-4YFSdgXL-1710363040621)]

[外链图片转存中…(img-SHGAHtWd-1710363040621)]