SQL索引

1.索引概述

使用索引是提高 SELECT 操作性能的最佳途径。 MyISAM 和 InnoDB 存储引擎的表默认创建的都是 BTREE 索引

MySQL 目前还不支持函数索引，但是支持前缀索引。

MySQL 中还支持全文本（FULLTEXT）索引，用于全文搜索，默认情况下，MEMORY 存储引擎使用 HASH 索引，但也支持 BTREE 索引。

语句	意义
CREATE[UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,…)	创建索引 index_col_name: col_name [(length)] [ASC|DESC]
DROP INDEX index_name ON tbl_name	删除索引

2.设计索引原则

• （1）搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在 WHERE子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。
• （2） 使用唯一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。
• （3） 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度。
• （4）利用最左前缀
• （5）不要过度索引

3.索引失效

• （1）如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引.

• （2）如果使用 MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列，那么不会用到索引。heap 表只有在“=”的条件下才会使用索引。

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

• （4）不符合最左前缀:有复合索引，但是搜索的那个列不是索引第一列

• （5）like以%开始

• （6）如果列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则的话即便这个列上有索引，MySQL 也不会用到的

4.查看索引使用情况

show status like ‘Handler_read%’;
输出：

• handler_read_key: 索引值被读次数 越高越好
• handler_read_rnd_next:查询效率，该值越低越好