mysql语句及执行计划

 

数据库链接：

mysql -uroot -p <!--数据库连接-->
mysql -h10.0.0.100 -uuser -password
show databases <!--查看所有db-->
INSERT into `user` ( 
select id+3333504 ,`user_name` , `password` , 
`birthday` , `mobile` , `email` , `province` , `city` , `region` , `create_at` , `update_at` ,
`login_at` , `last_login_at` , `account_status` , `account_open_at`, `risk_at` , `risk_end` , `risk` ,
`nick` , `sms_status`, `source` , `fund_account` , `branch_no` , `id_no` , `client_name` , 
`friend_id` , `client_id` , `user_type` , `sale_licence` , `operate_code` , `user_status` , `user_trace`, `remote_addr` , `channel` 
from `user` ) <!----插入测试数据-->

计划任务：

mysql> show variables like "%pro%";<!---查一下profile是不是打开（未打开）-->
mysql> set profiling=1; <!--设置profile打开-->
mysql> use test --切换使用db
select id_no,user_name,mobile from `user` where SUBSTR(id_no,11,4)='0512' ; --执行sql
mysql> show profiles; --查看sql记录
mysql> show profile for query 2; ---查看详情计划任务
mysql> show profile block io,cpu for query 3; ---CPU、IO使用情况
mysql> show profile memory for query 3; --内存使用

 {
"Status": "query end", 状态
"Duration": "1.751142", 持续时间
"CPU_user": "0.008999", cpu用户
"CPU_system": "0.003999", cpu系统
"Context_voluntary": "98", 上下文主动切换
"Context_involuntary": "0", 上下文被动切换
"Block_ops_in": "8", 阻塞的输入操作
"Block_ops_out": "32", 阻塞的输出操作
"Messages_sent": "0", 消息发出
"Messages_received": "0", 消息接受
"Page_faults_major": "0", 主分页错误
"Page_faults_minor": "0", 次分页错误
"Swaps": "0", 交换次数
"Source_function": "mysql_execute_command", 源功能
"Source_file": "sql_parse.cc", 源文件
"Source_line": "4465" 源代码行
},

简单一点：

explain select * from tab_no_index ;

 

时间戳：

mysql>SELECT FROM_UNIXTIME( 1249488000, '%Y%m%d' ) 
mysql> SELECT UNIX_TIMESTAMP('2009-08-06') ; 
SELECT DATE_FORMAT(date_id,'%Y-%m-%d') from batch_select_check_point a where date_id< STR_TO_DATE('2016-08-10','%Y-%m-%d')