MySQL数据库索引

一：索引的特点

作用：索引可以大大提高系统检索的性能。
优点：

• 通过创建唯一性索引，可以保证数据库中每一行数据的唯一性；
• 可以大大加快数据的检索速度，这也是创建索引的最主要原因；
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面很有意义；
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

• 创建索引和维护索引要耗费时间，时间随着数据量的增加而增加；
• 索引需要占用物理空间，除了数据表占数据空间外，每一个索引还要占一定的物理空间，如果建立聚簇索引（值在索引下边），需要的空间更大；
• 当对表中的 数据进行增加、删除和修改时，索引也要动态的维护，这样就降低了数据的维护速度。

索引分为：

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

索引结构
MySQL索引的数据结构是B-tree（B树），但是innodb存储引擎的MySQL的索引数据结构是B+树，并且innodb的主键索引是聚簇索引（主键下就是索引值），速度更快。
B树和二叉树的区别是B树有很多结点，二叉树至多有2个结点；而B树和B+树的区别是B+树的叶子结点之间有指向关系。

B树有n个key,m=n+1,称为m阶树，每个结点最多有m个子结点。
例：4阶B-tree树的插入：

创建索引

创建主键索引:

• 在创建表的时候，直接在字段名后指定 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);

主键索引的特点：

• 一个表中，最多有一个主键索引，包含复合主键（alter table user3 add primary key(id,name)；(id,name)就是符合主键）；
• 主键索引的效率高（主键不可以重复）；
• 创建主键索引的列，值不能为null,而且不能重复；
• 主键索引的列基本为int。
• 主键索引是一级索引

创建唯一键索引

• 在表定义时，在某列后直接指定unique唯一属性。
  create table user4(id int primary key, name varchar(30) unique);
• 创建表时，在表的后面指定某列或某几列为unique
  create table user5(id int primary key, name varchar(30), unique(name));
• create table user6(id int primary key, name varchar(30)）；
  alter table user6 add unique(name);

唯一键索引特点：

• 一个表中，可以有多个唯一索引；
• 查询效率高；
• 某列建立建立唯一索引后，该列数据不能重复；
• 若唯一索引指定not null,不等价于主键索引，因为唯一索引是二级索引，即通过唯一索引检索时，先检索唯一索引，然后再检索主键索引。

创建普通索引（index）

• create table user(id int primary key,
  name varchar(20),
  index(name) --在表的定义最后，指定某列为索引
  );

• create table user(id int primary key, name varchar(20),
  alter table user9 add index(name); --创建完表以后指定某列为普通索引

• create table user(id int primary key, name varchar(20), email varchar(30));
  – 创建一个索引名为 idx_name 的索引
  create index idx_name on user(name);

普通索引的特点：

• 索引的名字在整个DB是唯一的，尽管两张表，也要保证唯一；
• 普通索引和主键索引、唯一索引相比，可以重复。如果某列需要创建索引，但是该列有重复的值，就可以使用普通索引。

全文索引的创建
当对文章字段或大量文字的字段进行检索时，会用到全文索引。MySQL提供全文索引机制，表的存储引擎可以是innodb和MyISAM,默认的全文索引支持英文，不支持中文。

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

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 ...');

可以用explain查看是否用到全文索引：

mysql> explain select * from articles where body like '%database%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: articles
type: ALL
possible_keys: NULL
key: NULL <== key为null表示没有用到索引
key_len: NULL
ref: NULL
rows: 6
Extra: Using where
1 row in set (0.00 sec)

使用全文索引：

mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('database');

用explain查看是否用到全文索引：

mysql> explain SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: articles
type: fulltext     全文索引
possible_keys: title
key: title <= key用到了title
key_len: 0
ref:
rows: 1
Extra: Using where

查询索引：

• show keys from 表名
• show index from 表名;
• desc 表名；

删除索引

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