sql优化-检索及定位

一、执行频率早知道

 >show status；

使用 show status了解各种sql的执行频率 可搭配参数 global 全局查看，或者session当前连接开始计数 默认不加参数是指查看当前连接

show status; +-----------------------------------------------+----------------+ | Variable_name                                 | Value          | +-----------------------------------------------+----------------+ | Aborted_clients                               | 65             | | Aborted_connects                              | 589            | | Binlog_cache_disk_use                         | 0              | | Binlog_cache_use                              | 0              | | Binlog_stmt_cache_disk_use                    | 0              | | Binlog_stmt_cache_use                         | 0              | | Bytes_received                                | 1649           | | Bytes_sent                                    | 11767          | | Com_admin_commands                            | 0              | | Com_assign_to_keycache                        | 0              | | Com_alter_db                                  | 0              | | Com_alter_db_upgrade                          | 0              | ...

>常见的统计参数

 Com_select :执行select操作的次数，一次只累加1  Com_insert：执行insert操作的次数，一次也只累加1  Com_update:执行update操作的次数  Com_delete:执行delete的次数  Innodb_rows_read select查询的返回的行数  Innodb_rows_inserted 执行INSERT操作插入的行数  Innodb_rows_updated 执行update操作更新的行数  Innodb_rows_deleted 执行deleted操作删除的行数  Connections:试图连接MYSQL服务器的次数。  Uptime 服务器工作时间。  Slow_queries 慢查询的次数。

>查看慢查询次数

mysql> show global status like 'Slow_queries'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries  | 4     | +---------------+-------+ 1 row in set (0.00 sec)

二、效率较低早查询

>开启慢查询日志

        MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间通过long_query_time值来设定，超过这个值则会被记录到慢查询日志中。long_query_time的默认值为10，默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

1>查看是否开启忙日志查询

show variables like '%slow_query_log%';

2>开启满日志查询

1.配置文件修改 log-slow-queries = /usr/local/mysql/var/slowquery.log long_query_time = 3  #单位是秒 log-queries-not-using-indexes 2.使用sql语句修改 set global log_slow_queries = ON; set global slow_query_log = ON; set global long_query_time=0.01#单位是秒

3>慢日志格式

select * from auction1.user where user_name='liaoxz'; 执行的慢查询语句 # Time: 170427  4:41:48 日志记录的时间 # User@Host: root[root] @ localhost [] 服务器用户/主机名 # Query_time: 10.007816  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0 总的查询时间、锁定时间、"发送"或者返回的行数、查询过程中所检查的行数

三、分析语句早实行

>show profiles

profiling在mysql中默认是关闭的，在数据库会话级别可以打开，开启之后会收集在执行语句时候所使用的资源 在会话打开profilinges set profiling=1;

>使用show profiles查看执行的语句的持续时间和ID

MariaDB [auction1]> show profiles; +----------+------------+------------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration   | Query                                                                                                            | +----------+------------+------------------------------------------------------------------------------------------------------------------+ |        6 | 0.00038804 | select * from orders limit 1                                                                            | |        7 | 0.00019379 | select * from orders limit 1                                                                            | |        8 | 0.00028797 | select * from user limit 1                                                                                | |        9 | 0.00006671 | select * from orders right user where orders.ID=user.id                          | |       10 | 0.00336970 | select orders*.user.id from  where orders.ID=user.id                             | |       11 | 0.00005291 | select orders*,user.id from  where orders.ID=user.id                             | |       12 | 0.00004725 | select orders*,user.id from  orders,user where orders.ID=user.id | |       13 | 0.00003863 | select orders*,user.id from  orders,user where orders.ID=user.id | |       14 | 0.00024284 | select * from orders limit 1                                                                            | |       15 | 0.00027788 | select * from orders limit 10                                                                           | |       16 | 0.00007312 | select * from user where user='liaoxz'； select * from user where user='liaoxz'                    | |       17 | 0.00031860 | select * from user limit 1                                                                                | |       18 | 0.02771239 | select * from user where user_name='liaoxz'                                                               | |       19 | 0.03121868 | select * from user where user_name='liaoxz'                                                               | |       20 | 0.02219678 | select * from user where user_name='liaoxz'                                                               | +----------+------------+------------------------------------------------------------------------------------------------------------------+ 15 rows in set (0.00 sec)

>通过指定ID查询执行时的持续时间

MariaDB [auction1]> show profile for query 20; +----------------------+----------+ | Status               | Duration | +----------------------+----------+ | starting             | 0.000038 | | checking permissions | 0.000005 | | Opening tables       | 0.000010 | | After opening tables | 0.000004 | | System lock          | 0.000002 | | Table lock           | 0.000002 | | After table lock     | 0.000004 | | init                 | 0.000035 | | optimizing           | 0.000011 | | statistics           | 0.000013 | | preparing            | 0.000008 | | executing            | 0.000002 | | Sending data         | 0.021981 | | end                  | 0.000012 | | query end            | 0.000004 | | closing tables       | 0.000007 | | freeing items        | 0.000006 | | updating status      | 0.000019 | | logging slow query   | 0.000032 | | cleaning up          | 0.000002 | +----------------------+----------+ 20 rows in set (0.00 sec)

>具体资源耗费查询（all,cpu,block io,context switch,page faults）

MariaDB [auction1_1]> show profile cpu for query 20; +----------------------+----------+----------+------------+ | Status               | Duration | CPU_user | CPU_system | +----------------------+----------+----------+------------+ | starting             | 0.000038 | 0.000000 |   0.000000 | | checking permissions | 0.000005 | 0.000000 |   0.000000 | | Opening tables       | 0.000010 | 0.000000 |   0.000000 | | After opening tables | 0.000004 | 0.000000 |   0.000000 | | System lock          | 0.000002 | 0.000000 |   0.000000 | | Table lock           | 0.000002 | 0.000000 |   0.000000 | | After table lock     | 0.000004 | 0.000000 |   0.000000 | | init                 | 0.000035 | 0.000000 |   0.000000 | | optimizing           | 0.000011 | 0.000000 |   0.000000 | | statistics           | 0.000013 | 0.000000 |   0.000000 | | preparing            | 0.000008 | 0.000000 |   0.000000 | | executing            | 0.000002 | 0.000000 |   0.000000 | | Sending data         | 0.021981 | 0.026852 |   0.000000 | | end                  | 0.000012 | 0.000000 |   0.000000 | | query end            | 0.000004 | 0.000000 |   0.000000 | | closing tables       | 0.000007 | 0.000000 |   0.000000 | | freeing items        | 0.000006 | 0.000000 |   0.000000 | | updating status      | 0.000019 | 0.000000 |   0.000000 | | logging slow query   | 0.000032 | 0.000000 |   0.000000 | | cleaning up          | 0.000002 | 0.000000 |   0.000000 | +----------------------+----------+----------+------------+ 20 rows in set (0.00 sec)

四、常见show命令大全

>sql show大全

>1. show tables/show tables from database_name; -- 显示当前数据库中所有表的名称。 

>2. show databases; -- 显示mysql中所有数据库的名称。 

>3. show columns from table_name from database_name; 或show columns from database_name.table_name; -- 显示表中列名称。 

>4. show grants for user_name; -- 显示一个用户的权限，显示结果类似于grant 命令。 

>5. show index from table_name; -- 显示表的索引。 

>6. show variables; -- 显示系统变量的名称和值。 

>7. show processlist; -- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。 

>8. show table status; -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。 

>9. show privileges; -- 显示服务器所支持的不同权限。 

>10. show create database database_name; -- 显示create database 语句是否能够创建指定的数据库。 

>11. show create table table_name; -- 显示create database 语句是否能够创建指定的数据库。 

>12. show engines; -- 显示安装以后可用的存储引擎和默认引擎。 

>13. show innodb status; -- 显示innoDB存储引擎的状态。 

>14. show logs; -- 显示BDB存储引擎的日志。 

>15. show warnings; -- 显示最后一个执行的语句所产生的错误、警告和通知。 

>16. show errors; -- 只显示最后一个执行语句所产生的错误。 

>17. show [storage] engines; --显示安装后的可用存储引擎和默认引擎。

  

五、mysql配置文件来一套

>配置文件/etc/my.cnf

[client] port = 3306 socket = /tmp/mysql.sock default-character-set=utf8 [mysqld] default-time-zone = '+8:00' server-id=9527 user=mysql #bin-log日志相关优化参数 log-bin=mysql-master-bin #混合模式复制 binlog_format = mixed #存储二进制缓存 binlog_cache_size = 16M #bin-log日志保存多长时间 expire_logs_days=7 #最大连接数 max_connections=1000 #事务提交或事务外的指令把日志写入（flush）硬盘 innodb_flush_log_at_trx_commit=1 #文件系统自动识别缓存大小是否写入磁盘 sync_binlog=0 datadir = /data/mysqldb port = 3306 socket = /tmp/mysql.sock #table_cache = 5000 #缓存大小设置 query_cache_size = 256M #单个查询使用多大缓冲区 query_cache_limit = 16M #内存表大小 max_heap_table_size = 128M #线程池大小 thread_cache_size = 128 #慢查询超过多长时间写入日志 long_query_time = 3 #临时表大小 tmp_table_size = 128M #设置索引缓冲区大小 key_buffer_size = 256M #数据包大小接受设置 max_allowed_packet = 64M #索引扫描，范围索引扫描，无索引全表扫描的表连接 缓存的大小 join_buffer_size = 16M #排序 会话 的缓存大小 sort_buffer_size = 16M #顺序读表缓存 read_buffer_size = 16M #随机读缓冲区大小 read_rnd_buffer_size = 64M #缓存innodb表的索引，数据，插入数据时的缓冲 innodb_buffer_pool_size = 6G sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #timestamp类型的列明确的注明default值 explicit_defaults_for_timestamp=true #禁止域名解析 skip-name-resolve #数据库是否支持符号链接存储，1为允许 symbolic-links=0 #数据库级的字符集，对之前数据不产生影响 character-set-server=utf8mb4 #大小写铭感 lower_case_table_names=1 服务器关闭非交互连接之前等待活动的秒数 wait_timeout=180 #闭一个交互的连接之前所要等待的秒数 interactive_timeout=180