众所周知,索引类似于字典的目录,可以提高查询的效率。索引从物理上可以分为:聚集索引,非聚集索引从逻辑上可以分为:普通索引,唯一索引,主键索引,联合索引,全文索引

在列上进行运算或使用函数会使索引失效,从而进行全表扫描。如下面例子在publish_time,id列上分别加上索引,publish_time为datetime类型,id为int类型

-- 全表扫描select * from article where year(publish_time) < 2019-- 走索引select * from article where publish_time < '2019-01-01'-- 全表扫描select * from article where id  + 1 = 5-- 走索引select * from article where id = 4小心隐式类型转换假设id为varchar类型

-- 全表扫描select * from article where id = 100-- 走索引select * from article where id = '100'为什么呢?

select * from article where id = 100-- 等价于select * from article where CAST(id AS signed int) = 100上一条规则说过,不要在索引列上使用函数,隐式类型转换在索引字段上做了函数操作,因此会全表扫描

那么如果id是int,执行下面这个语句是否会导致全表扫描呢?

select * from article where id = '100'答案是会用到索引

-- 全表扫描select * from article where author like '%李'%李,%李%都会导致全表扫描,非前导模糊查询可以使用索引

-- 走索引select * from article where author like '李%'联合索引最左前缀原则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的顺序可以任意调整

1.将区分度最高的字段放在最左边

当不需要考虑排序和分组时,将区分度最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找

如果在a b列上建立联合索引,该如何建立,才能使查询效率最高

select count(distinct a) / count(*), count(distinct b) / count(*), count(*) from table执行如下语句,假设3个输出依次为0.001,0.373,16049,可以看到b列的选择性最高,因此将其作为联合索引的第一列,即建立(b, a)的联合索引

2.查询时=可以乱序如果建立了联合索引(a, b)。例如下面的2个写法是等价的,因为MySQL会将查询的顺序优化成和联合索引的顺序一致

select * from table where a = '1' and b = '1'select * from table where b = '1' and a = '1'3.优化查询,避免出现filesort

select * from table where a = ? and b = ? order by c最左前缀原则不仅用在查询中,还能用在排序中。MySQL中,有两种方式生成有序结果集:

通过有序索引顺序扫描直接返回有序数据

Filesort排序,对返回的数据进行排序

因为索引的结构是B+树,索引中的数据是按照一定顺序进行排列的,所以在排序查询中如果能利用索引,就能避免额外的排序操作。EXPLAIN分析查询时,Extra显示为Using index。

所有不是通过索引直接返回排序结果的操作都是Filesort排序,也就是说进行了额外的排序操作。EXPLAIN分析查询时,Extra显示为Using filesort,当出现Using filesort时对性能损耗较大,所以要尽量避免Using filesort

对于如下sql

select * from table where a = ? and b = ? order by c可以建立联合索引(a, b, c)

如果索引中有范围查找,那么索引有序性无法利用,如

select * from table where a > 10 order by b索引(a,b)无法排序。

放几个例子

-- 使用了a列where a = 3-- 使用了a b列where a = 3 and b = 5-- 使用了a b c列where a = 3 and c = 4 and b = 5-- 没有使用索引where b = 3-- 使用了a列 where a = 3 and c = 4-- 使用了a b列 where a = 3 and b > 10 and c = 7-- 使用了a b 列where a = 3 and b like 'xx%' and c = 7union,or,in都能命中索引,建议使用inselect * from article where id = 1union allselect * from article where id = 2select * from article where id in (1 , 2)新版MySQL的or可以命中索引

select * from article where id = 1 or id = 2效率从高到低为union,in,or。in和union的效率差别可以忽略不计,建议使用in

负向条件有:!=、<>、not in、not exists、not like 等

-- 全表扫描select * from article where id != 1 and id != 2知道id的所有取值范围,可以改为类似如下形式

-- 走索引select * from article where id in (0, 3, 4)建立覆盖索引众所周知,表数据是放在一个聚集索引上的,而建立的索引为非聚集索引,非聚集索引的叶子节点存放索引键值,以及该索引键指向的主键。一般查找的过程是从非聚集索引上找到数据的主键,然后根据该主键到聚集索引上查找记录,这个过程称为回表,不清楚的看推荐阅读。

如有下面这个sql

select uid, login_time from user where username = ? and passwd = ?可以建立(username, passwd, login_time)的联合索引,由于 login_time的值可以直接从索引中拿到,不用再回表查询,提高了查询效率

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

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

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

select uid from user where username = ? and passwd = ?可改为

select uid from user where username = ? and passwd = ? limit 1对文本建立前缀索引用邮箱登录是一个常见的问题,如果对email整个字段建立索引,会让索引变得大且慢

select username from user where email='xxx';这时我们可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率,但这样也会降低索引的区分度。索引的区分度是指,不重复的索引值和数据表的记录总数的比值。索引的区分度越高则查询效率越高,因为区分度高的索引可以让MySQL在查找时过滤掉更多的行。

因此我们选择足够长的前缀保证较高的区分度,同时又不能太长(以便节约空间)

可以进行如下实验

select count(distinct left(email, 5)) / count(*) as col5,count(distinct left(email, 6)) / count(*) as col6,count(distinct left(email, 7)) / count(*) as col7from user假设输出依次为0.0305,0.0309,0.0310查询显示当前缀长度达到7的时候,再增加前缀长度,区分度提升的幅度已经很小了,因此创建email(7)的前缀索引即可

需要注意的一点是,前缀索引不能使用覆盖索引,因为从索引中获取不到完整的数据,还得回表查询

只要列中包含有 NULL 值都将不会被包含在索引中,复合索引中只要有一列含有 NULL值,那么这一列对于此复合索引就是无效的。

因此,在数据库设计时,除非有一个很特别的原因使用 NULL 值,不然尽量不要让字段的默认值为 NULL。

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

●编号527,输入编号直达本文

●输入m获取文章目录

更多推荐《》

涵盖:程序人生、算法与数据结构、黑客技术与网络安全、大数据技术、前端开发、Java、Python、Web开发、安卓开发、iOS开发、C/C++、.NET、Linux、数据库、运维等。