溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
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; +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | 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)
---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; //查看表結構
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('F','M') | 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.ibd3.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索引、插入操作緩存區；
★備份：支持熱備；
★特性：
支持全文索引（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 TABLES
LOCK 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都可以自行維護鎖機制，而不用去手動施加。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
