MYSQL 的数据索引
在数据库系统中，索引是用来加速数据检索的数据结构。索引可以分类为聚集索引和非聚集索引，它们在数据的存储和检索方式上有所不同。
聚集索引
聚集索引是一种特殊类型的索引，其中表中的行数据按照索引键的顺序物理存储。也就是说，在聚集索引中，数据实体和索引是合为一体的：
- 存储方式：聚集索引决定了表中数据的物理存储顺序。一旦聚集索引被创建，数据行就会按照索引键的顺序排列。
- 唯一性：一个表只能有一个聚集索引，因为数据只能按一种顺序物理存储。
- 访问速度：对于基于索引键的查询，聚集索引通常提供了最快的查询响应速度，因为索引结构直接包含了实际的数据行。
在数据库中创建聚集索引的具体步骤取决于你使用的数据库管理系统（DBMS）。以两个最常用的DBMS为例——MySQL和SQL Server。
在SQL Server中创建聚集索引
SQL Server允许你为表明确指定一个聚集索引。如果你在创建表时没有指定聚集索引，SQL Server不会自动创建一个。创建聚集索引的基本语法如下：
CREATE CLUSTERED INDEX IndexName ON TableName (ColumnName ASC|DESC);这里，IndexName是索引的名称，TableName是表的名称，ColumnName是你想要索引的列名，ASC|DESC表示索引的排序方式，ASC是升序，DESC是降序。
示例
假设有一个名为Employees的表，其中有EmployeeID、LastName和FirstName等列，你想根据EmployeeID创建一个聚集索引：
CREATE CLUSTERED INDEX IX_EmployeeID ON Employees (EmployeeID ASC);这条命令会创建一个按EmployeeID升序排列的聚集索引。
在MySQL中创建聚集索引
在MySQL中，聚集索引的概念与主键索引密切相关。InnoDB存储引擎会自动使用表的主键作为聚集索引。如果表没有明确的主键，InnoDB会选择第一个唯一索引（所有键列都定义为非空的）作为聚集索引。如果这样的索引也不存在，InnoDB内部会生成一个隐藏的、包含行ID的聚集索引。
创建主键（聚集索引）
CREATE TABLE Employees (
EmployeeID INT AUTO_INCREMENT,
LastName VARCHAR(255),
FirstName VARCHAR(255),
PRIMARY KEY (EmployeeID)
);在这个例子中，EmployeeID被定义为主键，因此也是聚集索引。所有数据将根据EmployeeID的值存储在表中。
聚集索引可以包含多个字段，这种类型的索引通常被称为复合聚集索引或多列聚集索引。在这种索引中，数据的物理存储顺序是根据索引中的多个列的值来确定的。这样可以优化那些需要频繁访问多个列进行排序或比较的查询。
创建复合聚集索引的优点包括
- 查询优化：如果查询经常需要通过多个列来过滤数据，复合聚集索引可以提高这类查询的性能。
- 数据整理：由于数据是按索引列的顺序存储的，所以复合聚集索引可以减少数据页面的读取次数，提高数据检索的效率。
- 排序和分组：对于需要按多个列排序或分组的查询，复合聚集索引可以直接利用索引的顺序，避免额外的排序操作。
注意事项
- 选择列：在选择哪些列包含在聚集索引中时，应考虑查询模式。理想的列是那些经常用于过滤、排序和连接的列。
- 顺序：列在索引中的顺序很重要，因为它决定了数据的存储和查询方式。通常，应将筛选频率最高的列放在前面。
- 性能开销：虽然复合聚集索引可以提高查询性能，但也可能增加插入、更新和删除操作的成本，因为任何对索引列的修改都可能导致数据在物理存储上的重排。
示例
在SQL Server中创建一个包含多个字段的聚集索引的示例：
CREATE CLUSTERED INDEX IX_EmployeeDetails ON Employees (DepartmentID ASC, EmployeeID ASC);这个示例中，Employees表上的聚集索引由两个列组成：DepartmentID和EmployeeID。这意味着表中的数据将首先按DepartmentID排序，然后在每个部门内按EmployeeID排序。
复合聚集索引使得在多个列上的数据访问更为有效，尤其是在这些列经常一起用于查询的情况下。不过，正确地选择和配置这些索引需要仔细考虑数据的使用模式和查询需求。
重要考虑
- 聚集索引选择：选择作为聚集索引的列非常重要，因为它决定了表中数据的物理存储顺序。通常，应选择经常用作搜索、排序和分组的列。
- 性能影响：聚集索引影响表的插入速度，因为插入新数据或更改索引列的数据时，可能需要物理重新排列存储的数据。
- 修改和维护：聚集索引可能需要定期维护，如重建和重新组织，以保持数据库的性能。
通过上述步骤，你可以在不同的数据库系统中创建聚集索引，以提高查询性能并优化数据的存储方式。
非聚集索引
非聚集索引和聚集索引不同，它并不改变数据的物理存储顺序，而是创建一个单独的数据结构（通常是B树），来存储索引键和指向数据行的指针：
- 存储方式：非聚集索引存储的是索引键和指向表中数据行的指针，这意味着数据可以保持任何顺序，索引存在于表数据之外。
- 唯一性：一个表可以有多个非聚集索引，因为它们只是指向数据的指针而不是数据本身。
- 访问速度：虽然非聚集索引通常比聚集索引慢（因为需要两次查找，一次在索引上，一次跳转到实际的数据行），但它们非常适合于那些不需要全表扫描的查询类型。
覆盖索引
索引覆盖（Covering Index）是指一种查询优化技术，其中所有需要查询的数据都包含在一个索引中，从而无需访问表中的实际数据行。这可以显著提高查询性能，因为索引通常比表更小且更高效。
索引覆盖的实现
当一个查询可以完全通过访问索引来满足时，该索引称为覆盖索引。这个索引不仅包含查询条件中涉及的列，还包含需要返回的列。这样，数据库引擎只需读取索引，而不需要访问实际的表数据。
示例
假设有一个表 Employee，结构如下：
CREATE TABLE Employee (
id INT PRIMARY KEY,
name VARCHAR(100),
department_id INT,
salary DECIMAL(10, 2)
);如果有一个包含 id、name 和 salary 列的复合索引：
CREATE INDEX idx_employee_name_salary ON Employee (name, salary);现在有一个查询：
SELECT name, salary
FROM Employee
WHERE name = 'Alice';由于查询中的name和salary列都包含在idx_employee_name_salary索引中，并且查询条件name = ‘Alice’也是在该索引上进行匹配的，这个索引就是一个覆盖索引。数据库引擎可以直接使用索引来返回结果，而无需访问表中的实际数据行。
优点
- 性能提升：覆盖索引减少了对表数据的访问次数，从而减少了I/O操作，提高了查询速度。
- 减少锁争用：因为只读取索引，减少了表级别的锁争用，提高了并发性能。
- 提高缓存命中率：索引通常比表小，更多的索引页可以保存在内存中，提高了缓存命中率。
注意事项
- 索引大小：虽然覆盖索引可以提高查询性能，但索引本身也会占用存储空间，特别是当索引包含多个列时。
- 更新开销：每次对表进行写操作（如INSERT、UPDATE、DELETE）时，相关索引也需要更新，因此维护覆盖索引会增加写操作的开销。
- 选择性：覆盖索引适用于选择性较高的查询，即那些能够显著减少需要访问的数据行数的查询。
总结
索引覆盖是一种非常有效的查询优化技术，通过在索引中包含所有需要的列，避免了访问表数据，提高了查询性能。然而，在设计覆盖索引时需要权衡索引大小和维护开销，以达到最佳的性能优化效果。
示例
假设有一个员工表，其中包括员工ID、姓名和部门ID。如果按照员工ID创建聚集索引，那么员工表就会按照员工ID的顺序物理存储。这意味着基于员工ID的查询会非常快。
同时，可以对姓名或部门ID创建非聚集索引。这些索引将在独立的位置存储姓名或部门ID的索引条目和指向包含这些姓名或部门ID的数据行的指针，而实际的数据行位置并不会因此而改变。
聚集索引和非聚集索引在数据库设计中都非常重要，它们各自适用于不同的查询优化场景。选择正确的索引策略可以显著提高数据库的性能和响应速度。