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