计算机教程

当前位置:澳门娱乐场网址 > 计算机教程 > 《深入浅出Mysql》笔记之优化

《深入浅出Mysql》笔记之优化

来源:http://www.ablakeforum.com 作者:澳门娱乐场网址 时间:2020-04-24 05:37

上次我们说到mysql的一些sql查询方面的优化,包括查看explain执行计划,分析索引等等。今天我们分享一些 分析mysql表读写、索引等等操作的sql语句。

[toc]

闲话不多说,直接上代码:

澳门娱乐场网址 1

反映表的读写压力

mysql优化

SELECT file_name AS file, count_read, sum_number_of_bytes_read AS total_read, count_write, sum_number_of_bytes_write AS total_written, (sum_number_of_bytes_read   sum_number_of_bytes_write) AS total FROM performance_schema.file_summary_by_instanceORDER BY sum_number_of_bytes_read  sum_number_of_bytes_write DESC;

一 SQL语句优化

反映文件的延迟

1.1 使用 show status 命令了解sql执行频率

mysql> show session status 查询当前连接统计结果  
mysql> show global status 查询自数据库上次启动至今统计结果  

或者在操作系统的终端执行

shell> mysqladmin extended-status 

可以通过like语句来查询一些特定的内容

mysql> show global status like "Com_%";

上次启动以来的每个Com_xxx语句执行的次数

Com_select: 执行select查询的次数  
Com_insert: 执行insert的次数  
Com_update: 执行update的次数  
Com_delete: 执行delete的次数  

以上针对所有存储引擎表操作记录,专门针对某一些存储引擎的如下:

Innodb_rows_read  
Innodb_rows_insert  
Innodb_rows_update  
Innobd_rows_delete  

通过上面这些数据的比较可以判断数据库是写为主,还是查询为主

事务信息 可通过

Com_commit

Com_rollback

来了解提交和回滚情况。如回滚操作很频繁,说明应用编写存在问题。

其他重要参数,可展示数据库基本情况句子:

Connections:  试图连接Mysql数据库的次数  
Uptime:        服务器工作时间  
Slow_queries: 慢查询的次数  
SELECT (file_name) AS file, count_star AS total, CONCAT(ROUND(sum_timer_wait / 3600000000000000, 2), 'h') AS total_latency, count_read, CONCAT(ROUND(sum_timer_read / 1000000000000, 2), 's') AS read_latency, count_write, CONCAT(ROUND(sum_timer_write / 3600000000000000, 2), 'h')AS write_latency FROM performance_schema.file_summary_by_instanceORDER BY sum_timer_wait DESC;

1.2 定位执行效率比较低的sql语句

两种方法:

table 的读写延迟

1)慢查询日志定位

用--log-slow-queries[=filename]选项启动时,mysqld写一个包含所有执行时间超过long_query_time秒得sql语句的日志文件。

SELECT object_schema AS table_schema, object_name AS table_name, count_star AS total, CONCAT(ROUND(sum_timer_wait / 3600000000000000, 2), 'h') as total_latency, CONCAT(ROUND((sum_timer_wait / count_star) / 1000000, 2), 'us') AS avg_latency, CONCAT(ROUND(max_timer_wait / 1000000000, 2), 'ms') AS max_latency FROM performance_schema.objects_summary_global_by_type ORDER BY sum_timer_wait DESC;

2)show processlist

查看MySQL在进行的线程,包括线程状态,是否锁表等,可实时查看sql执行情况,并对一些锁表操作优化。

查看表操作频度

1.3 通过Explain分析低效SQL执行计划

通过上面步骤,查到低效sql语句后,可通过 explaindesc命令获取mysql如何执行select语句信息,如表连接。

explain 
select sum(money) 
from sales a, company b 
where a.company_id = b.id and a.year = 2006  


*************************** 1. row *****************
           id: 1  
  select_type: SIMPLE  
        table: user  
         type: system  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 0  
        Extra: const row not found  
1 row in set (0.00 sec)  

ERROR:  
No query specified  

每一列的含义说明

select_type: 
表示select的类型
(SIMPLE=>简单表,不使用表连接或者子查询,PRIMARY=>主查询,
外层的查询,UNION=>UNION中的第二个或者后面的查询语句,
SUBQUERY=>子查询中的第一个select)

table: 输出结果集的表 

possible_keys: 表示查询时,可能使用的索引

key: 表示实际使用的索引

key_len: 索引字段的长度

rows; 扫描的行的数量

Extra: 执行情况的说明和描述

type: 
表示表的连接类型,性能有好到差的链接类型为:  
   system=>只有一行,也就是常量表;  
   const=>单表中最多有一个匹配行,例如primary key或者unique index;  
   eq_ref=>对于前面的每一行,在此表中只查询一条记录,简单来说,就是多表连接中使用primary key或者unique index;   
   ref=>与eq_ref类似,区别在于不是使用primay key或者unique index而是使用普通的索引;  
   ref_or_null=>与ref类似,区别在于条件中包含对null的查询;  
   index_merge=>索引合并优化;  
   unique_subquery=>in的后面是一个查询主键字段的子查询;  
   index_subquery=>与unique_subquery类似,区别在于in的后面是查询非唯一索引字段的子查询;  
   range=>单表中的查询范围;  
   index=>对于前面的每一行,都通过查询索引来得到数据;  
   all=>对于前面的每一行,都通过全表扫描来得到数据  
SELECT object_schema AS table_schema, object_name AS table_name, count_star AS rows_io_total, count_read AS rows_read, count_write AS rows_write, count_fetch AS rows_fetchs, count_insert AS rows_inserts, count_update AS rows_updates, count_delete AS rows_deletes, CONCAT(ROUND(sum_timer_fetch / 3600000000000000, 2), 'h') AS fetch_latency, CONCAT(ROUND(sum_timer_insert / 3600000000000000, 2), 'h') AS insert_latency, CONCAT(ROUND(sum_timer_update / 3600000000000000, 2), 'h') AS update_latency, CONCAT(ROUND(sum_timer_delete / 3600000000000000, 2), 'h') AS delete_latency FROM performance_schema.table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC ;

二 索引问题

索引状况

2.1 索引存储分类

MyISAM存储索引  表数据 和 索引 自动分开存储,各自独立文件 
InnoDB存储引擎  表数据 和 索引 存储在同一表空间,但可有多个文件组成 
Mysql存储类型只有两种,BTREE和HASH,具体情况和表的存储引擎有关 
MyISAM和InnoDb存储引擎 都支持 BTREE索引 
MEMORY/HEAP存储引擎 支持HASH,BTREE索引 

mysql不支持函数索引,但是能对队列的前面的某一部分进行索引,例如name字段,可以只取name的前四个字符来进行索引。 
SELECT OBJECT_SCHEMA AS table_schema, OBJECT_NAME AS table_name, INDEX_NAME as index_name, COUNT_FETCH AS rows_fetched, CONCAT(ROUND(SUM_TIMER_FETCH / 3600000000000000, 2), 'h') AS select_latency, COUNT_INSERT AS rows_inserted, CONCAT(ROUND(SUM_TIMER_INSERT / 3600000000000000, 2), 'h') AS insert_latency, COUNT_UPDATE AS rows_updated, CONCAT(ROUND(SUM_TIMER_UPDATE / 3600000000000000, 2), 'h') AS update_latency, COUNT_DELETE AS rows_deleted, CONCAT(ROUND(SUM_TIMER_DELETE / 3600000000000000, 2), 'h')AS delete_latencyFROM performance_schema.table_io_waits_summary_by_index_usageWHERE index_name IS NOT NULLORDER BY sum_timer_wait DESC;

2.2 使用索引

对相关列使用索引是提高select性能的最佳途径。

使用索引的条件:

a、查询条件中有索引关键字,
b、多列索引只有查询条件使用了多列关键字最左边的前缀时,才可以使用索引,否则将不能使用索引。

全表扫描情况

2.2.1 使用索引

一下情况中会使用到索引:

 (1) 多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用 
 (2) 对于使用like查询,后面如果是常量,只有%号不在第一个字符时,
    索引才可能被用到 比如 like "%3" 不会用索引, like "3%"就会走索引 
 (3) 对大文本进行搜索的时候,使用全文索引,而不是使用 like '%...%' 
 (4) 如果列名是索引,使用column_name is null将使用索引, 
  例如: select * from aaa where name is null(name是索引列) 
SELECT object_schema, object_name, count_read AS rows_full_scanned FROM performance_schema.table_io_waits_summary_by_index_usageWHERE index_name IS NULL AND count_read  0ORDER BY count_read DESC;

2.2.2 存在索引但不使用

在下列情况下,虽然mysql存在索引,但是并不会使用到索引

 (1)如果Mysql估计使用索引比全表扫描更慢,则不使用索引。
  例如如果列key_1 均匀分布在1和100之间,那么查询 select * from table where key_1 > 1 and key_1 < 90;

 (2)如果使用MEMPRY/HEAP表并且,where条件中不使用“=”进行索引列,
    那么不会用到索引。heap表只有在使用“=”的时候,才使用索引

 (3)用or隔开的条件,如果or前面的列中有索引,而后面的列中没有索引,
    那么涉及的索引都不会被用到(or中有一个条件中的列没有索引就用不到索引)

 (4)如果不是索引列的第一部分(复合索引的第一部分) 
 (5)如果like是%开始的

 (6)如果列类型是字符串,那么一定记得在where条件中把字符常量值用引号引起来,
    否则即便是这个列上有索引,也不用用到(比如name字段是字符串的,却写了name=123。要改成“123”) 

澳门娱乐场网址,没有使用的index

2.3 查看索引使用情况

Handler_read_key

代表一个行被索引值读的次数。
如果索引正在工作,值将很高,很低表明增加索引性能改善不高。

Handler_read_rnd_next

代表数据文件中读下一行的请求数。

查看方法:

show status like 'Handler_read%

值高则查询效率低,应建立索引补救。
如果正在进行大量的表扫描,Handle_read_rnd_next的值较高,则通常说明表索引不正确或者写入的查询没有利用索引。

SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 AND object_schema not in ('mysql','v_monitor') AND index_name  'PRIMARY' ORDER BY object_schema, object_name;

3 两个简单优化方法

糟糕的sql问题摘要

3.1 定期分析表和检查表

本文由澳门娱乐场网址发布于计算机教程,转载请注明出处:《深入浅出Mysql》笔记之优化

关键词:

上一篇:Linux下卸载MySQL数据库澳门娱乐场网址:

下一篇:没有了