==================================================================

001-性能差分析思路

一、查OS

1、cpu

2、内存

3、iotop找线程及对应processid

二、查my.cnf参数

比如：

1、连接数

2、wait_timeout

3、共享内存/专用内存

innodb_buffer_pool_size

key_buffer_size

...

4、io

5、线程池参数

6、redo

7、undo

8、binlog格式

9、innodb_log_file_size

10、table_cache

三、查DB，SQL优化(慢sql或吃cpu)

1、show processlist线程多少/状态/时长/反复出现、

2、show innodb status锁追踪、

3、Slow Log降cpu(explain/profile)：99%大SQL出问题在索引、排序、子查询、少部份因为表设计不合理(没有自增主键，或是频繁修改)。

4、大表瘦身：横竖两刀，即字段该拆的拆+过期数据定期归档

四、堆硬件，争取架构优化的时间

1、cpu

2、mem

3、hdd

4、nic

五、加节点

1、主从

2、集群

3、分布式

===================================================================

002-找出或分析慢sql的几种方法

一、开启慢查询日志，持续优化sql

1、slow_query_log    ：是否开启慢查询日志，1表示开启，0表示关闭。

2、slow-query-log-file：5.6及以上版本慢查询日志存储路径。默认给一个缺省的文件host_name-slow.log

4、long_query_time ：慢查询阈值（默认10S），当查询时间多于设定的阈值时，记录日志。

5、log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。

6、log_output：日志存储方式。'TABLE'指记录到mysql.slow_log表中。

7、log-slow-slave-statements：  # 记录从库线程执行的慢查询语句

8、log-slow-admin-statements：  #记录执行缓慢的OPTIMIZE、ANALYZE、ALTER和其他管理命令语句

二、processlist

1、select * from information_schema.processlist where command != 'Sleep' and time > 60 order by time desc;

2、SHOWPROCESSLIST

三、pt-query-digest分析tcpdumplog，

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt 

pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

四、pt-query-digest分析binlog

mysqlbinlog mysql-bin. 000093 > mysql-bin000093.sql 

pt-query-digest  --type=binlog  mysql-bin000093.sql > slow_report10.log

五、pt-query-digest分析General log

pt-query-digest  --type=genlog  localhost.log > slow_report11.log

六、pt-query-digest分析slowlog，

1.  分析近 12 小时内的查询：

pt-query-digest  --since=12h  slow.log > slow_report2.log

2. 分析指定时间范围内的查询：

pt-query-digest slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00' > > slow_report3.log

3. 分析指含有 select 语句的慢查询

pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log

4. 针对某个用户的慢查询

pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log

5. 查询所有所有的全表扫描或 full join 的慢查询

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log

6. 把查询保存到 query_history 表

pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_history--create-review-table  slow.log_0001 

七、mysqldumpslow

-s 这个是排序参数，可选的有： 

al: 平均锁定时间 

ar: 平均返回记录数 

at: 平均查询时间 

c: 计数 

l: 锁定时间 

r: 返回记录 

t: 查询时间 

-t n 显示头n条记录。 

mysqldumpslow -s c -t 20 host-slow.log   访问次数多的20个sql语句

mysqldumpslow -s r -t 20 host-slow.log   返回记录集多的20个sql。 

mysqldumpslow -t 10 -s t -g “left join” host-slow.log 按照时间返回前10条里面含有左连接的sql语句。 

mysqldumpslow -t 10  /data/mysql/mysql-slow.log   #显示出慢查询日志中慢的10条sql

八、补充：kill掉执行时间超过60s的query

pt-kill --busy-time 60 --kill

==================================================================

003-执行计划

一、id表示查询中执行select子句或操作表的顺序 三种情况： 

1、id相同：执行顺序由上至下 

2、id不同：id值越大优先级越高，越先被执行（如果是子查询，id的序号会递增）

3、混合：

(1)id如果相同，可以认为是一组，从上往下顺序执行；

(2)在所有组中，id值越大，优先级越高，越先执行 

二、select_type用于区分普通查询、联合查询、子查询

1、SIMPLE：简单的select查询，查询中不包含子查询或者union 

2、PRIMARY：查询中包含任何复杂的子部分，外层查询则被标记为primary 

3、SUBQUERY：在select 或 where列表中包含了子查询 

4、DERIVED：！！！在from列表中包含的子查询被标记为derived[dɪ'raɪvd]（衍生）！！！ 

5、UNION：若第二个select出现在union之后，则被标记为union；

6、UNION RESULT：从union表获取结果的select 

三、type

访问类型，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，好的sql查询至少达到range级别，好能达到ref

要看到尽量用 index(type 为 const、 ref 等， key 列有值)，避免使用全表扫描(type 显式为 ALL)。

比如说有 where 条件且选择性不错的列，需要建立索引。

被驱动表的连接列，也需要建立索引。

被驱动表的连接列也可能会跟 where 条件列一起建立联合索引。

当有排序或者 group by 的需求时，也可以考虑建立索引来达到直接排序和汇总的需求。

1、system：表只有一行记录（等于系统表），这是const类型的特例

2、const：表示通过索引一次就找到了。如果将主键置于where列表中，mysql就能将该查询转换为一个const 

3、eq_ref：性索引扫描。常见于主键 或 索引扫描。 

4、ref：非性索引扫描，返回匹配某个单独值的所有行。

5、range：只检索给定范围的行，使用一个索引来选择行。一般就是在where语句中出现了bettween、<、>、in等的查询。

6、index：Full Index Scan，index与ALL区别为index类型只遍历索引树。（index是从索引中读取，而ALL是从硬盘读取） 

7、ALL：Full Table Scan，遍历全表以找到匹配的行 

四、key

实际使用的索引，如果为NULL，则没有使用索引。 

查询中如果使用了覆盖索引，则该索引仅出现在key列表中

五、Extra额外信息

1、Using temporary：使用临时表保存中间结果

2、Using index： 使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高

===================================================================

004-profile

SQL语句分析三板斧explain+show processlist+show profile for query query_id

1、MySQL可以使用profile分析SQL语句的性能消耗情况。例如，查询到SQL会执行多少时间，并看出CPU、内存使用量，执行过程中系统锁及表锁的花费时间等信息。

2、profile是用户级变量,每次都得重新启用

3、以下是有关profile的一些常用命令：

set profiling = 1;             #基于会话级别开启，关闭则用set profiling = off

show profile for query 1;         #1是query_id

show profile cpu for query 1;      #查看CPU的消耗情况

show profile memory for query 1;    #查看内存消耗情况

show profile block io,cpu for query 1;#查看I/O及CPU的消耗情况

==============================================================================

005-性能巡检

转自https://blog.csdn.net/hu2010shuai/article/details/55259516

1、QPS(每秒Query量) 

QPS = Questions(or Queries) / seconds 

mysql > show  global  status like 'Question%'; 

2、TPS(每秒事务量) 

TPS = (Com_commit + Com_rollback) / seconds 

mysql > show global status like 'Com_commit'; 

mysql > show global status like 'Com_rollback'; 

3、key Buffer 命中率 

mysql>show  global   status  like   'key%'; 

key_buffer_read_hits = (1-key_reads / key_read_requests) *  

key_buffer_write_hits = (1-key_writes / key_write_requests) *  

4、InnoDB Buffer命中率 

mysql> show status like 'innodb_buffer_pool_read%'; 

innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) *  

5、Query Cache命中率 

mysql> show status like 'Qcache%'; 

Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * ; 

6、Table Cache状态量 

mysql> show global  status like 'open%'; 

比较 open_tables  与 opend_tables 值 

7、Thread Cache 命中率 

mysql> show global status like 'Thread%'; 

mysql> show global status like 'Connections'; 

Thread_cache_hits = (1 - Threads_created / connections ) *  

8、锁定状态 

mysql> show global  status like '%lock%'; 

Table_locks_waited/Table_locks_immediate=0.3%  如果这个比值比较大的话，说明表锁造成的阻塞比较严重 

Innodb_row_lock_waits innodb行锁，太大可能是间隙锁造成的 

9、复制延时量 

mysql > show slave status 

查看延时时间 

10、 Tmp Table 状况(临时表状况) 

mysql > show status like 'Create_tmp%'; 

Created_tmp_disk_tables/Created_tmp_tables比值好不要超过10%，如果Created_tmp_tables值比较大， 

可能是排序句子过多或者是连接句子不够优化 

11、 Binlog Cache 使用状况 

mysql > show status like 'Binlog_cache%'; 

如果Binlog_cache_disk_use值不为0 ，可能需要调大 binlog_cache_size大小 

12、Innodb_log_waits 量 

mysql > show status like 'innodb_log_waits'; 

Innodb_log_waits值不等于0的话，表明 innodb log  buffer 因为空间不足而等待 

13、其它

https://blog.csdn.net/sj349781478/article/details/80013476

--查看MySQL本次启动后的运行时间(单位：秒)

show status like 'uptime';

--查看select语句的执行数

show [global] status like 'com_select';

--查看insert语句的执行数

show [global] status like 'com_insert';

--查看update语句的执行数

show [global] status like 'com_update';

--查看delete语句的执行数

show [global] status like 'com_delete';

--查看试图连接到MySQL(不管是否连接成功)的连接数

show status like 'connections';

--查看线程缓存内的线程的数量。

show status like 'threads_cached';

--查看当前打开的连接的数量。

show status like 'threads_connected';

--查看当前打开的连接的数量。

show status like 'threads_connected';

--查看创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。

show status like 'threads_created';

--查看激活的(非睡眠状态)线程数。

show status like 'threads_running';

--查看立即获得的表的锁的次数。

show status like 'table_locks_immediate';

--查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。

show status like 'table_locks_waited';

--查看创建时间超过slow_launch_time秒的线程数。

show status like 'slow_launch_threads';

--查看查询时间超过long_query_time秒的查询的个数。

show status like 'slow_queries';

==============================================================================

006-补充

一、索引类别

1、聚集索引：IOT表～索引记录被存储在树的叶子页上~数据以索引形式存放，因此找到索引，就等于找到了行数据。

2、非聚集索引：堆表～索引和表数据是分离的,(Myisam是非聚簇索引叶节点存的地址)

3、左前缀-where子句中使用频繁的一列放在左边

4、覆盖索引(Covering Index)，要打印的列有索引，不用回表。

5、自适应哈希索引：InnoDB监控对表上各索引页的查询。观察到建立哈希索引可带来速度提升，则建立哈希索引，称自适应哈希索引(Adaptive Hash Index, AHI)。

二、SQL开发规范

1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

3、很多时候用 exists 代替 in 是一个好的选择

4、用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

三、mysql的各种log

1、Error log       # -log-error：记录在mysqld服务启动、运行或停止时遇到的问题

2、General query log  # -log：记录从客户端获取到的连接和语句

3、Binary log       #  -log-bin：记录全部更改数据的语句。还用于复制。

4、Slow query log    #-log-slow-queries：记录全部运行时间超过long_query_time秒的全部查询或不使用索引的查询。

5、innodb redo log    #innodb的重做日志

6、Relay log        # 在复制结构中，从库从主库获取到的数据修改

7、DDL log(metadata log )#DDL日志或元数据日志记录由数据定义语句（如DROP TABLE和ALTER TABLE）生成的元数据。

动力小刚于2019年4月  个人邮箱：zcs0237#163.com