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
