MySQL存储引擎和锁-成都快上网建站

快上网建站品牌

13518219792

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://kswjz.com/article/jsdpco.html
扫二维码与项目经理沟通

我们在微信上24小时期待你的声音

解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流

其他资讯

行业动态

企业网站建设的重要性！

现在虽然是移动互联网时代，但企业网站依然重要，包含PC站点，移动站。可以说企业网站关系企业的未来发展和前途，尤其对中小企业更是如此，一些中小企业老板，对自己的名片很在乎，因为这是个门面。...

服务项目

联系吧 在百度地图上找到我们

电话：13518219792

如遇占线或暂未接听请拨：136xxx98888

业务咨询 技术咨询 售后服务
网站设计
响应式网站设计
成都网站设计
成都网站设计公司
网站设计公司
网站制作
梓潼网站制作公司
移动手机网站制作
成都网站制作
成都网站制作
联系我们
电话：13518219792
邮箱：631063699@qq.com
地址：成都青羊区锦天国际1002号
网址：www.kswjz.com
网站建设
品牌网站建设
都江堰网站建设
网站建设
广安网站建设

微信二维码

Copyright © 2002-2023 www.kswjz.com 快上网建站品牌 QQ：244261566 版权所有 备案号：蜀ICP备19037934号