mysql etc profile_Mysql之表的操作&profile 及优化-白红宇

mysql etc profile_Mysql之表的操作&profile 及优化

阅读量：5870 次

发布时间：2019-06-19

本文共 17688 字，大约阅读时间需要 58 分钟。

创建一个表create table(help create table)

=>rename table A to B 更改表名

=>alter table A rename to B 更改表

=>drop table A 删除表

mysql>show create database gtms;　　#查看建库语句+----------+---------------------------------------------------------------+

| Database | Create Database |

+----------+---------------------------------------------------------------+

| gtms | CREATE DATABASE `gtms` /*!40100 DEFAULT CHARACTER SET utf8*/ |

+----------+---------------------------------------------------------------+

1 row in set (0.00sec)

mysql>create table student (-> id int(4) not nullauto_increment,-> name char(20) not null,-> age tinyint(2) not null default '0',-> dept varchar(16) default null,-> primary key(id),->key index_name(name)->);

mysql>show create table student\G　　#查看建表语句*************************** 1. row ***************************Table: student

Create Table: CREATE TABLE `student` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name`char(20) NOT NULL,

`age` tinyint(2) NOT NULL DEFAULT '0',

`dept` varchar(16) DEFAULT NULL,

PRIMARY KEY (`id`),　　　　　　　　　　#主键索引，唯一，一般建表时添加

KEY `index_name` (`name`)　　　　　　#普通索引

) ENGINE=InnoDB DEFAULT CHARSET=utf8

显示表结构desc或show columns

mysql>desc student;+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

| age | tinyint(2) | NO | | 0 | |

+-------+-------------+------+-----+---------+----------------+

4 rows in set (0.00sec)

mysql>show columns from student;+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

| age | tinyint(2) | NO | | 0 | |

+-------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

字段的增加与删除

alter table 表名 add[drop,modify,change] 字段类型其他选项;

mysql> alter table student add phonenum char(11) after name;　　#如插到第一列为firstmysql>desc student;+----------+-------------+------+-----+---------+----------------+

+----------+-------------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

| age | tinyint(2) | NO | | 0 | |

+----------+-------------+------+-----+---------+----------------+mysql>alter table student drop column phonenum;mysql>desc student;+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

| age | tinyint(2) | NO | | 0 | |

+-------+-------------+------+-----+---------+----------------+

字段名称和属性的更改

=>谨慎修改字段属性，可能导致原有数据出错

=>谨慎修改长度，需保证不短于已有数据

mysql> alter table student change name names char(20);

mysql> alter table student change names names char(30);

mysql>desc student;+----------+-------------+------+-----+---------+----------------+

+----------+-------------+------+-----+---------+----------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

| age | tinyint(2) | NO | | 0 | |

+----------+-------------+------+-----+---------+----------------+

mysql> alter table student modify column phonenum char(12);　　#也可以使用modify column进行更改

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

数据插入

=>delete from A; 　　一次全部删除表内数据，逻辑清除，按行处理。所以要加条件(多级审核)

=>truncate table A;　　也是直接清空一张表，物理删除(速度快)

mysql> insert into student values(1,"张三","13701800003",23,"经理"),(2,"李四","13701800004",24,"助理");

mysql> insert into student (name,phonenum,age,dept)values ("王五","13701800005",25,"员工");

mysql> select *from student;+----+--------+-------------+-----+--------+

+----+--------+-------------+-----+--------+

| 1 | 张三 | 13701800003 | 23 | 经理 |

| 2 | 李四 | 13701800004 | 24 | 助理 |

| 3 | 王五 | 13701800005 | 25 | 员工 |

+----+--------+-------------+-----+--------+

表数据修改

=>更改表数据一定要加上where条件，严重案例：所有记录都被更改

防止人为误操作：

a、mysql登陆命令加-U选项，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序拒绝执行

b、linux别名设置# alias mysql='mysql -U' ，加到/etc/profile

mysql> update student set qq="87014247" where id=1;

mysql> select *from student;+----+--------+-------------+-----+--------+----------+

| id | names | phonenum | age | dept | qq |

+----+--------+-------------+-----+--------+----------+

| 1 | 张三 | 13701800003 | 23 | 经理 | 87014247 |

| 2 | 李四 | 13701800004 | 24 | 助理 | 12344754 |

| 3 | 王五 | 13701800005 | 25 | 员工 | 5414754 |

+----+--------+-------------+-----+--------+----------+

MYSQL查询优化 profile(==>可以详细看到每个执行语句整个详细执行步骤)

使用help show profile可以详细的查看此功能的帮助，

mysql>help show profile;

Name:'SHOW PROFILE'Description:

Syntax:

SHOW PROFILE [type [, type] ... ]

[FOR QUERY n]

[LIMIT row_count [OFFSET offset]]

type:

The SHOW PROFILE and SHOW PROFILES statements display profiling

information that indicates resource usageforstatements executed

during the course of the current session.

Profiling is controlled by the profiling session variable,whichhas a

default value of0(OFF). Profiling is enabled by setting profiling to1or ON:

mysql> SET profiling = 1;

SHOW PROFILES displays a list of the most recent statements sent to the

server. The size of the list is controlled by the

profiling_history_size session variable,whichhas a default value of15. The maximum value is 100. Setting the value to 0has the practical

effect of disabling profiling.

All statements are profiled except SHOW PROFILE and SHOW PROFILES, so

you willfind neither of those statements inthe profile list.

Malformed statements are profiled. For example, SHOW PROFILING is an

illegal statement, and a syntax error occursifyou try to execute it,

but it will show upinthe profiling list.

SHOW PROFILE displays detailed information about a single statement.

Without the FOR QUERY n clause, the output pertains to the most

recently executed statement. If FOR QUERY n is included, SHOW PROFILE

displays informationforstatement n. The values of n correspond to the

Query_ID values displayed by SHOW PROFILES.

The LIMIT row_count clause may be given to limit the output to

row_count rows. If LIMIT is given, OFFSET offset may be added to begin

the output offset rows into the full set of rows.

By default, SHOW PROFILE displays Status and Duration columns. The

Status values are like the State values displayed by SHOW PROCESSLIST,

although there might be some minor differencesin interpretion forthe

two statementsforsome status values (see

http://dev.mysql.com/doc/refman/5.5/en/thread-information.html).

Optional type values may be specified to display specific additional

types of information:

o ALL displays all information

o BLOCK IO displays countsforblock input and output operations

o CONTEXT SWITCHES displays countsforvoluntary and involuntary

context switches

o CPU displays user and system CPU usage times

o IPC displays countsformessages sent and received

o MEMORY is not currently implemented

o PAGE FAULTS displays countsformajor and minor page faults

o SOURCE displays the names of functions from the source code, together

with the name and line number of thefile in which the functionoccurs

o SWAPS displays swap counts

Profiling is enabled per session. When a session ends, its profiling

information is lost.

URL: http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

Examples:

mysql>SELECT @@profiling;+-------------+

| @@profiling |

+-------------+

| 0 |

+-------------+

1 row in set (0.00sec)

mysql> SET profiling = 1;

Query OK,0 rows affected (0.00sec)

mysql>DROP TABLE IF EXISTS t1;

Query OK,0 rows affected, 1 warning (0.00sec)

mysql> CREATE TABLE T1 (idINT);

Query OK,0 rows affected (0.01sec)

mysql>SHOW PROFILES;+----------+----------+--------------------------+

| Query_ID | Duration | Query |

+----------+----------+--------------------------+

| 0 | 0.000088 | SET PROFILING = 1 |

| 1 | 0.000136 | DROP TABLE IF EXISTS t1 |

| 2 | 0.011947 | CREATE TABLE t1 (id INT) |

+----------+----------+--------------------------+

3 rows in set (0.00sec)

mysql>SHOW PROFILE;+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| checking permissions | 0.000040 |

| creating table | 0.000056 |

| After create | 0.011363 |

| query end | 0.000375 |

| freeing items | 0.000089 |

| logging slow query | 0.000019 |

| cleaning up | 0.000005 |

+----------------------+----------+

7 rows in set (0.00sec)

mysql> SHOW PROFILE FOR QUERY 1;+--------------------+----------+

| Status | Duration |

+--------------------+----------+

| query end | 0.000107 |

| freeing items | 0.000008 |

| logging slow query | 0.000015 |

| cleaning up | 0.000006 |

+--------------------+----------+

4 rows in set (0.00sec)

mysql> SHOW PROFILE CPU FOR QUERY 2;+----------------------+----------+----------+------------+

+----------------------+----------+----------+------------+

| checking permissions | 0.000040 | 0.000038 | 0.000002 |

| creating table | 0.000056 | 0.000028 | 0.000028 |

| After create | 0.011363 | 0.000217 | 0.001571 |

| query end | 0.000375 | 0.000013 | 0.000028 |

| freeing items | 0.000089 | 0.000010 | 0.000014 |

| logging slow query | 0.000019 | 0.000009 | 0.000010 |

| cleaning up | 0.000005 | 0.000003 | 0.000002 |

+----------------------+----------+----------+------------+

7 rows in set (0.00 sec)

mysql> help show profile;

mysql> set profiling=1;　　#打开profile功能mysql>SELECT @@profiling;+-------------+

| @@profiling |

+-------------+

| 1 |

+-------------+

mysql>use gtms

Database changed

mysql> select *from student;+----+--------+-------------+-----+--------+----------+

| id | names | phonenum | age | dept | qq |

+----+--------+-------------+-----+--------+----------+

| 1 | 张三 | 13701800003 | 23 | 经理 | 9485754 |

| 2 | 李四 | 13701800004 | 24 | 助理 | 12344754 |

| 3 | 王五 | 13701800005 | 25 | 员工 | 5414754 |

+----+--------+-------------+-----+--------+----------+mysql> select * from student where qq=5414754;+----+--------+-------------+-----+--------+---------+

| id | names | phonenum | age | dept | qq |

+----+--------+-------------+-----+--------+---------+

| 3 | 王五 | 13701800005 | 25 | 员工 | 5414754 |

+----+--------+-------------+-----+--------+---------+mysql>show profiles;　　#可以查看到执行的sql语句及执行时间+----------+------------+----------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+----------------------------------------+

| 1 | 0.00013425 | SELECT @@profiling |

| 2 | 0.00017100 | SELECT DATABASE() |

| 3 | 0.05092200 | select * from student |

| 4 | 0.00043800 | select * from student where qq=5414754 |

+----------+------------+----------------------------------------+mysql> show profile for query 3;　　#查看Query_ID为3的sql语句极为详细的过程及执行时间+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000084 |

| checking permissions | 0.000006 |

| Opening tables | 0.050367 |

| System lock | 0.000010 |

| init | 0.000017 |

| optimizing | 0.000020 |

| statistics | 0.000048 |

| preparing | 0.000049 |

| executing | 0.000004 |

| Sending data | 0.000175 |

| end | 0.000006 |

| query end | 0.000004 |

| closing tables | 0.000005 |

| freeing items | 0.000103 |

| logging slow query | 0.000025 |

| cleaning up | 0.000002 |

+----------------------+----------+

==>如果出现以下关键字，表明出现问题

conver HEAP to Mysam查询结果太大，内存不够，往磁盘写了

Create tmp table　　拷贝数据到临时表，用完删除

Copy to tmp table on disk 把内存中临时表复制到磁盘，很凶险

locked

mysql> show profile CPU for query 3;　　#查看CPU_user CPU_system使用信息

+----------------------+----------+----------+------------+

+----------------------+----------+----------+------------+

| starting | 0.000084 | 0.000000 | 0.000000 |

| checking permissions | 0.000006 | 0.000000 | 0.000000 |

| Opening tables | 0.050367 | 0.001000 | 0.000000 |

| System lock | 0.000010 | 0.000000 | 0.000000 |

| init | 0.000017 | 0.000000 | 0.000000 |

| optimizing | 0.000020 | 0.000000 | 0.000000 |

| statistics | 0.000048 | 0.000000 | 0.000000 |

| preparing | 0.000049 | 0.000000 | 0.000000 |

| executing | 0.000004 | 0.000000 | 0.000000 |

| Sending data | 0.000175 | 0.000000 | 0.000000 |

| end | 0.000006 | 0.000000 | 0.000000 |

| query end | 0.000004 | 0.000000 | 0.000000 |

| closing tables | 0.000005 | 0.000000 | 0.000000 |

| freeing items | 0.000103 | 0.000000 | 0.000000 |

| logging slow query | 0.000025 | 0.000000 | 0.000000 |

| cleaning up | 0.000002 | 0.000000 | 0.000000 |

+----------------------+----------+----------+------------+

Mysql sleep线程过多的问题

mysql>show full processlist; #查看当前运行的所有线程，如果sleep线程过多，有必要进行相关优化+----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------+

+----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------+

| 2 | rep | 192.168.0.81:44962 | NULL | Binlog Dump | 15 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |

+----+------+--------------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------+

mysql> show variables like '%timeout%'; 查看相关超时设置情况+------------------------------+----------+

| Variable_name | Value |

+------------------------------+----------+

| connect_timeout | 10 |

| delayed_insert_timeout | 300 |

| innodb_lock_wait_timeout | 50 |

| innodb_rollback_on_timeout | OFF |

| interactive_timeout | 28800 |　　　　=set global interactive_timeout=120 (超时会提示ERROR 2006 (HY000): MySQL server has gone away)

| lock_wait_timeout | 31536000 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| rpl_semi_sync_master_timeout | 10000 |

| slave_net_timeout | 3600 |

| wait_timeout | 28800 |　　　　=>set global wait_timeout=120

+------------------------------+----------+

1)interactive_timeout:

参数含义：服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。

参数默认值：28800秒(8小时)

(2)wait_timeout:

参数含义：服务器关闭非交互连接之前等待活动的秒数。

在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。

参数默认值：28800秒(8小时)

my.cnf中配置

[mysqld]

wait_timeout=120

interactive_timeout=120其他方法

在php程序中，不使用持久连接，使用mysql_connect 而不是pconnect(java调整连接池)

php程序执行完毕，应该显式调用mysql_close

Mysqladmin命令

[root@node80 ~]# mysqladmin -uroot -prootabcd status

Uptime:2113 Threads: 2 Questions: 30 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.014[root@node80~]# mysqladmin -uroot -prootabcd status -i 2Uptime:2120 Threads: 2 Questions: 32 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.015Uptime:2122 Threads: 2 Questions: 33 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.015

[root@node80 ~]# mysqladmin -uroot -prootabcd processlist+----+------+--------------------+----+-------------+------+-----------------------------------------------------------------------+------------------+

+----+------+--------------------+----+-------------+------+-----------------------------------------------------------------------+------------------+

| 2 | rep | 192.168.0.81:44962 | | Binlog Dump | 1911 | Master has sent all binlog to slave; waiting for binlog to be updated | |

+----+------+--------------------+----+-------------+------+-----------------------------------------------------------------------+------------------+

[root@node80 ~]# watch mysqladmin -uroot -prootabcd processlist 加上watch进行实时观察

[root@node80 ~]# mysqladmin -uroot -prootabcd flush-logs　　　　　　刷新binlog[root@node80 ~]# mysqladmin -uroot -prootabcd extended-status | wc -l Mysql其他状态情况

307

生产场景其他常用命令

mysql>show processlist; 查看正在执行的sql语句，不全

mysql>show full processlist; 查看正在执行的完整sql语句，完整显示

#mysql -uroot -pxxx -e "show full processlist;' | grep XXX

mysql> set global key_buffer_size=32768; myisam引擎重要参数

mysql>kill ID

mysql>show status; 查看当前会话的数据库状态信息

mysql>show engine innodb status\G 显示innodb引擎性能状态(早期版本show innodb status\G)

mysql>show global status; 查看整个数据库运行状态信息，很重要，要分析并做好监控, 调优依据(调优后观察状态) ,可以使用mysqlreport软件

通过show global status查看当天的insert，delete情况(也可以按天分析binlog，获取数据库不同语句的频率)

mysql> show global status like "%sert%";+------------------------+-------+