索引通过减少数据扫描量、利用排序结果集、覆盖索引和优化连接操作提升查询性能。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语句,以提高查询性能。