MySQL索引总结：索引的原理、索引的分类与语法、索引的设计

索引的原理

索引的分类

索引的语法

索引的设计

索引的原理

创建索引是在数据库系统中建立一个复杂的数据结构（如B+树），这个数据结构包含了指向数据表中记录的引用或指针，用于加速查询操作。

在MySQL中，InnoDB存储引擎主要使用B+Tree作为其索引结构。基于以下原因：

搜索效率高：相比于二叉树，B+树的层级更少，这意味着在查询数据时，从根节点到叶子节点的路径更短，因此搜索效率更高。

磁盘读写能力强：B+树的根节点和非叶子节点只作为索引，不保存数据区，因此能存储更多的索引信息。这意味着每次磁盘加载（IO操作）能获取到更多的关键字，从而减少了磁盘IO次数。

排序和范围查询能力强：由于B+树的叶子节点之间有指针相连，且数据在叶子节点中是有序的，因此B+树天然具备排序能力，且非常适合范围查询。

索引的分类

1. 主键索引（PRIMARY KEY）

主键是一种唯一性索引，用于唯一标识表中的每一行记录。每个表只能有一个主键。在创建表时定义或在已存在的表上添加。

2. 唯一索引（UNIQUE）

索引列的所有值都只能出现一次，即必须唯一，值可以为空。

3. 普通索引（INDEX）

最基本的索引类型，没有唯一性的限制，值可以为空。

4. 全文索引（FULLTEXT）

全文索引主要用于全文搜索，可以在VARCHAR、CHAR、TEXT类型的列上创建。

需要注意的是，MyISAM存储引擎在较早的MySQL版本中支持全文索引，而InnoDB从MySQL 5.6版本开始也支持了全文索引。

5. 空间索引（R-tree）

主要用于地理空间数据类型，例如地理坐标数据。通常使用较少。

索引的语法

创建索引的基本语法如下：




-- 创建表时创建主键索引




CREATE TABLE users (  



    id INT NOT NULL,  



    username VARCHAR(50) NOT NULL,  



    email VARCHAR(100),  



    PRIMARY KEY (id)  



);



-- 其他索引




CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name  




ON table_name (column1, column2, ...);

或者，使用ALTER TABLE语句添加索引：





ALTER TABLE table_name  




ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name (column1, column2, ...);

删除索引的语法如下：





ALTER TABLE table_name  




DROP INDEX index_name;

索引的设计

为了发挥索引的优势并达到最佳效果，设计索引时需要考虑多个因素。以下是一些关键的建议和最佳实践：

1. 选择正确的索引列

优先为经常出现在WHERE子句、JOIN操作、ORDER BY子句中的列创建索引。
选择具有高选择性的列（即列中不同值的比例较高）作为索引列，这有助于数据库更快速地定位到相关行。
避免为具有大量重复值的列创建索引，因为这样的索引可能不会被有效利用。

2. 使用唯一索引

如果某列的值是唯一的，或者业务规则要求该列的值必须是唯一的，那么应该为该列创建唯一索引。
唯一索引不仅有助于快速查找数据，还可以确保数据的完整性。

3. 使用复合索引

如果查询条件经常同时涉及多个列，可以考虑创建复合索引（即包含多个列的索引）。
复合索引的列顺序很重要，将经常用于查询条件的列放在联合索引的前面，以利用最左匹配原则。

最左匹配原则是针对联合索引而言的。在联合索引中，查询条件只有从最左边的列开始匹配，索引才能被有效利用。如果查询条件没有使用最左边的列，即使后面的列在索引中存在，也无法使用索引来优化查询。这种原则的目的是为了提高查询性能。

举个例子，假设有一个联合索引(a,b,c)

若查询条件为a = 1 and b = 2 and c > 3。由于查询条件从最左边的列a开始，并且连续匹配了索引中的列，因此索引可以被有效利用。

若查询条件为b = 2 and a = 1 and c > 3。尽管查询条件中包含了索引的所有列，但由于没有从最左边的列开始匹配，所以索引不会被使用。

此外，最左匹配原则在遇到范围查询时（如 >、<、between、like）会停止匹配。也就是说，如果查询条件中某个列使用了范围查询，那么该列之后的列都无法使用索引。

若查询条件为 a = 1 AND b > 2 AND c = 3，虽然查询条件仍然从最左边的列a开始，但在b列上使用了范围查询（b > 2）。因此，索引在b列之后停止匹配，即使c列的值在查询条件中指定了，c列的索引也不会被使用。

4. 考虑索引的长度

对于字符串类型的列，不要对整个列创建索引，而是考虑只对该列的前几个字符创建索引。这可以节省存储空间并提高查询性能。
使用前缀索引时要小心，确保前缀长度足够区分不同的值。
如果完整索引的大小和性能成为问题，前缀索引可以是一个很好的解决方案。这在某些类型的项目中可能是常见的，比如日志记录、文本搜索或者某些具有大量长字符串数据的场景。但在其他项目中可能并不常见。

5. 避免过度索引

每个额外的索引都会占用磁盘空间并可能降低写操作的性能（如INSERT、UPDATE和DELETE）。
定期审查和优化索引，删除不再需要的索引。

秒客网

MySQL索引总结：索引的原理、索引的分类与语法、索引的设计

索引的原理

索引的分类

索引的语法

索引的设计

相关文章