MySQL索引
查阅书籍及文章，做下记录吧，方便熟悉，以免忘了。
索引
- 索引是什么？能帮助我们解决什么问题？
索引是对数据库的一列或多列的值进行排序定位的存储结构，索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针解决大数据下的快速查询。就像是书的目录一样，通过索引查找到正文的页数。
1. 索引结构方法
B-Tree
按顺序存贮数据。
- 索引的最左列查找，若不是，无法使用索引。如:'like name=%Ja%'。
- 若查询中有某一列的范围查询，则最右边所有列都不能使用索引优化查找。如：WHERE last_name = 'Smith' and first_name LIKE 'J%' AND dob='1976-12-23',like是一个范围查找，前两列可使用索引查找，后面列无法使用索引。
- 不能跳过索引中的列。
hash index
哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对每行数据，存贮引擎都会对所有的索引列计算一个哈希码（较小值）。哈希索引将所有的哈希码存储到索引中，同时在哈希表中保存指向的数据行的指针。
R-Tree(空间索引)
和B-Tree不同，这类索引无需前缀查询。空间索引会从所有维度来索引数据，查询时可以使用任意维度组合查询。 MyISAM支持空间索引，可用作地理数据存贮。
全文索引
全文索引是一种特殊类型的索引，查找的文本中的关键词，而不是直接比较索引中的值。
在相同列上同事建全文索引和B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通通的WHERE条件操作。
Mysql5.6版本之后InnoDB存储引擎开始支持全文索引
2. 索引类型
B+tree索引分为聚集索引与非聚集索引两大类。
理解聚集索引和非聚集索引可通过对比汉语字典的索引。汉语字典提供了两类检索汉字的方式，第一类是拼音检索（前提是知道该汉字读音），比如拼音为cheng的汉字排在拼音chang的汉字后面，根据拼音找到对应汉字的页码（因为按拼音排序，二分查找很快就能定位），这就是我们通常所说的字典序；第二类是部首笔画检索，根据笔画找到对应汉字，查到汉字对应的页码。拼音检索就是聚集索引，因为存储的记录（数据库中是行数据、字典中是汉字的详情记录）是按照该索引排序的；笔画索引，虽然笔画相同的字在笔画索引中相邻，但是实际存储页码却不相邻。
聚集索引
并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚集索引实际是在同一个结构中保存了B-Tree索引和数据行。
一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。
数据行的物理顺序与列值（一般是主键的那一列,mysql默认主键就是聚集索引）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
特性：
MySQL如果定义主键，那么主键就是聚集索引；
MySQL如果没有定义主键，表中第一个唯一非空索引就作为聚集索引；
MySQL如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增；
使用UUID聚集索引,主键值是乱序的，B-Tree的页数分行，重新排列，频繁的页分裂，变得复杂。
MySQL的索引分为B+树索引和hash索引与全文索引。MyISAM数据库引擎和InnoDB数据库引擎的索引都是基于B+树的。
MyISAM、InnoDB引擎、Memory三个常用引擎类型比较
| 索引 | MyISAM引擎 | InnoDB引擎 | Memory引擎 |
|---|---|---|---|
| B-Tree 索引 | 支持 | 支持 | 支持 |
| HASH 索引 | 不支持 | 支持 | 支持 |
| R-Tree 索引 | 支持 | 不支持 | 不支持 |
| Full-text 索引 | 不支持 | 暂不支持 | 不支持 |
非聚集索引
一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
聚集索引与非聚集索引区别
- 聚集索引一个表只存在一个，而非聚集索引一个表可以存在多个；
- 聚集索引的叶节点是最终的数据节点，而非聚集索引（二级索引）的叶节点仍然是索引节点，但它有一个指向最终数据的指针；所以获取数据速度聚集索引比较快。（二级索引子节点保存不是指向行的物理位置的指针，而是行的主键值。）
- 使用聚集索引来做查询操作时速度很快，但是做插入操作较为费事；
- InnoDB支持聚集索引，MyISAM不支持聚集索引。
- 更新聚集索引的列代价很高，InnoDB会强制将每一个更新的行移动到新的位置；
1). 唯一索引
数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
#创建唯一索引
ALTER TABLE table_name ADD UNIQUE (column);
#创建唯一组合索引
ALTER TABLE table_name ADD UNIQUE (column1,column2);
2). 主键索引
不允许有空值的唯一索引。(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)
3). 全文索引
是目前搜索引擎使用的一种关键技术。
ALTER TABLE table_name ADD FULLTEXT (column);
4). 普通索引
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
ALTER TABLE table_name add INDEX (column)
5). 组合索引
在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合
ALTER TABLE table_name INDEX ( column1,column2 )
6). 空间索引
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。
7). 前缀索引
有时候需要索引很长的字符列，这会让索引变得大且慢。索引开始的部分字符，大大节约索引空间，提高索引效率。
ALTER TABLE table_name ADD INDEX(column(prefix_length));
3. 索引优点
- 大大减少了服务器需要扫描的数量；
- 可以帮助服务器避免排序和临时表；
- 可以将随机I/O变成顺序I/O；
4. 索引策略
避免重复索引（列建多个索引）
独立的列
指索引列不能是表达式的一部分或是某个函数的参数。如：
SELECT * FROM user WHERE age+1=23;
前缀索引和索引选择性
计算合适的前缀长度，并使前缀的选择性接近于完整列的选择性。对于BLOB、TEXT、VARCHAR这些类型的列，必须使用前缀索引，mysql不允许索引这些列的完整长度。
选择合适的索引列顺序
对于联合索引，如何决定索引字段的顺序：
- 选择性和基数的经验法则：将选择性最高的列放到索引最前列（业务上常用的，如：某个网站根据不同性别，展示不同内容的 sex肯定要作为组合索引前列）；
- WHERE子句中的排序、分组、范围条件等因素。（明显缩短查找范围）
选择性
索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：
Index Selectivity = Cardinality / #T
显然选择性的取值范围为(0,1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。
如图：
覆盖索引
索引查找数据高效的方式，MySQL也可以根据索引直接获取到数据，这样就不比再去读取数据行。如果一个索引包含了所需要查询字段的值，我们就叫做覆盖索引。（减少了二次查询）
举例：学生成绩表(StudentScore)包含字段：id、username、score、subject；索引 clustered index(id) index(username)
#非聚集索引节点直接获取列数据
SELECT id,username FROM StudentScore WHERE username='小米'
SELECT username FROM StudentScore WHERE username='小米'
#二次查询
SELECT id,username,score FROM StudentScore WHERE username='小米'
冗余和重复索引
MySQL允许在同一列建多个索引。
重复索引是在相同的列上按照相同的顺序创建相同类型的索引。
冗余索引 如创建index(A,B) ,又创建index(A)。大多数情况不需要冗余索引，应尽量扩展已有索引，而不是创建新索引。
优化
- 索引查询字段类型一致；
- 最左原则；
- 最左前缀；
- 避免多个范围条件;
- 优化排序；
- 尽量使用覆盖索引；
4. 索引和锁
索引可以让查询锁定更少的行。
InnoDB在访问行的时候才会对其加锁，索引减少InnoDB访问的行数，从而减少锁的数量，减少锁争用。
索引操作
添加索引：alter table table_name add index index_name(fileds(length);
查看索引：show index from table_name;
删除索引：drop index index_name ON table_name;
模拟：
create table test(
a varchar(32) default '',
b varchar(32) default '',
c int(11) default 0,
d char(64) default ''
)
index union_index(a,b,c) 联合索引实际生成a、ab、abc索引，遵循最左原则。
a，ab，ba（查询优化器会交换），abc，acb(cb交换) 可以命中索引；
b,c,bc,cb 无法命中索引。
此时对a加索引index a(a)
explain select * from test where a='1' ;
+----+-------------+-------+------------+------+-----------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | union_index,a | union_index | 99 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-----------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
可以看到索引会命中到 联合索引union_index上。
此时删除union_index(a,b,c)，重新添加索引union_index(a,b,c)
mysql> alter table test add index union_index(a,b,c);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from test where a='1' ;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | a,union_index | a | 99 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
对abc查询
mysql> explain select * from test where a='1' and b='2' and c=1 ;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | a,union_index | a | 99 | const | 1 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
可以看到索引会命中到 索引a上。故此推断出，索引的添加顺序影响到索引的命中顺序，也就是说会优先从索引a上查找，如果颠倒union_index和a的添加顺序，会命中union_index。
注意：索引字段类型查询值不一致，无法命中索引；
explain通过key_len、ref属性，可以推测下联合索引命中的字段。
explain列解释：
select_type 查询中每个select子句的类型
(1) SIMPLE(简单SELECT，不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
type ：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：MYSQL认为必须检查的用来返回请求数据的行数
filtered:按表条件过滤的行百分比
Extra：关于MYSQL如何解析查询的额外信息
Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
No tables used：Query语句中使用from dual 或不含任何from子句
参考：
《高性能MySQL》书籍
