若有所思&&若有所想

MySQL能够支持建立分区表，利用分区表可以提升数据库的性能，创建分区表如下：

CREATE TABLE t1
( id INT NOT NULL,
uid INT NOT NULL,
PRIMARY KEY (id)
)
PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN(5) ENGINE = INNODB,
PARTITION p1 VALUES LESS THAN(10) ENGINE = INNODB
);

CREATE TABLE t1
( id INT NOT NULL,
uid INT NOT NULL,
PRIMARY KEY (id)
)
PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN(5) ENGINE = MyISAM DATA DIRECTORY=’/tmp’ INDEX DIRECTORY=’/tmp’,
PARTITION p1 VALUES LESS THAN(10) ENGINE = MyISAM DATA DIRECTORY=’/tmp’ INDEX DIRECTORY=’/tmp’
);

分区表对唯一约束有明确的规定，每一个唯一约束必须包含在分区表的分区键（也包括主键约束）。

分区表对存储引擎的限制：MERGE引擎不支持分区，分区表也不支持merge；FEDERATED引擎不支持分区，这限制可能会在以后的版本去掉；CSV引擎不支持分区；BLACKHOLE引擎不支持分区；在NDBCLUSTER引擎上使用分区表，分区类型只能是KEY(or LINEAR KEY) 分区；当升级MYSQL的时候，如果你有使用了KEY分区的表（不管是什么引擎，NDBCLUSTER除外），那么你需要把这个表dumped在reloaded；分区表的所有分区或者子分区的存储引擎必须相同，这个限制也许会在以后的版本取消；不指定任何引擎（使用默认引擎）；所有分区或者子分区指定相同引擎。

分区表对内置函数的限制，分区表中只能使用一下函数：

ABS()、CEILING()、FLOOR()、DAY()、DAYOFMONTH()、DAYOFWEEK()、DAYOFYEAR()、DATEDIFF()、EXTRACT()、HOUR()、MICROSECOND()、MINUTE()、MOD()、MONTH()、QUARTER()、SECOND()、TIME_TO_SEC()、TO_DAYS()、WEEKDAY()、YEAR()、YEARWEEK()

分区表的其他限制：

1. 对象限制：下面这些对象在不能出现在分区表达式

Stored functions, stored procedures, UDFs, or plugins.
Declared variables or user variables.

2. 运算限制：支持加减乘等运算出现在分区表达式，但是运算后的结果必须是一个INT或者NULL。 |, &, ^, <<, >>, , ~ 等不允许出现在分区表达式。

sql_mode限制：官方强烈建议你在创建分区表后，永远别改变mysql的sql_mode。因为在不同的模式下，某些函数或者运算返回的结果可能会不一样。

3. Performance considerations。(省略)

4. 分区数量限制：

最多支持1024个分区，包括子分区。

5. 当你建立分区表包含很多分区但没有超过1024限制的时候，如果报错 Got error 24 from storage engine，那意味着你需要增大open_files_limit参数。

6. 不支持外键。MYSQL中，INNODB引擎才支持外键。

7. 不支持FULLTEXT indexes（全文索引），包括MYISAM引擎。

8. 不支持spatial column types。

9. 临时表不能被分区。

10. log table不支持分区

11. 分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是当分区类型为KEY分区的时候，可以使用其他类型的列作为分区键（ BLOB or TEXT 列除外）。

12. 分区键不能是一个子查询。 A partitioning key may not be a subquery, even if that subquery resolves to an integer value or NULL

13. 只有RANG和LIST分区能进行子分区。HASH和KEY分区不能进行子分区。

14. 分区表不支持Key caches。

15. 分区表不支持INSERT DELAYED。

16. DATA DIRECTORY和INDEX DIRECTORY参数在分区表将被忽略。这个限制应该不存在了：

CREATE TABLE t1
( id INT NOT NULL,
uid INT NOT NULL,
PRIMARY KEY (id)
)
PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN(5) ENGINE = MyISAM DATA DIRECTORY=’/tmp’ INDEX DIRECTORY=’/tmp’,
PARTITION p1 VALUES LESS THAN(10) ENGINE = MyISAM DATA DIRECTORY=’/tmp’ INDEX DIRECTORY=’/tmp’
);

17. 分区表不支持mysqlcheck和myisamchk。在5.1.33版本中已经支持mysqlcheck和myisamchk。

18. 分区表的分区键创建索引，那么这个索引也将被分区。分区键没有全局索引一说。

19. 在分区表使用ALTER TABLE … ORDER BY，只能在每个分区内进行ORDER BY。

本篇文章绝大部分来自互联网，非原创，囧。引用地址点击此处。

MySQL从4.1开始支持事务处理，事务是构成多用户使用数据库的基础。

事务都应该具备ACID特征。所谓ACID是Atomic（原子性），Consistent（一致性），Isolated（隔离性），Durable（持续性）四个词的首字母所写，下面以“银行转帐”为例来分别说明一下它们的含义：

原子性：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。换句话说，事务是不可分割的最小单元。比如：银行转帐过程中，必须同时从一个帐户减去转帐金额，并加到另一个帐户中，只改变一个帐户是不合理的。

一致性：在事务处理执行前后，数据库是一致的。也就是说，事务应该正确的转换系统状态。比如：银行转帐过程中，要么转帐金额从一个帐户转入另一个帐户，要么两个帐户都不变，没有其他的情况。

隔离性：一个事务处理对另一个事务处理没有影响。就是说任何事务都不可能看到一个处在不完整状态下的事务。比如说，银行转帐过程中，在转帐事务没有提交之前，另一个转帐事务只能处于等待状态。

持续性：事务处理的效果能够被永久保存下来。反过来说，事务应当能够承受所有的失败，包括服务器、进程、通信以及媒体失败等等。比如：银行转帐过程中，转帐后帐户的状态要能被保存下来。

要使用MySQL的事务处理，首先把自动提交关闭：

SET @@AUTOCOMMIT=0;

用户输入一条新SQL语句以后，数据库立即就被修改。但是这个修改并非永久化。用户可以通过ROLLBACK撤销这一条修改，或者用COMMIT语句把修改持久化。

当一个应用程序的第一条SQL语句或者在COMMIT或ROLLBACK后的第一条SQL语句执行后，一个新的事务也就开始了。另外还可以使用一条START TRANSACTION语句显示启动一个事务，或者使用BEGIN WORK语句：

START TRANSACTION;
BEGIN WORK;

COMMIT语句是提交事务的意思，包含了AND CHAIN和AND RELEASE子句。前者是事务提交以后，立刻启动一个新事务；后者是当事务终止以后，立刻切断客户端和服务器端的联系：

COMMIT WORK AND CHAIN;

撤销事务是用ROLLBACK语句：

ROLLBACK WORK;

若是设置了回滚点，ROLLBACK语句可以回滚到指定的时间点上：

SAVEPOINT identifier;
ROLLBACK TO SAVEPOINT identifier;
RELEASE SAVEPOINT identifier;

RELEASE SAVEPOINT是用来删除回滚点的。

每一个事务都有一个所谓的隔离级，它定义了用户批次之间隔离和交互的程度。MySQL支持四个隔离级：序列化（SERIALIZABLE）、可重复读（REPEATABLE READ）、提交读（READ COMMITTED）、未提交读（READ UNCOMMITTED）。

未提交读（READ UNCOMMITTED）：允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。

提交读（READ COMMITTED）：允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

可重复读（REPEATABLE READ）：禁止不可重复读取和脏读取，但是有时可能出现幻影数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

序列化（SERIALIZABLE）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

MySQL提供了表锁定、页锁定、行锁定三个级别的锁定。MyISAM支持的是表锁定，而InnoDB则可以提供行锁定的级别：

因为MyISAM不支持InnoDB独有的COMMIT和ROLLBACK语法。MySQL提供了LOCK TABLE锁定表：

LOCK TABLE XS READ;
LOCK TABLE XS WRITE;

前者是读锁定，后者是写锁定，解锁则是：

UNLOCK TABLES

;

众所周知，但凡需要等到MySQL数据库都需要用已有的用户名和用户的密码。而MySQL的用户信息是存放在系统自带的mysql数据库中的user表中。如果创建了一个新用户，那么这个表中就会有一个新记录。MySQL的安全系统很灵活，它允许以多种不同方式设置用户权限，比如创建用户：

CREATEUSER ‘king’@'localhost’ IDENTIFIED BY ‘queen’;

IDENTIFIED BY后面定义的是新用户密码。至于删除用户：

DROP USER ‘king’@'localhost‘;

设置用户属性包括两部分，更改用户名和修改密码：

RENAME USER ‘king’@'localhost‘ TO ‘king2′@’localhost‘;
SET PASSWORD FOR ‘king’@'localhost‘=PASSWORD(‘queen1′);

GRANT与REVOKE是一对权限设置的SQL命令，GRANT用于授予权限，而REVOKE则是回收权限。比如授予king用户查询XS表的权限：

GRANT SELECT ON XS TO king@localhost;

授予king用户更新Employees表的Name和Sex的权限：

GRANT UPDATE(Name, Sex) ON Employees TO king@localhost;

MySQL中授予表和列的权限如下：

SELECT、INSERT、DELETE、UPATE、REFERENCES、CREATE、ALTER、INDEX、DROP、ALL

而授予数据库的则多出以下几个：

CREATE TEMPORARY TABLES、CREATE VIEW、SHOW VIEW、CREATE ROUTINE、ALTER ROUTINE、EXECUTE ROUTINE、LOCK TABLES

比如授予YGGL的查询权限给king用户：

GRANT SELECT ON YGGL.* TO king@localhost;

GRANT后面还可以更加多的子句：

WITH GRANT OPTION 授予一用户的权限同样适用于其他用户
WITH MAX_QUERIES_PER_HOUR 每小时查询数据库的次数
WITH MAX_CONNECTIONS_PER_HOUR 每小时连接的次数
WITH MAX_UPDATES_PER_HOUR 每小时更新的次数
WITH MAX_USER_CONNECTIONS 每小时最大的用户连接数

REVOKE是回收权限的意思，用法其实和GRANT近乎一样：

REVOKE SELECT ON XS FROM king@localhost;

MySQL还支持表维护语句：

更新表中的索引的可压缩性：

ANALYZE TABLE Employees:
SHOW INDEX FROM XS;

检查表是否存在错误的语句，后续还可以添加QUICK、FAST、MEDIUM、EXTENDED、CHANGED选项：

CHECK TABLE Salary FAST;

获得校验和的命令，后面可以添加QUICK和EXTENDED选项：

CHECKSUM TABLE Salary:

由于长期读写表，表会产生很多碎片，OPTIMIZE就是用来减少碎片提高速度的：

OPTIMIZE TABLE Salary;

修复表的错误：

REPAIR TABLE Salary;