【MySQL】 MySQL 索引的创建与维护

推荐阅读

• 学习笔记 《 深入理解 Java 虚拟机》
• 学习笔记 《 后端架构设计》
• 学习笔记 《 Java 基础知识进阶》
• 学习笔记 《 Nginx 学习笔记》
• 学习笔记 《 前端开发杂记》
• 学习笔记 《 设计模式学习笔记》
• 学习笔记 《 DevOps 最佳实践指南》
• 学习笔记 《 Netty 入门与实战》
• 学习笔记 《 高性能MYSQL》
• 学习笔记 《 JavaEE 常用框架》
• 学习笔记 《 Java 并发编程学习笔记》
• 学习笔记 《 分布式系统》
• 学习笔记 《 数据结构与算法》

---

1、索引的创建

创建索引可以在创建表的时候同步创建，也可以在创建表之后，手动的创建索引，两者没有本质的区别。
比如下面的语句就是在创建表的时候创建索引。

CREATE TABLE `customer` (
  `customer_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `store_id` tinyint(3) unsigned NOT NULL,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  PRIMARY KEY (`customer_id`),
  KEY `idx_fk_store_id` (`store_id`),
  KEY `idx_last_name` (`last_name`),
);

通过 `show index from table_name` 可以查询到某个表的索引信息

-- 查询索引
SHOW INDEX FROM customer;

***************************[ 1. row ]***************************
Table         | customer
Non_unique    | 0
Key_name      | PRIMARY
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>
***************************[ 2. row ]***************************
Table         | customer
Non_unique    | 1
Key_name      | idx_last_name
Seq_in_index  | 1
Column_name   | last_name
Collation     | A
Cardinality   | 599
Sub_part      | <null>
Packed        | <null>
Null          |
Index_type    | BTREE
Comment       |
Index_comment |
Visible       | YES
Expression    | <null>

同样的，也可以使用 CREATE INDEX 语法创建索引，语法的格式如下所示

> CREATE INDEX `idx_test_index` ON customer(first_name,last_name);

事实上，还有索引配置以及锁的配置没有提到，这里读者可以参考 MySQL8.0 的官方文档 https://dev.mysql.com/doc/refman/8.0/en/create-index.html 关于索引的创建的详细介绍

2、索引的更新与删除

-- 重命名索引
ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name

-- 删除所用
DROP INDEX `idx_test_index` ON customer;

4、索引总结

1. 针对查询频次高，数据量大的表创建索引，比如只新增就不必要创建索引了
2. 针对在WHERE 字句中出现的字段创建索引
3. 尽量使用唯一索引，区分度越高，检索速度越高
4. 索引并不是多多益善，因为在更新索引的时候对索引的维护也需要成本
5. 尽量使用短字段索引，比如长字符串，使用前10个字符创建索引
6. 合理的利用最左匹配原则，避免重复创建索引
7. 对于数据量非常的大的表，需要注意的是创建索引可能需要一些时间