vlambda博客
学习文章列表

一、查询截取分析

有哪些部分：

1.项目上线，进行观察，至少跑1天，看看生产的慢SQL情况。

2.开启慢查询日志，设置阈值，比如超过5s就是慢SQL，将其抓取出来。

3.explain+慢SQL分析。

4.show profile

5.最后时，将SQL数据库服务器的参数调优（一般由DBA进行操作）

总结====

1.慢查询的开启与捕获

2.explain+慢SQL分析

3.show profile查询SQL再Mysql服务器里面的执行细节和生命周期情况。

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

1、优化原则：

小表驱动大表，即小的数据集驱动大的数据集

###########原理（RBO）###############

这里我们用for循环进行理解

select * from A where id in(select id from B)

等价于：

for select id from B

for select * from A where A.id = B.id

当B表的数据集必须小于A表的数据集时，用in 优于exists

select * from A where exists(select 1 from B where B.id = A.id)

等价于：

for select * from A

for select * from B where B.id = A.id

当A表的数据集系小于B表的数据集时，用exists 优于in

注意：A表与B表的ID字段应建立索引。

#EXISTS

select * from table where exists(subquery)

语法理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留

2、order by关键字优化

（1）Order by 子句，尽量使用Index方式排序，避免使用FileSort方式排序（重点在于会不会产生FileSort）

#MySQL支持二中方式排序，FileSort和Index，Index效率搞，它指MySQL扫描索引本身完成排序，FileSort方式效率较低

（2）Order by满足两情况，会使用Index方式排序

1：Order by语句使用索引最左前列

2：使用Where子句与Order BY子句条件列组合满足索引最左前列

（3）如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序。

1：双路排序：

概念：Mysql4.1之前时是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据

读取行指针和orderby列，对他们进行排序，然后扫描已经排序号的列表，按照列表中的值重新从列表中的值重新从列表中读取对应的数据输出。

#就是从磁盘中区排序字段，在buffer进行排序，再从磁盘取其他字段

#进行磁盘I/O是十分消耗时间的，再mysql4.1之后，就是单路排序。

2：单路排序

概念：从磁盘读取查询需要的所有列，按照Order by列buffer对它们进行排序，然后扫描排序后的列表金进行输出，它的效率更快一些，避免了第二次读取数据。而且把随机IO变成了顺序IO，这里会使用更多的空间，因为它把每一行都保存在内存当中了

结论及引申出的问题----由于单路是后出的，总体而言好过双路—单路有问题!

3:优化策略：

1：增大sort_buffer_size参数的设置

2：增大max_length_for_sort_data参数的设置

3：

小结：

为排序使用索引：

1.mysql两种排序方式：文件排序或扫描有序索引排序

2.mysql能为排序与查询使用相同的索引。

4:group by 关键字优化

1)group by实质是先排序后分组，遵照索引建的最佳左前缀

2)当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buff_size参数的设置

3)where高于having，能写在where限定的条件就不要去having限定了。

3、慢查询日志

概念：

1：MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中

2：具体运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10s以上的语句

3：由他来查看哪些 SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5s，我们就算慢SQL，希望能手机超过5s的sql，结合之前的explain进行全面分析。

如何进行使用：

在默认情况下，MySQL数据库是没有开启慢查询日志的，这里需要我们手动的来开启，进行设置。当然，只是为了调优需要进行开启，一般是不建议开启这个参数的，因为慢查询，需要进行记录，这里可能会对Mysql的性能会有一定的影响，慢查询日志支持将日志记录写入文件。

#查看是否开启以及如何开启

默认-SHOW VARIABLES LIKE ‘%slow_query_log%’;

开启-set global show_query_log=1

#注意：使用set global slow_query_log=1开启了慢查询只对当前数据库生效

如果MySQL重启后则会失效

如果要永久生效，这里我们就得修改配置文件my.cnf（这是linux中的文件名，如果是window服务器，需要去修改my.ini文件）

修改my.cnf文件，[mysqld]下增加或修改参数

slow_query_log和slow_query_log_file，然后重启mysql服务器，也即将如下两行配置my.cnf文件

slow_query_log = 1

slow_query_log_file=/var/lib/mysql/…文件路径

4.哪些记录会被写入到慢查询日志里面：

5.设置慢的阀值时间

这里使用命令进行修改阀值为3s,当查询时间大于3秒的，就是慢sql

这里我们再使用show variables like ‘long_query_time%’,返现long_query_time值并没有发生修改

这里需要重新连接或新开一个会话才能看到修改值

show variables like ‘long_query_time%’;

show global variables like ‘long_query_time’;

这里我可以使用select sleep（4）；来进行测试—让查询语句睡4s后

进行查询，有多少条不符合

如果使用配置：

日志分析工具 mysqldumpslow

帮助信息：

以下为分析常用命令：

6.批量数据脚本P50

1、创建函数，这里如果报错：This function has none of DETERMINISTIC…

#由于开启过慢查询日志，这里我们开启bin-log，这里就必须为我们的function指定一个参数

show variables like ‘log_bin_trust_function_creators’;

set global log_bin_trust_function_creators=1;

#这样添加参数以后，如果mysqld重启，上述参数又会消失，永久方法：

windows下my.ini[mysqld]加上log_bin_trust_function_creators=1

linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1

这里调用存储过程和函数，进行批量数据的压力

7.Show Profile进行sql优化分析

我们在使用show profiles 要注意以下四个问题：

8.全局查询日志（永远不要在生产环境开启这个功能）

1.配置启用

2.编码启用

二、MySQL锁机制

概念：锁是计算机协调多个进程或线程并发访问某一资源的机制

在数据库中，出传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁的分类：

1.从对数据操作的类型（读\写）分

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

2.从对数据操作的粒度分：表锁，行锁。

3.三锁

（1）表锁（偏读）：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

1.1）案例分析—加读锁

1.2）加写锁

写表的时候，这个时候，我们对表加行加锁，这个时候，如果我们要对表进行读取，那么就会被阻塞，这个时候，是拿不到相应表的数据。

这里只有对表进行释放锁，才可以读取。

总结：MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁

MySQL的表级锁又两种模式：

表共享读锁（Table Read Lock）

表独占写锁（Table Write Lock）

看看哪些表被加锁了

mysql>show open tables;

【如何分析表锁定】

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定

SQL:show status like ‘table%’;

此外，Myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其它线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

（2）行锁（偏写）：

特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高

InnoDB与MyISAM的最大不同又两点：一是支持事务（TRANSACTION）;

二是采用了行级锁

行锁支持事务：事务（Transaction）及其ACID属性

并发事务处理带来的问题：更新丢失（Lost Update）、脏读（Dirty Reads）、不可重复读（Non-Repeatable Reads）、幻读（Phantom Reads）

查看数据库的默认隔离级别：show variables like’tx_isolation’;

Mysql默认的数据隔离级别为：可重复读（Repeatable read）

Mysql中关闭自动提交事务机制：set autocommit=0；

开启自动提交事务机制：

start transaction

这样我们可以进行测试行锁。

#这里需要注意一个问题：无索引升级为表锁

这里主要会遇到的一个问题是：由于在进行对表进行改变的时候，出现锁升级，由行锁变成表锁，导致一个语句还未进行提交，变成表锁，对整个表锁主，而使得出现问题。主要体现在varchar类型，如果没有对其中的查询数值加上单引号，发生了类型转化问题，使得索引失效，锁升级。

3）间隙锁的危害

考虑：如何锁定一行

     

总结：

用show profile进行分析

这里的建议：

1、尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

2、合理设计索引，尽量缩小锁的范围

3、尽可能较少检索条件，避免间隙锁

4、尽量控制事务大小，减少锁定资源量和时间长度

5、尽可能低级别事务隔离

页锁：开销和加锁时间界于表锁和行锁之间：会出现死锁：锁定粒度界于表锁和行锁之间，并发度一般

---------------------------------------------------------------

学习来源：

https://www.bilibili.com/video/BV1KW411u7vy?p=1