mysql

一、工具环境

1.mysql连接命令

./mysql -h<IP地址> -P<端口> -u<用户名> -p<密码> [数据库名] [-e <执行语句>]#查看mysql是否已经启动ps aux | grep mysqld

2.mysqldump导出数据

./mysqldump -h<IP地址> -P<端口> -u<用户名> -p<密码> <数据库名> <表名1> <表名2> > <备份文件保存地址>-w/--where "条件语句"--default-character-set=字符集   #（默认是latin1）--hex-blob   #（如果有二进制数据必须使用本选项来以十六进制格式导出二级制字符串字段）--lock-all-tables   #（可以保证数据的一致性）--lock-tables   #（只锁定当前导出的数据表）--single-transaction   #（不锁定表格，尤用于当连接用户权限较低时）-t/--no-create-info   #（只导出数据，不添加CREAT TABLE语句）-d/--no-data   #（不导出数据，只导出数据库表结构）-S, --socket name   #（使用指定的socket文件）--defaults-extra-file   #（使用配置文件来输入用户名密码端口等信息）

3.mysqladmin管理命令

用于对数据库进行管理和维护.可用它执行的命令有:

create   -- 创建数据库drop   -- 删除数据库shutdown   -- 停止MySQL服务password   -- 修改MySQL数据库口令status   -- 显示服务器的基本状态消息ping   -- 检测MySQL是否在运行refresh   -- 关闭所有表并关闭和重新打开日志文件

二、mysql命令

1.MySQL对标准SQL语句的扩展

use [数据库名]   -- 使用某个数据库desc [表名]   -- 显示表的内容CHANGE <旧列名> <新列名> <数据类型>   -- 用于ALTER TABLE子句，是对MODIFY的扩展，可以修改列名FIRST|[AFTER col_name]   -- 用于ALTER TABLE子句，放于SQL末尾，指定所修改列的位置LIMIT [offset_start,]row_count   -- 一般用于含有ORDER BY的查询子句，选择从第offset_start行开始的row_count行数据显示。offset_start不指定则为0。

2.show命令

//数据库
a、show databases;                                             显示所有的数据库。
b、show databases like "bill%";                           显示名称以bill开头的所有数据库。
c、show create database dbname;                      将看到创建这个数据库的sql语句。
 
//数据库中的表
a、show tables;或show tables from database_name;                                                     显示一个数据库中所有的表。
b、show tables likes "bill%";                                 显示名称以bill开头的所有数据表。
c、show create table tablename;                           将看到创建这个表的sql语句。
d、show columns/fields from table_name from database_name; 或show columns/field from database_name.table_name;                           显示表中列名称。经常使用简写desc table_name
      show full columns/fields from table_name from database_name;                            显示所有列的名称和信息，包括注释等列。
e、show index from table_name;                            显示表的索引。、
f、show table STATUS                                        查看数据库中当前表的状态，包含表的创建时间，更新时间等。
 
//查看某个用户所具有的权限
a、show grants for username                                查看用户username所具有的所有权限。
b、show grants for username@192.162.0.55                   查看用户username@{ip}所具有的所有权限。
 
//服务器系统状态
a、show processlist                                         查看服务器上运行的所有线程，每个线程正在做什么操作。::详见附录
b、show status;                                          显示一些系统特定资源的信息，例如，正在运行的线程数量。::详见附录
c、show privileges;                                           显示服务器所支持的不同权限。
d、show variables;                                        显示系统变量的名称和值。
e、show engies;或show storage engines;                                      显示安装以后可用的存储引擎和默认引擎。
f、show innodb status;                                        显示innoDB存储引擎的状态。
g、show logs;                                        显示BDB存储引擎的日志。
h、show warnings;                                        显示最后一个执行的语句所产生的错误、警告和通知。
i、show errors;                                      只显示最后一个执行语句所产生的错误。
j、show slave status;                                      用于提供有关从属服务器线程的关键参数的信息::详见附录

3.mysql开启自动补全的方法：

方法一、
vi {mysql主目录}/etc/my.cnf
[mysql] 
#no-auto-rehash 
auto-rehash         #添加auto-rehash
方法二、
在mysql启动时加参数auto-rehash
mysqld_safe --user=mysql --auto-rehash &
方法三：
当mysql服务器已经启动后，可以在USE XXX后，使用rehash 或者\# 在mysql 客户端上执行，然后就可以自动补全表明和列名了。

4.测试SQL性能

　　explain SQL语句性能测试返回值的具体含义如下：
　　mysql>explain select * from table;
　　+----+-------------+-------+------+---------------+------+---------+------+------+-------+
　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
　　+----+-------------+-------+------+---------------+------+---------+------+------+-------+
　　table #显示该语句涉及的表
　　type #这列很重要，显示了连接使用了哪种类别,有无使用索引，反映语句的质量。
　　possible_keys #列指出MySQL能使用哪个索引在该表中找到行
　　key #显示MySQL实际使用的键（索引）。如果没有选择索引，键是NULL。
　　key_len #显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好
　　ref #显示使用哪个列或常数与key一起从表中选择行。
　　rows #显示MySQL认为它执行查询时必须检查的行数。
　　extra #包含MySQL解决查询的详细信息。
　　其中：Explain的type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
　　system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL（优-->差）
　　一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题

mysqldumpslow
        用来对MySQL进行慢查询分析，发现MySQL的慢查询或者没有利用索引的查询；
参见：http://www.ccvita.com/410.html

5. binlog配置和使用

1. binlog配置

在my.cnf中配置如下项：

log_bin设置此参数表示启用binlog功能，并指定路径名称log_bin_index设置此参数是指定二进制索引文件的路径与名称expire_logs_days设置自动删除多少天前的日志binlog_do_db此参数表示只记录指定数据库的二进制日志binlog_ignore_db此参数表示不记录指定的数据库的二进制日志binlog_format此参数二进制日志的格式，分为：STATEMENT（默认值），ROW，MIXED三种max_binlog_cache_size此参数表示binlog使用的内存最大的尺寸binlog_cache_size此参数表示binlog使用的内存大小max_binlog_sizeBinlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，因为要在事务结束后判定是否超过大小。sync_binlog这个参数直接影响mysql的性能和完整性，默认的设置是sync_binlog=0binlog_cache_use使用二进制日志缓存的事务数量binlog_cache_disk_use使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
注释：

1. binlog_do_db和binlog_ignore_db的生效有坑，参见：MySQL参数binlog-do-db对binlogs写入的影响

2. binlog_format的详细说明，参见：Mysql Binlog三种格式介绍及分析

3. sync_binlog：sync_binlog=0，当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。

2. 查看binlog和使用binlog

登陆到mysql查看binlog日志：

#只查看第一个binlog文件的内容show binlog events;#查看指定的文件的从指定的Pos开始的内容，并只显示第100-500行；limit用法和select中相同show binlog events in 'mysql-bin.000002' from 1000 limit 100,400;#查看当前正在写入的binlog文件及其结尾的Positionshow master status;#获取binlog文件列表show binary logs;

用mysqlbinlog工具查看binlog日志：

#基于开始/结束时间，只提取指定数据库的日志mysqlbinlog --start-datetime='2016-09-08 00:00:00' --stop-datetime='2016-09-08 01:00:00' -d 库名 mysql-bin.000002#基于pos值mysqlbinlog --start-postion=100 --stop-position=1000 mysql-bin.000002#远程查看mysqlbinlog -u username -p password -hl-db1.dba.beta.cn6.qunar.com -P3306 --read-from-remote-server --start-datetime='2016-09-08 00:00:00' --stop-datetime='2016-09-08 01:00:00' mysql-bin.000002 > my.binlog#提取指定开始时间的binlog并输出到日志文件mysqlbinlog --set-charset=utf8 --start-datetime="2016-09-08 00:00:00" mysql-bin.000002 > my.sql#提取指定position位置的binlog日志导入数据库mysqlbinlog --start-position="100" mysql-bin.000002 | mysql -uroot -p

3. 删除binlog

#删除master的所有binlog，包括当前正在使用的binlogreset master;#删除slave的中继日志reset slave;#删除指定日期以前的日志索引中binlog日志文件purge master logs before '2016-09-08 17:20:00';#删除指定日志文件之前的日志文件，使得指定的日志文件变为第1个日志文件purge master logs to 'mysql-bin.000002';

4. 生成新binlog文件

1. Mysql会在服务器重启时生成一个新的日志文件，文件序号递增；服务器会把日志记入这个新生成的日志文件

2. 如果日志文件超过max_binlog_size（默认值1G）系统变量配置的上限时，也会生成新的日志文件

3. 日志被刷新时，新生成一个日志文件。在mysql中执行刷新命令为：“flush logs;”

附录A. 标准sql语句

1.类型

CHAR 字符
VARCHAR 可变长字符 
BIT 位 
INT/INTEGER 整型 
NUMERIC 数字 
REAL 浮点数 
DOUBLE 浮点数 
DATE 日期 
TIME 时间 
TIMESTAMP 时间戳,日期+时间
INTERVAL 间隔

2.核心动词,集函数和约束关键词

数据查询:SELECT
数据定义:CREATE,DROP,ALTER
数据操作:INSERT,UPDATE,DELETE
数据控制:GRANT,REVOKE


COUNT|SUM|AVG|MAX|MIN([DISTINCT] <列名>)


NOT NULL 非空  
PRIMARY KEY 主键
CHECK 约束条件
UNIQUE 唯一
DEFAULT 默认值
FOREIGN KEY 外键   
REFERENCES 索引  
ON DELETE 删除时动作  
ON UPDATE 更新时动作

3.语句

(1)CREATE TABLE <表名>(<列名> <数据类型> [列级完整性约束条件], 
                       <列名> <数据类型> [列级完整性约束条件],
                        ..., 
                       [表级完整性约束条件]);
(2)ALTER TABLE <表名>
   [ADD <新列名> <数据类型> [完整性约束条件]]
   [DROP <完整性约束名>]
   [MODIFY <列名> <数据类型>];
(3)DROP TABLE <表名>;
(4)CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
   ON <表名> (<列名> [ASC|DESC], <列名> [ASC|DESC], ...);
(5)DROP INDEX <索引名>;
(6)CREATE VIEW <视图名>[(<列名>, <列名>, ...)]
   AS <子查询>
   [WITH CHECK OPTION]
(7)DROP VIEW <视图名>;
(8)INSERT INTO <表名> [(<列名>, <列名>, ...)]
               VALUES (<值>, <值>, ...)|<子查询>;
(9)UPDATE <表名>
   SET <列名>=<值>, <列名>=<值>, ...
   [WHERE <条件>];
(10)DELETE FROM <表名>
    [WHERE <条件>];
(11)SELECT [DISTINCT] <目标列表达式> [别名], <目标列表达式> [别名], ...
    FROM <表名> [别名], <表名> [别名], ...
    [WHERE <条件>]
    [GROUP BY <列名> [HAVING <条件>]]
    [ORDER BY <列名> [ASC|DESC]];
    其中,条件可以是如下表达式:
    <列名> =|<>|<|>|<=|>= ALL|SOME|ANY (<值>, <值>, ...)|(<子查询>)
    <列名> [NOT] BETWEEN <列名>|<值>|<子查询> AND <列名>|<值>|<子查询>
    <列名> [NOT] IN (<值>, <值>, ...)|(<子查询>)
    [NOT] EXISTS <子查询>
(12)GRANT <权限>,<权限>, ...
    [ON <对象类型> <对象名>]
    TO <用户>, <用户>, ...
    [WITH GRANT OPTION];
(13)REVOKE <权限>,<权限>, ...
    [ON <对象类型> <对象名>]
    FROM <用户>, <用户>, ...

4.说明

在HAVING子句中的条件是针对整个组而言，因此通常使用聚集函数。在进行分组之后，在投影列表中只可直接引用那些出现在分组语句中的属性，对于其他属性，只能出现在聚集函数中。同样的规则也适用于HAVING子句。
子查询除不能使用ORDER BY子句外，与普通的查询语句并无不同。子查询中可以引用来自于父查询的属性，但反过来，父查询中不可以引用子查询中的属性。
视图只是存储了一个查询语句，故每次显示时总是重新查询而更新结果。只有行列视图才可以被更新。视图作用：以不同视角看数据；方便逻辑重组；便于机密数据的保护。

附录B. SHOW PROCESSLIST命令详解

SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限或者process权限，您可以看到所有人的线程，包括密码。否则，您只能看到您自己的线程（也就是，与您正在使用的MySQL账户相关的线程）。
show processlist;只列出前100条，如果想全列出请使用show full processlist; 


这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：（还有其他的状态没在下表中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。）
Checking table
　正在检查数据表（这是自动的）。
Closing tables
　正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
　复制从服务器正在连接主服务器。
Copying to tmp table on disk
　由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table
　正在创建临时表以存放部分查询结果。
deleting from main table
　服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables
　服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables
　正在执行FLUSH TABLES，等待其他线程关闭数据表。
Killed
　发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked
　被其他查询锁住了。
Sending data
　正在处理SELECT查询的记录，同时正在把结果发送给客户端。
Sorting for group
　正在为GROUP BY做排序。
　Sorting for order
　正在为ORDER BY做排序。
Opening tables
　这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates
　正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table
　获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting
　修复指令正在排序以创建索引。
Repair with keycache
　修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update
　正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
Sleeping
　正在等待客户端发送新请求.
System lock
　正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
Upgrading lock
　INSERT DELAYED正在尝试取得一个锁表以插入新记录。
Updating
　正在搜索匹配的记录，并且修改它们。
User Lock
　正在等待GET_LOCK()。
Waiting for tables
　该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert
　INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。
　大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。

附录C. SHOW STATUS命令详解

SHOW STATUS提供服务器的状态信息(象mysqladmin extended-status一样)。


字段的意义如下表所示：
Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
Connections 试图连接MySQL服务器的次数。
Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
Delayed_writes 用INSERT DELAYED写入的行数。
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands 执行FLUSH命令的次数。
Handler_delete 请求从一张表中删除行的次数。
Handler_read_first 请求读入表中第一行的次数。
Handler_read_key 请求数字基于键读行。
Handler_read_next 请求读入基于一个键的一行的次数。
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
Handler_update 请求更新表中一行的次数。
Handler_write 请求向表中插入一行的次数。
Key_blocks_used 用于关键字缓存的块的数量。
Key_read_requests 请求从缓存读入一个键值的次数。
Key_reads 从磁盘物理读入一个键值的次数。
Key_write_requests 请求将一个关键字块写入缓存次数。
Key_writes 将一个键值块物理写入磁盘的次数。
Max_used_connections 同时使用的连接的最大数目。
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
Open_tables 打开表的数量。
Open_files 打开文件的数量。
Open_streams 打开流的数量(主要用于日志记载）
Opened_tables 已经打开的表的数量。
Questions 发往服务器的查询的数量。
Slow_queries 要花超过long_query_time时间的查询数量。
Threads_connected 当前打开的连接的数量。
Threads_running 不在睡眠的线程数量。
Uptime 服务器工作了多少秒。

附录D. SHOW SLAVE STATUS命令详解

字段的意义如下列表所示：   
Slave_IO_State   
SHOW PROCESSLIST输出的State字段的拷贝。SHOW PROCESSLIST用于从属I/O线程。如果线程正在试图连接到主服务器，正在等待来自主服务器的时间或正在连接到主服务器等，本语句会通知您   
Master_User   
被用于连接主服务器的当前用户。   
Master_Port   
当前的主服务器接口。   
Connect_Retry   
–master-connect-retry选项的当前值   
Master_Log_File   
I/O线程当前正在读取的主服务器二进制日志文件的名称。   
Read_Master_Log_Pos   
在当前的主服务器二进制日志中，I/O线程已经读取的位置。   
Relay_Log_File   
SQL线程当前正在读取和执行的中继日志文件的名称。   
Relay_Log_Pos   
在当前的中继日志中，SQL线程已读取和执行的位置。   
Relay_Master_Log_File   
由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称。   
Slave_IO_Running   
I/O线程是否被启动并成功地连接到主服务器上。   
Slave_SQL_Running   
SQL线程是否被启动。   
Replicate_Do_DB,Replicate_Ignore_DB   
使用–replicate-do-db和–replicate-ignore-db选项指定的数据库清单。   
Replicate_Do_Table,Replicate_Ignore_Table,Replicate_Wild_Do_Table,Replicate_Wild_Ignore_Table   
使用–replicate-do-table,–replicate-ignore-table,–replicate-wild-do-table和–replicate-wild-ignore_table选项指定的表清单。   
Last_Errno,Last_Error   
被多数最近被执行的查询返回的错误数量和错误消息。错误数量为0并且消息为空字符串意味着“没有错误”。如果Last_Error值不是空值，它也会在从属服务器的错误日志中作为消息显示。   
举例说明：   
Last_Errno: 1051   
Last_Error: error ‘Unknown table ‘z” on query ‘drop table z’   
该消息指示，表z曾经存在于在主服务器中并已被取消了，但是它没有在从属服务器中存在过，因此对于从属服务器，DROP TABLE失败。（举例说明，在设置复制时，如果您忘记了把此表拷贝到从属服务器中，则这有可能发生。）   
Skip_Counter   
最近被使用的用于SQL_SLAVE_SKIP_COUNTER的值。   
Exec_Master_Log_Pos   
来自主服务器的二进制日志的由SQL线程执行的上一个时间的位置（Relay_Master_Log_File）。在主服务器的二进制日志中的(Relay_Master_Log_File, Exec_Master_Log_Pos)对应于在中继日志中的(Relay_Log_File,Relay_Log_Pos)。   
Relay_Log_Space   
所有原有的中继日志结合起来的总大小。   
Until_Condition,Until_Log_File,Until_Log_Pos   
在START SLAVE语句的UNTIL子句中指定的值。   
Until_Condition具有以下值：   
o 如果没有指定UNTIL子句，则没有值   
o 如果从属服务器正在读取，直到达到主服务器的二进制日志的给定位置为止，则值为Master   
o 如果从属服务器正在读取，直到达到其中继日志的给定位置为止，则值为Relay   
Until_Log_File和Until_Log_Pos用于指示日志文件名和位置值。日志文件名和位置值定义了SQL线程在哪个点中止执行。   
Master_SSL_Allowed,Master_SSL_CA_File,Master_SSL_CA_Path,Master_SSL_Cert,Master_SSL_Cipher,Master_SSL_Key   
这些字段显示了被从属服务器使用的参数。这些参数用于连接主服务器。   
Master_SSL_Allowed具有以下值：   
o 如果允许对主服务器进行SSL连接，则值为Yes   
o 如果不允许对主服务器进行SSL连接，则值为No   
o 如果允许SSL连接，但是从属服务器没有让SSL支持被启用，则值为Ignored。   
与SSL有关的字段的值对应于–master-ca,–master-capath,–master-cert,–master-cipher和–master-key选项的值。   
Seconds_Behind_Master   
本字段是从属服务器“落后”多少的一个指示。当从属SQL线程正在运行时（处理更新），本字段为在主服务器上由此线程执行的最近的一个事件的时间标记开始，已经过的秒数。当此线程被从属服务器I/O线程赶上，并进入闲置状态，等待来自I/O线程的更多的事件时，本字段为零。总之，本字段测量从属服务器SQL线程和从属服务器I/O线程之间的时间差距，单位以秒计。   
如果主服务器和从属服务器之间的网络连接较快，则从属服务器I/O线程会非常接近主服务器，所以本字段能够十分近似地指示，从属服务器SQL线程比主服务器落后多少。如果网络较慢，则这种指示不准确；从属SQL线程经常会赶上读取速度较慢地从属服务器I/O线程，因此，Seconds_Behind_Master经常显示值为0。即使I/O线程落后于主服务器时，也是如此。换句话说，本列只对速度快的网络有用。   
即使主服务器和从属服务器不具有相同的时钟，时间差计算也会起作用（当从属服务器I/O线程启动时，计算时间差。并假定从此时以后，时间差保持不变）。如果从属SQL线程不运行，或者如果从属服务器I/O线程不运行或未与主服务器连接，则 Seconds_Behind_Master为NULL（意义为“未知”）。举例说明，如果在重新连接之前，从属服务器I/O线程休眠了master- connect-retry秒，则显示NULL，因为从属服务器不知道主服务器正在做什么，也不能有把握地说落后多少  

附录E. MySQL开发规范

1.命名规范

(1)库名、表名、字段名必须使用小写字母,并采用下划线分割。
(2)库名、表名、字段名禁止超过32个字符。
(3)库名、表名、字段名必须见名知意。命名与业务、产品线等相关联。
(4)库名、表名、字段名禁止使用MySQL保留字。（保留字列表见官方网站）
(5)临时库、表名必须以tmp为前缀,并以日期为后缀。例如 tmp_test01_20130704。
(6)备份库、表必须以bak为前缀,并以日期为后缀。例如 bak_test01_20130704。

2.基础规范

(1)使用INNODB存储引擎。
(2)表字符集使用UTF8,必要时可申请使用UTF8MB4字符集。
(3)所有表都需要添加注释;除主键外的其他字段都需要增加注释。推荐采用英文标点,避免出现乱码。
(4)禁止在数据库中存储图片、文件等大数据。
(5)每张表数据量建议控制在5000W以内。
(6)禁止在线上做数据库压力测试。
(7)禁止从测试、开发环境直连数据库。

3.库表设计

(1)禁止使用分区表。
(2)将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据。
(3)推荐使用HASH进行散表,表名后缀使用十进制数,数字必须从0开始。
(4)按日期时间分表需符合YYYY[MM][DD][HH]格式,例如2013071601。年份必须用4位数字表示。例如按日散表user_20110209、 按月散表user_201102。
(5)采用合适的分库分表策略。例如千库十表、十库百表等。

4.字段设计

(1)建议使用UNSIGNED存储非负数值。
(2)建议使用INT UNSIGNED存储IPV4。
(3)用DECIMAL代替FLOAT和DOUBLE存储精确浮点数。例如与货币、金融相关的数据。
(4)INT类型固定占用4字节存储,例如INT(4)仅代表显示字符宽度为4位,不代表存储长度。
(5)区分使用TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT数据类型。例如取值范围为0-80时,使用TINYINT UNSIGNED。
(6)强烈建议使用TINYINT来代替ENUM类型。
(7)尽可能不使用TEXT、BLOB类型。
(8)禁止在数据库中存储明文密码。
(9)使用VARBINARY存储大小写敏感的变长字符串或二进制内容。
(10)使用尽可能小的VARCHAR字段。VARCHAR(N)中的N表示字符数而非字节数。
(11)区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。 存储时间(精确到秒)建议使用TIMESTAMP类型。
(12)所有字段均定义为NOT NULL。
 

5.索引规范

(1)单张表中索引数量不超过5个。
(2)单个索引中的字段数不超过5个。
(3)索引名必须全部使用小写。
(4)非唯一索引按照“idx_字段名称[_字段名称]”进用行命名。例如idx_age_name。
(5)唯一索引按照“uniq_字段名称[_字段名称]”进用行命名。例如uniq_age_name。
(6)组合索引建议包含所有字段名,过长的字段名可以采用缩写形式。例如idx_age_name_add。
(7)表必须有主键,推荐使用UNSIGNED自增列作为主键。
(8)唯一键由3个以下字段组成,并且字段都是整形时,可使用唯一键作为主键。其他情况下,建议使用自增列或发号器作主键。
(9)禁止冗余索引。
(10)禁止重复索引。
(11)禁止使用外键。
(12)联表查询时,JOIN列的数据类型必须相同,并且要建立索引。
(13)不在低基数列上建立索引,例如“性别”。
(14)选择区分度大的列建立索引。组合索引中,区分度大的字段放在最前。
(15)对字符串使用前缀索引,前缀索引长度不超过8个字符。
(16)不对过长的VARCHAR字段建立索引。建议优先考虑前缀索引,或添加CRC32或MD5伪列并建立索引。
(17)合理创建联合索引,(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
(18)合理使用覆盖索引减少IO,避免排序。

6.SQL设计

(1)使用prepared statement,可以提升性能并避免SQL注入。
(2)使用IN代替OR。SQL语句中IN包含的值不应过多,应少于1000个。
(3)禁止隐式转换。数值类型禁止加引号;字符串类型必须加引号。
(4)避免使用JOIN和子查询。必要时推荐用JOIN代替子查询。
(5)避免在MySQL中进行数学运算和函数运算。
(6)减少与数据库交互次数,尽量采用批量SQL语句。
(7)拆分复杂SQL为多个小SQL,避免大事务。
(8)获取大量数据时,建议分批次获取数据,每次获取数据少于2000条,结果集应小于1M。
(9)使用UNION ALL代替UNION。
(10)统计行数使用COUNT(*)。
(11)SELECT只获取必要的字段,禁止使用SELECT *。
(12)SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。
13)INSERT语句必须指定字段列表,禁止使用 INSERT INTO TABLE()。
(14)禁止单条SQL语句同时更新多个表。
(15)避免使用存储过程、触发器、视图、自定义函数等。
(16)建议使用合理的分页方式以提高分页效率。
(17)禁止在从库上执行后台管理和统计类功能的QUERY,必要时申请统计类从库。
(18)程序应有捕获SQL异常的处理机制,必要时通过rollback显式回滚。
(19)重要SQL必须被索引:update、delete的where条件列、order by、group by、distinct字段、多表join字段。
(20)禁止使用%前导查询,例如:like “%abc”,无法利用到索引。
(21)禁止使用负向查询,例如 not in、!=、not like。
(22)使用EXPLAIN判断SQL语句是否合理使用索引,尽量避免extra列出现:Using File Sort、Using Temporary。
(23)禁止使用order by rand()。

7.行为规范

(1)表结构变更必须通知DBA进行审核。
(2)禁止有super权限的应用程序账号存在。
(3)禁止有DDL、DCL权限的应用程序账号存在。
(4)重要项目的数据库方案选型和设计必须提前通知DBA参与。
(5)批量导入、导出数据必须通过DBA审核,并在执行过程中观察服务。
(6)批量更新数据,如UPDATE、DELETE操作,必须DBA进行审核,并在执行过程中观察服务。
(7)产品出现非数据库导致的故障时,如被攻击,必须及时通DBA,便于维护服务稳定。
(8)业务部门程序出现BUG等影响数据库服务的问题,必须及时通知DBA,便于维护服务稳定。
(9)业务部门推广活动或上线新功能,必须提前通知DBA进行服务和访问量评估,并留出必要时间以便DBA完成扩容。
(10)出现业务部门人为误操作导致数据丢失,需要恢复数据的,必须第一时间通知DBA,并提供准确时间点、 误操作语句等重要线索。
(11)提交线上建表改表需求,必须详细注明涉及到的所有SQL语句(包括INSERT、DELETE、UPDATE),便于DBA进⾏行审核和优化。
(12)对同一个表的多次alter操作必须合并为一次操作。
(13)不要在MySQL数据库中存放业务逻辑。

8. FAQ

1.库名、表名、字段名必须使用小写字母,并采用下划线分割。

a)MySQL有配置参数lower_case_table_names,不可动态更改,linux系统默认为 0,即库表名以实际情况存储,大小写敏感。如果是1,以小写存储,大小写不敏感。如果是2,以实际情况存储,但以小写比较。

b)如果大小写混合使用,可能存在abc,Abc,ABC等多个表共存,容易导致混乱。

c)字段名显式区分大小写,但实际使用不区分,即不可以建立两个名字一样但大小写不一样的字段。

d)为了统一规范, 库名、表名、字段名使用小写字母。

2.库名、表名、字段名禁止超过32个字符。

库名、表名、字段名支持最多64个字符,但为了统一规范、易于辨识以及减少传输量,禁止超过32个字符。

3.使用INNODB存储引擎。

INNODB引擎是MySQL5.5版本以后的默认引擘,支持事务、行级锁,有更好的数据恢复能力、更好的并发性能,同时对多核、大内存、SSD等硬件支持更好,支持数据热备份等,因此INNODB相比MyISAM有明显优势。

4.库名、表名、字段名禁止使用MySQL保留字。

当库名、表名、字段名等属性含有保留字时,SQL语句必须用反引号引用属性名称,这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。

5.禁止使用分区表。

分区表对分区键有严格要求;分区表在表变大后,执行DDL、SHARDING、单表恢复等都变得更加困难。因此禁止使用分区表,并建议业务端手动SHARDING。

6.建议使用UNSIGNED存储非负数值。

同样的字节数,非负存储的数值范围更大。如TINYINT有符号为 -128-127,无符号为0-255。

7.建议使用INT UNSIGNED存储IPV4。

UNSINGED INT存储IP地址占用4字节,CHAR(15)则占用15字节。另外,计算机处理整数类型比字符串类型快。使用INT UNSIGNED而不是CHAR(15)来存储IPV4地址,通过MySQL函数inet_ntoa和inet_aton来进行转化。IPv6地址目前没有转化函数,需要使用DECIMAL或两个BIGINT来存储。

例如:

SELECT INET_ATON('209.207.224.40');3520061480

SELECT INET_NTOA(3520061480);209.207.224.40

8.强烈建议使用TINYINT来代替ENUM类型。

ENUM类型在需要修改或增加枚举值时,需要在线DDL,成本较大;ENUM列值如果含有数字类型,可能会引起默认值混淆。

9.使用VARBINARY存储大小写敏感的变长字符串或二进制内容。

VARBINARY默认区分大小写,没有字符集概念,速度快。

10.INT类型固定占用4字节存储,例如INT(4)仅代表显示字符宽度为4位,不代表存储长度。

数值类型括号后面的数字只是表示宽度而跟存储范围没有关系,比如INT(3)默认显示3位,空格补齐,超出时正常显示,python、java客户端等不具备这个功能。

11.区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。 存储时间(精确到秒)建议使用TIMESTAMP类型。

DATETIME和TIMESTAMP都是精确到秒,优先选择TIMESTAMP,因为TIMESTAMP只有4个字节,而DATETIME8个字节。同时TIMESTAMP具有自动赋值以及自动更新的特性。注意:在5.5和之前的版本中,如果一个表中有多个timestamp列,那么最多只能有一列能具有自动更新功能。

如何使用TIMESTAMP的自动赋值属性?

a)自动初始化,并自动更新:column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

b)只是自动初始化: column1TIMESTAMP DEFAULT CURRENT_TIMESTAMP

c)自动更新,初始化的值为0:column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

d)初始化的值为0: column1TIMESTAMP DEFAULT 0

12.所有字段均定义为NOT NULL。

a)对表的每一行,每个为NULL的列都需要额外的空间来标识。

b)B树索引时不会存储NULL值,所以如果索引字段可以为NULL,索引效率会下降。

c)建议用0、特殊值或空串代替NULL值。

13.将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据。

有利于有效利用缓存,防止读入无用的冷数据,较少磁盘IO,同时保证热数据常驻内存提高缓存命中率。

14.禁止在数据库中存储明文密码。

采用加密字符串存储密码，并保证密码不可解密，同时采用随机字符串加盐保证密码安全。防止数据库数据被公司内部人员或黑客获取后，采用字典攻击等方式暴力破解用户密码。

15.表必须有主键,推荐使用UNSIGNED自增列作为主键。

表没有主键,INNODB会默认设置隐藏的主键列;没有主键的表在定位数据行的时候非常困难,也会降低基于行复制的效率。

16.禁止冗余索引。

索引是双刃剑,会增加维护负担,增大IO压力。(a,b,c)、(a,b),后者为冗余索引。可以利用前缀索引来达到加速目的,减轻维护负担。

17.禁止重复索引。

primary key a;uniq index a;重复索引增加维护负担、占用磁盘空间,同时没有任何益处。

18.不在低基数列上建立索引,例如“性别”。

大部分场景下,低基数列上建立索引的精确查找,相对于不建立索引的全表扫描没有任何优势,而且增大了IO负担。

19.合理使用覆盖索引减少IO,避免排序。

覆盖索引能从索引中获取需要的所有字段,从而避免回表进行二次查找,节省IO。INNODB存储引擎中, secondary index(非主键索引,又称为辅助索引、二级索引)没有直接存储行地址,而是存储主键值。如果用户需要查询secondary index中所不包含的数据列,则需要先通过secondary index查找到主键值,然后再通过主键查询到其他数据列,因此需要查询两次。覆盖索引则可以在一个索引中获取所有需要的数据,因此效率较高。主键查询是天然的覆盖索引。例如SELECT email,uid FROM user_email WHERE uid=xx,如果uid 不是主键,适当时候可以将索引添加为index(uid,email),以获得性能提升。

20.用IN代替OR。SQL语句中IN包含的值不应过多,应少于1000个。

IN是范围查找,MySQL内部会对IN的列表值进行排序后查找,比OR效率更高。

21.表字符集使用UTF8,必要时可申请使用UTF8MB4字符集。

a)UTF8字符集存储汉字占用3个字节,存储英文字符占用一个字节。

b)UTF8统一而且通用,不会出现转码出现乱码风险。

c)如果遇到EMOJ等表情符号的存储需求,可申请使用UTF8MB4字符集。

22.用UNION ALL代替UNION。

UNION ALL不需要对结果集再进行排序。

23.禁止使用order by rand()。

order by rand()会为表增加一个伪列,然后用rand()函数为每一行数据计算出rand()值,然后基于该行排序, 这通常都会生成磁盘上的临时表,因此效率非常低。建议先使用rand()函数获得随机的主键值,然后通过主键获取数据。

24.建议使用合理的分页方式以提高分页效率。

第一种分页写法：

select *

 from t

 where thread_id = 771025

  and deleted = 0

 order by gmt_create asc limit 0, 15;

select * from t

 where thread_id = 771025

  and deleted = 0

 order by gmt_create asc limit 0, 15;

 原理：一次性根据过滤条件取出所有字段进行排序返回。

 数据访问开销=索引IO+索引全部记录结果对应的表数据IO

 缺点：该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候。

适用场景：当中间结果集很小（10000行以下）或者查询条件复杂（指涉及多个不同查询字段或者多表连接）时适用。

第二种分页写法：

select t.* from (

   select id from t

    where thread_id = 771025 and deleted = 0 order by gmt_create asc limit0, 15) a, t

 where a.id = t.id;

前提：假设t表主键是id列，且有覆盖索引secondary key:(thread_id, deleted, gmt_create)

原理：先根据过滤条件利用覆盖索引取出主键id进行排序，再进行join操作取出其他字段。

数据访问开销=索引IO+索引分页后结果（例子中是15行）对应的表数据IO。

优点：每次翻页消耗的资源和时间都基本相同，就像翻第一页一样。

适用场景：当查询和排序字段（即where子句和order by子句涉及的字段）有对应覆盖索引时，且中间结果集很大的情况时适用。

25.SELECT只获取必要的字段,禁止使用SELECT *。

减少网络带宽消耗;

能有效利用覆盖索引;

表结构变更对程序基本无影响。

26.SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。

语句级复制场景下,引起主从数据不一致;不确定值的函数,产生的SQL语句无法利用QUERY CACHE。

27.采用合适的分库分表策略。例如千库十表、十库百表等。

采用合适的分库分表策略,有利于业务发展后期快速对数据库进行水平拆分,同时分库可以有效利用MySQL的多线程复制特性。

28.减少与数据库交互次数,尽量采用批量SQL语句。

使用下面的语句来减少和db的交互次数:

a)INSERT ... ON DUPLICATE KEY UPDATE

b)REPLACE INTO

c)INSERT IGNORE

d)INSERT INTO VALUES()

29.拆分复杂SQL为多个小SQL,避免大事务。

简单的SQL容易使用到MySQL的QUERY CACHE;减少锁表时间特别是MyISAM;可以使用多核CPU。

30.对同一个表的多次alter操作必须合并为一次操作。

mysql对表的修改绝大部分操作都需要锁表并重建表,而锁表则会对线上业务造成影响。为减少这种影响,必须把对表的多次alter操作合并为一次操作。例如,要给表t增加一个字段b,同时给已有的字段aa建立索引, 通常的做法分为两步:

alter table t add column b varchar(10);

然后增加索引:

alter table t add index idx_aa(aa);

正确的做法是:

alter table t add column b varchar(10),addindex idx_aa(aa);

31.避免使用存储过程、触发器、视图、自定义函数等。

这些高级特性有性能问题,以及未知BUG较多。业务逻辑放到数据库会造成数据库的DDL、SCALE OUT、 SHARDING等变得更加困难。

32.禁止有super权限的应用程序账号存在。

安全第一。super权限会导致read only失效,导致较多诡异问题而且很难追踪。

33.提交线上建表改表需求,必须详细注明涉及到的所有SQL语句(包括INSERT、DELETE、UPDATE),便于DBA进行审核和优化。

并不只是SELECT语句需要用到索引。UPDATE、DELETE都需要先定位到数据才能执行变更。因此需要业务提供所有的SQL语句便于DBA审核。

34.不要在MySQL数据库中存放业务逻辑。

数据库是有状态的服务,变更复杂而且速度慢,如果把业务逻辑放到数据库中,将会限制业务的快速发展。建议把业务逻辑提前,放到前端或中间逻辑层,而把数据库作为存储层,实现逻辑与存储的分离。