索引使用注意事项

索引使用注意事项

最左前缀法则

如果索引了多列数据（联合索引），要遵循最左前缀法则。

从索引的最左列开始，并且不跳过索引的列。如果跳过某一列，索引将部分失效（后面的字段也失效）。

索引列运算操作

不能再索引列上进行计算操作，不然会导致索引失效。

字符串不加引号

也会导致索引失效

模糊查询

如果只是尾部模糊匹配，索引不会失效。只要有头部模糊匹配，索引失效。

or连接的条件

用or分割开的条件，如果or前的条件有索引，or后的条件没有索引，那么涉及到的索引都不会被用到。

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

SQL提示

SQL提示是优化数据库的一个重要手段，简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

1
select ... from table_name use index(index_name) where ...;

use index(…)：建议MySQL使用的索引

force index(…)：强制MySQL使用的索引

覆盖索引

查询使用了索引，并且需要返回的列在索引中能全部找到。

using index condition：使用了索引，需要回表查询。

Useing where;using index：使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询。

前缀索引

当字段类型为字符串时，有时会需要所有很长的字符串，这会使索引变得很大，在查询时浪费大量的磁盘IO，影响查询效率。

此时可以将字符串的一部分前缀建立索引，不仅可以节省索引的存储空间，也能提高索引效率。

1
create index idex_name on table_name(column(n));

前缀长度

可以根据索引的选择性来确定，选择性是只不从夫的索引值与数据表记录总数记录的比值，索引选择性越高查询效率越高，唯一索引的选择性是1，这是性能最好的所有。

1
select count(distance substring(email,1,5)/count(*)) frome user;

单列索引和联合索引

  • 单列索引：一个索引包含单个列

  • 联合索引：一个索引包含多个列

推荐使用联合索引，创建联合索引时需要考虑索引顺序，需要遵循最左前缀原则，联合索引可以通过覆盖索引的方式直接拿到需要查询的字段，不需要再进行回表查询，从而提高了查询的效率。

索引设计原则

  1. 针对数据量较大，且查询频繁的表建立索引。
  2. 针对常作为查询条件、排序、分组操作的字段建立索引。
  3. 选择区分度高的列作为索引作为索引，尽量建立唯一索引，使索引的效率提高。
  4. 如果是字符串类型的字段，字段长度较长的话，可以针对字段的特点建立前缀索引。
  5. 尽量使用联合索引，减少单列索引，在查询时联合索引可以覆盖索引，节省存储空间，并且避免回表查询，提升查询效率。
  6. 要控制索引数量，索引越多，维护索引的代价越大，会影响到增删改的效率。
  7. 如果索引列不能存储NULL值，在创建表时用NOT NULL约束。当优化器知道每列是否包含NULL值时，可以更好的确定用那个索引进行查询。