MySQL索引

1、索引是什么

索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。可以理解为“排好序的快速查找数据结构”在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据,这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

2、优势

类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本。通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

3、劣势

①实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。

②虽然索引大大提高了查询速度，同时确会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。

因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。都会调整因为更新所带来的键值变化后的索引信息。 

4、索引的分类

在MySQL中，索引分为两大类：聚簇索引和非聚簇索引。聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引则不同；聚簇索引能够提高多行检索的速度，而非聚簇索引则对单行的检索速度很快。

在这两大类的索引类型下，还可以将索引分成四个小类：

①普通索引：最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。

②唯一索引：与普通索引类型，不同的是唯一索引的列值必须唯一，但允许为空值。

③全文索引：全文索引（FULLTEXT）仅可以适用于InnoDB（MySQL5.6.24支持）和MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。

④组合索引：将几个列作为一条索引进行检索，使用最左匹配原则。

⑤主键索引

大多数MySQL索引（主键索引，唯一索引，全文索引）是存储在Btree结构里

 

5、MySQL索引结构

1）BTree索引

B-Tree索引可以被用在像=，>，>=，<，<=和BETWEEN，!=， <>，IS NULL这些比较操作符上。而且还可以用于LIKE操作符只要它的查询条件是一个不以通配符开头的常量。像下面的语句就可以使用索引

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';

第一条是因为它以通配符开头，第二条是因为没有使用常量。

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

第一条是因为它以通配符开头，第二条是因为没有使用常量。

btree通常意味着所有的值都是按照顺序存储的，并且每一个叶子页到根的距离相同.

下图是innodb索引工作示意图，myisam使用的结构有所不同，但基本思想类似

btree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索，根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找,通过比较节点页的值和要查找的值可以找到合适的指针进入下一层子节点，这些指针实际上定义了子节点页中值的上限和下限，最终存储引擎要么是找到对应的值，要么是该记录不存在。

有时候mysql不会使用索引，即使这个在可用的情况下。例如当mysql预估使用索引会读取大部分的行数据时。（在这种情况下，一次全表扫描可能比使用索引更快，因为它需要更少的检索）。然而，假如语句中使用LIMIT来限定返回的行数，mysql则会使用索引。因为当结果行数较少的情况下使用索引的效率会更高。索引还可以用于查询中的order by操作，一般来说，如果btree可以按照某种方式查找的值，那么也可以按照这种方式用于排序，所以，如果order by子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求

2）Hash索引

而哈希索引的示意图则是这样的

其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端

①Hash 索引仅仅能满足"=","IN"和"<=>"(注意，<>和<=>是不同的操作)查询，不能使用范围查询。

②优化器不能用hash索引来为ORDER BY操作符加速。

③Hash 索引不能利用部分索引键查询。对于联合索引中的多个列，Hash是要么全部使用，要么全部不使用
④Hash 索引在任何时候都不能避免表扫描。
⑤Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

3）full-text全文索引

4）R-Tree索引

 

6、哪些情况需要创建索引

①主键自动建立唯一索引

②频繁作为查询条件的字段应该创建索引

③查询中与其他表关联的字段，外键关系建立索引

④频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引

⑤WHERE条件里用不到的字段不创建索引

⑥单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)

⑦查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度

⑧查询中统计或者分组字段

 

7、哪些情况不要创建索引

①表记录太少

②经常增删改的表

提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。

③注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

④数据变化不大的列。如XX类型，是否有效，状态等

使用索引时，有以下一些技巧和注意事项：
（1）索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
（2）使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
（3）索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
（4）like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
（5）不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:
select * from users where adddate<‘2007-01-01';
（6）不使用NOT IN和<>操作

 

8、mysql索引命中规则

最左匹配原则
当创建(a,b,c)复合索引时，相当于创建了(a)单列索引，(a,b)组合索引以及(a,b,c)组合索引

想要索引生效的话,只能使用a和a,b和a,b,c三种组合,跟字母的顺序没有关系（详见mysql查询优化器）所谓最左前缀原则你可以认为联合索引是闯关游戏的设计:那么a就是第一关 b是第二关， c就是第三关,你必须通过第一关,才能匹配第二关

注：在mysql中执行查询时，只能使用一个索引，如果我们在a,b,c上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。

实例：以下是常见的几个查询：

mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='aa' ;
mysql>SELECT `a`,`b`,`c` FROM A WHERE `b`='bb' AND `a`='cc';
mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='aa' AND `b`='bb' AND `c`='cc';

想要索引最大化的使用需要至少建几个索引

需要建立两个复合索引：a,c、b,c

在用MySQL查询数据库的时候，连接了很多个用，发现非常慢。例如：

SELECT ... WHERE p.languages_id = 1 AND m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1 AND p.products_id IN (472,474)

 这样查询需要20多秒，虽然在各个字段上都建立了索引。用分析Explain SQL一分析，发现在第一次分析过程中就返回了几万条数据：
WHERE p.languages_id = 1 ，然后再依次根据条件，缩小范围。

而我改变一下WHERE 字段的位置之后，速度就有了明显地提高：

WHERE p.products_id IN (472,474) AND p.languages_id = 1 AND m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1

这样，第一次的条件是p.products_id IN (472,474)，它返回的结果只有不到10条，接下来还要根据其它的条件来过滤，自然在速度上有了较大的提升。
经过实践发现，不要以为WHERE中的字段顺序无所谓，可以随便放在哪，应该尽可能地第一次就过滤掉大部分无用的数据，只返回最小范围的数据。 希望能帮到有同样遭遇的朋友。