MySQL存储引擎和锁
MySQL 存储引擎和锁
创新互联公司2013年成立,是专业互联网技术服务公司,拥有项目成都网站制作、成都网站设计、外贸营销网站建设网站策划,项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命,1280元新干做网站,已为上家服务,为新干各地企业和个人服务,联系电话:13518219792
===============================================================================
存储引擎:
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)
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; //查看表结构 +--------+---------------------+------+-----+---------+-------+ | 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.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 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; # 请求写锁,发现被阻塞,因为写锁是
分享文章:MySQL存储引擎和锁
当前路径:http://myzitong.com/article/jsdpco.html