mysql字符串索引原理_Mysql索引介绍和原理

索引的介绍

索引是什么？

官方介绍索引是帮助msyql搞笑获取数据的数据结构。更通俗一点的说：数据库索引好比是一本书前面的目录，能加快数据库的查询速度。优点是：方便查找--检索，索引查询内容--覆盖索引；排序；

一般来说索引本省也很大，不可能全部存储到内存中，因此索引旺旺是存储在磁盘上的文件中的(可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中)

我们通常所说的索引，包括：

聚集索引，覆盖索引，组合索引，前缀索引，唯一索引等，没有特别说明，默认都是使用B+树结构组织(多路搜索树，并不一定是二叉的)的索引

索引的优势和劣势

优势：

可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。--检索

通过索引列队数据进行排列，降低数据排序的成本，降低了CPU的消耗。--排序

被索引的列会自动排序，包括【单列索引】和【组合索引】，只组合索引的排序要更杂一些。

如果按照索引列的顺序进行排序。对应order by 语句来说效率就会提高很多。

where索引列在存储引擎层处理

劣势：

索引会占据磁盘空间

索引虽然会提高查询效率，但是会降低更新表的效率；比如每次对表进行增删改操作，MySQL不仅要保存数据，还要保存或者更新对应的索引文件。

索引的分类

单列索引

普通索引：Mysql中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。add index

唯一索引：索引列中的值必须是唯一的，但是允许空值，add unique index;

主键索引：是一种特殊的唯一索引，不允许有空值。pk

组合索引(建议使用)

在表中的多个字段组合上创建的索引 (add index(col1,col2,....))

组合索引的使用，需要遵循最左前缀原则:比如：like ‘col1%’;

全文索引

只能使用在Myisam，InnoDB的索引上(并且是5.7版本以上)才能是使用，并且只能在char,varchar,text,fultex类型字段上使用全文索引。

空间索引(没用过，也不常见)

索引的使用及实例

首先是创建索引

单列索引，普通索引

CREATE INDEX index_name ON table(column(length)) ;

ALTER TABLE table_name ADD INDEX index_name (column(length)) ;

单列索引，唯一索引

CREATE UNIQUE INDEX index_name ON table(column(length)) ;

alter table table_name add unique index index_name(column);

单列索引，全文索引

CREATE FULLTEXT INDEX index_name ON table(column(length)) ;

alter table table_name add fulltext index_name(column)

组合索引

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;

删除索引

DROP INDEX index_name on table

查看索引

show index from table_name

索引的原理分析

索引的存储结构

索引是在存储引擎中实现的，也就是说不通的存储引擎，会使用不同的索引。

Myisam和InnoDB：只支持B+Tree索引，也就说默认使用BTree,无法更换

MEMORY/HEAP存储引擎：支持hash和BTree索引

数据结构实例网站：Max.Degree=3：说明是三层结构，如下图

超过三个数就会裂变成树结构，如下图

如下图所示：每个节点上，都是左边小右边大，子节点是有顺序的。

B树结构图：

B树是为了磁盘或其他存储设配而设计的一种多叉平衡树。

B树的高度一般都是2-4这个高度，树的高度直接影响力IO读写的次数；

如果是三层树结构--支撑的数据可以达到20G,如果是四层树结构--支撑的数据可以达到几十T

B树和B+树的区别：

B树是非叶子节点和叶子节点都会存储数据。

B+树只有叶子节点会存储数据，而且存储的数据都是在一行上。这些数据都是有指针指向的，也就是有顺序的，索引列order by

MYISAM(是：非聚集索引)

B+树叶子节点只会存储数据行(数据文件)的指针，简单说数据和索引不在一起，就是非聚集索引

非聚集索引包含主键索引和辅助索引都会存储指针的值

主键索引

这里设表一共有三列，假设以Col1为主键，则上图是一个Myisam表的主索引(Primary key)示意。可以看出myisam的索引文件仅仅保存数据记录的地址。

辅助索引(次要索引)

在MyISAM中主索引和辅助索引在结构上没有什么区别，主要就是主索引要求key是唯一的，而辅助索引的key是可以重复的。如果在Col2上建立一个辅助索引，则此索引的结构如下所示：

同样也是一颗 B+Tree,data 域保存数据记录的地址。因此,MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法 搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。

InnoDB(是：聚集索引)

主键索引(聚集索引)的叶子节点会存储数据行，也就是说数据和索引都在一起的，这就是聚集索引

辅助索引只会存储主键值

如果没有主键，则使用唯一索引建立聚集索引；如果没有唯一索引，MySQL会按照一定规则创建聚集索引。

主键索引

InnoDB必须有主键的，而myisam中可以没有 。如果没有显示指定，则MySQL系统会自动旋转一个可以唯一标识数据记录的列作为主键，如果不存在中列，则MySQL自动为InnoDB表生产一个隐含字段作为主键，类型为bigint(或者说长整)型、

上图是 InnoDB 主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索 引。因为 InnoDB 的数据文件本身要按主键聚集,

辅助索引(次要索引)

第二个与myisam索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都是引用主键作为data域的。

聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键, 然后用主键到主索引中检索获得记录。

select * from user where name='Alice'

select id,name from user where name='Alice' 覆盖索引

select id, age,name from user where name='Alice' 覆盖索引

#回表查询 检索两次 非主键索引 --- pk---索引--->数据

引申:为什么不建议使用过长的字段作为主键?

因为所有辅助索引都会引用主键索引，过长的主索引会令辅助索引变得过大。

另外，请尽量在InnoDB上采用自增字段作为标的主键。