MySQL 优化、管理

1. 通过show status 和 应用特点了解各种 SQL 的执行频率

 



2.定位执行效率较低的 SQL 语句

1) 可以通过慢查询日志定位那些执行效率较低的 SQL 语句， 用--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超时long_query_time秒的SQL语句的日志文件。

2）慢查询日志在查询结束以后才记录，所以在应用反映执行效率出问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态，是否锁表等等，可以实时的查看SQL执行情况，同时对一些锁表操作进行优化。


3. 通过 EXPLAIN 分析低效 SQL 的执行计划


4. MySQL 如何使用索引

查询要使用索引最主要的条件是查询条件中需要使用索引关键字，如果是多列索引，那么只有查询条件使用了多列关键字最左边的前缀时，才可以使用索引，否则将不能使用索引。


5.MySQL设置查看

show variables like '%timeout%';  #查询超时时间
innodb_lock_wait_timeout 120
lock_wait_timeout         31536000
wait_timeout             28800

show variables like '%per_table%';  #查询是否打开独立表空间
innodb_file_per_table     ON

SHOW VARIABLES LIKE '%query_cache%';#查看qcache状态
# 开启查询缓存对于读写都增加了额外的开销。对于读，在查询开始前需要先检查缓存；对于写，在写入后需要更新缓存。
# 一般情况这些开销相对较小，所以查询缓存一般还是有好处的。但也要根据业务特征权衡是否需要开启查询缓存。

have_query_cache             YES
query_cache_limit             2097152
query_cache_min_res_unit     4096
query_cache_size             8388608
query_cache_type             OFF
query_cache_wlock_invalidate OFF

SHOW VARIABLES LIKE '%innodb_buffer_pool%';#查看buffer_pool的大小
#  buffer pool是innodb存储引擎带的一个缓存池，查询数据的时候，它首先会从内存中查询，如果内存中存在的话，直接返回，从而提高查询响应时间。
#  innodb buffer pool和qcache的区别是：qcacche缓存的是sql语句对应的结果集，buffer pool中缓存的是表中的数据。buffer pool一般设置为服务器物理内存的70%。

innodb_buffer_pool_chunk_size     134217728
innodb_buffer_pool_dump_at_shutdown ON
innodb_buffer_pool_dump_now         OFF
innodb_buffer_pool_dump_pct         25
innodb_buffer_pool_filename         ib_buffer_pool
innodb_buffer_pool_instances     8
innodb_buffer_pool_load_abort     OFF
innodb_buffer_pool_load_at_startup ON
innodb_buffer_pool_load_now         OFF
innodb_buffer_pool_size             19327352832

SHOW VARIABLES LIKE '%slow%';#查看慢查询配置
log_slow_admin_statements OFF
log_slow_slave_statements OFF
slow_launch_time         2
slow_query_log             ON
slow_query_log_file         /opt/data/mysql2-slow.log

SHOW VARIABLES LIKE 'long_query_time'; #默认情况下，该临界值设置为 10 秒(10.000000)。
long_query_time 1.000000

SHOW GLOBAL STATUS LIKE "Questions"; #已执行语句（由客户端发出）计数
Questions 852575071

SHOW GLOBAL STATUS LIKE "Com_select"; #SELECT 语句吞吐量
Com_select 324269844

SHOW GLOBAL STATUS LIKE "Writes";#插入，更新或删除吞吐量


SHOW GLOBAL STATUS LIKE "Slow_queries";#超过可配置的 long_query_time 限制的查询数量
Slow_queries 582692

SHOW VARIABLES LIKE 'max_connections'; #数据库最大连接数
max_connections 1500

# 如果想实时的获取有没有执行时间长的sql，用下面这个sql语句
select id,`user`,`host`,DB,command,`time`,state,info from information_schema.PROCESSLIST where TIME>=60;


show variables  like 'port';#查看数据库使用端口