# 索引设计的原则 1）适合索引的列是出现在where子句中的列，或者连接子句中指定的列； 2）基数较小的类，索引效果较差，没有必要在此列建立索引； 3）使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间； 4）不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。 # 索引设计的原则索引优化规则： 1）如果MySQL估计使用索引比全表扫描还慢，则不会使用索引。 返回数据的比例是重要的指标，比例越低越容易命中索引。记住这个范围值——30%，后面所讲的内容都是建立在返回数据的比例在30%以内的基础上。 2）前导模糊查询不能命中索引。 前导模糊查询不能命中索引： EXPLAIN SELECT * FROM user WHERE name LIKE '%s%';  非前导模糊查询则可以使用索引，可优化为使用非前导模糊查询： EXPLAIN SELECT * FROM user WHERE name LIKE 's%';  3）数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。 EXPLAIN SELECT * FROM user WHERE name=1;  EXPLAIN SELECT * FROM user WHERE name='1';  4）复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左原则），不会命中符合索引。 name,age,status列创建复合索引： ALTER TABLE user ADD INDEX index_name (name,age,status);  根据最左原则，可以命中复合索引index_name EXPLAIN SELECT * FROM user WHERE name='swj' AND status=1;  **注意：** 最左原则并不是说是查询条件的顺序。 EXPLAIN SELECT * FROM user WHERE name='swj' AND status=1;  **注意：** 而是查询条件中是否包含索引最左列字段： EXPLAIN SELECT * FROM user WHERE status=2 ;  5）union、in、or都能够命中索引，建议使用in。 **注意：** 查询的CPU消耗：or>in>union。 6）用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。 EXPLAIN SELECT * FROM payment WHERE customer_id = 203 OR amount = 3.96;  7）负向条件查询不能使用索引，可以优化为in查询。 负向条件有：!=、<>、not in、not exists、not like等。 负向条件不能命中索引： EXPLAIN SELECT * FROM user WHERE status !=1 AND status != 2;  可以优化为in查询，但是前提是区分度要高，返回数据的比例在30%以内： EXPLAIN SELECT * FROM user WHERE status IN (0,3,4);  8）范围条件查询可以命中索引。范围条件有：、>=、between等。 范围条件查询可以命中索引： EXPLAIN SELECT * FROM user WHERE status>5;  **注意：** 范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引： EXPLAIN SELECT * FROM user WHERE status>5 AND age<24;  **注意：** 如果是范围查询和等值查询同时存在，优先匹配等值查询列的索引： EXPLAIN SELECT * FROM user WHERE status>5 AND age=24;  8）数据库执行计算不会命中索引。 EXPLAIN SELECT * FROM user WHERE age>24;  EXPLAIN SELECT * FROM user WHERE age+1>24;  计算逻辑应该尽量放到业务层处理，节省数据库的CPU的同时最大限度的命中索引。 9）利用覆盖索引进行查询，避免回表。 被查询的列，数据能从索引中取得，而不用通过行定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。 EXPLAIN SELECT status FROM user where status=1;  **注意：** 当查询其他列时，就需要回表查询，这也是为什么要避免SELECT*的原因之一： EXPLAIN SELECT * FROM user where status=1;  10）建立索引的列，不允许为null。 单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集，所以，请使用not null约束以及默认值。 # 索引总结： a. 更新十分频繁的字段上不宜建立索引：因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的。 b. 区分度不大的字段上不宜建立索引：类似于性别这种区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外返回数据的比例在30%以外的情况下，优化器不会选择使用索引。 c. 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。虽然唯一索引会影响insert速度，但是对于查询的速度提升是非常明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，在并发的情况下，依然有脏数据产生。 d. 多表关联时，要保证关联字段上一定有索引。 e. 创建索引时避免以下错误观念：索引越多越好，认为一个查询就需要建一个索引；宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度；抵制唯一索引，认为业务的唯一性一律需要在应用层通过“先查后插”方式解决；过早优化，在不了解系统的情况下就开始优化。