MySQL创建索引

索引是创建在表上的，对数据库表中一列或多列的值进行排序的一种结构

其作用主要在于提高查询的速度，降低数据库系统的性能开销

通过索引，查询数据不必读完记录的全部信息进行匹配，而是只查询索引列

索引相当于字典中的音序表，要查询某字时可以在音序表中找到

然后直接跳转到那一音序所在位置，而不必从字典第一页开始翻，逐字匹配

tips：索引虽能提高查询速度，但在插入记录时会按照索引进行排序，因此降低了插入速度

　 　  最好的操作方式是先删除索引，插入大量记录后再创建索引

索引分类

1.普通索引：不附加任何限制条件，可创建在任何数据类型中

2.唯一性索引：使用unique参数可以设置索引为唯一性索引，在创建索引时，限制该索引的值必须唯一，主键就是一种唯一性索引

3.全文索引：使用fulltext参数可以设置索引为全文索引。全文索引只能创建在char、varchar或text类型的字段上。查询数据量较大的字符串类型字段时，效果明显。但只有MyISAM存储引擎支持全文检索

4.单列索引：在表中单个字段上创建的索引，单列索引可以是任何类型，只要保证索引只对应一个一个字段

5.多列索引：在表中多个字段上创建的索引，该索引指向创建时对应的多个字段

6.空间索引：使用spatial参数可以设置索引为空间索引，空间索引只能建立在空间数据类型上比如geometry，并且不能为空，目前只有MyISAM存储引擎支持

在创建表时创建索引
创建普通索引

mysql> create table index1(
    -> id int,
    -> name varchar(20),
    -> sex boolean,
    -> index(id)
    -> );
Query OK, 0 rows affected (0.11 sec)

此处在id字段上创建索引，show create table可查看

创建唯一性索引

mysql> create table index2(
    -> id int unique,
    -> name varchar(20),
    -> unique index index2_id(id ASC)
    -> );
Query OK, 0 rows affected (0.12 sec)

此处使用id字段创建了一个名为index2_id的索引

这里的id字段可以不设置唯一性约束，但这样一来索引就没有作用

创建全文索引

mysql> create table index3(
    -> id int,
    -> info varchar(20),
    -> fulltext index index3_info(info)
    -> )engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)

要注意创建全文索引时只能使用MyISAM存储引擎

创建单列索引

mysql> create table index4(
    -> id int,
    -> subject varchar(30),
    -> index index4_st(subject(10))
    -> );
Query OK, 0 rows affected (0.12 sec)

此处subject字段长度是30,而索引长度则是10

这么做的目的在于提高查询速度，对于字符型的数据不用查询全部信息

创建多列索引

mysql> create table index5(
    -> id int,
    -> name varchar(20),
    -> sex char(4),
    -> index index5_ns(name,sex)
    -> );
Query OK, 0 rows affected (0.10 sec)

可以看出，这里使用了name字段和sex字段创建索引列

创建空间索引

mysql> create table index6(
    -> id int,
    -> space geometry not null,
    -> spatial index index6_sp(space)
    -> )engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)

这里需要注意空间space字段不能为空，还有存储引擎

在已经存在的表上创建索引
创建普通索引

mysql> create index index7_id on example0(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

这里在现有表的id字段上创建了一条名为index7_id的索引

创建唯一性索引

mysql> create unique index index8_id on example1(course_id);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

此处只需要在index关键字前面加上unique即可

至于表中的course_id字段，最要也设置唯一性约束条件

创建全文索引

mysql> create fulltext index index9_info on example2(info);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

fulltext关键字用来设置全文引擎，此处的表必须是MyISAM存储引擎

创建单列索引

mysql> create index index10_addr on example3(address(4));
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

此表中address字段的长度是20,这里只查询4字节，不需要全部查询

创建多列索引

mysql> create index index11_na on example4(name,address);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引创建好之后，查询中必须有name字段才能使用

创建空间索引

mysql> create spatial index index12_line on example5(space);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

这里需要注意存储引擎是MyISAM，还有空间数据类型

用alter table语句来创建索引
创建普通索引

mysql> alter table example6 add index index13_n(name(20));
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建唯一性索引

mysql> alter table example7 add unique index index14_id(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 

创建全文索引

mysql> alter table example8 add fulltext index index15_info(info);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建单列索引

mysql> alter table example9 add index index16_addr(address(4));
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建多列索引

mysql> alter table example10 add index index17_in(id,name);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建空间索引

mysql> alter table example11 add spatial index index18_space(space);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

到此，三种操作方式，每种索引类别的建立就都列举了

对于索引，重要的是理解索引的概念，明白索引的种类

更多的是自己的使用经验

最后来看看索引的删除

删除索引

mysql> drop index index18_space on example11;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

这里是刚刚创建的一条索引

其中index18_space是索引名，example11是表名

mysql索引最左匹配原则的理解

这是你的表结构，有三个字段，分别是id,name,cid

CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `cid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name_cid_INX` (`name`,`cid`),) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

索引方面：id是主键，(name,cid)是一个多列索引。

-----------------------------------------------------------------------------

下面是你有疑问的两个查询：

 

EXPLAIN SELECT * FROM student WHERE cid=1;

 

EXPLAIN SELECT * FROM student WHERE cid=1 AND name='小红';

 

 

你的疑问是：sql查询用到索引的条件是必须要遵守最左前缀原则，为什么上面两个查询还能用到索引？

---------------------------------------------------------------------------------------------------------------------------

 

讲上面问题之前，我先补充一些知识，因为我觉得你对索引理解是狭隘的：

上述你的两个查询的explain结果中显示用到索引的情况类型是不一样的。,可观察explain结果中的type字段。你的查询中分别是：

1. type: index

2. type: ref

 

解释：

index：这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个复合索引的一部分，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。

 

所以：对于你的第一条语句：

EXPLAIN SELECT * FROM student WHERE cid=1;

判断条件是cid=1,而cid是(name,cid)复合索引的一部分，没有问题，可以进行index类型的索引扫描方式。explain显示结果使用到了索引，是index类型的方式。

 

---------------------------------------------------------------------------------------------------------------------------

 

ref：这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一 一的扫描判断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。

 

 

有些了解的人可能会问，索引不都是一个有序排列的数据结构么。不过答案说的还不够完善，那只是针对单个索引，而复合索引的情况有些同学可能就不太了解了。

 

下面就说下复合索引：

以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的：

mysql创建复合索引的规则是首先会对复合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。

 

所以：第一个name字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个cid字段进行条件判断是用不到索引的，当然，可能会出现上面的使用index类型的索引。这就是所谓的mysql为什么要强调最左前缀原则的原因。

 

那么什么时候才能用到呢?

当然是cid字段的索引数据也是有序的情况下才能使用咯，什么时候才是有序的呢？观察可知，当然是在name字段是等值匹配的情况下，cid才是有序的。发现没有，观察两个name名字为 c 的cid字段是不是有序的呢。从上往下分别是4 5。

这也就是mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因。（而且第一个索引必须是等值匹配）。

---------------------------------------------------------------------------------------------------------------------------

所以对于你的这条sql查询：

EXPLAIN SELECT * FROM student WHERE cid=1 AND name='小红';

没有错，而且复合索引中的两个索引字段都能很好的利用到了！因为语句中最左面的name字段进行了等值匹配，所以cid是有序的，也可以利用到索引了。

 

你可能会问：我建的索引是(name,cid)。而我查询的语句是cid=1 AND name='小红'; 我是先查询cid，再查询name的，不是先从最左面查的呀？

 

好吧，我再解释一下这个问题：首先可以肯定的是把条件判断反过来变成这样 name='小红' and cid=1; 最后所查询的结果是一样的。

那么问题产生了？既然结果是一样的，到底以何种顺序的查询方式最好呢？

 

所以，而此时那就是我们的mysql查询优化器该登场了，mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以mysql查询优化器会最终以这种顺序进行查询执行。