MySQL索引

1. 索引分类

按照数据结构分类：

• B+ tree索引：大多数数据库系统使用的索引类型，包括MySQL的InnoDB引擎。
• Full text索引：用于全文搜索。
• Hash索引：适用于等值查询，但不适合范围查询。

按照物理存储分类：

• 聚簇索引（Clustered Index）：数据行的物理顺序与索引键的逻辑顺序相同。
• 二级索引（Secondary Index）（辅助索引）：索引结构和数据行的物理顺序不同。

按照字段分类：

• 主键索引：唯一标识表中每行的索引。
• 普通索引（非唯一索引）：用于加速查询，没有唯一性要求。
• 前缀索引：当列的前缀对于查询已经足够区分行时使用。

按照字段个数分类：

• 单列索引：仅包含一个列的索引。
• 联合索引（Composite Index）（组合索引）：包含多个列的索引。

2.从索引的底层结构来说

自适应哈希索引

innoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，就建立Adaptive Hash Index。读写速度提高2倍，辅助索引的连接操作性能提高五倍。需要连续访问模式相同（查询条件一样），只能搜索等值查询：

select * from table where index_col = 'xxx';

可以用以下命令查看是否使用：

show engine innodb status;

B+树索引

b+树索引并不能找到一个给定键值的具体行，而是找到被查找数据行所在的页，然后通过数据库把页读入到内存，再在内存中进行查找，最后得到要查找的数据。对于每一条记录的查询是通过对于主键进行二分查找得到的。

B+树是为磁盘或者其他直接直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录结点都是按照键值的大小顺序存放在同一层的叶子结点上，由各叶子结点进行连接。

例如一个高度为2的b+树，根节点扇出为5（fanout：每个结点允许的最大子节点数），一个叶节点为一页，每页可以存放四条记录。叶节点的父节点存储的是范围，例如根节点为[]25[]50[]75[]85[]，如果要查找的键在50到75之间，那么会去拿到第三个叶子结点。

类似其他平衡查找树，B+树的插入和删除也需要调整拆分合并结点。

B+树索引在数据库中有一个特点是高扇出性，在数据库中，树的高度一般在2到4层，也就是查找某一个键值的行记录最多只需要2到4次io。

聚集索引和辅助索引（底层为B+树）

• 聚集索引：一张表的所有行根据主键构建一棵B+树，叶子为一个page（包含若干row的完整数据）。每个表只能有一个聚集索引。

  在创建表时通常通过定义主键来创建聚集索引，如果没有显式定义主键，数据库系统会自动生成一个隐藏的聚集索引。在MySQL的InnoDB存储引擎中，如果没有指定索引类型，默认为主键创建聚集索引。

  CREATE CLUSTERED INDEX index_name ON table_name (column);

• 辅助索引：叶子结点不包含记录行的全部数据，但是有指向数据行的指针。首先检索辅助索引获得主键，然后用主键到主索引中获得记录。一张表可以创建多个辅助索引。

  CREATE INDEX index_name ON table_name (column);

全文索引

将存储于数据库中的整本书或者整篇文章中的任意内容信息查找出来的技术。

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT(title, body) WITH PARSER ngram  -- 创建全文索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO articles (title, body) VALUES
('MySQL全文索引入门', 'MySQL全文索引可以帮助你快速检索文章内容。'),
('数据库技术指南', '这篇文章介绍了数据库技术的基础知识。'),
('全文索引的使用方法', '本文详细介绍了全文索引的使用方法和技巧。');

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('全文索引' IN NATURAL LANGUAGE MODE);
# 从 articles 表中选择所有列，其中 title 或 body 列包含 '全文索引' 这个短语的所有记录

3.创建一个新的索引要考虑什么

在选择字段、复合索引的顺序和索引的唯一性时，需要考虑以下因素：

1. 选择字段：

   • 查询需求：选择那些经常作为查询条件的字段，如WHERE子句中的列、JOIN操作中的列，或者出现在ORDER BY和GROUP BY子句中的列。
   • 数据类型：对于全文索引，只能对CHAR、VARCHAR或TEXT类型的列创建索引。
   • 数据分布：选择那些具有高区分度的列，即列值重复率低的列。

2. 复合索引的顺序：

   • 最左前缀原则：复合索引会遵循最左前缀原则，即索引查询会从左到右匹配列。因此，应该将最常用于搜索条件的列放在索引的最左边。
   • 选择性：选择那些具有高选择性的列（列值分散的列），这样可以减少索引的宽度，提高索引效率。
   • 查询模式：根据查询中列的组合方式，将最常一起出现的列放在索引中，这样可以使得索引更加高效。

3. 索引的唯一性：

   • 唯一索引：如果列的值必须唯一，可以使用唯一索引来强制执行这一约束。这可以防止插入重复的值。
   • 主键索引：主键索引是一种特殊类型的唯一索引，每个表只能有一个主键索引，它保证了表中每一行都有一个唯一标识。
   • 非唯一索引：如果列的值可以重复，可以创建非唯一索引。非唯一索引允许列值中有重复，适用于大多数非唯一性查询优化。

示例

假设有一个employees表，包含以下列：

• id：员工ID，唯一标识符。
• last_name：员工的姓氏。
• first_name：员工的名字。
• department_id：员工所属部门的ID。
• salary：员工的薪资。

基于以上信息，我们可以创建以下索引：

• 主键索引：通常在id上自动创建。

  ALTER TABLE employees ADD PRIMARY KEY (id);

• 复合索引：如果经常根据last_name和first_name联合查询员工，可以创建一个复合索引。

  ALTER TABLE employees ADD INDEX idx_name (last_name, first_name);

• 唯一索引：如果department_id和salary的组合在表中必须是唯一的，可以创建一个唯一索引。

  ALTER TABLE employees ADD UNIQUE INDEX idx_department_salary (department_id, salary);

• 全文索引：如果需要对员工的描述信息进行全文搜索，可以对description列创建全文索引。

  ALTER TABLE employees ADD FULLTEXT INDEX idx_description (description) WITH PARSER ngram;

在创建索引时，应该根据实际的查询需求和数据访问模式来决定索引的类型和顺序。

4.索引失效的情况有哪些

在MySQL中，索引失效可能由多种原因引起，以下是一些常见的索引失效情况及具体例子：

1. 不满足最左匹配原则：在使用联合索引时，如果不按照索引列的顺序查询，可能会导致索引失效。

   • 例如，如果有一个索引是(idx1, idx2)，查询条件中只使用idx2，则不会使用索引。

2. 索引列参与运算：索引列参与计算或其他表达式操作时，索引可能失效。

   • 例如，SELECT * FROM table WHERE id + 1 = 10，这里对id进行了加法运算，索引将不会被使用 。

3. 使用函数或表达式：在WHERE子句中对索引列使用函数，如DATE(column)，索引将失效。

4. 类型隐式转换：当索引列的数据类型与查询条件的数据类型不匹配时，可能会发生类型隐式转换，导致索引失效。

   • 例如，如果id_no字段类型为VARCHAR，但查询条件中使用了整型1002，索引将不会使用 。

5. 使用OR操作符：如果查询条件中使用OR，而且不是所有条件都使用索引列，索引可能会失效。

   • 例如，SELECT * FROM table WHERE id = 10 OR name = 'John'，如果只有id上有索引，索引将不会使用。

6. LIKE语句使用不当：如果使用LIKE进行模糊匹配，且通配符%在开头，索引将失效。

   • 例如，SELECT * FROM table WHERE name LIKE '%John'。

7. NOT IN或NOT EXISTS：使用NOT IN或NOT EXISTS时，索引可能不会使用。

   • 例如，SELECT * FROM table WHERE id NOT IN (10, 20, 30)，索引可能不会使用。

8. ORDER BY使用错误：如果ORDER BY的列不是查询中已经使用的索引列，索引可能不会使用。

   • 例如，SELECT * FROM table ORDER BY name，如果name列上没有索引，将导致全表扫描。

9. 全表扫描速度比索引速度快：在某些情况下，MySQL认为全表扫描比使用索引更快，这时索引会失效。

   • 例如，对于非常小的表或者索引列数据重复率很高的情况。

10. IS NOT NULL：使用IS NOT NULL查询条件时，索引可能不会使用。

    在数据库查询中，IS NULL 和 IS NOT NULL 操作通常用于查找列值缺失的记录。但是，当这些操作用于索引列时，它们可能会导致索引失效，因为索引是基于列值的顺序存储的，而NULL值通常不参与索引排序。

    使用 IS NULL 或 IS NOT NULL 的问题

    假设你有一个users表和一个orders表，其中orders表有一个user_id列作为外键引用users表的id列。如果你想要找到所有没有订单的用户，你可能会写这样的查询：

    SELECT * FROM users WHERE user_id IS NULL;

    如果user_id上有索引，这个查询通常会导致索引失效，因为IS NULL条件无法利用索引来快速过滤记录。

11. 参数不同导致索引失效：根据查询参数的不同，优化器可能会决定不走索引。

12. 索引选择性差：如果索引的选择性不高，即索引列的值重复率很高，MySQL可能不使用索引。

在对索引行为有疑问时，可以使用EXPLAIN关键字查看查询的执行计划，以确定索引是否被正确使用。

5.如何优化索引

1. 遵循最左前缀法则：在使用联合索引时，确保查询条件按照索引列的顺序使用，从左到右。

   • 假设有表users，包含字段country和city，建立联合索引：ALTER TABLE users ADD INDEX idx_country_city (country, city);

2. 避免索引列上的函数操作：不要在索引列上使用函数或表达式，因为这会导致索引失效。

   • 避免使用：SELECT * FROM users WHERE YEAR(signup_date) = 2023;
   • 改为使用：SELECT * FROM users WHERE signup_date >= '2023-01-01' AND signup_date < '2024-01-01';

3. 选择合适的索引类型：根据数据和查询的特点选择合适的索引类型，如B-Tree索引、哈希索引或全文索引。

   • 对于经常用于排序的列，可以使用B-Tree索引：ALTER TABLE users ADD INDEX idx_name (name);
   • 对于需要快速查找的列，可以使用哈希索引（MySQL 5.6及以上版本支持）：ALTER TABLE users ADD INDEX idx_email (email USING HASH);

4. 使用覆盖索引：如果可能，创建覆盖索引（包含所有查询列的索引），这样查询可以直接使用索引而不需要回表查询。

   • 假设查询经常访问name和email字段：ALTER TABLE users ADD INDEX idx_cover_name_email (name, email);

5. 考虑索引的选择性：选择性高的索引（不同的值多）通常更有效。避免在具有大量重复值的列上创建索引。

   • 使用SHOW INDEX FROM users;查看索引的选择性，选择选择性高于50%的列建立索引。

6. 避免冗余索引：删除那些不常用或重复的索引，因为它们会增加写操作的开销。

   • 删除不使用的索引：DROP INDEX idx_old ON users;

7. 使用合适的数据类型：确保索引列的数据类型与查询条件的数据类型一致，避免隐式类型转换。

   • 确保查询条件与索引列的数据类型一致：SELECT * FROM users WHERE CAST(id AS CHAR) = '123';

8. 考虑前缀索引：对于很长的字符字段，有时只索引前几个字符，可以节省空间并提高效率。

   • 对于长文本字段，只索引前缀：ALTER TABLE users ADD INDEX idx_email_prefix (email(10));

9. 定期重建索引：随着数据的插入、更新和删除，索引可能会变得碎片化。定期重建索引可以提高性能。

   • 重建索引以减少碎片化：OPTIMIZE TABLE users;

10. 使用索引提示：在某些情况下，可以通过在查询中指定索引提示来告诉数据库优化器使用特定的索引。

    • 强制使用特定索引：SELECT * FROM users USE INDEX (idx_name) WHERE name = 'John Doe';

11. 分析查询模式：根据应用程序的查询模式创建索引，为常用的查询创建索引。

    • 根据常见查询创建索引：ALTER TABLE users ADD INDEX idx_login (login);

12. 避免使用SELECT *：在查询时只获取必要的列，而不是所有列，可以减少索引和数据的I/O。

    • 只获取必要的列：SELECT id, name FROM users;

13. 使用EXPLAIN分析查询：使用EXPLAIN关键字分析查询计划，查看索引是否被有效利用。

    • 分析查询计划：EXPLAIN SELECT * FROM users WHERE name = 'John Doe';

14. 考虑使用分区表：对于大型表，可以考虑使用分区表来提高查询效率。

    • 对大表进行分区：ALTER TABLE users PARTITION BY RANGE (year(signup_date)) (PARTITION p0 VALUES LESS THAN (2020), PARTITION p1 VALUES LESS THAN (2021), PARTITION p2 VALUES LESS THAN MAXVALUE);

15. 避免大范围的JOIN操作：大范围的JOIN操作可能会导致索引失效，优化JOIN条件以使用索引。

    • 优化JOIN条件：SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.country = 'USA';

16. 使用OR条件时要小心：如果必须使用OR，确保每个条件都能使用索引。

    • 确保每个OR条件都能使用索引：ALTER TABLE users ADD INDEX idx_name_or_email (name, email);

17. 定期监控索引使用情况：使用数据库的监控工具定期检查索引的使用情况和性能。

    • 使用性能监控工具，如Percona Monitoring and Management (PMM)。

18. 考虑索引的维护成本：索引虽然可以提高查询速度，但也会降低更新表的速度，因为数据库需要同时更新索引。

    • 权衡索引对查询和更新操作的影响。

19. 优化ORDER BY和GROUP BY子句：确保这些子句中的列已经被索引。

    • 对排序字段创建索引：ALTER TABLE orders ADD INDEX idx_order_date (order_date);

20. 避免在索引列上使用IS NULL或IS NOT NULL：这些条件可能会导致索引失效。

    • 改为使用LEFT JOIN和IS NULL：SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.user_id IS NULL;

      查找没有订单的客户：

      SELECT u.*
      FROM users u
      LEFT JOIN orders o ON u.id = o.user_id
      WHERE o.user_id IS NULL;

      这里，LEFT JOIN会从users表中返回所有用户，即使在orders表中没有匹配的订单。然后，WHERE o.user_id IS NULL条件会过滤掉那些在orders表中有匹配订单的用户。因为LEFT JOIN会为没有匹配订单的用户在orders表中创建NULL行，所以这个查询可以有效地利用users表上的索引。