我面试几乎必问：你设计索引的原则是什么？怎么避免索引失效？

设计索引的原则及避免索引失效的方法如下：

• 主键索引设计

  主键应设计为自增类型，不建议使用UUID作为主键。因为MySQL维护聚簇索引时按主键顺序排序，数据页间通过双向链表连接，数据页内通过单向链表连接且数据按主键从小到大排列。自增主键能快速定位新增记录插入位置，非自增主键则需从头比较寻找合适位置，严重影响效率。

  唯一索引与主键索引类似，但不一定自增，维护成本大于主键索引。唯一索引值唯一（可有一个值为NULL），能快速通过索引字段确定记录，但可能需回表查询。

• 为频繁查询的字段建立索引

  经常作为查询条件的字段应建立索引，以提高查询速度。由于查询条件通常不止一个字段，所以联合索引较为常见。

  查询条件中有like模糊查询时，需遵守最左前缀查询原则。

• 避免为“大字段”建立索引

  尽量使用数据量小的字段作为索引。例如，在varchar(5)和varchar(200)两个字段中，优先为varchar(5)的字段建立索引。因为MySQL维护索引时会将字段值一起维护，使用大字段会导致索引占用更多空间，排序时花费更多时间。

  若必须为varchar(100)等大字段建立索引，可取部分数据。如address类型为varchar(200)，建立索引时可写成CREATE INDEX tbl_address ON dual(address(20));。

• 选择区分度大的列作为索引

  区分度不高的字段不适合做索引，如“性别”字段，其值只有男和女，出现几率几乎相等。当值出现在表的数据行中的百分比很高时，MySQL查询优化器一般会忽略索引，进行全表扫描，惯用的百分比界线是“30%”。

• 尽量为ORDER BY和GROUP BY后面的字段建立索引

  为Order by后面的字段建立索引，查询时无需再排序，因为建立索引后B+树中的记录是排序好的。

  GROUP BY与ORDER BY类似，GROUP BY时需先根据其后面的字段排序，再执行聚合操作。若GROUP BY后面的字段没有排序，MySQL会产生临时表进行排序后再执行聚合操作，效率较低。若GROUP BY后面的字段已建立索引，则无需排序，不会产生临时表。

  以下情况会产生临时表：

  GROUP BY 的列没有索引。

  GROUP BY时，SELECT的列不止GROUP BY列一个，并且GROUP BY的列不是主键。

  GROUP BY的列有索引，ORDER BY的列没索引。

  GROUP BY的列和ORDER BY的列不一样，即使都有索引也会产生临时表。

  GROUP BY或ORDER BY的列不是来自JOIN语句第一个表。

  DISTINCT 和 ORDER BY的列没有索引。

  GROUP BY 和 ORDER BY 的列一样且是主键，但SELECT 列含有除GROUP BY列之外的列。

• 不要在条件中使用函数

  已建立索引的字段在使用时执行函数操作，索引将无法使用。因为MySQL为索引维护的B+树基于字段原始数据，使用函数后MySQL不认为这是原来的字段，所以不走索引。

  若因使用MySQL内部函数导致索引失效，可在建立索引时连着函数一起创建。例如，字段age创建了索引，查询SELECT * FROM student WHERE round(age) = 2;时索引使用不到，可创建索引create index stu_age_round on test(round(age));，之后通过该方式查询索引生效。

• 不要建立太多的索引

  MySQL维护索引需要空间和耗费性能，会为每个索引字段维护一颗B+树。索引过多会增加MySQL负担。

• 频繁增删改的字段不要建立索引

  字段变化时MySQL需要重新维护索引，若某字段频繁修改，意味着需频繁重建索引，必然影响MySQL性能。

• 避免使用OR关键字

  使用OR关键字会导致索引失效，若想使用OR又不让索引失效，需为or条件中的每个列都建立索引，但这与不要建立太多索引的原则相违背。

• 遵循联合索引的最左前缀原则

  联合索引如果不遵循最左前缀原则，索引将失效。

• 模糊查询避免以%开头

  使用模糊查询时以%开头会导致索引失效。

• 避免索引列使用隐式转换

  假设字段age类型为int，查询SELECT * FROM student WHERE age = 15能使用索引，而查询SELECT * FROM student WHERE age = '15'则使用不到索引，即age列的索引失效。