mysql复习第十九天：索引（一）

╰+攻爆jí腚メ 2023-07-07 11:20 42阅读 0赞

### 1、基础介绍： ###

> 排好序的快速查找数据结构

### 2、索引分类： ###

> 1、单值索引：即一个索引包含单个列；  
> 2、唯一索引：索引列的值必须唯一，但允许有空值；  
> 3、复合索引：即一个索引包含多个列；

### 3、语法： ###

> 创建语法：

CREATE [UNIQUE] INDEX indexName ON tableName(columnname(length));

ALTER tableNameADD [UNIQUE] INDEX [indexName] ON (columnname(length));

> 删除：

DROP INDEX [indexName] ON tableName;

> 查看：

SHOW INDEX FROM TABLE;

### 4、创建索引需要注意的内容 ###

> 1、主键自动建立唯一索引；  
> 2、频繁作为查询条件的字段；  
> 3、频繁更新的字段不适合创建索引，因为更新不单单更新了记录还会更新索引，加重IO负担；  
> 4、单键/组合索引的选择，高并发倾向组合索引；  
> 5、查询统计或分组字段；  
> 6、表记录少不要创建索引；  
> 7、经常增删改的表不要创建索引，提高了查询速度，同时会降低更新表的速度，对表进行INSERT,UPDATE和DELETE，mysql不仅要保存数据还要保存索引文件；  
> 8、数据重复且分布均匀的表字段建立索引意义不大；

### 4、EXPLAIN关键字 ###

#### 4.1、查看sql执行计划； ####

> explain + sql语句；

#### 4.2、执行计划包含的信息； ####

![在这里插入图片描述][20200222232921187.png]

*  id：执行select语句或操作表的顺序；

> id相同：执行顺序从上之下；  
> id不同：id值越大优先级越高，越先被执行；

*  select\_type：查询的类型；

> SIMPLE：简单select查询；  
> PREMARY：如果包含复杂查询部分，最外层 查询被标记；  
> SUBQUERY：在select或where中包含了子查询；  
> DERIVED：子查询被标记为临时表；  
> UNION：若第二个select出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层select将被标记为UNION;  
> UNION RESULT：从UNION表获取结果的select

*  table：这一行的数据属于哪张表；
 *  type：显示查询使用了哪种类型；

> system：表中只有一条数据；  
> const：通过索引一次即找到，用于比较primary和unique索引；  
> eq\_ref：唯一索引扫描，对于每个索引键，表中只有一条数据匹配；  
> ref：非唯一索引建，返回匹配某个单独值的所有行；  
> rang：只检索给定范围的行，使用一个索引来选择行；  
> index：全表扫描，只不过是从索引中读取的，比all快；  
> all：全表扫描，从硬盘读取；

*  从最好到最差：

> system>const>eq\_ref>ref>rang>index>all

*  possible\_keys：将理论上用到的索引全部列出；
 *  key：实际用到的索引列出，为null则没有用索引；
 *  key\_len：表示索引用到的字节数，在不损失精度的情况下，数值越小越好；
 *  ref：表示索引的那一列被使用了；
 *  rows：大致记录查找记录数所需要的行数；
 *  Extra：重要的额外信息；

> Using filesor：mysql使用外部索引，而没有用到自建的索引  
> Using temporary：mysql使用了临时表保存中间数据，常见于group by，order by  
> USING index：表示在select中使用了覆盖索引，表示数据全部从索引中查找到了，效率不错，如果同时出现using where，表示索引被用来执行索引键值的查找，如果没有同时出现，表示索引用来读取数据而非执行查找动作  
> using where：表明使用了where过滤；  
> using join buffer：使用了连接缓存；  
> impossible where：where语句没有效果；  
> select tables optimized away：SELECT操作已经优化到不能再优化了（MySQL根本没有遍历表或索引就返回数据了）  
> distinct：优化distinct，在找到第一行匹配的数据之后停止查找相同值的动作；

### 5、如果避免索引失效 ###

*  1、如果是复合索引，查询的时候不要跳过中间相；
 *  2、不要在索引列上面做任何操作(计算，函数，自动或者手动类型转化)，会导致索引失效而转向全表扫描；
 *  3、尽量使用覆盖索引，减少select \* ；
 *  4、在使用不等于(!=或<>)的时候无法使用索引导致全表扫描；
 *  5、is null，is not null也无法使用索引；
 *  6、like以通配符开头(’%dfasdf’)的时候无法使用索引导致全表扫描；

> 解决%开头，使用复合索引，索引包含查询条件列即可

*  7、字符串不加单引号时索引失效；
 *  8、少用or，用他来连接时索引失效；

[20200222232921187.png]: https://img-blog.csdnimg.cn/20200222232921187.png