SQL优化相关问题

查询优化

优化SQL语句

避免使用SELECT *，只查询需要的列；优化WHERE子句，将过滤性强的条件放在前面；避免使用!=或<>操作符、慎用OR等。

使用索引

合理创建索引，为高频查询条件、JOIN字段、排序字段等创建索引；遵循最左前缀原则，设计复合索引；避免索引失效，防止索引列上使用函数、类型转换等。

避免不走索引的场景

    1.尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。
    2.尽量避免使用in 和not in，会导致引擎走全表扫描。
    3.尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。
    4.尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。
    5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。
    6.当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。
    7.查询条件不能用 <> 或者 !=
    8.where条件仅包含复合索引非前置列
    9.隐式类型转换造成不使用索引
    10.order by 条件要与where中条件一致，否则order by不会利用索引进行排序

最左前缀原则

    最左前缀原则指的是在复合索引中，查询条件必须包含索引列的最左部分，才能有效利用该索引进行查询优化。也就是说，复合索引中左边的列（前导列）在查询条件中被使用时，索引才能被利用，而右边的列（非前导列）则不一定能被利用。
    假设有一个复合索引 index(a, b, c) ，那么根据最左前缀原则：
    如果查询条件中包含列 a ，则索引可以被利用，因为 a 是最左列。
    如果查询条件包含列 a 和 b ，同样可以利用索引。
    如果查询条件包含列 a 、b 和 c ，也可以利用索引。
    然而，如果查询条件只包含列 b 或列 c ，或者包含列 b 和 c 而不包含列 a ，则无法利用该复合索引，因为没有满足最左前缀条件。
    这是因为在数据库存储索引时，是按照索引列的顺序构建的树形结构（如 B + 树）。只有按照最左列的顺序进行搜索，才能快速定位到符合条件的记录范围。

索引为什么快

1.数据查找原理方面
有序性优势
索引通常是按照一定的顺序（如升序或降序）存储数据的。以数据库中的B - 树索引为例，数据是按照键值（如用户ID）有序排列的。当需要查找某个特定键值（如查找用户ID为12345的记录）时，可以利用这种有序性进行二分查找。
二分查找的基本思想是：先找到中间的节点，如果目标键值等于中间节点的键值，查找成功；如果目标键值小于中间节点的键值，就在左子树中继续查找；如果目标键值大于中间节点的键值，就在右子树中查找。这种查找方式的时间复杂度是O(log n)，其中n是索引中的记录数。相比顺序查找（时间复杂度为O(n)），二分查找效率大大提高。例如，在一个包含100万条记录的索引中，顺序查找可能需要比较100万次，而二分查找最多只需要比较20次左右（因为log2(1000000)≈20）。

2.减少数据扫描范围
索引可以快速定位到数据存储的特定位置。在没有索引的情况下，数据库系统需要对整个数据表进行全表扫描。全表扫描就像在一本没有目录的书中查找某段内容，需要逐页查看。而索引就像书的目录，它记录了数据的存储位置。
例如，在一个电商数据库中，有一个订单表，包含数百万条订单记录。如果要查找某个特定客户的订单，没有索引时，数据库系统需要逐条扫描整个订单表。但如果在客户ID上建立了索引，数据库系统就可以直接通过索引找到该客户订单的存储位置，跳过其他无关的订单记录，大大减少数据扫描的范围，从而加快查找速度。

3.数据存储结构方面
索引结构的高效性
常见的索引结构如B - 树和哈希表等都有其高效存储和检索的特点。对于B - 树索引，它是一种平衡的多叉树结构。B - 树的每个节点可以存储多个键值，而且树的高度相对较低。这种结构使得在查找、插入和删除操作时，访问的节点数量较少。
以一个高度为3的B - 树为例，每次查找最多只需要访问3个节点（根节点、中间层节点和叶子节点）。而且B - 树的叶子节点通常存储实际数据的指针，这种结构使得数据存储和检索紧密相连，进一步提高了检索效率。
对于哈希索引，它是基于哈希函数来存储和检索数据的。哈希函数可以将键值快速映射到一个存储位置。只要哈希函数设计得合理，能够均匀地分布数据，查找操作的时间复杂度可以接近O(1)。例如，在一个小型的用户登录系统中，使用哈希索引存储用户名和密码，当用户登录时，系统通过哈希函数快速定位到用户名对应的存储位置，验证密码，这种方式非常快速。

4.缓存友好性
索引数据通常更容易被缓存。因为索引结构相对较小，且访问模式较为规律。当数据库系统频繁访问索引时，索引数据会被加载到内存中的缓存区域。
例如，对于一个经常被查询的字段，其索引数据可能会长期驻留在内存缓存中。当再次执行相关查询时，可以直接从内存缓存中获取索引数据，而不需要从磁盘读取，这大大减少了磁盘I/O操作，提高了查询速度。磁盘I/O操作速度相对较慢，而内存访问速度要快得多，缓存机制在索引快速检索过程中起到了关键的加速作用。

5.其他知识点
什么是回表？
回表（Back - to - Table）是数据库查询中一个常见的概念，通常出现在使用索引进行查询时。它指的是在通过索引找到目标数据的索引项后，还需要回到原始数据表中去获取完整数据的过程。

如何避免回表
1.使用覆盖索引
覆盖索引（Covering Index）是指查询所需的全部字段都包含在索引中，这样数据库可以直接通过索引获取所有需要的数据，而无需回表。

2.优化索引设计
合理设计索引字段：确保索引字段能够覆盖查询中常用的字段。
避免冗余索引：过多的索引会增加维护成本和存储开销，同时可能影响插入、更新和删除操作的性能。
使用合适的索引类型：根据查询需求选择B - 树索引、哈希索引或全文索引等。

3.使用缓存
缓存热点数据：对于频繁查询的数据，可以将其缓存到内存中，减少对数据库的访问。
缓存中间结果：如果某些查询结果可以复用，可以将其缓存起来，避免重复查询。

4.分析查询和执行计划
使用EXPLAIN分析查询：通过EXPLAIN语句查看查询的执行计划，了解是否发生了回表操作。
优化查询语句：根据执行计划调整查询语句，尽量减少回表操作。

5.优化JOIN和分页查询
遵循小表驱动大表的原则进行JOIN操作；优化分页查询，避免大偏移量，使用WHERE id > last_id LIMIT n替代LIMIT m,n等。

6.优化排序和子查询：为ORDER BY/GROUP BY字段建立索引，减少排序数据量；用JOIN替代子查询，使用EXISTS代替IN等。

索引常用类型和适用场景

 B - 树索引
       结构：B - 树是一种平衡的多叉树结构，每个节点可以存储多个键值和子节点指针。
       优点：支持范围查询、排序查询和等值查询，查询效率高（时间复杂度为O(log n)）。
       缺点：占用空间相对较大，插入和删除操作可能需要调整树的平衡。
       适用场景：查询某个时间段内的数据。按某个字段排序。查找某个特定值。

哈希索引
        结构：基于哈希表实现，通过哈希函数将键值映射到存储位置。
        优点：对于等值查询非常高效，时间复杂度接近O(1)。
        缺点：不支持范围查询和排序查询，插入和删除操作可能需要重新计算哈希值。
        适用场景： 
        等值查询：当查询主要是查找某个特定值时。
        在高并发的场景下，哈希索引可以快速定位数据，减少锁的争用

全文索引
        结构：用于存储文本数据的索引，支持复杂的文本搜索操作。
        优点：支持全文搜索，可以快速查找包含特定单词或短语的文本。
        缺点：占用空间较大，构建和维护成本较高。
        适用场景： 文本搜索：例如，搜索引擎、文档管理系统等。

位图索引
        结构：使用位图来表示数据的存在与否，每个位对应一行数据。
        优点：对于低基数（即字段值种类较少）的字段非常高效，占用空间小。
        缺点：不适用于高基数字段，更新操作可能非常耗时。
        适用场景：低基数字段：例如，性别、状态等字段。

复合索引
        结构：在多个字段上创建索引。
        优点：可以同时优化多个字段的查询，减少回表操作。
        缺点：索引的维护成本较高，插入、更新和删除操作可能更慢。
        适用场景：多字段查询：当查询涉及多个字段时，复合索引可以提高查询效率。

唯一索引
        结构：类似于普通索引，但要求索引列的值必须唯一。
        优点：可以防止数据重复，同时支持快速查找。
        缺点：插入和更新操作需要额外检查唯一性。
        适用场景：唯一性约束：例如，用户名、邮箱等字段。

空间索引
        结构：用于存储和查询空间数据（如地理坐标）。
        优点：支持空间查询，如范围查询、邻近查询等。
        缺点：实现复杂，占用空间较大。
        适用场景：地理信息系统（GIS）：例如，查找某个区域内的地点。

如何查看索引类型？
mysql
使用SHOW INDEX
查询INFORMATION_SCHEMA.STATISTICS