MySQL索引

一、索引的定义（索引别称index，key，键）

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引的作用

设置了合适的索引之后，数据库利用各种快速的定位技术，能够大大加快查询速率。特别是当表很大时，或者查询涉及到多个表时，使用索引可使查询加快成干倍。可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本通过创建唯一性索引保证数据表数据的唯一性可以加快表与表之间的连接在使用分组和排序时，可大大减少分组和排序时间

一般情况下，对于查询语句，在没有建立索引的时候，mysql会进行全表扫描，而且不扫描完10W个记录不会停止，如果我在nickname上建立索引，那么mysql相当于只扫描nickname这一列即可，而且因为这一列已排好序，找到对应结果或结果集可以直接返回。

  mysql的索引分为单列索引(全文索引，主键索引，唯一索引，普通索引)和组合索引。

单列索引:一个索引只包含一个列，一个表可以有多个单列索引。

组合索引:一个组合索引包含两个或两个以上的列，

 

 

二、普通索引

    普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。 

2.1、直接创建索引(length表示使用名称前1ength个字符)  

CREATE INDEX index_name ON table_name(column_name(length))  

2.2、修改表结构的方式添加索引  

ALTER TABLE table_name ADD INDEX index_name ON (column_name)  

2.3、创建表的时候同时创建索引  

CREATE TABLE table_name (  

id int(11) NOT NULL AUTO_INCREMENT ,  

title char(255) NOT NULL ,  

PRIMARY KEY (id),  

INDEX index_name (title)  

ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;  

2.4、删除索引  

DROP INDEX index_name ON table_name;  

2.5、建立复合索引 。  

CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);  

2.6、命名时的习惯使用“表名字段1名字段2名”的方式  

 

 

三、唯一索引

    与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。
    如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。 

 

3.1、创建唯一索引  

CREATE UNIQUE INDEX index_name ON table_name(column_name)  

3.2、修改表结构  

ALTER TABLE table_name ADD UNIQUE index_name ON (column_name)  

3.3、创建表的时候直接指定  

CREATE TABLE table_name (  

id int(11) NOT NULL AUTO_INCREMENT ,  

title char(255) NOT NULL ,  

PRIMARY KEY (id),  

UNIQUE index_name (title)  

);  

四、主键索引

必须为主键字段创建一个索引，这个索引就是所谓的”主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。 

主键索引的创建方式：

方式1：ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

比如：ALTER TABLE users ADD PRIMARY KEY ( id )

方式2：创建表的时候指定主键

五、全文索引

文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。 
　　这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建。

5.1、创建表的适合添加全文索引  

CREATE TABLE table_name (  

id int(11) NOT NULL AUTO_INCREMENT ,  

content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL 

PRIMARY KEY (id),  

FULLTEXT (content)  

);  

5.2、修改表结构添加全文索引  

ALTER TABLE table_name ADD FULLTEXT index_name(column_name)  

5.3、直接创建索引  

CREATE FULLTEXT INDEX index_name ON table_name (column_name)  

 

六、查看索引

索引查看

show index from 表名;

show keys from 表名;

例：show index from stu02;

show keys from stu02;

七、删除索引

删除索引
格式：drop index 索引名 on 表名;
或者 alter table 表名 drop index 索引名;

删除主键索引

格式：alter table 表名 drop primary key;

例子：alter table stu02 drop primary key;