mysql常用

安装

mysql官网下载地址
https://www.mysql.com/downloads/

Download MySQL Community Server
自己选择操作系统。ubuntu的安装如下：

sudo apt-get install mysql-server mysql-client

sudo service mysql restart

让apache支持mysql（可选）

sudo apt-get install libapache2-mod-auth-mysql

让php支持mysql（可选）

sudo apt-get install php5-mysql

登陆可以是windows或者linux

mysql -u root -p

使用mysql（选择的数据库名）数据库

use mysql

无法远程访问的办法
http://blog.csdn.net/mydeman/article/details/3847695

使用SHOW PROFILE

show profile 命令实在MySQL 5.1以后的版本引入的，来源于开源社区中的Jeremy Cole的贡献。默认是禁用的。会话（连接）级别开启show profile命令；

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

然后，在服务器上执行的所有有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。
先看个测试；

mysql> select * from proc;

结果有点长；下面查看show profiles；

mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration   | Query              |
+----------+------------+--------------------+
|        1 | 0.00109700 | select * from proc |
+----------+------------+--------------------+
1 row in set, 1 warning (0.00 sec)

上面显示了测量的语句执行时间；下面看看具体耗时；

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000040 |
| checking permissions | 0.000004 |
| Opening tables       | 0.000168 |
| init                 | 0.000015 |
| System lock          | 0.000004 |
| optimizing           | 0.000002 |
| statistics           | 0.000007 |
| preparing            | 0.000006 |
| executing            | 0.000001 |
| Sending data         | 0.000815 |
| end                  | 0.000003 |
| query end            | 0.000003 |
| closing tables       | 0.000005 |
| freeing items        | 0.000018 |
| cleaning up          | 0.000008 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

上面就是查到的结果；但是无法是用order by排序，所以不容易查看哪个步骤花费时间最长；
可以使用下面方式查————通过查看INFORMATION_SCHEMA中对应的表；

mysql> set @query_id = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select state,sum(duration) as total_r,
    ->     round(
    ->        100 * sum(duration) /
    ->            (select sum(duration) from information_schema.profiling
    ->              where query_id = @query_id
    ->            ),2)  as pct_r,
    ->     count(*) as calls,
    ->     sum(duration) / count(*) as "R/Call"
    -> from information_schema.profiling
    -> where query_id = @query_id
    -> group by state
    -> order by total_r desc;
+----------------------+----------+-------+-------+--------------+
| state                | total_r  | pct_r | calls | R/Call       |
+----------------------+----------+-------+-------+--------------+
| Sending data         | 0.000815 | 74.16 |     1 | 0.0008150000 |
| Opening tables       | 0.000168 | 15.29 |     1 | 0.0001680000 |
| starting             | 0.000040 |  3.64 |     1 | 0.0000400000 |
| freeing items        | 0.000018 |  1.64 |     1 | 0.0000180000 |
| init                 | 0.000015 |  1.36 |     1 | 0.0000150000 |
| cleaning up          | 0.000008 |  0.73 |     1 | 0.0000080000 |
| statistics           | 0.000007 |  0.64 |     1 | 0.0000070000 |
| preparing            | 0.000006 |  0.55 |     1 | 0.0000060000 |
| closing tables       | 0.000005 |  0.45 |     1 | 0.0000050000 |
| System lock          | 0.000004 |  0.36 |     1 | 0.0000040000 |
| checking permissions | 0.000004 |  0.36 |     1 | 0.0000040000 |
| query end            | 0.000003 |  0.27 |     1 | 0.0000030000 |
| end                  | 0.000003 |  0.27 |     1 | 0.0000030000 |
| optimizing           | 0.000002 |  0.18 |     1 | 0.0000020000 |
| executing            | 0.000001 |  0.09 |     1 | 0.0000010000 |
+----------------------+----------+-------+-------+--------------+
15 rows in set, 16 warnings (0.01 sec)

Sending Data(发送数据)：这个状态代表的原因非常多，可能是各种不同的服务器活动，包括在关联时搜索匹配的行记录等，这部分很难说能优化节省多少消耗的时间。

SHOW CREATE TABLE

查看mysql中的创建语句（这样可以看到更详细的创建语句，可以把INTEGER、BOOL等别名转换为MySQL的基本数据类型。）；

mysql> show create table char_test;
+-----------+------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                         |
+-----------+------------------------------------------------------------------------------------------------------+
| char_test | CREATE TABLE `char_test` (
  `char_col` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

\G 放到sql语句后,可以使每个字段打印到单独的行，

如：

mysql> select * from user_msg limit 2 \G;
*************************** 1. row ***************************
         uid: 1
   today_msg: 0
  today_recv: 1
recvmsgcount: 59
sendmsgcount: 0
     twoday1: 0
    treeday1: 0
     towday2: 0
    treeday2: 0
   todayfist: 0
*************************** 2. row ***************************
         uid: 2
   today_msg: 0
  today_recv: 1
recvmsgcount: 292
sendmsgcount: 2693
     twoday1: 0
    treeday1: 0
     towday2: 0
    treeday2: 0
   todayfist: 0
2 rows in set (0.02 sec)

检查连接数

检查最多连接数

(carl@localhost)[carl]> show variables like '%max_connections%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| extra_max_connections | 1     |
| max_connections       | 2048  |
+-----------------------+-------+
2 rows in set (0.00 sec)

检查当前连接数

(carl@localhost)[carl]> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 49    |
| Threads_connected | 41    |
| Threads_created   | 90    |
| Threads_running   | 1     |
+-------------------+-------+

查看mysql版本

MySQL> select version();

查看所有用户

select user,host from mysql.user

查看表占用存储大小

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
where table_schema='mysql' 
order by data_length desc, index_length desc;

查看字段信息

select column_name, column_comment  from information_schema.columns

---

参考：高性能MySQL（第3版）. Baron Schwartz，Peter Zaitsev，Vadim Tkachenko 著；宁海元，周振兴，彭立勋 等 译
参考：MySQL查看数据库表容量大小 https://blog.csdn.net/fdipzone/article/details/80144166