数据库索引和MySQL优化-优快云博客

本文链接：https://blog.youkuaiyun.com/wdywxwxld/article/details/126385325

本文详细阐述了MySQL索引的基础概念，包括聚簇和非聚簇索引的区别，以及索引的优劣势和适用场景。重点讲解了何时建立、何时避免索引，以及索引失效的常见情况。此外，提供了优化查询的技巧，如避免子查询和函数索引等，帮助开发者提升数据库性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。简而言之，数据库索引是排好序的数据结构，为了提高数据的查询效率。

在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

MySQL常用的索引就是B+树。

索引分为聚簇索引和非聚簇索引两种。

聚簇索引是按照数据存放的物理位置为顺序的，能提高多行检索的速度。
注意：一个表中只能有一个聚集索引。

非聚簇索引不对数据进⾏物理排序，对于单行的检索很快。
注意：一个表中可以有多个非聚集索引。

单值索引（最通用、最简单）：一个索引只包含单个列，一个表中可以有多个单值索引

唯一索引：索引列的值必须唯一，可为空

复合索引(又叫联合索引，功能最强大)：一个索引包括多个列

1）提高数据检索效率，降低磁盘IO成本
2）通过对数据的排序，降低排序成本

1）索引虽提高了查询效率，但同时降低了更新、修改、删除的效率，因为MySQL不仅要保存数据，还要维护数据和索引的关系。

2）需要成本去维护索引。一个性能良好的索引需要不断的去尝试，以找到最优解。

1）主键自动建立唯一索引
2）频繁作为查询条件的字段（where后面的字段）
3）查询中与其他表关联的字段（各种join on后面的字段）
4）单值/复合索引选择？（高并发下倾向选择复合索引）
5）查询中排序的字段
6）查询中统计或分组的字段

1）表数据太少
2）频繁更新的字段
3）where后面用不到的字段

1）like以通配符开头(‘%abc’)会导致索引失效，违反最左前缀法则
最左前缀法则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

2）在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描

3）存储引擎不能使用索引中范围条件右边的列，举例：select id，name from student where id > 50 and name = ‘张三’，会导致name索引失效

4）尽量使用覆盖索引，不要select *

5）MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描，理由也很简单，B+Tree叶子节点用指针相连且是排好序的，这种数据结构只能解决有序的定值查询，像不等于这种无法利用索引查询。

6）IS NULL、IS NOT NULL无法使用索引，理由同上

7）字符串不加单引号索引失效
隐式转换–>函数操作

8）用or连接时会导致索引失效

因为这样能降低数据库开销，对于InnoDB来说，如果不手动建主键索引，MySQL底层依然会帮我们创建一个聚集索引来维护整张表的所有数据，因为B+Tree必须依靠索引才能建立。

因为比较两个int型更具有优势，字符串需要逐位的去比较。

因为如果主键已经插入了1、2、3、4、6、7，这时候我们插入了5，MySQL在维护索引的时候就会打破原有链表顺序，导致链表节点分裂重排，从而消耗性能。而使用自增主键就不会产生这种问题。

保持一致性，当数据库表进行DML操作时，同一行记录的页地址会发生改变，因非主键索引保存的是主键的值，无需进行更改。同时还可以节省存储空间，因为Innodb数据本身就已经汇聚到主键索引所在的B+树上了，如果普通索引还继续再保存一份数据，就会导致有多少索引就要存多少份数据。

1、选择最合适的字段属性
2、尽量把字段设置为NOT NULL
3、使用连接（JOIN）来代替子查询（Sub-Queries）
4、使用联合（UNION）来代替手动创建的临时表
5、使用事务
6、使用外键
7、锁定表
8、使用索引
9、优化查询语句