避免mysql查询全表扫描的方法包括：1. 根据查询需求创建合适的索引，选择性高的列优先；2. 使用explain分析查询执行计划，查看是否使用索引及扫描行数；3. 避免在where子句中对索引列使用函数、表达式或类型不匹配；4. 合理使用like语句，避免前置通配符；5. 利用覆盖索引减少回表查询；6. 优化分页查询时采用延迟关联或覆盖索引；7. 注意or条件、not in、!=、is null等可能导致索引失效的情况；8. 根据业务场景选择合适的索引类型如b-tree、hash、fulltext、spatial索引。
避免MySQL查询全表扫描，核心在于有效利用索引。索引就像书籍的目录，能帮你快速定位到所需内容，而不是一页一页地翻阅。
解决方案
明确查询需求，创建合适的索引： 这是最关键的一步。索引并非越多越好，过多的索引会增加写操作的负担。需要根据实际的查询条件，选择合适的列建立索引。比如，经常根据
user_id
查询订单信息，那么在order
表的user_id
列上建立索引是明智之举。-
利用
EXPLAIN
分析查询语句： 在执行查询之前，使用EXPLAIN
命令可以查看MySQL的查询执行计划。关注type
列，如果显示ALL
，则表示进行了全表扫描。possible_keys
列显示了可能使用的索引，key
列显示了实际使用的索引。根据EXPLAIN
的结果，调整查询语句或索引，以避免全表扫描。
避免在
WHERE
子句中使用函数或表达式： 在WHERE
子句中对索引列使用函数或表达式会导致索引失效。例如，WHERE DATE(order_date) = '2023-10-26'
会导致order_date
列的索引失效。应该尽量避免这种情况，可以将表达式移到等号的另一边，例如WHERE order_date BETWEEN '2023-10-26 00:00:00' AND '2023-10-26 23:59:59'
。-
注意
LIKE
语句的使用：LIKE '%keyword'
会导致索引失效，因为%
在前面表示需要匹配任意字符，MySQL无法使用索引进行查找。如果必须使用LIKE
，尽量使用LIKE 'keyword%'
，这样可以利用索引的前缀匹配功能。
组合索引的顺序： 对于组合索引，列的顺序很重要。应该将选择性高的列放在前面。选择性是指该列不同值的数量与总行数的比例。例如，如果
user_id
的选择性比order_date
高，那么应该创建INDEX(user_id, order_date)
，而不是INDEX(order_date, user_id)
。覆盖索引： 如果查询只需要索引中的列，那么可以使用覆盖索引。覆盖索引是指查询所需的所有列都包含在索引中，这样MySQL可以直接从索引中获取数据，而不需要回表查询，从而提高查询效率。例如，如果经常查询
user_id
和order_date
，可以创建INDEX(user_id, order_date)
，然后使用SELECT user_id, order_date FROM order WHERE ...
进行查询。分页查询优化： 对于分页查询，如果数据量很大，
LIMIT
语句可能会导致性能问题。可以使用延迟关联或者覆盖索引来优化分页查询。延迟关联是指先通过索引找到需要的数据的id
，然后再根据id
回表查询。覆盖索引是指使用覆盖索引来避免回表查询。
如何判断MySQL查询是否使用了索引？
使用
EXPLAIN命令是判断MySQL查询是否使用了索引最直接的方式。在
EXPLAIN的结果中，如果
type列的值不是
ALL，并且
key列显示了实际使用的索引，那么就表示查询使用了索引。此外，还可以关注
rows列，该列显示了MySQL估计需要扫描的行数。如果
rows的值很小，那么就表示查询使用了索引，并且索引的效果很好。反之，如果
rows的值很大，接近表的总行数，那么就表示查询没有使用索引，或者索引的效果不好。
索引失效的常见原因有哪些，如何避免？
索引失效是导致全表扫描的常见原因。以下是一些常见的索引失效原因以及避免方法：
在索引列上进行计算或使用函数： 避免在
WHERE
子句中对索引列进行计算或使用函数。例如，WHERE YEAR(order_date) = 2023
会导致order_date
列的索引失效。应该尽量避免这种情况，可以将表达式移到等号的另一边，例如WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'
。类型不匹配： 如果查询条件的数据类型与索引列的数据类型不匹配，会导致索引失效。例如，如果
order_id
是VARCHAR
类型，但是查询条件是WHERE order_id = 123
，那么会导致order_id
列的索引失效。应该确保查询条件的数据类型与索引列的数据类型一致。OR
条件： 如果WHERE
子句中使用OR
条件，并且OR
条件中的一个列没有索引，那么会导致整个查询的索引失效。应该尽量避免使用OR
条件，可以使用UNION
或者多个查询来替代。NOT IN
和!=
： 在某些情况下，NOT IN
和!=
会导致索引失效。可以使用BETWEEN
或者多个IN
来替代。IS NULL
和IS NOT NULL
： 在某些情况下，IS NULL
和IS NOT NULL
会导致索引失效。应该尽量避免使用IS NULL
和IS NOT NULL
，可以使用默认值来替代。
除了B-Tree索引，MySQL还有哪些索引类型？
虽然B-Tree索引是最常用的索引类型，但MySQL还支持其他几种索引类型，每种索引类型都有其特定的适用场景：
Hash索引： Hash索引使用哈希函数将索引列的值映射到一个哈希值，然后将哈希值存储在索引中。Hash索引的查找速度非常快，时间复杂度为O(1)，但是Hash索引只能用于精确匹配，不能用于范围查询和排序。Hash索引适用于等值查询，例如
WHERE id = 123
。Fulltext索引： Fulltext索引用于全文搜索，可以对文本内容进行索引。Fulltext索引适用于需要进行全文搜索的场景，例如搜索文章内容。
Spatial索引： Spatial索引用于存储和查询地理空间数据，例如经纬度。Spatial索引适用于需要进行地理空间查询的场景，例如查找附近的餐厅。
选择合适的索引类型需要根据具体的业务场景和查询需求进行权衡。B-Tree索引是最通用的索引类型，适用于大多数场景。如果需要进行全文搜索，可以使用Fulltext索引。如果需要存储和查询地理空间数据，可以使用Spatial索引。Hash索引适用于等值查询，但是使用场景有限。
