开发者学堂课程【MySQL 实操课程：MySQL 下索引的分类(二)】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/717/detail/12822
MySQL 下索引的分类（二）
内容介绍
一、主键索引
二、前缀索引
三、全文索引
一、主键索引
主键索引就是如下图创建表中的主键，具有如下特性：
1、定义
（1）自增。不允许重复，不能为空值。
（2）一般是表的主键 id ;
（3）又叫聚集索引，每张表中有且仅有一个主键，可以由表中一个或多个字段组成。
（4）主键索引必须满足的条件:主键值必须唯一 ;不能包含 Null 值，这点与前面讲解过的唯一性索引不同。该值是自增的。
（5）使用自增列作为主键，可以保证写入数据的顺序也是自增的，在很大程度上提高存取效率。
2、演示
下面进行主键索引的创建演示
首先创建表 user，school，school1 定义属性如下，如：
mysql> show create table user;
mysql> show create table school;
mysql> show create table school1;
mysql> create table school1 (id int (11)auto_increment primary key, name varchar (20) ) ;
然后进行查看表 school1 可以看到，主键 PRIMARY KEY 属性为自增，name 长度为20，如：
mysql> show create table school1;
…
| school1 | CREATE TABLE ‘school1’
‘id’ int(11) NOT NULL AUTO INCREMENT,
‘name’ varchar (20) DEFAULT NULL,
PRIMARY KEY (‘id’)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+--------------------------+
1 row in set (0.00 sec)
此时查看 school1 表单，可以发现之前并没有创建索引，而这里自动创建了索引，列是id，列表名是 school1，Key 类型为 PRIMARY，这里说明了 mysql 数据库的一个原则，就是当创建了一个表时，如果里面有一个自增并且是主键的一个列，那么就会自动的在主键的自增列道上增加一个主键索引，如：
mysql> show indexes from school1;
Table | Non_unique | Key_name | Seq in index…
school1 | 0 | PRIMARY | 1…
1 row in set(0.00 sec)
再向表中插入如下几条数据，这里没有复制 id，因为 id 是属于自增的，如：
mysql> insert into school1 (name) values ( ' yizhong') ;
Query OK, 1 row affected (0.00 sec)
mysql> insert into school1 (name) values ( ' erzhongzhong') ;
Query OK, 1 row affected (0.01 sec)
mysql> insert into school1 (name) values ( ' sanzhong') ;
Query OK, 1 row affected (0.00 sec)
数据插入成功，下面进行查看 id=1 的数据，发现自动进行的主键的索引，如：
mysql> explain select from school1 where id=1;
+----+------------+-------+-----------+-----+--------------+---…
| id | select_type | table | partitions | type | possible keys | key…
| 1| SIMPLE |school1| NULL |const| PRIMARY |PRI…
+----+------------+-------+-----------+-----+--------------+---…
1 row in set, 1 warning (0.00 sec)
这就是主键索引的用法，所以在设计表的时候尽量去设置主键面，也叫技术主键，用来表示主键，不具有任何业务含义。因为这里 name 没有加索引，所以如果查询name 是不会进行索引的。
二、前缀索引
1、定义
abc 建立索引相当于 a，ab，abc 建立索引。
前缀索引主要是为了高效，比如说有些字段属于字符串型的，字符串长度比较长，如果给整个字符串都建立索引，会增加索引存储的开销，也会降低检索的效率，所以引发了对字符串前几个字符进行建立索引的思考，就是前缀索引。
2、演示
下面进行演示。
创建表 company，设置属性，如：
mysql> create table company (id int (11)，name varchar (50));
Query OK, 0 rows affected (0.01 sec)
前缀索引的关键词还是 index，没有其他的修饰符，插入 name 数据 idx_name 并将表名为 company 中的 name 字段设置为5，插入成功后进行查看，如：
mysql> create index idx_name on company (name (5));
Query OK，0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 warnings:
mysql> show indexes from company;
| Table | Non_unique | Key_name | Seq… | Sub part…
| company | 1 | idx_name | 1… | 5…
1 row in set (0.00 sec)
向表中写入数据，如下:
mysql> insert into company values (1001, 'ibm') ;
Query OK，1 row affected (0.00 sec)
mysql> insert into company values (1002, ' microsoft') ;
Query OK, 1 row affected (0.00 sec)
mysql> insert into company values (1003, 'oracle');
Query OK, 1 row affected (0.00 sec)
然后查看数据是否插入成功。插入成功后进行查询，可以发现，查询的数据正确，并且呈现形式相同。
mysql> select * from company where name= ' ibm ' ;
…
mysql> explain select from company where name= ' ibm' ;
+----+------------+-------+-----------+-----+--------------+---…
| id | select_type | table | partitions | type | possible keys | key…
| 1| SIMPLE |company| NULL |ref | idx_name |8…
+----+------------+-------+-----------+-----+--------------+---…
1 row in set, 1 warning (0.00 sec)
mysql> explain select*from company where name ' microsoft' ;
+----+------------+-------+-----------+-----+--------------+---…
| id | select_type | table | partitions | type | possible keys | key…
| 1| SIMPLE |company| NULL |ref | idx_name |8…
+----+------------+-------+-----------+-----+--------------+---…
1 row in set, 1 warning (0.00 sec)
写成下面语法形式也是可以的，如：
mysql> explain select*from company where name like ' micro%' ;
+----+------------+-------+-----------+-----+--------------+---…
| id | select_type | table | partitions | type | possible keys | key…
| 1| SIMPLE |company| NULL |range| idx_name |8…
+----+------------+-------+-----------+-----+--------------+---…
1 row in set, 1 warning (0.00 sec)
mysql> explain select*from company where name like ' micros%' ;
…
这里like语句与之前的形式相比只是 type 值改变，从 ref 变为 range，所以这两种查询写法效果是一样的，以上就是前缀索引，可以通过前缀，快速的匹配出数据，从而提高检索的效率。比如插入数据，然后进行查找 micros 字段数据，rows 扫描数据为两条：
mysql> insert into company values (1004, ' microalibaba') ;
Query OK, 1 row affected (0.00 sec)
mysql> explain select from company where name like 'micros&';
+----+------------+-------+-----------+-----+--------------+---…
| id | select_type | table | partitions | type | …| rows…
| 1| SIMPLE |company| NULL |range| …| 2…
+----+------------+-------+-----------+-----+--------------+---…
1 row in set, 1 warning (0.00 sec)
这就是前缀索引的功能，主要是为了节省存储空间，就是如果不想占用太大的存储空间，又想使用这个索引，那么就可以这样做，前面也讲过，如果不用索引的话就可以删除，如:
mysql>show indexes from company;
…
mysql> drop index idx_name on company;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings : 0
删除后再进行查看，可以看到扫描为4行，type 为 ALL：
mysql> explain select*from company where name like ' microt %' ;
+----+------------+-------+-----------+-----+--------------+---…
| id | select_type | table | partitions | type | …| rows…
| 1| SIMPLE |company| NULL | ALL | …| 4…
+----+------------+-------+-----------+-----+--------------+---…
1 row in set, 1 warning (0.00 sec)
所以对于不用的索引要及时的删除，因为在更新这个表的时候会进行维护索引，要负责索引做更新，这是他的特点，应该将他删除。
三、全文索引
1、定义
对于全文索引，什么是全文索引，主要解决什么问题，首先要了解在很多情况下使用 like 关键字或者正则表达式去进行模糊查询的时候，会进行对表，进行全表的扫描，就像前后都有百分号，如: ' %microt %'，这种情况的检索效率很低，所以可以通过全文索引去提高字符串的检索效率。简单来说，MySQL 中的全文索引，主要是用来使用一种特定的分值计数，而在利用查询的关键字和查询的字段内容相关度进行检索，通过全文索引可以提高文本匹配速度，所以需要注意是根据全文检索的关键值和查询内容字段进行相关度的匹配。就比如一些浏览器输入关键字，然后根据关键字快速的检索结果。
(1)分词。全文索引首先会进行分词，对于要检索的文本一个段落，或者一篇文章，首先会进行切分，然后根据切分后的关键词进行匹配。
(2)FULLTEXT 索引仅可用于 MyISAM 表;就是早期 FULLTEXT 索引只支持 MyISAM表，但是在5.6的版本开始支持 Innodb 全文索引。
(3)可以从 CHAR、VARCHAR 或 TEXT 列中作为 CREATE TABLE 语句的一部分被创建,或是随后使用 ALTER TABLE 或 CREATE INDEX 被添加。
2、演示
下面可以看一个例子：
首先创建一个表，如：
mysql> create table book (
-> isbn char (20) primary key,
-> name char (100) not null,
-> brief int roduction text not null，
-> price decimal (6,2)，
-> publish time date not null,
-> unique index isbn_unique (isbn)，
-> index name_index (name (20) )，
-> index complex index (price , publish time)
-> );
Query OK, 0 rows affected (0.04 sec)
然后写入数据，如：mysql> insert into book (isbn, name,brief_introduction, price, publish time)
-> insert into book_noindex(isbn, name,brief_introduction，price, publish time) values
-> (‘978-7-115-25626-3’…
…
然后进行查询：
mysql> select*from book;
| isbn | name | brief_introduction | price | publish_time |
…
可以查看到插入了三条数据，此时再进行查询，首先会想到使用like进行查询，如下查询 practices，可以看到查询数据为一条：mysql> select * from book where name like '%practices%' or brief_introduction like ' %practices%'
…
| 978-7-115-25626-3 |PHP Fundamentals & Practices | Web Database Applications MySQL offers web developers a mixture of theoretical and practical information on creating web database applications. | 42. 00 | 2012-07-01|
…
然后进行 explain 详解，可以看到扫描了全表的行，所以是进行了全表的扫描，这种做法是不建议的，如：
mysql> explain select * from book where name like '%practices%' or brief_introduction like ' %practices%'
| id | select_type | table | partitions | type | …| rows…
| 1| SIMPLE |book | NULL | ALL | …| 3…
而如果要不加%就要可以利用全文索引，如下 match 中是字段，加上关键词against：
mysq1> select*from book where match(name,brief _introduction) against( practices') ;
ERROR 1191 (HY000) : Can't find FULLTEXT index matching the column list
此时会出现报错为没有全文索引，因为前面没有创建全文索引，所以要进行创建，指令如下：
mysql> create fulltext index brief fulltext on book (name, brief int roduction) ;
Query OK, 0 rows affected, 1 warning (0.19 sec)
Records: 0 Duplicates:0 Warnings: 1
然后再次进行查询则查询成功，并且 explain 详解显示为使用的是全文索引，并且扫描行数为一行，如：
mysql> explain select*from book where match(name,brief _introduction) against( 'practices') ;
| id | select_type | table | partitions | type | …| rows…
| 1| SIMPLE |book | NULL | fulltext | …| 1…
下面进行查询 mysql 关键字也是相同：
mysql> select*from book where match(name,brief _introduction) against( 'mysql') ;
…
可以看到3条数据均被查询出。
以上就是全文索引的用法。
