MySQL基础命令

mysql登录命令：mysql -h ip -P port -u 用户名 -p 密码
未登录情况下，查看本机mysql版本：mysql --version 或者 mysql -V
登录情况下，查看库版本：select version();或者select @@version;
显示所有数据库：show databases;
进入指定的库：use 库名;
显示当前库中所有的表：show tables;
查看其他库中所有的表：show tables from 库名;
查看表的创建语句：show create table 表名;
查看表结构：desc 表名;
查看当前所在库：select database();
查看当前mysql支持的所有存储引擎：SHOW ENGINES;
查看某表中的索引：show index from acl_class;，具体参数见下
查看mysql配置文件： mysql --help|grep my.cnf
显示正在运行的线程：show full processlist;，具体参数见下
查看当前会话的索引使用情况：show status like '%Handler_read%';
查询全局索引使用情况：show global status like 'Handler_read%';
查看有表是否被死锁：show open tables where in_use > 0;
查询innodb中一页大小（默认16k）：SHOW GLOBAL STATUS LIKE "Innodb_page_size";
开启关闭索引下推功能：set optimizer_switch='index_condition_pushdown=off|on';(MySQL5.6版本以后引入的一种优化机制，默认开启)

MySQL与变量相关的命令

查看所有系统变量：show global VARIABLES;
查看指定系统变量：SHOW VARIABLES like '%变量名%';，支持模糊检索
查看事务隔离级别：show variables like '%isolation%';
查看所有的全局变量：show global variables;
查看所有的会话变量：show session variables; 或者 show variables;
查看是否开启自动提交事务：SHOW VARIABLES LIKE 'autocommit';
查看慢日志开启状态和位置：show variables like '%slow_query_log%';
查看慢查询阈值（默认10s）：show variables like '%long_query_time%';
一个事务等待行锁的最大时间（默认50s）：show VARIABLES like '%innodb_lock_wait_timeout%';
查看MySQL数据目录的位置：SHOW VARIABLES LIKE 'datadir';
查看错误日志位置：SHOW VARIABLES LIKE "log_error";

MySQL慢日志命令

查看慢日志开启状态和位置：show variables like '%slow_query_log%';
查看慢查询阈值（默认10s）：show variables like '%long_query_time%';

MySQL事务、锁命令

设置隔离级别为RU级别（当前会话、连接生效）：set transaction isolation level read uncommitted;
一个事务等待行锁的最大时间（默认50s）：show VARIABLES like '%innodb_lock_wait_timeout%';
查看有表是否被死锁：show open tables where in_use > 0;
查询innodb中一页大小（默认16k）：SHOW GLOBAL STATUS LIKE "Innodb_page_size";
开启关闭索引下推功能：set optimizer_switch='index_condition_pushdown=off|on';(MySQL5.6版本以后引入的一种优化机制，默认开启)

MySQL进阶命令

`select CONNECTION_ID();`

每个MySQL连接，都有一个连接ID，可以通过该命令查看当前连接接id。navicat每新建一个查询窗口等同于建立一个连接

等价SHOW PROCESSLIST;中的id列
等价select * from information_schema.processlist;中的ID列
等价select thread_id, name, processlist_id from performance_schema.threads;中的processlist_id列

支持使用kill 连接id;命令来断开连接

`show full processlist;`

show processlist是显示用户正在运行的线程，最好使用root用户运行，否则只能看到当前用户下正在运行的线程。该命令只显示前100条，可以通过show full processlist显示全部

通常我们通过top检查发现mysql CPU或者io wait过高那么解决这些问题都离不开show processlist查询当前mysql有些线程正在运行，然后分析其中的参数,找出那些有问题的线程从而优化!

show processlist显示的信息都是来自MySQL系统库information_schema中的processlist表。所以使用下面的查询语句可以获得相同的结果： select * from information_schema.processlist;

响应参数说明如下：

参数	含义
Id	登录mysql后，系统分配的connection_id表示线程的唯一标识，可以使用函数`select CONNECTION_ID();`查看。当需要kill一个连接的时候会用到。这个Id就是`information_schema.processlist`表的主键。
User	就是指启动这个线程的用户,如果是`system user`，它是指由服务器产生的非客户线程，以在内部处理任务。这可能是复制从站或延迟行处理程序使用的I/O或SQL线程。`unauthenticated user`指的是已经与客户端连接关联但是还没有完成客户机用户的认证的线程。`event_scheduler`指的是监视预定事件的线程。如果是`system user`那么在Host列中不会指定主机。
Host	记录了发送请求的客户端的IP和端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。
db	当前执行的命令是在哪一个数据库上
Command	显示当前连接的执行的命令，一般就是休眠或空闲（sleep），查询（query），连接（connect）等，具体见下
Time	表示该线程处于当前状态的时间(秒)
State	显示使用当前连接的sql语句的状态，很重要的列。state只是语句执行中的某一个状态，一个 sql语句，以查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成，具体见下
Info	一般记录的是线程执行的语句

Command列

线程可以具有以下任何 Command值：

Binlog Dump： 这是主服务器上的线程，用于将二进制日志内容发送到从服务器。
Table Dump： 线程将表内容发送到从服务器。
Change user： 线程正在执行改变用户操作。
Close stmt： 线程正在关闭准备好的语句。
Connect： 复制中，从服务器连接到其主服务器。
Connect Out： 复制中，从服务器正在连接到其主服务器。
Create DB： 线程正在执行create-database操作。
Daemon： 此线程在服务器内部，而不是服务客户端连接的线程。
Debug： 线程正在生成调试信息。
Delayed insert： 线程是一个延迟插入处理程序。
Drop DB： 线程正在执行drop-database操作。
Execute： 线程正在执行一个准备好的语句（prepare statement)类型就是预编译的语句，JDBC支持次类型执行SQL）。
Fetch： 线程正在执行一个准备语句的结果。
Field List： 线程正在检索表列的信息。
Init DB： 线程正在选择默认数据库。
Kill： 线程正在杀死另一个线程。
Long Data： 该线程在执行一个准备语句的结果中检索长数据。
Ping： 线程正在处理服务器ping请求。
Prepare： 线程正在为语句生成执行计划。
Processlist： 线程正在生成有关服务器线程的信息。
Query： 该线程正在执行一个语句。
Quit： 线程正在终止。
Refresh： 线程是刷新表，日志或缓存，或重置状态变量或复制服务器信息。
Register Slave： 线程正在注册从服务器。
Reset stmt： 线程正在重置一个准备好的语句。
Set option： 线程正在设置或重置客户端语句执行选项。
Shutdown： 线程正在关闭服务器。
Sleep： 线程正在等待客户端向其发送新的语句。
Statistics： 线程正在生成服务器状态信息。
Time： 没用过。

State列

一般线程状态（State）值

以下列表描述State 了与常规查询处理关联的线程值，而不是更复杂的活动，例如复制。其中许多仅用于在服务器中查找错误。

After create： 当线程创建表（包括内部临时表）时，会在创建表的函数的末尾创建。即使由于某些错误而无法创建表，也会使用此状态。
Analyzing： 线程正在计算MyISAM表密钥分布（例如:for ANALYZE TABLE）。
checking permissions： 线程正在检查服务器是否具有执行语句所需的权限。
Checking table： 线程正在执行表检查操作。
cleaning up： 线程已经处理了一个命令，正在准备释放内存并重置某些状态变量。
closing tables： 线程将更改的表数据刷新到磁盘并关闭已用表。这应该是一个快速的操作。如果没有，请验证您是否没有完整的磁盘，并且磁盘没有被非常大的使用。
copy to tmp table： 线程正在处理ALTER TABLE语句。此状态发生在已创建新结构的表之后，但是将行复制到该表之前。对于此状态的线程，可以使用性能模式来获取有关复制操作的进度。
Copying to group table： 如果语句具有不同ORDER BY和GROUP BY标准，各行按组排列和复制到一个临时表。
Creating index： 线程正在处理ALTER TABLE … ENABLE KEYS一个MyISAM表。
Creating sort index： 线程正在处理一个SELECT使用内部临时表解析的线程。
creating table： 线程正在创建一个表，这包括创建临时表。
committing alter table to storage engine： 服务器已经完成就位ALTER TABLE并提交结果。
deleting from main table： 服务器正在执行多表删除的第一部分，它仅从第一个表中删除，并从其他（引用）表中保存要用于删除的列和偏移量。
deleting from reference tables： 服务器正在执行多表删除的第二部分，并从其他表中删除匹配的行。
discard_or_import_tablespace： 线程正在处理ALTER TABLE … DISCARD TABLESPACE或ALTER TABLE … IMPORT TABLESPACE声明。
end： 这发生在结束，但的清理之前ALTER TABLE， CREATE VIEW， DELETE， INSERT， SELECT，或UPDATE语句。
executing： 该线程已经开始执行一个语句。
Execution of init_command： 线程正在init_command系统变量的值中执行语句
freeing items： 线程已经执行了一个命令，在这种状态下完成的项目的一些释放涉及查询缓存，这个状态通常在后面cleaning up。
FULLTEXT initialization： 服务器正在准备执行自然语言全文搜索。
init： 此操作在初始化ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE之前发生，服务器在该状态中采取的操作包括刷新二进制日志、Innodb日志和一些查询缓存清理操作。对于最终状态, 可能会发生以下操作：更改表中的数据后删除查询缓存项、将事件写入二进制日志、释放内存缓冲区, 包括blob。
Killed： 执行KILL语句，向线程发送了一个声明，下次检查kill标志时应该中断。在MySQL的每个主循环中检查该标志，但在某些情况下，线程可能需要很短时间才能死掉。如果线程被某个其他线程锁定，则一旦其他线程释放锁定，该kill就会生效。
Locking system tables： 线程正在尝试锁定系统表（例如，时区或日志表）。
login： 连接线程的初始状态，直到客户端成功认证为止。
manage keys： 服务器启用或禁用表索引。
NULL： 该状态用于SHOW PROCESSLIST状态。
Opening system tables： 线程尝试打开系统表（例如，时区或日志表）。
Opening tables： 线程正在尝试打开一个表，这应该是非常快的程序，除非有事情阻止打开。例如，一个ALTER TABLE或一个LOCK TABLE语句可以阻止打开一个表，直到语句完成。还可能需要关注table_open_cache参数的值是否足够大。对于系统表，使用Opening system tables状态。
optimizing： 服务器正在执行查询的初始优化。
preparing： 此状态发生在查询优化期间。
Purging old relay logs： 线程正在删除不需要的中继日志文件。
query end： 处理查询之后，freeing items状态之前会发生这种状态。
Removing duplicates： 该查询的使用SELECT DISTINCT方式使得MySQL不能在早期阶段优化不同的操作。因此，MySQL需要一个额外的阶段来删除所有重复的行，然后将结果发送给客户端。
removing tmp table： 处理语句后，该线程正在删除一个内部临时表SELECT 。如果没有创建临时表，则不使用该状态。
rename： 线程正在重命名一个表。
rename result table： 线程正在处理一个ALTER TABLE语句，已经创建了新表，并重新命名它来替换原始表。
Reopen tables： 线程获得了表的锁，但在获得基础表结构更改的锁之后注意到。它释放了锁，关闭了table，并试图重新打开它。
Repair by sorting： 修复代码正在使用排序来创建索引。
preparing for alter table： 服务器正在准备就地执行ALTER TABLE。
Repair done： 线程已经完成了一个MyISAM表的多线程修复。
Repair with keycache： 修复代码通过密钥缓存逐个使用创建密钥，这比慢得多Repair by sorting。
Rolling back： 线程正在回滚事务。
Saving state： 对于MyISAM表操作（如修复或分析），线程将新的表状态保存到.MYI文件头。状态包括行数， AUTO_INCREMENT计数器和键分布等信息。
Searching rows for update： 线程正在进行第一阶段，以便在更新之前查找所有匹配的行。如果UPDATE要更改用于查找涉及的行的索引，则必须执行此操作。
setup： 线程正在开始一个ALTER TABLE操作。
Sorting for group： 线程正在做一个满足一个GROUP BY。
Sorting for order： 线程正在做一个满足一个ORDER BY。
Sorting index： 线程是排序索引页，以便在MyISAM表优化操作期间更有效地访问。
Sorting result： 对于一个SELECT语句，这类似于Creating sort index，但是对于非临时表。
statistics： 服务器正在计算统计信息以开发查询执行计划。如果一个线程长时间处于这种状态，服务器可能是磁盘绑定的，执行其他工作。
update： 线程正在准备开始更新表。
Updating： 线程正在搜索要更新的行并正在更新它们。
updating main table： 服务器正在执行多表更新的第一部分，它仅更新第一个表，并保存用于更新其他（引用）表的列和偏移量。
updating reference tables： 服务器正在执行多表更新的第二部分，并从其他表更新匹配的行。
User lock： 线程将要求或正在等待通过GET_LOCK()呼叫请求的咨询锁定。因为 SHOW PROFILE，这个状态意味着线程正在请求锁定（不等待它）。
User sleep： 线程调用了一个 SLEEP()调用。

故障诊断状态（State）值（个人提取）

logging slow query： 线程正在向慢查询日志写入语句。
altering table： 服务器正在执行就地ALTER TABLE。
Receiving from client： 服务器正在从客户端读取数据包。
Copying to tmp table： 服务器正在复制磁盘到内存的临时表，是直接在磁盘创建的临时表而并非从内存转到磁盘的临时表。
Copying to tmp table on disk： 对于线程将临时表从内存中更改为基于磁盘的格式存储以节省内存后，又把临时表从磁盘复制到内存时的状态。
Creating tmp table： 线程正在内存或磁盘上创建临时表。如果表在内存中创建，但后来转换为磁盘表，则该操作中的状态将为Copying to tmp table on disk。
Sending data： 线程正在读取和处理SELECT语句的行，并将数据发送到客户端。由于在此状态期间发生的操作往往执行大量的磁盘访问（读取），所以在给定查询的整个生命周期内通常是最长的运行状态。
Sending to client： 服务器正在向客户端写入数据包。
Waiting for commit lock： FLUSH TABLES WITH READ LOCK正在等待提交锁。
Waiting for global read lock： FLUSH TABLES WITH READ LOCK正在等待全局读锁定或read_only正在设置全局系统变量。
Waiting for tables： 线程得到一个通知，表格的底层结构已经改变，需要重新打开表以获得新的结构。但是，要重新打开表格，必须等到所有其他线程都关闭该表。如果另一个线程已使用FLUSH TABLES或下面的语句之一：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或OPTIMIZE TABLE都会发生通知。
Waiting for table flush： 线程正在执行FLUSH TABLES并正在等待所有线程关闭它们的表，或者线程得到一个通知，表中的底层结构已经改变，并且需要重新打开表以获得新的结构。但是，要重新打开表，必须等到所有其他线程都关闭该表。如果另一个线程已使用FLUSH TABLES或下面的语句之一：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或OPTIMIZE TABLE都会发出这个通知。
Waiting for lock_type lock： 服务器正在等待THR_LOCK从元数据锁定子系统获取锁或锁，其中lock_type指示锁的类型。THR_LOCK状态表示：Waiting for table level lock；这些状态表示等待元数据锁定：Waiting for event metadata lock、Waiting for global read lock、Waiting for schema metadata lock、Waiting for stored function metadata lock、Waiting for stored procedure metadata lock、Waiting for table metadata lock、Waiting for trigger metadata lock。
Writing to net： 服务器正在将数据包写入网络，如果一个线程长时间在执行并且一直处于Writing to net状态，那么一直在发送数据包到网络，可以试着调整max_allowed_packet大小。另外，这可能会导致其他线程大量阻塞。
Waiting on cond： 线程等待条件成为true的一般状态，没有特定的状态信息可用。
System lock： 线程已经调用mysql_lock_tables() ，且线程状态从未更新。这是一个非常普遍的状态，可能由于许多原因而发生。例如, 线程将请求或正在等待表的内部或外部系统锁。当InnoDB在执行锁表时等待表级锁时, 可能会发生这种情况。如果此状态是由于请求外部锁而导致的，并且不使用正在访问相同表的多个mysqld服务器MyISAM，则可以使用该–skip-external-locking选项禁用外部系统锁。但是，默认情况下禁用外部锁定，因此这个选项很有可能不起作用。因为SHOW PROFILE，这个状态意味着线程正在请求锁定（不等待它）。对于系统表，使用Locking system tables状态。

查询缓存状态（State）值

checking privileges on cached query： 服务器正在检查用户是否具有访问缓存查询结果的权限。
checking query cache for query： 服务器正在检查当前查询是否存在于查询缓存中。
invalidating query cache entries： 查询缓存条目被标记为无效，因为底层表已更改。
sending cached result to client：服务器正在从查询缓存中获取查询的结果，并将其发送给客户端。
storing result in query cache： 服务器将查询结果存储在查询缓存中。
Waiting for query cache lock： 当会话正在等待采取查询缓存锁定时，会发生此状态。这种情况可能需要执行一些查询缓存操作，如使查询缓存无效的INSERT或DELETE语句，以及RESET QUERY CACHE等等。

事件调度器线程状态（State）值

这些状态适用于事件调度程序线程，创建用于执行调度事件的线程或终止调度程序的线程。

Clearing：调度程序线程或正在执行事件的线程正在终止，即将结束。
Initialized：调度程序线程或将执行事件的线程已初始化。
Waiting for next activation：调度程序具有非空事件队列，但下一次激活是将来。
Waiting for scheduler to stop：线程发出SET GLOBAL event_scheduler=OFF并正在等待调度程序停止。
Waiting on empty queue：调度程序的事件队列是空的，它正在休眠。

`show open tables;`

列出当前在表缓存中打开的非 TEMPORARY 表，查看表锁定情况： show open tables;

指定数据库： show open tables from 数据库名

指定表名,支持通配： show open tables like '%表名%';

param	desc
Database	数据库名
Table	表名
In_use	表锁定或锁请求数，见下
Name_locked	指定表是否已锁定。名称锁定用于删除或重命名表等操作

`In_use`详解

例如，如果一个客户端执行LOCK TABLE 表1 WRITE 尝试获取该表的锁，那么 In_use 是 1。如果另一个客户端在表保持锁定的情况下执行LOCK TABLE 表1 WRITE，则客户端将阻塞，等待锁定，但锁定请求会导致 In_use 为 2。

如果计数为零，则表已打开，但当前未使用
HANDLER ... OPEN 语句会增加 In_use 值
HANDLER ... CLOSE 语句会减少 In_use 值

`show status like 'table%';`

标题
Table_locks_immediate	表示产生表级锁定的次数，每获得一个锁就加1
Table_locks_waited	表示发生表级锁争抢时，所等待的次数。若该值很大，说明当前存在比较严重的表级锁的竞争
Table_open_cache_hits
Table_open_cache_misses
Table_open_cache_overflows

`show status like 'innodb_row_lock%';` 行锁分析

可以查询查看innodb行锁相关的统计信息，这个命令会返回一些关于InnoDB行锁的状态值。如下：

这些值对于分析数据库性能和排查锁竞争问题都非常有帮助。各参数解释如下：

param	desc
Innodb_row_lock_current_waits	当前正在等待锁定的数量
Innodb_row_lock_time	从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg	每次等待所花平均时长
Innodb_row_lock_time_max	从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits	系统启动后到现在总共等待的次数

如果Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比较高，说明锁争用比较严重

`show engine innodb status;`

用于显示InnoDB存储引擎的状态信息，包括在途事务、锁、缓冲池和日志文件等，从而帮助诊断数据库性能问题。

SHOW ENGINE INNODB STATUS是SHOW STATUS的一种特殊形式，它能够显示InnoDB Monitor的输出。

InnoDB Monitor提供了关于InnoDB内部状态的信息，对于我们诊断InnoDB的问题有很大帮助。

显示的内容有：

STATUS：输出时间戳、InnoDB Monitor的名字、秒数，或显示InnoDB Monitor到当前时间经过了多少时间。
BACKGROUND THREAD：srv_master_thread展示了后台主线程所执行的工作。
SEMAPHORES：正在等待信号量的线程和该线程在互斥信号量或读写锁信号量上需要旋转和等待的次数。如果这个线程数量过大了，可能是由于存在I/O或争用的问题，可以尝试减小系统变量innodb_thread_concurrency。Spin rounds per wait展示了每个操作系统等待互斥信号量的自旋锁轮数。
LATEST FOREIGN KEY ERROR：显示外键错误，输出发生错误的语句、相关的外键和表的信息。
LATEST DETECTED DEADLOCK：显示死锁信息。输出死锁涉及到的事务、正在执行、保持和要求锁定的语句，以及回滚的事务。
TRANSACTIONS：帮助确定锁争用以及造成死锁的原因。
FILE I/O：InnoDB的线程信息以及挂起的IO操作和IO性能统计信息。
INSERT BUFFER AND ADAPTIVE HASH INDEX：InnoDB insert buffer和Adaptive hash index的状态信息包括每种操作的执行次数以及Adaptive hash index的性能信息。
LOG：InnoDB的日志信息，包括当前日志的序列号和刷新到磁盘上的日志的距离、上一次InnoDB建立checkpoint的位置、挂起的写操作以及写操作的性能统计信息。
BUFFER POOL AND MEMORY：缓存池页面的读写信息，可以查看查询所执行的I/O操作的次数。
ROW OPERATIONS：主线程相关的信息，包括各种类型的行操作的数量和性能水平。

`show index from 表名;`

Table：当前索引属于那张表。
Non_unique：目前索引是否属于唯一索引，0代表是的，1代表不是。
Key_name：当前索引的名字。
Seq_in_index：如果当前是联合索引，目前字段在联合索引中排第几个。
Column_name：当前索引是位于哪个字段上建立的。
Collation：字段值以什么方式存储在索引中，A表示有序存储，NULL表无序。
Cardinality：当前索引的散列程度，也就是索引中存储了多少个不同的值。
Sub_part：当前索引使用了字段值的多少个字符建立，NULL表示全部。
Packed：表示索引在存储字段值时，以什么方式压缩，NULL表示未压缩，
Null：当前作为索引字段的值中，是否存在NULL值，YES表示存在。
Index_type：当前索引的结构（BTREE, FULLTEXT, HASH, RTREE）。
Comment：创建索引时，是否对索引有备注信息。

常用sql

按客户端 IP 分组，看哪个客户端的链接数最多

select client_ip,count(client_ip) as client_num 
from (select substring_index(host,':' ,1) as client_ip 
from information_schema.processlist ) as connect_info 
group by client_ip 
order by client_num desc;

查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程

select * 
from information_schema.processlist 
where Command != 'Sleep' 
order by Time desc;

找出所有执行时间超过 1 分钟的线程，拼凑出 kill 语句，方便后面查杀

select concat('kill ', id, ';') as kill_command
from information_schema.processlist 
where Command != 'Sleep' and Time > 60 
order by Time desc;

查询所有数据库记录数和容量

SELECT table_schema AS '数据库',SUM(table_rows) AS '记录数',SUM(TRUNCATE(data_length/1024/1024, 2)) AS '数据容量(MB)',SUM(TRUNCATE(index_length/1024/1024, 2)) AS '索引容量(MB)' FROM information_schema.tables GROUP BY table_schema ORDER BY SUM(data_length) DESC, SUM(index_length) DESC;

统计所有数据库的总容量：

SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS DATA FROM information_schema.TABLES;

查询每张表的记录数和容量：

SELECT table_schema AS '数据库',table_name AS '表名',table_rows AS '记录数',TRUNCATE(data_length/1024/1024, 2) AS '数据容量(MB)',TRUNCATE(index_length/1024/1024, 2) AS '索引容量(MB)' FROM information_schema.tables ORDER BY data_length DESC, index_length DESC;

MySQL常用命令笔记