索引的数据结构
二叉树
树高度普通二叉树最坏n.
红黑树
二叉平衡树,树高度logn+1.以20000000条数据为例,h=log20000000=25
Hash表
- 对索引的key进行一次hash计算就可以定位出数据存储的位置
- 很多时候Hash索引要比B+Tree更高效
- 仅能满足=、in,不支持范围查询
- hash冲突问题(数组+链表)
B-Tree
- 叶子节点具有相同的深度,叶节点的指针为空。
- 所有索引的元素不重复
- 节点中的数据索引从左到右递增排列
B+Tree
- 非叶子节点不存储data,只存储索引(冗余),索引空白部分指向其他页的地址,可以放更多的索引,
- 查看页大小:show global status like 'innodb_page_size';
存储引擎
MyISAM:索引文件和数据文件是分离的,属于非聚集索引
- test.frm 存放表结构等信息
- test.MYD 存放数据
- test.MYI 存放索引
InnoDB:索引文件和数据文件是不分离的,属于聚集索引
- 表结构文件本身就是按B+Tree组织的一个索引结构文件。
- 叶子节点包含了完整的数据记录。
- 建议InnoDB表必须建主键,并且推荐使用整型的自增主键。
- 非主键索引(二级索引)结构中的叶子节点存储的是主键值。
- 保持一致性,若修改记录内容,只需要改一次主键索引中的数据,再更新非主键索引即可。
- 节省空间,只用保留一份数据即可,若索引中全都要有完整数据,浪费硬盘。
explain工具介绍
- 模拟优化器执行SQL语句
- 分析你的查询语句或是结构的性能瓶颈
- 执行查询会返回执行计划的信息
- from 中包含子查询,仍会执行该子查询,将结果放入临时表中
explain中的type
- system:system是const的特例
- const:读取一次
- eq_ref:primary key 或 unique key 索引的所有部分被连接使用 ,最多只会返回一条符合条件的记录。这可能是在const 之外最好的联接类型了,简单的 select 查询不会出现这种 type。
- ref:相比 eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行
- range:范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行
- index:通常扫描二级索引,扫描全索引拿到结果
- all:全表扫描,可以考虑通过增加索引进行优化
效率对比:system>const>eq_ref>ref>range>inex>ALL
索引最佳实践
- 全值匹配
- 最左前缀法则
- 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少 select * 语句
- mysql在使用不等于(!=或者<>),not in ,not exists 的时候无法使用索引会导致全表扫描< 小于、 > 大于、 <=、>= 这些,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
- is null,is not null 一般情况下也无法使用索引
- like以通配符开头('$abc...')mysql索引失效会变成全表扫描操作
- 字符串不加单引号索引失效
- 少用or或in,用它查询时,mysql不一定使用索引,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
- 范围查询优化