mysql 创建index 语句

转载

mob64ca14089531 2024-07-07 09:31:27

文章标签 mysql 创建index 语句数据聚簇索引子节点 文章分类 MySQL 数据库

相关基础

基本概念

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
创建索引的方式：

CREATE INDEX index_name ON table(column(n))   #直接创建索引，n个字节的前缀索引
CREATE UNIQUE INDEX indexName ON table(column(length)) #直接创建唯一索引

ALTER TABLE table_name ADD FULLTEXT ( column)   #为表增加全文索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length)) #修改表结构创建索引
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

DROP INDEX index_name ON table  #删除索引

索引的优点：
创建索引可以大大提高系统的性能。

第一，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
在没有索引的情况下，数据库会遍历全部数据后选择符合条件的记录；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。
第二，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

一是增加了数据库的存储空间，若要建立聚簇索引，则所需空间更大。
二是创建和维护索引耗费时间，这种时间随着数据量的增加而增加。
三是当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

索引设计原则

尽量使用短索引。如果对字符串进行索引，应该指定一个前缀长度，比如一个char(200)的列，如果前10个字符多数是唯一的，那就不要对整个列进行索引。较小的索引占用更少的磁盘I/O，比较起来也更快；另外在索引高速缓存中的块能容纳更多的键值，因此就可以读取更少的块找到行。

适合创建索引的列：

在经常需要搜索的列上，可以加快搜索的速度，注意不是select中的列，而是where子句和连接子句中的列；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

不适合创建索引的列：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。
既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。
由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。
这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。
修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。

索引分类

HASH索引和BTREE索引：
存储引擎负责实现索引。
MySQL中索引存储类型有两种：HASH和BTREE。
MyISAM和InnoDB引擎默认创建的都是BTREE索引；
MEMORY引擎默认使用HASH索引，但也支持BTREE索引；只有MyISAM支持全文（FULLTEXT）索引，且只能用在char/varchar/text列上。
HASH索引和BTREE索引的区别：

HASH索引只用于=或<=>操作符的等式比较；而BTREE索引对>、<、>=、<=、BETWEEN、!=或者<>、LIKE ‘pattern’（pattern不以通配符开始）操作符都可以使用相关列上的索引。
HASH索引不能加速ORDER BY操作
HASH索引无法实现范围查询；BTREE索引适合进行全关键字、关键字范围和关键字前缀查询；

理解hash索引和btree索引的底层实现原理，自然能明白它们的区别。

普通索引和唯一索引：

普通索引：基本的索引类型，允许在索引的列中插入重复值和空值；
唯一索引：索引列的值必须唯一，如果为组合索引则列值的组合必须唯一，但允许有空值。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

主键索引：主键索引是特殊的唯一索引，不允许有空值。为表定义主键时将自动创建主键索引。

聚簇索引和非聚簇索引：
聚簇索引是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行（因为数据只有一份，所以一张表只能有一个聚簇索引），数据行存放在叶子页中。
聚簇索引是按照数据存放的物理位置为顺序的，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。
如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。
二级索引（非聚簇索引）需要两次索引查找。因为二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值，所以通过二级索引查找行时存储引擎要先找到二级索引的叶子节点获取对应的主键值，然后根据这个值去聚簇索引中查找对应的行。这意味进行了两次B-Tree查找，InnoDB的自适应哈希可以减少这样的重复操作。
聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。
联合索引和单个索引

索引基础数据结构

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。