mysql 索引总结

创建原则

1）需要创建索引的情况：
· 主外键和唯一约束的字段自动创建索引
· 频繁作为查询条件的字段应该创建索引
· 查询中排序的字段应该创建索引
· 查询中分组或统计的字段应该创建索引
2）不需要创建索引的情况：
· 表中记录太少不需要创建索引
· 需要频繁增删改的字段不适合创建索引
· where子句中用不到的字段不需要创建索引
· 重复值较多的字段不需要创建索引

mysql索引类型normal，unique，full text的区别是什么？

normal：表示普通索引
unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique
full textl: 表示 全文搜索的索引。

聚集索引和非聚集索引

总结

1. 数据库中查询记录时 每次只能使用一个索引 （可以是一个组合索引）

引用其中的一句话：“与其说是数据库只支持一条查询语句只使用一个索引，倒不如说N条独立索引同时在一条语句使用的消耗比只使用一个索引还要慢。”

2. 字符串不加单引号会导致索引失效

## 使用索引进行了查询
select id,code  from big_data where code ='123456';
## 索引失效
select id,code  from big_data where code =123456;

3. 使用mysql内部函数导致索引失效.对于这样情况应当创建基于函数的索引.

## 错误的例子： 索引失效
select * from test where round(id)=10; 
## 正确的例子：首先建立函数索引， 
create index test_id_fbi_idx on test(round(id)); 
## 然后  这时函数索引起作用了
select * from test where round(id)=10; 

4. 更新十分频繁的字段上不宜建立索引：因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的

索引使用技巧

1.索引不会包含有NULL的列

只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的。

2.使用短索引

对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char（255）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3.索引列排序

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。

4.like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。

5.不要在列上进行运算

6.不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的

负向条件查询不能使用索引，可以优化为in查询。
负向条件有：!=、<>、not in、not exists、not like等。
范围条件查询可以命中索引。范围条件有：<、<=、>、>=、between等。

7.索引要建立在经常进行select操作的字段上。

这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

8.索引要建立在值比较唯一的字段上。

9.对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。

10.在where 、join 中出现的列需要建立索引 ，多表关联时，要保证关联字段上一定有索引。

11.where的查询条件里有不等号(where column != …),mysql将无法使用索引。

12.如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。

13.在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用。

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

15. 在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。尽管如此，在涉及多个数据表的查询里，即使有索引可用，那些索引在加快ORDER BY操作方面也没什么作用

16. 不要给“性别”增加索引。如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。

简单的说吧，不需要，因为性别，就两个值男与女。为这两个值建立索引是不值得的，因为无论多少条记录，建立性别的索引，最多让你的语句少检索一半。但与建立索引带来的损失比，捡芝麻丢西瓜。(可能不准确，但大意如些)。

17.如果列名是索引，使用column_name is null将使用索引

18. union、in、or都能够命中索引，建议使用in。

19. 相join的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循环计算（nested loop）。

索引分析方法

查看索引使用情况

如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数。

Handler_read_key：如果索引正在工作，Handler_read_key的值将很高。

Handler_read_rnd_next：数据文件中读取下一行的请求数，如果正在进行大量的表扫描，值将较高，则说明索引利用不理想。

mysql> show status like 'Handler_read%'; 
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Handler_read_first    | 9      |
| Handler_read_key      | 16     |
| Handler_read_last     | 0      |
| Handler_read_next     | 680908 |
| Handler_read_prev     | 0      |
| Handler_read_rnd      | 0      |
| Handler_read_rnd_next | 935519 |
+-----------------------+--------+
7 rows in set (0.00 sec)

mysql> 

参考

mysql 索引 锁 及优化系列
索引优化示例