顺序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版本后的默认存储引擎）
支持事务、 行级锁、聚集索引，支持外键
网友评论