MySQL相关查询和操作

mysql查询当前进程（可筛选）

SELECT *  FROMinformation_schema.processlist

WHERE user = 'test' and state IS NOT NULL and info IS NOTNULL;

 

#用explain查看执行计划

explain select count(*) from `retail`.`crm_vip` t1 leftjoin crm_customer t2 ont1.customer_code=t2.customer_code

where 1=1  order by t1.vip_iddesc  LIMIT 1;

 

 

#查看已存在的索引

show index from crm_vip;

show keys from crm_vip;

 

 

#添加适当索引消除排序：

alter table crm_vip add indexcrm_vip_customer_code(customer_code);#未验证

CREATE UNIQUE INDEX index_name ON crm_vip(customer_code);

 

 

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

 

查看等待锁的事务

SELECT * FROMINFORMATION_SCHEMA.INNODB_LOCK_WAITS;

 

kill掉死锁的mysql语句

kill 82230715;

 

 

MYSQL赋权限

GRANT all PRIVILEGES on *.* to test@"%" IDENTIFIED by'test' WITHGRANT OPTION; #所有库的所有权限（远程）

GRANT all PRIVILEGES on *.* to test2@localhost IDENTIFIEDby 'test2' WITH GRANT OPTION; #所有库的所有权限（本机）

FLUSH PRIVILEGES;#重新赋权

 

#慢查询相关

set globallong_query_time=2;         #设置成2秒，加上global,下次进mysql已然生效 

set globalslow_query_log='ON';       #启用慢查询 

show variables like "%long_query_time%"; #查看一下慢查询的时间 

show variables like "%slow_query_log%";#查看是否已经开启慢查询和文件路径

SELECT SLEEP(3);

 

 

set GLOBAL max_connections=500;#最大连接数

 

show processlist;#进程

show global  STATUS ;

show variables like 'max_connections';

show variables like 'innodb_buffer_pool_size';

 

show variables like 'key_buffer_size';

show global status like 'qcache%';

show variables like 'query_cache_size';

 

show engine innodb status;

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

 

#相关调试语句

SELECT SLEEP(5);

#cat /alidata/server/mysql/data/anquan-slow.log|grepTime:|cut -b 1-15|uniq -c

 

 

MySQL数据库备份命令

备份MySQL数据库的命令

mysqldump -hhostname -uusername -ppassword databasename> backupfile.sql

 

备份MySQL数据库为带删除表的格式

备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。

mysqldump -–add-drop-table -uusername -ppassworddatabasename > backupfile.sql

 

直接将MySQL数据库压缩备份

mysqldump -hhostname -uusername -ppassword databasename| gzip > backupfile.sql.gz

 

备份MySQL数据库某个(些)表

mysqldump -hhostname -uusername -ppassword databasenamespecific_table1 specific_table2 > backupfile.sql

同时备份多个MySQL数据库

mysqldump -hhostname -uusername -ppassword –databasesdatabasename1 databasename2 databasename3 >multibackupfile.sql

仅仅备份数据库结构

mysqldump –no-data –databases databasename1 databasename2databasename3 > structurebackupfile.sql

备份服务器上所有数据库

mysqldump –all-databases > allbackupfile.sql

还原MySQL数据库的命令

mysql -hhostname -uusername -ppassword databasename <backupfile.sql

还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -uusername-ppassword databasename

将数据库转移到新服务器

mysqldump -uusername -ppassword databasename | mysql–host=*.*.*.* -C databasename

 

 

1、将时间转换为时间戳

select unix_timestamp('2009-10-26 10-06-07');

 

select unix_timestamp(now());

如果参数为空，则处理为当前时间

2、将时间戳转换为时间

select from_unixtime(1256540102)

有些应用生成的时间戳是比这个多出三位，是毫秒表示，如果要转换，需要先将最后三位去掉，否则返回NULL

 

 select date_format(日期字段,’%Y-%m-%d’)as ‘日期’ from test

 

 

 

#表中没有lastchanged时执行以下语句(增加lastchanged列，并自动填写时间戳内容)

ALTER TABLE `taobao_trade`

ADD COLUMN `lastchanged`  timestamp NOTNULL ON UPDATE CURRENT_TIMESTAMP  ;

 

 

 

#MYSQL统计查询

#按小时算：left(shijian,13)

#按分钟算：left(shijian,16)

select left(shijian, 16) as DT, COUNT(*) asDataCount

from taobao_trade

group by left(shijian, 16)

order by DT;

 

 

 

#MYSQL统计查询2

#按小时算：left(from_unixtime(shijian), 13)

#按分钟算：left(from_unixtime(shijian), 16)

select left( from_unixtime(shijian), 13) as DT, COUNT(*)as DataCount

from order_info

group by left( from_unixtime(shijian), 13)

order by DT desc;

 

 

单量统计语句如下：

--（SQLSERVER）WMS统计下载订单量

--按小时算：CONVERT(varchar(13),CreateDate,120)

--按分钟算：CONVERT(varchar(16),CreateDate,120)

select CONVERT(varchar(13),CreateDate,120) as DT, COUNT(*)as DataCount

from TBillOrderInfo with(nolock)

group by CONVERT(varchar(13),CreateDate,120)

order by DT desc

 

 

--（SQLSERVER）wms统计回写EC订单量

select CONVERT(varchar(13),LogDate,120) as DT, COUNT(*) asDataCount

from TSyncSenderLog with(nolock)

where   Category ='UpdateSalesOrderStatus'

 And Flag =  'SUCCESS'--And Flag可以是'Sending'或者'SUCCESS'

group by CONVERT(varchar(13),LogDate,120)

order by DT desc

 

 

#（MYSQL）EC统计下载单量

#按小时算：left(lastchanged,13)

#按分钟算：left(lastchanged,16)

select left(lastchanged, 16) as DT, COUNT(*) asDataCount

from taobao_trade

group by left(lastchanged, 16)

order by DT;

 

 

 

 

#（MYSQL）统计WMS回写EC每小时/分钟回写成功数

#按小时算：left(from_unixtime(shijian), 13)

#按分钟算：left(from_unixtime(shijian), 16)

select left( from_unixtime(shipping_time_ck), 16) as DT,COUNT(*) as DataCount

from order_info

group by left( from_unixtime(shipping_time_ck),16)

order by DT desc

LIMIT 100;

 

 

 

 

mysql 几个重要参数

innodb_buffer_pool_size

如 果用Innodb，那么这是一个重要变量。相对于MyISAM来说，Innodb对于buffersize更敏感。MySIAM可能对于大数据量使用默认的key_buffer_size也还好，但Innodb在大数据量时用默认值就感觉在爬了。Innodb的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用Innodb，可以把这个值设为内存的70%-80%。和key_buffer相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率。

innodb_additional_pool_size

这个的效果不是很明显，至少是当操作系统能合理分配内存时。但你可能仍需要设成20M或更多一点以看Innodb会分配多少内存做其他用途。

innodb_log_file_size

对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间。我一般用64M-512M，具体取决于服务器的空间。

innodb_log_buffer_size

默认值对于多数中等写操作和事务短的运用都是可以的。如 果经常做更新或者使用了很多blob数据，应该增大这个值。但太大了也是浪费内存，因为1秒钟总会flush（这个词的中文怎么说呢？）一次，所以不需要设到超过1秒的需求。8M-16M一般应该够了。小的运用可以设更小一点。

innodb_flush_log_at_trx_commit （这个很管用）

抱怨Innodb比MyISAM慢100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。

 

 

 

MySQL性能诊断与调优（转载）

LAMP 系统性能调优，第 3 部分: MySQL 服务器调优

http://www.ibm.com/developerworks/cn/linux/l-tune-lamp-3.html

LoadRunner监控MySQL

http://www.docin.com/p-92272846.html

Advanced MySQL Performance Optimization

http://www.mysqlperformanceblog.com/files/presentations/UC2005-Advanced-MySQL-

Performance-Optimization.pdf

Improving MySQL Server Performance with Intel C++Compiler

http://www.mysqlperformanceblog.com/files/presentations/LinuxWorld2005-Intel.pdf

 

MySQL性能诊断与调优相关工具：

http://www.mysqlperformanceblog.com/tools/

http://hackmysql.com/mysqlreport

Spotlight On MySQL:

http://www.quest.com/spotlight-on-mysql/

Mysql Administator

http://downloads.mysql.com/archives.php?p=MySQLAdministrationSuite

phpMyAdmin

http://www.phpmyadmin.net/home_page/index.php

 

 

mysql slow log分析工具

http://hackmysql.com/

http://www.willamowius.de/mysql-tools.html

http://code.google.com/p/mysql-log-filter/

http://myprofi.sourceforge.net/

 

mysql网络协议嗅探工具- mysqlsniffer

http://hackmysql.com/mysqlsniffer

 

MySQL性能诊断与调优相关书籍：

《High Performance MySQL SecondEdition》

 

优化mysql数据库性能的十个参数

(1)、max_connections：

允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 too manyconnections 错误。 默认数值是100，我把它改为1024。

(2)、record_buffer：

每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128k)，我把它改为16773120(16m)

(3)、key_buffer_size：

索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是8388600(8m)，我的mysql主机有2gb内存，所以我把它改为402649088(400mb)。

4)、back_log：

要求 mysql 能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。

back_log 值指出在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的tcp/ip连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。

当你观察你的主机进程列表，发现大量 264084 | unauthenticated user |xxx.xxx.xxx.xxx | null | connect | null | login | null的待连接进程时，就要加大 back_log 的值了。默认数值是50，我把它改为500。

(5)、interactive_timeout：

服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用client_interactive 选项的客户。 默认数值是28800，我把它改为7200。

(6)、sort_buffer：

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速orderby或groupby操作。默认数值是2097144(2m)，我把它改为16777208 (16m)。

(7)、table_cache：

为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是64，我把它改为512。

(8)、thread_cache_size：

可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较connections 和 threads_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。

(9)mysql的搜索功能

用mysql进行搜索，目的是能不分大小写，又能用中文进行搜索

只需起动mysqld时指定--default-character-set=gb2312

(10)、wait_timeout：

服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800，我把它改为7200。

注：参数的调整可以通过修改 /etc/my.cnf 文件并重启 mysql 实现。这是一个比较谨慎的工作，上面的结果也仅仅是我的一些看法，你可以根据你自己主机的硬件情况（特别是内存大小）进一步修改。

MySQL中查询所有数据库占用磁盘空间大小和单个库中所有表的大小的sql语句

查询所有数据库占用磁盘空间大小的SQL语句

select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_length desc;


查询单个库中所有表磁盘占用大小的SQL语句

select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size,
concat(truncate(index_length/1024/1024,2),' MB') as index_size
from information_schema.tables where TABLE_SCHEMA = 'TestDB'
group by TABLE_NAME
order by data_length desc;

windows、linux 下启用mysql日志功能

MYSQL日志分类及日志文件的概述

错误日志： -log-err 查询日志： -log 慢查询日志: -log-slow-queries 更新日志:-log-update 二进制日志： -log-bin 

1．错误日志   记录启动、运行或停止mysqld时出现的问题。
My.ini配置信息：
|#Enter a name for the error log file.   Otherwise a default name will be used.
|#log-error=d:/mysql_log_err.txt
2．查询日志    记录建立的客户端连接和执行的语句。
My.ini配置信息：
|#Enter a name for the query log file. Otherwise a default name will be used.
|#log=d:/mysql_log.txt
3．更新日志   记录更改数据的语句。不赞成使用该日志。
My.ini配置信息：
|#Enter a name for the update log file. Otherwise a default name will be used.
|#log-update=d:/mysql_log_update.txt
4．二进制日志    记录所有更改数据的语句。还用于复制。
My.ini配置信息：
|#Enter a name for the binary log. Otherwise a default name will be used.
|#log-bin=d:/mysql_log_bin
5．慢日志    记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。
My.ini配置信息：
|#Enter a name for the slow query log file. Otherwise a default name will be used.
|#long_query_time =1
|#log-slow-queries= d:/mysql_log_slow.txt


MYSQL日志的常用操作
在默认情况下，mysql安装是没有启用日志管理功能的，这为后续的维护带来很多不便的地方。

查看是否启用了日志
mysql>show variables like 'log_bin';
怎样知道当前的日志
mysql> show master status;
展示二进制日志数目 
mysql> show master logs;
看二进制日志文件用mysqlbinlog 
shell>mysqlbinlog mail-bin.000001 
或者shell>mysqlbinlog mail-bin.000001 | tail
在配置文件中指定log的輸出位置. 
Windows：Windows 的配置文件为 my.ini，一般在 MySQL 的安装目录下或者 c:\Windows 下。 
Linux：Linux 的配置文件为 my.cnf ，一般在 /etc 下。


MYSQL日志文件的开启

在linux下： 
Sql代码 
|# 在[mysqld] 中輸入   
|#log   
log-error=/usr/local/mysql/log/error.log   
log=/usr/local/mysql/log/mysql.log   
long_query_time=2   
log-slow-queries= /usr/local/mysql/log/slowquery.log  
|# 在[mysqld] 中輸入
|#log
log-error=/usr/local/mysql/log/error.log
log=/usr/local/mysql/log/mysql.log
long_query_time=2
log-slow-queries= /usr/local/mysql/log/slowquery.log


windows下: 
Sql代码 
|# 在[mysqld] 中輸入   
|#log   
log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log"  
log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log"  
long_query_time=2   
log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log"  
|# 在[mysqld] 中輸入
|#log
log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log"
log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log"
long_query_time=2
log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log"


开启慢查询 
long_query_time =2  --是指执行超过多久的sql会被log下来，这里是2秒 
log-slow-queries= /usr/local/mysql/log/slowquery.log  --将查询返回较慢的语句进行记录 
log-queries-not-using-indexes = nouseindex.log  --就是字面意思，log下来没有使用索引的query 
log=mylog.log  --对所有执行语句进行记录 


Linux下MYSQL清除日志文件命令

1、按文件:删除mysql-bin.000354之前的日志,不包含mysql-bin.000354
MYSQL>purge binary logs to 'mysql-bin.000354';
Query OK, 0 rows affected (0.16 sec)

2、按时间:删除2011-11-10 00:00:00 之前的日志
MYSQL>purge binary logs before '2011-11-1000:00:00';

3、 按时间:请理三天之前的日志
MYSQL> purge master logs before date_sub(now(), interval 3day);

4、自动清理日志 :修改my.cnf文件配置bin-log过期时间
[mysqld]
expire-logs-days=7
max-binlog-size=268435456