索引基础知识

聚簇索引与非聚簇索引

聚簇索引

如何建立聚簇索引：

• 主键就是一个聚簇索引，每个表只能有一个聚簇索引。

• 如果没有定义主键，数据库会使用第一非空的唯一索引作为聚簇索引 。
• 如果既没有主键也找不到合适的非空索引，那么InnoDB会自动生成一个不可见的聚簇索引，随着插入而自增。

聚簇索引特点：

• 叶子节点包含该行的所有信息，找到叶子节点不需要回表，直接可以去除该行数据的所有信息

  回表：普通索引查询方式，需要先搜索非聚簇索引，接着回到聚簇索引再搜索一次，这个过程称为回表。

• 每个表只能有一个聚簇索引。

• 聚簇索引中，数据的排列顺序按照聚簇索引来排序。

非聚簇索引

非聚簇索引的特点

• 可以有多个
• 建立了聚簇索引才能有非聚簇索引
• 非聚簇索引叶子节点的数据是主键值

二者比较

• 查询速度

  聚簇索引的查询速度由于非聚簇索引

• 插入速度

  非聚簇苏索引的速度优于聚簇索引

• 聚簇索引存储记录在物理上是连续存在的，而非聚簇索引是逻辑上连续，物理存储不连续

非聚簇索引缺陷弥补

非聚簇索引是需要回表查询的，如何避免回表查询或减少回表次数？

使用联合索引、覆盖索引、索引下推来解决上述问题。

索引使用场景

需要索引

1. 主键自动建立唯一索引
2. 经常出现在where后的字段
3. 排序的字段
4. 分组字段
5. 多表关联中，on两边的关联字段都应该创建索引
6. 联合索引

不需要索引

1. 表记录很少
2. 需要频繁更新的表
3. 查询字段使用频率不高

索引有效情况和注意事项

1. 尽量使用全值匹配

   在联合索引中，尽量使条件与索引一一对应

2. 遵守最左前缀法则

   查询从建立的索引最左前列开始，并且不跳过中间索引

3. 索引列不做计算

   应该把索引列放在表达式的左边，不添加任何函数或其他形式，其他条件放在表达式右边

4. 范围条件右边的列失效

   范围条件：between、like、<、>、in，右边的列索引会失效

   举例：

   -- t上存在联合索引(name,age,sex)
   -- name和age会使用联合索引,但sex不会
   select name,age from t where name='张三' and age>18 and sex='F'
   

5. 尽量使用覆盖索引

   覆盖索引，只查询索引的列不回表，可以提升查询效率

6. 索引字段使用不等，索引会失效。

   在索引字段上使用!=或<>时，索引会失效导致全表扫描。

   注意：逐渐索引会使用范围索引，辅助索引会失效。

7. 主键索引字段上不可以判断null？

   如果在主键字段上判断is null，主键索引会失效

8. 索引字段使用like，以%开头

   以%开头将不使用索引，其他字符开头均使用索引

9. 索引字段字符串、日期要加单引号

   如果查询时不加单引号，会导致索引失效转向全表扫描

10. 索引字段不要使用or

    只要有or，所有的索引都会失效

联合索引

联合索引：对表上多个列进行索引，也叫组合索引、复合索引、多列索引。

联合索引的优势：可以通过联合索引实现覆盖索引，不回表查询，提高查询效率。

联合索引最左匹配原则

联合索引在使用的时候要注意顺序和最左匹配原则

：使用联合索引检索数据时，从索引中最左边的列开始，一直向右匹配，如果遇到范围查询（>、<、between、like等），会停止匹配，哪怕后面还有索引。

示例

对字段(a,b,c)建立联合联合索引

-- 以下内容可以使用索引
where a = xxx
where a = xxx and b = xxx
where a = xxx and b = xxx and c = xxx

where a like 'xx%'
where a > xxx

where a = xxx order by b -- 注意:另一个索引写在order by这里也可以

-- 如下查询也会使用索引,MySQL优化器会自动调整顺序
where b = xxx and a = xxx
where a = xxx and and c=xxx b = xxx 

-- 以下只会使用索引的一部分
where a = xxx and c = xxx         -- 使用索引(a)
where a like 'xxx%' and b = xxx   -- 使用索引(a)
where a > xxx and b = xxx         -- 使用索引(a)

-- 以下查询不使用索引
where b = xxx
where c = xxx
where a like '%xxx'        -- 不满足最左前缀
where d = xxx order by a  
where a +1 = xxx           -- 使用函数、运算表达式以及饮食类型转换

1. 如果查询是where a=xxx and b=xxx and c=xxx

   建立索引时，可以将选择性高的列放在索引最前列，选择性低的放在后边

2. 如果是where a>xxx and b=xxx或where a like 'xxx%' and b=xxx

   建立索引时，将范围索引建立在后面,即建立(b,a)索引

3. 如果是where a = xxx order by b

   根据顺序建立索引即可

索引覆盖

建立了联合索引后，直接在索引中就可以得到查询结果，从而不需要回表查询聚簇索引中的行数据信息。

覆盖索引好处：

• 辅助索引不包含数据的所有信息，大小远远小于聚簇索引，可以减少大量IO操作。

• 覆盖索引只需要扫描一次索引树，不需要回表扫描聚簇索引树，所以性能比回表查询高。

• 索引中列值按顺序存储，覆盖索引能避免范围查询回表带来的大量随机IO操作。

执行explain命令，如果extra处出现了using index就表示用到了索引，并且所取的数据完全在索引中能拿到，即使用了覆盖索引。

索引下推

索引下推是MySQL 5.6及以上版本推出的，用于对查询进行优化。

索引下推把本应该在server层进行筛选的条件，下推到存储引擎层来进行筛选判断，这样就能有效减少回表。

• 举例

  有联合索引(name,age)，有如下查询

  select * from t where name like 'L%' and age=17
  

  根据最左匹配原则，上述查询使用的索引是name，age索引没有用到，原因：范围查询右边的索引会失效。但是这样age的信息就浪费了，因为已经存储在了索引中

  不用索引下推的执行过程为

  ：

  1、索引找出name包含'L'的全部行
  2、逐一回表扫描，从聚簇索引中找到对应的行数据,将找到的行返回给server层
  3、在server层进行判断age=17,得到最终结果
  

  使用索引下推的执行过程为

  1、索引找出name包含'L'的全部行
  2、根据age=17这个条件,对上述查找获得行进行判断筛选,保留符合条件的
  注:这一步不回表，是对查找得到的索引数据进行判断
  3、将2筛选完的数据,回表扫描,最终找到的行数据返回给server层
  

好处：使用索引下推的方式减少了回表次数，提高了查询速度。

索引下推注意事项：前提是索引中有where后字段的内容。

默认情况下，MySQL开启索引下推，通过下列命令开启或关闭。

-- 关闭索引下推
set optimizer_switch='index_condition_pushdown=off'; 
-- 开启索引下推
set optimizer_switch='index_condition_pushdown=on';