Day162，京东物流java面试几轮

最新推荐文章于 2024-06-21 17:14:47 发布

Java有多久学会

最新推荐文章于 2024-06-21 17:14:47 发布

阅读量887

点赞数 18

分类专栏： Java程序员文章标签： java 面试 adb

本文链接：https://blog.csdn.net/m0_60635609/article/details/136826693

版权

Java程序员专栏收录该内容

9 篇文章 0 订阅

订阅专栏

索引失效判断准则

全值匹配我最爱
最佳左前缀法则：如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

带头大哥不能死，中间兄弟不能断，末尾兄弟随便卖

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

索引列上少计算

存储引擎不能使用索引中范围条件右边的列

范围之后全失效

尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

**按需写查询列，减少select ***

mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
is null，is not null 也无法使用索引（早期版本不能走索引，后续版本应该优化过，可以走索引）
like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描操作

百分like加右边 — abc%

字符串不加单引号索引失效

MySql会自动隐式调用类型转换，就出现了上面【3】的情况

少用or，用它连接时会索引失效

最佳左匹配法则：带头大哥不能死，中间兄弟不能断

只有带头大哥 name 时
key = index_staffs_nameAgePos 表明索引生效
ref = const ：这个常量就是查询时的 ‘July’ 字符串常量

mysql> EXPLAIN SELECT * FROM staffs WHERE name = ‘July’;

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±----------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±----------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±----------------------+

1 row in set (0.00 sec)

带头大哥 name 带上小弟 age
key = index_staffs_nameAgePos 表明索引生效
ref = const,const：两个常量分别为 ‘July’ 和 23

mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July’AND age = 23;

±—±------------±-------±-----±------------------------±------------------------±--------±------------±-----±----------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------------±-----±----------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------------±-----±----------------------+

1 row in set (0.00 sec)

带头大哥 name 带上小弟 age ，小弟 age 带上小小弟 pos
key = index_staffs_nameAgePos 表明索引生效
ref = const,const,const ：三个常量分别为 ‘July’、23 和 ‘dev’

mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July’AND age = 23 AND pos = ‘dev’;

±—±------------±-------±-----±------------------------±------------------------±--------±------------------±-----±----------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------------------±-----±----------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------------------±-----±----------------------+

1 row in set (0.00 sec)

带头大哥 name 挂了
key = NULL 说明索引失效
ref = null 表示 ref 也失效

mysql> EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = ‘dev’;

±—±------------±-------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±--------------±-----±--------±-----±-----±------------+

1 row in set (0.00 sec)

带头大哥 name 没挂，小弟 age 跑了
key = index_staffs_nameAgePos 说明索引没有失效
ref = const 表明只使用了一个常量，即第二个常量（pos = ‘dev’）没有生效

mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July’AND pos = ‘dev’;

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±----------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±----------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±----------------------+

1 row in set (0.00 sec)

在索引列上进行计算，会导致索引失效，进而转向全表扫描

不对带头大哥 name 进行任何操作：key = index_staffs_nameAgePos 表明索引生效

mysql> EXPLAIN SELECT * FROM staffs WHERE name = ‘July’;

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±----------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±----------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±----------------------+

1 row in set (0.00 sec)

对带头大哥 name 进行操作：使用 LEFT 函数截取子串
key = NULL 表明索引生效
type = ALL 表明进行了全表扫描

mysql> EXPLAIN SELECT * FROM staffs WHERE LEFT(name,4) = ‘July’;

±—±------------±-------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±--------------±-----±--------±-----±-----±------------+

1 row in set (0.00 sec)

范围之后全失效

精确匹配
type = ref 表示非唯一索引扫描，SQL 语句将返回匹配某个单独值的所有行。
key_len = 140 表明表示索引中使用的字节数

mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July’AND age = 23 AND pos = ‘dev’;

±—±------------±-------±-----±------------------------±------------------------±--------±------------------±-----±----------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------------------±-----±----------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------------------±-----±----------------------+

1 row in set (0.00 sec)

将 age 改为范围匹配
type = range 表示范围扫描
key = index_staffs_nameAgePos 表示索引并没有失效
key_len = 78 ，ref = NULL 均表明范围搜索使其后面的索引均失效

mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July’AND age > 23 AND pos = ‘dev’;

±—±------------±-------±------±------------------------±------------------------±--------±-----±-----±----------------------+

±—±------------±-------±------±------------------------±------------------------±--------±-----±-----±----------------------+

±—±------------±-------±------±------------------------±------------------------±--------±-----±-----±----------------------+

1 row in set (0.00 sec)

尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少 select \*

SELECT * 的写法

mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July’AND age > 23 AND pos = ‘dev’;

±—±------------±-------±------±------------------------±------------------------±--------±-----±-----±----------------------+

±—±------------±-------±------±------------------------±------------------------±--------±-----±-----±----------------------+

±—±------------±-------±------±------------------------±------------------------±--------±-----±-----±----------------------+

1 row in set (0.00 sec)

覆盖索引的写法：Extra = Using where; Using index ，Using index 表示使用索引列进行查询，将大大提高查询的效率

mysql> EXPLAIN SELECT name, age, pos FROM staffs WHERE name = 'July’AND age = 23 AND pos = ‘dev’;

±—±------------±-------±-----±------------------------±------------------------±--------±------------------±-----±-------------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------------------±-----±-------------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------------------±-----±-------------------------+

1 row in set (0.00 sec)

覆盖索引中包含 range 条件：type = ref 并且 Extra = Using where; Using index ，虽然在查询条件中使用了范围搜索，但是由于我们只需要查找索引列，所以无需进行全表扫描

mysql> EXPLAIN SELECT name, age, pos FROM staffs WHERE name = 'July’AND age > 23 AND pos = ‘dev’;

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±-------------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±-------------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±-------------------------+

1 row in set (0.00 sec)

mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描

在使用 != 会 <> 时会导致索引失效：
key = null 表示索引失效
rows = 3 表示进行了全表扫描

mysql> EXPLAIN SELECT * FROM staffs WHERE name != ‘July’;

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE name <> ‘July’;

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

1 row in set (0.00 sec)

is null，is not null 也无法使用索引

is null，is not null 会导致索引失效：key = null 表示索引失效

ysql> EXPLAIN SELECT * FROM staffs WHERE name is null;

±—±------------±------±-----±--------------±-----±--------±-----±-----±-----------------+

±—±------------±------±-----±--------------±-----±--------±-----±-----±-----------------+

±—±------------±------±-----±--------------±-----±--------±-----±-----±-----------------+

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE name is not null;

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

1 row in set (0.00 sec)

like % 写最右

staffs 表的索引关系

mysql> SHOW INDEX from staffs;

±-------±-----------±------------------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

±-------±-----------±------------------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |

| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |

| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |

±-------±-----------±------------------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

4 rows in set (0.00 sec)

like % 写在左边的情况
type = All ，rows = 3 表示进行了全表扫描
key = null 表示索引失效

mysql> EXPLAIN SELECT * FROM staffs WHERE name like ‘%July’;

±—±------------±-------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±--------------±-----±--------±-----±-----±------------+

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE name like ‘%July%’;

±—±------------±-------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±--------------±-----±--------±-----±-----±------------+

1 row in set (0.00 sec)

like % 写在右边的情况：key = index_staffs_nameAgePos 表示索引未失效

mysql> EXPLAIN SELECT * FROM staffs WHERE name like ‘July%’;

±—±------------±-------±------±------------------------±------------------------±--------±-----±-----±----------------------+

±—±------------±-------±------±------------------------±------------------------±--------±-----±-----±----------------------+

±—±------------±-------±------±------------------------±------------------------±--------±-----±-----±----------------------+

1 row in set (0.00 sec)

解决【like ‘%str%’ 】索引失效的问题：覆盖索引

创建表

建表 SQL

CREATE TABLE tbl_user(

id INT(11) NOT NULL AUTO_INCREMENT,

name VARCHAR(20) DEFAULT NULL,

ageINT(11) DEFAULT NULL,

email VARCHAR(20) DEFAULT NULL,

PRIMARY KEY(id)

)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(name,age,email)VALUES(‘1aa1’,21,‘a@163.com’);

INSERT INTO tbl_user(name,age,email)VALUES(‘2bb2’,23,‘b@163.com’);

INSERT INTO tbl_user(name,age,email)VALUES(‘3cc3’,24,‘c@163.com’);

INSERT INTO tbl_user(name,age,email)VALUES(‘4dd4’,26,‘d@163.com’);

tbl_user 表中的测试数据

mysql> select * from tbl_user;

±—±-----±-----±----------+

| id | name | age | email |

±—±-----±-----±----------+

| 1 | 1aa1 | 21 | a@163.com |

| 2 | 2bb2 | 23 | b@163.com |

| 3 | 3cc3 | 24 | c@163.com |

| 4 | 4dd4 | 26 | d@163.com |

±—±-----±-----±----------+

4 rows in set (0.00 sec)

创建索引

创建索引的 SQL 指令

CREATE INDEX idx_user_nameAge ON tbl_user(name, age);

在 tbl_user 表的 name 字段和 age 字段创建联合索引

mysql> CREATE INDEX idx_user_nameAge ON tbl_user(name, age);

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW INDEX FROM tbl_user;

±---------±-----------±-----------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

±---------±-----------±-----------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

| tbl_user | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |

| tbl_user | 1 | idx_user_nameAge | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |

| tbl_user | 1 | idx_user_nameAge | 2 | age | A | 4 | NULL | NULL | YES | BTREE | | |

±---------±-----------±-----------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

3 rows in set (0.00 sec)

测试覆盖索引

如下 SQL 的索引均不会失效：
只要查询的字段能和覆盖索引扯得上关系，并且没有多余字段，覆盖索引就不会失效
但我就想不通了，id 扯得上啥关系。。。

EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE ‘%aa%’;

EXPLAIN SELECT name FROM tbl_user WHERE NAME LIKE ‘%aa%’;

EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE ‘%aa%’;

EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE ‘%aa%’;

EXPLAIN SELECT id, name FROM tbl_user WHERE NAME LIKE ‘%aa%’;

EXPLAIN SELECT id, age FROM tbl_user WHERE NAME LIKE ‘%aa%’;

EXPLAIN SELECT id, name, age FROM tbl_user WHERE NAME LIKE ‘%aa%’;

mysql> EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE ‘%aa%’;

±—±------------±---------±------±--------------±-----------------±--------±-----±-----±-------------------------+

±—±------------±---------±------±--------------±-----------------±--------±-----±-----±-------------------------+

±—±------------±---------±------±--------------±-----------------±--------±-----±-----±-------------------------+

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE ‘%aa%’;

±—±------------±---------±------±--------------±-----------------±--------±-----±-----±-------------------------+

±—±------------±---------±------±--------------±-----------------±--------±-----±-----±-------------------------+

±—±------------±---------±------±--------------±-----------------±--------±-----±-----±-------------------------+

1 row in set (0.00 sec)

如下 SQL 的索引均会失效：但凡有多余字段，覆盖索引就会失效

EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE ‘%aa%’;

EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE ‘%aa%’;

mysql> EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE ‘%aa%’;

±—±------------±---------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±---------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±---------±-----±--------------±-----±--------±-----±-----±------------+

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE ‘%aa%’;

±—±------------±---------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±---------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±---------±-----±--------------±-----±--------±-----±-----±------------+

1 row in set (0.00 sec)

字符串不加单引号索引失效

正常操作，索引没有失效

mysql> SHOW INDEX FROM staffs;

±-------±-----------±------------------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

±-------±-----------±------------------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |

| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |

| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |

±-------±-----------±------------------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

4 rows in set (0.00 sec)

mysql> explain select * from staffs where name=‘2000’;

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±----------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±----------------------+

±—±------------±-------±-----±------------------------±------------------------±--------±------±-----±----------------------+

1 row in set (0.00 sec)

如果字符串忘记写 ‘’ ，那么 mysql 会为我们进行隐式的类型转换，但凡进行了类型转换，索引都会失效

mysql> explain select * from staffs where name=2000;

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

1 row in set (0.00 sec)

少用or，用它连接时会索引失效

使用 or 连接，会导致索引失效

mysql> SHOW INDEX FROM staffs;

±-------±-----------±------------------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

±-------±-----------±------------------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |

| staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |

| staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |

±-------±-----------±------------------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

4 rows in set (0.00 sec)

mysql> explain select * from staffs where name=‘z3’ or name = ‘July’;

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

±—±------------±-------±-----±------------------------±-----±--------±-----±-----±------------+

1 row in set (0.00 sec)

小练习总结

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AOYmIkjp-1610526935978)(C:\Users\PePe\AppData\Roaming\Typora\typora-user-images\image-20210113150141190.png)]$

a是带头大哥，√
a带头大哥，b中间小弟，√
a带头大哥，b中间小弟，c索引最后，√
a带头大哥没了，直接不能引用索引，且使用了or，×
a带头大哥，b中间小弟没了，c最后小弟，前面a用了索引，c没用，因为中间b断了
a带头大哥，b为range范围，a能用，b能用，c最后小弟没用，b断了
a带头大哥，b中间小弟用的like右%，like也相当于range范围，原理跟上面条一样，c最后小弟断了，所以a能用，b能用，c不能用

6.2、索引优化面试题

索引优化面试题

创建表

建表 SQL

create table test03(

id int primary key not null auto_increment,

c1 char(10),

c2 char(10),

c3 char(10),

c4 char(10),

c5 char(10)

);

insert into test03(c1,c2,c3,c4,c5) values (‘a1’,‘a2’,‘a3’,‘a4’,‘a5’);

insert into test03(c1,c2,c3,c4,c5) values (‘b1’,‘b2’,‘b3’,‘b4’,‘b5’);

insert into test03(c1,c2,c3,c4,c5) values (‘c1’,‘c2’,‘c3’,‘c4’,‘c5’);

insert into test03(c1,c2,c3,c4,c5) values (‘d1’,‘d2’,‘d3’,‘d4’,‘d5’);

insert into test03(c1,c2,c3,c4,c5) values (‘e1’,‘e2’,‘e3’,‘e4’,‘e5’);

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

test03 表中的测试数据

mysql> select * from test03;

±—±-----±-----±-----±-----±-----+

| id | c1 | c2 | c3 | c4 | c5 |

±—±-----±-----±-----±-----±-----+

| 1 | a1 | a2 | a3 | a4 | a5 |

| 2 | b1 | b2 | b3 | b4 | b5 |

| 3 | c1 | c2 | c3 | c4 | c5 |

| 4 | d1 | d2 | d3 | d4 | d5 |

| 5 | e1 | e2 | e3 | e4 | e5 |

±—±-----±-----±-----±-----±-----+

5 rows in set (0.00 sec)

test03 表中的索引

mysql> SHOW INDEX FROM test03;

±-------±-----------±-----------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

±-------±-----------±-----------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

| test03 | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | |

| test03 | 1 | idx_test03_c1234 | 1 | c1 | A | 5 | NULL | NULL | YES | BTREE | | |

| test03 | 1 | idx_test03_c1234 | 2 | c2 | A | 5 | NULL | NULL | YES | BTREE | | |

| test03 | 1 | idx_test03_c1234 | 3 | c3 | A | 5 | NULL | NULL | YES | BTREE | | |

| test03 | 1 | idx_test03_c1234 | 4 | c4 | A | 5 | NULL | NULL | YES | BTREE | | |

±-------±-----------±-----------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

5 rows in set (0.00 sec)

问题：我们创建了复合索引idx_test03_c1234，根据以下SQL分析下索引使用情况？

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';
即全值匹配

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2=‘a2’ AND c3=‘a3’ AND c4=‘a4’;

±—±------------±-------±-----±-----------------±-----------------±--------±------------------------±-----±----------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------------------±-----±----------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------------------±-----±----------------------+

1 row in set (0.00 sec)

EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';
mysql 优化器进行了优化，所以我们的索引都生效了

mysql> EXPLAIN SELECT * FROM test03 WHERE c4=‘a4’ AND c3=‘a3’ AND c2=‘a2’ AND c1=‘a1’;

±—±------------±-------±-----±-----------------±-----------------±--------±------------------------±-----±----------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------------------±-----±----------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------------------±-----±----------------------+

1 row in set (0.00 sec)

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4';
c3 列使用了索引进行排序，并没有进行查找，导致 c4 无法用索引进行查找【范围之后全失效】

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2=‘a2’ AND c3>‘a3’ AND c4=‘a4’;

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

1 row in set (0.00 sec)

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3';
mysql 优化器进行了优化调整顺序，所以我们的索引都生效了，在 c4 时进行了范围搜索

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2=‘a2’ AND c4>‘a4’ AND c3=‘a3’;

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

1 row in set (0.00 sec)

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3;
c3 列将索引用于排序，而不是查找，c4 列没有用到索引

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2=‘a2’ AND c4=‘a4’ ORDER BY c3;

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

1 row in set (0.00 sec)

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3;
那不就和上面一样的嘛~~~，c4 列都没有用到索引

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2=‘a2’ ORDER BY c3;

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

1 row in set (0.00 sec)

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4;
妈耶，因为索引建立的顺序和使用的顺序不一致，导致 mysql 动用了文件排序
看到 Using filesort 就要知道：此句 SQL 必须优化

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2=‘a2’ ORDER BY c4;

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±---------------------------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±---------------------------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±---------------------------------------------------+

1 row in set (0.00 sec)

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3;
只用 c1 一个字段索引，但是c2、c3用于排序，无filesort
难道因为排序的时候，c2 紧跟在 c1 之后，按照索引顺序来，所以就不用 filesort 吗？

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c5=‘a5’ ORDER BY c2, c3;

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±-----------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±-----------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±-----------------------------------+

1 row in set (0.00 sec)

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2;
出现了filesort，我们建的索引是1234，它没有按照索引顺序来，32颠倒了

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c5=‘a5’ ORDER BY c3, c2;

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±---------------------------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±---------------------------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±---------------------------------------------------+

1 row in set (0.00 sec)

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3;
用c1、c2两个字段索引，但是c2、c3用于排序，无filesort

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2=‘a2’ ORDER BY c2, c3;

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

1 row in set (0.00 sec)

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3;
和 c5 这个坑爹货没啥关系

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2=‘a2’ AND c5=‘a5’ ORDER BY c2, c3;

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

1 row in set (0.00 sec)

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2;
注意查询条件 c2=‘a2’ ，我都把 c2 查出来了（c2 为常量），我还给它排序作甚，所以没有产生 filesort

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2=‘a2’ AND c5=‘a5’ ORDER BY c3, c2;

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------±-----±-----------------------------------+

1 row in set (0.00 sec)

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3;
顺序为 1 2 3 ，没有产生文件排序

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c4=‘a4’ GROUP BY c2, c3;

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±-----------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±-----------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±-----------------------------------+

1 row in set (0.00 sec)

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2;
group by 表面上叫分组，分组之前必排序，group by 和 order by 在索引上的问题基本是一样的
Using temporary、Using filesort 两个都有，我只能说是灭绝师太

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c4=‘a4’ GROUP BY c3, c2;

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±--------------------------------------------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±--------------------------------------------------------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±--------------------------------------------------------------------+

1 row in set (0.01 sec)

结论：
group by 、order by 排序法则几乎一致，他们基本上都需要进行排序，分组之前必排序，但凡使用不当，会有临时表产生
定值为常量、范围之后失效，最终看排序的顺序

6.3、索引失效总结

一般性建议

对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠左越好。
在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HLTXDdzt-1610526935981)(C:\Users\PePe\AppData\Roaming\Typora\typora-user-images\image-20210113162134157.png)]$

索引优化的总结

like 后面以常量开头，比如 like ‘kk%’ 和 like ‘k%kk%’ ，可以理解为就是常量

like SQL 实测

= ‘kk’ ：key_len = 93 ，请记住此参数的值，后面有用

----±------------±-------±-----±-----------------±-----------------±--------±------------------±-----±----------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------------±-----±----------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------------------±-----±----------------------+

1 row in set (0.00 sec)

like ‘kk%’：
key_len = 93 ，和上面一样，说明 c1 c2 c3 都用到了索引
type = range 表明这是一个范围搜索

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2 like ‘kk%’ AND c3=‘a3’;

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

1 row in set (0.00 sec)

like ‘%kk’ 和 like ‘%kk%’ ：key_len = 31 ，表示只有 c1 用到了索引

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2 like ‘%kk’ AND c3=‘a3’;

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±----------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±----------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±----------------------+

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2 like ‘%kk%’ AND c3=‘a3’;

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±----------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±----------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±----------------------+

1 row in set (0.00 sec)

like ‘k%kk%’ ：key_len = 93 ，表示 c1 c2 c3 都用到了索引

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2 like ‘k%kk%’ AND c3=‘a3’;

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

1 row in set (0.00 sec)

小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频

如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）

–±------------±-------±-----±-----------------±-----------------±--------±------±-----±----------------------+

±—±------------±-------±-----±-----------------±-----------------±--------±------±-----±----------------------+

1 row in set (0.00 sec)

like ‘k%kk%’ ：key_len = 93 ，表示 c1 c2 c3 都用到了索引

mysql> EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2 like ‘k%kk%’ AND c3=‘a3’;

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

±—±------------±-------±------±-----------------±-----------------±--------±-----±-----±----------------------+

1 row in set (0.00 sec)

小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
[外链图片转存中…(img-EVUTtXOT-1710782411939)]
[外链图片转存中…(img-qOzTysS6-1710782411939)]
[外链图片转存中…(img-4N0GasIK-1710782411940)]

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频

如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）
[外链图片转存中…(img-OOXFMZku-1710782411940)]

Java有多久学会

关注

18
点赞
踩
15

收藏

觉得还不错? 一键收藏
0
评论
Day162，京东物流java面试几轮

一般性建议对于单键索引，尽量选择针对当前query过滤性更好的索引在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠左越好。在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的索引优化的总结like 后面以常量开头，比如 like ‘kk%’ 和 like ‘k%kk%’ ，可以理解为就是常量like SQL 实测。
复制链接

扫一扫