1. 索引类型
- B+Tree
适合全键值, 键值范围或键前缀查询. 其中键前缀只适合最左前缀查询. - Hash
只支持等值比较查询.
不支持匹配查询, 范围查询, 排序
列的选择性越高, 哈希索引的性能越好; 反之, 列的选择性越低, 哈希索引的性能越差.
只有 Memory 存储引擎支持哈希索引 - FULLTEXT 全文索引
仅可用于 MyISAM 和 InnoDB 存储引擎
2. 索引的使用
- 聚簇索引
- 覆盖索引
- 联合索引
两个或更多个列上的索引
3. 索引的特点
3.1. 优点
- 大大减少服务器需要扫描的数据量
- 帮助服务器避免排序和临时表
- 将随机I/O变为顺序I/O
3.2. 缺点
- 索引本身也是表, 因此会占用存储空间
- 数据表做增删改操作时, 需要同时维护索引表
4. 索引的使用
// TODO 待验证整理
4.1. 什么时候使用索引?
- 经常作为查询条件的列要建立索引
- 用作排序的列要建立索引
- 与其他表关联的字段, 外键关系要建立索引
- 用于聚合函数的列可以建立索引
4.2. 什么时候应不建或少建索引?
- 经常增删改的列不要建立索引
- 选择性低的列不建立索引
- 表记录太少
4.3. 索引失效的情况
- 在一个 SELECT 语句中, 索引只能使用一次, 如果在WHERE中使用了, 那么在ORDER BY中就不会失效.
- LIKE 操作中, ‘%aaa%’ 不会使用索引, 也就是索引会失效, 但是 ‘aaa%’ 可以使用索引.
- 在索引的列上使用表达式或者函数会使索引失效
- 在组合索引中不能有列的值为NULL, 如果有, 那么这一列对组合索引就是无效的.
- 在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效.
- 在查询条件中使用不等于, 包括<符号, >符号和！=会导致索引失效. 特别的是如果对主键索引使用！=则不会使索引失效, 如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效. (经erwkjrfhjwkdb同学提醒, 不等于, 包括<符号, >符号和！, 如果占总记录的比例很小的话, 也不会失效)
- 类型不一致会导致
- 尽量不要包括多列排序, 如果一定要, 最好为这队列构建组合索引
在一个SELECT语句中, 索引只能使用一次, 如果在WHERE中使用了, 那么在ORDER BY中就不要用了.
LIKE操作中, '%aaa%'不会使用索引, 也就是索引会失效, 但是‘aaa%’可以使用索引.
在索引的列上使用表达式或者函数会使索引失效, 例如: select * from users where YEAR(adddate)<2007, 将在每个行上进行运算, 这将导致索引失效而进行全表扫描, 因此我们可以改成: select * from users where adddate<’2007-01-01′. 其它通配符同样, 也就是说, 在查询条件中使用正则表达式时, 只有在搜索模板的第一个字符不是通配符的情况下才能使用索引.
在查询条件中使用不等于, 包括<符号, >符号和！=会导致索引失效. 特别的是如果对主键索引使用！=则不会使索引失效, 如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效. (经erwkjrfhjwkdb同学提醒, 不等于, 包括<符号, >符号和！, 如果占总记录的比例很小的话, 也不会失效)
在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效.
字符串不加单引号会导致索引失效. 更准确的说是类型不一致会导致失效, 比如字段email是字符串类型的, 使用WHERE email=99999 则会导致失败, 应该改为WHERE email=‘99999’.
在查询条件中使用OR连接多个条件会导致索引失效, 除非OR链接的每个条件都加上索引, 这时应该改为两次查询, 然后用UNION ALL连接起来.
如果排序的字段使用了索引, 那么select的字段也要是索引字段, 否则索引失效. 特别的是如果排序的是主键索引则select * 也不会导致索引失效.
尽量不要包括多列排序, 如果一定要, 最好为这队列构建组合索引;