mysql查询优化

亦凉 2021-11-23 11:58 478阅读 0赞

# 数据库查询优化 #

> Innodb存储引擎支持以下几种常见的索引：

*  B+树索引
 *  全文索引
 *  哈希索引

**Innodb存储引擎支持的哈希索引是自适应的，Innodb存储引擎会根据表的使用情况自动生成哈希索引，不能认为干预是否生成哈希索引。**

# mysql 创建索引和删除索引方法 #

> 索引的创建可以在`CREATE TABLE`语句中进行，也可以单独用`CREATE INDEX或ALTER TABLE`来给表增加索引。删除索引可以利用`ALTER TABLE`或`DROP INDEX`语句来实现

alter table table_name add index index_name (column_list) ;
    alter table table_name add unique (column_list) ;
    alter table table_name add primary key (column_list) ;

> 其中包括普通索引、`UNIQUE`索引和`PRIMARY` `KEY`索引3种创建索引的格式，table\_name是要增加索引的表名，column\_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index\_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。

> 使用CREATE INDEX语句对表增加索引。能够增加普通索引和UNIQUE索引两种

create index index_name on table_name (column_list) ;
    create unique index index_name on table_name (column_list) ;

### 删除索引。 ###

> 删除索引可以使用`ALTER TABLE`或`DROP INDEX`语句来实现。`DROP INDEX`可以在`ALTER TABLE`内部作为一条语句处理，其格式如下：

drop index index_name on table_name ;
    alter table table_name drop index index_name ;
    alter table table_name drop primary key ;

> 其中，在前面的两条语句中，都删除了`table_name`中的索引`index_name`。而在最后一条语句中，只在删除`PRIMARY KEY`索引中使用，因为一个表只可能有一个`PRIMARY KEY`索引，因此不需要指定索引名。如果没有创建`PRIMARY KEY`索引，但表具有一个或多个`UNIQUE`索引，则`MySQL`将删除第一个`UNIQUE`索引。果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

# B+树索引 #

> B+树索引是真正的索引数据结构，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。  
> \[外链图片转存失败(img-yTfvnx2A-1564136718757)(D:/ideaworkspace/zhaoshang/PlatfromGroupShare/source/\_posts/mysql/B+.png)\]

### B+树查找过程 ###

> 如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

### 为什么要使用B+树 ###

> 通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有`h=㏒(m+1)N`，当数据量N一定的情况下，**m越大，h越小**；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，**如果数据项占的空间越小，数据项的数量越多，树的高度越低**。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这**也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高**。当数据项等于1时将会退化成线性表。**这也就是不用B-树作为索引数据结构，因为B-树中的的数据项保存的是原始的数据。导致数据项太大，导致树的高度增高**

> 当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；**但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询**。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， **这个是非常重要的性质，即索引的最左匹配特性**。

### 建索引的几大原则 ###

*  最左前缀匹配原则，非常重要的原则\*\*，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配\*\*，比如`a = 1 and b = 2 and c > 3 and d = 4`如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
 *  **=和in可以乱序**，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
 *  尽量选择区分度高的列作为索引,区分度的公式是`count(distinctcol)/count(*)`，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，**一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录**
 *  **索引列不能参与计算，保持列“干净”，比如`from_unixtime(create_time) = ’2014-05-29’`就不能使用到索引**，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成`create_time = unix_timestamp(’2014-05-29’)`;
 *  尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
    
    # B+树索引种类 #
 *  聚集索引
 *  辅助索引

### INNODB聚集索引 ###

> 对于表中数据存储，Innodb存储引擎采用了聚集的方式，因此每张表中的存储都是按主键的顺序进行存放，如果没有显式的在定义表时指定主键，Innodb存储引擎会为每一行生成一个6字节的RowID，并以此作为主键。聚集索引就是按照每张表的主键构造一棵B+树。同时叶子节点中存放的即是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。**聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分**。由于数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。查询优化器倾向于采用聚集索引，因为聚集索引能够在B+树的叶子节点找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快针对范围值的查询，查询优化器能够快速发现某一段范围的数据页进行扫描。Innodb采用的是聚集索引。

\[外链图片转存失败(img-wXVf8OWK-1564136718760)(聚集索引.png)\]

> 聚集索引给人错觉就是好像按顺序连续存储的，其实聚集索引就是逻辑上按顺序连续存储。物理上并不是。主要有两点：一是页是通过双向链表，页是按照主键的顺序排序，另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以不按照主键存储。

#### 聚集索引的两大优点 ####

*  使用Ordey by对记录进行排序，但是在实际过程中并没有进行所谓的file sort操作，这就是聚集索引的特点
 *  另一个是范围查询，如果要查找主键某一范围内的数据，通过叶子节点的上层节点就可以得到页的范围，之后直接读取数据页即可。

### INNODB辅助索引 ###

> 对于辅助索引也称为非聚集索引，**叶子节点并不包含记录的全部数据**，data域存储相应记录主键的值（这个跟Myisam不一样的是，myisam存放的是地址而不是主键），辅助索引的存在不影响数据在聚集索引中的组织，因此每张表可以有很多个辅助索引，所以当通过辅助索引来寻找数据时，Innodb存储引擎会遍历辅助索引通过叶子级别的指针获得主键索引的主键，然后再通过主键索引引来找到一个完整的行记录，举一个简单的例子：如果在一棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定的主键，如果聚集索引树的高度同样为3，那么还需要还 需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问以得到一个最终的 数据页。所以辅助索引的效率远不如聚集索引（仅限Innodb引擎），所有不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令索引变得过大，非单调字段作为主键在Innodb不是很好的注意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

# mysql主键生产策略 #

> 这里插一个mysql主键生产策略的对比

### Sequence ID ###

> 数据库自增序列或字段

#### 优点 ####

*  简单、代码方便，性能可以接受
 *  对分页或者需要排序的结果很有帮助

#### 缺点 ####

*  不同数据库语法和实现不一致，特别数据库迁移特别麻烦
 *  读写分离，一主多从的情况下，只有一个主库可以生成，单点故障
 *  很难横向扩展
 *  分库分表麻烦

#### 优化方案 ####

针对主库单点，如果有多个master，则每个master库设置起始数字不一样，步长不一样

### UUID ###

> 利用数据库或者程序生成，全球唯一

\#\#\#\#优点：

*  简单方便
 *  全球唯一，遇见数据迁移，系统数据合并，可以快速应对

#### 缺点 ####

*  没有排序，无法保证趋势递增
 *  UUID往往使用字符串存储，查询效率低下
 *  存储空间大
 *  不可读
 *  传输数据量大

#### 优化方案 ####

解决UUID不可读，可以使用UUID to Int64的方法

### GUID ###

微软对接UUID这个标准实现的。优缺点同UUID

### COMB ###

> 通过组合GUID和系统时间，保留UniqueIdentitier的前十个字节，用后6个字节表示GUID生成时间。

#### 优点 ####

*  解决UUID无序的问题，在其主键生成方式提供了Comb算法，保留GUID的10个字节，用另6个字节表示GUID生成的时间。
 *  性能优于UUID

### Twitter的snowflake算法 ###

结果是一个long型的ID，核心思想：使用41bit表示毫秒数，10bit表示机器的ID（5个bit表示数据中心，5bit的机器ID），12bit作为毫秒内的流水好，也就是意味每个节点可以产生4096个ID，最后还有一个符号位，永远是0

#### 优点 ####

*  不依赖数据库，灵活方便，且性能优于数据库
 *  ID按照时间在单机上是递增的

#### 缺点 ####

*  单机上是递增的，但是由于涉及到分布式环境，每个机器时间有差别，也会出现全局不递增情况。

### Mongdb objectID ###

MongoDB官方文档 ObjectID可以算作是和snowflake类似方法，通过“时间+机器码+pid+inc”共12个字节，通过4+3+2+3的方式最终标识成一个24长度的十六进制字符

### [Leaf——美团点评分布式ID生成系统][Leaf_ID] ###

# Myisam索引实现 #

> myisam索引实现和Innodb索引实现差别比较大，myisam索引都是非聚集索引，不论是主键还是非主键的索引叶子节点的data域都是存放**数据记录的地址**  
> \[外链图片转存失败(img-4qlQ6Aff-1564136718762)(D:/ideaworkspace/zhaoshang/PlatfromGroupShare/source/\_posts/mysql/Myisam\_1.png)\]  
> MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。  
> \[外链图片转存失败(img-s21z9tZn-1564136718763)(D:/ideaworkspace/zhaoshang/PlatfromGroupShare/source/\_posts/mysql/Myisam\_2.png)\]  
> 所以不论myisam是否是主键索引还是非主键索引都是辅助索引（不满足聚集所以的定义）  
> 注意：从上面的图表中看到堆表（myisam形式）比索引组织方式(innodb)好像要快一点，但是不能忽视对于排序和范围查找，索引组织表通过B+树的中间节点就可以找到所有的页，虽然堆表在离散读比较有优势，但是一般数据库都有实现预读技来避免多次离散读操作，所以不存在哪个更好更优的问题，没有说绝对的好和坏。

# 哈希索引 #

> 哈希算法对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式，对于缓冲池页的哈希表来说，在缓冲池中page页都有一个chain指针，它指向相同哈希函数值的页。对于除法散列，m的取值为略大于2倍的缓冲池页数量的质数。

### 自适应哈希索引 ###

> 自适应哈希索引经过哈希函数映射到一个哈希表中，因此对于字典型查找速度很快，如：

select * from table  where index_col='xxx'

> 但是对于范围查找就无能为力，范围查找，是不能使用哈希索引的。
> 
> # 全文索引 #

select *　from blog where content like 'xxx%' --可以使用B+树索引的
    select *  from blog where  content like '%xxx%' --不能使用B+树索引

> 全文索引是将存储于数据库的整本书或整篇文章中的任意内容信息查找出的技术，他可以根据需要获得全文有关的文章、节、段、句、词等信息，也可以进行各种统计和分析。Innodb 1.2x版本开始，Innodb存储引擎开始支持全文索引检索。

### 倒排索引 ###

> 全文索引通常使用倒排索引来实现，倒排索引利用 辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。通常使用关联数组实现，其拥有两种表现形式：

*  inverted file index,其表现形式为\{单词，单词所在的文档的ID\}
 *  full inverted index,其表现形式为\{单词,(单词所在文档的ID，在具体文档中的位置)\}

> Innodb存储引擎从1.2x版本开始支持全文检索的技术，其采用的是`full inverted index`的方式，在Innodb存储引擎中将(documentid,position)视为一个ilist，因此在全文检索的表中，有两个列，一个是`word`字段，另一个是`ilist`字段，并且在word字段上设有索引，正如上面所说，倒排索引需要一张辅助表（Auxiliary table），在Innodb存储引擎中，为了提高全文检索的并行性能，共有6张这样的表。Auxiliary table是持久的表，存放在磁盘上。除此之外，Innodb存储引擎的全文索引中，还有一个重要的概念：FTS index cache(全文检索索引缓存)。FTS index cache是一个红黑树结构，根据（word，list）进行排序
> 
> # 查询优化神器 - explain命令 #

### explain详解 ###

> 我们会有时会开慢查询去记录一些执行时间比较久的SQL语句,找出这些SQL语句并不意味着完事了，到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

### explain限制 ###

1. explain根本不会告诉你触发器、存储过程或UDF会如何影响查询
    2. 它并不支持存储过程，尽管手动抽取查询并单独地对其进行explain操作
    3. 它并 不会告诉你Mysql在查询的执行计划中所做的特定优化
    4. 它并不会显示关于查询的执行计划的所有信息
    5. 它并不区分具有相同名字的事物，例如：它对内存排序和临时文件都是用“file sort”。
    6. 可能会误导，例如：它会对一个有着很小limit的查询显示全索引扫描

### 慢查询优化基本步骤 ###

1. 设置SQL_No_cache,运行看看是否真的很慢
    2. where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
    3. 利用explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
    4. `order by limit` 形式的sql语句让排序的表优先查
    5. 了解业务方使用场景
    6. 加索引时参照建索引的几大原则
    7. 观察结果，不符合预期继续从0分析

## 索引使用发生的情况 ##

> 我们可以根据以下语句能够查到某张表的所有索引。假设条件:我们通过以下语句查询出来的包含聚集索引: <emp\_no, title, from\_date>

SHOW INDEX FROM [database].[table];

### 全列匹配 ###

> 当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。这里有一点需要注意，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引。所以下列两个语句查询出来的结果是一模一样

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26' AND emp_no='10001' AND title='Senior Engineer';

### 最左前缀匹配 ###

> 当查询条件精确匹配索引的左边连续一个或几个列时，如<emp\_no>或<emp\_no, title>，所以可以被用到，但是只能用到一部分，即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引，但是key\_len为4(key\_len表示使用的索引长度，是以字节为单位，由于int型占用4个字节，而索引中只包含了1列。索引key\_len是4)，说明只用到了索引的第一列前缀。

#### mysql 中key\_len就算规则 ####

*  如果列可以为空，则数据类型占用的字节的基础上加１，如果是int型,不能为空key\_len是4，可为空key\_len是5。
 *  如果列是变长，则数据类型占用的字节的基础上加2。如Varbinary(10),不能为空，则key\_len=10+2，可以为空key\_len为10+2+1
 *  如果是字符型，则还需要考虑字符集，如果某列定义是varchar(10),且是utf-8，不能为空，则key\_len为10*3+2,可以为空key\_len为10*3+2+1

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';

# 参考文章 #

[MySQL索引原理及慢查询优化][MySQL]  
[MySQL索引背后的数据结构及算法原理][MySQL 1]  
[万字总结：学习MySQL优化原理，这一篇就够了][MySQL 2]  
[MySQL干货之-利用EXPLAIN优化查询][MySQL_-_EXPLAIN]

[Leaf_ID]: https://tech.meituan.com/2017/04/21/mt-leaf.html
[MySQL]: http://tech.meituan.com/mysql-index.html
[MySQL 1]: http://blog.codinglabs.org/articles/theory-of-mysql-index.html
[MySQL 2]: https://dbaplus.cn/news-155-1531-1.html
[MySQL_-_EXPLAIN]: https://www.jianshu.com/p/593e115ffadd