mysql基础+优化整合（部分）

mysql基础+优化部分整合相关

sql92 sql99

当一条语句中有group by的话，select后面只能跟分组函数和参加分组的字段

一、注意事项：

1.1、笛卡尔积

在两张或多张表未加加条件查询时，匹配次数和加了判定条件的查询一致，加过判定条件后只是展示有效数据。

1.2、内连接于外连接效率比较

[]: 【讨论】左连接和内连接哪个性能更好？-CSDN社区(https://bbs.csdn.net/topics/390963453)

1.3、嵌套子查询注意临时表的使用与否

二、知识点

1、sql 92和sql 99语法

1.1、sql92

SELECT a.name,b.name

FROM a,b

WHERE …AND…AND…

1.2、sql99

用join连接要查询的表，ON后加表连接的条件，WHERE后还可以进行条件筛选

SELECT *

FROM

a JOIN b

ON a.name=b.name

WHERE …

三、索引

3.1、索引的概述

mysql索引是一种用来快速高效获取数据的数据结构，对数据库表中数据根据索引以二叉树形式进行查询

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

3.2、索引的优势和劣势

3.3、索引结构

索引在存储引擎层面使用的，

索引以文件形式存储在磁盘上

3.3.1、BTREE

BTREE又叫多路平衡搜索树，一个m叉的BTREE特性如下

实例：以5叉BTREE为例：

将C N G A H E K Q M F W L T Z D P R X Y S,插入数据

演变过程

1）插入前四个字母 C N G A

2）插入H，n>4，中间元素G字母向上分裂到新的节点

3）插入E，K，Q不需要分裂

4）插入M，中间元素M字母向上分裂到父节点G中

5）插入F,W,L,T不需要分裂

6）插入Z，中间元素T向上分裂到父节点中

7）插入D，中间元素D向上分裂到父结点中。然后插入P,R,X,Y不需要分裂

8）最后插入S，NPQR节点n>5,中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂

3.3.2、B+TREE

3.3.3、mysql中B+TREE

MySQL索引结构对B+TREE进行了优化，在原先基础上，增加一个指向相邻叶子节点的链表指针，形成带有顺序的B+TREE，提高区间访问性能

3.4、索引分类&&语法

3.4.1、索引分类

1）单值索引：一个索引只包含单个列，一个表可以有多个单列索引

2）唯一索引：索引列必须唯一，但允许有空值

3）复合索引：一个索引包含多个列

3.4.2、索引语法

索引可在创建表时同时创建，也可以随时增加新的索引

创建索引语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASc | DESC] 

UNIQUE : 唯一索引（可以为null）

FULLTEXT：全文索引

SPATIAL : 复合索引

index_name：索引名称

[USING index_type] ：指定索引使用类型（默认B+Tree）

tbl_name(index_col_name,…) ：那一张表的那些字段

查看索引

show index from 表名

删除索引

DROP INDEX 索引名  ON  表名

ALERT命令修改和指定索引

3.5、索引的设计原则

​

1、单表优化

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3');

SELECT * FROM ARTICLE;

EXPLAIN SELECT id,author_id
FROM article
WHERE category_id=1
AND comments >1
ORDER  BY views DESC LIMIT 1

CREATE index idx_article_ccv  on article(category_id,comments,views)

SHOW INDEX FROM article

DROP INDEX idx_article_ccv ON article

CREATE INDEX idx_article_cv on article(category_id,views)

查询过后出现

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D337sPWV-1633939163575)(D:\A-工作学习笔记\Typore照片位置\mysql基础\image-20210924164128063.png)]type为ALL Extra出现文件排序

首先根据查询的条件尝试建立索引

建立索引 CREATE index idx_article_ccv on article(category_id,comments,views) 后type变为range可以忍受，但是extra中出现Using filesort是不行的

索引失效原因：

索引按照的是B+TREE索引的工作原理，首先对 category_id 进行排序，当遇到相同的 category_id 再对comments进行排序，如果遇到相同的comments再对views进行排序。 当comments在联合索引中处于中间位置时，因为comments>1是一个范围值（range）MYSQL无法医用索引再对后面的views部分及进行检索，即range类型查询字段后面的索引无效

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-K7LsdYsH-1633939163576)(D:\A-工作学习笔记\Typore照片位置\mysql基础\image-20210924164509768.png)]

建立索引 CREATE INDEX idx_article_cv on article(category_id,views) 完美解决

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vdHvJ3HB-1633939163577)(D:\A-工作学习笔记\Typore照片位置\mysql基础\image-20210924165704894.png)]

2、双表优化

一般来说如果是左连接则加索引到右边表上

右连接查询时要在有索引的要放在左边表

3、三表优化

索引要设置在经常使用的字段中

4、索引优化具体

1、最佳左前缀法则（复合索引）

#定义：在创建了多列索引的情况下，查询从索引的最左前列开始且不能跳过索引中的列。（老大哥不能死，中间兄弟不能断，where后）

最佳左前缀法则就是说如果创建了多个索引，在使用索引时要按照创建索引的顺序来使用，不能缺少或跳过，当然如果只使用最左边的索引列，也就是第一个索引是可以的，通俗理解：“带头大哥不能死，中间兄弟不能断”。要点：“头不能掉”。

#.创建复合索引，并执行explain

create index idx_nameagegender on tb_table(name,age,gender)

火车头单独跑没问题，火车头与直接相连的车厢一起跑也没问题，但是火车头与车尾，如果中间没有车厢，只能火车头自己跑（只使用了部分索引，也就是火车头name的索引）。

在执行批量等值查询时，改变索引列的顺序并不会改变explain的执行结果，因为mysql底层优化器会进行优化，但是推荐按照索引列顺序编写SQL语句

在使用order by时，注意索引顺序、常量，以及可能会导致Using filesort的情况（排序时按照索引的顺序）

---

3、不要再索引列上做任何操作

在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效从而转向全表扫描

---

4、范围右边全失效

存储引擎中不能使用索引中范围右边的列（age>27），也就是说范围右边的索引列会失效，但是范围当前位置（age）的索引是有效的，从key_len可验证

范围右边索引列失效，是有顺序的：c1,c2,c3,c4，如果c3有范围，则c4失效；如果c4有范围，则没有失效的索引列，从而会使用全部索引

---

5、尽量使用覆盖索引（explain->Extra）

尽量使用覆盖索引（查询列和索引列尽量一致，通俗就是对A、B列创建了索引，然后查询中也使用A、B列），减少select * 的使用

---

6、不等于（!=或<>）、is null、is not null 无法使用索引

---

7、like通配符以%开头会使索引失效

（like百分加右边，加左边会导致索引失效，解决方法：使用覆盖索引）

like查询为范围查询，%出现在左边，则索引失效；%出现在右边，索引未失效，即like百分号加右边

但在实际生产环境汇总，%仅出现在右边不一定能解决问题，所以解决%出现在左边索引失效的方法：使用覆盖索引，当select 字段不止索引列时，会导致索引失效

select name,age,gender from tb_table where name like like '%jack%'

---

8、字符串不加单引号导致索引失效，转向全表扫描

---

9、少用or，用or连接会使索引失效

六、查询优化

6.1、小表驱动大表

优化原则：对于MySQL数据库而言，永远都是小表驱动大表。

/**
* 举个例子：可以使用嵌套的for循环来理解小表驱动大表。
* 以下两个循环结果都是一样的，但是对于MySQL来说不一样，
* 第一种可以理解为，和MySQL建立5次连接每次查询1000次。
* 第一种可以理解为，和MySQL建立1000次连接每次查询5次。
*/
for(int i = 1; i <= 5; i ++){
    for(int j = 1; j <= 1000; j++){
        
    }
}
// ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
for(int i = 1; i <= 1000; i ++){
    for(int j = 1; j <= 5; j++){
        
    }
}

IN和EXISTS

/* 优化原则：小表驱动大表，即小的数据集驱动大的数据集 */

/* IN适合B表比A表数据小的情况*/
SELECT * FROM `A` WHERE `id` IN (SELECT `id` FROM `B`)

/* EXISTS适合B表比A表数据大的情况 */
SELECT * FROM `A` WHERE EXISTS (SELECT 1 FROM `B` WHERE `B`.id = `A`.id);

EXISTS：

• 语法：SELECT....FROM tab WHERE EXISTS(subquery);该语法可以理解为：
• 该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（true或是false）来决定主查询的数据结果是否得以保留。

提示：

• EXISTS(subquery)子查询只返回true或者false，因此子查询中的SELECT *可以是SELECT 1 OR SELECT X，它们并没有区别。
• EXISTS(subquery)子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。
• EXISTS(subquery)子查询往往也可以用条件表达式，其他子查询或者JOIN替代，何种最优需要具体问题具体分析。

---

6.2 、ORDER BY优化

数据准备

CREATE TABLE `talA`(
`age` INT,
`birth` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO `talA`(`age`) VALUES(18);
INSERT INTO `talA`(`age`) VALUES(19);
INSERT INTO `talA`(`age`) VALUES(20);
INSERT INTO `talA`(`age`) VALUES(21);
INSERT INTO `talA`(`age`) VALUES(22);
INSERT INTO `talA`(`age`) VALUES(23);
INSERT INTO `talA`(`age`) VALUES(24);
INSERT INTO `talA`(`age`) VALUES(25);

/* 创建索引 */
CREATE INDEX idx_talA_age_birth ON `talA`(`age`, `birth`);

案例

/* 1.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`;

/* 2.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`,`birth`;

/* 3.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`;

/* 4.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`,`age`;

/* 5.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` ORDER BY `birth`;

/* 6.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `birth`;

/* 7.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `age`;

/* 8.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` ORDER BY `age` ASC, `birth` DESC;

ORDER BY子句，尽量使用索引排序，避免使用Using filesort排序。

MySQL支持两种方式的排序，FileSort和Index，Index的效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足两情况，会使用Index方式排序：

• ORDER BY语句使用索引最左前列。
• 使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列。

结论：尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀原则。

如果不在索引列上，File Sort有两种算法：MySQL就要启动双路排序算法和单路排序算法

1、双路排序算法：MySQL4.1之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，対他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。一句话，从磁盘取排序字段，在buffer中进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在MySQL4.1之后，出现了改进的算法，就是单路排序算法。

2、单路排序算法：从磁盘读取查询需要的所有列，按照ORDER BY列在buffer対它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

由于单路排序算法是后出的，总体而言效率好过双路排序算法。

但是单路排序算法有问题：如果SortBuffer缓冲区太小，导致从磁盘中读取所有的列不能完全保存在SortBuffer缓冲区中，这时候单路复用算法就会出现问题，反而性能不如双路复用算法。

单路复用算法的优化策略：

• 增大sort_buffer_size参数的设置。
• 增大max_length_for_sort_data参数的设置。

提高ORDER BY排序的速度：

• ORDER BY时使用SELECT *是大忌，查什么字段就写什么字段，这点非常重要。在这里的影响是：
  • 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会使用单路排序算法，否则使用多路排序算法。
  • 两种排序算法的数据都有可能超出sort_buffer缓冲区的容量，超出之后，会创建tmp临时文件进行合并排序，导致多次IO，但是单路排序算法的风险会更大一些，所以要增大sort_buffer_size参数的设置。
• 尝试提高sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
• 尝试提高max_length_for_sort_data：提高这个参数，会增加用单路排序算法的概率。但是如果设置的太高，数据总容量sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。

---

6.3、group by优化

• GROUP BY实质是先排序后进行分组，遵照索引建的最佳左前缀。

• 当无法使用索引列时，会使用Using filesort进行排序，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置，会提高性能。

• WHERE执行顺序高于HAVING，能写在WHERE限定条件里的就不要写在HAVING中了。

  ---

  6.4.总结

  为排序使用索引

  • MySQL两种排序方式：Using filesort和Index扫描有序索引排序。
  • MySQL能为排序与查询使用相同的索引，创建的索引既可以用于排序也可以用于查询。

  /* 创建a b c三个字段的索引 */
  idx_table_a_b_c(a, b, c)
  
  /* 1.ORDER BY 能使用索引最左前缀 */
  ORDER BY a;
  ORDER BY a, b;
  ORDER BY a, b, c;
  ORDER BY a DESC, b DESC, c DESC;
  
  /* 2.如果WHERE子句中使用索引的最左前缀定义为常量，则ORDER BY能使用索引 */
  WHERE a = 'Ringo' ORDER BY b, c;
  WHERE a = 'Ringo' AND b = 'Tangs' ORDER BY c;
  WHERE a = 'Ringo' AND b > 2000 ORDER BY b, c;
  
  /* 3.不能使用索引进行排序 */
  ORDER BY a ASC, b DESC, c DESC;  /* 排序不一致 */
  WHERE g = const ORDER BY b, c;   /* 丢失a字段索引 */
  WHERE a = const ORDER BY c;      /* 丢失b字段索引 */
  WHERE a = const ORDER BY a, d;   /* d字段不是索引的一部分 */
  WHERE a IN (...) ORDER BY b, c;  /* 对于排序来说，多个相等条件(a=1 or a=2)也是范围查询 */
  

七、SQL慢查询原因分析步骤

sql分析过程

观察，至少跑一天，观察生产的慢sql情况

开启慢查询日志，设置阈值，将查询速度超过阈值的慢sql抓取出来

使用explain+慢SQL分析

使用 show profile

找到问题上报，

========总结

1、慢查询的开启并捕获

2、explain+慢SQL分析

3、show profile查询SQL在mysql服务器里面的执行细节和生命周期情况

4、SQL数据库服务器的参数调优