MySQL之索引（索引的原理、常见索引的使用、索引创建原则）

1.索引

1.1 索引原理

索引(Index)是帮助MySQL高效获取数据的数据结构。

索引是用来快速寻找具有特定值的，索引的底层使用B+树来实现。

未创建索引时，执行MySQL查询时，会从第一个记录开始扫描整个表的所有记录，直到查找到符合要求的记录，表中的记录越多，查询的代价就越大。

但是作为搜索条件的列上已经创建了索引，MySQL查询时不再扫描任何记录便可迅速得到目标记录所在的位置；大大提高了海量数据的检索速度。

索引虽然能提高查询速度，但同时也有其他影响：

• 占用磁盘空间
• 当对表中的数据进行增删改的操作时，处理要维护数据表，还有维护二叉树，对速度会有一定的影响
• 创建一个索引并不能解决所有的查询问题，需要分别给字段建立索引，创建索引和维护索引都需要耗费时间，耗费的时间会随着数据量的增加而增加
• 索引是以空间来换时间

1.2 常见索引

• 主键索引（primary key）
• 唯一索引（unique）
• 普通索引（index）
• 全文索引（fulltext）

1.3.1创建主键索引

• 第一种方式：
  在创建表的时候，直接在字段名后指定primary key：

create table user1(id int primary key,name varchar(30));

• 第二种方式：
  在创建表的最后，指定某列或者某几列为主键索引：

create table user2(id int ,name varchar(30),primary key(id));

• 第三种方式：
  创建表后再添加主键索引：

create table user3(id int,name varchar(30));
alter table user3 add primary key(id);

主键索引的特点：

• 一个表中，最多有一个主键索引（可以使用复合主键）
• 主键索引的效率高（主键不可重复）
• 创建主键索引的列，它的值不能为null，且不能重复
• 主键索引的列基本上是int

1.3.2创建唯一索引

唯一索引的字段为 unique，创建方式与主键索引一致，也有三种方法来创建。

唯一索引的特点：

• 一个表中可以有多个唯一索引
• 查询效率很高
• 如果某一列建立唯一索引，必须保证这列不能有重复元素
• 如果一个唯一索引上指定 not null，等价于主键索引

1.3.3普通索引的创建

• 第一种方式：
  在表的最后定义，指定某列为索引：

create table user4(id int primary key,name varchar(20),index(name));

• 第二种方式：
  创建完表以后指定某列为普通索引：

create table user5(id int primary key,name varchar(20));
alter table user5 add index(name);

• 第三种方式：

create table user6(id int primary key,name varchar(20),email varchar(30));

建表完成后，创建一个索引名为idx_name的索引：

create index idx_name on user6(name);

普通索引的特点：

• 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
• 如果某列需要创建索引，但是该列有重复的值，那么此时应该使用普通索引

1.3.4全文索引的创建

当对文章字段或右大量文字的字段进行检索时，会使用到全文索引。

MySQL提供全文索引机制，但是要求表的存储引擎必须MyISAM，而且默认的全文索引支持英文，不支持中文。

如果要对中文进行全文检索，可以使用sphinx的中文版（coreseek）。

创建表：

 CREATE TABLE articles (    
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,    
        title VARCHAR(200),  
        body TEXT,    
        FULLTEXT (title,body) 
        )engine=MyISAM;

插入数据：

INSERT INTO articles (title,body) VALUES    
('MySQL Tutorial','DBMS stands for DataBase ...'),    
('How To Use MySQL Well','After you went through a ...'),    
('Optimizing MySQL','In this tutorial we will show ...'),    
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),    
('MySQL vs. YourSQL','In the following database comparison ...'),    
('MySQL Security','When configured properly, MySQL ...');

查询有没有database数据：

mysql>  select * from articles where body like '%database%';
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.03 sec)

使用explain工具看一下是否使用索引：

mysql>  explain select * from articles where body like '%database%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

key为null，表示没有使用索引

使用全文索引：

mysql>  SELECT * FROM articles  WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.01 sec)

通过explain查询：

mysql>  explain SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles
   partitions: NULL
         type: fulltext
possible_keys: title
          key: title
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

key=title，表示key用到了title

1.4 查询索引

• 第一种：show keys from 表名
• 第二种：show index from 表名
• 第三种：desc 表名

1.5 删除索引

1. 删除主键索引：alter table 表名 drop primary key;
2. 其他索引的删除：alter table 表名 drop index 索引名
   索引名就是show keys from 表名中的Key_name字段
3. 删除其他索引：drop index 索引名 on 表名

1.6 索引总结

优点：提高查询效率

缺点：增删慢，索引文件需要更新，增加空间需求

索引创建原则：

• 比较频繁作为查询条件的字段应该创建索引
• 唯一性太差的字段不适合单独创建索引，及时频繁作为查询条件
• 更新非常频繁的字段不适合创建索引
• 不会出现在where子句中的字段不该创建索引