Day162，java校招面试经验

已于 2024-04-20 15:28:10 修改
阅读量978 收藏 9
点赞数 24
分类专栏： 2024年程序员学习 文章标签： java 面试 开发语言
于 2024-04-20 15:28:09 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2401_84412194/article/details/138000980
版权

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

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

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

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注Java）
img

正文

| 20 | 10 |

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

20 rows in set (0.00 sec)

查询案例

  • 实现三表的连接查询：

mysql> SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;

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

| id | card | bookid | card | phoneid | card |

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

| 2 | 13 | 8 | 13 | 3 | 13 |

| 2 | 13 | 9 | 13 | 3 | 13 |

| 2 | 13 | 12 | 13 | 3 | 13 |

| 8 | 16 | 1 | 16 | 7 | 16 |

| 10 | 16 | 1 | 16 | 7 | 16 |

| 14 | 16 | 1 | 16 | 7 | 16 |

| 8 | 16 | 18 | 16 | 7 | 16 |

| 10 | 16 | 18 | 16 | 7 | 16 |

| 14 | 16 | 18 | 16 | 7 | 16 |

| 7 | 1 | 2 | 1 | 10 | 1 |

| 7 | 1 | 11 | 1 | 10 | 1 |

| 7 | 1 | 15 | 1 | 10 | 1 |

| 13 | 18 | 7 | 18 | 12 | 18 |

| 11 | 9 | 17 | 9 | 13 | 9 |

| 18 | 9 | 17 | 9 | 13 | 9 |

| 11 | 9 | 17 | 9 | 14 | 9 |

| 18 | 9 | 17 | 9 | 14 | 9 |

| 6 | 3 | 4 | 3 | 18 | 3 |

| 4 | 17 | 3 | 17 | NULL | NULL |

| 9 | 17 | 3 | 17 | NULL | NULL |

| 12 | 17 | 3 | 17 | NULL | NULL |

| 1 | 12 | 6 | 12 | NULL | NULL |

| 3 | 12 | 6 | 12 | NULL | NULL |

| 5 | 11 | NULL | NULL | NULL | NULL |

| 15 | 7 | NULL | NULL | NULL | NULL |

| 16 | 8 | NULL | NULL | NULL | NULL |

| 17 | 19 | NULL | NULL | NULL | NULL |

| 19 | 6 | NULL | NULL | NULL | NULL |

| 20 | 5 | NULL | NULL | NULL | NULL |

| 21 | 6 | NULL | NULL | NULL | NULL |

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

30 rows in set (0.00 sec)

  • 使用 explain 分析 SQL 指令：

mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 21 | NULL |

| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) |

| 1 | SIMPLE | phone | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) |

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

3 rows in set (0.00 sec)

  • 结论：

  • type 有All ，rows 为表数据总行数，说明 class、 book 和 phone 表都进行了全表检索

  • Extra 中 Using join buffer ，表明连接过程中使用了 join 缓冲区

创建索引

  • 创建索引的 SQL 语句

ALTER TABLE book ADD INDEX Y (card);

ALTER TABLE phone ADD INDEX Z (card);

  • 进行 LEFT JOIN ，永远都在右表的字段上建立索引

mysql> ALTER TABLE book ADD INDEX Y (card);

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW INDEX FROM book;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| book | 0 | PRIMARY | 1 | bookid | A | 20 | NULL | NULL | | BTREE | | |

| book | 1 | Y | 1 | card | A | 20 | NULL | NULL | | BTREE | | |

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

2 rows in set (0.00 sec)

mysql> ALTER TABLE phone ADD INDEX Z (card);

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW INDEX FROM phone;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| phone | 0 | PRIMARY | 1 | phoneid | A | 20 | NULL | NULL | | BTREE | | |

| phone | 1 | Z | 1 | card | A | 20 | NULL | NULL | | BTREE | | |

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

2 rows in set (0.00 sec)

  • 执行查询：后2行的type都是ref，且总rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。

mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card = phone.card;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 21 | NULL |

| 1 | SIMPLE | book | ref | Y | Y | 4 | db01.class.card | 1 | Using index |

| 1 | SIMPLE | phone | ref | Z | Z | 4 | db01.book.card | 1 | Using index |

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

3 rows in set (0.00 sec)

Join 语句优化的结论

小结果为遍历全表，大结果建立索引；

如左外连接，左表为小表，右表为大表

将 left join 看作是两层嵌套 for 循环

  1. 尽可能减少Join语句中的NestedLoop的循环总次数；

  2. 永远用小结果集驱动大的结果集（在大结果集中建立索引，在小结果集中遍历全表）；

  3. 优先优化NestedLoop的内层循环；

  4. 保证Join语句中被驱动表上Join条件字段已经被索引；

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

  1. 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置；

我的理解

  1. 使用小表驱动大表，这就相当于外层 for 循环的次数少，内层 for 循环的次数多

  2. 然后我们在大表中建立索引，这样内层 for 循环的效率明显提高

  3. 综上，使用小表驱动大表，在大表中建立了索引

.

6、索引失效

索引失效（应该避免）

创建表

  • 建表 SQL

CREATE TABLE staffs(

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(24)NOT NULL DEFAULT’’ COMMENT’姓名’,

age INT NOT NULL DEFAULT 0 COMMENT’年龄’,

pos VARCHAR(20) NOT NULL DEFAULT’’ COMMENT’职位’,

add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT’入职时间’

)CHARSET utf8 COMMENT’员工记录表’;

INSERT INTO staffs(name,age,pos,add_time) VALUES(‘z3’,22,‘manager’,NOW());

INSERT INTO staffs(name,age,pos,add_time) VALUES(‘July’,23,‘dev’,NOW());

INSERT INTO staffs(name,age,pos,add_time) VALUES(‘2000’,23,‘dev’,NOW());

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name,age,pos);

  • staffs 表中的测试数据

mysql> select * from staffs;

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

| id | name | age | pos | add_time |

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

| 1 | z3 | 22 | manager | 2020-08-04 14:42:33 |

| 2 | July | 23 | dev | 2020-08-04 14:42:33 |

| 3 | 2000 | 23 | dev | 2020-08-04 14:42:33 |

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

3 rows in set (0.00 sec)

  • staffs 表中的复合索引：name、age、pos

mysql> SHOW INDEX FROM staffs;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

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

| staffs | 1 | index_staffs_nameAgePos | 1 | name | 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)

6.1、索引失效准则

索引失效判断准则

  1. 全值匹配我最爱

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

  • 带头大哥不能死，中间兄弟不能断，末尾兄弟随便卖
  1. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
  • 索引列上少计算
  1. 存储引擎不能使用索引中范围条件右边的列
  • 范围之后全失效
  1. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
  • **按需写查询列，减少select ***

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

  2. is null，is not null 也无法使用索引（早期版本不能走索引，后续版本应该优化过，可以走索引）

  3. like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描操作

  • 百分like加右边 — abc%
  1. 字符串不加单引号索引失效
  • MySql会自动隐式调用类型转换，就出现了上面【3】的情况
  1. 少用or，用它连接时会索引失效

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

  • 只有带头大哥 name 时

  • key = index_staffs_nameAgePos 表明索引生效

  • ref = const ：这个常量就是查询时的 ‘July’ 字符串常量

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | const,const | 1 | Using index condition |

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

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’;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using index condition |

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

1 row in set (0.00 sec)

  • 带头大哥 name 挂了

  • key = NULL 说明索引失效

  • ref = null 表示 ref 也失效

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |

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

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’;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |

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

1 row in set (0.00 sec)

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

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

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |

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

1 row in set (0.00 sec)

  • 对带头大哥 name 进行操作：使用 LEFT 函数截取子串

  • key = NULL 表明索引生效

  • type = ALL 表明进行了全表扫描

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |

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

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’;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using index condition |

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

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’;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | Using index condition |

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

1 row in set (0.00 sec)

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

  • SELECT * 的写法

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | Using index condition |

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

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’;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using where; Using index |

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

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’;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using where; Using index |

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

1 row in set (0.00 sec)

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

  • 在使用 != 会 <> 时会导致索引失效：

  • key = null 表示索引失效

  • rows = 3 表示进行了全表扫描

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |

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

1 row in set (0.00 sec)

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |

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

1 row in set (0.00 sec)

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |

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

1 row in set (0.00 sec)

like % 写最右

  • staffs 表的索引关系

mysql> SHOW INDEX from staffs;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

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

| staffs | 1 | index_staffs_nameAgePos | 1 | name | 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’;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |

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

1 row in set (0.00 sec)

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |

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

1 row in set (0.00 sec)

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

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | NULL | 1 | Using index condition |

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

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;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| 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%’;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index |

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

1 row in set (0.00 sec)

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index |

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

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%’;

12

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where |

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

1 row in set (0.00 sec)

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where |

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

1 row in set (0.00 sec)

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

  • 正常操作，索引没有失效

mysql> SHOW INDEX FROM staffs;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

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

| staffs | 1 | index_staffs_nameAgePos | 1 | name | 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’;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |

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

1 row in set (0.00 sec)

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

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |

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

1 row in set (0.00 sec)

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

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

mysql> SHOW INDEX FROM staffs;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

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

| staffs | 1 | index_staffs_nameAgePos | 1 | name | 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’;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |

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

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;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| 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’;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using index condition |

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

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’;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using index condition |

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

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’;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93 | NULL | 1 | Using index condition |

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

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’;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 124 | NULL | 1 | Using index condition |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where; Using filesort |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where; Using filesort |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where; Using temporary; Using filesort |

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

1 row in set (0.01 sec)

  • 结论：

  • group by 、order by 排序法则几乎一致，他们基本上都需要进行排序，分组之前必排序，但凡使用不当，会有临时表产生

  • 定值为常量、范围之后失效，最终看排序的顺序

.

6.3、索引失效总结

一般性建议

  1. 对于单键索引，尽量选择针对当前query过滤性更好的索引

  2. 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠左越好。

  3. 在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引

  4. 尽可能通过分析统计信息和调整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 ，请记住此参数的值，后面有用

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 93 | const,const,const | 1 | Using index condition |

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

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’;

总结

谈到面试，其实说白了就是刷题刷题刷题，天天作死的刷。。。。。

为了准备这个“金三银四”的春招，狂刷一个月的题，狂补超多的漏洞知识，像这次美团面试问的算法、数据库、Redis、设计模式等这些题目都是我刷到过的

并且我也将自己刷的题全部整理成了PDF或者Word文档（含详细答案解析）

我的美团offer凉凉了？开发工程师（Java岗）三面结束等通知...

66个Java面试知识点

架构专题（MySQL，Java，Redis，线程，并发，设计模式，Nginx，Linux，框架，微服务等）+大厂面试题详解（百度，阿里，腾讯，华为，迅雷，网易，中兴，北京中软等）

我的美团offer凉凉了？开发工程师（Java岗）三面结束等通知...

算法刷题（PDF）

我的美团offer凉凉了？开发工程师（Java岗）三面结束等通知...

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）
img

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

字段在索引字段顺序中，位置越靠左越好。

  1. 在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引

  2. 尽可能通过分析统计信息和调整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 ，请记住此参数的值，后面有用

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 93 | const,const,const | 1 | Using index condition |

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

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’;

总结

谈到面试，其实说白了就是刷题刷题刷题，天天作死的刷。。。。。

为了准备这个“金三银四”的春招，狂刷一个月的题，狂补超多的漏洞知识，像这次美团面试问的算法、数据库、Redis、设计模式等这些题目都是我刷到过的

并且我也将自己刷的题全部整理成了PDF或者Word文档（含详细答案解析）

[外链图片转存中…(img-8d9y0IZT-1713598071650)]

66个Java面试知识点

架构专题（MySQL，Java，Redis，线程，并发，设计模式，Nginx，Linux，框架，微服务等）+大厂面试题详解（百度，阿里，腾讯，华为，迅雷，网易，中兴，北京中软等）

[外链图片转存中…(img-xuZjKrug-1713598071650)]

算法刷题（PDF）

[外链图片转存中…(img-BD9gLDsx-1713598071651)]

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Java）
[外链图片转存中…(img-wP1eQrWx-1713598071651)]

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

2401_84412194
关注
  • 24
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值