mysql

之前写过MySQL在各平台的安装过程，本文主要描述了客户端对于数据库的操作。

连接数据库

连接服务端参数简介

-u 指定用户-p 指定密码-S 指定Sock文件-h 指定主机名称-P 指定端口-e 不交互，执行sql语句< 导入数据source 导入数据

示例：

#-----使用Sock连接mysql -uroot -p -S /tmp/mysql.sock#-----使用TCP/IP连接mysql -uroot -p -h 10.0.0.1 -P3306#-----使用默认Sock连接，并且查看数据库mysql -uroot -proot -e "show databases;"#############返回结果############################mysql: [Warning] Using a password on the command line interface can be insecure.+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+#############返回结果#############################-----将创建数据库语句写入到文件中echo "create database db01 charset utf8;" > /root/db01.sql#-----非交互式执行sql语句文件mysql -uroot -proot </root/db01.sqlmysql -uroot -proot -e "show databases;"#############返回结果############################+--------------------+| Database |+--------------------+| information_schema || db01 || mysql || performance_schema || sys |+--------------------+#########################################

常用内置命令

help 打印mysql帮助\c 终止当前语句执行\q 退出\G 格式化查询结果source 恢复备份文件source /root/db01.sql;

SQL基础应用

SQL介绍

结构化的查询语言，是关系型数据库通用的命令，遵循SQL92的标准(SQL_MODE)。

SQL种类

DDL 数据定义语言DCL 数据控制语言DML 数据操作语言DQL 数据查询语言SQL引入数据库的逻辑结构

库

库名字库属性：字符集和校对规则表

表名表属性：存储引擎类型，字符集，排序规则列名列属性：数据类型、约束、其他属性数据行字符集

相当于MySQL的密码本(编码表)

默认字符集为latin1，推荐使用utf8mb4

utf8字符集占三个字节

utf8mb4字符集占四个字节，虽然占用的空间多，但是支持的字符种类更完全，MySQL8.0l默认使用utf8mb4字符集，支持emoji

#查看字符集show charset;

排序规则:collation

对于英文字符串大小写的敏感

校对规则跟着字符集，如果不设置，则使用默认设置的字符集与排序规则

utf8mb4generalci 大小写不敏感

utf8mb4_bin 大小写敏感(存拼音、日文)

#查看排序规则show collation;

数据库常用数据类型

数字

数据库常用数据类型

整数

tinyint 极小的整数数据类型(0-255)

int 常规整数数据类型(-2^32-2^32-1)

浮点

略

字符串

char(100) 定长的字符串类型，不管存储的字符串长度多长，都立即分配100个字符长度的存储空间，未填满的空间，以空格填充，char可以存储255个字符

varchar(100) 变长的字符串类型，每次存储前，都要先判断一下长度，按需要分配磁盘空间，会单独申请一个字符长度的空间存储字符长度(字符长度小于255，如果超过255以上的字符串长度，会占用两个存储空间)，varchar最多可以存储65535个字符

如何选择这两种存储类型呢？

少于255个字符串长度，定长的列值，选择char多于255个字符长度，变长的列值，选择varchar悬念，以上数据类型可能会影响到索引的性能

enum 枚举数据类型

address enum('sz','sh','bj'....)

编号 1 2 3

将编号与地址关联起来，在程序中选择编号，提升性能

时间

时间

date YYYY-MM-DDTIME hh:mm:ss[.uuuuu]datetime YYYY-MM-DD hh:mm:ss[.uuuuu]

范围从 1000-01-01 00:00:00.0000000 至 9999-12-31 23:59:59.99999

timestamp YYYY-MM-DD hh:mm:ss[.uuuuu]

范围从 1970-01-01 00:00:00.0000000 至 2038-01-19 03:14:07.99999

以后会使用datetime这种格式，它的时间范围更大

timestamp会自动跟着时区变化，datetime不会

作者 | 阿文

责编 | 郭芮

今天和大家分享一下 MySQL 的慢日志。

在实际工作中，我面对很多用户会经常碰到一个问题，那就是在使用 MySQL 时，执行一条语句需要很长时间返回，这是为什么呢？当你在使用 MySQL 中，当发现一些性能问题的时，比如执行一个语句要很长时间才返回结果，我们称之为慢查询，一般来说，发生慢查询的原因有：

你的索引设计有问题，可能会导致每次执行语句都是全表扫描，这样很耗费时间；你的 SQL 语句没有写好，可能会导致查询时间变长；MySQL 选择了错误的索引，同样会导致全表扫描。

通常情况下，在业务上线之前，会在测试环境里面，把 MySQL 的慢查询打开，也就是把 longquerytime 设置为 0，这样确保每一条语句都被记录到慢日志中去，具体的配置可以参考下文。

MySQL 的慢查询日志是MySQL提供的一种日志记录，它是用来记录在MySQL中响应时间超过阀值的语句。系统默认情况下，MySQL 并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

慢查询配置

默认情况下slowquerylog的值为OFF，表示慢查询日志是禁用的，可以通过设置slowquerylog的值来开启，如下所示：

mysql> show variables like '%slow_query_log%' -> ; +---------------------+-------------------------------+| Variable_name | Value | +---------------------+-------------------------------+ | slow_query_log | OFF || slow_query_log_file | /var/lib/mysql/mysql-slow.log | +---------------------+-------------------------------+2 rows in set (0.00 sec)

开启慢查询日志：

mysql> setglobal slow_query_log=1;Query OK, 0rows affected (0.00 sec)

然后看状态：

mysql> show variables like '%slow_query_log%';+---------------------+-------------------------------+| Variable_name | Value |+---------------------+-------------------------------+| slow_query_log | ON || slow_query_log_file | /var/lib/mysql/mysql-slow.log |+---------------------+-------------------------------+2 rows in set (0.00 sec)

使用set global slowquerylog=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）。

例如如下所示：

[root@mysql ~]# vim /etc/my.cnfslow_query_log=1slow_query_log_file=/var/lib/mysql/mysql-slow.log

参数说明：

slowquerylog 慢查询开启状态；slowquerylog_file 慢查询日志存放的位置；longquerytime 查询超过多少秒才记录。

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？ 是否会被写入到慢日志是由参数longquerytime控制，默认情况下longquerytime的值为10秒，可以使用命令修改，也可以在my.cnf参数里面修改。关于运行时间正好等于longquerytime的情况，并不会被记录下来。也就是说，在MySQL源码里是判断大于longquerytime，而非大于等于。从MySQL 5.1开始，longquerytime开始以微秒记录SQL语句运行时间，之前仅用秒为单位记录。如果记录到表里面，只会记录整数部分，不会记录微秒部分。

查看long_time 值，默认是 10 秒。

mysql> show variables like '%long_query_time%'; +-----------------+-----------+| Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+1 row in set (0.00 sec)

设置值，例如这里设置为 5 秒：

mysql> setglobal long_query_time=5; Query OK, 0 rows affected (0.00 sec) mysql> show variables like'%long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+1 row inset (0.01 sec)

如上所示，修改了变量longquerytime，但是查询变量longquerytime的值还是10。

使用命令 set global longquerytime=5修改后，需要重新连接或新开一个会话才能看到修改值。因为通过用show variables like 'longquerytime'查看是当前会话的变量值。

也可以不用重新连接会话，而是用 show global variables like 'longquerytime'; 如下所示：

mysql> show global variables like 'long_query_time'; +-----------------+----------+| Variable_name | Value | +-----------------+----------+ | long_query_time | 5.000000 | +-----------------+----------+1 row in set (0.00 sec)

我们设置longquerytime的值为2：

mysql> setglobal long_query_time=2; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 2.000000 | +-----------------+----------+1 row inset (0.00 sec)

然后执行一条超时5秒的语句（需要重新连接MySQL）：

[root@mysql ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands endwith ; or \g. Your MySQL connection id is 4 Server version: 5.7.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type'\c'toclear the currentinput statement. mysql> showvariableslike'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 2.000000 | +-----------------+----------+ 1 row in set (0.02 sec) mysql> selectsleep(5) -> ; +----------+ | sleep(5) | +----------+ | 0 | +----------+ 1 row in set (5.01 sec)

然后查看慢日志可以看到类似信息：

[root@mysql ~]# cat /var/lib/mysql/mysql-slow.log /usr/sbin/mysqld, Version: 5.7.21 (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sockTime Id Command Argument /usr/sbin/mysqld, Version: 5.7.21-log (MySQL Community Server (GPL)). started with: Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sockTime Id Command Argument # Time: 2018-02-09T06:59:47.782111Z # User@Host: root[root] @ localhost [] Id: 4 # Query_time: 5.000252 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0SET timestamp=1518159587;select sleep(5);

logoutput 参数是指定日志的存储方式。logoutput='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slowlog表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：logoutput='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

mysql> show variables like '%log_output%'; +---------------+-------+| Variable_name | Value | +---------------+-------+ | log_output | FILE | +---------------+-------+1 row in set (0.01 sec)

设置为表：

mysql> set global log_output='TABLE'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%log_output%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | TABLE | +---------------+-------+1 row in set (0.00 sec) mysql> select sleep(5); +----------+ | sleep(5) | +----------+ | 0 | +----------+1 row in set (5.00 sec)

执行 select * from mysql.slowlog; 观察慢查询日志里每类语句的输出，其中querytime表示query语句的执行时间，单位是为秒，lock time是锁定的时间，rowssent是query语句执行返回的记录数，而rowsexamined则是优化器估算的扫描行数，querytime、rowsexamined、rowssent 三个值可以大致衡量一条查询的成本，特别留意 rowsexamined 字段是否与预期一致。

系统变量log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan的sql也会被记录到慢查询日志。

mysql> show variables like'log_queries_not_using_indexes'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | OFF | +-------------------------------+-------+1 row inset (0.00 sec) mysql> setglobal log_queries_not_using_indexes=1; Query OK, 0 rows affected (0.00 sec) mysql> show variables like'log_queries_not_using_indexes'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | +-------------------------------+-------+1 row inset (0.00 sec) mysql>

系统变量logslowadmin_statements表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志：

mysql> show variables like'log_slow_admin_statements'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | log_slow_admin_statements | OFF | +---------------------------+-------+1 row inset (0.00 sec) mysql> mysql> setglobal log_slow_admin_statements=1; Query OK, 0 rows affected (0.01 sec) mysql> show variables like'log_slow_admin_statements'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | log_slow_admin_statements | ON | +---------------------------+-------+1 row inset (0.00 sec)

查询有多少条慢日志，可以使用系统变量：

mysql> show global status like '%slow_queries%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 2 | +---------------+-------+1 row in set (0.01 sec)

日志分析工具mysqldumpslow

MySQL 自带了一个查看慢日志的工具 mysqldumpslow，执行mysqldumpslow --help 可以查看其相关参数和说明：

[root@mysql ~]# mysqldumpslow --help Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] Parse and summarize the MySQL slow query log. Options are--verbose verbose--debug debug--help write this text to standard output -v verbose -d debug -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average locktime ar: average rows sentat: average querytime c: count l: locktime r: rows sent t: querytime -r reverse the sortorder (largest last instead offirst) -t NUM just show the top n queries -a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract locktimefrom total time

参数解释：

-s:是表示按照何种方式排序；c: 访问计数；l: 锁定时间；r: 返回记录；t: 查询时间；al:平均锁定时间；ar:平均返回记录数；at:平均查询时间；-t:是top n的意思，即为返回前面多少条的数据；-g:后边可以写一个正则匹配模式，大小写不敏感的。

例如，得到返回记录集最多的10个SQL：

mysqldumpslow -s r -t 10 /mysql/mysql_slow.log

得到访问次数最多的10个SQL：

mysqldumpslow -s c -t 10 /mysql/mysql_slow.log

通过以上方式我们可以配置和发现慢 SQL，那么在发现慢 SQL 语句之后，我们可以通过对数据库进行优化来提升 SQL 的执行速度，比如加索引或修改该应用的实现方式等。

本教程关于如何下载MySQL并创建连接。

MySQL Python:关于MySQL

MySQL是一个快速、简单易上手的数据库。它是目前最流行的开源数据库。MySQL适用于许多大小企业，由瑞典MySQL AB公司开发、销售和支持，是用C和c++编写的。MySQL是一个开源数据库，所以你可以免费下载使用。下载MySQL

遵循以下步骤:

访问MySQL官方网站。为您想要的MySQL社区服务器选择版本号。Python MySQL连接器

MySQL Python连接器用于从Python访问MySQL数据库，您需要一个数据库驱动程序。

MySQL Connector/Python是MySQL提供的标准化数据库驱动程序。

检查mysql连接器是否可用，我们输入以下命令:

import mysql.connector

输入命令之后，我们清楚地看到没有名为MySQL的模块。

因此，我们必须安装MySQL，Python需要一个MySQL驱动程序来访问MySQL数据库。

接下来，我们下载使用pip的mysql连接器。

C:\Users\Nitin Arvind Shelke>pip install mysql-connector

安装后，我们测试它是否工作，可用以下命令检查:

import mysql.connector

上面的代码行在程序中导入了MySQL Connector Python模块，可以使用这个模块的API来连接MySQL。

如果执行上面的代码没有错误，我们就说“MySQL Connector”安装正确，然后准备好使用就可以了。

from mysql.connector import Error

MySQL connector error对象用于在连接数据库失败时或在处理数据库时发生其他数据库错误时显示错误。

创建到数据库的连接

安装完MySQL Python连接器后，我们需要对其进行测试，以确保其工作正常，并且您可以毫无问题地连接到MySQL数据库服务器。要验证安装，请使用以下步骤:

输入以下代码行:

>>> import mysql.connector

To establish a connection to the database we should know the following parameters,

Host= localhost (In general it is same for all)

Database=mysql (You can set as per your wish)

User=root (It is a username)

Password= root@123 (password set by me while installation of MyQL)

>>> mysql.connector.connect( host = 'localhost', database = 'mysql', user = 'root', password = 'root@123')

显示可用的数据库

你可使用“显示资料库”语句，列出系统内所有资料库，以检查你的系统是否有资料库：

>>> my_database = mysql.connector.connect( host = 'localhost', database = 'mysql', user = 'root', password = 'root@123')

>>> cursor = my_database.cursor()

>>> cursor.execute( " show databases " )

>>> for db in cursor:

... print(db)

...

输出：

('bank',)

('information_schema',)

('mysql',)

('performance_schema',)

('sakila',)

('sys',)

('world',)

>>>

创建一个数据库

要在MySQL中创建数据库，我们使用“CREATE DATABASE”语句创建名为“college”的数据库:

>>> my_database = mysql.connector.connect( host = 'localhost', user = 'root', password = 'root@123' )

>>> cursor = my_database.cursor()

>>> cursor.execute( " CREATE DATABASE college " )

>>> for db in cursor:

... print(db)

...

>>> cursor.execute( " show databases " )

>>> for db in cursor:

... print(db)

...

创建表

接下来，我们为“college”数据库创建表。

在为数据库创建表时，必须定义数据库的名称。

创建表的语法是：

create table_name(

column 1 datatype,

column 2 datatype,

column 3 datatype,

…………………………………………,

column n datatype

)

现在创建数据库学院的学生、系和教员表。

>>> my_database = mysql.connector.connect ( host = 'localhost', database = 'college', user = 'root', password = 'root@123' )

>>> cursor = my_database.cursor()

>>>cursor. execute( " CREATE TABLE students ( stud_id varchar(200), stud_name VARCHAR(215), address VARCHAR(215), city char(100)) " )

>>> cursor. execute( " CREATE TABLE department ( dept_id varchar(200), dept_name VARCHAR(215)) " )

>>> cursor.execute( "CREATE TABLE faculty ( faculty_id varchar(200),faculty_name VARCHAR(215) )" )

显示表

要显示这些表，我们必须使用“SHOW TABLES”

下面的代码显示了数据库“college”中存在的所有表：

>>> cursor. execute ( " SHOW TABLES " )

>>> for x in cursor:

... print(x)

...

('department',)

('faculty',)

('students',)

在表中分配主键

主键:它是表或关系中最小的一组属性(列)，可以惟一地标识该表中的元组(行)。

例如，Student (Stud_Roll_No, Stud_Name, Addr)

在学生关系中，只有Stud_Roll_No属性是一个主键，因为每个学生都有一个惟一的id，可以标识表中的学生记录。

>>> my_database = mysql.connector.connect ( host = 'localhost', database = 'college', user = 'root', password = 'root@123' )

>>> cursor = my_database.cursor()

>>>cursor. execute( " CREATE TABLE students2 ( stud_id varchar(200) PRIMARY KEY, stud_name VARCHAR(215), address VARCHAR(215), city char(100)) " )

如果表已经存在，使用ALTER TABLE关键字:

>>> my_database = mysql.connector.connect ( host = 'localhost', database = 'college', user = 'root', password = 'root@123' )

>>> cursor = my_database.cursor()

>>>cursor.execute( " ALTER TABLE student ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY " )

描述创建的表

Desc关键字是用来描述MySQL表。

下面的代码描述了学院数据库的学生表:

>>> cursor.execute("desc students")

>>> for x in cursor:

... print(x)

...

('stud_id', 'varchar(200)', 'YES', '', None, '')

('stud_name', 'varchar(215)', 'YES', '', None, '')

('address', 'varchar(215)', 'YES', '', None, '')

('city', 'char(100)', 'YES', '', None, '')

>>>

示例2

下面的代码描述了college数据库的students2(其中stud_id被称为主键)表:

>>> cursor.execute("desc students2")

>>> for x in cursor:

... print(x)

...

('stud_id', 'varchar(200)', 'NO', 'PRI', None, '')

('stud_name', 'varchar(215)', 'YES', '', None, '')

('address', 'varchar(215)', 'YES', '', None, '')

('city', 'char(100)', 'YES', '', None, '')

>>>

将数据插入表中

要将数据插入表中，使用“insert into”语句。

让我们将数据插入到学院数据库的student表中，

>>> my_database = mysql.connector.connect ( host = 'localhost', database = 'college', user = 'root', password = 'root@123' )

>>> stm = " INSERT INTO students ( stud_id, stud_name, address, city ) VALUES ('101','Nitin Shelke', 'Congress Nagar', 'Amravati' ) "

>>> cursor = my_database.cursor()

>>> cursor.execute(stm)

从表中显示或选择插入的数据

>>> cursor.execute(" select * from students")

>>> for x in cursor:

... print(x)

...

('101', 'Nitin Shelke', 'Congress Nagar', 'Amravati')

另一种方法是使用fetchall()方法。

> > > cursor.fetchall ()

[(' 101 '， ' Nitin Shelke '， ' Congress Nagar '， ' Amravati ')]

感谢你的阅读!如果你有更好的想法，请在评论区评论！

香港服务器

在选择MySQL版本的时候，了解- 下版本的变迁历史是有帮助的。对于怀旧者也可以享受一下过去的好日子里是怎么使用MySQL的。

版本3.23 (2001)

一般认为这个版本的发布是MySQL真正“诞生"的时刻，其开始获得广泛使用。在这个版本，MySQL依然只是一个在平面文件(Flat File)上实现了SQL查询的系统。但一个重要的改进是引人MyISAM代替了老旧而且有诸多限制的ISAM引擎。InnoDB引擎也已经可以使用，但没有包含在默认的二进制发行版中，因为它太新了。所以如果要使用InnoDB,必须手工编译。版本3.23还引入了全文索引和复制。复制是MySQL成为互联网应用的数据库系统的关键特性(killer feature)。

版本4.0 (2003)

支持新的语法，比如UNION和多表DELETE语法。重写了复制，在备库使用了两个线程来实现复制，避免了之前一个线程做所有复制工作的模式下任务切换导致的问题。InnoDB成为标准配备，包括了全部的特性：行级锁、外键等。版本4.0中还引人了查询缓存(自那以后这部分改动不大)，同时还支持通过SSL进行连接。

版本4.1 (2005)

引入了更多新的语法，比如子查询和INSERT ON DUPLICATE KEY UPDATE。 开始支持UTF-8字符集。支持新的二进制协议和prepared语句。

版本5.0 (2006)

这个版本出现了一些“企业级”特性：视图、触发器、存储过程和存储函数。老的ISAM引擎的代码被彻底移除，同时引人了新的Federated等引擎。

版本5.1 (2008)

这是Sun收购MySQL AB以后发布的首个版本，研发时间长达五年。版本5.1引入了分区、基于行的复制，以及plugin API (包括可插拔存储引擎的API)。移除了BerkeyDB引擎，这是MySQL最早的事务存储引擎。其他如Federated引擎也将被放弃。同时Oracle收购的InnoDB oy发布了InnoDB plugin。

版本5.5 (2010)

这是Oracle收购Sun以后发布的首个版本。版本5.5的主要改善集中在性能、扩展性、复制、分区、对微软Windows系统的支持，以及此其他方面。InnoDB成为默认的存储引擎。更多的一些遗留特性和不建议使用的特性被移除。增加了PEFOWANE SOHEA库， 包含了一些可测量的性能指标的增强。增加了复制、认证和审计APl。半同步复制(cincroouo epiatio插件进人实用阶段。Ornale还在2011年发布了商用的认证插件和线程池(tread polig)。InoDB 在架构方面也做了较大的改进，比如多个子缓冲池(buffer pool)。

版本56 (还未发布)

版本56将包含些重大更新。 比如多年来首次对查询优化器进行大规模的改进，更多的插件API (比如全文索引),复制的改进，以及PERFORMANCE SCHEMA库增加了更多的性能指标。InoDB团队也做了大量的改进工作，这些改进在已经发布的里程碑版本和实验室版本中都已经包括。MySQL 5.5主要着重在基础部分的改进和加强，引入了部分新特性。而MySQL 5.6则在MySQL 5.5的基础上提升香港服务器的开发和性能。

版本6.0 (已经取消)

版本6.0的概念有些模糊。最早在版本5.1还在开发的时候就宣布要开发版本6.0。传说中宣布要开发的6.0拥有大量的新特性，包括在线备份、香港服务器层面对所有存储引擎的外键支持，以及子查询的改进和线程池。后来该版本号被取消，Sun 将其改为版本5.4继续开发，最后发布时变成版本5.5。版本6.0中很多特性的代码陆续出现在版本5.5和5.6中。

简单总结一下MySQL的发展史：早期的MySQL是一一种破坏性创新，有诸多限制，并且很多功能只能说是二流的。但是它的特性支持和较低的使用成本，使得其成为快速增长的互联网时代的杀手级应用。在5.x版本的早期，MySQL引入了视图和存储过程等特性，期望成为“企业级”数据库，但并不算成功，成长并非一帆风顺。从事后分析来看，MySOL5.0充满了bug,直到5.0.50以后的版本才算稳定。这种情况在MySQL5.1也依然没有太多改善。版本5.0和5.1的发布都延期了许多时日，而且Sun和Oracle的两次收购也使得社区人土有所担心。但我们认为事情还在按部就班地发展，MySQL5.5可以说是MySOL历史上质量最高的版本。Oracle收购以后帮助MySOL更好地往企业级应用的方向发展，MySQL 5.6也承诺在功能和性能方面将有显著提升。

提到性能，我们可以比较一下在不同时代MySQL的性能测试的数据。在目前的生产环境中4.0及更老的版本已经很少见了，所以这里不打算测试4.1之前的版本。另外如此多的版本如果要做完全等同的测试是比较困难的，具体原因将在后面讨论。我们尝试设计了多个测试方案来尽量保证在不同版本中的基准一一致，井为此做了很多努力。表1-2显示了在香港服务器层面不同并发下的每秒事务数的测试结果。

图1-2: MySQL不同版本的只读基准测试

在解释结果之前，需要先介绍一下测试环境。测试的机器是Cisco UCS C250，两颗6核CPU,每个核支持两个线程，内存为384GB，测试的数据集是2.5GB，所以MySQL的buffer pool设置为4GB。采用SysBench的read-only只读测试进行压测，并采用InnoDB存储引擎，所有的数据都可以放人内存，因此是CPU密集型(CPU-bound) 的测试。每次测试持续60分钟，每10秒获取一次吞吐量的结果，前面900秒用于预热数据，以避免预热时的I/O影响测试结果。

现在来看看结果，有两个很明显的趋势。第一个趋势，采用了InnoDB plugin的版本，在高并发的时候性能明显更好，可以说InnoDB plugin的扩展性更好。这是可以预期的结果，旧的版本在高并发时确实存在问题。第二个趋势，新的版本在单线程的时候性能比旧版本更差。一开始可能无法理解为什么会这样，仔细想想就能明白，这是一个非常简单的只读测试。新版本的sQL语法更复杂，针对复杂查询增加了很多特性和改进，这对于简单查询可能带来了更多的开销。旧版本的代码简单，对于简单的查询反而会更有利。

原计划做一个更复杂的不同并发条件下的读写混合场景的测试(类似TPC-C),但要在不同版本间做到可比较基本是不可能的。一般来说， 新版本在复杂场景时性能有更多的优化，尤其是高并发和大数据集的情况下。

这更多地取决F业务需求而不是技术需求。理想情况下当然是版本越新越好，当然也可以选择等别第一个bug修复版本以后再采用新的大版本。如果应用还没有上限，也可以采用即将发有的新版本，以尽可能地延迟应用上线后的升级操作。

后续港云互联会为大家讲解MySQL的开发模式。

前言

在开发过程中，使用到了MySQL数据库，但是想知道每次对数据库进行了哪些操作，方便对自己的代码进行优化，这时候就需要用到查询日志genral_log。

动态修改配置

mysql > show variables like "%general_log%";

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

| Variable_name | Value |

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

| general_log | ON |

| general_log_file | /tmp/mariadb_general_log.log |

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

可以看到设置到两个变量，一个是开关，一个是general_log的文件保存路径。

开启文件记录

mysql > set global general_log_file='/tmp/mariadb_general_log.log';

mysql > set global general_log=on;

配置文件里面修改

在mysqld的节点加上以下配置

general_log_file=/tmp/mariadb_general_log.log

general_log=on

将日志记录到表

如果不方便查看文件，也可以将日志直接存放到表里面（适合mysql数据库在远程）

mysql > set global log_output='table';

mysql > set global general_log=on;

总结

这个使用本地的开发环境，生产上勿用。

因为删除数据操作失误的缘故，导致丢了3个库（年前电脑硬盘有问题本地备份库都没有了，正好碰上过年和好久都没有出问题，也就没有及时备份），虽然自己之前有遇到误操作数据丢失的情况，知道碰到这事情第一定要保持冷静，不然一旦不冷静操作失误搞不好会造成更多的问题（关键在于很多按照官方的提示去操作不但解决不了问题，反而会产生更多的问题）好在这次物理文件 frm 和 ibd 文件都还有，但是时间比较紧，有些操作考虑不到位，中间走了不少弯路，所以简单说明一下恢复过程，供大家参考：

首先使用 dbsake 工具通过 frm 得到表结构。

工具从下载，安装到使用，简单，利落。

curl -s get.dbsake.net > dbsake

chmod u+x dbsake

./dbsake frmdump {frm-file-path}

得到表结构后重新创建数据库以及对应的数据表。

然后就是通过 ibd 文件恢复数据表中的数据。

1.执行 ALTERTABLE {table_name} DISCARD TABLESPACE; 删除新建的表空间

2.将待恢复的{table_name}.ibd文件copy到目标数据库文件夹下，并修改文件权限

cp {table_name}.ibd {mysql_path}/data/{database_name}/{table_name}.ibd

cd {mysql_path}/data/{database_name}/

chown mysql:mysql <table_name>.ibd

3.执行 ALTERTABLE {table_name} IMPORTTABLESPACE; 导入表空间

最后经过一小时多的各种操作终于是把3个库的数据都恢复过来。

总之最重要的事情还是要保持冷静，每当遇到不可逆的操作哪怕会多花时间去操作，也一定要先备份，避免造成更大的问题。

MySQL是当前流行的数据库引擎之一，具有成本低、速度快、体积小且开放源代码的优点。今天就给大家分享14款MySQL数据库管理工具。

1.MySQLDumper

这款软件的应用，有效解决使用PHP进行大数据库备份和恢复的问题，数百兆的数据库都可以方便的备份恢复，不用担心中间中断的问题。

2.phpMyAdmin

这是一款MySQL维护工具，管理数据库非常方便。不过这款软件也有弱点，那就是不方便大数据库的备份和恢复。

3.MySQL GUI Tools

这是一款图形化管理工具，功能非常强大，但是没有中文界面。

4.Navicat

这款软件，和微软SQLServer的管理器很像，不仅简单，而且实用。它的用户界面图形化，用户使用以及管理起来更加轻松。这款软件不仅支持中文，还提供免费版本。

5.Induction

它属于理解数据关系的开源管理工具，主要功能是探索行/列，查询以及数据可视化。这个工具支持PostgreSQL，MySQL，SQLite，Redis。

6.MySQL ODBC Connector

如果你的系统安装这个程序，可以实现SQLServer、Access和MySQL之间的数据转换。除此以外，这个软件还支持ASP访问MySQL数据库。

7.DbNinja

它属于MySQL数据库管理与开发应用程序，支持触发器、事件、视图、存储过程和外键等最新功能。不仅如此，这款软件还可以导入和备份数据、MySQL对象结构以及管理用户等。

8.Pinba

Pinba 是一种MySQL存储引擎，用于PHP实时监控和数据服务器的MySQL只读接口。它整理并处理通过UDP发送的数据，并以可读的简单报告的形式统计显示多个PHP进程。为了获取下一代更为复杂的报告和统计数据，Pinba提供了原始数据的只读接口。

9.Dbeaver

这款软件免费，可运用于多种不同的引擎，包括MySQL，MSSQL，Oracle、SQLite、Sybase和Firebird等等。由Java编写而成，该应用程序适用于所有主流操作系统(Windows、Mac和Linux)。

10.DB Tools Manager

DBManager是一款功能强大的数据管理应用程序。作为最先进的应用程序，DBManager内置支持MySQL、PostgreSQL、Interbase/Firebird、 SQLite,DBF表、MSAccess,MSSQL服务器,Sybase,Oracle和ODBC数据库引擎等一些新特性。DBManager目前拥有个人和企业两个版本，用户可按需选择使用。

11.Chive

Chive由PHP搭建而成，是一款基于web的开源MySQL管理应用程序。它有一个内置编辑器，当运行复杂的查询或易用的界面时，可用于快速浏览。

12.SQL Lite Manger

这款基于web的开源应用程序，用于管理无服务器、零配置SQL Lite数据库。该程序是用PHP写成，可以控制多个数据库。SQL Lite Manager主要用来查询数据，将MySQL查询转化为兼容SQL Lite数据库，并能创建和编辑触发器。

13.HeidiSQL

HeidiSQL是一种专为web开发人员设计的工具，微软SQL数据库和PostgreSQL。通过这个工具，可以浏览和编辑数据、创建和编辑表格、视图、过程、触发器和日志安排等事件。

14.Sequel Pro

Sequel Pro是基于MySQL数据库的一种Mac数据库管理应用程序，这个软件非常好用。用户通过Sequel Pro，可以在本地和远程服务器上直接访问MySQL数据库。

MySQL运行包括两部分，一部分是服务器端程序mysqld，另外一部分则是客户端程序mysql。只有mysqld启动了，mysql客户端才能与之连接。mysqld的启动方式有4种，同样mysql的连接方式也有4种。

mysqld的启动方式

方式1：mysqld

mysqld是一个可执行命令，也是服务器端程序，启动这个程序就相当于启动了MySQL服务器端的进程。但这个命令不常用，常用mysqld_safe命令。

命令格式：mysqld [OPTIONS]如：mysqld --defaults-file=/etc/my.cnf查看帮助：mysqld --verbose --help方式2：mysqld_safe

mysqldsafe是一个启动脚本，间接引用mysqld。在启动mysqldsafe脚本时，在启动MySQL服务器进程时，同时会启动一个守护进程，作用是监控mysqld，如mysqld服务挂了后，会立即重启一个mysqld服务。另外，mysqld_safe启动方式也会把运行过程的报错日志和其它一些诊断信息输出到某一个文件中，这样方便我们排查解决问题。这个启动方式是最常用的方式。

命令格式：mysqld_safe [OPTIONS]&如：mysqld_safe --defaults-file=/etc/my.cnf &查看帮助：mysqld_safe --help方式3：mysqld_multi

如需要在一台主机运行多个MySQL实例，则就可以使用mysqld_multi来对不同的mysqld服务进行管理，这个启动方式对MySQL集群很有帮助。

命令格式：mysqld_multi [OPTIONS]{start|reload|stop|report}[GNR,GNR,GNR...]查看帮助：mysqld_multi --help方式4：mysql.server

mysql.server也是一个启动脚本，它会间接的调用mysqld_safe，在调用mysql.server时在后边指定start参数就可以启动服务器程序了。这个启动方式一般是通过RPM包安装的MySQL产生的。mysql.server 文件其实是一个链接文件，它的实际文件是 ../support-files/mysql.server

命令格式：mysql.server start|restart|stop|statusmysql的连接方式

方式1：TCP/IP套接字

在类Unix和Windows操作系统下都可以使用，TCP/IP套接字是一个网络协议，通过MySQL服务器启动时监听一个端口，客户端启动连接时指明MySQL服务器的主机IP+端口即可实现连接。此种通信方式，MySQL服务器和客户端可以部署在不同主机上，也是最常用的一种连接方式。

mysql -h主机IP -P端口-u用户-p如：mysql -h127.0.0.1-P3306 -uroot -p

方式2：Unix套接字

仅限类Unix操作系统下使用，Unix套接字不是网络协议，只能在MySQL服务器和客户端在同一个主机上才能使用。如果我们在启动客户端程序的时候指定的主机名为localhost，或者指定了--protocol=socket的启动参数，那服务器程序和客户端程序之间就可以通过Unix域套接字文件来进行通信了。MySQL服务器程序默认监听的Unix域套接字文件路径为/tmp/mysql.sock，客户端程序也默认连接到这个Unix域套接字文件。

1. mysql -uroot -p//默认通过socket通信 缺省localhost2. mysql -hlocalhost -uroot -p3. mysql -S socket文件路径-uroot -p如：mysql -S /tmp/mysqld.sock -uroot -p

查看socket文件位置：show variables like 'socket';

方式3：命名管道

仅限类Windows操作系统下使用，只能在MySQL服务器和客户端在同一个主机上才能使用。需要在启动服务器程序的命令中加上--enable-named-pipe参数，然后在启动客户端程序的命令中加入--pipe或者--protocol=pipe参数。

–enable-named-pipe=on/off;方式4：共享内存

仅限类Windows操作系统下使用，只能在MySQL服务器和客户端在同一个主机上才能使用。需要在启动服务器程序的命令中加上--shared-memory参数，在成功启动服务器后，共享内存便成为本地客户端程序的默认连接方式，不过我们也可以在启动客户端程序的命令中加入--protocol=memory参数来显式的指定使用共享内存进行通信。

–shared-memory=on/off；

1：什么是索引？

所以是帮助mysql高效查找数据的数据结构

2：什么是Explain？1：解释

使用explain关键字可以模拟优化器执行sql查询语句，从而知道Mysql是如何处理你的sql语句，分析你的查询语句或者是表机构的性能瓶颈

2：使用方法

explain+sql语句

3：返回结果含义1：id介绍

id相同时，加载顺序由上到下

id不同时，如果有子查询，id的序号会递增，id值越大优先级越高，越先被执行

id相同不同时（混合），id相同的可以分为一组，顺序由上到下执行，不同的按照id值越大优先级越高，越先被执行

2：select_type介绍

SIMPLE：简单的select查询，查询中不包含子查询或者UNION

PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记该类型

SUBQUERY：在select或者where列表中包含了子查询

DERIVED：在from列表中包含的子查询被标记为DERIVED（衍生），mysql会递归执行这些子查询，把结果放在临时表里面

UNION：若第二个select出现在union之后，则被标记为union，若union出现在from子句的子查询中，外层select将被标记为derived

UNION RESULT：从union表获取结果的select

3：table介绍

关于执行的哪张表的记录

4：type介绍（下面查询类型是从最好到最次依次排序）

system：表只有一行表，是const类型的特里，平时不会出现，这个可以忽略不记

const ：表示通过索引一次找到，const用于比较primary key或者union索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将该查询转为一个常量

eq_ref：唯一性索引扫描，对越每个索引键，每个表中只有一条记录与之匹配。常见于主键或者唯一索引扫描

ref：非唯一性索引扫描，返回匹配某个单独值的所有行

range：之检索给定范围的行，使用一个索引来选择行

index：与ALL的区别是index只遍历索引树

ALL：将全表扫描一遍

5：possible_key介绍

显示可能应用到这张表的索引，一个或者多个。查询涉及到的字段若存在索引，该索引将被列出。（但不一定被查询实际使用）

6：key介绍

实际使用的索引，如果为null，则没有使用索引。

7：key_len介绍

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

8：ref介绍

表示索引哪一列被使用，如果可能的话，是一个常数。哪些列或者常量被用于查找索引列上的值。

9：rows介绍

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

10：Extra介绍

*using filesort：使用内部文件排序

*using temporary：使用临时表

*using index：使用索引排序（最好能用到）

using where：使用where条件

using join buffer：使用了连接缓存

impossible where：不合理的where条件

select tables optimized away：操作已经优化到不能再优化了（MySQL根本没有遍历表或索引就返回数据了）

distinct：不使用排序来做分组

第一节 SQL语句简介

SQL语言

SQL(Structured Query Language)是用于访问和处理数据库的标准计算机语言。使用 SQL 访问和处理数据系统中的数据，这类数据库包括：Oracle,mysql,Sybase, SQL Server, DB2, Access 等等。

基本规范

SQL 对大小写不敏感，一般数据库名称、表名称、字段名称全部小写

MySQL要求在每条 SQL 命令的末端使用分号（MS Access 和 SQL Server 2000，则不必在每条 SQL 语句之后使用分号）。

注释

mysql> select 1+1; # 这个注释直到该行结束mysql> select 1+1; -- 这个注释直到该行结束mysql> select 1 /* 这是一个在行中间的注释 */ + 1;mysql> select 1+/*这是一个多行注释的形式*/

第二节 MySQL基本数据类型

字段类型

数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储方式，代表了不同的信息类型。不同的数据库，数据类型有所不同，MySQL数据库有以下几种数据类型：

字符串型

类型 字节 大小 说明

char 1 0-255字符 (2^8) 定长字符串

varchar 2 0-65 535字符 (2^16) 变长字符串

tinytext 1 0-255字符 (2^8) 短文本（与char存储形式不同）

text 2 0-65 535字符(2^16) 文本

mediumtext 3 0-16 777 215字符 (2^24) 中等长度文本

longtext 4 0-4 294 967 295字符 (2^32) 极大文本

注意：char和varchar需要指定长度，例如：char（10）

整数型

类型 字节 范围（有符号） 范围（无符号） 用途

tinyint 1 (-128，127) (0，255) 很小整数值

smallint 2 (-32 768，32 767) (0，65 535) 小整数值

mediumint 3 (-8 388 608，8 388 607) (0，16 777 215) 中整数值

int或integer 4 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 整数值

bigint 8 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 很大的整数值

很多人喜欢在定义数据时，这样写：

create table tbl_name(age int(10));

int后面()中的数字，不代表占用空间容量。而代表最小显示位数。这个东西基本没有意义，除非你对字段指定zerofill。mysql会自动分配长度：int(11)、tinyint(4)、smallint(6)、mediumint(9)、bigint(20)。所以，建议在使用时，就用这些默认的显示长度就可以了。不用再去自己填长度（比如：int(10)、tinyint(1)之类的基本没用）。

浮点型

类型 字节 范围 用途

float(M,D) 4 23bit（约6~7位 10进制数字） 单精度浮点数<br />值绝对能保证精度为6~7位有效数字

double(M,D) 8 52bit（约15~16位 10进制数字） 双精度浮点数值<br />精度为15~16位有效数字

decimal(M,D) M+2 依赖于M和D的值 定点型

M（精度），代表总长度（整数位和小数位）限制

D（标度），代表小数位的长度限制。

M必需大于等于D

数字的修饰符 功能 说明

unsigned 无符号 非负数

zerofill 前导 0 整形前加0（自动添加unsigned）

日期型

类型 字节 范围 格式 用途

date 3 1000-01-01---9999-12-31 YYYY-MM-DD 日期值

time 3 -838:59:59---838:59:59 HH:MM:SS 时间值或持续时间

year 1 1901---2155 YYYY 年份值

datetime 8 1000-01-01 00:00:00---9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值

timestamp 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 时间戳，混合日期和时间值

列举与枚举

名称 字节 说明

set 1、2、3、4或8 列举：可以取SET列表中的一个或多个元素（多选）

enum 1或2 枚举：可以取ENUM列表中的一个元素（单选）

create table students(id tinyint, # 微小整型 name varchar(10), #变长字符 sex enum('m','w'), #单选 birthday date, # 日期型 tel char(11), # 定长字符 city char(1), # 城市 hobby set('1','2','3','4'), #多选 introduce text # 个人介绍 );

字段属性

属性 功能 说明

not null 非空 必须有值，不允许为null

default 默认值 当插入记录时没有赋值，自动赋予默认值（允许为null）

primary key 主键 惟一标识一行数据的字段（主键自动为not null）

auto_increment 自动增量 不能单独使用，必须与primary key 一起定义

unique(unique key) 唯一 记录不能重复（一张表可以有多个unique，允许为null）

第三节 数据的 增、删、改

增删改查（简称：CURD）

增

# 方法1：指定字段

insert into students(name,age) values('张三','20');

# 方法2： 省略字段名，字段位要一一对应，不能跳过（auto_increment字段，可以使用null或 default）

insert into students values(null,'张三','20');

# 方法3：批量增加数据

insert into students(name,age) values('张三','20')，('李四','21')，('王五','22') ……

删

# 用delete删除记录，一定要加where条件，否则表数据全部删除！！

delete from 表名 where xx=xxx;

# 用truncate删除记录，不能加where条件，直接删除全部记录，id索引重新从1开始

truncate table 表名;

改

#单条修改

update 表名 set xx=xx,xxx=xx where xxx=xxx and xxx=xxx;

#多条修改

update studentsset name = case id # id字段 when 1 then 'zhangsan' when 2 then 'lisi' when 3 then 'wangwu' when 4 then 'zhaoliu' end, city = case id when 1 then '2' when 2 then '4' when 3 then '1' when 4 then '2' endwhere id in (1,2,3,4);