索引三星系统
- 如果索引能够将数据相关的行放到一起,则获得一星。即索引将数据排序、区域集中、可以减少IO复杂度。
- 索引的数据顺序和查询的排列顺序一致。根据最左前缀原则,如果索引建立1,2,3;查询使用3,2,1;则不会使用到索引。所以索引建立要和查询排序一致。
- 索引中的列包含了查询中全部需要的列(即select 后面要查询的列),如Innodb根据一个普通索引查询主键值(select id from table where name = ‘wm’)。普通索引中保存了聚簇索引的值。而只查询聚簇索引值。这样只使用一次普通索引查询,就可以获取到全部需要的数据。不需要回表;但如果改成(select * from table where name = ‘wm’).查询需要查询全部字段。只根据非聚簇索引不能满足需求,还要根据聚簇索引再查询得到整行数据。即不满足。
索引选择性
- 计算方法:不重复的索引值(基数)/#T(记录数)
- 索引选择性低的列不宜建立任何类型索引,例如表示男女的字段。
- b-tree自带排序,选择性低导致树中会出现大量重复数据,失去索引意义。
- hash索引中相同的值记算出来的hash值一样出现hash碰撞,导致同一个槽变成链表。查询会遍历链表
- 唯一索引选择性最高,比例为1。所以唯一索引性能最高。
建立索引
实测Mysql5.7,key和index都可以创建索引。
alter table tableName add key/index index_name(cloumn(X))
memory
- 默认使用hash索引,支持Btree索引
- memory是非唯一hash索引,出现hash碰撞,使用链表存放多个记录指针到同一个hash槽内,查询时遍历链表逐条匹配
MyISAM
btree索引实现中,索引中保存的是数据行的物理位置引用。即真正查表查数据行。
没有聚簇索引,主键索引只是一个普通的唯一索引。
Innodb
- 普通索引则根据引用聚集索引的值,从聚集索引中找寻被索引的行,为了减少出现页分裂或者行移动导致的索引维护问题。
- Innodb特殊功能,自适应hash索引。当出现某些热key被经常查询,Innodb会在B+Tree之上,在内存中建立一个hash索引,用于精确的hash查询。使BTree一直程度上具有hash的一部分特性。这是个对用户完全透明的操作。
- 页填充因子:15/16。余1kb用于数据修改,可变长度列。