mysql优化和常用语句.md

常用语句

进程连接

1. 查看表是否在使用 如果查询结果为空。则证明表没有在使用

show open tables where in_use > 0 ;

2. 显示用户正在运行的进程 除了 root 用户能看到所有正在运行的进程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的进程。

show processlist;

-- 结束指定进程 kill 6433339;

锁和事务

1. 当前运行的所有事务　

SELECT * FROM information_schema.INNODB_TRX; 　

2. 查看当前正在被锁定的对象以及等待锁的对象。

SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
select  * FROM information_schema.processlist;

binlog日志

1. 查看binlog日志列表

show master logs;

2. 查看指定日志文件指定位置日志数据

SHOW BINLOG EVENTS in 'mysql-bin.000945'  limit 1,100

3. 删除指定日志之前得日志

PURGE MASTER LOGS TO 'mysql-bin.001535';

创建用户，给权限

1. 创建用户

CREATE USER 'demo'@'%' IDENTIFIED BY '123';

优化参数

1. table_open_cache

show variables like '%table_open_cache%';
show global status like 'open%tables%';
SHOW GLOBAL STATUS LIKE 'Opened_tables';

2. thread_cache_size

SHOW GLOBAL STATUS LIKE 'Threads_created';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

sql语句优化，来源

1. 查看执行计划

EXPLAIN FORMAT=JSON
SELECT * FROM Country WHERE continent='Asia' and population > 5000000;
{
  "query_block": {
   "select_id": 1,
   "cost_info": {
   "query_cost": "53.80"            # This query costs 53.80 cost units
   },
   "table": {
   "table_name": "Country",
   "access_type": "ALL",            # ALL is a table scan
   "rows_examined_per_scan": 239,   # Accessing all 239 rows in the table
   "rows_produced_per_join": 11,
   "filtered": "4.76",
   "cost_info": {
      "read_cost": "51.52",
      "eval_cost": "2.28",
      "prefix_cost": "53.80",
      "data_read_per_join": "2K"
   },
   "used_columns": [
      "Code",
      "Name",
      "Continent",
      "Region",
      "SurfaceArea",
      "IndepYear",
      "Population",
      "LifeExpectancy",
      "GNP",
      "GNPOld",
      "LocalName",
      "GovernmentForm",
      "HeadOfState",
      "Capital",
      "Code2"
   ],
   "attached_condition": "((`world`.`Country`.`Continent` = 'Asia') and (`world`.`Country`.`Population` > 5000000))"
   }
  }
}