索引通过减少数据扫描量、利用排序结果集、覆盖索引和优化连接操作提升查询性能。1. 减少数据扫描量是核心原理，通过b树或哈希表结构快速定位目标行；2. 排序结果集允许数据库直接使用已排序的索引避免额外排序；3. 覆盖索引可直接从索引获取所有所需字段，省去回表查询；4. 连接优化通过索引加速多表连接中的匹配过程。选择索引列应考虑where子句常用列、高区分度列、常用于排序的列，并避免小表和过度索引。索引失效常见原因包括对列使用函数、隐式类型转换、not/操作符、like以%开头、未遵循最左前缀原则以及优化器选择全表扫描。可通过explain命令查看sql是否使用索引及执行计划详情。
索引，简单来说，就像一本书的目录。没有目录，你要找某个章节，只能一页一页翻；有了目录，直接定位到页码，效率自然大大提高。
索引在SQL中主要通过减少数据库需要扫描的数据量来提高查询速度，但同时也会增加写入操作的成本。
索引INDEX提升查询性能的4个原理：
减少数据扫描量： 这是最核心的。索引通过创建数据结构的副本（通常是B树或哈希表），允许数据库引擎快速定位到包含目标值的行，而无需扫描整个表。想象一下，在一本500页的书里找一个词，没索引的话，你得从头翻到尾。有了索引，直接根据词在索引里的位置，找到对应的页码。
排序结果集： 有些查询需要排序，比如
ORDER BY。如果索引已经按照排序字段排好序，数据库可以直接使用索引的排序结果，避免额外的排序操作。这就像你的书架，如果你按照作者姓名排好序，找特定作者的书就非常快。覆盖索引： 如果一个索引包含了查询所需的所有字段（包括
WHERE子句和SELECT子句中的字段），那么数据库可以直接从索引中获取数据，而不需要回表查询。回表查询是指，先通过索引找到行的位置，然后再去表中读取完整的行数据。覆盖索引避免了这一步，性能提升非常明显。连接优化： 在多表连接查询中，索引可以加速连接操作。数据库可以使用索引快速找到匹配的行，避免全表扫描。这有点像用电话号码簿找人，如果电话号码簿按照姓名排序，找到特定姓名的人的电话号码就很快。
如何选择合适的索引列？
选择合适的索引列是个技术活，没有绝对的公式，需要根据实际情况权衡。一般来说，以下几点可以作为参考：
-
WHERE子句中常用的列： 这是最基本的原则。如果某个列经常出现在
WHERE子句中，那么它很可能是一个好的索引候选列。 -
区分度高的列： 区分度是指列中不同值的数量。区分度越高的列，索引效果越好。比如，
id列的区分度通常很高，适合作为索引。而性别列的区分度很低，索引效果可能不明显。 -
经常用于排序的列： 如果某个列经常用于
ORDER BY子句，那么它可以考虑作为索引列。 - 避免在小表上创建索引： 小表的数据量很小，全表扫描可能比使用索引更快。
- 不要过度索引： 索引会增加写入操作的成本。过多的索引会降低写入性能。
举个例子，假设你有一个 user 表，包含 id、name、age、city 等字段。如果你的查询经常是 SELECT * FROM user WHERE city = '北京'，那么 city 列就是一个好的索引候选列。如果你的查询经常是 SELECT * FROM user WHERE age > 20 ORDER BY name，那么 age 和 name 列都可以考虑作为索引列。
索引失效的常见原因有哪些？
即使创建了索引，也可能因为各种原因导致索引失效，从而影响查询性能。常见的索引失效原因包括：
-
使用了函数或表达式： 在
WHERE子句中对索引列使用了函数或表达式，会导致索引失效。比如WHERE YEAR(date) = 2023。因为数据库无法直接使用索引来计算函数或表达式的结果。 -
隐式类型转换： 如果查询条件中的数据类型与索引列的数据类型不匹配，可能会发生隐式类型转换，导致索引失效。比如，索引列是字符串类型，而查询条件是数字类型，
WHERE id = 123，如果id是字符串类型，可能会导致索引失效。 -
使用了
NOT、、!=： 这些操作符会导致数据库无法使用索引。 -
LIKE查询以%开头：LIKE '%keyword'会导致索引失效，因为数据库无法使用索引来定位以任意字符开头的字符串。LIKE 'keyword%'则可以使用索引。 -
组合索引未遵循最左前缀原则： 如果创建了组合索引
(a, b, c)，那么只有在查询条件中包含a或(a, b)或(a, b, c)时，才能使用索引。如果查询条件只包含b或c，则索引失效。 - 优化器认为全表扫描更快： 在某些情况下，数据库优化器可能会认为全表扫描比使用索引更快，从而放弃使用索引。这通常发生在数据量很小或者索引区分度很低的情况下。
如何查看SQL语句是否使用了索引？
要判断SQL语句是否使用了索引，可以使用 EXPLAIN 命令。EXPLAIN 命令可以显示SQL语句的执行计划，包括是否使用了索引、使用了哪个索引、扫描了多少行等信息。
例如，对于以下SQL语句：
SELECT * FROM user WHERE city = '北京';
可以使用以下命令查看执行计划：
EXPLAIN SELECT * FROM user WHERE city = '北京';
执行结果会显示一个表格，其中 possible_keys 列显示可能使用的索引，key 列显示实际使用的索引，rows 列显示扫描的行数。如果 key 列为 NULL，则表示没有使用索引。如果 rows 列的值很小，则表示使用了索引，并且效果很好。
通过分析 EXPLAIN 命令的结果，可以了解SQL语句的执行情况，并根据需要优化索引或SQL语句，以提高查询性能。
