MySQL索引

一、索引

1、索引定义
    索引是数据库中专门用于帮助用户快速查询数据的一种数据结构。索引需要单独保存在索引文件中。
类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。
优点：可以提高检索数据的速度
缺点：创建和维护索引需要耗费时间

2、索引分类
（1）普通索引：仅加速查询
（2）唯一索引：加速查询 + 列值唯一（可能有null）
（3）主键索引：加速查询 + 列值唯一 + 表中只有一个（不可以有null）
（4）组合索引：多列值组成一个索引，其效率大于索引合并
（5）全文索引：对文本的内容进行分词搜索

3、如何创建/删除索引
（1）创建表的时候创建索引

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件]，
，，，,
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY[索引名称](字段名称[(长度)])
[ASC|DESC]
);

（2）在已存在的表上创建索引
创建索引时如果是BLOB和TEXT类型，必须指定length

(1) CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 ON 表名{字段名称[(长度)] [ASC|DESC]};
(2) ALTER TABLE tbl_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX索引名称(字段名称[(长度)][ASC|DESC]);

（3）删除索引

DROP INDEX 索引名称 ON tbl_name

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name),  // 创建普通索引
    unique ix_name (name), // 创建唯一索引
    primary key(nid),      // 创建主键
)

// 创建普通/唯一索引
create [unique] index 索引名 on 表名(列名);
// 创建主键
alter table 表名 add primary key(列名);
// 创建组合索引
create index 索引名 on 表名(列名1, 列名2);
列名1 and 列名2  --- 使用索引
列名1            --- 使用索引
列名2            --- 不使用索引

// 删除普通/唯一索引
drop [unique] index 索引名 on 表名
// 删除主键
alter table 表名 drop primary key

二、explain + 查询SQL
用于显示SQL执行信息参数，根据参数信息进行SQL优化

mysql> explain select * from t_users where Fuser_id=2000;
+----+-------------+-----------------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table                 | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+-----------------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t_users               | ref  | Fuser_id      | Fuser_id | 8       | const |    7 | Using index condition |
+----+-------------+-----------------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

参数解释
1、id：查询顺序标识
2、select_type：查询类型
	SIMPLE          简单查询
	PRIMARY         最外层查询
	SUBQUERY        映射为子查询
	DERIVED         子查询
	UNION           联合
	UNION RESULT    使用联合的结果
	...
3、table：正在访问的表名
4、type：查询时的访问方式
    性能 all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
all：全表扫描，对于数据表从头到尾找一遍
     select * from tb1;
index：全索引扫描，对索引从头到尾找一遍
       select nid from tb1;
range：对索引列进行范围查找
       select * from tb1 where name < 'alex';
index_merge：合并索引，使用多个单列索引搜索
             select * from tb1 where name = 'alex' or nid in (11,22,33);
ref：根据索引查找一个或多个值 
     select * from tb1 where name = 'seven';
eq_ref：连接时使用primary key 或 unique类型 
        select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
const：常量 表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
        select nid from tb1 where nid = 2 ;
system：系统 表仅有一行(=系统表)。这是const联接类型的一个特例。
        select * from (select nid from tb1 where nid = 1) as A;
5、possible_keys：可能使用的索引
6、key：真实使用的索引
7、key_len：mysql中使用索引字节长度
8、rows：mysql估计为了找到所需的行而要读取的行数（只是预估值）
9、extra：该列包含mysql解决查询的详细信息
"Using index"
	此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
"Using where"
	这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，
	因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
"Using temporary"
	这意味着mysql在对查询结果排序时会使用一个临时表。
"Using filesort"
	这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，
	explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
"Range checked for each record(index map: N)"

三、聚集索引、辅助索引、覆盖索引、联合索引
1、聚集索引
按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的行记录数据。
聚集索引的叶子节点称为数据页，每个数据页通过一个双向链表来进行链接，而且数据页按照主键的顺序进行排序。
如果定义了主键，InnoDB会自动使用主键来创建聚集索引；
如果没有定义主键，InnoDB会选择一个唯一的非空索引代替主键；
如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚集索引。

2、辅助索引
也叫非聚集索引，和聚集索引相比，叶子节点中并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点的索引行还包含了一个书签(bookmark),
该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。

3、覆盖索引
如果一个索引包含(或覆盖)所有需要查询的字段的值，称为覆盖索引，即只需要扫描索引而无须回表。
InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。
优点：
（1）可以减少大量的IO操作，select * from t_users where Fuser_id=2000;
如果要查询辅助索引中不含有的字段，得先遍历辅助索引，再遍历聚集索引，
而如果要查询的字段值在辅助索引上，就不用再查询聚集索引了，这显然会减少IO操作。
（2）有助于统计，select count(1) from t_users;

4、联合索引
联合索引是指对表上的多个列进行索引。
联合索引也是一棵B+树，其键值数量大于等于2。键值都是排序的，通过叶子节点可以逻辑上顺序的读出所有数据。
数据（1,1）（1,2）（2,1）（2,4）（3,1）（3,2）是按照（a，b）先比较a再比较b的顺序排列。

下列查询使用联合索引
select * from table where a=xxx and b=xxx;
select * from table where a=xxx;
下面查询不能使用联合索引
select * from table where b=xxx;