Mysql

SQL base

Sql分类：

DDL： Data Definition Languages，涉及create，drop，alert，主要供数据库管理人员使用
DML： Data Manipulation Language，涉及查询操作，主要供开发人员使用。
DCL： Data Control Language，数据控制语句，用于控制不同数据段直接的许可和

访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

DML连接操作：

外连接分为左连接和右连接：

左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录

DML子查询操作：

某些情况下，当我们查询的时候，需要的条件是另外一个select 语句的结果，这个时候，就

要用到子查询。用于子查询的关键字主要包括 in、not in、=、!=、exists、not exists 等。

DML记录联结操作：

我们经常会碰到这样的应用，将两个表的数据按照一定的查询条件查询出来后，将结果合并

到一起显示出来，这个时候，就需要用 union 和 union all 关键字来实现这样的功能。

Mysql datatype

MySQL 支持所有标准 SQL 中的数值类型，其中包括严格数值类型（INTEGER、SMALLINT、

DECIMAL 和 NUMERIC），以及近似数值数据类型（FLOAT、REAL 和 DOUBLE PRECISION），并在此基础上做了扩展。扩展后增加了 TINYINT、MEDIUMINT 和 BIGINT 这 3 种长度不同的整型，并增加了 BIT 类型，用来存放位数据。

在整数类型中，按照取值范围和存储方式不同，分为tinyint、smallint、mediumint、int、

bigint 这 5 个类型。如果超出类型范围的操作，会发生“Out of range”错误提示。为了避免此

类问题发生，在选择数据类型时要根据应用的实际情况确定其取值范围，最后根据确定的结

果慎重选择数据类型。

对于整型数据，MySQL 还支持在类型名称后面的小括号内指定显示宽度，例如 int(5)表

示当数值宽度小于 5 位的时候在数字前面填满宽度，如果不显示指定宽度则默认为 int(11)。

一般配合 zerofill 使用，顾名思义，zerofill 就是用“0”填充的意思，也就是在数字位数不够

的空间用字符“0”填满。以下几个例子分别描述了填充前后的区别。

可以发现，在数值前面用字符“0”填充了剩余的宽度。大家可能会有所疑问，设置了宽度限制后，如果插入大于宽度限制的值，会不会截断或者插不进去报错？答案是肯定的：不会对插入的数据有任何影响，还是按照类型的实际精度进行保存，这是，宽度格式实际已经没有意义，左边不会再填充任何的“0”字符。

mysql string type

MySQL 中提供了多种对字符数据的存储类型，不同的版本可能有所差异。以 5.0 版本为例，

MySQL 包括了 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等多种字符串类型。

mysql string常用函数

stringfunc

mysql常用数值函数

numfunc

mysql选择合适的数据类型

char和varchar

在 MySQL 中，不同的存储引擎对 CHAR 和 VARCHAR 的使用原则有所不同，这里简单概

括如下。

MyISAM 存储引擎：建议使用固定长度的数据列代替可变长度的数据列。
MEMORY 存储引擎：目前都使用固定长度的数据行存储，因此无论使用 CHAR 或VARCHAR 列都没有关系。两者都是作为 CHAR 类型处理。
InnoDB 存储引擎：建议使用 VARCHAR 类型。对于 InnoDB 数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的 CHAR 列不一定比使用可变长度 VARCHAR 列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR 平均占用的空间多于VARCHAR，因此使用 VARCHAR 来最小化需要处理的数据行的存储总量和磁盘 I/O 是比较好的。

text和blob

一般在保存少量字符串的时候，我们会选择 CHAR 或者VARCHAR；而在保存较大文本时，通常会选择使用 TEXT 或者 BLOB，二者之间的主要差别是BLOB 能用来保存二进制数据，比如照片；而 TEXT 只能保存字符数据，比如一篇文章或者日记。

根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”，那么用 1 个字节来存储的 YEAR 类型完全可以满足，而不需要用 4 个字节来存储的 DATE 类型。这样不仅仅能节约存储，更能够提高表的操作效率。

如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用 DATETIME，而不要使用TIMESTAMP。因为TIMESTAMP 表示的日期范围比DATETIME 要短得多。
如果记录的日期需要让不同时区的用户使用，那么最好使用 TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。

Mysql Storage Engine

storageengine

mysql存储引擎概述

MySQL 5.0 支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、

NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等，其中 InnoDB 和 BDB 提供事务安

全表，其他存储引擎都是非事务安全表。

myIsam

MyISAM 是 MySQL 的默认存储引擎。MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用

这个引擎来创建表。

每个 MyISAM 在磁盘上存储成3 个文件，其文件名都和表名相同，但扩展名分别是：

.frm（存储表定义）；
.MYD（MYData，存储数据）；
.MYI （MYIndex，存储索引）。

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

Innodb

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

外建约束

外键（Foreign Key）是一种用来建立和加强表之间数据连接的约束。它用于确保数据的完整性和一致性。外键约束的作用是将一张表中的字段（称为子表）与另一张表中的主键或唯一键（称为父表）关联起来，从而使子表的数据必须符合父表中的数据约束规则。

MySQL 支持外键的存储引擎只有 InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

innodb存储表和索引有两种方式

使用共享表空间存储，这种方式创建的表的表结构保存在.frm 文件中，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件。
使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm 文件中，但是每个表的数据和索引单独保存在.ibd 中。如果是个分区表，则每个分区对应单独的.ibd文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的IO 均匀分布在多个磁盘上。

mysql Index

索引设计的原则

搜索的索引列不一定是索引选择的列，最合适的索引的列是出现在where子句中的列，或者连接自居中指定的列。
使用唯一索引。考虑某列中值的分布，索引的列的技术越大，索引的效果越好，例如，存放出生日期的列具有不同的值，很容易区分各行。
使用短索引。节省io，比如邮箱的前缀值不同我们就可以取前面的唯一值做索引。
利用最左前缀。比如在创建一个n列的索引时，实际上是创建了mysql中可利用的n个索引。多列索引可一起几个索引的作用，针对于复合索引
1
2
3
4
CREATE INDEX idx_first_last ON users (first_name, last_name);
--mysql will create the several available indexs such as
--(first name)
--(first_name, last_name)
聚簇索引对于 InnoDB 存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的，所以 InnoDB 表尽量自己指定主键，当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为主键，提高查询的效率。另外，还需要注意，InnoDB 表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果。

Btree索引和hash索引

索引用于快速找出在某个列中有一特定值的行。如果不使用索引，MySQL 必须从第 1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引， MySQL 能快速到达一个位置去搜寻数据文件的中间，没有必要看所有数据。

如果一个表有 1000 行，这比顺序读取至少快 100 倍。注意如果需要访问大部分行，顺序读取要快得多，因为此时应避免磁盘搜索。

大多数 MySQL 索引（如PRIMARY KEY、UNIQUE、INDEX 和 FULLTEXT 等）在 BTREE 中存储。只是空间列类型的索引使用 RTREE，并且MEMORY 表还支持 HASH 索引。

视图

视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

视图相对于普通的表的优势主要包括以下几项。

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

事务控制与锁定语句

事务控制

--mysql支持本地事务的方法
START TRANSTACTION | BEGIN[WORK]
COMIIT [WORK]
ROLLBACK [WORK]
SET AUTOCOMMIT{0 | 1}

分布式事务的使用

分布式事务的原理：

在 MySQL 中，使用分布式事务的应用程序涉及一个或多个资源管理器和一个事务管理器。

资源管理器（RM）用于提供通向事务资源的途径。数据库服务器是一种资源管理器。该管理器必须可以提交或回滚由 RM 管理的事务。例如，多台 MySQL 数据库作为多台资源管理器或者几台 Mysql 服务器和几台Oracle 服务器作为资源管理器。
事务管理器（TM）用于协调作为一个分布式事务一部分的事务。TM 与管理每个事务的 RMs 进行通讯。一个分布式事务中各个单个事务均是分布式事务的“分支事务”分布式事务和各分支通过一种命名方法进行标识。

分布式事务的语法

distributedTrasactions

存在的问题：

如果分支事务在达到 prepare 状态时，数据库异常重新启动，服务器重新启动以后，可以继续对分支事务进行提交或者回滚得操作，但是提交的事务没有写 binlog，存在一定的隐患，可能导致使用 binlog 恢复丢失部分数据。如果存在复制的数据库，则有可能导致主从数据库的数据不一致。

--query actor table
select * from actor;
--query rows in set
xa start 'test'
delete from actor from actor_id = 301;
--query ok
select * from actor;
--empty set
xa end 'test'
xa prepare 'test';
query Ok
--when the database throw exception, the query will be bad