MySQL的索引

MySQLSE

发布于 2024-02-22 15:18:23

1330

发布于 2024-02-22 15:18:23

MySQL的索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后遍历整个表以找到相关的行。表越大，成本就越高。如果表中有相关列的索引，MySQL可以快速确定在数据文件中查找的位置，而不必查看所有数据。使用索引是打开MySQL的正确方式，本篇将介绍MySQL的索引相关内容。

MySQL的索引可以用于以下操作：

快速查找与“WHERE”语句匹配的数据行。
排除数据行。如果在多个索引之间进行选择，MySQL通常使用找到行数最少的索引(最具选择性的索引)。
在执行联结操作时，从其他表获取数据行。
查找特定索引列“key_col”的“MIN()”或“MAX()”值。
如果排序或分组是在可用索引的最左边的前缀上完成的，则对表进行排序或分组(例如，ORDER BY key_part1, key_part2)。
在某些情况下，可以对查询进行优化，以便在不查询数据行的情况下检索值。

需要注意，对于查询需要访问大多数行时，顺序读取比通过索引处理要快。顺序读取可以最大限度地减少磁盘查找，即使查询并不需要所有行。

索引的类型

MySQL中的索引可以分为如下类型：

非唯一索引：索引值可以出现多次（默认索引类型）。
唯一索引：索引值必须唯一或为NULL。
主键：值必须唯一，并且不能包含NULL。
全文索引：索引由字符串构成，并支持全文检索。
空间索引：索引由空间数据类型构成。
函数索引：对表中的列执行表达式或函数计算后的结果构成索引。

创建和删除索引

表中不包含主键，创建一个主键：

ALTER TABLE tablename ADD PRIMARY KEY (col1, col2);

替换一个已存在的主键：

ALTER TABLE tabelname DROP PRIMARY KEY, ADD PRIMARY KEY (col1, col2);

增加一个唯一键：

ALTER TABLE tablename ADD UNIQUE (col3);
CREATE UNIQUE INDEX index2 ON tablename(col4);

增加一个顺序索引：

ALTER TABLE tablename ADD INDEX (col5);
CREATE INDEX index3 ON tablename (col6);

增加一个函数索引：

ALTER TABLE tablename ADD INDEX ((func(col7)));
CREATE INDEX index4 ON tablename ((func(col8)));

在已存表上删除索引，可以使用如下语句：

ALTER TABLE table DROP PRIMARY KEY;
ALTER TABLE tabel DROP INDEX indexname;
DORP INDEX indexname ON table;

用户可以使用“SHOW CREATE TABLE”语句查看索引的元数据。例如：

 MySQL  localhost:3306 ssl  sakila  SQL > SHOW CREATE TABLE payment\G
*************************** 1. row ***************************
       Table: payment
Create Table: CREATE TABLE `payment` (
  `payment_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` smallint unsigned NOT NULL,
  `staff_id` tinyint unsigned NOT NULL,
  `rental_id` int DEFAULT NULL,
  `amount` decimal(5,2) NOT NULL,
  `payment_date` datetime NOT NULL,
  `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `fk_payment_rental` (`rental_id`),
  CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0063 sec)

也可以通过“SHOW INDEX FROM”语句查看索引的专用信息：

 MySQL  localhost:3306 ssl  sakila  SQL > SHOW INDEX FROM payment\G
*************************** 1. row ***************************
        Table: payment
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: payment_id
    Collation: A
  Cardinality: 16086
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: payment
   Non_unique: 1
     Key_name: idx_fk_staff_id
 Seq_in_index: 1
  Column_name: staff_id
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: payment
   Non_unique: 1
     Key_name: idx_fk_customer_id
 Seq_in_index: 1
  Column_name: customer_id
    Collation: A
  Cardinality: 599
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 4. row ***************************
        Table: payment
   Non_unique: 1
     Key_name: fk_payment_rental
 Seq_in_index: 1
  Column_name: rental_id
    Collation: A
  Cardinality: 16044
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
4 rows in set (0.0112 sec)

用户通过创建索引改善查询性能，例如，查询一个索引字段的值，可以快速地返回包含该值的行，如果查询非索引字段的值，则必须读取全部的行以获取该值。利用索引可以支持以下操作：

直接匹配值：查找字符为“SHENYANG”。
检查是否存在：判断字符“SHENYANG”是否存在。
范围扫描：查询起始字符包含“SHENYANG”的全部字符。

隐藏索引

隐藏索引功能支持用户对优化器隐藏索引，可以帮助测试删除索引后是否对查询性能产生影响，避免实际删除索引后，产生性能问题，再次进行索引重建的开销。使用该功能时，可以在创建/更改表或创建索引语句时加上“INVISIBLE”关键字。

维护InnoDB的索引统计信息

MySQL的优化器利用索引的分布统计信息决定查询时使用的索引及联结顺序，当表中的行超过10%的变更后，会自动更新统计信息。此外，用户也可以通过“ANALYZE TABLE”语句手动更新统计信息。InnoDB将统计信息持久化存储在“mysql.innodb_index_stats”表中。当用户使用“ANALYZE TABLE”语句时，MySQL将分析并存储统计信息，可以令查询选择最佳的索引。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2024-02-19，如有侵权请联系 cloudcommunity@tencent.com 删除

性能