mysql索引学习

一、索引类型

1、唯一索引（主键）

建表语句：

CREATE TABLE `person` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

2、普通索引

建表语句：

CREATE TABLE `person` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY (`NAME`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

添加索引：

CREATE INDEX `NAME` ON person(NAME)

3、全文索引

建表语句：

CREATE TABLE `person` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  `INTRODUCE` varchar(100),
  PRIMARY KEY (`ID`),
  KEY (`NAME`),
  FULLTEXT KEY (`INTRODUCE`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

添加索引：

CREATE FULLTEXT INDEX `INTRODUCE` ON person(INTRODUCE);

4、组合索引

建表语句：

CREATE TABLE `person` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  `INTRODUCE` varchar(100) DEFAULT NULL,
  `AGE` int(2) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `NAME` (`NAME`) USING BTREE,
  KEY `NAME_AGE` (`NAME`,`AGE`) USING BTREE,
  FULLTEXT KEY `INTRODUCE` (`INTRODUCE`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

添加索引：

CREATE INDEX `NAME_AGE` ON person(NAME,AGE)

二、索引原理

1、数据结构

学习索引原理之前，先了解一些概念：BTREE、B-TREE、B+TREE、B*TREE：

1）BTREE

BTREE即为二叉搜索树（二叉查询树等），基本结构：

• 所有非叶子结点至多拥有两个儿子（Left和Right）；
• 所有结点存储一个关键字；
• 非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树；

2）B-TREE

B-TREE是一种多路搜索树（并不是二叉的），基本结构（D=3）：

• D为大于1的一个正整数，称为B-Tree的度；
• H为一个正整数，称为B-Tree的高度；
• 根结点的key数为[2, D]；
• 每个非叶子结点最多只有D个key；且D>2；
• 每个叶子节点最少包含一个key，最多包含2D-1个key，叶节点的指针均为null
• 指针数等于key数+1

它的特性是关键字集合分布在整颗树中，任何一个关键字出现且只出现在一个结点中，这样搜索有可能在非叶子结点结束。

3）B+TREE

B+树是B-树的变体，也是一种多路搜索树，基本结构：

其定义基本与B-TREE相同，除了非叶子结点的子树指针与关键字个数相同；

它的特性是所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的，索引不可能在非叶子结点命中。

4）B*TREE

是B+树的变体，在B+树的非根和非叶子结点再增加指向兄弟的指针。

2、索引原理

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。也就是说：索引是一种数据结构。

1）MyISAM的索引

MyISAM引擎使用B+Tree作为索引结构，叶结点的data域存放的是数据记录的地址，所以说MyISAM的索引文件仅仅保存数据记录的地址，它的数据和索引文件是分开的。在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，取出地址值，读取相应数据记录，而地址值不是按照索引顺序分布的，MyISAM的索引方式也叫做非聚集索引。

2）InnoDB索引

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。第一个重大区别是InnoDB的数据文件本身就是索引文件，这棵树的叶结点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

InnoDB的这种索引叫做聚集索引，因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键。

3）非聚集索引和聚集索引

从上面了解，聚集索引就像字典中的拼音搜索页，拼音的顺序与页码的顺序一直；非聚集索引就像字典中的偏旁部首搜索页，偏旁部首的顺序并不与页码顺序对应。所以这也就应对了MyISAM和InnoDB存储引擎的优缺点，InnoDB可以直接根据索引迅速找到硬盘上存储位置进行INSERT或UPDATE，所以说InnoDB更适用大量的存储和更新操作。

三、索引的使用及注意事项

1、EXPLAIN用法和结果的含义

• select_type：SELECT 查询的类型
• table：显示这一行的数据是关于哪张表的
• type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一
• possible_keys：此次查询中可能选用的索引
• key：此次查询中确切使用到的索引
• key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好
• ref：显示使用哪个列或常数与key一起从表中选择行
• rows：显示MySQL认为它执行查询时必须检查的行数
• extra: 额外的信息

其中type从好到坏的排序：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

2、不走索引的sql

1）涉及到函数运算的，包括字符串操作：

/*不使用索引*/
EXPLAIN SELECT * FROM student WHERE CONCAT(nickname,'ss') = '小明ss';
EXPLAIN SELECT * FROM student WHERE age + 3 = 26
/*使用索引*/
EXPLAIN SELECT * FROM student WHERE nickname = '小明';
EXPLAIN SELECT * FROM student WHERE age = 23;

2）like的使用

/*不使用索引*/
EXPLAIN SELECT * FROM student WHERE nickname LIKE '%小明%';
/*使用索引*/
EXPLAIN SELECT * FROM student WHERE nickname LIKE '小明%';
EXPLAIN SELECT * FROM student WHERE nickname LIKE '小明';

3）数字向字符串的隐式转换

/*使用索引*/
EXPLAIN SELECT * FROM student WHERE grade = 027021;
/*不使用索引*/
EXPLAIN SELECT * FROM student WHERE grade = '027021';
EXPLAIN SELECT * FROM student WHERE age = '23';
EXPLAIN SELECT * FROM student WHERE age = 23;

这里grade字段是数字的字符串类型，age是数字类型，当027021数字类型向字符串类型进行隐式转换的时候，没有走索引，而‘23’转为数字时候是可以走索引的。

4）条件判断

EXPLAIN SELECT * FROM student WHERE nickname='小明' AND age = 23;
EXPLAIN SELECT * FROM student WHERE nickname='小明' OR age = 20; 

and和or的分析：

使用and时，只要其中有条件带索引，都会使用到索引来查找；而or的使用会比较复杂，当前后条件中只有一个带有索引的话，是不会使用索引的；当前后条件都带有索引的话，需要针对查询的结果进行分析，当查询出的结果较多的时候不会使用到索引，而查询出的结果较少时候，会使用到索引。

EXPLAIN SELECT s.*,t.* FROM student s,teacher t WHERE t.name = s.nickname AND s.nickname = '小明';
EXPLAIN SELECT s.*,t.* FROM student s,teacher t WHERE t.name = s.nickname

当多表联查使用where and作为条件判断的时候，如果条件中都带有索引，并且有一个精准的定位（s.nickname = ‘小明’）会使用到索引；但是如果没有精准定位的话会比较复杂，需要针对sql进行判断，包括查询出来的是哪几列。

EXPLAIN SELECT * FROM teacher t LEFT JOIN student s ON t.name = s.nickname;

当多表联查使用左（又）连接作为条件判断的时候，只会使用到副表的索引，如例子中的student表如果nickname字段是索引，就会使用到索引。但是主表还是不会使用到索引，因为它需要查询所有字段。

5）!=、=、IN、NOT IN、BETWEEN

/*使用索引*/
EXPLAIN SELECT * FROM student WHERE GRADE = '27000';
EXPLAIN SELECT * FROM student WHERE GRADE IN ('27001','27002');
EXPLAIN SELECT * FROM student WHERE GRADE BETWEEN '27001' AND '27002';
/*不使用索引*/
EXPLAIN SELECT * FROM student WHERE GRADE != '27000';
EXPLAIN SELECT * FROM student WHERE GRADE NOT IN ('27001','27002');
EXPLAIN SELECT * FROM student WHERE GRADE NOT BETWEEN '27001' AND '27002';

6) IN，EXISTS

EXPLAIN SELECT * FROM student WHERE NICKNAME IN (SELECT NAME FROM sys_user);
EXPLAIN SELECT * FROM student WHERE EXISTS (SELECT NAME FROM sys_user WHERE NAME = student.NICKNAME);

比较一下两种方法：

IN的结果显示，虽然都用到了连个表的索引列，但是student的效率更高，sys_user的效率非常低；EXISTS的结果显示，只要用到了sys_user的索引列，而且效率很高，没有使用student的索引列。所以到底使用IN还是EXISTS要结合表的大小来定，两者不一定谁的效率会更高，如果子查询（sys_user）的表更大，使用EXISTS，相反使用IN。

3、联合索引

当搜索条件使用到联合索引的时候，最高的效率是多个条件都用到了联合索引，其次是只用联合索引中的第一个索引，而使用联合索引第二位及其后面的索引作为条件（没有使用联合索引的第一个索引），就不会走索引了。

四、其他sql优化

1、 使用LIMIT 1

当确定只需要一条数据的时候，使用LIMIT 1，这样在查询到一条数据的时候可以直接返回，不再向下继续搜索。

2、不使用SELECT *

从数据库里读出越多的数据，那么查询就会变得越慢，而且增加数据传输的负担。

3、NULL的使用

建表时候字符串类型不要选择默认NULL，因为NULL也会占用内存，增加开销，可以设置为空串，因为空串不会占用内存。

4、固定长度的表会更快

固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。