MySQL 存储引擎和锁
发表于：2024-11-23 作者：千家信息网编辑
千家信息网最后更新 2024年11月23日，MySQL 存储引擎和锁===============================================================================存储引擎：1.介绍
千家信息网最后更新 2024年11月23日MySQL 存储引擎和锁
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: 1024Max_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 privileges1 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 -htotal 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_controldrwx------ 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 mysqlsrwxrwxrwx 1 mysql mysql 0 Nov 27 09:49 mysql.sockdrwx------ 2 mysql mysql 4.0K Oct 12 20:06 performance_schemadrwx------ 2 mysql mysql 131 Oct 20 16:55 Syslogdrwx------ 2 mysql mysql 79 Nov 24 19:37 testdb //创建的数据库目录drwx------ 2 mysql mysql 36K Oct 13 10:22 ultraxdrwx------ 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: 16384Max_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]# lltotal 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索引、插入操作缓存区；
★备份：支持热备；
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: 624Max_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]# lltotal 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都可以自行维护锁机制，而不用去手动施加。
存储
引擎
数据
文件
索引
空间
支持
数据库
目录
级别
阻塞
不同
事务
内存
功能
技术
粒度
演示
场景
手动
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
计算机网络技术专业未来职业
西安软件开发行业招聘信息
桌面终端安全管理服务器
odbc连接多个数据库
深圳无源网络技术开发工艺
权威的软件开发
网络安全法规定国内信息
20kw能带多少台服务器
c 简单数据库
猪八戒软件开发商铺考试答案
dns服务器服务类型有哪些
梅河口软件开发
辽宁统一软件开发服务参考价格
魔域服务器每天几点更新最好
定时任务部署到多台服务器上
天黑黑伴奏软件开发
项目数据库建设管理服务方案
爱如生数据库登录进去了不能显示
联合网络安全检查办法
tb级别数据库
权威的软件开发
数据库 恢复之前备份文件
郑州真爱科技互联网
xml数据库技术原理
数据库导入数据丢失
河南服务器机柜定制价格
访问控制技术是网络安全技术吗
服务器root权限
管理软件开发有哪些
前台获取数据库数据
