【Mysql】Mysql索引详解

索引概述

　　MYSQL数据库系统除了保存数据之外，为了能够提高对数据的快速访问，MYSQL为此设计了索引，MYSQL索引是一种可以快速提高MYSQL高效查询数据的一种数据结构。举个例子索引就跟我们的书本的目录一样，如果一本书没有目录，那么你要找想看的地方，那会是相当费劲的，只能一页页去翻，而数据库的索引就是扮演这样的角色，索引会告诉你对应的数据存放的磁盘地址，就好比目录上面的页数。

 　　

 

索引的优点和缺点

　　优点

　　　　1. 可以提高数据的查询速度，降低IO所消耗的时间。

　　　　2. 通过索引可以数据进行排序，因为索引本身就是有序的，可以降低CPU的消耗。

 　 缺点

　　　　1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
　　　　2. 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

 

MYSQL索引类型

 　　MYSQL的索引是由具体的存储引擎来实现的。下面是两个维度来看MYSQL的索引类型。

　　　　应用层次维度划分

　　　　　　1. 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引。

　　　　　　2. 唯一索引：索引列的值必须唯一，但允许有空值。

　　　　　　3. 联合索引：一个索引包含多个列。

 

　　　  存储结构维度划分

　　　　　　1. B-TREE索引（B+tree，B-tree）。
　　　　　　2. 哈希索引。
　　　　　　3. 全文索引（full-index）。

 

索引的使用

　　索引的使用分为适合使用索引的场景，不适合使用索引的场景，还有索引失效的情况。

　　1. 适合使用索引的场景

　　　　1) 主键自动创建唯一索引

　　　　2) 频繁作为查询条件的字段

　　　　3) 查询中与其他表关联的字段

　　　　4) 查询中排序的字段

　　　　5) 查询中统计或分组字段

 

　　2. 不适合使用索引的场景

　　　　1) 频繁更新的字段。

　　　　2) where 条件中用不到的字段。

　　　　3) 表记录太少。

　　　　4) 经常增删改的表。

　　　　5) 字段的值的差异性不大或重复性高。

 

　　3. 索引失效的情况

　　　　1) 使用like模糊查询时，以 % 开头。

　　　　2) 使用 or 时，如：字段1（非索引）or 字段2（索引）会导致索引失效。

　　　　3) 使用复合索引时，不使用第一个索引列。

Mysql索引数据结构

　　Mysql使用的是B+树，查找复杂度是logN，同时插入新的节点不必移动全部节点。兼顾了插入，更新与查询性能。

 

索引覆盖

 　　如果一个索引包含所有需要的查询的字段的值，我们称之为覆盖索引。覆盖索引是非常有用的工具，能够极大的提高性能。因为，只需要读取索引，而无需读表，极大减少数据访问量。

 

最左前缀原则

　　用于联合索引中。

 

索引下推（Index Condition Pushdown，ICP）

　　索引下推是指根据联合索引查询所满足条件的在进行筛选，然后在回表返回数据。比如这样的查询条件  like 'songgj%’and age >20检索，在MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，这样可以减少回表次数提升查询效率。

 

 

查看索引大小

　　查看表数据占用的字节数以及索引大小的字节数是在库information_schema下的tables中

SELECT 
  table_name,
  table_rows,
 concat(round((index_length)/1024/1024,2),'MB') index_data ,
 concat(round((data_length)/1024/1024,2),'MB') data_data 
 from tables  
 where table_name='表名'  AND table_schema = '数据库名'

 具体查询如下