mysql表分区使用及详细介绍

一、分区概念

分区是将一个表分成多个区块进行 *** 作和保存，从而降低每次 *** 作的数据，提高性能。而对于应用来说则是透明的，从逻辑上看只有一张表，但在物理上这个表可能是由多个物理分区组成的，每个分区都是独立的对象，可以进行独立处理。

二、分区作用

1.可以逻辑数据分割，分割数据能够有多个不同的物理文件路径。

2.可以存储更多的数据，突破系统单个文件最大限制。

3.提升性能，提高每个分区的读写速度，提高分区范围查询的速度。

4.可以通过删除相关分区来快速删除数据

5.通过跨多个磁盘来分散数据查询，从而提高磁盘I/O的性能。

6.涉及到例如SUM()、COUNT()这样聚合函数的查询，可以很容易的进行并行处理。

7.可以备份和恢复独立的分区，这对大数据量很有好处。

三、分区能支持的引擎

MySQL支持大部分引擎创建分区，入MyISAM、InnoDB等；不支持MERGE和CSV等来创建分区。同一个分区表中的所有分区必须是同一个存储引擎。值得注意的是，在MySQL8版本中，MyISAM表引擎不支持分区。

四、确认MySQL支持分区

从MySQL5.1开始引入分区功能，可以如下方式查看是否支持：

老版本用：SHOW VARIABLES LIKE '%partition%'

新版本用：show plugins

五、分区类型

1. RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

例如，可以将一个表通过年份划分成两个分区，2001 -2010年、2011-2020。

2. LIST分区：类似于RANGE分区，LIST是列值匹配一个离散值集合中的某个值来进行选择。

比如 根据字段 把值为1、3、5的放到一起，2、4、6的另外放到一起 等等...

3. HASH分区：基于用户定义的表达式的返回值来进行选择分区，该表达式使用将要插入到表中的这些行的列值来进行计算，这个函数必须产生非负整数值。

通过HASH运算来进行分区，分布的比较均匀

4. KEY分区：类似于按HASH分区，由MySQL服务器提供其自身的哈希函数。

按照KEY进行分区类似于按照HASH分区

六、分区创建注意事项

1. 如果表中存在primary key 或者 unique key 时，分区的列必须是paimary key或者unique key的一个组成部分，也就是说，分区函数的列只能从pk或者uk这些key中取子集

2. 如果表中不存在任何的paimary key或者unique key，则可以指定任何一个列作为分区列

3. 5.5版本前的RANGE、LIST、HASH分区要求分区键必须是int；MySQL5.5及以上，支持非整形的RANGE和LIST分区，即：range columns 和 list columns (可以用字符串来进行分区)。

七、分区命名

1. 分区的名字基本上遵循其他MySQL 标识符应当遵循的原则，例如用于表和数据库名字的标识符。应当注意的是， 分区的名字是不区分大小写的 。

2. 无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录。

八、 创建分区

1. RANGE分区：

CREATE TABLE `test01` (

`dayid` int(11) DEFAULT NULL,

`mac` varchar(32) NOT NULL DEFAULT '',

`dtype` varchar(50) NOT NULL DEFAULT ''

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY LIST (dayid)

(PARTITION p20171205 VALUES IN (20171205) ENGINE = InnoDB,

PARTITION p20171204 VALUES IN (20171204) ENGINE = InnoDB,

PARTITION p20171206 VALUES IN (20171206) ENGINE = InnoDB,

PARTITION p20171207 VALUES IN (20171207) ENGINE = InnoDB) */

解读：以上为 uuid小于5时放到p0分区下，uuid大于5且小于10放到p1分区下，uuid大于10且小于15放到p2分区下，uuid大于15 一直到最大值的存在p3分区下

2. LIST分区：

CREATE TABLE tbl_test (

uuid INT NOT NULL,

title VARCHAR(20)

)

)

PARTITION BY List (uuid) (

PARTITION p0 VALUES in (1,2,3,5),

PARTITION p1 VALUES in (7,9,10),

PARTITION p2 VALUES in (11,15)

)

)

解读：以上为uuid 等于1/2/3/5时放到p0分区，7/9/10放到p1分区，11/15放到p2分区。当时用insert into时 如果uuid的值不存在p0/p1/p2分区时，则会插入失败而报错。

3. HASH分区:

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE分区和LIST分区中必须明确指定一个指定的列值或列值集合以指定应该保存在哪个分区中。而在HASH分区中，MySQL会自动完成这些工作，要做的只是基于将要被哈希的列值指定一个表达式，以及指定被分区的表将要被分割成的分区数量，如：

CREATE TABLE tbl_test (

uuid INT NOT NULL,

title VARCHAR(20)

))

PARTITION BY HASH (uuid) (

PARTITIONS 3

))

解读：MySQL自动创建3个分区，在执行insert into时，根据插入的uuid通过算法来自动分配区间。

注意：

(1) 由于每次插入、更新、删除一行，这个表达式都要计算一次，这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时影响大量行的运算（例如批量插入）的时候。

(2) 最有效率的哈希函数是只对单个表列进行计算，并且它的值随列值进行一致的增大或减小，因为这考虑了在分区范围上的“修剪”。也就是说，表达式值和它所基于的列的值变化越接近，就越能有效地使用该表达式来进行HASH分区。

3.1：线性HASH分区

线性HASH分区在“PARTITION BY”子句中添加“LINEAR”关键字。

线性HASH分区的有点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有及其大量数据的表。它的缺点在于各个分区间数据的分布不大可能均衡。

4. KEY分区

类似于HASH分区，HASH分区允许用户自定义的表达式，而KEY分区则不允许使用用户自定义的表达式；HASH分区只支持整数分区，KEY分区支持除了blob和text类型之外的其他数据类型分区。

与HASH分区不同，创建KEY分区表的时候，可以不指定分区键，默认会选择使用主键或唯一键作为分区键，没有主键或唯一键，就必须指定分区键。

CREATE TABLE tbl_test (

uuid INT NOT NULL,

title VARCHAR(20)

))

PARTITION BY LINEAR Key (uuid)

PARTITIONS 3

解读：根据分区键来进行分区

5. 子分区

子分区是分区表中，每个分区的再次分割，适合保存非常大量的数据。

CREATE TABLE tbl_test (

registerTime Date

))

PARTITION BY GANGE(YEAR(registerTime))

SUBPARTITION BY HASH (TO_DAYS(registerTime))

SUBPARTITIONS 2

(

PARTITION p0 VALUES LESS THAN (2017),

PARTITION p1 VALUES LESS THAN (2020),

PARTITION p2 VALUES LESS THAN MAXVALUE

)

解读：主分区使用RANGE按照年来进行分区，有3个RANGE分区。这3个分区中又被进一步分成了2个子分区，实际上，整个表被分成了3 * 2 = 6个分区。每个子分区按照天进行HASH分区。小于2017的放在一起，2017-2020的放在一起，大于2020的放在一起。

注意：

(1) 在MySQL5.1中，对于已经通过RANGE或LIST分区了的表在进行子分区是可能的。子分区既可以使用HASH分区，也可以使用KEY分区。这也被称为复合分区。

(2) 每个分区必须有相同数量的子分区。

(3) 如果在一个分区表上的任何分区上使用SUBPARTITION来明确定义任何子分区，那么就必须定义所有的子分区。

(4) 每个SUBPARTITION子句必须包含（至少）子分区的一个名字。

(5) 在每个子分区内，子分区的名字必须是惟一的，目前在整个表中，也要保持唯一。例如：

PARTITION BY RANGE(YEAR(registerTime))

SUBPARTITION BY HASH(TO_DAYS(registerTime))

(

PARTITION p0 VALUES LESS THAN (2017) (

SUBPARTITION s0,

SUBPARTITION s1

),

PARTITION p1 VALUES LESS THAN (2020) (

SUBPARTITION s2,

SUBPARTITION s3

),

PARTITION p2 VALUES LESS THAN MAXVALUE (

SUBPARTITION s4,

SUBPARTITION s5

)

)

子分区可以用于特别大的表，可以在多个磁盘间分配数据和索引。例如：

SUBPARTITION s0

DATA DIRECTORY = '/disk0/data'

INDEX DIRECTORY = '/disk0/idx'

,

,

SUBPARTITION s1

DATA DIRECTORY = '/disk1/data'

INDEX DIRECTORY = '/disk1/idx'

九、MySQL分区处理NULL值的方式

MySQL中的分区禁止空值NULL上没有进行处理，无论它是一个列值还是一个用户定义表达式的值，一般而言，在这种情况下MySQL把NULL视为0。如果你希望回避这种做法，你应该在设计表时声明列“NOT NULL”。

十、分区管理概述

可以对分区进行添加、删除、重新定义、合并或拆分等管理 *** 作。

① RANGE和LIST分区的管理

1. 删除分区语句如：alter table tbl_test drop partition p0

注意：

(1) 当删除了一个分区，也同时删除了该分区中所有的数据。

(2) 可以通过show create table tbl_test来查看新的创建表的语句。

(3) 如果是LIST分区的话，删除的数据不能新增进来，因为这些行的列值包含在已经删除了的分区的值列表中。

2. 添加分区语句如：alter table tbl_test add partition(partition p3 values less than(50))

注意：

(1) 对于RANGE分区的表，只可以添加新的分区到分区列表的最高端。

(2) 对于LIST分区的表，不能添加已经包含在现有分区值列表中的任意值。

3. 如果希望能不丢失数据的条件下重新定义分区，可以使用如下语句：

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition_definitions)

(1) 拆分分区如：

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO(partition s0 values less than(5),partition s1 values less than(10))

或者如：

ALTER TABLE tbl_name REORGANIZE PARTITION p0 INTO(partition s0 values in(1,2,3), partition s1 values in(4,5))

(2) 合并分区如：ALTER TABLE tbl_name REORGANIZE PARTITION s0,s1 INTO(partition p0 values in(1,2,3,4,5))

4. 删除所有分区，但保留数据，形式：ALTER TABLE tbl_name remove partitioning

② HASH和KEY分区的管理

1. 减少分区数量语句如：ALTER TABLE tbl_name COALESCE PARTITION 2

2. 添加分区数量语句如：ALTER TABLE tbl_name add PARTITION partitions 2

③ 其他分区管理语句

1. 重建分区 ：类似于先删除保存在分区中的所有记录，然后重新插入它们，可用于整理分区碎片。如：ALTER table tbl_name REBUILD PARTITION p2,p3

2. 优化分区 ：如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB或TEXT类型的列）做了许多修改，可以使用 ALTER TABLE tbl_name OPTIMIZE PARTITION来收回没有使用的空间，并整理分区数据文件的碎片。如：ALTER TABLE tbl_name OPTIMIZE PARTITION p2,p3

3. 分析分区 ：读取并保存分区的键分布，如：ALTER TABLE tbl_name ANALYZE PARTITION p2,p3

4. 检查分区 ：检查分区中的数据或索引是否已经被破坏，如：ALTER TABLE tbl_name CHECK PARTITION p2,p3

5. 修补分区 ：修补被破坏的分区，如：ALTER TABLE tbl_name REPAIR PARTITION p2,p3

十、查看分区信息

1. 查看分区信息：select * from information_schema.partitions where table_schema='arch1' and table_name = 'tbl_test' G

2. 查看分区上的数据：select * from tbl_test partition(p0)

3. 查看MySQL会 *** 作的分区：explain partitions select * from tbl_test where uuid = 2

十一、 局限性

1. 最大分区数目不能超过1024，一般建议对单表的分区数不要超过50个。

2. 如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内。

3. 不支持外键。

4. 不支持全文索引，对分区表的分区键创建索引，那么这个索引也将被分区。

5. 按日期进行分区很合适，因为很多日期函数可以用。但是对字符串来说合适的分区函数不太多。

6. 只有RANGE和LIST分区能进行子分区，HASH和KEY分区不能进行子分区。

7. 临时表不能被分区。

8. 分区表对于单条记录的查询没有优势。

9. 要注意选择分区的成本，没插入一行数据都需要按照表达式筛选插入的分区。

10. 分区字段尽量不要可以为null

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。本章我们着重讨论MySQL锁机制的特点，常见的锁问题，以及解决MySQL锁问题的一些方法或建议。

MySQL锁概述

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL这3种锁的特性可大致归纳如下。

开销、加锁速度、死锁、粒度、并发性能

l 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

l 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

l 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。这一点在本书的“开发篇”介绍表类型的选择时，也曾提到过。下面几节我们重点介绍MySQL表锁和 InnoDB行锁的问题，由于BDB已经被InnoDB取代，即将成为历史，在此就不做进一步的讨论了。

MyISAM表锁

MyISAM存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。随着应用对事务完整性和并发性要求的不断提高，MySQL才开始开发基于事务的存储引擎，后来慢慢出现了支持页锁的BDB存储引擎和支持行锁的InnoDB存储引擎（实际 InnoDB是单独的一个公司，现在已经被Oracle公司收购）。但是MyISAM的表锁依然是使用最为广泛的锁类型。本节将详细介绍MyISAM表锁的使用。

查询表级锁争用情况

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql>show status like 'table%'

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Table_locks_immediate | 2979 |

| Table_locks_waited| 0 |

+-----------------------+-------+

2 rows in set (0.00 sec))

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

MySQL表级锁的锁模式

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。锁模式的兼容性如表20-1所示。

表20-1MySQL中的表锁兼容性请求锁模式

是否兼容

当前锁模式

None

读锁

写锁

读锁

是

是

否

写锁

是

否

否

可见，对MyISAM表的读 *** 作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写 *** 作，则会阻塞其他用户对同一表的读和写 *** 作；MyISAM表的读 *** 作与写 *** 作之间，以及写 *** 作之间是串行的！根据如表20-2所示的例子可以知道，当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新 *** 作。其他线程的读、写 *** 作都会等待，直到锁被释放为止。

表20-2 MyISAM存储引擎的写阻塞读例子session_1

session_2

获得表film_text的WRITE锁定

mysql>lock table film_text write

Query OK, 0 rows affected (0.00 sec)

当前session对锁定表的查询、更新、插入 *** 作都可以执行：

mysql>select film_id,title from film_text where film_id = 1001+---------+-------------+

| film_id | title |

+---------+-------------+

| 1001| Update Test |

+---------+-------------+

1 row in set (0.00 sec)

mysql>insert into film_text (film_id,title) values(1003,'Test')Query OK, 1 row affected (0.00 sec)

mysql>update film_text set title = 'Test' where film_id = 1001Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

其他session对锁定表的查询被阻塞，需要等待锁被释放：

mysql>select film_id,title from film_text where film_id = 1001等待

释放锁：

mysql>unlock tables

Query OK, 0 rows affected (0.00 sec)

等待

Session2获得锁，查询返回：

mysql>select film_id,title from film_text where film_id = 1001+---------+-------+

| film_id | title |

+---------+-------+

| 1001| Test |

+---------+-------+

1 row in set (57.59 sec)

如何加表锁

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新 *** 作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。在本书的示例中，显式加锁基本上都是为了方便而已，并非必须如此。

给MyISAM表显示加锁，一般是为了在一定程度模拟事务 *** 作，实现对某一时间点多个表的一致性读取。例如，有一个订单表orders，其中记录有各订单的总金额total，同时还有一个订单明细表order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检查这两个表的金额合计是否相符，可能就需要执行如下两条SQL：

Select sum(total) from orders

Select sum(subtotal) from order_detail

这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，order_detail表可能已经发生了改变。因此，正确的方法应该是：

Lock tables orders read local, order_detail read localSelect sum(total) from orders

Select sum(subtotal) from order_detail

Unlock tables

要特别说明以下两点内容。

? 上面的例子在LOCK TABLES时加了“local”选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录，有关MyISAM表的并发插入问题，在后面的章节中还会进一步介绍。

? 在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询 *** 作，而不能执行更新 *** 作。其实，在自动加锁的情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁（Deadlock Free）的原因。

在如表20-3所示的例子中，一个session使用LOCK TABLE命令给表film_text加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误；同时，另外一个session可以查询表中的记录，但更新就会出现锁等待。

表20-3 MyISAM存储引擎的读阻塞写例子session_1

session_2

获得表film_text的READ锁定

mysql>lock table film_text read

Query OK, 0 rows affected (0.00 sec)

当前session可以查询该表记录

mysql>select film_id,title from film_text where film_id = 1001+---------+------------------+

| film_id | title|

+---------+------------------+

| 1001| ACADEMY DINOSAUR |

+---------+------------------+

1 row in set (0.00 sec)

其他session也可以查询该表的记录

mysql>select film_id,title from film_text where film_id = 1001+---------+------------------+

| film_id | title|

+---------+------------------+

| 1001| ACADEMY DINOSAUR |

+---------+------------------+

1 row in set (0.00 sec)

当前session不能查询没有锁定的表

mysql>select film_id,title from film where film_id = 1001ERROR 1100 (HY000): Table 'film' was not locked with LOCK TABLES其他session可以查询或者更新未锁定的表

mysql>select film_id,title from film where film_id = 1001+---------+---------------+

| film_id | title |

+---------+---------------+

| 1001| update record |

+---------+---------------+

1 row in set (0.00 sec)

mysql>update film set title = 'Test' where film_id = 1001Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

当前session中插入或者更新锁定的表都会提示错误：

mysql>insert into film_text (film_id,title) values(1002,'Test')ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updatedmysql>update film_text set title = 'Test' where film_id = 1001ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated其他session更新锁定表会等待获得锁：

mysql>update film_text set title = 'Test' where film_id = 1001等待

释放锁

mysql>unlock tables

Query OK, 0 rows affected (0.00 sec)

等待

Session获得锁，更新 *** 作完成：

mysql>update film_text set title = 'Test' where film_id = 1001Query OK, 1 row affected (1 min 0.71 sec)Rows matched: 1 Changed: 1 Warnings: 0

当使用LOCK TABLES时，不仅需要一次锁定用到的所有表，而且，同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次，否则也会出错！举例说明如下。

（1）对actor表获得读锁：

mysql>lock table actor read

Query OK, 0 rows affected (0.00 sec)

（2）但是通过别名访问会提示错误：

mysql>select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <>b.last_nameERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES（3）需要对别名分别锁定：

mysql>lock table actor as a read,actor as b readQuery OK, 0 rows affected (0.00 sec)

（4）按照别名的查询可以正确执行：

mysql>select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <>b.last_name+------------+-----------+------------+-----------+| first_name | last_name | first_name | last_name |+------------+-----------+------------+-----------+| Lisa | Tom | LISA | MONROE|+------------+-----------+------------+-----------+1 row in set (0.00 sec)

并发插入（Concurrent Inserts）

上文提到过MyISAM表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入 *** 作的并发进行。

MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

l 当concurrent_insert设置为0时，不允许并发插入。

l 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

l 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

在如表20-4所示的例子中，session_1获得了一个表的READ LOCAL锁，该线程可以对表进行查询 *** 作，但不能对表进行更新 *** 作；其他的线程（session_2），虽然不能对表进行删除和更新 *** 作，但却可以对该表进行并发插入 *** 作，这里假设该表中间不存在空洞。

表20-4 MyISAM存储引擎的读写（INSERT）并发例子session_1

session_2

获得表film_text的READ LOCAL锁定

mysql>lock table film_text read local

Query OK, 0 rows affected (0.00 sec)

当前session不能对锁定表进行更新或者插入 *** 作：

mysql>insert into film_text (film_id,title) values(1002,'Test')ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updatedmysql>update film_text set title = 'Test' where film_id = 1001ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated其他session可以进行插入 *** 作，但是更新会等待：

mysql>insert into film_text (film_id,title) values(1002,'Test')Query OK, 1 row affected (0.00 sec)

mysql>update film_text set title = 'Update Test' where film_id = 1001等待

当前session不能访问其他session插入的记录：

mysql>select film_id,title from film_text where film_id = 1002Empty set (0.00 sec)

释放锁：

mysql>unlock tables

Query OK, 0 rows affected (0.00 sec)

等待

当前session解锁后可以获得其他session插入的记录：

mysql>select film_id,title from film_text where film_id = 1002+---------+-------+

| film_id | title |

+---------+-------+

| 1002| Test |

+---------+-------+

1 row in set (0.00 sec)

Session2获得锁，更新 *** 作完成：

mysql>update film_text set title = 'Update Test' where film_id = 1001Query OK, 1 row affected (1 min 17.75 sec)Rows matched: 1 Changed: 1 Warnings: 0

可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。例如，将concurrent_insert系统变量设为2，总是允许并发插入；同时，通过定期在系统空闲时段执行 OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。有关OPTIMIZE TABLE语句的详细介绍，可以参见第18章中“两个简单实用的优化方法”一节的内容。

Myisam 更适合读取大于写入的业务，同时不支持事物。支持全文搜索。

Innodb 支持事物，效率上比myisam稍慢。不支持全文搜索。

Myism物理文件结构为：

.frm文件： 与表相关的 元数据信息 都存放在frm文件， 包括表结构的定义信息等 。

.myd文件： myisam存储引擎专用，用于存储myisam 表的数据

.myi文件： myisam存储引擎专用，用于存储myisam表的 索引相关信息

Innodb的物理文件结构为：

.frm文件： 与表相关的 元数据信息 都存放在frm文件， 包括表结构的定义信息等 。

.ibd文件和.ibdata文件：

这两种文件都是存放innodb数据的文件，之所以用两种文件来存放innodb的数据，是因为innodb的数据存储方式能够通过配置来决定是使用 共享表空间 存放存储数据，还是用 独享表空间 存放存储数据。

独享表空间 存储方式使用.ibd文件，并且每个表一个ibd文件

共享表空间 存储方式使用.ibdata文件，所有表共同使用一个ibdata文件

觉得使用哪种方式的参数在mysql的配置文件中 innodb_file_per_table

关于删除了数据之后，物理文件大小并没有变化的解释

删除之后还有碎片，通过OPTIMIZE TABLE 命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费 。OPTIMIZE TABLE 命令只对 MyISAM 、 BDB 和 InnoDB 表起作用 。

---