MySql存储引擎与索引

MySql引擎

存储引擎是具体操作数据的地方，是一种对数据存储的技术与其配套的功能

不同存储引擎所采用存储的方式的不同，并且索引技巧与锁定水平也不同

根据业务的需求灵活的选择存储引擎即可满足的实际的需要

Innodb

        Innodb是MySql中的默认安装的引擎，并且每张表默认使用的引擎就是的Innodb

        Innodb支持事务，支持外键，支持全文索引，行级锁，支持主键自增，不存储数据库中的总记录数

        运行时会在内存中建立缓存，缓存数据与索引

MyISAM

        MyISAM也是MySql中默认安装的引擎

        MyISAM不支持事务，不支持外键，支持全文索引，表级锁，存储表的总记录数量

        更新操作时效率低，但是读取的性能高

索引

引入

        Mysql中的数据在文件系统中存储是存储在数据页上的，并且一页数据最多能存储16k的数据

        如果需要读取数据页中的一条数据，那么则需要一条一条查询比对才能，找到查询的数据

        这么做的效率非常低，并且I/O的次数也非常高

索引

        索引就是帮助MySql进行高效查询的数据结构

        索引实际上是一棵树，树上的结点存储索引的键值与物理地址的指针

        查询时通过索引去找到数据对应的位置

优点

        数据检索的效率变高，能够快速定位，降低I/O的次数

        降低数据排序的成本

缺点

        索引实际上存储的是一张实体表，需要占用空间

        更新数据的同时也需要维护索引，导致性能降低

分类

主键索引

        主键索引是默认创建的，在设置主键之后，创建表时会默认创建的该索引，这也是为什么查询都尽量使用主键作为条件的原因

        新增主键索引：

        ALTER TABLE 表名 add PRIMARY KEY 表名(列名);

        删除主键索引：

        ALTER TABLE 表名 drop PRIMARY KEY ;

单值索引

        对非主键的单个列创建索引，一个表中可以有多个单列索引

        创建单值索引

        CREATE INDEX 索引名 ON 表名(列名);

        删除索引：

        DROP INDEX 索引名;

唯一索引

        对一列唯一的值创建索引，允许值为null

        创建唯一索引

        CREATE UNIQUE INDEX 索引名 ON 表名(列名);

        删除索引

        DROP INDEX 索引名 ON 表名;

组合索引

        组合索引表示对多列的值创建索引，可以同时使用多列的值作为条件进行查询

        创建单值索引

        CREATE INDEX 索引名 ON 表名(列名1，列名2，列名3.....);

        删除索引：

        DROP INDEX 索引名;

最左前缀原则

        该原则是在使用该索引进行检索的时候需要遵守的

        该原则要求检索条件带有索引最左侧的列的值，否则索引失效

        例如CREATE INDEX test_index ON test(a,b,c);

        select * from test where a = 11 and b =11

        select * from test where a = 11 and c =11

        select * from test where b = 11 and a =11

        以上情况索引都是生效的

        select * from test where b = 11 and c =11 ,当查询条件中没有a时失效

索引数据结构

        索引的底层采用B+树的数据结构，在B+树之前我们首先排除掉了二叉树与红黑树

        二叉树与红黑树在结点内只能存储一个数据，虽然红黑树可以自平衡，但是还是会出现树高过高的情况，这样就会导致检索的效率变低。

        B树的特点： 自平衡的多路树，一个结点内存储多个数据（横向拓展），非叶子节点不存储数据,只存储索引，数据放在叶子结点中

        B+树的特点：自平衡的多路树，一个结点内存储多个数据（横向拓展），非叶子节点不存储数据,只存储索引，数据放在叶子结点中，并且叶子结点中会有指针连接，

        B+树底层的链表对与自增的主键的检索比较方便，而且能够提高范围内检索的效率

聚簇索引与非聚簇索引

聚簇索引

        聚簇索引的索引值与数据是存放在一起的，根据索引值查询数据可以直接查询到表中的数据

        例如，根据主键查数据，查询到叶子结点就相当于查到的了数据

        在Innodb引擎中大部分查询的方式都是聚簇索引的方式，并且索引与数据是存放在一个文件中的

非聚簇索引

        非聚簇索引的索引值与数据是分开存放的，根据索引值查询到的是数据的映射

        非聚簇索引例如在非主键的索引查询多条数据的话，会根据索引查询到主键值，再根据主键索引回表查询到对应的数据，在Innodb中非主键的索引就是使用的该策略，该场景下是非聚簇式的索引。

        非聚簇的索引，索引存储的是物理空间上的地址，会直接指向数据存放的位置

        在MyISAM引擎中大部分查询的方式都是非聚簇索引的方式，并且索引与数据是分开存放在两个文件中的