MySQL基础操作指南

MySQL最流行的关系型数据库管理系统RDBMS（Relational Database Management System）。

RDBMS术语：

数据库： 数据库是一些关联表的集合。
数据表： 电子表格形式，保存数据的地方。
列：一列（数据元素）包含了相同的数据。
行：一行相关的数据。
冗余： 存储两倍数据，冗余可以使系统速度更快。
主键： 主键是唯一的。一个数据表中只能包含一个主键。
外键： 外键用于关联两个表。
复合键： 复合键将多个列作为一个索引键，一般用于复合索引。
索引： 使用索引可快速访问数据库表中的特定信息。索引是对数据库表一列或多列的值进行排序的一种结构。类似书籍的目录。
参照完整性： 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
数据类型
MySQL中数据类型有：整形、浮点型、定点数、字符串、二进制、日期时间类型

计算机存储信息的最小单位是 位（bit） 。二进制一个’0’或一个’1’叫一位。
计算机存储容量最基本的单位是 __字节（Byte）__。8个二进制位(bit)组成一个字节。一个标准的英文字母占一个字节位置，一个标准的汉字占二个字节位置。（不同编码可能占用字节不一样！）

UTF-8编码中，一个英文字母字符存储需一个字节，一个汉字字符存储需要3~4个字节。

整形

MySQL数据类型	含义
tinyint(m) [UNSIGNED]	1个字节范围（-128~127）
smallint(m) [UNSIGNED]	2个字节范围（-32768~32767）
mediumint(m)[UNSIGNED]	3个字节范围（-8388608~8388607）
int(m) [UNSIGNED]	4个字节范围（-2147483648~2147483647）
bigint(m) [UNSIGNED]	8个字节范围（+-9.22*10的18次方）

取值范围如果加了 unsigned ，则最大值翻倍。例如 tinyint unsigned 的取值范围为(0~256)。

浮点型

MySQL数据类型	含义
float(m, d)	单精度浮点型 8位精度（4字节） m总个数， d小数位
double(m, d)	双精度浮点型 16位精度（8字节） m总个数， d小数位

设一个字段定义为float(5,3)，如果插入一个数123.45678，实际数据库里存的是123.456，总个数以实际为准。

定点数

浮点数在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。
decimal(m, d) 参数 m<65是总个数，d<30且d<m是小数位。

字符串

MySQL数据类型	含义
char(n)	固定长度，最多255个字节
varchar(n)	可变长度，最多65535个字节
tinytext	可变长度，最多255个字节
text	可变长度，最多65535个字节
mediumtext	可变长度，最多2的24次方-1个字节
longtext	可变长度，最多2的32次方-1个字节

char(n) 中的n设定字符串存储时的长度，如果长度不足n，则空格补于其后，查询时在将空格去掉。
varchar(n) 中的N设定字符串存储时的长度，如果长度不足n,不会再后面填充空格，但varchar实际长度是存储时长度+1。因为varchar字段会用一个字节保存字符串长度。

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
‘’	‘ ‘	4bytes	‘’	1byte
‘ab’	‘ab ‘	4bytes	‘ab’	3bytes
‘abcd’	‘abcd’	4bytes	‘abcd’	5bytes
‘abcdsd’	‘abcd’	4bytes	‘abcd’	5bytes

char类型的字符串检索速度要比varchar类型快。但占的存储空间可能比varchar大。

二进制

MySQL数据类型	含义
Blob	最大长度 65535个字节
MediumBlob	最大长度 16777215个字节
LongBlob	最大长度 4294967295个字节

Blob (Binary large objects)储存二进位资料，且有分大小写

日期类型

MySQL数据类型	含义
date	日期 ‘2008-12-2’
time	时间 ‘12:25:36’
datetime	日期时间 ‘2008-12-2 22:06:44’
timestamp	自动存储记录修改时间（系统当前时间）
year	年份yyyy

数据类型属性

MySQL关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

基础SQL

SQL语句主要可以划分为三种类型：

DDL(Data Definition Languages) 数据库定义语言，这些语句定义不同的数据段、数据库、表、列、索引等数据对象。常用的语句关键字 create 、 drop 、 alter 等。
DML(Data Manipulation Language) 数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字包括： insert 、 delete 、 update 、 select 等。
DCL（Data Control Language）数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要语句关键字包括 grant 、 revoke 等。

CREATE

create用于创建数据库和数据库表。

创建数据库：

1	CREATE DATABASE database_name;

创建数据库表：

CREATE TABLE tablename (
column_name_1 column_type_1 constraints,
column_name_2 column_type_2 contraints,
.......
column_name_n column_type_n contraints
)ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

column_name 列名字
column_type 列的数据类型
constraints 列的约束条件

查看表结构：DESC table_name \G;。
查看创建表的表达式： show create table table_name \G;

CREATE TABLE persons (
id int AUTO_INCREMENT PRIMAY KEY，
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

创建MySQL账户，语法：

1	CREATE USER 'user_name'@'[localhost\|%]' IDENTIFIED BY 'password';

localhost 表示只能是本地登录， % 表示可以远程登录。

DROP

删除数据库，删除数据库表，删除表索引。

删除数据库：

1	DROP DATABASE database_name;

删除数据库表：

1	DROP TABLE table_name;

删除表索引：

1	ALTER TABLE table_name DROP INDEX index_name;

ALTER

对于已经创建好的表，如果需要做一些结构上的改变可以使用alter table语句。

修改表列类型

1	ALTER TABLE tbale_name MODIFY [COLUMN] column_definition [FIRST \| AFTER col_name];

COLUMN 字段名
column_definition 字段定义
[FIRST | AFTER col_name] 字段相对位置

例子：

1	ALTER TABLE emp MODIFY ename varchar(20);

增加表字段

1	ALTER TABLE table_name ADD [COLUMN] column_definition [FIRST \| AFTER col_name];

例子：

1	ALTER TABLE emp add column age int(3)

删除表字段

1	ALTER TABLE table_name DROP COLUMN column_name

例子：

1	ALTER TABLE emp DROP COLUMN age;

修改默认值

1	ALTER TABLE table_name ALTER column_name SET DEFAULT value;

删除默认值

1	ALTER TABLE table_name ALTER column_name DROP DEFAULT;

修改字段名

1	ALTER TABLE table_name CHANGE [COLUMN] old_column_name column_definition [FIRST \| AFTER column_name]

例子：

1	ALTER TABLE emp CHANGE age age1 int(4);

修改字段排列顺序

前面介绍字段增加和修改语法（ADD/CHANGE/MODIFY）中，都有一个可选 first|after column_name 这个选项可以用于修改字段在表中的位置。ADD 增加的字段默认式是加在表最后位置。而CHANGE/MODIFY默认都不会改变字段位置。

例子，新增的字段birth date 加在ename之后：

1	ALTER TABLE emp ADD birth date after ename;

修改表名

1	ALTER TABLE old_table_name RENAME TO new_table_name;

INSERT

表创建好后，就可以向里面插入数据。
插入数据基本语法如下：

1	INSERT INTO table_name (field1,field2,field3) VALUES (value1,vaule2,vaule3),(value1.1,value1.2,value1.3);

例子：

1	INSERT INTO persons (LastName,FirstName,Address,City) VALUES ('Gates','Bill','xuanwumen 10','Bei jing');

表中允许为空值的字段可以在插入时不插入数据，但如果表中字段为非空并且没有默认值则每次插入都需要插入数据。

可空字段，非空但含有默认值的字段，自增长字段可以不用在insert后的字段列表里面出现。

数值顺序如果是表结构字段顺序则可以省略字段声明部分。例如：

1	INSERT INTO dept VALUES (5,'dept5);

UPDATE

表里的记录值通过 update命令进行更新。语法：

1	UPDATE tablename SET field1=value1,field2=value2,fieldn=valuen [WHERE CONDITION]

例子：

1	UPDATE persons SET FirstName='Fred' WHERE LastName = 'Wilson';

DELETE

删除记录使用关键字 delete。语法：

1	DELETE FROM table_name [WHERE CONDITION]；

例如：

1	DELETE FROM Person WHERE LastName = 'Wilson'

在MySQL中可以一次删除多个表的数据，语法：

1	DELETE t1,t2,...,tn FROM t1,t2,...tn [WHERE CONDITION];

例子：

1	delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;

SELECT

数据插入到数据库中后，可以用SELECT命令进行各种各样的查询。最基本的语法如下：

1	SELECT * FROM table_name [WHERE CONDITION]

* 表示将所有记录都选出来，等同于用逗号分割所有字段。

查询不重复的记录 distinct

有时需要将表中的记录去掉重复后显示出来，可以使用关键字 distinct。

1	SELECT DISTINCT field1,field2 FROM table_name;

被 distinct 字段必须在首位,且一条查询语句只能有一个 distinct 。

根据限定条件查询 where

很多情况，我们不需要将所有数据查询出来，而只是根据限定条件查询一部分数据，用where关键字可以实现这样操作。

1	select * from persons where City='Bei jing';

where关键字后面条件除了 = 外，还可以使用 > <__ __>= <= != 等比较运算符。多个条件之间还可以使用 or and 等逻辑运算符进行条件联合查询。

1	select * from emp where deptno=1 and sal<3000;

ORDER, LIMIT

我们经常有这样的需求，取出按照某个字段进行排序后的记录结果集，这就需要用到数据库的排序操作。用关键字 ORDER BY来实现。语法如下：

1	SELECT * FROM table_name [WHERE CONDITION] [ORDER BY field1 [DESC\|ASC], field2 [DESC\|ASC],..., fieldn [DESC\|ASC]]

其中 DESC 和 ASC 是排序顺序关键字， DESC 表示按字段进行降序排列，ASC 则表示升序排列，如果不写关键字默认是升序排列。 ORDER BY后面可以跟多个不同的排序字段，并且每个排序字段可以有不同的排序顺序。

1	select * from emp order by deptno, sal desc;

排序后希望只显示一部分而不是全部，就可以使用LIMIT关键字来实现。语法如下：

1	SELECT ... [LIMIT offset_start, row_count];

其中 offset_start 表示记录的起始偏移量， row_count 表示显示的行数。

1	select * from emp order by sal limit 3;

limit经常和order by 一起配合使用来进行记录的分页。

GRANT

给MySQL账户分配权限使用 grant。语法：

1	GRANT operate1,operate2,...,operateN ON db.tableName to 'user'@'[%\|localhost]';

假如数据库中有个 testdb 数据库和 developer 账户。
给 developer 账户授予查询，插入，更新 testdb 数据库中所有表的权限，并且可以在远程登录。例子：

1	grant select,insert,update on testdb.* to 'developer'@'%'；

授予所有权限语法：

1	grant all privileges on testdb.* to 'developer'@'%';

REVOKE

撤回某个账户的权限使用关键字 revoke。语法：

1	revoke operate1,operate2,...,operateN ON db.tableName FROM 'user'@'[%\|localhost]';

高级SQL

聚合操作 count()、sum()、max()、min()、GROUP BY 、HAVING 、WITH ROLLUP

count(column_name) - 返回指定列的值得数目。
- count(*) - 返回表中记录数
- count(DISTINCT column_name) - 返回函数指定列不同值得数目
sum(column_name) - 返回数值列得总数
max(column_name) - 返回一列中的最大值。
min(column_name) - 返回一列中的最小值。
GROUP BY - 结合合计函数，根据一个或多个列对结果进行分组
HAVING - 对分类后的结果再进行条件的过滤。

表连接 JOIN

表连接分为内连接和外连接。它们之间的主要区别是，内连接仅选出两张表中互相匹配的记录，而外连接会选出其他不匹配的记录。

内连接，例子：

1	select ename,deptname from emp,dept where emp.deptno = dept.deptno;

左连接，LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。语法：

1	SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name = table_name2.column_name;

右连接，RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。语法：

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

INNER JOIN

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。语法：

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

UNION 和 UNION ALL

UNION 操作符用于合并两个或多个SELECT语句的集合。语法：

1
2
3

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

UNION默认会选取不同的值，如果允许值重复使用UNION ALL。

存储引擎

和大多数数据库不同，MySQL中有存储引擎的概念，不同的存储引擎其应用方面不一样。常用到的MySQL存储引擎有：MyISAM、InnoDB、MEMORY、MERGE。
常用数据库对比

MyISAM

MySQL5.5之前，MyISAM 是 MySQL 的默认存储引擎。MyISAM不支持事务、也不支持外键。其优势是访问的速度快，对事务完整性没有要求或以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。
每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但扩展名分别是：.frm(存储表定义)、.MYD(MYData,存储数据)、.MYI(MYIndex,存储索引)。
MyISAM的表支持三种不同的存储格式，分别是：静态（固定长度）、动态表、压缩表。

MyISAM存储引擎特别适合在一下几种情况下使用：

选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎适合管理邮件或Web服务器日志数据。

InnoDB

MySQL5.5起，InnoDB是MySQL的默认存储引擎。
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但对比MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

在一下场合下，使用InnoDB是最理想的选择：

更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
自动崩溃恢复。与其它存储引擎不同，InnoDB表能够自动从崩溃中恢复。
外键约束。MySQL支持外键的存储引擎只有InnoDB。
支持自动增加列AUTO_INCREMENT属性。

MEMORY

MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。
MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉。
MEMORY 类型的存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。对 MEMORY 存储引擎的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

MERGE

MERGE存储引擎是一组表结构完全相同的MyISAM表的组合。
MERGE本身没有数据，对MERGE类型的表进行的查询、更新、删除操作实际上是对其内部MyISAM表进行的。
应用例子，以年为分割点的日志记录，可以将多年的日志表合并为一张表进行查询。例如：

CREATE TABLE log_YY (  
	  dt  DATETIME NOT NULL,  
	  info VARCHAR(100) NOT NULL,  
	  INDEX (dt)  
) ENGINE = MyISAM;

上面SQL创建日志表。

CREATE TABLE log_merge(  
	    dt DATETIME NOT NULL,  
	    info VARCHAR(100) NOT NULL,  
	    INDEX(dt)  
) ENGINE = MERGE UNION = (log_2014, log_2015, log_2016, log_2017);

上面SQL将多张日志表合并为一张表。

TokuDB

TokuDB不是MySQL官方存储引擎(安装教程)。它是TokuTek公司（已被 Percona收购）研发的新引擎，支持事务/MVCC，有着出色的数据压缩功能，支持异步写入数据功能。

TokuDB索引结构采用fractal tree数据结构，是buffer tree的变种，写入性能优异，适合写多读少的场景。除此之外，TokuDB还支持在线加减字段，在线创建索引，锁表时间很短。

Percona Server和Mariadb支持TokuDB作为大数据场景下的引擎，目前官方MySQL还不支持TokuDB。ApsaraDB for MySQL从2015年4月开始支持TokuDB，在大数据或者高并发写入场景下推荐使用。

外键（FOREIGN KEY）

外键作用是将两张表关联起来，以保证数据的一致性。
例如：
有一张用户表(user)和一张订单表(order)。
订单表里有个字段user_id于用户表的字段id进行了外键约束。
每创建一条订单数据，user_id字段必须是用户表中id中一个值，如果user_id值不存在于id就会报错。
删除用户表中一条数据（订单表有该用户的订单数据），如果没有提前设定规则数据库应提示错误。

创建外键语法

[CONSTRAINT symbol] FOREIGN KEY [id] (从表的字段1)

REFERENCES tbl_name (主表的字段2)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}

CONSTRAINT symbol： 外键约束名字，如果不加此参数，系统会自动分配一个名字。
FOREIGN KEY: 将从表中的字段1作为外键的字段。
REFERENCES: 映射到主表的字段2。
ON DELETE / ON UPDATE: 删除或更新主表时所做的约定：
- RESTRICT(限制)：如果你想删除的那个主表，它的下面有对应的从表的记录，此主表无法删除。
- CASCADE（级联）: 如果主表的记录删除，则从表中相关联的记录都将被删除。
- SET NULL: 将外键设置为空。
- NO ACTION: 什么都不做。

例子：

mysql> CREATE TABLE `user` (                   //创建用户表  
 ->   `id` int(11) NOT NULL auto_increment COMMENT '用户ID',  
 ->   `name` varchar(50) NOT NULL default '' COMMENT '名称',  
 ->   `sex` int(1) NOT NULL default '0' COMMENT '0为男，1为女',  
 ->   PRIMARY KEY  (`id`)  
 -> ) ENGINE=innodb  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;   //innodb引擎  

mysql> create table `order` (              //创建订单表  
 ->  `order_id` int(11) not null auto_increment comment '订单ID',  
 ->  `u_id` int(11) not null default '0' comment '用户ID',  
 ->  `username` varchar(50) not null default '' comment '用户名',  
 ->  `money` int(11) not null default '0' comment '钱数',  
 ->  `datetime` timestamp not null default current_timestamp comment '生成时 
间',  
 ->  primary key(`order_id`),  
 ->  index (`u_id`),  
 ->  FOREIGN KEY order_f_key (u_id) REFERENCES user(id)     //创建外键  
 -> )ENGINE=innodb  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;    //innodb引擎

外键只有InnoDB存储引擎才支持。对外键关联的表进行检索会影响效率。

索引

索引是数据库中提高查询操作性能的最佳途径。但滥用索引会降低写操作的性能，因为在更新表时不仅要保存数据，还要保存一下索引文件。

创建索引

1	CREATE [UNIQUE\|FULLTEXT\|SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name1,index_col_name2,...,index_col_nameN)

index_col_name: col_name [(length)] [ASC | DESC]
如果是CHAR、VARCHAR类型，length可以小于字段实际长度；

修改表结构

1	ALTER mytable ADD INDEX [indexName] ON (username(length))

创建表的时候指定

CREATE TABLE mytable(  

ID INT NOT NULL,   

username VARCHAR(16) NOT NULL,  

[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

);

unique|fulltext|spatial为可选参数，分别表示唯一索引、全文索引和空间索引；
index和key为同义词，两者作用相同，用来指定创建索引
col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；
index_name指定索引的名称，为可选参数，如果不指定，MYSQL默认col_name为索引值；
length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
asc或desc指定升序或降序的索引值存储

删除索引

1	DROP INDEX [indexName] ON mytable;

索引类型

索引使在MySQL的存储引擎层中实现的。
从数据结构：

B-Tree索引 - 大部分引擎都支持。原理参考 MySQL索引背后的数据结构及算法原理
HASH索引 -
1. 仅仅能满足“=”，“IN”和“<=>”查询，不能使用范围查询。
2. 其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从跟节点到枝节点，最后才能访问页面节点这样多次IO访问，所以Hash索引的查询效率要远高于B-Tree索引。
3. 只有Memory存储引擎显示支持
FULLTEXT索引（全文索引） - MyISAM支持，InnoDB从MySQL5.6版本开始支持。
R-Tree(空间索引)：空间索引使MyISAM的一个特殊索引类型，主要用于地理空间数据类型。

从物理存储角度：

聚焦索引（clustered idnex）
非聚焦索引（non-clustered index）

从逻辑角度：

主键索引：主键索引是一种特殊的唯一索引，不允许有空值
多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
唯一索引或者非唯一索引
空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

视图

视图-事先定义好SQL操作规则的虚拟表。
对视图的使用就把它当作table,但它并没有定义字段。它的数据和字段来自真实的table。

视图相对普通表的优势主要包括一下：

简单：使用视图的用户不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件结果集。
安全：使用视图的用户只能访问它们被允许查询的结果集，对表的权限管理并不能限制到某行某列，但通过视图就可以简单的实现。
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改了列名，通过修改视图来解决，不会造成对访问者的影响。

创建视图

创建视图语法：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

__OR REPLACE: __ 替换已有视图
__ALGORITHM: __ 视图选择算法。
1. 默认算法UNDEFINED：MySQL自动选择要使用的算法。
2. MERGE: MySQL会先将输入的查询语句和视图的声明语句进行合并，然后执行合并后的语句并返回。
3. TEMPTABLE:MySQL先基于视图的声明创建一张temporary table，当输入查询语句时会直接查询这张temporary table。由于需要创建temporary table来存储视图的结果集, TEMPTABLE的效率要比MERGE策略低，另外使用temporary table策略的视图是无法更新的。
select_statement: select语句
[WITH [CASCADED | LOCAL] CHECK OPTION]: 视图在更新时保证在视图的权限范围之内。
1. CASCADE 默认值，表示更新视图的时候，要满足视图和表相关条件。
2. local表示更新视图的时候，要满足该视图定义的一个条件即可

例子：

mysql>CREATE OR REPLACE VIEW staff_list_view AS
->SELECT s.staff_id,s.first_name,s.last_name,a.address
->FROM staff AS s,address AS a
->where s.address_id = a.address_id
->with check option;

视图一旦创建完毕，就可以像普通表那样使用，视图主要用来查询：

1	mysql> select * from view_name

删除视图

语法：

1	SQL DROP VIEW Syntax DROP VIEW view_name;

例子：

1	drop view staff_list;

存储过程和函数

存储过程-数据库端可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的 控制结构 组成。
存储过程可以看做是对编程中面向对象方法的模拟。

存储过程优点：

将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用。
执行速度快。存储过程经过编译之后会比单独的一条一条执行的更快。
减少网络传输。存储过程是直接在数据库服务器上跑，所有的数据访问都是在服务器内部，不需要传输数据到其它终端。
生成环境下，可以通过直接修改存储过程的方式修改业务逻辑，而不用重启服务器。

存储过程缺点：

SQL本身是一种结构化查询语言，加上一些控制，但它不是OO，本质还是过程化的，面对复杂的业务逻辑，过程化处理会很复杂。
无法适应数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。
不便于调试。
增加数据库服务器开销。

创建存储过程

语法：

CREATE PROCEDURE sp_name ([proc_parameter])
	BEGIN
		routine_body
	END

proc_parameter - [IN | OUT | INOUT] param_name type
- IN - 参数传数据中存储过程内部。
- OUT - 存储过程内部的数据传给参数，被外部变量获得。
- INOUT - 参数即将数据传给存储过程内部，又接收存储过程传给的数据，以被外部变量获得。
routine_body - SQL语句

例子 IN参数：

mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=2;
    ->    select P_in;
    -> end$$
mysql> delimiter ;
mysql> set @p_in=1;
mysql> call in_param(@p_in);

delimiter 定义MySQL语句最后结束符。
存储过程的调用通过关键字 call + pro_name(params)。

例子OUT参数：

mysql> delimiter //
mysql> create procedure out_param(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;
mysql> set @p_out=1;
mysql> call out_param(@p_out);

删除存储过程

1	DROP {PROCEDURE \| FUNCTION} [IF EXISTS] sp_name

存储过程语法

变量的定义

通过 DECLARE 可以定义一个局部变量，该变量的作用域只能在 BEGIN…END 块中。可以用于嵌套块中。

语法：

1	DECLARE var_name [,...] type [DEFAULT value]

例子：

1	DECLARE last_month_start DATE;

变量的赋值

变量可以直接赋值，或通过查询赋值。

直接赋值通过 SET 例子：

1	SET var_name = expr [, var_name = expr] ...

通过查询赋值，例子：

1	SELECT col_name[,...] INTO var_name[,...] table_expr

定义条件和处理

条件的定义和处理可以用来定义在处理过程中遇到问题时对应的处理步骤。

条件的定义，语法：

1	DECLARE condition_name CONDITION FOR condition_value

condition_value:
- SQLSTATE [VALUE] sqlstate_value
- mysql_error_code

条件的处理，语法：

1	DECLARE handle_type HANDLER FOR condition_value[,...] sp_statement

handle_type
- CONTINUE - 继续执行下面的语句
- EXIT - 执行终止
condition_value
- SQLSTATE [VALUE] sqlstate_value
- confition_name
- SQLWARNING 所有以 01 开头的 SQLSTATE 代码的速记
- NOT FOUND 所有以 02 开头的 SQLSTATE 代码的速记
- SQLEXCEPTION 所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 代码的速记
- mysql_error_code 所有mysql错误代码。

例子，向一张表批量插入数据。当数据主键有重复时插入会失败并报 23000 错误。可以通过定义错误处理忽略此错误。

mysql> delimiter $$

mysql>

mysql> CREATE PROCEDURE actor_insert ()

-> BEGIN

-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;

-> SET @x = 1;

-> INSERT INTO actor(actor_id,first_name,last_name) VALUES (201,'Test','201');

-> SET @x = 2;

-> INSERT INTO actor(actor_id,first_name,last_name) VALUES (1,'Test','1');

-> SET @x = 3;

-> END;

-> $$

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; 此句告诉数据库当出现 23000错误时继续执行下面的SQL语句。

上面的例子可以写成下面几种形式：

捕获 mysql-error-code: DECLARE CONTINUE HANDLER FOR 1062 SET @x2 = 1;
事先定义 condition_name:
1. DECLARE DuplicateKey CONDITION FOR SQLSTATE '23000';
2. DECLARE CONTINUE HANDLER FOR DuplicateKey SET @x2 = 1;
捕获 SQLEXCEPTION：DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x2 = 1;

光标的使用

在存储过程和函数中可以通过光标和循环对结果集中的每行数据进行处理。光标的使用包括：声明、打开、获取、关闭。

声明光标：DECLARE cursor_name CURSOR FOR select_statement
打开光标：OPEN cursor_name
获取：FETCH cursor_name INTO var_name,[.var_name]…
关闭：CLOSE cursor_name

例子：对payment表按照行进行循环处理，按照staff_id值的不同累加 amount值。判断循环结束的条件是捕获NOT FOUND 的条件，当FETCH光标找不到下一条记录的时候，就会关闭光标然后退出过程。

mysql> delimiter $$
mysql> 
mysql> CREATE PROCEDURE payment_stat()
> BEGIN
> DECLARE i_staff_id int;
> DECLARE d_amount decimal(5,2);
> DECLARE cur_payment cursor for select staff_id,amount from payment;
> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
> set @x1 = 0;
> set @x2 = 0;
> OPEN cur_payment;
> REPEAT
> FETCH cur_payment INTO i_staff_id,d_amount;
> if i_staff_id = 2 then
> set @x1=@x1+d_amount;
> else
> set @x2=@x2 + d_amount;
> end if;
> UNTIL 0 END REPEAT;
> CLOSE cur_payment;
> END;
> $$

流程控制

可以使用 IF、CASE、LOOP、LEAVE、ITERATE、REPEAT及WHILE语句进行流程的控制。

IF 实现条件判断，满足不同的条件执行不同语句列表，语法：IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF
CASE 类似JAVA语言中的SWITCH。语法：CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE
LOOP 简单的循环，退出循环的条件需要使用其它语句定义，通常使用LEAVE。语法：[begin_label:] LOOP statement_list END LOOP [end_label] 如果不再 statement_list中增加退出循环的语句，那么LOOP语句可以用来实现简单的死循环。
LEAVE 用来从标注的流程构造中退出，通常和BEGIN…END或循环一起使用。
ITERATE 必须在循环中使用，作用跳出当前循环的剩余语句，直接进入下一轮循环用法和JAVA中BREAK一样。
REPEAT 有条件的循环控制语句，当满足条件的时候退出循环。语法：[begin_lable:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]
WHILE 语句实现的是有条件的循环控制语句，当满足条件时执行循环内容。语法：[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]

WHILE循环和REPEAT循环的区别在于：WHILE是满足条件才执行循环，REPEAT是满足条件退出循环。WHILE最少执行0次，而REPEAT最少执行1次。

触发器

MySQL从5.0.2版本开始支持触发器的功能。触发器是当某张表发生触发器中定义触发事件时所做的反应。例如定义某张表发生插入或更新时进行某些SQL操作。

创建触发器

语法：

1	CREATE TRIGGER trigger_name trigger_time trigger_event on tbl_name FOR EACH ROW trigger_stmt

注意：触发器只能创建在永久表上，不能对临时表创建触发器。

trigger_name 触发器名称
trigger_time 触发器的触发时间 BEFORE 或 AFTER。BEFORE 的含义指在检查约束前触发，而AFTER是在检查约束后触发。
trigger_event 触发器的触发事件，可以是 INSERT、UPDATE 或 DELET。对同一个表同一触发时间的相同触发事件，只能定义一个触发器。

例子，为film表创建 AFTER INSERT的触发器，具体如下：

mysql> DELIMITER $$
mysql> CREATE TRIGGER ins_film
> AFTER INSERT ON film FOR EACH ROW BEGIN
> INSERT INTO film_text(film_id, title, description)
> VALUES (new.film_id, new.title, new.description);
> END;
> $$
mysql> DELIMITER ;

上面定义一个film插入数据触发器。当film表有插入数据操作后，触发器会将插入的数据添加到表film_text中。

对于有重复的记录，需要进行UPDATE操作的INSERT，触发器触发的顺序是 BEFORE INSERT、BEFORE UPDATE、AFTER UPDATE、AFTER INSERT。对于没有重复记录的 INSERT，就是简单的执行 INSERT 操作，触发器触发的顺序是 BEFORE INSERT、AFTER INSERT。对于那些实际执行UPDATE操作的记录，仍然会执行BEFORE INSERT触发器的内容。

删除触发器

一次可以删除一个触发器，如果没指定shema_name，默认为当前数据库，具体语法：

1	DROP TRIGGER [schema_name.]trigger_name。

查看触发器

1	mysql> show triggers \G

触发器执行的语句有以下两个限制：

触发器程序不能调用将数据返回客户端的存储程序，也不能使用才CALL语句的动态SQL语句，但允许存储程序通过参数将数据返回触发程序。也就是存储过程或者函数通过 OUT或者 INOUT 类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。
不能在触发器中使用以显式或隐式方式开始或结束事务的语句，如 START TRANSACTION、COMMIT 或 ROLLBACK。
事务控制
事务（Transaction）是并发控制的基本单位。一个事务可以有一条SQL语句，一组SQL语句或整个程序。
事务内的操作要么都执行，要么都不执行，它是一个不可分割的工作单元。
特点（ACID）：

原子性： 一组事务要么成功，要么失败。
一致性： 数据库总是从一个一致性状态转换到另一个一致性状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。再比如有非法数据（外键约束之类），事务撤回。
隔离性： 事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。事务隔离级别分四种：READ UNCOMMITED 、READ COMMITED 、 REPEATABLE READ 、 SERIALIZABLE
- READ UNCOMMITED： 使用查询语句不会加锁，可以会读到未提交的行。
- READ COMMITED： 一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。
- REPEATABLE READ： 该级别保证了在同一个事务中多次读取同样记录的结果是一致的。可重复读是mysql的默认事务隔离级别。
- SERIALIZABLE： serilizable是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题。简单来说，serializable会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。
持久性： 事务的持久性就体现在，一旦事务被提交，那么数据一定会被写入到数据库中并持久存储起来。当事务已经被提交之后，就无法再次回滚了，唯一能够撤回已经提交的事务的方式就是创建一个相反的事务对原操作进行『补偿』，这也是事务持久性的体现之一。

如果不考虑隔离性可能出现的几种情况：脏读，不可重复读，幻读。

脏读： 指在一个事务处理过程里读取了另一个未提交的事务中的数据。
不可重复读： 指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这个由于在查询间隔，被另外一个事务修改并提交了。
幻读： 幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

语法：

START TRANSACTION | BEGIN [WORK]

SAVEPOINT identifier

COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

RELEASE SAVEPOINT identifier

ROLBACK TO identifier

SET TRANSACTION

SET AUTOCOMMIT = {0|1}

START TRANSACTION 或BEGIN语句可以开始一项新的事务。
COMMIT 和 ROLLBACK用来提交或回滚事务。
CHAIN和RELEASE子句分别用来定义事务提交或回滚后的操作。
- CHAIN会立即启动一个新事务并和刚才事务有相同的隔离级别。
- RELEASE则会断开和客户端的连接。
SET AUTOCOMMIT 可以修改当前连接的提交方式，如果设置为 SET AUTOCOMMIT=0,则设置之后的所有事务都需要明确的命令进行提交或回滚。
SAVEPOINT identifier 在事务内创建标记点，一个事务内可以创建多个标记点
RELEASE SAVEPOINT identifier 删除一个事务标记点，当没有指定的事务标记点时，执行该语句会报错。
ROLLBACK TO identifier 把事务回滚到标记点。
SET TRANSACTION 用来设置事务隔离级别。InnoDB存储引擎隔离级别有 READ UNCOMMITED 、READ COMMITTED 、REPEATABLE READ 、SERIALIZABLE。

例子：

1
2
3

mysql> start transaction;
mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom');
mysql> commit;

分布式事务

分布式事务-在不同的数据库中执行事务操作。所有行动都必须成功完成，或者一起回滚。
在MySQL中，使用分布式事务的应用涉及一个或多个资源管理器和一个事务管理器。

资管管理器（RM）：用于提供进行事务行为的资源。数据库服务器是一种资源管理器。该管理器必须可以提交或回滚RM管理的事务。
事务管理器（TM）:协调为一个分布式事务一部分的事务。TM与管理每个事务的RMs进行通信。在一个分布式事务中，各个单个事务均是分布式事务的“分支事务”。分布式事务和各个分支通过一个命名方法进行标识。

分布式事务分为两个阶段：

所有分支被预备好。即它们被TM告知要准备提交。这意味着用于管理分支的每个RM会记录对于被稳定保存的分支的行为。分支指示它们可以这么做。这些结果被用于第二阶段。
TM告知RMs是否要提交或回滚。如果在预备分支时，所有的分支指示它们将能够提交，则所有的分支被告知可以提交。如果在预备时，有任何分支指示它将不能提交，则所有分支被告知回滚。

语法：

# 启动一个带xid值得XA事务
#xid:gtrid [,bqual [,formateID]]
#gtrid - 一个分布式事务标识符，相同得分布式事务应该使用相同得 gtrid。
#bqual - 一个分支限定符，默认为空。对于一个分布式事务中的每个分支事务，bqual值必须是唯一的。
#formatID - 是一个数字，用于标识由gtrid和bqual值使用的格式，默认值是1。
XA {START|BEGIN} xid [JOIN|RESUME] 

XA END xid [SUSPEND [FOR MIGRATE]]

# 使事务进入PREPARE状态
XA PREPARE xid

XA COMMIT xid [ONE PHASE]
XA ROLLBACK xid

# 返回当前数据库中PREPARE状态的分支事务的详细信息。
XA RECOVER

例子：

mysql> xa start 'test','db1';
mysql> insert into actor (actor_id,first_name,last_name) values (301,'simon','Tom')
mysql> xa end 'test','db1'
mysql> xa prepare 'test','db1'
mysql> xa revocer \G
mysql> xa commit 'test','db1';

skills

查看、修改数据库编码

show variables like 'character%'; --- 查看数据库编码；
set character_set_client = utf8; --- 设置mysql 客户端编码；
show variables like 'collation_%' --- 查看数据库排序规则;
在 mysql.ini 手动设置 字符编码；
[mysqld] 修改或添加
character-set-server=utf8
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
[client] 修改或添加
default-character-set=utf8
[mysql]
default-character-set=utf8

查看索引工作情况

如果索引正在工作，Handler_read_key 的值将会很高，这个值代表一个行被索引读的次数，很低的值表名增加所以得到的性能改善不高，因为索引没经常使用。

Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该添加索引补救。这个值含义是数据文件中读下一行的请求数。如果正进行大量的表扫描，Handler_read_rnd_next 的值会很高。则说明索引不正确或写入的查询没利用索引。

1	mysql> show status like 'Handler_read%';

使用 explain进行查询性能分析

在查询语句添加关键字 explain 可查看次查询语句具体查询数据。

mysql> explain select customer_id from customer order by store_id \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: a

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1000

Extra: Using where

select_type: 表示SELECT的类型。
- SIMPLE - 简单表，既不使用表连接或子查询
- PRIMARY - 主查询，即外层的查询
- UNION
- SUBQUERY 子查询的第一个SELECT
table: 输出结果集的表
type: 表示表的连接类型，性能由好到差的连接类型为：
- system 表中仅有一行，即常量表
- const 单表中最多有一个匹配
- eq_ref 对于前面的每一行，此表中只查询一条记录，简单来说，就是多表连接中使用primary key unique index
- ref 与eq_ref类似，区别在于不是使用primary key 或 unique index，而是使用普通的索引
- ref_or_null 与ref类似，区别在于条件中包含对NULL的查询
- index_merge 索引合并优化
- unique_subquery in的后面是一个查询主键字段的子查询
- index_subquery 与unique_subquery类似区别在于in的后面是查询非唯一索引字段的子查询
- rang 单表中的范围查询
- index 对于前面的每一行，都通过查询索引来得到数据
- all 对前面的每一行都通过全表扫描来得到数据
possible_keys: 表示查询时，可能使用的索引。
key: 表示实际使用的索引
ken_len: 索引字段的长度
rows: 扫描行的数量
Extra: 执行情况的说明和描述

连接远程数据库

在本机电脑连接远程数据库服务器，语法：

1	> mysql -h www.hostname.com -P 3306 -uroot -p

在远程服务器打开防火墙，允许外部地址连接 3306端口。
打开防火墙后，要需要确保MySQL允许远程访问。

限定访问地址配置项是在 mysql数据库 user表 host字段。
host字段默认是’localhost’。只允许本地登陆。修改host字段为’%’允许所有地址登陆。然后重启mysql就可以了。

批量执行SQL

在批量执行SQL时，可能因为某条SQL错误导致后面SQL无法执行。错误的SQL提示又不够明显，调试会很麻烦。通过在执行SQL中添加 -f 强制执行，-v显示错误SQL --show-warnings显示全部错误信息。可以解决这个问题。例子：

1	$ mysql -uroot -v --show-warnings -f test < a.sql

使用mysqlbinlog提取二进制日志

由于服务器生成的二进制日志文件以二进制格式保存，所以如果要想检查这些文件的文本格式，就会用到 mysqlbinlog 日志管理工具。

语法：

1	shell> mysqlbinlog [options] log-files1 log-files2...

option选项：

-d, –database=name 指定数据库名称，只列出指定的数据库相关操作。
-o, –offset=# 忽略掉日志中的前 n 行命令
-r, –result-file=name 将输出的文本格式日志输出到指定文件
-s, –short-form 显示简单格式，省略掉一些信息
–start-datetime=name –stop-datetime=name：指定日期间隔内的所有日志
–start-position=# –stop-position=#：指定位置间隔内的所有日志

mysqlcheck（MyISAM 表维护工具）

mysqlcheck 客户端工具可以检查和修复 MyISAM 表，还可以优化和分析表。实际上，它集成
了 mysql 工具中 check、repair、analyze、optimize 的功能。
语法：

shell> mysqlcheck[options] db_name [tables]

shell> mysqlcheck[options] ---database DB1 [DB2 DB3...]

shell> mysqlcheck[options] --all—database

option常用项：

-c, –check 检查表
-r, –repair 修复表
-a, –analyze 分析表
-0, –optimize 优化表

例子：

1
2
3

[root@localhost mysql]# mysqlcheck -uroot -c test
[root@localhost mysql]# mysqlcheck -uroot -a test
[root@localhost mysql]# mysqlcheck -uroot -o test

mysqldump（数据导出工具）

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表或装载表的 SQL 语句。mysqldump 目前是 MySQL 中最常用的备份工具。它是和mysql一起被安装的，在mysql安装目录的bin目录下可以找到它。

常用命令：

mysqldump -u 用户名 -p 数据库名 > 导出的文件名  #导出一个数据库
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 # 导出一个表
mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >d:\wcnc_db.sql # 导出表结构
mysqldump  -uroot -p -all-databases # 导出所有数据库

mysqlshow（数据库对象查看工具）

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或索引。

optimizations

优化 GROUP BY语句

GROUP BY 语句结合合计函数，根据一个或多个列对结果集进行分组。
语法：

1	SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operate value GROUP BY column_name

例子：

1 2	SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer

默认情况下，MySQL对所有GROUP BY col1,col2….的字段进行排序。这与在查询中指定ORDER BY col1,col2…类似。因此，如果显示包括一个包含相同的列的 ORDER BY子句，则对MySQL的实际执行性能没什么影响。
如果查询包括GROUP BY但用户想要避免排序结果的消耗，则可以指定ORDER BY NULL。

优化 INSERT 语句

当进行数据INSERT的时候，可以考虑采用一下几种优化方式。

同时插入多行数据，尽量使用多个值表的INSERT语句。例如：insert into test values(1,2),(1,3),(1,4)…。这种方式将大大缩减客户端与数据库之间的连接，关闭等消耗，使得效率比分开执行的单个INSERT语句块。
如果从不同客户插入多行数据，能通过使用 INSERT DELAYED语句得到更高的速度。DELAYED的含义是让 INSERT语句马上执行，其实数据都被放在内存队列中，并没有真正写入磁盘，这比每条语句分别插入要快的多。LOW_PRIORITY刚好相反，在所有其他用户对表的读写完后才进行插入。
将索引文件和数据文件分在不同的磁盘上存放。
如果进行批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是，只对MyISAM表使用。
当从一个文本文件装载一个表时，使用LOAD DATE INFILE。通常比使用INSERT语句快20倍。

优化嵌套查询

MySQL 4.1 开始支持 SQL 的子查询。这个技术可以使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。

连接（JOIN）之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

MySQL 如何优化 OR 条件

对于含有 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。

使用 SQL 提示

SQL 提示（SQL HINT）是优化数据库的一个重要手段，简单来说就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

USE INDEX

在查询语句中表名的后面，添加USE INDEX来提供系统MySQL去参考索引列表，可以让MySQL不再考虑其它可用索引。

例子：

1	mysql> explain select * from sales2 use index (ind_sales2_id) where id = 3\G;

IGNORE INDEX

如果用户只是单纯地想让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX 作为 HINT。

例子：

1	explain select * from sales2 ignore index (ind_sales2_id) where id = 3\G;

FORCE INDEX

为强制 MySQL 使用一个特定的索引，可在查询中使用 FORCE INDEX 作为 HINT。例如，当不强制使用索引的时候，因为 id 的值都是大于 0 的，因此 MySQL 会默认进行全表扫描，而不使用索引:

mysql> explain select * from sales2 where id > 0 \G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: sales2

type: ALL

possible_keys: ind_sales2_id

key: NULL

key_len: NULL

ref: NULL

rows: 1000

Extra: Using where

1 row in set (0.00 sec)

但是，当使用 FORCE INDEX 进行提示时，即便使用索引的效率不是最高，MySQL 还是选择使用了索引，这是 MySQL 留给用户的一个自行选择执行计划的权力。加入 FORCE INDEX 提示后再次执行上面的 SQL：

mysql> explain select * from sales2 force index (ind_sales2_id) where id > 0

\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: sales2

type: range

possible_keys: ind_sales2_id

key: ind_sales2_id

key_len: 5

ref: NULL

rows: 1000

223

Extra: Using where

1 row in set (0.00 sec).

参考：

http://www.w3school.com.cn/sql/index.asp
http://www.runoob.com/mysql/mysql-tutorial.html
深入浅出Mysql
https://www.alibabacloud.com/help/zh/doc-detail/26133.htm

文章目录

数据类型

整形

浮点型

定点数

字符串

二进制