MYSQL索引优化小记

向右看齐 2022-08-31 00:48 190阅读 0赞

### 文章目录 ###

*  前言
 *  一、什么是索引
 *  二、索引类型
 *   *  主键索引
     *  唯一索引
     *  普通索引
     *  全文索引
     *  组合索引
 *  三、EXPLAIN执行计划（重要）
 *   *  语法
     *  id
     *  select\_type：
     *  type（重要）
     *  possible\_keys
     *  key(重要)
     *  key\_len
     *  ref
     *  rows
     *  Extra
 *  总结
 *   *  为什么不推荐使用select \* from table
     *  排序字段推荐建立索引
     *  建立索引原则

--------------------

# 前言 #

最近断断续续学习[某机构MYSQL优化视频][MYSQL]，只看不复习记录纯属耍流氓。以下为参考别人思维导图及自己所记所记录

--------------------

提示：记录学习，有误欢迎指正

# 一、什么是索引 #

索引是 MySQL 中一种十分重要的数据库对象。它是数据库性能调优技术的基础，常用于实现数据的快速检索。  
索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。（[摘抄自网上][Link 1]）

简而言之：索引就是一种数据结构，方便快速查找数据，提升查询速度

# 二、索引类型 #

## 主键索引 ##

特殊的唯一索引，也叫主索引，根据主键建立的索引，不允许重复，不允许空值

CREATE INDEX index_name ON table(column(length))

## 唯一索引 ##

索引列的值必须唯一，允许有空值

CREATE UNIQUE INDEX indexName ON table(column(length))

## 普通索引 ##

没有唯一性限制的索引

CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) NOT NULL ,
        PRIMARY KEY (`id`)
    );

## 全文索引 ##

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) CHARACTER NOT NULL ,
        `content` text CHARACTER NULL ,
        `time` int(10) NULL DEFAULT NULL ,
        PRIMARY KEY (`id`),
        FULLTEXT (content)
    );

## 组合索引 ##

最左优先，以最左边的为起点任何连续的索引都能匹配上  
遇到范围查询(>、<、between、like%)后面的字段就会停止匹配  
原理：先按照第一个字段排序，第一个字段相同就按照第二个排序

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

> 都只写了添加索引sql，需要删除索引、修改索引等sql的话自行百度吧，这些都简单

# 三、EXPLAIN执行计划（重要） #

在真正的工作中我们优化sql 的依据主要是看执行计划。执行计划就是sql在mysql中sql解析器是如何解析sql进行查询的

## 语法 ##

explain   <具体执行sql>

## id ##

查询的序列号，id越大执行越先，顺序相同则从上往下顺序执行

## select\_type： ##

查询的类型，主要是区别普通查询、联合查询、子查询之类的复杂查询

1.  SIMPLE：简单的 SELECT，不使用 UNIONE 或者子查询。
2.  PRIMARY：最外层 SELECT。
3.  UNION：第二层，在 SELECT 之后使用了 UNION。
4.  DEPENDENT UNION: UNION 语句中的第二个 SELECT，依赖于外部子查询。
5.  UNION RESULT: UNON 的结果。
6.  SUBQUERY：子查询中的第一个 SELECT。
7.  DEPENDENT SUBQUERY：子查询中的第一个 SELECT，取決于外面的查询。
8.  DERIVED：导出表的 SELECT (FROM 子句的子查询）

## type（重要） ##

访问类型，保证查询至少达到range级别，最好能达到 ref

1.  all：全表扫描。
2.  const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。
3.  eq\_ref：最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。
4.  fulltext：进行全文索引检索。
5.  index：全索引扫描。
6.  index\_merge：询中同时使用两个（或更多）索引，然后对索引结果进行合并（merge），再读取表数据。
7.  index\_ subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。
8.  rang：索引范围扫描。
9.  ref: Join 语句中被驱动表索引引用的查询。
10. ref\_ or\_null：！与 ref 的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。
11. system：系统表，表中只有一行数据。
12. unique\_ subquery：子询中的返回结果字段组合是主键或唯一约束。

## possible\_keys ##

可能用到的索引

## key(重要) ##

实际用到的索引

## key\_len ##

索引中使用的字节数（越短越好）

## ref ##

显示索引的哪一列被使用了（最好是个常数）

## rows ##

找到所需记录需要读取的行数（越少越好）

## Extra ##

1.  Using filesort：使用了文件排序，不好
2.  Using temporary：使用了临时表，非常耗性能
3.  Using index：使用了【覆盖索引】，效果不错  
    覆盖索引就是select的数据列只从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读数据文件，减少回表次数，提高查询效率
4.  Using where｜impossible where：前者用到了where条件，后者没有

# 总结 #

## 为什么不推荐使用select \* from table ##

1.  在索引中有个叫覆盖索引的说法，指的在查询时直接可以通过索引返回相关的数据。如果查询返回字段恰好建立了索引，就可以直接返回索引字段大大减少了查询时间
2.  IO成本高\*中很多字段不需要

## 排序字段推荐建立索引 ##

建立索引会对索引进行排序，索引本身已经排好序，速度更快

## 建立索引原则 ##

前提：数据量大，小数据量没必要

1.  唯一
2.  相同数据分布小
3.  经常使用查询条件
4.  排序字段、分组字段
5.  经常修改删除的字段尽量不要建立索引，处理数据后都会处理索引速度慢

> 觉得总结的不错的两个思维导图：[索引优化][Link 2]，[索引优化][Link 3]

[MYSQL]: https://www.bilibili.com/video/BV1KW411u7vy?p=31
[Link 1]: http://c.biancheng.net/view/2604.html
[Link 2]: https://www.processon.com/view/6034b5ade401fd0290f88d67?fromnew=1#outline
[Link 3]: https://www.processon.com/view/6038a7a2f346fb2a7e3c497e?fromnew=1#outline