MySQL索引

MySQL索引

1：索引是什么？

官方介绍索引是帮助MySQL 高效获取数据的数据结构。通俗来讲，数据库索引好比一本书的目录，能加快数据库的查询速度。
一般来说，索引本身也很大，不可能全部存储在内存中，因此，索引往往是存储在磁盘上的文件中的。
我们通常所说的索引，没有特别说明，都是指的B树（多路搜索树，并不一定是二叉结构）结构组织的索引。其中聚集索引、次要索引、覆盖索引、符合索引、前缀索引、唯一索引默认都是使用B+树索引。

2：索引的优势和劣势？
1）优势：
类似于书的目录，可以提高数据检索的效率，降低数据库的IO成本。
通过索引列队数据进行排序，降低数据排序的成本，降低了cpu的消耗。
2）劣势：
索引会占据磁盘空间，也会降低更新表的效率。
比如：每次对表进行增删改操作，MySQL不仅要保存数据，还要保存或者更新对应的索引文件。

索引的分类

1. 单列索引

2. 组合索引 表中多个字段组合上创建的索引。
   2.1 创建组合索引语句 ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col1’,‘col2’,…)
   索引使用过程中需要遵循最左前缀原则（最左匹配原则）
   2.2 最左前缀原则 指的是在where 字句中一些条件或表达式中穿行的列的顺序要保持和复合索引列的一致或以复合索引顺序出现，如果是非顺序出现、断层 都无法使用到复合索引。
   为了更多的提高MySQL效率可建立组合索引，建索引遵循“最左前缀” 原则。创建复合索引应该将最常用的限制条件的列放在最左边，依次递减 组合索引最左字段用in是可以用到索引的
   2.3 MySQL创建组合索引的规则首先会对组合索引最左边的，也就是第一个字段就行排序，在第一个字段的排序的基础上，然后在对后面的第二个字段进行排序。其实相当于实现了类似 order by col1,col2这样的一种排序规则

   注意MySQL中一次select语句，只会使用一个索引

3. 全文索引

4. 空间索引

索引的存储结构

索引是在存储引擎中实现的,也就是说不同的存储引擎，会使用不同的索引
MYISAM和InnoDB存储引擎：只支持BTREE索引。

1. 非聚集索引 MYISAM
   叶子节点只会存储数据行（数据文件中）的指针，简单来说数据和索引不在一起就是非聚集索引。
   主键索引和辅助索引都会存储指针的值
   MYISAM数据文件：
   .frm文件：主要存放与表相关的数据信息，主要包括表结构定义的信息
   .myd文件：主要用来存储表数据信息 d:data
   .myi文件：主要用来存储表数据文件中任何索引的数据数 i:index

2. 聚集索引InnoDB
   聚集索引（主键索引）的叶子节点会存储数据行，也就是说数据和主键索引是在一起，着就是聚集索引。

   辅助索引只会存储主键值。
   如果没有主键，则使用唯一索引建立聚集索引，如果没有唯一索引，MySQL会按照一定规则创建聚集索引。
   

   2.1：主键索引
   InnoDB 要求表必须有主键（MYISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含自动作为主键，类型为长整型。
   叶子节点存储的是主键的值
   尽量在InnoDB上采用自增字段做表的主键。

   2.2：辅助索引（次要索引）
   如果是非主键查询，则需要索引两次索引树（一次是辅助索引树，一次是主键索引树），最终取出数据。
   叶子节点存储的是主键索引中的主键值，不是地址值。

   为什么不建议使用过长的字段做为主键？
   因为所有的辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

创建索引时注意事项
一：需要创建索引

 1.主键自动建立唯一索引
 2.频繁做为查询条件的字段应该创建索引
 3.多表关联查询中，关联字段应该创建索引
 4.查询中排序的字段尽量创建索引
 5.查询中统计或者分组字段尽量创建索引

二：不需要创建索引

1.表记录太少
2.经常进行增删改操作的表
3.频繁更新的字段
4.where 条件里使用频率不高的字段

三：其它注意事项

1.尽量创建组合索引（组合索引其实相当于默认按照最左原则帮我们创建多组索引，实际没有真正的创建其它索引）
2.覆盖索引：要查询的列，也要使用索引覆盖住。指的是select语句中的列，最好都创建为索引
3.MySQL查询只会使用一个索引
4.like  like'%aa%'  或者 like '%aa' 不会使用索引，而like 'aa%'可以使用索引
5. is null 、is not null会不使用索引