Mysql高级

最新推荐文章于 2023-02-12 21:56:26 发布

qq_41783100

最新推荐文章于 2023-02-12 21:56:26 发布

阅读量92

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_41783100/article/details/115976552

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL中可以用union实现全外连接

一、Mysql逻辑架构

在这里插入图片描述

二、存储引擎

查看命令：show variables like '%storage_engine%'
在这里插入图片描述

三、索引优化分析

定义：索引是帮助Mysql高效获取数据的数据结构。
理解：排好序的快速查找数据结构。
性能分析：Explain
①能干吗？
②执行计划包含的信息
id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，理解：表的读取顺序
select_type：查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
table：
type：显示查询使用了何种类型，从最好到最差依次是
system>const>eq_ref>ref>range>index>All
possoble_keys：显示可能应用在这张表的索引，一个或多个，但不一定被查询实际使用
key：实际使用的索引
key_len：表中索引使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引哪一列被使用了，如果可能的话，是一个常数。
rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
extra：不适合在其他列中显示但十分重要的额外信息：Using filesort，Using temporary，Using index，Using where，Using join buffer，impossibe where，…

Join语句优化结论：

①尽可能减少Join语句中的NestedLoop的循环总次数：“永远用小的结果集驱动大的结果集”
②优先优化NestedLoop的内层循环
③保证Join语句中被驱动表上Join条件字段已经被索引
④当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置

索引优化总结：

①最佳左前缀法则（里面包含两点，一是带头大哥不能死，二是中间兄弟不能断）
②不在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效而转向全表扫描。
③存储引擎不能使用索引中范围条件右边的列。
④尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *。
⑤mysql在使用不等于（!=或<>）的时候无法使用索引会导致全表扫描。
⑥is null，is not null也无法使用索引。
⑦like查询时以通配符开头（’%abc’），mysql索引失效会变成全表扫描操作。
解决方案是：使用覆盖索引，即查询列和索引列尽量一致
⑧字符串不加单引号会导致索引失效。
⑨少用or，用它连接时会索引失效。
注意点：
1.在等值查询时，更改索引列顺序，并不会影响explain的执行结果，因为mysql底层会进行优化。
2.在使用order by时，注意索引顺序、常量，以及可能会导致Using filesort的情况。
3.group by在分组时一般是需要排序的，所以不能用到索引时会产生using temporary和using filesort。

四、查询截取分析

步骤
①开启慢查询日志，设置阈值，将超过阈值的慢SQL抓取出来。
②用explain模拟优化器执行SQL查询语句，从这里可以知道MySQL是如何处理语句的，是否存在索引失效的问题，或者用到了哪些索引都可以显示出来。
③用show profile查询SQL在MySQL服务器里面的执行细节和生命周期情况。
④和运维经理或DBA工程师确认，是否可以进行SQL数据库服务器的参数调优。

优化结论：
①永远小表驱动大表（典型是用in和exists的区别，当前面的表的数据集大于后面的数据集时，用in优于exists）
②order by关键字优化：

1、order by子句，尽量使用index方式排序，避免使用filesort方式排序。
2、尽可能在索引列上完成排序操作，遵从最佳左前缀法则。
注意：如果不在索引列上排序，filesort有两种排序算法：MySQL就要启动双路排序和单路排序。
3、优化策略：增大sort_buffer_size参数的设置，增大max_length_for_sort_data参数的设置。
总结：
MySQL支持两方式的排序，FileSort和Index，Index效率较高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
Order By满足两种情况，会使用Index方式排序。一是Order By语句使用索引最佳左前列。二是使用Where子句和Order By子句条件列组合组合满足索引最佳左前列（这里可以是where子句使用索引的最左前缀定义为常量）。

③group by关键字优化：

1、group by实质是先排序后进行分组，遵照索引建的最佳左前缀。
2、当无法使用索引列，增大max_length_for_sort_data参数的设置和sort_buffer_size参数的设置。
3、where高于having，能写在where限定的条件就不要去having限定了。

五、慢查询日志分析

在生产环境中，不需要手工分析日志，查找、分析SQL，MySQL提供了日志分析工具mysqldumpslow。

六、Show Profile

是什么？
是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。
分析步骤：
①查看当前MySQL版本是否支持，若支持，开启该功能，默认是关闭状态。
②运行SQL，查看结果，show profiles。
③诊断SQL，show profile cpu,block io for query 问题SQL的Id
注意点：上面诊断分析后，其中有如下四个指标，均是导致慢SQL的原因。

1.converting HEAP to MyISAM 查询结果太大，内存都不够用了，往磁盘上搬了。
2.creating tmp table 创建了临时表，先拷贝数据到临时表，用完再删除。
3.copying to tmp table on disk 把内存中临时表复制到磁盘上
4.locked

七、MySQL锁

①表锁（适用MyISAM引擎）
session1在给某张表加读锁后，session1能读当前表，不能修改当前表，不能读其他未锁定的表；session2能读当前表，能读其他表，会阻塞修改当前表。
session1在给某张表加写锁后，session1能操作当前表，不能读其他表，session2不能读当前表。
总结分析：
①查询哪些表被加锁：show open tables;
②分析表的锁定次数：show status like “table%”
其中table_locks_immediate表示产生表级锁定的次数，每立即获取锁值加1；
table_locks_waited表示出现表级锁定争用而发生等待的次数，每等待一次锁值加1，此值高则说明存在着较严重的表级锁争用情况。

②行锁（适用InnoDB引擎）
说明：偏向InnoDB引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
设置不自动提交之后，当前会话在更新某一行数据没有提交之前，其他会话对于当前行的更新会被阻塞，对其他行的更新没有影响，只有当前会话提交之后，其他会话才会解除阻塞，然后更新。
分析： show status like ‘innodb_row_lock%’
其中比较重要的是如下三个指标：
innodb_row_lock_time_avg：等待平均时长
innodb_row_lock_waits：等待总次数
innodb_row_lock_time：等待总时长

③索引失效行锁变表锁
update时字符串不加单引号，也能修改成功，但是这会导致加在字符串所在字段上的索引失效，因此行锁会变成表锁，其他会话在修改当前行时会被阻塞。

④间隙锁
用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“”“间隙”。
**危害：**在Query过程中，通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在，导致在锁定的时候无法插入锁定键值范围内的任何数据。这在某些场景下可能会对性能造成很大的危害。

优化建议：
①尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
②合理设计索引，尽量减少锁的范围
③尽可能较少检索条件，避免间隙锁
④尽量控制事务大小，减少锁定资源量和时间长度
⑤尽可能低级别事务隔离