mysql常用

落日映苍穹つ 2022-06-03 03:28 191阅读 0赞

## 安装 ##

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 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 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

[Download MySQL Community Server]: https://dev.mysql.com/downloads/mysql/