如何创建索引
建立索引是以浪费空间为代价,换取高查询效率。建立索引会对修改操作的性能造成影响
1 、建立基础索引:在使用 where 、 order by 、 join 字段上建立索引
2 、优化组合索引:基于业务规则和业务逻辑
如果条件经常性的出现在一起,可以考虑将多个字段的索引升级为复合索引
如果添加个别字段的索引就可能出现索引覆盖,可以考虑为该字段建立索引
3 、如果查询时不常出现的索引应该删除掉
4 、建立索引后注意 sql 语句的使用
%开头的模糊查询导致索引失效
索引列最好非空,null 值不会出现在索引中。 sex boolean default 1
少用not in 或者 != 查询, not in 可以使用 not exists 替代
不要针对列进行计算,针对列建议的索引无效
5 、使用 explain 查看执行计划,判断索引是否生效
前缀索引
一般希望索引表应该比具体的数据表要小。当建立索引时默认使用字段的全部内容建立索引,可以指定使用字段的前10 个字符建立索引,而不是整个字段内容。语法: index( 列名称 ( 长度 ))
使用前缀索引的前提是:字段的前缀标识度比较高,例如口令比较适合创建前缀索引,因为各个密码几乎都不相同
使用前缀索引的难度就是如何定义前缀截取的长度
可以使用 select count(*)/count(distinct left(password,len)) 进行比较,通过调整 len 值的大小查看不同前缀的平均匹配度,接近1 时就可以了
索引的具体实现
Innodb 存储引擎采用的是索引组织表,在 Innodb 中表数据文件本身就是按照 B+ 树组织的一个索引结构。MyISAM 中主索引和辅助索引在结构上没有任何区别,只是主索引要求 key 值唯一的,而辅助索引的key值允许重复
Innodb 存储引擎
在 innodb 中的数据文件本身就是按照主键聚集存放【聚集索引】,所以要求 innodb 中的数据表必须有主键,MyISAM 可以没有。如果没有显式指定主键, mysql 会自动选择一个可以标识的列作为主键,如果不存在可以标识的列,mysql 自动为 innodb 表生成一个隐含字段充当主键,这个字段为 6B
在 Innodb 中索引不建议使用过长的字段作为主键,因为所有的辅助索引都要使用主索引,过长的主索引会导致辅助索引变大,建议使用整型数据充当主键,占用空间少,比较速度快。另外使用非单调的字段充当主键不是个好主意,因为innodb 数据文件本身就是一个 B+ 树,非单调的主键会造成插入新记录时数据文件为了维护B+ 树的特性而出现频繁的分裂调整,十分低效。最佳实践;可以考虑使用 bigint auto_increment的代理主键
MyISAM 存储引擎
MyISAM 主索引使用的是 B+ 树作为索引结构,叶子节点 data 域中存储的是数据记录的地址
MyISAM 索引的检索算法是首先按照 B+ 树的搜索算法查找对应的 key ,如果 key 存在,则获取存储在 data域中的地址值,最后根据地址值获取对应行的数据,这种叶子节点不存储具体数据的方式叫做非聚集索引
hash 索引
在索引被载入内存中,使用 hash 结构存储数据
类似 HashMap 实现