MySQL索引

1.MySQL 的SQL语句执行顺序

1.1.手写SQL语句结构

SELECT 
   <select_list>
FROM 
    <left_table> <join_type>
JOIN <right_table> ON <join_condition>
where 
  <where_condition>
GROUP BY 
  <group_by_list>
HAVING
  <having_condition>
ORDER BY 
  <order_by_condition>
limit <limit_number>

1.2.MySQL内部SQL语句执行顺序

FROM 
<left_table>
ON <join_condition>
<join_type> JOIN <right_table>
where 
  <where_condition>
GROUP BY 
  <group_by_list>
HAVING
  <having_condition>
SELECT  distinct
 <select_list>
ORDER BY 
  <order_by_condition>
limit <limit_number>

2.MySQL索引

2.1.索引是什么?

1. 索引是帮助mysql高效获取数据的数据结构
2. 可以简单理解为:排好序的用于快速查找数据的数据结构
3. 除数据本身之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高级查找算法.这种数据结构就是索引

2.2.索引的数据结构

1. 通常我们所说的索引是指B-Tree索引(通常指的是B+Tree),它是目前关系型数据库中查找数据最为常用和有效的索引,大多数存储引擎都支持这种索引。使用B-Tree这个术语,是因为MySQL在CREATE TABLE或其它语句中使用了这个关键字,但实际上不同的存储引擎可能使用不同的数据结构,比如InnoDB存储引擎就是使用的B+Tree。
2. B+Tree中的B是指balance,意为平衡。需要注意的是,B+Tree索引并不能找到一个给定键值的具体行,它找到的只是被查找数据行所在的页,接着数据库会把页读入到内存,再在内存中进行查找,最后得到要查找的数据。

2.3.数据库索引为什么使用B+Tree?

一般来说,索引本身也很大,随着数据库中数据的增加,索引本身大小随之增加,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。这样的话,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取,I/O存取的消耗要高几个数量级,所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说,索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。而B-/+/*Tree,经过改进可以有效的利用系统对磁盘的块读取特性,在读取相同磁盘块的同时,尽可能多的加载索引数据,来提高索引命中效率,从而达到减少磁盘IO的读取次数。

2.4.B+Tree索引的检索原理

如图:

说明:
[初始化过程]
一颗B+树,浅蓝色的块我们称之为一个磁盘块,可以看出每个磁盘块包含几个数据项(深蓝色)和指针(黄色).如:磁盘块1包含数据项17和35,包含指针P1,P2,P3;P1指向小于17的磁盘块,P2指向在17和35之间的磁盘块,P3指向大于35的磁盘块;真实数据存在于叶子节点,即3,5,9,10,13,15,28,29,36,60,75,79,98,99;非叶子节点只存储指引搜索方向的数据项,而不存储真实的数据,如17,35这些数据并不真实存在于数据表中

[查找过程]
如果要查找数据项29,首先会把磁盘块1加载到内存,此时发生一次IO;在内存中使用二分查找确定29在17到35之间,锁定磁盘块1的P2指针,由于内存中查找速度快,时间非常短,可以忽略不计;通过指针P2将磁盘块3加载到内存中,发生第二次IO;在内存中使用二分查找确定29在26和30之间,锁定磁盘块3的指针P2,然后将磁盘块8加载到内存中,发生第三次IO;同时在内存中做二分查找找到29,结束查询,总计三次IO.

*注意:

• B+Tree中查找任意的数据的次数: n次(n表示B+Tree的高度)
• 3层的B+数可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么就需要上百万次的IO,成本非常高

#扩展:聚簇索引与非聚簇索引

①.聚簇索引

所谓聚簇索引,就是指主索引文件和数据文件为同一份文件,聚簇索引主要用在Innodb存储引擎中.在该索引实现方式中B+Tree的叶子节点上的data就是数据本身,key为主键,如果是一般索引的话,data便会指向对应的主索引,如下图所示:

在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的B+Tree.做这个优化的目的是为了提高区间访问的性能,例如上图中如果要查询key为从18到49的所有数据记录,当找到18后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,极大提到了区间查询效率。

②.非聚簇索引

非聚簇索引就是指B+Tree的叶子节点上的data并不是数据本身,而是数据存放的地址.主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的.主要用在MyISAM存储引擎中,如下图:

***注意:非聚簇索引比聚簇索引多了一次读取数据的IO操作,所以查找性能上会差一些.

2.5.MySQL索引实现

在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,下面主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式.

2.5.1.MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址.下图是MyISAM索引的原理图：
这里设表一共有三列,假设我们以Col1为主键,则上图是一个MyISAM表的主索引（Primary key）示意.可以看出MyISAM的索引文件仅仅保存数据记录的地址.在MyISAM中,主索引和辅助索引（Secondary key）在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复.如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示：
同样也是一颗B+Tree,data域保存数据记录的地址.因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录.

MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分.

2.5.2.InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同.

第一个重大区别是InnoDB的数据文件本身就是索引文件.从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址.而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录.这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引
上图是InnoDB主索引（同时也是数据文件）的示意图,可以看到叶节点包含了完整的数据记录.这种索引叫做聚集索引.因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键（MyISAM可以没有）,如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形.

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址.换句话说,InnoDB的所有辅助索引都引用主键作为data域.例如:下图为定义在Col3上的一个辅助索引：
这里以英文字符的ASCII码作为比较准则,聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引;首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

2.5.3.InnoDB索引和MyISAM索引的区别:

1. 主索引的区别:InnoDB的数据文件本身就是索引文件.而MyISAM的索引和数据是分开的.
2. 辅助索引的区别:InnoDB的辅助索引data域存储相应记录主键的值而不是地址.而MyISAM的辅助索引和主索引没有多大区别.
3. InnoDB的主索引文件上直接存放该行数据,称为聚簇索引.次索引指向对主键的引用.
4. Myisam中,主索引和次索引都指向物理行,即主索引文件上直接存放该行数据的地址

2.5.4.总结

1. 了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大.再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择.
2. 对于InnoDB而言,因为节点下有数据文件,因此节点的分裂将会比较慢.对于InnoDB的主键,尽量用整型,而且是递增的整型.如果是无规律的数据,将会产生页的分裂,影响速度.
3. 索引覆盖:索引覆盖是指如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.这种查询速度非常快,称为“索引覆盖”.

2.6.索引的优势和劣势

2.6.1.优势:

1. 提高数据检索效率,降低数据库IO成本;通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
2. 索引内部通过b+tree(多路搜索树/二叉排序树)将索引列已经按照一定规则(左子节点小于父节点,右子节点大于父节点)排好序了,当要查找某个索引值时候,只需要根据规则查找某个范围内即可

2.6.2.劣势:

1. 索引本身也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用表空间的;虽然索引可以提高查询速度,同时却也会降低更新表的速度,当对表进行增,删,改操作时,由于涉及到表中记录的更新,mysql不仅要保存数据,还要保存索引文件中每次更新记录时所添加的索引列字段,这些操作都会调整因为更新带来的键值变化后的索引信息

2.7.索引的分类

1. 单值/列索引 : 一个索引只包含一个列(/字段),一个表中可以有多个单值索引

2. 唯一索引(unique): 索引列的值必须唯一,但是允许空值
   (主键索引属于唯一索引,主键索引不允许null值)

3. 复合索引 : 一个索引包含多个列(/字段),相当于二级目录(如,z:zhao)

2.8.索引的使用

2.8.1.创建索引

语法:
create 索引类型 索引名称 on 数据表(索引列);

或者
alter table 数据表 add 索引类型 索引名称(索引列)

例如:
create index index_name on table_name(columnName(length));  //创建普通索引
CREATE UNIQUE INDEX index_name ON table_name (column_list)  //创建唯一索引

ALTER TABLE table_name ADD INDEX index_name (column_list)  //添加普通索引
ALTER TABLE table_name ADD UNIQUE (column_list)            //添加唯一索引
ALTER TABLE table_name ADD PRIMARY KEY (column_list)       //添加主键索引

2.8.2.删除索引

语法:
drop index 索引名称 on 数据表

或者
ALTER TABLE 数据表 DROP INDEX 索引名称

例如:
drop index index_name on table_name;

ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

2.8.3.查看索引

语法:
show index from 数据表

例如:
show index from table_name;

2.9.那些情况需要创建索引?

1. 主键自动创建唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段,外键关系建立索引
4. 单值/复合索引,更加倾向于创建复合索引
5. 排序字段需要创建索引
6. 查询中统计和分组字段需要创建索引

2.10.那些情况下不需要创建索引?

1. 频繁更新的字段(/经常增删改的表)不适合创建索引(因为不但要更新索引,还要更新索引信息)

2. where条件里用不到的字段不需要创建索引
   (就是select 关键字后面不需要查询出来的字段)

3. 表记录太少
   (mysql表数据超过300万时候,数据库性能就会下降)

4. 数据重复且分布均匀的字段不需要创建索引
   (例如:一张表中有10万条数据,这些数据都是true和false两种值,那么就算创建了索引也没什么效果)