MySQL -- 索引 一 基本概念

MySQL 评论

数据库索引的概念

常见索引类型

MySQL常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

普通索引：最基本的索引，没有任何限制。
唯一索引：与”普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引：它是一种特殊的唯一索引，不允许有空值，在一张表中只能定义一个主键索引。
主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。主键分为复合主键和联合主键。
全文索引：针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

索引存储结构

B+树：B+树数据结构以平衡树的形式来组织，因为是树型结构，所以更适合用来处理排序，范围查找等功能。
相对 Hash ，B+树在查找单条记录的速度虽然比不上 Hash ，但是因为更适合排序等操作，所以他更受用户的欢迎。
毕竟不可能只对数据库进行单条记录的操作.

Hash：Hash 用的较少，它是把数据的索引以 Hash 形式组织起来，因此当查找某一条记录，速度非常快。
缺点是 Hash 结构，每个键只对应一个值，分布方式是散列的。所以 Hash 并不支持范围查找和排序等功能.

数据库索引的增删改

创建索引

  1. 执行 CREATE TABLE 语句时可以创建索引, 见MySQL从入门到放弃 – 语法1:数据库表操作。

  2. 使用 ALTER TABLE 语句时可以创建索引

    1
    2
    3
    4
    mysql>ALTER TABLE table_name ADD INDEX index_name (column_list) COMMENT '普通索引';
    mysql>ALTER TABLE table_name ADD UNIQUE index_name (column_list) COMMENT '唯一索引';
    mysql>ALTER TABLE table_name ADD PRIMARY KEY (column_list) COMMENT '主键';
    mysql>ALTER TABLE table_name ADD FULLTEXT index_name (column_list) COMMENT '全文索引';

  3. 使用 CREATE INDEX 语句时可以创建索引
    注: 不能用CREATE INDEX语句创建PRIMARY KEY索引

    1
    2
    3
    mysql>CREATE INDEX index_name ON table_name (column_list) COMMENT '普通索引';
    mysql>CREATE UNIQUE INDEX index_name ON table_name (column_list) COMMENT '唯一索引;
    mysql>CREATE FULLTEXT INDEX index_name ON table_name (column_list) COMMENT '全文索引';

删除索引

如删除多列组合索引的某列，则该列也会从索引中删除。
如删除组合索引的所有列，则整个索引将被删除。

只使用 DORP 关键字

1
mysql>DROP INDEX 索引名 ON 表名 列名;

使用 ALTER DORP 两个关键字

1
2
3
4
5
6
mysql>ALTER TABLE 表名 DROP INDEX 索引名 列名;
mysql>ALTER TABLE 表名 DROP UNIQUE 索引名 列名;

// 因为一个表只可能有一个PRIMARY KEY索引，因此也可不指定索引名。
// 如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
mysql>ALTER TABLE 表名 DROP PRIMARY KEY 索引名 列名;

重建索引

长时间运行数据库后，索引有可能被损坏，这时就需要重建。重建索引可以提高检索效率。

1
mysql>REPAIR TABLE table_name QUICK;

查询索引

使用 INDEX 关键字

1
mysql>SHOW INDEX FROM table_name;

使用 KEYS 关键字

1
mysql>SHOW KEYS FROM table_name;