mysql 索引

mysql 索引类型：

1、普通索引： 这是最基本的索引类型，而且它没有唯一性之类的限制。
2、唯一索引：和“普通索引”相同，区别是“唯一索引”的值必须唯一，表中不能出现相同的值
3、主键：主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。
4、全文索引：在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。
5、组合索引：
比如创建表A 的 组合索引：ALTER TABLE A ADD INDEX index_name_a (x,y,z);
由于索引文件以B-树格式保存，MySQL能够立即转到合适的firstname，然后再转到合适的lastname，最后转到合适的age。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录！ 即：最左前缀 。
如下的查询能用到索引：
select * from A where x=1 and y=1 and z=1;
select * from A where x=1 and y=1
select * from A where x=1
下面的查询用不到索引：
select * from A where y=1 and z=1;
select * from A where x=1 or y=1;
......

注：在mysql中执行查询时，只能使用一个索引，如果我们在x,y,z上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。

设计原则：
1、搜索的索引列，不一定是所要选择的列。最适合索引的列是出现在 WHERE 子
句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列 
2、使用惟一索引。考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个
重复值的列，其索引效果最差。如：对性别索引效果就不好
3、使用短索引。如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样 做 。
例如，如果有一个 CHAR(200) 列，如果在前 10 个或 20 个字符内，多数值是惟一的，
那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空
间，也可能会使查询更快。较小的索引涉及的磁盘 I/O 较少，较短的值比较起来更快 。
更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL
也可以在内存中容纳更多的值。这增加 了找到行而不用读取索引中较多块的可能性。
（当然，应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的 ，因为这个索引中不会有许多不 同的值。）
4、利用最左前缀。在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。
多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集
称为最左前缀。（这与索引一个列的前缀不同，索引一个列的前缀是利用该的前 n 个字
符作为索引值。）
5、不要过度索引。不要以为索引“越多越好”。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍 过。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时
间越长。MySQL 在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索
引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的
最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引，应该考
虑所要增加的索引是否是现有多列索引的最左索引。
6. 考虑在列上进行的比较类型。索引可用于“ <” 、 “ < = ” 、 “ = ” 、 “ > =” 、 “ >
”和 BETWEEN 运算。在模式具有一个直接量前缀时，索引也用于 LIKE 运算。如果只
将某个列用于其他类型的运算时（如 STRCMP( )），对其进行索引没有价值。


使用不到索引的情况：
查询要使用索引最主要的条件是查询条件中需要使用索引关键字，如果是多列索引，那么只有查询条件使用了多列关键字最左边的前缀时，才可以使用索引，否则将不能使用索引。
1. 如果 mysql 估计使用索引比全表扫描更慢，则不使用索引。
2. 如果使用 heap 表并且 where 条件中不用＝索引列，其他> 、 < 、 >= 、 <=均不使用索引；
3. 如果不是索引列的第一部分；
4. 如果 like 是以％开始；
5. 对 where 后边条件为字符串的一定要加引号，字符串如果为数字 mysql 会自动转为
字符串，但是不使用索引。
6. 对索引列有计算


查看索引使用情况：
如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用 。
Handler_read_rnd_next 这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明表索引不正确或写入的查询没有利用索引。值高则意味着查询运行低效，并且应该建立索引补救。
mysql> show status like 'Handler_read%';