mysql存储引擎的知识

2019独角兽企业重金招聘Python工程师标准>>>

mysql存储引擎的知识
最常用的搜索引擎为myisam5.5.5以前默认存储引擎和innodb全文搜索引擎两个都已经支持
.myd数据，.myi索引，.frm表的定义
[root@localhost mysql]# file user.MYI
user.MYI: MySQL MISAM compressed data file Version 1
支持事务innodb,ndb
事务介绍
原子性：要么都成功，要么都失败
一致性：事务发生前和发生后，数据的完整性保持一致
隔离性：当并发执行数据库时，一个正在执行的时候，在执行完成前别的
事务看不到当前状态
持久性：一个事务一旦被提交，他对数据库的数据改变是永久性的
事务的开启
数据库的默认事务是自动提交的
数据库事务开启命令
start transaction
rollback
commmit
set autocommit=0禁止自动提交
set autocommit=1开启自动提交
1myisam不支持事务，
2表级锁定
3读写相互阻塞
4只会缓存索引
5读取速度快，占用资源少
6不支持外键约束（连接查询），但支持全文索引
生产场景
1.不支持事务的业务
2并发相对较低
3读数据较多的应用，读写都频繁的都不适合，读多或者写多适合
4以读为主的业务，列如blog
5对数据的一致性要求不是特别高的业务
6 硬件资源比较差的机器
myisam调优精要
设置合适的索引，缓存机制
调整读写优先级，根据实际需求确保重要操作更优先执行
3启用延迟插入改善大批量写入性能，尽可能多条数据一次性写入
4尽量顺序操作让insert数据写入尾部，减少阻塞，
5分解大的操作，降低单个操作的阻塞时间
6降低并发数减少对mysql访问，某些高并发场景应使用排队队列机制
使用cache或者nosql
query_cache_size=2M
query_cache_limit=1M
query_chache_min_res_unit=2K
innodb引擎
优点
1支持事务的四个级别
2行级锁定，全表扫描表说
3，读写策越和事务隔离相关
4具有非常高的缓存，可以缓存索引，也可以缓存数据
5整个表和主键以cluster方式存储，组成一个平衡树
6所有secondary index都会保存主键信息
7支持分区表空间
8支持外键约束，不支持全文索引（5.5以前），以后支持了
9对硬件资源要求比较高
innodb生产场景
1需要支持事务的业务
2行级锁定对高并发有较好的适应能力，
3数据读写及跟新都较为频繁的场景
4数据一致性比较高的业务
5硬件内存比较大的
mysql> show variables like '%innodb%';
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| have_innodb                     | YES                    |
| ignore_builtin_innodb           | OFF                    |
| innodb_adaptive_flushing        | ON                     |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 16777216               |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_instances    | 1                      |
| innodb_buffer_pool_size         | 209715200              |
| innodb_change_buffering         | all                    |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |--
| innodb_doublewrite              | ON                     |
| 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_force_load_corrupted     | OFF                    |
| innodb_force_recovery           | 0                      |
| innodb_io_capacity              | 200                    |
| innodb_large_prefix             | OFF                    |
| innodb_lock_wait_timeout        | 120                    |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 16777216               |
| innodb_log_file_size            | 536870912              |
| innodb_log_files_in_group       | 3                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 60                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_old_blocks_pct           | 37                     |
| innodb_old_blocks_time          | 0                      |
| innodb_open_files               | 300                    |
| innodb_print_all_deadlocks      | OFF                    |
| innodb_purge_batch_size         | 20                     |
| innodb_purge_threads            | 0                      |
| innodb_random_read_ahead        | OFF                    |
| innodb_read_ahead_threshold     | 56                     |
| innodb_read_io_threads          | 4                      |
| innodb_replication_delay        | 0                      |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_rollback_segments        | 128                    |
| innodb_spin_wait_delay          | 6                      |
| innodb_stats_method             | nulls_equal            |
| innodb_stats_on_metadata        | ON                     |
| innodb_stats_sample_pages       | 8                      |
| innodb_strict_mode              | OFF                    |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 30                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 16                     |
| innodb_thread_sleep_delay       | 10000                  |
| innodb_use_native_aio           | OFF                    |
| innodb_use_sys_malloc           | ON                     |
| innodb_version                  | 5.5.32                 |
| innodb_write_io_threads         | 4                      |
+---------------------------------+------------------------+
62 rows in set (0.08 sec)
表名.frm
调优
1主键尽可能比较小
2避免全表扫描
3尽可能缓存所有数据和索引，减少io
4在大批量小插入的时候，尽量自己控制事务，不要使用autocommit提交
5合理使用innodb_flush_log_at_trx_commit参数值，不要过度最求安全
若为0.log buffer 会刷新日志文件到磁盘，提交事务的时候不会做任何操作
6避免主键跟新
查看哪些引擎可用
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.04 sec)
生产环境如何批量改存储引擎
alter table tt engine='MyISAM';
2.使用sed对备份内容进行替换
which mysql_convert_table_format --host --user= -type=  -e
default_table_type=InnoDB

转载于:https://my.oschina.net/goudingcheng/blog/597105