mysql索引及数据库引擎

顺序io（不需要每次都磁盘寻址，效率高），随机io（需要每次都磁盘寻址，效率低）

如何正确的创建合适的索引？

索引是一种分散存储的数据结构

磁盘io特性：每次交互是以页为单位，每页4K数据。innoDB每次和磁盘交互是四页16k数据（InnoDB叶节点data域保存了完整的数据记录相比较MYISAM(因为只存储了叶子节点的指针，所占空间1kb都不到)更能充分利用每次磁盘io）

InnoDB 主键索引存储了完整的数据库记录行，辅助索引仅存储了索引和主键值（这里提供了一种解决海量数据分页问题的思路，例如要取一张千万级别数据的表中5百万到5百万零五十行的记录，如何快速获取，可以先使用辅助索引查询找主键，再通过主键走主键索引获取数据库记录。）

mysql索引底层使用的是B+树，索引所有元素都位于树的叶子节点,相邻叶子节点间有双向指针查找索引时先将根节点load到磁盘中（这是一个磁盘IO过程，比较耗时，）在节点中定位指向下一级节点的指针，三层B+树饱和状态能存储大概2000万个索引

什么叫做聚集索引（聚簇索引）？

就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。（比如字典的目录是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引）。

MYISAM引擎主键是非聚族索引，索引和数据分开存储的，  不支持事务

InnoDB是主键索引是聚族索引，查询的时候不用回表，少一次磁盘IO；  支持事务

聚族索引，B+树叶子节点不仅包含指针还包含数据

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

第一个重大区别是InnoDB的数据文件本身就是索引文件。MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

无用的索引会拖垮数据库操作的性能（更新删除新增树的结构要变化，节点内关键字会重排）

myisam引擎中B+树数据库记录行的指针（5.5版本之前的默认引擎）

innodb引擎主键索引叶子节点存储了索引和数据记录（5.5版本之后的默认引擎）

列的离散性越高，选择性就越好（离散型太低还不如全表扫描，不适合建立索引）

离散性太差，mysql可能自动不选择索引（由mysql的查询优化器实现）

前缀索引：当需要给长度很长的字符串字段加索引时，可以使用前缀索引，给字符串的前几个字符位加索引，需要先判断一下前几位字符的离散度也就是重复率。

联合索引：选择原则：

1、经常用的列优先（最左匹配原则）

2、选择性（离散度）高的列优先（离散型高原则）

3、宽度小的列优先（最少空间原则）

覆盖索引：

1、如果查询里可通过索引节点中的关键字直接返回，则称为覆盖索引

例 user表中 联合索引为name+age  查询语句为select name, age from user where name=?

则为覆盖索引，直接返回叶子节点位置的数据，不用再回表，大大减少磁盘io，提高性能（这就是为什么公司不让使用select * ，有可能命中覆盖索引，只返回必要的数据，减少数据包的大小，降低数据传输的开销）

索引失效的情况

1.where条件中not in、or和<>操作无法使用索引（离散型太差）;

2.复合索引未用左列字段;

3.like以%开头;

4.需要类型转换;

5.where中索引列有运算;

6.where中索引列使用了函数;

7.如果mysql觉得全表扫描更快时（数据少，离散型差等情况）;

插拔式存储引擎

CSV存储引擎：

(数据存储以CSV文件格式）

特点：不能定义没有索引、列定义必须为not null 、不能设置自增列

-->不适用大表或者数据的在线处理

CSV数据的存储用逗号隔开，可直接编辑CSV文件进行数据的编排

-->数据安全性低

应用场景：数据的快速导入导出，表格直接装换成CSV

Archive存储引擎：

压缩协议进行数据的存储（ARZ文件格式）

特点：只支持insert和select两种操作，只允许自增id建立索引，行级锁，不支持事务，数据占用磁盘少

应用场景：日志系统，大量的设备数据采集

Memory存储引擎（heap存储引擎）

Myisam存储引擎

特点：select count(*) from table无需进行数据的扫描

        数据（MYD）和索引（MYI）分开存储

      表级锁

      不支持事务

Innodb（5.5版本后的默认存储引擎）

            支持事务、 行级锁、聚集索引，支持外键