mysql存储引擎的知识

mysql存储引擎的知识
最常用的搜索引擎为myisam5.5.5以前默认存储引擎和innodb全文搜索引擎两个都已经支持
.myd数据&＃xff0c;.myi索引&＃xff0c;.frm表的定义
[root&＃64;localhost mysql]# file user.MYI
user.MYI: MySQL MISAM compressed data file Version 1
支持事务innodb,ndb
事务介绍
原子性&＃xff1a;要么都成功&＃xff0c;要么都失败
一致性&＃xff1a;事务发生前和发生后&＃xff0c;数据的完整性保持一致
隔离性&＃xff1a;当并发执行数据库时&＃xff0c;一个正在执行的时候&＃xff0c;在执行完成前别的
事务看不到当前状态
持久性&＃xff1a;一个事务一旦被提交&＃xff0c;他对数据库的数据改变是永久性的
事务的开启
数据库的默认事务是自动提交的
数据库事务开启命令
start transaction
rollback
commmit
set autocommit&＃61;0禁止自动提交
set autocommit&＃61;1开启自动提交
1myisam不支持事务&＃xff0c;
2表级锁定
3读写相互阻塞
4只会缓存索引
5读取速度快&＃xff0c;占用资源少
6不支持外键约束&＃xff08;连接查询&＃xff09;&＃xff0c;但支持全文索引
生产场景
1.不支持事务的业务
2并发相对较低
3读数据较多的应用&＃xff0c;读写都频繁的都不适合&＃xff0c;读多或者写多适合
4以读为主的业务&＃xff0c;列如blog
5对数据的一致性要求不是特别高的业务
6 硬件资源比较差的机器
myisam调优精要
设置合适的索引&＃xff0c;缓存机制
调整读写优先级&＃xff0c;根据实际需求确保重要操作更优先执行
3启用延迟插入改善大批量写入性能&＃xff0c;尽可能多条数据一次性写入
4尽量顺序操作让insert数据写入尾部&＃xff0c;减少阻塞&＃xff0c;
5分解大的操作&＃xff0c;降低单个操作的阻塞时间
6降低并发数减少对mysql访问&＃xff0c;某些高并发场景应使用排队队列机制
使用cache或者nosql
query_cache_size&＃61;2M
query_cache_limit&＃61;1M
query_chache_min_res_unit&＃61;2K
innodb引擎
优点
1支持事务的四个级别
2行级锁定&＃xff0c;全表扫描表说
3&＃xff0c;读写策越和事务隔离相关
4具有非常高的缓存&＃xff0c;可以缓存索引&＃xff0c;也可以缓存数据
5整个表和主键以cluster方式存储&＃xff0c;组成一个平衡树
6所有secondary index都会保存主键信息
7支持分区表空间
8支持外键约束&＃xff0c;不支持全文索引&＃xff08;5.5以前&＃xff09;&＃xff0c;以后支持了
9对硬件资源要求比较高
innodb生产场景
1需要支持事务的业务
2行级锁定对高并发有较好的适应能力&＃xff0c;
3数据读写及跟新都较为频繁的场景
4数据一致性比较高的业务
5硬件内存比较大的
mysql> show variables like &＃39;%innodb%&＃39;;
&＃43;---------------------------------&＃43;------------------------&＃43;
| Variable_name                   | Value                  |
&＃43;---------------------------------&＃43;------------------------&＃43;
| 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                      |
&＃43;---------------------------------&＃43;------------------------&＃43;
62 rows in set (0.08 sec)
表名.frm
调优
1主键尽可能比较小
2避免全表扫描
3尽可能缓存所有数据和索引&＃xff0c;减少io
4在大批量小插入的时候&＃xff0c;尽量自己控制事务&＃xff0c;不要使用autocommit提交
5合理使用innodb_flush_log_at_trx_commit参数值&＃xff0c;不要过度最求安全
若为0.log buffer 会刷新日志文件到磁盘&＃xff0c;提交事务的时候不会做任何操作
6避免主键跟新
查看哪些引擎可用
mysql> show engines;
&＃43;--------------------&＃43;---------&＃43;----------------------------------------------------------------&＃43;--------------&＃43;------&＃43;------------&＃43;
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
&＃43;--------------------&＃43;---------&＃43;----------------------------------------------------------------&＃43;--------------&＃43;------&＃43;------------&＃43;
| 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         |
&＃43;--------------------&＃43;---------&＃43;----------------------------------------------------------------&＃43;--------------&＃43;------&＃43;------------&＃43;
8 rows in set (0.04 sec)
生产环境如何批量改存储引擎
alter table tt engine&＃61;&＃39;MyISAM&＃39;;
2.使用sed对备份内容进行替换
which mysql_convert_table_format --host --user&＃61; -type&＃61;  -e
default_table_type&＃61;InnoDB