MYSQL中的索引

 

参考：https://blog.51cto.com/13689359/2163402

一、概念：

索引(index) 是用来快速地寻找那些具有特定值的记录，主要是为了检索的方便，加快查询速度。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。

如果对MySQL与所了解，应该会知道除了索引外，MySQL中还要主键、外键以及唯一索引，那这些的区别与联系是什么呢？？

1.index与key的区别

  • key有两个作用，1是约束作用（primary key与unique key规范数据的唯一性，foreign key规范数据的引用完整性），2是建立了索引关系
  • index只是建立了索引关系，用于辅助查询。
  • 普通的key与普通的index同义

2.相关概念

    1、主键(primary key) 是能够唯一标识表中某一行的属性或属性组。一个表只能有一个主键，但可以有多个候选索引。主键常常与外键构成参照完整性约束，防止出现数据不一致。主键可以保证记录的唯一和主键域非空,数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。

    2、外键（foreign key） 是用于建立和加强两个表数据之间的链接的一列或多列。外键约束主要用来维护两个表之间数据的一致性。简言之，表的外键就是另一表的主键，外键将两表联系起来。一般情况下，要删除一张表中的主键必须首先要确保其它表中没有外键和它相关联。

    3、唯一索引(unique key ) 所谓唯一性索引，这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。

 
作用
在表中
值是否能为空
与外键的关系
主键 唯一标识表中的某一行数据 只能有一个主键或联合主键 主键或联合主键必须不能为空，且值唯一 可以被其他字段作外键引用
外键 建立和加强两个表数据之间的链接，维护两个表之间数据的一致性 删除一张表中的主键必须首先要确保其它表中的没有相同外键 不为空  
索引 提高数据的检索速度 可以有多个索引 可以为空 不能作为外键引用
唯一索引 唯一标识表中的某一行数据 可以有多个唯一索引 允许空值  

唯一键约束是通过参考索引实施的，如果插入的值均为NULL，则根据索引的原理，全NULL值不被记录在索引上，所以插入全NULL值时，可以有重复的，而其他的则不能插入重复值。 

alter table t add constraint uk_t_1 unique (a,b); 
insert into t (a ,b ) values (null,1);    # 不能重复 
insert into t (a ,b ) values (null,null);#可以重复 

3.MySQL的索引分类总结：

1.普通索引

  • index :加速查找

2.唯一索引

  • 主键索引：primary key ：加速查找+约束（不为空且唯一）
  • 唯一索引：unique key：加速查找+约束 （唯一）

3.联合索引

  • -primary key(id,name):联合主键索引
  • -unique(id,name):联合唯一索引
  • -index(id,name):联合普通索引

4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。



二、实战操作：


1.普通索引


（1）添加索引


    

  • 创建索引，例如: CREATE INDEX <索引的名字> ON tablename (列的列表);
    • 

  • 修改表，例如:  ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表); 
    • 

  • 创建表的时候指定索引，例如:  CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );
    • 



（2）删除索引:   alter table table_name drop index_name;


2.primary key:


主键是唯一的，所以没有主键名的概念。没有创建主键的方式。


（1）添加主键


    

  • 修改表，例如: ALTER TABLE table_name ADD PRIMARY KEY (列的列表); 
    • 

  • 创建表的时候指定主键，例如:  CREATE TABLE `table_name` ( [...], PRIMARY KEY (列的列表)); 
    • 



        常用的主键形式：id int(10) not null primary key auto_increment 


（2）删除主键: alter table table_name drop primary key;




3.唯一索引


这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。


（1）添加唯一索引


    

  • 创建索引，例如: CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表); 
    • 

  • 修改表，例如:ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表); 
    • 

  • 创建表的时候指定索引，例如:CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );
    • 



（2）删除唯一索引: alter table table_name drop index_name;        


4.外键


假设有两张表，表test_result用来保存测试场景和其对应的结果，表test_scene用来保存所有的场景名，这时，两张表就存在一种约束关系(constraint)——test_result表中的测试场景要受表test_scene中的场景名约束。


假设两张表的结构如下：









test_result



test_scene









CREATE TABLE `test_result` (


  `id` int(11) NOT NULL AUTO_INCREMENT,


  `scene` varchar(50) NOT NULL,


  `result` varchar(50) DEFAULT NULL,


  PRIMARY KEY (`id`)


)




CREATE TABLE `test_scene` (


  `id` int(11) NOT NULL AUTO_INCREMENT,


  `Sname` varchar(50) NOT NULL,


  PRIMARY KEY (`id`)


)










该结构只是来说明怎么使用外键，不保证结构的合理性，正常情况下，该情况应该是将test_result表中scene直接替换为test_scene中的id，联表操作即可。


设置索引


　　若要设置外键，在参照表(referencing table，即test_scene表) 和被参照表 (referencedtable，即test_result表) 中，相对应的两个字段必须都设置索引(index)。


　　ALTER TABLE test_scene ADD INDEX idx_Sname (Sname);


　　ALTER TABLE test_result ADD INDEX idx_scene (scene);


　　事实上这两个索引可以在创建表的时候就设置。这里只是为了突出其必要性。　


定义外键


　　因为test_result的scene必须参照test_scene表中的场景名，所以我们将test_result表的scene字段设置为“外键”(FOREIGNKEY)，即这个键的参照值来自于其他表。


ALTER TABLE test_result ADD CONSTRAINT fk_scene_name
FOREIGN KEY (scene)
REFERENCES test_scene(Sname);


　　第一行是说要为test_result表设置外键，给这个外键起一个名字叫做fk_scene_name；第二行是说将本表的scene字段设置为外键；第三行是说这个外键受到的约束来自于test_scene表的Sname字段。


　　这样，我们的外键就可以了。如果我们试着插入一条结果到test_result，如果它所使用的场景名在test_scene中不存在，那么MySQL会禁止这条数据插入。


级联操作


　　考虑以下这种情况：


        如果test_scene中的场景名发生变化，怎样将test_scene中的场景名变化同步到test_result中，使其能够自动更新？？


　    方法是在定义外键的时候，加入ON UPDATE CASCADE;即在主表更新时，子表（们）产生连锁更新动作，即：


ALTER TABLE test_result ADD CONSTRAINT fk_scene_name
FOREIGN KEY (scene)
REFERENCES test_scene(Sname) ON UPDATE CASCADE;


　除了 CASCADE 外，还有 RESTRICT(禁止主表变更)、SET NULL(子表相应字段设置为空)等操作


 


5.单列索引与多列索引（联合索引）

索引可以是单列索引，也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别。假设有这样一个PR表：


需求是获取某个version状态为Closed、优先级是Major的issue_key，SQL命令是select issue_key from test_index_43 where status='Closed' and priority='Major' and version='RDB-3.1.5.11';




由于我们不想让MySQL每次执行查询就去扫描整个表，这里需要考虑运用索引。


考虑在单个列上创建索引，比如status：


MySQL将通过status上的索引迅速把搜索范围限制到那些status='Closed'的记录，然后再在这 个“中间结果集”上进行其他条件的搜索


alter table test_index_43 add index sta (status);


EXPLAIN select issue_key from test_index_43 where priority='Major' and status='Closed' and version='RDB-3.1.5.11';




考虑在在两列上创建索引，比如status，priority：


alter table test_index_43 drop index sta;alter table test_index_43 add index sta_pri(status,priority);




考虑在在三列上创建索引，比如status，priority，version：


由于索引文件以B-树格式保存，MySQL能够立即转到合适的status，然后再转到合适的priority，最后转到合适的version。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录！


alter table test_index_43 drop index sta_pri;alter table test_index_43 add index sta_pri_ver(status,priority,version);




在三列中分别创建索引的效果：


如果在status、priority、version这三个列上分别创建单列索引，效果是否和创建一个多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但即使是限制最严格的单列索引，它的限制能力也肯定低于status、priority、version这三个列上的多列索引。


alter table test_index_43 drop index sta_pri_ver;alter table test_index_43 add index sta(status);alter table test_index_43 add index pri (priority);alter table test_index_43 add index ver(version);


EXPLAIN select issue_key from test_index_43 where status='Closed' and priority='Major' and version='RDB-3.1.5.11';




多列索引还有另外一个优点，它通过称为最左前缀（LeftmostPrefixing）的概念体现出来。继续考虑前面的例子，现在我们有一个status，priority，version列上的多列索引，我们称这个索引为sta_pri_ver。当搜索条件是以下各种列的组合时，MySQL将使用sta_pri_ver索引：










status，priority，version| status，priority| status










从另一方面理解，它相当于我们创建了(status，priority，version)、(status，priority)以及(status)这些列组合上的索引。下面这些查询都能够使用这个sta_pri_ver索引：




下边的不能使用sta_pri_ver索引：




 


三、其他内容


当我们在desc 表名; 的时候,有一个Key值，表示该列是否含有索引


假设表结构如下所示
mysql> desc aa;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
| xx    | int(11) | YES  | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
| yy    | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
| zz    | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)


我们看到Key那一栏，可能会有4种值，即'啥也没有','PRI','UNI','MUL'


MySQL Key值（PRI, UNI, MUL）的含义：


PRI主键约束；


UNI唯一约束；


MUL可以重复。


1. 如果Key是空的, 那么该列值的可以重复，表示该列没有索引, 或者是一个非唯一的复合索引的非前导列
2. 如果Key是PRI,  那么该列是主键的组成部分
3. 如果Key是UNI,  那么该列是一个唯一值索引的第一列(前导列)，且不能含有空值(NULL)
4. 如果Key是MUL,  那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL


注：
1、如果对于一个列的定义，同时满足上述4种情况的多种，比如一个列既是PRI，又是UNI（如果是PRI，则一定是UNI）
那么"desc 表名"; 的时候，显示的Key值按照优先级来显示 PRI->UNI->MUL
那么此时，显示PRI。
2、如果某列不能含有空值，同时该表没有主键，则一个唯一性索引列可以显示为PRI，
3、如果多列构成了一个唯一性复合索引，那么一个唯一性索引列可以显示为MUL。（因为虽然索引的多列组合是唯一的，比如ID+NAME是唯一的，但是每一个单独的列依然可以有重复的值，因为只要ID+NAME是唯一的即可）




 






		

		
posted @ 
2019-11-08 16:29 
声声慢43 
阅读(295) 
评论(0) 
编辑 
收藏 
举报