1、索引创建

1）查看表中的索引， show index from table; show indexes from table;

2）我们一般都不在数据库层面限制外键，因为约束太多数据库压力太大，死锁产生的概率也会变大，但是关联字段肯定是会有的，这些关联字段都必须建立索引，因为外键都是为了关联查询的，连接条件最好有索引可用。

3）经常出现在查询条件里的字段，order by， group by 的字段， 连接条件的字段 都是应该添加索引的，但是对于那些枚举值比较少的字段不要建立索引，因为索引基数太小，没什么作用。

4）字段是否为 null，对于索引的使用本身是没问题的，因为查询条件 is null 也是会使用索引的，但是会影响字段的存储空间以及索引的存储空间，因为 null 比较特殊，存储这个值需要额外开辟空间，同样索引也是一样需要额外空间。如果数据量比较大的话那么索引数据越多，也会是查询变慢，所以尽量不使用 null，也就是在字段定义时限制 not null。

5）字段是否需要 unique，unique 对索引来说是最好的，因为最高效的索引就是最大基数的索引，索引的基数最大也不过记录数，那么如果记录的字段都是unique，索引基数也是最大的，也就是为什么 主键索引会优先使用。

2、索引使用

1）保持数据完整性，优化数据访问性能

2）改进表的连接操作，对结果进行排序，简化聚合数据操作

3）如果经常使用 or 查询的，那么应该使用单列索引，因为mysql对 or 的查询会使用多个索引的并集，即一个表可能使用多个索引，但是如果经常使用 and 查询，那么建议对这些列使用复合索引，因为mysql对 and 的查询一般一个表只会使用一个索引，可想而知如果索引覆盖越大，效率越高。

3、索引失效

1）本来是数值类型，结果使用字符串（使用引号引起来）查询，索引会失效

我们系统的ID大多使用 bigInt ，所以都是数值类型，查询的时候直接使用 id = 223 这个时候肯定是会使用 Primary 索引的，但是如果使用带引号的字符串查询则会全表扫描，如 id = '223';

同样，如果本来就是字符串类型，结果用不带引号的数值去查询，也不会使用索引，其实这里都会涉及到一个数据类型隐式转换的问题，还有就是查询条件中如果有函数计算其实也是不会使用索引的。

2）如果mysql估计使用全表扫描要比使用索引快,则不使用索引

数据量小的时候千万不要纠结索引的问题，因为你会被困扰的，所以请记住这条，不到优化的时候不要去优化，优化是在有必要的情况才需要做的额外事情。

3）mysql 索引讲究“最左原则”，即建立的索引时mysql根据字段顺序依次建立子索引

这个只是在联合索引建立的时候才会出现，比如 index(name,value,sex) ，那么会有如下的三个索引

index1：name-value-sex

index2：name-value

index3：name

所以，如果查询条件中只有 value 和 sex ，那么mysql无法匹配到索引。

4）or 连接的字段必须都预先建立索引，否则所有索引失效

查询条件经常会使用 or 连接， 如果使用 or 连接的字段中有一个没有建立索引，那么整个表的查询就不会使用索引，如果所有列都使用了索引，那么会使用这些索引的并集，即一个表不仅仅使用一个索引。Using union(index_1,index_2.....)，所以使用 or 连接的时候需要注意索引的覆盖，并且也要反驳那些说使用 or 就不会使用索引的谬论。

5）in 也会使用索引，合理对待 in 和 exists

所有一开始学习sql的人都会觉得使用 in 就不会使用索引，所以遇到 in 就会改为 exists , 这个说明学得太肤浅了。难道使用 exists 就会使用索引了，搞笑。首先 IN 有两种情况下使用

in1：使用常量，即 id in(1,2,3) ， 这种情况无法使用exists替代，而且如果 id 上有索引的话，这个索引会被使用。

in2：使用子查询，即 id in(select id2 from ....) ， 这种情况可以使用 exists 替代，但是并不是使用 exists替代in就会效率高一些，如果这个很有必要，那in岂不是没有存在的道理，那为什么网上这么多人说使用exists替代in呢，这其实和表的运行先后有关系，如下：

示例1：

示例2：

说明，示例1使用的是in，结果mysql是先执行子查询里面，所以第一行是表 t，但是由于 t 的student_name 没有预先建立索引，所以整个查询无法使用索引，所以对表 t 使用全表扫描；子查询执行得到结果之后，再执行主查询，很明显表 teri 使用了索引 student_id，所以说虽然是使用in但还是使用到了索引查询。

然后再说示例2，示例2使用 exists ，然而mysql是先执行主查询，即扫描表 teri，这里使用了全表扫描，然后再把扫描的结果作为子查询的条件，这个时候子查询使用了索引。

总结，in 和 exists 的区别是执行主查询、子查询的顺序，in 是先执行子查询，再执行主查询；exists是先执行主查询再执行子查询，关于索引的使用 in 是主表的使用，exists 则是字表使用，至于说 exists 的效率高于 in ，那么这个是以偏概全了，从上面的示例可以看出 in 的效率明显高于 exists。总结起来就是：

A：如果主表和子表的数量级差不多，那么 in 和 exists 相差无几

B：如果主表大于子表，那么建议使用 in，因为这样主表可以使用索引

C：如果主表小于子表，那么建议使用 exists，因为这样子表可以使用索引

D：如果可以使用连接替代以上任何情况，那么就是用连接查询，不要使用子查询。

E：Not Exists 要比 Not IN 好，是因为 Not IN 在任何情况下都不会使用索引。

6）like 查询如果以 '%' 开头，必然不会使用该列的索引

其实这个很好理解，无需解释。对于那些需要使用 '%'开头的查询其实建议使用在表中多建一个字段，即该字段的 reverse 值，也就是把源值reverse之后再存储到这个字段中，然后使用模糊匹配时就可以使用 % 结尾查询，同样可以使用索引。最后还建议使用搜索引擎做，比如 Lucene.

7）这些运算符不会使用外键索引

A：!= 否定语句最好少使用，其实可以通过 < 符号来变通使用，因为 < 是会使用索引的。

B：<> 同上，使用 < 符号来变通

C：> 同上，< 符号来变通

D：not in 可以使用 not exists 替代，对于常量同上， < 符号来变通

E：is not null 很多时候如果要建索引的话最好就是这个字段不为空，因为null需要额外一些空间存储，增大了索引的空间，但是如果你说使用 is null 就不会使用索引的话，那你就错了，他会使用索引的。

8）连接语句，连接顺序，条件顺序

写SQL时有些人使用 inner join，left join，using 或者是默认内连接的使用方式等，网上有很多说哪种比哪种好，可是当我使用 explain 去查看时，发现他们使用的索引都是一样的，那么应该不存在有什么快慢的，可能也有其他因素，总之使用索引与连接方式没有关系，与连接的顺序也没有关系。还有人也说把常量放在前面，条件连接放在后面，实际上也没有效果，因为mysql优化引擎会自动优化这些，所以不存在条件顺序问题，如果说顺序会影响索引的使用，那么mysql也就没有必要去寻找最好的索引了。对于另一个疑惑是，某些条件是写在 join 上呢还是写在 where上呢？个人觉得除了全连接意义是一样的外，其他连接使用这两种方式意义完全不同，所以还是根据使用的意义出发，尽量把逻辑主键的连接写在join后面用and连接。

9）覆盖索引

索引查询的语句中如果只包含了那个索引列那么这个索引就会被体现最大化，也就是覆盖索引。比如表主键ID都会有索引，如果这样查询

select id from t where id > num ;

比下面这个查询要快很多

select * from t where id > num ;

这也就是为什么不建议使用 * 的原因之一，其他原因比如网络流量等。从覆盖索引我们可以得到启发那就是在大数据量情况下想要数据分页，那么可以先查询出满足条件的ID，然后通过ID作为过滤条件去查询列表信息。如下分页代码：

1）select * from product limit 866613, 20 37.44秒

2）select * from product where id > =(select id from product limit 866613, 1) limit 20 0.2秒

3）select * from product a join (select id from product limit 866613, 20) b on a.ID = b.id 0.2秒