2、union、in、or 都能够命中索引,建议使用 in
union
能够命中索引,并且MySQL 耗费的 CPU 最少。
select * from doc where status=1
union all
select * from doc where status=2;
复制代码
in
能够命中索引,查询优化耗费的 CPU 比union all
多,但可以忽略不计,一般情况下建议使用in
。
select * from doc where status in (1, 2);
复制代码
or
新版的 MySQL 能够命中索引,查询优化耗费的 CPU 比in
多,不建议频繁用or
。
select * from doc where status = 1 or status = 2
复制代码
- 补充:有些地方说在
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`)。
- 建立联合索引的时候,区分度最高的字段在最左边
- 存在非等号和等号混合判断条件时,在建立索引时,把等号条件的列前置。如
where a>? and b=?
,那么即使a
的区分度更高,也必须把b
放在索引的最前列。
- 最左前缀查询时,并不是指SQL语句的where顺序要和联合索引一致。
- 下面的 SQL 语句也可以命中
(login_name, passwd)
这个联合索引:
select uid, login_time from user where passwd=? andlogin_name=?
复制代码
- 但还是建议
where
后的顺序和联合索引一致,养成好习惯。
- 假如
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的场景,请注意利用索引的有序性
order by
最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现file_sort 的情况,影响查询性能。
- 例如对于语句
where a=? and b=? order by c
,可以建立联合索引(a,b,c)
。
- 如果索引中有范围查找,那么索引有序性无法利用,如
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)]