索引的设计和使用

 

InnoDb和MyISAM存储引擎的表默认创建的都是BTREE索引，mysql不支持函数索引，但是支持前缀索引（对索引字段的前N个字符创建索引），只有MyISAM支持全文索引。

 

1.创建索引：

CREATE index author_name on t_author(tname(10));

 

2.删除索引：

drop index author_name ON t_author;

 

 

1.设计索引的原则：

1. 搜索的索引列，最适合的列是出现在where 字句的列，或链接字句中指定的列。而不是出现在select关键字后的选择列表中的列。

1. 使用唯一索引， 考虑列中的值得分布， 索引的列的基数越大， 索引的效果越好。

1. 使用短索引（就是指定前缀长度）

1. 利用最左前缀

1. 不要 过度使用索引，（因为引擎会增加额外的磁盘存储空间，降低写操作性能）

1. InnoDB 按主键索引->其次唯一索引->主键要尽可能选择较短的数据类型()降低索引的磁盘占用 提高索引的缓存效果

注意：InnoDB的普通索引会保存主键的键值

 

2.BTREE索引与HASH索引

HASH索引的特征：

1. 只用于=或<=>操作符的等式比较

1. 优化器不能使用HASH索引来加速ORDER BY操作

1. 只能使用整个关键字来搜索一行

BTREE索引：

当使用>, <, >=,<=,BETWEEN,!=或者<>或者 like'pattern'操作符时都可以使用相关列上的索引。

 

3.MySQL中能够使用索引的典型场景

1. 匹配全值（Match the full value）

2.匹配值得范围查询

3.匹配最左前缀

4.仅仅对索引进行查询

5.匹配列前缀

6.能过实现索引匹配部分精确而其他部分进行范围匹配

 

4.索引失效（存在索引但是不能使用索引的典型场景）

 

1. 以%开头的like查询不能够使用B-TREE索引

1. 数据库中出现隐式转换的时候不会使用索引

1. 复合索引的情况下，假如查询条件不包含索引列的最左部分，即不满足最左原则

1. 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。

 

5.两个简单实用的优化方法

1.定期分析和检查表：analyze table tablename 找出可能出现的问题

check table tablename 检查一个或多个表是否有错

 

2.定期优化表：

optimize table tablename

个人是这样理解的，当你删除数据时，mysql并不会回收已删除的数据所占据的存储空间，以及索引位。而是空在那里，而是等待新的数据来弥补这个空缺，这样就有一个缺少，如果一时半会，没有数据来填补这个空缺，那这样就太浪费资源了。所以对于写比较频繁的表，要定期进行optimize，一个月一次，看实际情况而定了。