MySQL 基础 (2) -- MySQL 优化

文章目录

- 1. EXPLAIN
- 2. 索引优化
- 3. ORDER BY 优化
- 4. 常用的优化手段
- - 1. 分表技术
  - 2. 读写分离
  - 3. 主从复制
- 5. 慢查询日志
- - 1. 开启慢查询日志
  - 2. 慢查询的相关参数
  - 3. 分析慢查询日志

1. EXPLAIN

id：id 相同时，table 执行顺序从上往下；id 不同时，id 大的优先级越高；id 既有相同也有不同，id 大的先执行，相同的按顺序从上往下执行
table：sql 操作属于哪张表
select_type：
1. SIMPLE：指简单的 select 语句，没有包含复杂的子查询或 union 查询
2. PRIMARY：指 select 语句中包含了复杂的子语句，但是最外层使用的是 primary
3. SUBQUERY：指 select 或 where 包含了子查询
4. DERIVED：指 from 包含了子查询
5. UNION：第二个 select 出现在 union 之后，若 union 包含在 from 子句的子查询中，外层 select 将被标记为 DERIVED
6. UNION RESULT：从 union 表获取结果的 select
partitions：table 所在的分区，NULL 表示表未被分区
type：指查询所使用的访问类型，主要有八种：
1. system：表中只有一行记录（等于系统表），是 const 的特殊类型，MySQL 5.7 开始好像不存在了，单表单行查询
2. const：通过一次索引就找到了结果，常出现于primary key或unique索引。因为只匹配一行数据，所以查询非常快。如将主键置于where条件中，MySQL就能将查询转换为一个常量，单表单行查询
3. eq_ref：使用唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描，多表查询
```
select * from stu,class where stu.id = class.sid;
-- stu.id 是主键，所以 table 为 stu 的查询操作 type 为 eq_ref
           
```
  MySQL 基础 (2) -- MySQL 优化
4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某值（某条件）的多行值，属于查找和扫描的混合体
```
create index idx on stu(s_name); --创建普通索引
select * from stu where s_name = 'hello';
           
```
5. range：只检索给定范围的行，使用一个索引来检索行，一般出现在where语句的条件中，如使用between、>、<、in、like 等查询，这种索引的范围扫描比全索引扫描要好，因为索引的开始点和结束点都固定，范围相对较小
6. index：全索引扫描，index和ALL的区别：index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描，但是index是从索引中读取，ALL是从磁盘中读取
7. ALL：全表扫描，一般来说，需保证查询至少达到 range 级别，最好能达到 ref
possible_keys：显示可能应用在表中的索引，可能一个或多个
key：实际中使用的索引，如为NULL，则表示未使用索引
key_len：表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度，在不损失精确性的情况下，长度越短越好
ref：显示关联的字段。如果使用常数等值查询，则显示const，如果是连接查询，则会显示关联的字段
rows：实际扫描的行数，越小越好
filtered：百分比值，表示存储引擎返回的数据经过滤后，剩下多少满足查询条件记录数量的比例
Extra：显示十分重要的额外信息。其取值有以下几个：
1. Using filesort：没有使用 table 显示的表的索引来作为排序的列（即 order by 跟的字段不是索引列）， mysql 中无法利用索引完成的排序操作称为“文件排序” ，需要优化（可以给该列建立索引列）
2. Using temporary：使用了临时表保存中间结果，常见于排序order by和分组查询group by ，最严重，需要优化（给该列建立索引列）
3. Using index：联合索引字段覆盖了 select 的字段，这样就不用去访问额外的数据行，效率不错
  1. 如果同时出现了 Using where，表明索引被用来执行索引键值的查找
  2. 如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作

2. 索引优化

左外连接：右表条件字段建索引，extra 可以达到 using index
右外连接：反过来
最佳左前缀：比如创建三个字段的联合索引，根据最佳左前缀，第一个字段可以单独用，效果不错，也可以第一个字段和与它相邻的第二个字段一起用，效果不错，如果是第一个字段与不是和它相邻的第三个字段、第四个字段等等一起用，只会使用索引的部分也就是第一个字段，其他字段不会用到；尽量使用覆盖索引（查询列和索引列尽量一致，通俗说就是对A、B列创建了索引，然后查询中也使用A、B列），减少 select * 的使用
索引不能参与表达式、函数、手动或者自动的类型转换，否则索引会失效从而转向全表扫描
如果进行范围查找，会导致范围右边的索引列失效，参与范围比较的索引列仍然有效
使用 != 、<> 、is NULL 、is NOT NULL 会使索引失效

使用模糊查询 % 开头会使索引失效，使用覆盖索引时，%放在开头索引也不会失效，使用 id 时也一样时覆盖索引了

create index idx on stu(name,age,gender);
select id from stu where name like '%h%';
select age from stu where name like '%h%';
select name,age from stu where name like '%h%';
select name,age,gender from stu where name like '%h%';
select id,name,age,gender from stu where name like '%h%';

使用 or 会使索引失效，少用 or
索引秒实体面试题分析：创建复合索引的顺序为c1,c2,c3,c4
1. case1：在执行常量等值查询时，改变索引列的顺序并不会更改explain的执行结果，因为mysql底层优化器会进行优化，但是推荐按照索引顺序列编写sql语句
  
  MySQL 基础 (2) -- MySQL 优化
2. case2：当 c3 出现范围的时候，c4 索引失效
  
  MySQL 基础 (2) -- MySQL 优化
3. case 2.1：对此sql语句mysql底层优化器会进行优化：范围右边索引列失效（c4右边已经没有索引列了），注意索引的顺序（c1,c2,c3,c4），所以c4右边不会出现失效的索引列，因此4个索引全部用上
  
  MySQL 基础 (2) -- MySQL 优化
4. case 2.2：在最佳左前缀法则中，如果最左前列（带头大哥）的索引失效，则后面的索引都失效；在c1处使用范围，这里违背了最佳左前缀法则，带头大哥已死，因为c1主要用于范围，而不是查询，这时会进行全表扫描，所有索引失效
  
  MySQL 基础 (2) -- MySQL 优化
  解决方法：使用覆盖索引
5. case3：利用最佳左前缀法则：中间兄弟不能断，因此用到了c1和c2索引（查找），没有用到 c4， c3索引列用在排序过程中
  
  MySQL 基础 (2) -- MySQL 优化
  
  MySQL 基础 (2) -- MySQL 优化
6. case3.2：查询使用了c1和c2索引，由于用了c4进行排序，跳过了c3，c4 索引失效，也就是说排序的字段不是索引列，则出现Using filesort
  
  MySQL 基础 (2) -- MySQL 优化
7. case4：查找只用到索引c1，c2和c3用于排序，无Using filesort
  
  MySQL 基础 (2) -- MySQL 优化
8. case4.1：出现了Using filesort，因为索引的创建顺序为c1,c2,c3,c4，但是排序的时候c2和c3颠倒位置了（排序的顺序要一致，当然如果c2 的索引列是常量，则mysql底层会优化，那此时就不会有 filesort 了）
  
  MySQL 基础 (2) -- MySQL 优化
  
  MySQL 基础 (2) -- MySQL 优化
9. case4.2：在查询时增加了c5，但是explain的执行结果一样，因为c5并未创建索引
  
  MySQL 基础 (2) -- MySQL 优化
10. case5：只用到c1上的索引，因为c4中间间断了，根据最佳左前缀法则，只用到了一个索引
  
  MySQL 基础 (2) -- MySQL 优化
11. case5.1： c3和c2与索引创建顺序相反
  
  MySQL 基础 (2) -- MySQL 优化

3. ORDER BY 优化

创建联合索引 c1,c2,c3,c4
case1：

MySQL 基础 (2) -- MySQL 优化
case1.1：排序时要按照索引顺序，虽然使用了覆盖查询解决了case1，但是因为c1用了范围，所以实际上查询并没有用索引c1，所以在排序时，如果要用到 c2就必须先有c1被使用，否则会出现 Using filesort（使用顺序要与索引列顺序一致）

MySQL 基础 (2) -- MySQL 优化

MySQL 基础 (2) -- MySQL 优化

MySQL 基础 (2) -- MySQL 优化
where 字段后面的第一个索引列如果参与范围查找，则会失效

MySQL 基础 (2) -- MySQL 优化
case 2.2：虽然排序的字段列与索引顺序一样，且order by默认升序，这里c2 desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort

MySQL 基础 (2) -- MySQL 优化
MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低
order by满足两种情况会使用Using index ：
1. order by语句使用索引最左前列
2. 使用where子句与order by子句条件列组合满足索引最左前列
filesort有两种排序算法：双路排序和单路排序：
1. 双路排序：在MySQL4.1之前使用双路排序，就是两次磁盘扫描，得到最终数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出
2. 如果使用双路排序，取一批数据要对磁盘进行两次扫描，众所周知，I/O操作是很耗时的，因此在MySQL4.1以后，出现了改进的算法：单路排序：从磁盘中查询所需的列，按照order by列在buffer中对它们进行排序，然后扫描排序后的列表进行输出。它的效率更高一些，避免了第二次读取数据，并且把随机I/O变成了顺序I/O，但是会使用更多的空间，因为它把每一行都保存在内存中
3. 单路排序出现的问题：当读取数据超过sort_buffer的容量时，就会导致多次读取数据，并创建临时表，最后多路合并，产生多次I/O，反而增加其I/O运算；解决：
  1. 增加sort_buffer_size参数的设置
  2. .增大max_length_for_sort_data参数的设置
提升order by速度的方式：
1. 在使用order by时，不要用select *，只查询所需的字段
  
  因为当查询字段过多时，会导致sort_buffer不够，从而使用多路排序或进行多次I/O操作
2. 尝试提高sort_buffer_size
3. 尝试提高max_length_for_sort_data
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。当无法使用索引列的时候，也要对sort_buffer_size和max_length_for_sort_data参数进行调整。注意where高于having，能写在where中的限定条件就不要去having限定了
在使用 group by 分组查询时，默认分组后，还会排序，可能会降低速度，在group by 后面增加 order by null 就可以防止排序。

有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。

select * from dept, emp where dept.deptno=emp.deptno; 
// 替换成
select * from dept left join emp on dept.deptno=emp.deptno;

4. 常用的优化手段

查询语句无论是使用哪种判断条件等于、小于、大于， WHERE 左侧的条件查询字段不要使用函数或者表达式
使用 EXPLAIN 命令优化你的 SELECT 查询，对于复杂、效率低的 sql 语句，我们通常是使用 explain sql 来分析这条 sql 语句，这样方便我们分析，进行优化。
当你的 SELECT 查询语句只需要使用一条记录时，要使用 LIMIT 1
不要直接使用 SELECT *，而应该使用具体需要查询的表字段，因为使用 EXPLAIN 进行分析时，SELECT * 使用的是全表扫描，也就是 type = all。
为每一张表设置一个 ID 属性
避免在 WHERE 字句中对字段进行 NULL 判断
避免在 WHERE 中使用 != 或 <> 操作符
使用 BETWEEN AND 替代 IN
为搜索字段创建索引
选择正确的存储引擎，InnoDB 、MyISAM 、MEMORY 等
使用 LIKE %abc% 不会走索引，而使用 LIKE abc% 会走索引
对于枚举类型的字段(即有固定罗列值的字段)，建议使用ENUM而不是VARCHAR，如性别、星期、类型、类别等
拆分大的 DELETE 或 INSERT 语句
选择合适的字段类型，选择标准是尽可能小、尽可能定长、尽可能使用整数。
字段设计尽可能使用 NOT NULL
进行水平切割或者垂直分割
1. 水平分割：通过建立结构相同的几张表分别存储数据
2. 垂直分割：将经常一起使用的字段放在一个单独的表中，分割后的表记录之间是一一对应关系。

1. 分表技术

为什么要分表？

如果一个表的每条记录的内容很大，那么就需要更多的IO操作，如果字段值比较大，而使用频率相对比较低，可以将大字段移到另一张表中，当查询不查大字段的时候，这样就减少了I/O操作
如果表的数据量非常非常大，那么查询就变的比较慢；也就是表的数据量影响这查询的性能。
表中的数据本来就有独立性，例如分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，而另外一些数据不常用。
分表技术有(水平分割和垂直分割)

垂直分割

垂直分割是指数据表列的拆分，把一张列比较多的表拆分为多张表。垂直分割一般用于拆分大字段和访问频率低的字段，分离冷热数据。

垂直分割比较常见：例如博客系统中的文章表，比如文章

tbl_articles(id, titile, summary, content, user_id, create_time)

，因为文章中的内容 content 会比较长，放在 tbl_articles 中会严重影响表的查询速度，所以将内容放到

tbl_articles_detail(article_id, content)

，像文章列表只需要查询 tbl_articles 中的字段即可。

垂直拆分的优点：可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂。

水平分割

水平拆分是指数据表行数据的拆分，表的行数超过500万行或者单表容量超过10GB时，查询就会变慢，这时可以把一张的表的数据拆成多张表来存放。水平分表尽可能使每张表的数据量相当，比较均匀。

水平拆分会给应用增加复杂度，它通常在查询是需要多个表名，查询所有数据需要union操作。在许多数据库应用中，这种复杂性会超过它带来的优点。

因为只要索引关键字不大，则在索引用于查询时，表中增加2-3倍数据量，查询时也就增加读一个索引层的磁盘次数，所以水平拆分要考虑数据量的增长速度，根据实际情况决定是否需要对表进行水平拆分。

水平分割最重要的是找到分割的标准，不同的表应根据业务找出不同的标准

用户表可以根据用户的手机号段进行分割如user183、user150、user153、user189等，每个号段就是一张表

用户表也可以根据用户的id进行分割，加入分3张表user0,user1,user2，如果用户的id%3=0就查询user0表，

如果用户的id%3=1就查询user1表

对于订单表可以按照订单的时间进行分表

2. 读写分离

实现MySQL读写分离的前提是我们已经将MySQL主从复制配置完毕，读写分离实现方式：

（1）配置多数据源。

（2）使用mysql的proxy中间件代理工具。

3. 主从复制

MySQL的主从复制和读写分离两者有着紧密的联系，首先要部署主从复制，只有主从复制完成了才能在此基础上进行数据的读写分离。

MySQL 基础 (2) -- MySQL 优化

读写分离的原理：读写分离就是只在主服务器上写，只在从服务器上读。基本原理是让主数据库处理事务性查询，而从服务器处理select查询。数据库复制被用来把事务性查询导致的变更同步到从数据库中。

5. 慢查询日志

慢查询日志是MySQL提供的一种日志记录，它记录MySQL中响应时间超过阈值的语句，具体指运行时间超过

long_query_time

值的sql语句，该sql语句会被记录到慢查询日志中。慢查询日志主要与explain进行联合分析。

1. 开启慢查询日志

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要，一般不建议开启该参数，因为开启慢查询日志或多或少会带来一定的性能影响。

首先查看慢查询日志是否开启结果

MySQL 基础 (2) -- MySQL 优化
使用如下命令开启慢查询日志查询是否开启：

MySQL 基础 (2) -- MySQL 优化
注：使用该命令开启慢查询只对当前数据库生效，MySQL重启后就会失效，如果要配置长期有效，请在my.cnf中进行配置。

2. 慢查询的相关参数

查看慢查询的阈值时间

该值由 long_query_time 控制。默认情况下为10秒。结果

MySQL 基础 (2) -- MySQL 优化
注：假如sql语句的运行时间正好等于long_query_time时，sql并不会被记录下来，因此判断慢查询是sql的执行时间大于long_query_time，而不是大于等于。
设置long_query_time的值注：当设置long_query_time值后，查看其值并没有变化。

解决方式：使用如下命名：
SHOW GLOBAL VARIABLES LIKE '%long_query_time%';
，多了个 global

但是，设置了 long_query_time 值后，需要关闭连接，然后重新开一个新的连接，否则设置的时间是没有效果的，也就是说，不重新开的话，long_query_time 值仍然是设置之前的那个值。
查看慢查询sql的数目

先执行了两次select sleep(9)命令，方便测试可以看到效果结果

MySQL 基础 (2) -- MySQL 优化
可以到慢日志文件中去查看详情，根据上面 SHOW VARIABLES LIKE '%slow_query_log%'; 命令，我的慢日志文件的位置：

MySQL 基础 (2) -- MySQL 优化

3. 分析慢查询日志

因为直接分析日志文件是个体力活，因此mysql为我们提供了相关工具mysqldumpslow来对慢查询日志文件进行分析。

具体使用方式可用

mysqldumpslow --help

命令查看具体参数。

如果该命令报错说

mysqldumpslow不是内部或外部命令，也不是可运行的程序

，解决方法

经常使用的参数：

-s 按照哪种方式排序
1. c：访问计数
2. l：锁定时间
3. r：返回记录
4. al：平均锁定时间
5. ar：平均访问记录数
6. at：平均查询时间
-t 是top n的意思，返回多少条数据。
-g 可以跟上正则匹配模式，大小写不敏感。

案例：

-- 得到返回记录最多的10个sql
perl E:\Installer\mysql-8.0.16-winx64\bin\mysqldumpslow.pl -s r -t 10 E:\Installer\mysql-8.0.16-winx64\data\WIN-4N0A7UODU50-slow.log

-- 得到平均访问次数最多的20条sql
perl E:\Installer\mysql-8.0.16-winx64\bin\mysqldumpslow.pl -s ar -t 20 E:\Installer\mysql-8.0.16-winx64\data\WIN-4N0A7UODU50-slow.log

-- 得到平均访问次数最多,并且里面含有ttt字符的20条sql
perl E:\Installer\mysql-8.0.16-winx64\bin\mysqldumpslow.pl -s ar -t 20 -g "ttt" E:\Installer\mysql-8.0.16-winx64\data\WIN-4N0A7UODU50-slow.log

MySQL 基础 (2) -- MySQL 优化

文章目录

1. EXPLAIN

2. 索引优化

3. ORDER BY 优化

4. 常用的优化手段

1. 分表技术

2. 读写分离

3. 主从复制

5. 慢查询日志

1. 开启慢查询日志

2. 慢查询的相关参数

3. 分析慢查询日志

继续阅读

Testlink安装部署之XAMPP

set define off关闭替代变量功能

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述