Mysql——SQL性能优化基础知识

最新推荐文章于 2022-02-21 21:08:45 发布

保暖大裤衩LeoLee

最新推荐文章于 2022-02-21 21:08:45 发布

阅读量267

点赞数

分类专栏： Mysql 文章标签： mysql 索引 btree b树

本文链接：https://blog.csdn.net/qq_25805331/article/details/112882019

版权

Mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

数据库查询慢基本与以下几点相关

查询语句写的烂
索引失效
关联查询太多
服务器调优差，Mysql参数配置不理想

作为后端开发，其实最关注的是前三点，第三点有些特殊，可能工作中很多情况是不得已才会写出一个很多表关联的SQL，比如表结构设计已经定性，由于业务的发展，导致之前看似合理的表结构在当下业务中显得不再合理，亦或者是某些“令人头疼”的不合理业务要求造成的。

优化基础

这一块不想看的同学可以直接跳过QAQ

SQL的执行顺序

SQL的执行顺序其实很多稍微研究过的朋友已经知道了，我们“手写”的SQL和数据库理解的SQL顺序其实是不一样的。

人理解的SQL

select
    <fields>
from
    <mainTable>
    join <othorTable> on <referenceFiled>
    ...
where
    <selectCondition>
gourp by
    <gourpByCondition>
having
    <havingCondition>
order by
    <orderByCondition>
...

数据库理解SQL

from
    <mainTable>
on
    <referenceFiled>
<join_type> join <othorTable>
...
where
    <selectCondition>
gourp by
    <gourpByCondition>
having
    <havingCondition>
select
    <fields>
order by
    <orderByCondition>
limit
    <offset>, <rows>
...

由于数据库解析执行SQL的顺序与开发者编写SQL的顺序不一致，所以在编写SQL时候存在很大的优化空间。

JOIN

这个就不用说了吧QAQ

内连接：查询两表之间的交集

select 
    * 
from 
    A a
    inner join B b on a.KEY = b.KEY

左连接：查询左表的全部以及与右表的交集

select
    *
from
    A a
    left join B b on a.KEY = b.KEY

右连接：查询右表的全部以及与左表的交集

select
    *
from
    A a
    right join B b on a.KEY = B.KEY

只查询左表中不包含与右表的交集

select
    *
from
    A a
    left join B b on a.KEY = b.KEY
where
    b.KEY = NULL

同上，只查询右表中不包含与左表的交集

select
    *
from
    A a
    right join B b on a.KEY = B.KEY
where
    a.KEY = NULL;

全连接（Mysql用UNIO，非FULL OUTER JOIN）

select
    *
from
    A a

UNION

select
    *
from
    B b

索引

什么是索引？

索引是一种帮助数据库提高数据检索效率的数据结构。索引的目的是提高查询的效率，类比查字典，相当于对数据进行规整排序，经排序的数据查询起来效率肯定是提升的。也可以说索引是一种有序的快速查找数据结构。严格的来讲，在数据之外，数据库还维护着一套满足特点查找查找算法的数据结构，这些数据结构以某种方式指向对应的数据，可以通过这种特殊的数据结构实现高效的数据查找，这种数据结构就是索引。如最常见的B+tree索引，就是一种多路搜索树结构，除此之外还有哈希树索引等。不过日常开发中说到索引指的就是B+tree索引。

索引的优势

提高数据的检索效率，减少表数据的扫描，降低数据库I/O的成本。
通过索引列对数据进行排序，降低数据的排序成本，降低了CPU的消耗占用。

索引的劣势

实际上索引也是一张表，保存了主键和索引字段，并且指向了实体表的记录，所以索引列也是要占用空间的。
索引会降低更新表数据的速度，因为insert、update、delete数据的时候，数据库除了对操作数据进行变更外，还要变更索引列的字段，调整由于更新表数据带来的索引信息的变更。

索引的操作

CREATE [UNIQUE] INDEX indexName ON table_name (column_name);

ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName);

DROP INDEX [indexName] ON tableName;

SHOW INDEX FROM tableName;

//特殊语句
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list); 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
以下实例为在表中添加索引

UNIQUE：唯一索引
只有一个column就是单列索引，多个column就是符合索引

索引的原理

简单的来讲，就是有一棵用于检索的树，最末端的是叶子节点，只有叶子节点指向的数据是真实的表数据，每次检索的时候，从树的最上层在每个分支节点进行大小的比较，比较对象为两个，如O1和O2，当检索对象小于O1的时候会有指针P1指向下一个分支节点，大于O1小于O2的时候会有指针P2指向另一个下一层的分支节点，大于O2同理。像这样的检索方式可以过滤掉大量的数据，极大的减少了数据库的I/O。

索引建立的依据或者是条件

适合

设置一个字段为主键后自动创建其索引。
频繁作为查询条件的字段应该建立索引。
查询中与其他表关联的字段可以建立字段。
频繁更新的字段不适合建立索引。
where条件中用不到的字段不要创建索引。
单列索引 or 复合索引？
- 高并发下倾向于复合索引
查询中的排序字段。
查询中统计或者分组的字段。

不适合

表记录太少
经常删改的表
重复数据较多的字段

SQL性能分析

之前有写过EXPLAIN的使用，再提一次加深记忆

通常来讲，Mysql的底层会对SQL进行一些列的执行优化，实际上我们口中所说的SQL优化本质就是配合Mysql的执行优化策略。

EXPLAIN关键字可以模拟数据库优化器执行SQL查询语句，从而分析得出Mysql是怎么处理该条查询语句的，通过分析结果可以相应的得出查询语句的优化点或者是表结构设计缺陷。

其反应的信息大致如下：

表的读取顺序
数据读取操作类型
哪些索引可以使用
哪些索引实际的使用到了
表之间的引用
每张表被检索的数据量

id
- id相同的情况下，表检索的顺序自上而下进行。
- id不同，表检索的顺序是按照id值从大到小执行。
- 在既有id相同，又有id不同的情况下，id大的先执行，id相同的自上而下顺序执行。
select_type
- SIMPLE：简单的select查询，查询中不包含子查询和union。
- PRIMARY：查询中包含任何复杂的子查询，查询语句中最外层的查询就是主查询。通常会最后执行。
- SUBQUERY：在select或者where列表中包含了子查询。
- DERIVED：在from列表中的子句会被标记为DERIVED，Mysql会递归执行这些子查询，并将结果放在临时表中。
  - DERIVED[id]，DERIVED后面的数字代表该临时表对应的查询id
- UNION：若select出现在UNION之后，该段检索语句会被标记为UNION。若UNION包含在FROM子句的子查询中，外层查询将会被标记为DERIVED
- UNION RESULT：从UNION表中查询的结果的select会被标记为UNION RESULT
type：查询所用的访问类型，如下是常见类型从最好类型到最差类型的排序（所有的包括：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL，一般来说需要保证查询能够达到range，最好能达到ref）
- system：等于系统表，const类型的特例，日常开发也不一定出现，可以忽略。
- const：标识通过索引一次就可以查找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键至于where条件中进行比较（... where id = 123），Mysql就能将该查询转换为一个常量。
- eq_ref：唯一性的索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描。如使用手机号、身份证号等唯一索引字段与人员表做关联查询，只能匹配到一条数据。
- ref：非唯一性索引扫描，返回匹配某个单值的所有行，换句话说，其也是一种索引的访问，可能回找到多个复合条件的行而已，属于查找和扫描的混合体。
- range：只检索给定范围的行，使用一个索引来选择行，一般就是在where语句中出现的between、<、>、in等范围性的过滤条件。这种范围性的扫描索引肯定比全表扫描要好，因为只需要开始于索引的某个点，结束与索引的某个点，不用全量扫描索引。
- index：Full index scan，index与ALL的区别在于index只遍历索引数，而ALL是真正的全表，由于索引文件比实际数据要小的多，虽然index和ALL都是读全表，但是index是读索引，ALL是读硬盘数据。
- ALL：检索是全表扫描
possible_keys：显示可以应用在这张表上的所有索引，查询涉及到的字段上若存在索引，则这些索引将被列出，但是这些索引并不一定被查询实际所用。
key：
- 查询实际使用的索引，如果为NULL，则没有使用索引。
- 若查询中使用了覆盖索引，则该索引会出现在key的列表中。如查询column1，column2，正好存在一个复合索引是使用column1和column2创建的，且查询顺序和复合索引的字段顺序一致，则查询就可以直接通过该复合索引检索结果，就不需要去表中进行数据扫描了。
key_len：标识索引中使用的字节数
- key_len显示的值为索引字段的最大可能长度，并非实际使用长度，根据表定义来计算得出，并非通过表内检索得出
- 查询语句中，在不损失精确性（where列表中条件的过滤粒度）的情况下，长度越短越好。
ref：显示索引的哪一列被使用了，经常是与其他表建立关系的字段或者是常量const
rows：根据表统计信息以及索引使用情况，大致的估算出查找目标数据所需读取的行数。
Extra：主要关注Using filesort、Using temporary、Using index
- Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取（Mysql中无法使用索引完成的排序成为“文件排序”），效率较低。
  - ```
  t1表创建了复合索引 idx_t1_col1_col2_col3,
  create index idx_t1_col1_col2_col3 on t1 (col1, col2, col3);
  
  //Extra Using where, Using index, Using filesort
  select
      col1
  from t1
  where col1 = "xxx"
  order by col3;
  
  //Extra Using where, Using index
  select
      col1
  from t1
  where col1 = "xxx"
  order by col2, col3;
```
  左前缀法则，参考其他博主的文章：SQL 索引最左前缀原理
- Using temporary：使用了临时表保存中间结果，Mysql在查询结果排序的时候使用了临时表，效率极低，常见于order by和group by。
  - ```
  t1表创建了复合索引 idx_t1_col1_col2,
  create index idx_t1_col1_col2 on t1 (col1, col2);
  
  //Extra Using where, Using index, Using temporary, Using filesort
  select
      col1
  from t1
  where col1 in ("xxx", "yyy", "zzz")
  order by col2;
  
  //Extra Using where, Using index for group by
  select
      col1
  from t1
  where col1 in ("xxx", "yyy", "zzz")
  order by col1, col2;
```
  创建临时表、存放中间数据、清除临时表非常损伤性能，数据量越大性能损失越明显。
- Using index：表明select语句使用了覆盖索引，避免了访问表数据，效率不错。
  - 如果同时出现Using where，表明索引被用来执行索引键值的查找
  - 如果没有同时出现Using where，则表明索引被用来读取数据而非执行查找操作。
  - 什么是覆盖索引（Covering Index）：select的数据列只需从索引中就能获取到，不必读取表数据。也就是Mysql可以通过索引直接返回select列表中的字段，并不会根据索引去关联数据文件检索。概括来说查询列被索引覆盖了。查询的字段必须和复合索引的字段一致，顺序一致。
- Using where：表明使用了where过滤
- Using join buffer：使用了连接缓存
- impossible where：条件列表的结果始终为false，不能用来获取任何元组，如：where name = "aaa" and name = "bbb"
- distinct：优化distinct操作，在找到第一条匹配的元组数据后就停止相同值的检索。
- select tables optimized away：在没有gourp by的情况下，基于索引优化MIN/MAX操作或者对MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划完成的阶段即完成优化。

单表优化案例

建表语句

CREATE TABLE IF NOT EXISTS article (
	id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键自增',
	author_id INT(10) UNSIGNED NOT NULL COMMENT '作者',
	title VARCHAR(255) NOT NULL COMMENT '文章标题',
	content TEXT NOT NULL COMMENT '文章内容',
	category_id INT(10) UNSIGNED NOT NULL COMMENT '分类id',
	views INT(10) UNSIGNED NOT NULL COMMENT '查阅数',
	comments INT(10) UNSIGNED NOT NULL COMMENT '评论数'
);

INSERT INTO article (author_id, title, content, category_id, views, comments) 
VALUES
(1, 'title1', 'content1', 1, 1, 1)
,(2, 'title2', 'content2', 2, 2, 2)
,(1, 'title3', 'content3', 1, 3, 3);

COMMIT;

查询category_id为1的且comments大于1的情况下，views最多的作者。

SELECT
	*
FROM article a
WHERE a.category_id = 1 AND a.comments > 1
ORDER BY views DESC
LIMIT 1;

执行计划：

显然意见的最明显的问题就是Using filesort
其次是查询类型为ALL，全表扫描

尝试创建category_id、comments、views三者的复合索引

create INDEX idx_ccv ON article(category_id, comments, views);

创建索引成功

创建索引过后的执行计划如下

使用上了索引
使用的索引之后，type从ALL优化为range
Using filesort仍旧曾在

首先为了讲解方便，修改一下sql如下

EXPLAIN
SELECT
	*
FROM article a
WHERE a.category_id = 1 AND a.comments = 1
ORDER BY views DESC
LIMIT 1;

由于comments > 1变成了comments = 1，range变成了ref，ref的关联列也是const，Using filesort问题也得到了处理，但是这样就不是目标的查询结果。但是可以得出结论：

between、<、>、in等范围性的过滤条件打断了索引列

由于复合索引的column顺序是 category_id、comments、views
按照B-tree索引的排序原理来讲，首先category_id进行排序，如果遇到相同的category_id则再对comments进行排序
但是由于comments处于三个索引列的中间位置，且出现了comments > 1的范围条件，打断了索引，导致Mysql无法使用索引对之后的views进行检索，也就是索引失效了，Mysql只能通过表中元数据进行排序，继而产生了Using filesort。

删除索引idx_ccv，建立索引idx_cv：

DROP INDEX idx_ccv ON article;
create INDEX idx_cv ON article(category_id, views);

优化过后执行计划如下：

两表索引优化案例

建表语句（主要是为了主外键关联，表和字段没有什么实际意义）

CREATE TABLE IF NOT EXISTS `class` (
	id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS book (
	book_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY(book_id)
);


-- 插入的测试数据语句可以多执行几次
INSERT INTO class (card) VALUES (FLOOR((1+(RAND()*20))));
INSERT INTO class (card) VALUES (FLOOR((1+(RAND()*20))));
INSERT INTO class (card) VALUES (FLOOR((1+(RAND()*20))));

INSERT INTO book (card) VALUES (FLOOR((1+(RAND()*20))));
INSERT INTO book (card) VALUES (FLOOR((1+(RAND()*20))));
INSERT INTO book (card) VALUES (FLOOR((1+(RAND()*20))));

没有任何索引的情况下以class为主表left join book查询

EXPLAIN SELECT * FROM class c LEFT JOIN book b ON c.card = b.card;

可以看出两表的关联查询都出现了type为ALL，全表检索的情况，需要加索引优化，但是怎么加，加在哪张表的card字段呢？

给book表的card字段建立索引（给LEFT JOIN中的右表）

CREATE INDEX idx_book_card ON book(card);

可以看到book表使用了索引之后，type从ALL变味了ref，索引关联列为card，检索行数由22行变为了1。性能提升明显。

给class表的card字段建立索引（给LEFT JOIN中的左表）

DROP INDEX idx_book_card ON book;
CREATE INDEX idx_class_card ON class(card);

虽然class表使用了索引，但是type也仅仅为index，且通过rows数据得出，两张表检索的行数并没有任何变化

由于左连接的特性决定，由于左连接的左表的数据一定都有，会根据左表在右表中检索，所以右表是优化的侧重点，索引应该加在右表中，同理，右连接也是如此。

三表索引优化案例

创建第三张表t3，删除所有索引。

CREATE TABLE IF NOT EXISTS t3 (
	id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY(id)
);


-- 插入的测试数据语句可以多执行几次
INSERT INTO t3 (card) VALUES (FLOOR((1+(RAND()*20))));
INSERT INTO t3 (card) VALUES (FLOOR((1+(RAND()*20))));
INSERT INTO t3 (card) VALUES (FLOOR((1+(RAND()*20))));

在没有索引的情况三表查询如下

EXPLAIN
SELECT 
	*
FROM class c 
LEFT JOIN book b ON c.card = b.card
LEFT JOIN t3 t ON b.card = t.card;

按照两表索引优化的结论，我们给book和t3表建立索引

CREATE INDEX idx_book_card ON book(card);
CREATE INDEX idx_t3_card ON t3(card);

由以上三个例子还可以得出如下结论

尽量减少join语句中的NestedLoop次数：永远要用数量级小的表驱动大的表。
优先去优化NestedLoop的内层循环
保证驱动表上的join条件字段建立索引