本文所有实验基于MySQL5.7.21，实验将会用到Explain工具，不了解的同学可参考此文章：MySQL性能优化神器Explain详解
联合索引失效
先创建一个包含三个字段的联合索引，索引顺序如下：
由以下三张图的key_len字段我们可以得出三个索引的长度分别为：title长303，author长122，price长5.
联合索引使用时遵循最左匹配原则，如果不是从最左列开始时，整个索引失效，如果最左匹配则依次往右使用索引，直到碰到不匹配的地方之后生效之前匹配到的索引
范围查询会引起索引失效
为以上联合索引更换字段顺序为title，price，author。如果查询时包含范围查询则范围字段后面的字段失效
like查询失效
使用模糊查询时，%只有在最右方的时候才能生效
为title设置一个单独的索引
查询中含有不等于或者or则索引不生效
在索引列上做计算或函数导致失效
删除刚才创建的联合索引，为price字段创建一个单独的索引
字符串类型不加引号同样会失效
is null和 is not null
为title字段创建一个单独的索引
注意事项
在进行索引使用测试时，可能会因为测试数据太少从而MySQL会认为查询语句走全表扫描比走索引更有效，所以会自动去除索引，为避免测试结果误导可使用如下方式强制MySQL使用索引
1
|
explain select * from book FORCE index(索引名称) where price=1+1;
|
使用索引的优点
- 唯一索引或主键索引可以保证数据库表的唯一性
- 可以提高查询效率和性能
- 加速表连接的速度
- 加快排序的效率
使用索引的缺点
- 每次更新修改删除都需要维护索引、从而消耗性能
- 索引文件会占用物理空间
推荐阅读
- SpringCloud学习系列汇总
- 为什么一线大厂面试必问redis，有啥好问的？
- 多线程面试必备基础知识汇总
- Java集合源码分析汇总-JDK1.8
- Linux常用命令速查-汇总篇
- JVM系列文章汇总
博客所有文章首发于公众号《Java学习录》转载请保留
扫码关注公众号即可领取2000GJava学习资源
相关推荐
MySQL索引是数据库管理系统中用于...总的来说，理解MySQL索引的优缺点以及如何在实践中有效地使用它们，是提升数据库性能的关键。正确地创建和管理索引，可以平衡查询速度和数据更新速度，实现数据库系统的高效运行。
索引的缺点 需要占用磁盘空间，因此冗余低效的索引将占用大量的磁盘空间 降低DML性能，对于数据的任意增删改都需要调整对应的索引，甚至出现索引分裂 索引会产生相应的碎片，产生维护开销 一、explain 用法：...
8. **版本相关问题**：MySQL 的旧版本中存在一些索引失效的情况，如 IS NULL、IS NOT NULL 和不等操作符。解决方法包括转换查询条件或创建特殊索引。 三、聚集索引的生成规则 聚集索引决定了数据行的实际物理存储...
10. **索引失效因素**：全列模糊匹配（如`LIKE '%text%'`），ORDER BY或GROUP BY未使用索引列，或者使用非等值比较（如`, `>`, `BETWEEN`, `IN`等）都可能导致索引失效。 11. **MySQL内部技术**：MySQL包括查询解析...
3. 索引失效：分析可能导致索引失效的情况，如全表扫描、索引覆盖不足等。 四、事务处理 1. 事务特性：理解ACID（原子性、一致性、隔离性、持久性）原则。 2. 事务控制：掌握BEGIN, COMMIT, ROLLBACK语句，以及...
5、索引有哪些优缺点？ 6、讲一讲聚簇索引与非聚簇索引？ 7、百万级别或以上的数据如何删除 8、什么是最左前缀原则？什么是最左匹配原则 9、数据库为什么使用B+树而不是B树 10、非聚簇索引一定会回表查询吗？ 11、有...
- 在查询中使用了函数、运算符导致索引失效。 - 使用全表扫描的查询。 - 使用了OR操作符而没有同时使用索引。 15. **创建索引但未使用的情况**： - 查询条件不完全匹配索引列。 - 使用了不等操作符（<, >, !=...
- 索引类型：B-TREE、HASH、R-TREE等，它们的适用场景及优缺点。 - 唯一索引与非唯一索引的区别。 - 全文索引在全文搜索中的应用。 - 如何选择合适的列创建索引，避免全表扫描。 2. **查询优化**： - 使用...
#### 1.4 查询缓存的优缺点 - **优点**：减少了对磁盘的访问次数，提高了查询速度。 - **缺点**：增加了服务器内存的负担，对于频繁修改的数据表可能会导致大量的缓存更新操作，反而降低性能。 ### 二、MySQL 内部...
索引失效的情况包括：查询条件包含`OR`操作，未正确使用引号包裹字符串，`LIKE`通配符导致部分索引无效，非联合索引的第一个字段用于查询，索引列使用函数，对索引列进行算术运算，使用`!=`或`<>`、`NOT IN`，以及`...
- **优缺点**：查询缓存可以提高查询性能，但在数据频繁变动的场景下可能会导致缓存失效的问题。 ##### 3. 临时表 - **定义**：在执行某些复杂查询时，可能需要创建临时表来存储中间结果。 - **注意事项**：过度...
2. **索引原理**：索引是提升查询速度的关键，书中详细讲解了B-Tree、Hash、R-Tree等不同类型的索引以及它们的优缺点，如何选择合适的索引策略，以及避免索引失效的方法。 3. **查询优化**：涵盖查询分析、执行计划...
- **避免使用OR**：OR运算符可能导致索引失效，尽量改写为多个AND表达式。 - **使用EXPLAIN**：通过`EXPLAIN`分析SQL执行计划，找出性能瓶颈。 #### 三、数据的配置 - **调整缓冲池大小**：根据服务器资源合理分配...
在Java面试中，MySQL是常被讨论的话题，涵盖了SQL语句的执行顺序、索引的优缺点、如何避免索引失效、SQL优化方法、不同存储引擎的比较、事务的ACID特性以及锁机制等多个核心知识点。 1. **SQL执行顺序**： SQL语句...
4. 常见索引底层数据结构及优缺点： - **哈希表**：适合等值查询，不适合范围查询，内存效率高。 - **有序数组**：适用于静态存储，等值和范围查询快，但更新成本高。 - **搜索树**（如B+树）：平衡多路搜索树，...
- WHERE子句中的索引使用：理解索引失效的情况，如使用函数、不等值比较。 3. **查询优化**： - EXPLAIN命令：分析查询执行计划，理解查询性能瓶颈。 - 子查询优化：避免嵌套查询，使用JOIN或临时表优化。 - ...
- 索引优化：根据查询模式创建合适索引，避免索引失效。 8. **面试常见问题** - 事务的隔离级别及其影响。 - 锁的类型（行级锁、表级锁、页级锁）及应用场景。 - 存储引擎的区别，如InnoDB与MyISAM。 - 触发器...
- 索引失效的情况及解决方案。 - SQL提示的使用方法。 - 覆盖索引与回表查询的区别。 - 前缀索引的设计原则。 - 单列与联合索引的选择策略。 - 索引设计的最佳实践。 #### 三、SQL优化 SQL优化是提高MySQL...
- **B树和哈希索引**：它们的工作原理及优缺点。 - **唯一索引与非唯一索引**：使用场景和对查询性能的影响。 - **复合索引与覆盖索引**：如何创建和利用它们提高查询效率。 4. **事务处理**： - **ACID属性**...
书中会讨论不同类型的索引（如B-TREE、哈希索引、全文索引）及其适用场景，如何选择合适的索引，以及如何避免索引失效。 3. **查询优化**：深入解析SQL查询优化，包括查询语句的编写技巧、JOIN操作的优化、子查询的...