MySQL表存储引擎

引擎是什么?

引擎（Engine）是电子平台上开发程序或系统的核心组件。

存储引擎又是什么?

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。

mysql有哪些存储引擎?

我们需要关注的一般都是myisam ,innodb这几种,myisam在mysql 5.1版本前是默认引擎 ,5.5的版本就是innodb是默认存储引擎了.

我们要关注存储引擎的哪几个方面呢?

字段数据类型/锁/索引/事务

锁机制

什么是锁机制?

数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则。

大家想想 如果同时三个连接进来了,一个在执行

a. update table set pos = 1 where id =250;

b.select pos from table where id =250;

c. insert into table (pos) values (12);

那会发生什么事情?

MYISAM存储引擎的锁机制

MySQL有三种锁的级别：页级、表级、行级。

MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

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

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

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

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

字段数据类型

  • TINYINT:一个很小的整数。有符号的范围是-128到127，无符号的范围是0到255。

  • SMALLINT:一个小整数。有符号的范围是-32768到32767，无符号的范围是0到65535。

  • MEDIUMINT[(M)]:一个中等大小整数。有符号的范围是-8388608到8388607，无符号的范围是0到16777215。

  • INT:一个正常大小整数。有符号的范围是-2147483648到2147483647，无符号的范围是0到4294967295。

  • BIGINT:一个大整数。有符号的范围是-9223372036854775808到9223372036854775807，无符号的范围是0到18446744073709551615。

  • FLOAT:一个小(单精密)浮点数字。不能无符号。允许的值是-3.402823466E+38到-1.175494351E-38，0 和1.175494351E-38到3.402823466E+38。M是显示宽度而D是小数的位数。没有参数的FLOAT或有<24 的一个参数表示一个单精密浮点数字。

  • DOUBLE:一个正常大小(双精密)浮点数字。不能无符号。允许的值是-1.7976931348623157E+308到-2.2250738585072014E-308、 0和2.2250738585072014E-308到1.7976931348623157E+308。M是显示宽度而D是小数位数。没有一个参数的 DOUBLE或FLOAT(X)(25 < = X < = 53)代表一个双精密浮点数字。

  • DATE:一个日期。支持的范围是'1000-01-01'到'9999-12-31'。MySQL以'YYYY-MM-DD'格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列。

  • DATETIME:一个日期和时间组合。支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。MySQL以'YYYY-MM-DD HH:MM:SS'格式来显示DATETIME值，但是允许你使用字符串或数字把值赋给DATETIME的列。

  • TIMESTAMP[(M)]:一个时间戳记。范围是'1970-01-01 00:00:00'到2037年的某时。MySQL以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD或YYMMDD 格式来显示TIMESTAMP值，取决于是否M是14(或省略)、12、8或6，但是允许你使用字符串或数字把值赋给TIMESTAMP列。一个 TIMESTAMP列对于记录一个INSERT或UPDATE操作的日期和时间是有用的，因为如果你不自己给它赋值，它自动地被设置为最近操作的日期和时间。你以可以通过赋给它一个NULL值设置它为当前的日期和时间。

  • TIME:一个时间。范围是'-838:59:59'到'838:59:59'。MySQL以'HH:MM:SS'格式来显示TIME值，但是允许你使用字符串或数字把值赋给TIME列。

  • YEAR:一个2或4位数字格式的年(缺省是4位)。允许的值是1901到2155，和0000(4位年格式)，如果你使用2位，1970-2069( 70-69)。MySQL以YYYY格式来显示YEAR值，但是允许你把使用字符串或数字值赋给YEAR列。(YEAR类型在MySQL3.22中是新类型。)

  • CHAR:一个定长字符串，当存储时，总是是用空格填满右边到指定的长度。M的范围是1 ～ 255个字符。当值被检索时，空格尾部被删除。CHAR值根据缺省字符集以大小写不区分的方式排序和比较，除非给出BINARY关键词。NATIONAL CHAR(短形式NCHAR)是ANSI SQL的方式来定义CHAR列应该使用缺省字符集。这是MySQL的缺省。CHAR是CHARACTER的一个缩写。

  • VARCHAR:一个变长字符串.

  • TINYBLOB,TINYTEXT:一个BLOB或TEXT列，最大长度为255(2^8-1)个字符。

  • BLOB,TEXT:一个BLOB或TEXT列，最大长度为65535(2^16-1)个字符。

  • MEDIUMBLOB,MEDIUMTEXT:一个BLOB或TEXT列，最大长度为16777215(2^24-1)个字符。

  • LONGBLOB,LONGTEXT:一个BLOB或TEXT列，最大长度为4294967295(2^32-1)个字符。

  • ENUM('value1','value2',...):枚举。一个仅有一个值的字符串对象，这个值式选自与值列表'value1'、'value2', ...,或NULL。一个ENUM最多能有65535不同的值。

MYISAM存储引擎

MYISAM存储引擎的锁机制

MyISAM存储引擎只支持表锁，是现在用得最多的存储引擎。

1.查询表级锁争用情况

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

show status like 'table%'查询锁的争用情况

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

2.MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。

所以对MyISAM表进行操作，会有以下情况：

a、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

b、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

简单来说就是

读锁可以被读操作共享,不影响,但读锁会影响写操作.

写锁会影响所有的读写操作.

3.并发插入

原则上数据表有一个读锁时，其它进程无法对此表进行更新操作，但在一定条件下，MyISAM表也支持查询和插入操作的并发进行。MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

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

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

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

4.MyISAM的锁调度

由于MySQL认为写请求一般比读请求要重要，所以如果有读写请求同时进行的话，MYSQL将会优先执行写操作。这样MyISAM表在进行大量的更新操作时（特别是更新的字段中存在索引的情况下），会造成查询操作很难获得读锁，从而导致查询阻塞。

我们可以通过一些设置来调节MyISAM的调度行为：

a、通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。

b、通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。

MYISAM存储引擎的索引

1 MyISAM默认使用B-tree索引只把索引载入内存

2 MyISAM数据库中的数据是按照插入的顺序保存，在每个索引节点中保存对应的数据行的地址，理论上说主键索引和其他索引是一样的

3 对于字符串索引，MyISAM默认采用增量保存，例如第一个索引值是'perform'，第二个索引的值是'performance', 在索引文件中第二个索引被保存为'7,ance'。这样能够减小索引的尺寸。

4 MyISAM保存索引的状态信息在磁盘里，每次执行ANALYZE TABLE会更新这个信息。

5 索引长期运行之后会产生碎片，一种碎片是一行数据被保存在不同的数据段，另一种是连续的表空间或行在磁盘上被分散地保存。对于MyISAM两种索引碎片都会出现

MYISAM存储引擎的事务

不支持事务

INNODB存储引擎

INNODB存储引擎的锁

查询行级锁争用情况 show status like 'innodb_row_lock%';

innodb的锁 分为 共享锁和排他锁

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

  • 排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。意向锁是innodb自动处理,无需人工干预

  • 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

  • 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

从sql语句上来说 update ,delete 会自动给涉及到的数据集加上排他锁 ,而普通的select则不加任何锁!

那insert 会加什么锁呢?

mysql 5.0以前,如果主键用的是A_I , 会在内存中保存A_I 的值,插入的时候用的表锁,也就是说,只要用A_I ,插入的时候就会锁表. 当然可以不用A_I .

还有一个方法就是使用MySQL5.1.22之后的版本，出现了一个新的配置选项：innodb_autoinc_lock_mode，它是专门用来在使用auto_increment的情况下调整锁策略的，目前有三种选择：

  1. innodb_autoinc_lock_mode = 0 (“traditional” lock mode) # 仍然是表锁

  2. innodb_autoinc_lock_mode = 1 (“consecutive” lock mode) #默认方式, 只锁分配A_I的过程,可一次分配多个

  3. innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)# 只锁分配A_I的过程,来一个分配一个

总结:

1.innodb引擎有表锁和行锁

2.行锁不影响读操作,只影响写操作

3.当执行某写操作,如果有索引,则根据索引锁住数据所在的行,如果没有索引,则锁住整张表.表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。

4.mvcc,当有个写锁存在的时候,读操作会去快照取数据,只要写操作没有commit前,都会取到写操作之前的数据

5.表锁不影响读操作

INNODB存储引擎的事务

事务就是为了让数据库从一种一致的状态 切换到 另一种一致的状态!

用我们的先辈来说 : 要么生 ,要么死! 别折磨人,让人生不如死!

我们自己的理解就是: 这一系列的操作比如 取款和卡中余额减少,要么都成功,要门都不成功

要么某东的订单状态支付变更和 余额减少 都成功,要么都不成功 ! 这之间就需要用事务来保证!

如果让你来实现事务的逻辑, 你会怎么操作?

PreviousMySQLNext标准PHP库（SPL）

Last updated