Knightdreams'Blog

Appearance

索引是什么 ​

Mysql官方对索引的定义：索引（Index）是帮助Mysql高效获取数据的 数据结构。因此索引的本质就是 数据结构。索引的目的在于提高查询效率，可类比字典、书籍的目录等这种形式。

可简单理解为“排好序的快速查找数据结构”。在数据之外，数据库系统还维护者 满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现 高级查找算法，这种数据结构就是 索引。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以 索引文件的形式存储在磁盘上。

平常所说的索引，如果没有特别指明，都是B树索引。其中聚集索引、次要索引、覆盖索引、前缀索引、唯一索引默认都是用B树。

通过show index from tablename可以查看表的索引情况。

索引的优缺点 ​

优点

  1. 类似大学图书馆的书目索引，提高数据的检索效率，降低数据库的IO成本。
  2. 通过索引列对数据进行排序，降低数据的排序成本，从而降低CPU的消耗。

缺点

  1. 索引实际上也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间。
  2. 虽然索引大大提高了查询效率，但是降低了更新表的速度，如insert、update和delete操作。因为更新表时，Mysql不仅要保存数据，还要保存索引文件每次更新的索引列字段，并且在更新操作后，会更新相应字段索引的信息。
  3. 索引只是提高查询效率的一个因素，如果有大量的数据表，就需要花时间研究建立最优秀的索引或优化查询语句。

索引分类 ​

  1. 单值索引：一个索引只包含单个列，一个表可以有多个单值索引
  2. 唯一索引：索引列的值必须唯一，但允许有空值，主键就是唯一索引
  3. 复合索引：一个索引包含多个列

索引的结构：

BTREE索引；Hash索引；Full-Text索引；R-Tree索引。

基本语法 ​

创建索引 ​
sql
create [unique] index indexname on tablename(columnname(length));
alter table tablename add index indexname (columnname(length));

注：如果是char、varchar类型的字段，length可以小于字段实际长度；如果是blob、text类型，必须指定length。

删除索引 ​
sql
drop index indexname on tablename;
查看索引 ​
sql
show index from tablename;
其他创建索引的方式 ​
sql
# 添加主键索引
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) 

# 添加唯一索引
ALTER TABLE `table_name` ADD UNIQUE (`column`)

# 添加全文索引
ALTER TABLE `table_name` ADD FULLTEXT (`column`)

# 添加普通索引
ALTER TABLE `table_name` ADD INDEX index_name (`column`)

# 添加组合索引
ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)

建立索引与否的具体情况 ​

需建立索引的情况 ​
  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 高并发下趋向创建组合索引
  5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  6. 查询中通过或分组的字段
不需要建立索引的情况 ​
  1. 表记录太少
  2. 经常增删改的表
  3. 数据重复且平均分配的字段，如国籍、性别，不适合创建索引

索引优化 ​

索引优化的目的主要是让索引不失效

1.最佳左前缀法则 ​

在创建了多列索引的情况下，查询从索引的 最左前列开始且不能跳过索引中的列。

最佳左前缀法则就是说如果创建了多个索引，在使用索引时要按照创建索引的顺序来使用，不能缺少或跳过。（如果使用跳过开头则索引失效、如果跳过中间则后面索引失效）

通俗理解：“带头大哥不能死，中间兄弟不能断”。要点：“头不能掉”。

2.不要在索引列上做任何操作 ​

在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效从而转向全表扫描。

3.范围右边索引列全失效 ​

存储引擎不能使用索引中范围右边的列

当使用 age > 27 这种范围时，会导致范围右边的索引列失效

4.尽量使用覆盖索引 ​

尽量使用覆盖索引（查询列和索引列尽量一致，通俗说就是对A、B列创建了索引，然后查询中也使用A、B列），减少select *的使用。

explain执行计划显示：Extra从Null变成了Using index，提高检索效率。

5.使用不等于（!= 或 <>）会使Type = range ​

使用 != 会使type=range，Extra Using index condition

6.is null 或 is not null 也无法使用索引 ​

在使用is null的时候，索引完全失效，使用is not null的时候，type=ALL全表扫描，key=Null索引失效。

7.like通配符以 % 开头会使索引失效 ​

like的%位置不同，所产生的效果不一样，当%出现在左边的时候，type=ALL，key=Null（全表扫描，索引失效），当%出现在右边的时候，type=range，索引未失效。

口诀：like百分加右边。

但是在实际生产环境中，%仅出现在右边可能不能够解决我们的问题，所以解决%出现在左边索引失效的方法：使用覆盖索引。

8.字符串不加单引号导致索引失效 ​

varchar类型的字段，在查询的时候不加单引号(进行了类型转换)导致索引失效，转向全表扫描

9.少用or，用or连接会使Type = range ​

在使用or连接的时候会使type = range、Extra = Using where; Using index

索引分析结论 ​

case 1、

在执行常量等值查询时，改变索引列的顺序并不会更改explain的执行结果，因为mysql底层优化器会进行优化，但是推荐按照索引顺序列编写sql语句。

case 2、

范围右边索引列失效，但是范围当前位置的索引是有效的

范围右边索引列失效，是有顺序的：c1,c2,c3,c4，如果c3有范围，则c4失效；如果c4有范围，则没有失效的索引列，从而会使用全部索引。

如果在c1处使用范围，则type=ALL，key=Null，索引失效，全表扫描，这里违背了最佳左前缀法则，带头大哥已死，因为c1主要用于范围，而不是查询。解决方式使用覆盖索引。

结论：在最佳左前缀法则中，如果最左前列（带头大哥）的索引失效，则后面的索引都失效。

总结：

①最佳左前缀法则。

1.在等值查询时，更改索引列顺序，并不会影响explain的执行结果，因为mysql底层会进行优化。

2.在使用order by时，注意索引顺序、常量，以及可能会导致Using filesort的情况。

②group by容易产生Using temporary。

orderBy 优化 ​

在使用order by时，经常出现Using filesort，

因此对于此类sql语句需尽力优化，使其尽量使用Using index。

总结：

  1. MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

  2. order by 满足两种情况会使用 Using index

    • order by语句使用索引最左前列。
    • 使用where子句与order by子句条件列组合满足索引最左前列。

  3. 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最佳左前缀法则。

  4. 如果order by的条件不在索引列上，就会产生Using filesort。

    filesort有两种排序算法：双路排序和单路排序。

  5. 提升order by速度的方式：

    • 在使用order by时，不要用select *，只查询所需的字段。因为当查询字段过多时，会导致sort_buffer不够，从而使用多路排序或进行多次I/O操作
    • 尝试提高sort_buffer_size
    • 尝试提高max_length_for_sort_data

  6. group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。当无法使用索引列的时候，也要对sort_buffer_size和max_length_for_sort_data参数进行调整。注意where高于having，能写在where中的限定条件就不要去having限定了