MYSQL 索引

MYSQL 索引

MYSQL支持多种索引类型，具体如下：

1. B+树索引：适合范围查询，所有数据存储在叶子节点，复杂度为O(logn)。例如，创建一个B+树索引可以加速基于年龄的范围查询：

   CREATE INDEX idx_age ON users(age);
   

2. 哈希索引：适合等值查询，检索效率高，一次到位。例如，为了加速根据用户名的查找，可以创建哈希索引：

   CREATE INDEX idx_username ON users USING HASH(username);
   

3. 全文索引：用于在文本类型的列上进行高效的全文搜索。例如，为了在文章的内容中搜索关键词，可以创建全文索引：

   CREATE FULLTEXT INDEX idx_content ON articles(content);
   

4. R-Tree索引：用于对GIS数据类型创建SPATIAL索引。例如，为了在地图上搜索特定区域内的地点，可以创建R-Tree索引。
5. 聚集索引：以主键创建的索引，在叶子节点存储的是表中的数据（Innodb存储引擎）。例如，表创建时主键自动成为聚集索引：

   CREATE TABLE users (
       id INT PRIMARY KEY,
       name VARCHAR(100)
   );
   

6. 非聚集索引：以非主键创建的索引，在叶子节点存储的是主键和索引列（Innodb存储引擎）。例如，创建一个非聚集索引可以加速根据邮箱的查询：

   CREATE INDEX idx_email ON users(email);
   

7. 主键索引：一种特殊的唯一索引，不允许有空值。例如，创建表时指定主键：

   CREATE TABLE orders (
       order_id INT PRIMARY KEY,
       user_id INT,
       amount DECIMAL(10, 2)
   );
   

8. 普通索引：MySQL中基本索引类型，允许空值和重复值。例如，创建一个普通索引可以加速根据用户ID的查询：

   CREATE INDEX idx_user_id ON orders(user_id);
   

9. 联合索引：多个字段创建的索引，使用时遵循最左前缀原则。例如，为了加速根据用户ID和订单状态的查询，可以创建联合索引：

   CREATE INDEX idx_user_id_status ON orders(user_id, status);
   

10. 唯一索引：索引列中的值必须是唯一的，但是允许为空值。例如，为了保证每个用户的邮箱唯一，可以创建唯一索引：

    CREATE UNIQUE INDEX idx_email ON users(email);
    

11. 空间索引：MySQL5.7之后支持空间索引，在空间索引这方面遵循OpenGIS几何数据模型规则。例如，为了在地图上搜索特定区域内的地点，可以创建空间索引。
12. 前缀索引：对索引列的前N个字符进行索引，可以减少索引的大小和提高查询性能。例如，如果用户名很长，可以创建前缀索引：

    CREATE INDEX idx_username_prefix ON users(username(10));
    

13. 复合索引：包含多个列的索引，用于加快联合查询和排序的效率。例如，为了加速根据用户ID和订单金额的查询，可以创建复合索引：

    CREATE INDEX idx_user_id_amount ON orders(user_id, amount);
    

以上是MYSQL支持的常见索引类型及其简要说明和创建示例。索引的选择应根据实际查询需求和数据特性来决定，以充分发挥其性能优势。

MYSQL 索引失效的常见场景

1. LIKE 查询以通配符 ‘%’ 开头：当使用 LIKE 进行模糊查询时，如果模式以通配符（%）开头，索引将不会被使用。例如：

   SELECT * FROM table_name WHERE column_name LIKE '%value';
   数据库中的索引通常是为了快速定位特定值或范围的值而设计的。当查询条件以通配符开头时，
   它意味着搜索的字符串可以匹配任意长度的字符序列，这导致索引无法有效定位起始点，因此无法发挥索引的优势。
   

2. OR 条件未同时使用索引：在 OR 条件中，如果只有一个条件字段是索引，那么该索引失效。例如：

   SELECT * FROM table_name WHERE indexed_column = 'value' OR non_indexed_column = 'value';
   

3. 组合索引非最左列查询：在使用组合索引时，查询条件必须包含索引的最左侧列，否则索引将不会生效。例如，对于组合索引 (column1, column2)，以下查询不会使用索引：

   SELECT * FROM table_name WHERE column2 = 'value';
   

4. 索引列上使用函数或运算：在 WHERE 子句中对索引列进行运算或使用函数会导致索引失效。例如：

   SELECT * FROM table_name WHERE YEAR(date_column) = 2023;
   SELECT * FROM table_name WHERE column_name + 1 = 10;
   

5. 数据类型不一致导致的隐式转换：如果查询中的值与索引列的数据类型不匹配，可能会发生隐式类型转换，导致索引失效。例如：

   SELECT * FROM table_name WHERE varchar_column = 123;
   

6. IS NULL 或 IS NOT NULL 操作：在索引列上使用 IS NULL 或 IS NOT NULL 可能会导致索引失效，因为索引是不索引空值的。例如：

   SELECT * FROM table_name WHERE indexed_column IS NULL;
   

7. 负向查询：在 WHERE 子句中使用负向查询（如 <>、！=）可能会导致索引失效。例如：

   SELECT * FROM table_name WHERE column_name != 'value';
   

8. 范围查询右侧索引失效：当进行范围查询时，如果使用了小于或大于操作符，那么范围右侧的索引将失效。例如：

   SELECT * FROM table_name WHERE column_name > 'value';
   

针对这些情况，开发者应尽量避免上述情况的发生，通过优化查询语句来确保索引的有效使用，从而提高数据库查询的性能。

MySQL 中是否使用索引取决于优化器

MySQL查询优化器的主要目标是通过选择合适的索引来提高查询性能。它不是基于简单规则，也不是完全基于成本（CBO），而是采用一种混合方式，主要倾向于基于成本的优化。优化器会根据以下因素来决定是否使用索引：

1. 查询解析：首先，优化器会解析SQL语句，确定查询类型（如SELECT、UPDATE、DELETE等）以及涉及的表和条件。
2. 统计信息：优化器会利用表的统计信息，如行数、唯一值的数量等，来评估索引的选择性。
3. 索引相关性：根据查询条件的相关性来评估索引的效果。例如，等值查询可能会偏好唯一索引，而范围查询可能更适合B树索引。
4. 索引成本：考虑索引的成本，包括读取次数、I/O成本和CPU成本等因素。
5. 索引提示：在某些情况下，可以使用USE INDEX提示来推荐优化器使用的索引。
6. 参数设置：优化器的行为还受到optimizer_prune_level、optimizer_search_depth、optimizer_switch等参数的影响。

MySQL优化器是一个复杂的系统，它会根据多种因素决定是否使用索引。

MYSQL 慢查询优化方案

1. 使用索引：

   • 创建索引：在经常用于查询条件的列上创建索引，可以加快查询速度。例如，如果经常根据用户ID查询用户信息，可以在用户ID列上创建索引。
   • 选择合适的索引类型：根据查询条件选择合适的索引类型，如B-tree索引、哈希索引等。
   • 使用复合索引：如果查询条件涉及多个列，可以使用复合索引来提高查询效率。

2. 优化查询语句：

   • 避免使用SELECT *：尽量只查询需要的列，而不是使用SELECT *查询所有列。这样可以减少数据传输量，提高查询速度。
   • 使用LIMIT限制结果集：如果只需要查询部分结果，可以使用LIMIT子句来限制结果集的大小。
   • 避免使用子查询：子查询可能会导致多次查询，降低查询效率。可以尝试将子查询转换为JOIN操作。
   • 使用预编译语句：预编译语句可以减少SQL解析和编译的时间，提高查询速度。

3. 调整数据库结构：

   • 规范化表结构：将表结构进行规范化，减少数据冗余，提高数据一致性。
   • 反规范化表结构：在某些情况下，为了提高查询速度，可以将表结构进行反规范化，将经常一起查询的列放在同一张表中。
   • 分割表：如果表的数据量非常大，可以考虑将表分割成多个小表，以提高查询速度。

4. 调整数据库配置：

   • 调整缓冲池大小：增加缓冲池大小可以提高缓存命中率，减少磁盘I/O操作。
   • 调整日志设置：适当调整binlog和redo log的设置，可以减少日志写入的频率和数量。
   • 调整线程池大小：根据实际情况调整线程池大小，可以提高并发处理能力。

5. 使用分区表：

   • 水平分区：将表中的数据按照某个字段的值进行划分，将不同范围的数据存储在不同的物理磁盘上。这样可以提高查询速度，同时也可以提高数据的可管理性。
   • 垂直分区：将表中的部分列分离出来，形成一个新的表。这样可以减小表的大小，提高查询速度。

6. 使用缓存：

   • 使用MySQL缓存：可以利用MySQL自带的缓存机制，将经常查询的结果缓存起来，减少查询时间。
   • 使用外部缓存：可以使用外部缓存系统，如Redis、Memcached等，将经常查询的结果缓存起来，减轻数据库的压力。

7. 监控和分析：

   • 开启慢查询日志：通过开启慢查询日志，可以记录执行时间较长的SQL语句，便于分析优化。
   • 使用性能分析工具：可以使用MySQL提供的性能分析工具，如EXPLAIN、SHOW PROCESSLIST等，分析SQL语句的执行情况。
   • 定期检查表和索引：定期检查表和索引的状态，如检查表是否需要修复、索引是否需要重建等。

以上是常用的MYSQL慢查询优化方案，每种方案都有其适用场景和优缺点。在实际应用中，需要根据具体情况选择合适的优化方案。

MYSQL 常见索引类型

MYSQL中常见的索引类型包括NORMAL（普通索引）、UNIQUE（唯一索引）、FULLTEXT（全文索引）和SPATIAL（空间索引），它们各自的特点如下：

1. 普通索引（Normal）：

• 这是最基本的索引类型，适用于大多数情况。
• 它允许在列中出现重复值，也不要求列值为空。
• 例如，如果要经常根据某个非唯一字段进行查询，可以对该字段创建普通索引以提高查询效率。

2. 唯一索引（Unique）：

• 唯一索引要求列中的每个值都是唯一的，但允许空值。
• 这种索引类型通常用于那些需要确保唯一性的字段，如用户的用户名或电子邮件地址。
• 例如，为了确保数据库表中的每条记录都可以通过唯一的用户名找到，可以对用户名字段创建唯一索引。

3. 全文索引（Fulltext）：

• 全文索引专门用于在文本类型的列上进行高效的全文搜索。
• 它适用于长文本的搜索，如果文本较短，普通的索引也可以胜任。
• 例如，如果你经常需要在一篇文章的内容中搜索关键词，可以为文章内容字段创建全文索引以加速搜索过程。

4. 空间索引（Spatial）：

• 空间索引是针对空间数据类型字段建立的索引，用于地理空间数据的存储和检索。
• MYSQL中的空间数据类型包括GEOMETRY、POINT、LINESTRING、POLYGON等。
• 例如，如果你的应用涉及到地理位置数据的存储和查询，如地图上的兴趣点，可以使用空间索引来提高查询效率。

综上所述，每种索引类型都有其特定的使用场景和优势。在选择索引时，应该根据实际的数据特性和查询需求来决定最合适的索引类型。