Mysql优化-索引优化

Mysql优化-索引优化

1 什么是索引

索引：MySQL索引是一种数据结构，用于提高在MySQL数据库中查找和检索数据的速度。索引会对表中的一列或多列进行排序，从而让数据库更快地查找和返回数据。通过使用索引，可以减少数据库需要扫描的数据量，从而提高查询效率。MySQL支持多种类型的索引，包括B-Tree索引、包括B+Tree索引、哈希索引、全文索引等。在设计和优化数据库时，正确使用索引是提高查询性能的关键。简单的说，索引就像图书的目录，帮助用户快速到需要的内容，提高查询效率。与查询图书所用的目录类似，先定位到章，再定位到小结，最后找到页数。其他类似的例子包括查字典、查地图等。

2 索引的优缺点

优点

• 减少数据扫描量，加快数据检索速度。
• 避免排序和创建临时表。
• 将随机IO变成顺序IO，提高检索效率。
• 对于InnoDB引擎，可以减少锁的粒度，提高表访问并发性。
• 通过创建唯一性索引，保证数据的唯一性。
• 加速表和表之间的连接，实现数据的参考完整性。
• 减少分组和排序的时间。
• 使用索引可以通过优化隐藏器，提高系统的性能。

缺点

• 创建和维护索引需要耗费时间，数据量越大耗费的时间越多。
• 索引需要占用物理空间，如果需要建立聚簇索引，占用的空间会更大。
• 对表中的数据进行增、删、改的时候，索引也要动态维护，降低维护速度。
• 对于某些包含许多重复内容的数据列，为其建立索引效果不大。
• 对于非常小的表，简单的全表扫描更高效。

3 索引结构的类型

• B树索引：是一种平衡树结构，是一种平衡多路搜索树，另外并保证了每个叶子结点到根节点的距离相同，每个节点保存了data。

• B+树索引：B+树是一种多路平衡查找树，可以支持快速的插入、删除和查找操作，并且可以在不同数据页之间进行快速的遍历。在InnoDB中，每个索引都对应一个B+树，数据也是按照B+树的结构进行存储的。B+树的使用可以有效地提高InnoDB的查询性能和数据存储效率。

• 哈希索引：是一种散列表结构，适用于等值查询，查询效率非常高，但不支持范围查询。

• 全文索引：适用于文本类型的数据，可以快速进行全文检索，比如MySQL中的MyISAM引擎支持全文索引。

4 索引的类型

1. 主键索引（Primary Key Index）

主键索引是一种特殊的唯一索引，它强制表中每一行都具有唯一标识。主键索引的值不能为NULL。主键索引的特征是：

• 主键索引是一种特殊的唯一索引，用于标识表中的每一行数据。
• 主键索引的值不能为NULL，必须是唯一的。

主键索引的用法是：

• 在设计表结构时，应该为每个表定义一个主键。
• 主键通常是一个自增的整数类型，例如ID列。

假设我们有一个表格 users，其中有一个名为 id 的列，类型为 INT，我们希望将 id 列作为主键索引。我们可以使用如下的语句来创建主键索引：

ALTER TABLE users ADD PRIMARY KEY (id);

这个语句会创建一个名为 PRIMARY 的主键索引，它将 id 列作为唯一标识每一行数据的列。

2. 唯一索引（Unique Index）

唯一索引要求表中每一行的索引列都具有唯一性，但允许有空值（NULL）。唯一索引的特征是：

• 唯一索引要求表中每一行的索引列都具有唯一性，但允许有空值（NULL）。

唯一索引的用法是：

• 在设计表结构时，如果需要保证某个列的唯一性，可以为该列创建唯一索引。

假设我们有一个表格 products，其中有一个名为 sku 的列，类型为 VARCHAR(50)，我们希望将 sku 列作为唯一索引。我们可以使用如下的语句来创建唯一索引：

ALTER TABLE products ADD UNIQUE INDEX idx_sku (sku);

这个语句会创建一个名为 idx_sku 的唯一索引，它将 sku 列进行索引并保证其唯一性。

3. 普通索引（Normal Index）

普通索引是最基本的索引类型，它没有任何限制，可以在表的任意列上创建。普通索引的特征是：

• 普通索引是最基本的索引类型，它没有任何限制，可以在表的任意列上创建。

普通索引的用法是：

• 在设计表结构时，如果需要加速某个列的查询，可以为该列创建普通索引。

假设我们有一个表格 orders，其中有一个名为 customer_id 的列，类型为 INT，我们希望对 customer_id 列进行普通索引。我们可以使用如下的语句来创建普通索引：

CREATE INDEX idx_customer_id ON orders (customer_id);

这个语句会创建一个名为 idx_customer_id 的普通索引，它将 customer_id 列进行索引以提高查询效率。

4. 全文索引（Fulltext Index）

全文索引用于全文搜索，它可以快速地搜索文本列中的关键字，支持自然语言搜索、布尔搜索和通配符搜索等。全文索引的特征是：

• 全文索引用于全文搜索，可以快速地搜索文本列中的关键字。
• 全文索引支持自然语言搜索、布尔搜索和通配符搜索等。

全文索引的用法是：

• 在设计表结构时，如果需要对文本列进行全文搜索，可以为该列创建全文索引。

假设我们有一个表格 articles，其中有一个名为 content 的列，类型为 TEXT，我们希望对 content 列进行全文索引。我们可以使用如下的语句来创建全文索引：

CREATE FULLTEXT INDEX idx_articles_content ON articles (content);

这个语句会创建一个名为 idx_articles_content 的全文索引，它将 content 列进行全文索引以支持全文搜索。

5. 空间索引（Spatial Index）

空间索引用于地理空间数据的查询，支持多维数据的范围查询和最近邻查询等。空间索引的特征是：

• 空间索引用于地理空间数据的查询，支持多维数据的范围查询和最近邻查询等。

空间索引的用法是：

• 在设计表结构时，如果需要对地理空间数据进行查询，可以为该列创建空间索引。

假设我们有一个表格 locations，其中有一个名为 coordinate 的列，类型为 POINT，我们希望对 coordinate 列进行空间索引。我们可以使用如下的语句来创建空间索引：

CREATE SPATIAL INDEX idx_locations_coordinate ON locations (coordinate);

这个语句会创建一个名为 idx_locations_coordinate 的空间索引，它将 coordinate 列进行空间索引以支持地理空间数据的查询。

6. 组合索引 （Composite Index）

组合索引用于提高数据库查询性能。具体来说，组合索引可以在查询时快速找到符合多个条件的行，而不必扫描整个表。这是因为组合索引是由多个列组成的，可以同时匹配多个查询条件。需要注意的是，组合索引的顺序非常重要，需要根据查询的常见条件来确定列的顺序。如果查询中的条件顺序与组合索引的顺序不一致，数据库将无法使用该索引，从而影响查询性能。组合索引的特征是：

• 将多个列组合在一起创建的索引，可以提高多列查询的性能，但是对于单列查询的性能可能没有太大的提升。

空间索引的用法是：

• 在多个列同时进行查询时，比如 WHERE col1 = ? AND col2 = ?。

假设我们有一个表格 orders，其中有三个列，分别是 customer_id、product_id 和 order_date。我们希望对这三个列进行组合索引，以提高查询效率。我们可以使用如下的语句来创建组合索引：

CREATE INDEX idx_orders_customer_product_date ON orders (customer_id, product_id, order_date);

这个语句会创建一个名为 idx_orders_customer_product_date 的组合索引，它同时索引了 customer_id、product_id 和 order_date 这三个列。

7. 前缀索引 （Prefix Index）

前缀索引是一种特殊的索引类型，它只对列值的前缀进行索引，而不是对整个列值进行索引。这种索引类型可以在某些情况下提高查询性能，尤其是在列值较长或者数据量较大的情况下。
前缀索引的特征是：

• 只索引列的前缀部分，而不是整个列的值，可以减小索引的大小，从而提高查询的性能。

前缀索引的用法是：

• 在列的前缀部分有较高的选择性时，比如对于一个名字列，前缀索引可能只需要索引名字的前几个字符就可以满足查询需求。

假设我们有一个表格 products，其中有一个名为 description 的列，类型为 VARCHAR(500)，我们希望对 description 列进行前缀索引。我们可以使用如下的语句来创建前缀索引：

CREATE INDEX idx_products_description ON products (description(100));

这个语句会创建一个名为 idx_products_description 的前缀索引，它只索引 description 列的前 100 个字符。

8. 外键索引（Foreign Key Index）

外键索引是一种特殊的索引类型，它用于实现表与表之间的关系约束，保证数据的一致性和完整性。外键索引的特征是：

• 外键索引用于实现表与表之间的关系约束，保证数据的一致性和完整性。

外键索引的用法是：

• 在设计表结构时，如果需要实现表与表之间的关系约束，可以为外键列创建外键索引。

假设我们有两个表格 orders 和 customers，其中 orders 表格有一个名为 customer_id 的列，它是一个外键，参考了 customers 表格的 id 列。我们希望为 customer_id 列创建外键索引。我们可以使用如的语句来创建外键索引：

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer_id FOREIGN KEY (customer_id) REFERENCES customers (id);

这个语句会创建一个名为 fk_orders_customer_id 的外键索引，它将 customer_id 列进行外键索引以保证数据的一致性和完整性。

5 索引优化方法

1. 设计合理的表结构

例如，对于一个订单表，应该将订单号、订单日期、客户、订单金额等信息分别存储在不同的列中，而不是将所有信息存储在同一列中。

2. 选择合适的数据类型

例如，对于一个用户表，应该使用整型数据类型存储用户编号，而不是使用字符型数据类型。这样可以减小索引的大小，提高查询效率。

3. 创建合适的索引

创建合适的索引可以大大提高查询性能。对于经常被查询的列，应该为其创建索引。例如，对于以下的查询语句：

SELECT * FROM users WHERE age > 20 AND gender = 'male';

可以为age和gender两列创建联合索引：

CREATE INDEX idx_age_gender ON users (age, gender);

这样可以使查询更加高效。

4. 选择合适的索引类型

MySQL支持多种索引类型，如B-tree索引、哈希索引、全文索引等。在选择索引类型时，应该根据具体的情况选择合适的索引类型。例如，B-tree索引适用于范围查询，而哈希索引适用于等值查询。举个例子，如果要对一个字符串类型的列进行模糊查询，可以使用全文索引：

CREATE FULLTEXT INDEX idx_content ON articles (content);

这样可以大大提高查询性能。

5. 限制索引的数量

例如，对于一个用户表，应该只为用户名和邮箱地址创建索引，而不是为所有列创建索引。

CREATE INDEX idx_username ON user(username);
CREATE INDEX idx_email ON user(email);

6. 正确设置索引列的顺序

在创建联合索引时，应该将最常用的列放在前面。这样可以使索引更加有效，提高查询性能。例如，对于以下的查询语句：

SELECT * FROM users WHERE age > 20 AND gender = 'male';

可以将age列放在gender列前面，创建联合索引：

CREATE INDEX idx_age_gender ON users (age, gender);

这样可以使索引更加有效，提高查询性能。

7. 避免使用函数索引

在查询中使用函数会使索引失效，因此应该尽量避免在索引列上使用函数。例如，对于以下的查询语句：

SELECT * FROM users WHERE YEAR(created_at) = 2022;

使用YEAR函数会使索引失效，可以改为：

SELECT * FROM users WHERE created_at >= '2022-01-01' AND created_at < '2023-01-01';

这样可以避免使用函数索引，提高查询性能。

8. 避免使用长字符串作为索引

长字符串会占用更多的磁盘空间，也会使索引变得更加缓慢。因此，应该尽量避免在索引列上使用长字符串。例如，对于以下的查询语句：

SELECT * FROM users WHERE email = 'abc@example.com';

可以为email列创建索引：

CREATE INDEX idx_email ON users (email(50));

这里的50表示索引只取email列的前50个字符，避免了使用长字符串作为索引。

9. 使用覆盖索引

覆盖索引是一种特殊的索引，它包含了查询所需的所有列，而不仅仅是索引列本身。这样，当查询需要使用覆盖索引时，数据库引擎可以直接从索引中获取所需的数据，而不需要再去访问数据表，从而提高查询性能。

举个例子，假设有一个名为"orders"的数据表，包含以下列：order_id, customer_id, order_date, total_amount。现在我们希望查询某个特定日期的订单总金额。

如果我们创建一个覆盖索引，包含了order_date和total_amount列，那么查询可以直接从索引中获取所需的数据，而不需要再去访问数据表。对应的 SQL 查询语句如下：

SELECT total_amount FROM orders WHERE order_date = '2023-11-08';

在这个例子中，由于覆盖索引包含了total_amount列，数据库引擎可以直接从索引中获取该列的值，而不需要再去访问数据表，也就是不需要回表查询。这样可以提高查询性能，尤其是当数据表包含大量列或者数据量很大时。。

10. 定期维护索引

​ 定期维护索引可以优化索引的使用效率。例如，使用OPTIMIZE TABLE命令来优化索引。

OPTIMIZE TABLE user;

11. 监控数据库性能

​ 监控数据库性能可以及时发现问题并进行优化。例如，使用SHOW STATUS命令来监控数据库的性能。

SHOW STATUS LIKE 'Queries';