Mysql index使用事项

1,操作符和index关系

<>,!=,not in 不会使用index
<,<=,=,>,>=,between,in 可以使用索引
like 看情况,遵循最左原则就会用.
 like ‘%xxx%’不会用.
 like ‘xxx%’ 会用
or 看情况
 所有的or条件都必须是独立索引，这样才会使用索引.
 可以用in或者union来替换or.
 where后面field不一样的判断用union,一样的判断用or.

2,数据类型和index关系

类型一定要匹配，否则使用不了index.
select * from test where mobile = 13711112222;
如果mobile是char或者varchar类型，那么上面即使mobile字段设置了索引，也不会使用，注意类型匹配.要添加”: select * from test where mobile = ‘13711112222’;

3,索引列字段进行了函数操作，索引不会使用

如 where md5(password) = “xxxx”,所以不要在索引列上进行运算.

4,NULL会导致索引不可用，可以通过其他方式来表达，比如-1.

5,学会使用短索引,即建立“前缀索引”

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

6,当mysql一旦估计检查的行数可能会“太多”，范围查找优化将不会被使用,索引无效

select uid from users where registered < 1295001384；应避免这样的大数据查询

7.尽量借用覆盖索引，减少select * from..语句使用

select uid,fuid from users where uid = 1223232; 把index的field正好按index顺序输出.index(uid,fuid)

8.order by子句，尽量使用index方式排序，避免使用filesort方式排序

MySQL支持二种方式的排序，FileSort和Index，后者效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
ORDER BY满足以下情况，会使用Index方式排序:
 a)ORDER BY 语句使用索引最左前列。
 b)使用Where子句与Order BY子句条件列组合满足索引最左前列.

9.强制使用索引和禁止使用索引

1)强制使用索引: force index(index_name)

select * from table force index(PRI) limit 2;(强制使用主键)
select * from table force index(ziduan1_index) limit 2;(强制使用索引”ziduan1_index”)
select * from table force index(PRI,ziduan1_index) limit 2;(强制使用索引”PRI和ziduan1_index”)

2)禁止使用索引:ignore index(index_name)

select * from table ignore index(PRI) limit 2;(禁止使用主键)
select * from table ignore index(ziduan1_index) limit 2;(禁止使用索引”ziduan1_index”)
select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引”PRI,ziduan1_index”)

10.mysql索引选择，和IO操作数量有关

索引选择性就是结果个数与总个数的比值。
用sql语句表示为：
SELECT COUNT() FROM table_name WHERE column_name/SELECT COUNT() FROM table_name
一般来说(例如书 “SQL Tuning“)，如果选择性超过 20% 那么全表扫描比使用索引性能更优(oracle)。
但MySQL是没有计算索引的选择性的，只是预测逻辑IO操作的数量，因此对于MySQL索引要慎重选择。
参考链接:
http://database.51cto.com/art/201104/253568.htm
http://www.cnblogs.com/fanqi/p/6188093.html