绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
简单总结mysql性能优化的常用技术
2019-05-03 14:54:57

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

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) * 100% 

key_buffer_write_hits = (1-key_writes / key_write_requests) * 100% 

 

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) * 100% 

 

5、Query Cache命中率 

mysql> show status like 'Qcache%'; 

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

 

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 ) * 100% 

 

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


xkyang :
2019-05-05 09:19:50
0
分享好友

分享这个小栈给你的朋友们,一起进步吧。

MySQL干货资料
创建时间:2020-05-06 14:18:32
每天都有干货输出哦
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

技术专家

查看更多
  • 飘絮絮絮丶
    专家
戳我,来吐槽~