mysql支持锁要用什么引擎_MySQL 存储引擎和锁

最新推荐文章于 2022-11-11 11:10:34 发布

敬岁月敬纯真

最新推荐文章于 2022-11-11 11:10:34 发布

阅读量108

点赞数

文章标签： mysql支持锁要用什么引擎

本文链接：https://blog.csdn.net/weixin_31484881/article/details/114347448

版权

MySQL 存储引擎和锁

===============================================================================

存储引擎：

1.介绍★存储引擎：Storage Engine(负责向下管理文件，向上提供关系模型)MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能；

表类型：表级别概念，不建议在同一个库中的表上使用不同的ENGINE；

★常见的存储引擎：MyISAM,Aria, InnoDB, MRG_MYISAM, CSV, BLACKHOLE, MEMORY, PERFORMANCE_SCHEMA, ARCHIVE, FEDERATED

★查看数据库支持的存储引擎种类：mysql> SHOW ENGINES;

★查看默认的存储引擎：mysql> SHOW GLOBA|[SESSION] VARIABLES [LIKE clause];

★查看指定表的存储引擎：mysql> SHOW TABLE STATUS LIKE clause;

★创建表时设定其存储引擎的方法：CREATE TABLE ... ENGINE[=]STORAGE_ENGINE_NAME ...

演示：

1.查看mysql所支持的存储引擎；MariaDB [mysql]> SHOW ENGINES;

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

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

| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES |

| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |

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

10 rows in set (0.00 sec)

2.查看默认的存储引擎；MariaDB [mysql]> show global variables like '%storage_engine%';

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

| Variable_name | Value |

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

| default_storage_engine | InnoDB |

| storage_engine | InnoDB |

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

2 rows in set (0.01 sec)

3.查看指定表的存储引擎(即查看表的状态信息)MariaDB [mysql]> SHOW TABLE STATUS LIKE 'user'\G

*************************** 1. row ***************************

Name: user

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 10

Avg_row_length: 102

Data_length: 1024

Max_data_length: 281474976710655

Index_length: 2048

Data_free: 0

Auto_increment: NULL

Create_time: 2016-10-12 20:06:15

Update_time: 2016-11-12 18:13:46

Check_time: NULL

Collation: utf8_bin

Checksum: NULL

Create_options:

Comment: Users and global privileges

1 row in set (0.00 sec)

InnoDB存储引擎

---Percona-XtraDB, Supports transactions(支持事务), row-level locking(行级锁), and foreign keys(外键)★表结构的定义：在数据库目录，tbl_name.frm

★数据存储于“表空间(table space)"中：

☉所有InnoDB表的数据和索引存储于同一个表空间中；

◆表空间文件：datadir定义的目录中文件：ibdata1, ibdata2, ...

☉innodb_file_per_table=ON，意味着每表使用单独的表空间文件；

◆数据文件(数据和索引，存储于数据库目录): tbl_name.ibd

注意：默认所有innodb存储引擎的数据和索引都存储于一个表空间中；

要想使每表使用单独的表空间文件，就要在/etc/my.cnf中定义

★功能：

☉事务型存储引擎适合对事务要求较高的场景中，但较适用于处理大量短期事务；

☉基于MVCC(Mutli Version Concurrency Control)支持高并发；支持四个隔离级别，默认级别为 REPEATABLE-READ；间隙锁以防止幻读；

☉使用聚集索引(主键索引)；

☉支持"自适应Hash索引"；

☉锁粒度：行级锁；间隙锁；

★查看innodb的状态SHOW ENGINE INNODB STATUS

演示：

1.查看mysql数据库目录[root@centos7 ~]# cd /var/lib/mysql/

[root@centos7 mysql]# ll -h

total 29M

-rw-rw---- 1 mysql mysql 16K Nov 26 21:40 aria_log.00000001

-rw-rw---- 1 mysql mysql 52 Nov 26 21:40 aria_log_control

drwx------ 2 mysql mysql 4.0K Nov 25 19:16 hellodb

-rw-rw---- 1 mysql mysql 18M Nov 27 11:20 ibdata1 //innodb存储引擎的数据文件存放位置，即公共表空间文件

-rw-rw---- 1 mysql mysql 5.0M Nov 27 11:20 ib_logfile0 //事物日志相关的文件

-rw-rw---- 1 mysql mysql 5.0M Nov 27 11:20 ib_logfile1 //事物日志相关的文件

drwx------ 2 mysql mysql 4.0K Oct 12 20:06 mysql

srwxrwxrwx 1 mysql mysql 0 Nov 27 09:49 mysql.sock

drwx------ 2 mysql mysql 4.0K Oct 12 20:06 performance_schema

drwx------ 2 mysql mysql 131 Oct 20 16:55 Syslog

drwx------ 2 mysql mysql 79 Nov 24 19:37 testdb //创建的数据库目录

drwx------ 2 mysql mysql 36K Oct 13 10:22 ultrax

drwx------ 2 mysql mysql 12K Nov 12 21:47 zabbix

2.查看testdb数据库的文件，如下：MariaDB [testdb]> show tables; //testdb数据库中有两张表，对应/var/lib/mysql目录下的testdb目录

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

| Tables_in_testdb |

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

| tbl1 |

| tbl2 |

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

2 rows in set (0.00 sec)

MariaDB [testdb]> show table status like 'tbl2'\G

*************************** 1. row ***************************

Name: tbl2

Engine: InnoDB //存储引擎

Version: 10

Row_format: Compact

Rows: 3

Avg_row_length: 5461

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2016-11-24 19:37:41

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

MariaDB [testdb]> desc tbl2; //查看表结构

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

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

| id | int(10) unsigned | YES | | NULL | |

| age | tinyint(3) unsigned | YES | | NULL | |

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

4 rows in set (0.01 sec)

[root@centos7 mysql]# pwd

/var/lib/mysql

[root@centos7 mysql]# cd testdb/

[root@centos7 testdb]# ll

total 220

-rw-rw---- 1 mysql mysql 65 Nov 24 15:39 db.opt

-rw-rw---- 1 mysql mysql 8586 Nov 24 15:41 tbl1.frm //定义表格式(tbl、tbl2的格式)

-rw-rw---- 1 mysql mysql 98304 Nov 24 16:00 tbl1.ibd //数据存放位置，每表使用单独的表空间，不再使用默认的公共表空间 ibdata1

-rw-rw---- 1 mysql mysql 8654 Nov 24 19:37 tbl2.frm

-rw-rw---- 1 mysql mysql 98304 Nov 24 21:02 tbl2.ibd

3.innodb存储引擎的相关参数MariaDB [testdb]> show variables like '%innodb%';

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

| Variable_name | Value |

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

| have_innodb | YES |

| ignore_builtin_innodb | OFF |

| innodb_adaptive_flushing | ON |

| innodb_adaptive_flushing_method | estimate |

| innodb_adaptive_hash_index | ON |

| innodb_adaptive_hash_index_partitions | 1 |

| innodb_additional_mem_pool_size | 8388608 |

| innodb_autoextend_increment | 8 |

| innodb_autoinc_lock_mode | 1 |

| innodb_blocking_buffer_pool_restore | OFF |

| innodb_buffer_pool_instances | 1 |

| innodb_buffer_pool_populate | OFF |

| innodb_buffer_pool_restore_at_startup | 0 |

| innodb_buffer_pool_shm_checksum | ON |

| innodb_buffer_pool_shm_key | 0 |

| innodb_buffer_pool_size | 134217728 |

| innodb_change_buffering | all |

| innodb_checkpoint_age_target | 0 |

| innodb_checksums | ON |

| innodb_commit_concurrency | 0 |

| innodb_concurrency_tickets | 500 |

| innodb_corrupt_table_action | assert |

| innodb_data_file_path | ibdata1:10M:autoextend |

| innodb_data_home_dir | |

| innodb_dict_size_limit | 0 |

| innodb_doublewrite | ON |

| innodb_doublewrite_file | |

| innodb_fake_changes | OFF |

| innodb_fast_checksum | OFF |

| innodb_fast_shutdown | 1 |

| innodb_file_format | Antelope |

| innodb_file_format_check | ON |

| innodb_file_format_max | Antelope |

| innodb_file_per_table | ON |

| innodb_flush_log_at_trx_commit | 1 |

| innodb_flush_method | |

| innodb_flush_neighbor_pages | area |

| innodb_force_load_corrupted | OFF |

| innodb_force_recovery | 0 |

| innodb_ibuf_accel_rate | 100 |

| innodb_ibuf_active_contract | 1 |

| innodb_ibuf_max_size | 67092480 |

| innodb_import_table_from_xtrabackup | 0 |

| innodb_io_capacity | 200 |

| innodb_kill_idle_transaction | 0 |

| innodb_large_prefix | OFF |

| innodb_lazy_drop_table | 0 |

| innodb_lock_wait_timeout | 50 |

| innodb_locking_fake_changes | ON |

| innodb_locks_unsafe_for_binlog | OFF |

| innodb_log_block_size | 512 |

| innodb_log_buffer_size | 8388608 |

| innodb_log_file_size | 5242880 |

| innodb_log_files_in_group | 2 |

| innodb_log_group_home_dir | ./ |

| innodb_max_bitmap_file_size | 104857600 |

| innodb_max_changed_pages | 1000000 |

| innodb_max_dirty_pages_pct | 75 |

| innodb_max_purge_lag | 0 |

| innodb_merge_sort_block_size | 1048576 |

| innodb_mirrored_log_groups | 1 |

| innodb_old_blocks_pct | 37 |

| innodb_old_blocks_time | 0 |

| innodb_open_files | 300 |

| innodb_page_size | 16384 |

| innodb_print_all_deadlocks | OFF |

| innodb_purge_batch_size | 20 |

| innodb_purge_threads | 1 |

| innodb_random_read_ahead | OFF |

| innodb_read_ahead | linear |

| innodb_read_ahead_threshold | 56 |

| innodb_read_io_threads | 4 |

| innodb_recovery_stats | OFF |

| innodb_recovery_update_relay_log | OFF |

| innodb_replication_delay | 0 |

| innodb_rollback_on_timeout | OFF |

| innodb_rollback_segments | 128 |

| innodb_show_locks_held | 10 |

| innodb_show_verbose_locks | 0 |

| innodb_simulate_comp_failures | 0 |

| innodb_spin_wait_delay | 6 |

| innodb_stats_auto_update | 1 |

| innodb_stats_method | nulls_equal |

| innodb_stats_modified_counter | 0 |

| innodb_stats_on_metadata | ON |

| innodb_stats_sample_pages | 8 |

| innodb_stats_traditional | ON |

| innodb_stats_update_need_lock | 1 |

| innodb_strict_mode | OFF |

| innodb_support_xa | ON |

| innodb_sync_spin_loops | 30 |

| innodb_table_locks | ON |

| innodb_thread_concurrency | 0 |

| innodb_thread_concurrency_timer_based | OFF |

| innodb_thread_sleep_delay | 10000 |

| innodb_track_changed_pages | OFF |

| innodb_use_atomic_writes | OFF |

| innodb_use_fallocate | OFF |

| innodb_use_global_flush_log_at_trx_commit | ON |

| innodb_use_native_aio | ON |

| innodb_use_stacktrace | OFF |

| innodb_use_sys_malloc | ON |

| innodb_use_sys_stats_table | OFF |

| innodb_version | 5.5.43-MariaDB-37.2 |

| innodb_write_io_threads | 4 |

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

105 rows in set (0.00 sec)

-------------------------------------------------------------------------------

innodb总结：★数据存储：表空间；

★并发：MVCC，间隙锁，行级锁；

★索引：聚集索引、辅助索引；

★性能：预读操作、内存数据缓冲、内存索引缓存、自适应Hash索引、插入操作缓存区；

★备份：支持热备；

MyISAM：存储引擎★特性：支持全文索引(FULLTEXT index)、压缩、空间函数(GIS)；

不支持事务；

锁粒度：表级锁；

崩溃无法保证表安全恢复

★适用场景：只读或读多写少的场景、较小的表(以保证崩溃后恢复的时间较短)；

★文件：每个表有三个文件，存储于数据库目录中tbl_name.frm：表格式定义；

tbl_name.MYD：数据文件；

tbl_name.MYI：索引文件；

总结：加锁和并发：表级锁；

修复：手动或自动修复、但可能会丢失数据；

索引：非聚集索引；

延迟索引更新；

表压缩；

演示：MariaDB [hellodb]> show table status like 'students'\G

*************************** 1. row ***************************

Name: students

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 25

Avg_row_length: 24

Data_length: 624

Max_data_length: 281474976710655

Index_length: 3072

Data_free: 0

Auto_increment: 26

Create_time: 2016-11-27 15:01:38

Update_time: 2016-11-27 15:01:38

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:[root@centos7 mysql]# cd hellodb/

[root@centos7 hellodb]# ll

total 140

-rw-rw---- 1 mysql mysql 8636 Nov 25 19:16 classes.frm # 每个表有三个文件

-rw-rw---- 1 mysql mysql 172 Nov 25 19:16 classes.MYD

-rw-rw---- 1 mysql mysql 2048 Nov 25 19:16 classes.MYI

-rw-rw---- 1 mysql mysql 8630 Nov 25 19:16 coc.frm

-rw-rw---- 1 mysql mysql 112 Nov 25 19:16 coc.MYD

-rw-rw---- 1 mysql mysql 2048 Nov 25 19:16 coc.MYI

-rw-rw---- 1 mysql mysql 8602 Nov 25 19:16 courses.frm

-rw-rw---- 1 mysql mysql 144 Nov 25 19:16 courses.MYD

-rw-rw---- 1 mysql mysql 2048 Nov 25 19:16 courses.MYI

-rw-rw---- 1 mysql mysql 61 Nov 25 19:16 db.opt

-rw-rw---- 1 mysql mysql 8658 Nov 25 19:16 scores.frm

-rw-rw---- 1 mysql mysql 180 Nov 25 19:16 scores.MYD

-rw-rw---- 1 mysql mysql 2048 Nov 25 19:16 scores.MYI

-rw-rw---- 1 mysql mysql 8736 Nov 27 15:01 students.frm

-rw-rw---- 1 mysql mysql 624 Nov 27 15:01 students.MYD

-rw-rw---- 1 mysql mysql 3072 Nov 27 15:01 students.MYI

-rw-rw---- 1 mysql mysql 8656 Nov 25 19:16 teachers.frm

-rw-rw---- 1 mysql mysql 92 Nov 25 19:16 teachers.MYD

-rw-rw---- 1 mysql mysql 2048 Nov 25 19:16 teachers.MYI

-rw-rw---- 1 mysql mysql 8622 Nov 25 19:16 toc.frm

-rw-rw---- 1 mysql mysql 0 Nov 25 19:16 toc.MYD

-rw-rw---- 1 mysql mysql 1024 Nov 25 19:16 toc.MYI

其他存储引擎★其它的存储引擎：CSV：将CSV文件(以逗号分隔字段的文本文件)作为MySQL表文件；

MRG_MYISAM：将多个MyISAM表合并成的虚拟表；

BLACKHOLE：类似于/dev/null，不真正存储数据；

MEMORY：内存存储引擎，支持hash索引，表级锁，常用于临时表；

FEDERATED: 用于访问其它远程MySQL服务器上表的存储引擎接口；

★MariaDB额外支持很多种存储引擎：OQGraph、SphinxSE、TokuDB、Cassandra、CONNECT、SQUENCE、...

★搜索引擎：lucene：Solr, Elasticsearch

sphinx

并发控制：锁

锁：Lock★锁类型：读锁：共享锁，可被多个读操作共享；

写锁：排它锁，独占锁；

★锁粒度：表锁：在表级别施加锁，并发性较低；

行锁：在行级别施加锁，并发性较高；

★锁策略：在锁粒度及数据安全性之间寻求一种平衡机制；存储引擎：级别以及何时施加或释放锁由存储引擎自行决定；

MySQL Server：表级别，可自行决定，也允许显式请求；

★锁类别：显式锁：用户手动请求的锁；

隐式锁：存储引擎自行根据需要施加的锁；

★显式锁的使用：

☉LOCK TABLESLOCK TABLES tbl_name read|write, tbl_name read|write, ...//添加锁

UNLOCK TABLES //释放锁

☉FLUSH TABLES

强制把内存中表的相关数据同步到磁盘之后，再一次以施加指令的类型的锁之后打开FLUSH TABLES tbl_name,... [WITH READ LOCK];

UNLOCK TABLES;

eg：FLUSH TABLES WITH READ LOCK(表示把所有的表同步到磁盘上，然后再请求读锁)

☉SELECT cluase

锁定指定表中的行[FOR UPDATE | LOCK IN SHARE MODE]

演示：

在node1会话中请求读锁，发现可以正常读数据，但不能写数据MariaDB [testdb]> LOCK TABLES tbl2 READ; # 请求读锁

Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> SELECT * FROM tbl2; # 可以正常查看数据

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

| id | name | age | gender |

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

| 1 | tom | 21 | NULL |

| 2 | tao | 15 | NULL |

| 3 | jing | 22 | NULL |

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

3 rows in set (0.00 sec)

MariaDB [testdb]> INSERT INTO tbl2 VALUES (4,'LinghuChong'); # 但是不能执行写操作，因为写操作为写锁，是独占的

ERROR 1099 (HY000): Table 'tbl2' was locked with a READ lock and can't be updated

在node2会话中，可以正常请求读锁，但是不能请求写锁，因为写锁只能是独占的，要想执行写操作必须保证其他会话中释放了所有的锁MariaDB [testdb]> LOCK TABLES tbl2 READ;

Query OK, 0 rows affected (0.01 sec)

MariaDB [testdb]> SELECT * FROM tbl2;

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

| id | name | age | gender |

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

| 1 | tom | 21 | NULL |

| 2 | tao | 15 | NULL |

| 3 | jing | 22 | NULL |

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

3 rows in set (0.01 sec)

MariaDB [testdb]> UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> LOCK TABLES tbl2 WRITE; # 请求写锁，发现被阻塞，因为写锁是独占的，必须使node1会话中释放了读锁之后才可以请求读锁；

# 在node1会话中释放读锁，再次请求写锁发现可以正常请求

MariaDB [testdb]> LOCK TABLES tbl2 WRITE;

Query OK, 0 rows affected (10.49 sec) # 阻塞时长10.49s

此时，因为node2上请求了写锁，所以node1会话中再请求读锁，是被阻塞的，因为写锁为排他锁，连接写锁时，其他的会话中的任何读写操作均阻塞；只有等node2会话中写操作执行完成后释放了写锁，node1会话才可以请求读锁MariaDB [testdb]> LOCK TABLES tbl2 READ; # 请求读锁阻塞

# node2释放写锁后，请求读锁成功

MariaDB [testdb]> LOCK TABLES tbl2 READ;

Query OK, 0 rows affected (5.66 sec) # 阻塞时长5.66s

注意：

大多数情况下，mysql都可以自行维护锁机制，而不用去手动施加。

敬岁月敬纯真

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql支持锁要用什么引擎_MySQL 存储引擎和锁

MySQL 存储引擎和锁===============================================================================存储引擎：1.介绍★存储引擎：Storage Engine(负责向下管理文件，向上提供关系模型)MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平...
复制链接

扫一扫