MySQL索引

prz0590

已于 2022-11-21 08:55:32 修改

阅读量1.9k

点赞数

文章标签： mysql 数据库

于 2022-11-03 14:11:19 首次发布

本文链接：https://blog.csdn.net/qq_34125713/article/details/127668815

版权

概念

1、主键(primary key)能够唯一标识表中某一行的属性或属性组。一个表只能有一个主键，但可以有多个候选索引。主键常常与外键构成参照完整性约束，防止出现数据不一致。主键可以保证记录的唯一和主键域非空，数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。主键的唯一作用就是唯一标识表中的某一行数据。
2、外键(foreign key)是用于建立和加强两个表数据之间的链接的一列或多列。外键约束主要用来维护两个表之间数据的一致性。简言之，表的外键就是另一表的主键，外键将两表联系起来。一般情况下，要删除一张表中的主键必须首先要确保其它表中的没有相同外键(即该表中的主键没有一个外键和它相关联)。
3、索引(index)是用来快速地寻找那些具有特定值的记录。主要是为了检索的方便，是为了加快访问速度，按一定的规则创建的，一般起到排序作用。所谓唯一性索引，这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。索引的作用就是提高数据的检索速度。

一、常用索引类型：

1). 普通索引
最基本的索引，它没有任何限制，用于加速查询。

2). 唯一索引
索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

3). 主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

4). 多列索引（最左前缀原则）
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

# 最左前缀原则:
mysql建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：
如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；
如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；
当对多列创建索引后，并不是只要包含了创建索引的列就能使用索引，索引的使用要遵循最左前缀匹配原则。
假设对列(A, B, C)创建索引，那么只有以下场景能使用索引：
对列(A, B, C)/(A, C)或者(A, B)进行查询会匹配索引，对(C, A)或者(B, C)来说不能使用索引。
通配符只能使用LIKE 'val%'形式，不能使用LIKE '%VAL%'，后者会导致全表扫描。
索引列不能进行运算，例如WHERE A + 1 = 5这种场景会导致索引失效。
索引列不能包含范围值查询，如LIKE/BETWEEN/>/<等都会导致后面的列无法匹配索引。
索引列不能包含有NULL值。

5). 全文索引
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。
（在MySQL 5.6版本以前,只有MyISAM存储引擎支持全文引擎。在5.6版本中,InnoDB加入了对全文索引的支持,但是不支持中文全文索引.在5.7.6版本,MySQL内置了ngram全文解析器,用来支持亚洲语种的分词。主要用来利用关键词查询文本，不是MySQL的主要面向场景，使用较少，这里就不展开讨论了。）
fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。
fulltext索引配合match against操作使用，而不是一般的where语句加like。
它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。

二、索引设计原则（什么情况需要索引）

1、对查询次数频次较高，且数据量较大的表建立索引，MySQL把同一个数据表里的索引总数限制为16个。

2、索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

3、使用唯一索引，区分度高、使用效率越高。

4、索引可以有效的提升查询数据的效率 , 但索引数量并不是多多益善 , 索引越多 , 维护索引的代价自然也就水涨船高。对于插入, 更新, 删除等 DML 操作比较繁琐的表来说 , 索引过多 , 会引入相当高的维护代价 , 降低 DML 操作的效率 , 增加相应操作的时间消耗 , 另外索引过多的话 , MySQL也会犯选择困难症 , 虽然最终仍然会找到一个可用的索引 , 但无疑提高了索引的代价。

5、使用段索引 , 索引创建之后也是使用硬盘来存储的 , 因此提高索引访问的 I/O 效率 , 也可以提高总体的访问效率。假如构成索引的字段总长度比较短 , 那么在给定大小的存储块内 , 可以存储更多的索引值 , 相应的可以有效地提升MySQL访问索引的 I/O 效率。

6、利用最左前缀的原则 , N个列组合而成的组合索引 , 那么相当于是创建了N 个索引。如果查询时where 子句使用了组成该索引的前几个字段 , 那么这条查询SQL可以利用组合索引来提升查询效率。

7、索引不是万能的！索引可以加快数据检索操作，但会使数据修改操作变慢。每修改数据记录，索引就必须刷新一次。为了在某种程序上弥补这一缺陷，许多SQL命令都有一个DELAY_KEY_WRITE项。这个选项的作用是暂时制止MySQL在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。在需要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE选项的作用将非常明显。另外，索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。

8、如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

9、表记录行数较少或者经常插入、删除、修改的表不应创建索引。

三、创建索引（其中两种方法）

1) 建表后创建索引
ALTER table_name ADD INDEX [indexName] ON (column(length))
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

# 1.添加PRIMARY KEY(主键索引)
mysql> ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );
# 2.添加UNIQUE(唯一索引)
mysql> ALTER TABLE `table_name` ADD UNIQUE (`column`);
# 3.添加INDEX(普通索引)
mysql> ALTER TABLE `table_name` ADD INDEX index_name ( `column` );
# 4.添加FULLTEXT(全文索引)
mysql> ALTER TABLE `table_name` ADD FULLTEXT ( `column`);
# 5.添加多列索引（联合索引）
mysql> ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` );
# 6.删除索引
mysql> DROP INDEX <索引名|index_name> ON <表名|table_name>
mysql> DROP INDEX sampleId ON sampleInfo;
mysql> DROP PRIMARY KEY ON <表名>    # 表示删除表中的主键。一个表只有一个主键，主键也是索引。
mysql> DROP FOREIGN KEY fk_symbol ON <表名|table_name>    # 表示删除外键，外键也是索引。
# 7.修改索引
在mysql中，索引无法直接修改，可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。
# 8.查看索引
mysql> SHOW INDEX FROM `table_name`;

2)建表时创建索引

CREATE TABLE tableName(  
  id INT NOT NULL,   
  columnName  columnType,
  KEY|INDEX [indexName] (columnName(length))  
);
# 可以在 第四行 INDEX 前面添加限定条件，使其成为普通索引，唯一索引，全文索引等。
# 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
# 实例：将id设为主键索引，userId设为普通索引，索引名为idx_userid，name设为唯一索引，索引名u_name 
CREATE TABLE user ( 
  id int(11) NOT NULL AUTO_INCREMENT, 
  userId varchar(32) NOT NULL, 
  age varchar(16) NOT NULL, name varchar(255) NOT NULL, 
  PRIMARY KEY (id),
  KEY idx_userid (userId),
  UNIQUE INDEX u_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

索引与主键关系

主键一定是索引，但是索引不一定是主键。
一个表只能有一个主键或联合主键，但是可以有多个索引。
主键字段必须不能为空，但是索引字段可以为空。
主键一定是唯一性索引，唯一性索引并不一定就是主键。
一个表中可以有多个唯一性索引，但只能有一个主键。
主键列不允许空值，而唯一性索引列允许空值。
主键可以被其他字段作外键引用，而索引不能作为外键引用。