深入浅出MySql（1）

基础篇+开发篇，包括一些基础知识。
后续将学习优化篇

---

1、 子查询

# 子查询select * from emp where deptno in (select deptno from dept);#如果子查询记录数唯一，还可以用in代替select * from emp where deptno = (select deptno from dept limit 1);#子查询还可以转化为表连接select * from emp where deptno in (select deptno from dept);#等价于select * frmo emp,dept where emp.deptno = dept.deptno;

2、记录联合

union 和 union all

select * from t1 UNION | UNION ALL select * from t2 UNION | UNION ALLselect * ...

主要区别：
- UNION ALL将结果简单的合并，可能存在重复的元素。效率更高。
- UNION将前者的结果进行一次DISTINCT，去除重复的记录。

3、 数据类型

3.1、数值类型

浮点数和定点数都可以在类型名称之后加“（M，D）”方式表示。
- M：精度，表示一共显示多少位。
- D：标度，表示位于小数点后面。

注意：
- 浮点数如果不写精度和标度，则会按照实际精度值显示。如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错。
- 定点数如果不写精度和标度，按照默认值decemal(10,0)显示，如果超越了精度和标度值，系统会报错。

3.2、日期类型

date、time、datetime、timestamp
- date: 2017-07-22
- time: 14：42：58
- datetime:2017-07-22 14：42：58
- timestamp:时间戳。默认为current_timestamp一个表中只能有一列的默认值为current_timestamp。
- year：记录年份

timestamp的取值范围到2038年，因此不适合存放比较久远的数据。超过范围设置为0。

TIMESTAMP和DATETIME的区别：
- timestamp支持范围比较小。datetime范围更大。
- 表中的第一个timestamp列自动设置为系统时间，默认类型是cunrrent_timestamp。溢出的设置为0000
- timestamp的插入和查询受到当地时区的影响，更能反映实际的日期。

3.3、字符串类型

3.3.1 CHAR和VARCHAR

• CHAR：固定长度。长度可以是0~255。去除尾部的空格。
• VARCHAR：到65535，不去除尾部空格。

3.3.2 BINARY和VATBINARY

3.3.3 ENUM类型

3.3.4 SET类型

4、运算符

4.1 算术运算符

• +
• -
• *
• /
• %

4.2 比较运算符

• =
• !=
• <=>:null安全的等于
• <
• >
• <=
• >=
• BETWEEN
• IN
• IS NULL
• IS NOT NULL
• LIKE：通配符匹配。
• REGEXP或RELIKE：正则表达式匹配。

4.3 逻辑运算符

• NOT或！
• AND或&&
• OR或||
• XOR：异或

4.4 位运算符

• &：位与
• |：位或
• ^：为异或
• ~：位取反
• >>
• <<

5、常用函数

6、图形化工具的使用

7、表类型（存储引擎）的选择

常用的引擎有InnoDB、MyISAM。
- InnoDB：5.5版本之后的默认引擎，支持事务。
- MyISAM：不支持事务。

查看引擎：

#查看默认引擎show varaibles like 'storage_engine';#查看某个表的引擎show create table t1;#查看当前数据库支持的引擎show engines;

修改表的引擎

alter table t1 ENGINE = innodb

7.2 各种引擎的特性

7.2.1 MyISAM

• 不支持外键，不支持事务，访问速度快，占用内存和空间小，批量插入速度快，锁机制为：表锁。
• 对事务没有要求或者以SELECT、INSERT操作为主的应用基本可以使用。

每个MyISAM表在磁盘上存储成3个文件，文件名都和表名相同，扩展名分别是：
- .frm(存储表定义)
- .MYD(MYData，存储数据)
- .MYI(MYIndex，存储索引)

数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。

要指定索引文件和数据文件的路径，需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定。

MyISAM表可能毁损坏，原因多种多样，可以通过CHECK TABLE指令检查表，REPAIR TABLE修复一个表。

MyISAM的表支持3中不同的存储格式：
- 静态表（默认）
- 动态表
- 压缩表

1）静态表中的字段是非变长字段，每个数据都是固定的长度，存储迅速，容易缓存，故障易修复。缺点是占用空间比动态多。静态表的数据在存储时会按照定义的宽度补足空格，但是在应用访问数据时候不会得到这些空格，因此在插入时如果有尾部的空格也会丢失。

2）动态表包含变长字段，记录不是固定长度，占用空间少，易产生碎片，需要定期执行OPTIMIZE TABLE语句改善性能，并且在出现故障时恢复相对比较困难。

查看表数据的位置，打开my.ini文件，查找datadirectory参数找到位置。

7.2.2 InnoDB

提供提交、回滚和奔溃恢复能力的事务安全，但是处理效率较前者较低，占用更多的磁盘空间以保留数据和索引。

InnoDB特性
1、自动增长列
AUTO_INCREMENT，默认从1开始。
使用LIST_INSERT_ID()函数查看最后插入的id。

• 对于InnoDB，自动增长列必须是索引，如果是组合索引，也必须是组合索引的第一列。
• 对于MyISAM，自动增长列可以是组合索引的其他列。

2、外键约束
只有InnoDB支持，MyISAM不支持。

3、存储方式
InnoDB存储表和索引有以下两种方式：
- 使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。
- 使用多表空间存储，表结构存储在.frm文件，表数据和索引存储在.ibd文件中。

7.2.3 MEMORY

使用存在于内存中的内容来创建表，每个MEMORY表实际对应一个.frm文件。索引方式有HASH索引和BTREE索引。

7.3 选择合适的存储引擎

• MyISAM：读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高。锁机制是表锁。
• InnoDB：用于事务，支持外键。较多的更新和删除。锁机制是行锁。有效的降低了删除和更新导致的锁定，还可以确保事务的完整提交和回滚。对于数据准确性要求比较高的系统是合适的选择。
• MEMORY：表数据保存在内存中，提供极快的访问，缺陷是大小有限制。用的较少。

---

8 、合适数据类型的选取

8.1 CHAR和VARCHAR

CHAR固定长度，VARCHAR

CHAR属于固定长度，处理速度相较VARCHAR的可变长度要快很多，缺点是浪费空间。

随着MySql的发展，VARCHAR被运用的越来越多。在不同的数据存储引擎中有不同的适用原则，简单概括如下：
- MyISAM：分静态表和动态表，建议使用固定长度的CHAR。
- MEMORY：使用固定长度的数据行存储，因此CHAR和VARCHAR没差别，最终都会作为CHAR类型处理。
- InnoDB：建议使用VARCHAR。

8.3 浮点数与定点数

浮点数：含有小数部分的数值，float、double来表示。超过精度会四舍五入保存。
定点数：以字符串形式存放，更加精确。decimal。

精确度比较：
float

8.4 日期类型选择

DATE、TIME、DATETIME、TIMESTAMP
日期类型选择小结：
- 根据实际需要选择满足应用的最小存储的日期类型。
- 记录的日月年份比较久远，选择DATETIME而不是TIMESTAMP。
- 与时区有关的最好使用TIMESTAMP，只有它能与时区对应。

8.5 小结

• 字符类型，根据存储引擎进行相应的选择。
• 对精度要求较高的应用中，尽量使用定点数来存储数值，保证结果的准确性。
• 对含有TEXT和BLOB字段的表中，如果经常作删除和修改记录的操作，要定时执行OPTIMIZE TABLE功能对表进行碎片整理。
• 日期类型，按需选择。

9 、字符集

最常用的有UTF-8。
UTF-8：
- 变长编码，1~4字节编码。
- 数据库需要大量的字符运算，如排序、比较，那么选择定长字符集可能更好，定长字符集的速度更快。

MySql与ORACLE数据库在字符集上的区别：

MySQL：不同数据库、不同表、不同字段可以选择不同的字符集。
ORACLE：同一数据库。

9.5.1 校对规则

每个字符集对应多种校对规则，存在于information_schema数据库中的COLLATIONS表中。

校验规则命名规则：
- -ci结尾，表大小写不敏感。如utf8_general_ci。
- -cs结尾，表大小写敏感。
- -bin结尾，表比较的是给予字符编码的值而与具体语言无关。

10、索引的设计与使用

10.1 索引概述

建立索引是提高SELECT操作性能的最佳途径。根据存储引擎可以定义每个表达最大索引数和最大索引长度。每种存储引擎对每个表至少支持16个索引，总索引长度至少为256字节，大多数存储引擎有更高的限制。

MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引。

MyISAM存储引擎的索引，前缀长度可以达到1000**字节**长。
InnoDB存储引擎的表，索引的前缀长度最长是767**字节**。

MyISAM支持全文本(FULLTEXT)索引，只限于CHAR、VARCHAR和TEXT列。
MyISAM还支持空间类型索引，索引字段必须为空。

默认情况下，MEMORY引擎默认使用HASH索引，也支持BTREE索引。

#创建索引CREATE INDEX index_name ON table_name (col_name(length));#删除索引DROP INDEX index_name ON table_name;

10.2 索引设计原则

• 搜索的索引列，不一定是索要选择的列，而是出现在WHERE条件之后的列。
• 使用唯一索引，某列中数值的分布基数越大，索引的效果越好，因为索引的唯一性越好。举例来说，对性别列进行索引的效果就不好，因为其唯一性差，相同索引多。
• 使用短索引，如果对字符串索引，指定索引长度可以节省索引空间，并且查询更快。
• 不要过度索引。索引会额外占用磁盘空间，修改表的同时，必须对索引进行更新。
• 对于InnoDB引擎的表，记录会默认按照一定的顺序保存，1、如果存在明确定义的主键，则按照逐渐的顺序保存；2、如果没有主键，但是有唯一索引，则按照唯一索引的顺序保存。3、两者皆无，自动生成一个内部列，按照该列的顺序保存。按照主键索引是最快的，因此，InnoDB表应尽量自己指定主键。另外，InnoDB表的普通索引都会保存主键的键值，所以主键应尽量选择较短的数据类型，可以有效减少磁盘占用，提高索引的缓存效果。

10.3 BTREE索引和HASH索引

MEMORY引擎的表可以选择使用BTREE或者HASH索引，两种不同类型的索引各有不同的适用范围。HASH索引注意事项如下：
- 只用于使用=或者<=>操作符的等式比较。
- 优化器不能使用HASH索引来加速ORDER BY操作。
- MySQL不能确定在两个值之间的范围，如果将MyISAM表改为HASH索引的MEMORY表，会影响一些查询的效率。
- 只能使用整个关键字来查找一行。（这两条是由HASH值得特性决定的）

而是用BTREE索引，当使用<,>,>=,<=,BETWEEN,!=,<>,LIKE操作符时，都可以使用相关列上的索引。

#下列语句适用于BTREE索引和HASH索引，=SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);#下列语句只适用于BTREE索引SELECT * FROM t1 WHERE key_col >1 AND key_col <10;SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'lisa' AND 'lucy';

了解了BTREE索引和HASH索引的区别之后，在编写SELECT语句时应特别注意WHERE条件能否用于当前的索引类型。

11、视图

12、存储过程和函数

MySQL从5.0版本开始支持过程和函数。

12.1 存储过程和函数

定义：多条SQL语句的集合。
不同：
- 函数：必须有返回值。参数类型只能是IN
- 过程：可以没有返回值。参数类型可以是IN、OUT、INOUT

12.2 相关操作

首先确认当前用户是否具备
- 创建存储过程或者函数的权限：CREATE ROUTINE。
- 修改或者删除存储过程或者函数的权限：ALTER ROUTINE。
- 执行的权限：EXECUTE。

Ref：
http://www.cnblogs.com/chenpi/p/5136483.html

13、触发器

14、事务控制和锁定语句

MyISAM和MEMORY支持表级锁定。
InnoDB支持行级锁定。

14.1 LOCK TABLE和UNLOCK TABLE

session1:

#锁定表lock table cities read;#当前session可以查询select * from cities;#释放锁unlock tables;

session2:

#此时被session1锁定，可以查询，不能修改select * from cities;#修改操作被阻塞,释放锁之后可以操作。update cities set city = 'new city' where id = 5;

14.2 事务控制

START TRANSACTION

COMMIT|ROLLBACK

CHAIN、RELEASE：CHIAN会立即开启一个新事务，并且和之前的事务具有相同的隔离级别，RELEASE则会断开和客户端的连接。

在锁表期间，START TRANSACTION会造成一个隐含的UNLOCK TABLES被执行。因此在同一事务中，最好统一表的存储引擎。

和Oracle的事务管理相同，所有的DDL操作是不能回滚的，并且部分的DDL语句会造成隐式的提交。

SAVEPOINT可以指定回滚事务的一个部分。

START TRANSACTION;SAVEPOINT sp1;...SAVEPOINT sp2;...ROLLBACK TO sp1|sp2;COMMIT;

15、SQL安全问题

注意注入问题。

16、SQL Mode及相关问题

SQL模式的作用：
- 通过设置SQL Mode，可以完成不同严格程度的数据检验，有效的保证数据的准确性。
- 通过设置SQL Mode为ANSI模式，保证大多数SQL符合标准的SQL语法，在不同的数据库之间迁移时，不需要做大的改变。
- 在不同数据库迁移之前，通过修改SQL Mode可以更方便的进行迁移。

查询默认的SQL Mode的指令是：SELECT @@sql_mode;
修改Mode的指令是：SET [SESSION|GLOBAL] sql_mode = 'modes';

mysql> select @@sql_mode;#此为默认模式，简单模式+-------------------------------------------------------------+| @@sql_mode                                                  |+-------------------------------------------------------------+| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |+-------------------------------------------------------------+1 row in set (0.00 sec)#通过set指令修改模式。STRICT：严格模式mysql> set session sql_mode = 'strict_trans_tables';Query OK, 0 rows affected (0.00 sec)mysql> select @@sql_mode;+---------------------+| @@sql_mode          |+---------------------+| STRICT_TRANS_TABLES |+---------------------+1 row in set (0.00 sec)

严格模式具备更加严格的数据验证，比如对超过长度的字符串，会抛出ERROR，而简单模式会进行截取并抛出WARNNING，通过show warnnings可以查看警告信息。

16.3 常用的Mode

常用的就三种：
- ANSI：等同于REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE和ANSI组合模式，这种模式使语法和行为更加符合标准的SQL。
- STRICT_TRANS_TABLES：适用于事务表和非事务表，是严格模式，不允许非法日期，不允许超过字段定义的长度，返回错误而非警告。
- TRADITIONAL：严格模式，用于事务和非事务，用于事务表时，只要出现错误立即回滚。

以上三种Mode都是一些原子Mode的组合。类似于角色和权限的关系。

16.3 SQL Mode在数据库迁移中的使用

针对不同的数据库，可以使用以下的集中Mode：
- DB2
- MAXDB
- MSSQL
- ORACLE
- POSTGRESQL

可以设置SQL Mode为NO_TABLE_OPTIONS模式，这样将去掉show create table中的engine字样。

17、MySQL分区

分区指：将数据库分解为多个更小的、更容易管理的部分，以获得：
- 存储更多数据
- 优化查询
- 删除分区实现快速删除数据。
- 跨多个磁盘查询，获取更大的查询吞吐量。

17.1 分区概述

分区引入了分区键（partition key）的概念，分区键用于根据某个区间值（或者范围值）、特定值列表或者HASH函数值执行数据的聚集，让数据分布在不同的分区中。

通过SHOW VARIABLES LIKE '%partitiom%';查看当前MySQL是否支持分区。

创建分区的语句：

CREATE TABLE emp(empid INT,salary DECIMAL(7,2),birth_date DATE)) ENGINE = INNODBPARTITION BY HASH (MONTH(birth_date))#HASH分区，按照birth的year的hash值分区。PARTITIONS 6;#分区数量

注意：MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区；反过来也是一样，不能只对索引分区而不对表分区。

17.2 分区类型

分区类型主要有以下几种：
- RANGE分区：基于一个给定连续区间范围，把数据分配到不同的分区。从属于连续区间。
- LIST分区：类似RANGE。从属于枚举类型。
- HASH分区：根据给定的分区个数，根据HASH值将数据分配到不同的分区。
- KEY分区：类似HASH分区。

RANGE、LIST、HASH要求分区键必须是INT类型，或者函数返回的INT类型。在MYSQL5.5之后支持了非整数的RANGE、LIST。

不能使用主键/唯一键字段之外的其他字段分区。无论哪一种分区类型，要么没主键，只要有主键，分区键就必须包含主键字段。

17.2.1 Range分区

按照取值范围将数据分成分区，区间要连续并且不能重叠，使用VALUES LESS THAN操作符进行分区定义。

CREATE TABLE emp(...)PARTITION BY RANGE (store_id)(PARTITION p0 VALUES LESS THAN(10),PARTITION p1 VALUES LESS THAN(20),PARTITION p2 VALUES LESS THAN(30))；＃超过30将会报错。使用VALUES LESS THAN MAXVALUES

MySQL5.5支持了RANGE的非整数分区。

RANGE分区适用于一下几种情况:
- 当需要删除过期的数据时，按分区为单位进行删除可以提高效率。
- 查询的时候也可以先确定分区在进行查询。

17.2.2 List分区

与RANGE的区别在于从属于枚举类型。VALUES IN

CREATE TABLE expenses(expense_date DATE NOT NULL,category INT,amount DECIMAL(10,3))PARTITION BY LIST (catefory)(PARTITION p0 VALUES IN (3,5),PARTITION p1 VALUES IN (1,10),...)#类似于java中的switch case语句

注意：将要匹配的任何值都必须能在分区列表中找得到，否则会报错。
MySQL5.5支持非整数的LIST分区。

17.2.3 Columns分区

MySQL5.5引入的分区，解决了RANGE和LIST不支持非整数分区的问题。

可以细分为RANGE Columns分区和LIST Columns分区。区别于RANGE和LIST的是，可以插入多个字段当着主键，其不再支持函数表达式作为分区键了。

CREATE TABLE rc3(a INT,b INT)PARTITION BY RANGE COLUMNS(a,b)(PARTITION p01 VALUES LESS THAN (0,10),PARTITION p02 VALUES LESS THAN (10,10),PARTITION p03 VALUES LESS THAN (10,20),PARTITION p04 VALUES LESS THAN (10,35),PARTITION p05 VALUES LESS THAN (10,MAXVALUE),PARTITION p06 VALUES LESS THAN (MAXVALUE,MAXVALUE),);/*插入：（1，10），存入p02插入：（10，9），存入p02插入：（10，10），存入p03插入：（10，99），存入p05元组的比较依次按照顺序进行比较，其实就是多列比较，按排序结果存放分区。*/

17.2.4 Hash分区

确保数据尽量平均的分配到分区。支持两种分区模式：
- 常规HASH分区：取模算法。
- 线性HASH分区：线性的2的幂运算。

HASH分区不适合需要灵活变动的分区的需求。一旦更改了分区数，之前存入的数据需要进行rehash操作。为了降低分区管理的代价，MySQL提供了线性HASH分区，分区函数不再是取模运算而是一个线性的2的幂的运算法则。

唯一区别是在PARTITION BY字句中添加了LINEAR关键字。

CREATE TABLE emp()PARTITION BY LINEAR HASH(store_id) PARTITION 4;# HASH依然不支持非整数

具体算法不再关心，线性HASH分区的优点是：
- 在分区维护（增加、删除、合并、拆分）时，MySQL能够处理的更加迅速。适用于需要经常变更分区的操作。

缺点是：
- 分布不太均匀。

17.2.5 Key分区

类似于HASH，区别是KEY只允许使用HASH函数，HASH还支持自定义的函数。
PARTITION BY KEY(hash())定义。

CREATE TABLE emp()PARTITION BY KEY (job) PARTITION 4;# PARITITON BY KEY () PARTITION 4;

可以不指定分区键，默认使用主键作为分区键。

17.3 分区管理

提供了添加、删除、重定义、合并、拆分分区的命令。

17.4 小结

分区通过“分而治之”的方法管理数据库表，提高了数据处理的并行度从而能够提升性能。