MyISAM存储引擎
mysql系统表很多都用myisam引擎
- 不支持事务
- 表级别的锁定，更新数据上表锁
- 读写相互阻塞，但是读不阻塞读
- 缓存只会缓存索引，通过key_buffer_size缓存索引
- 读取较快，占用资源比较少
- 不支持外键约束，但支持全文索引
- 在MySQL5.5.5之前默认引擎
生产场景：
- 不需要事务支持的业务
- 一般为读书节比较多的应用，读写都频繁的场景不适合，读多或者写多都适合
- 读写并发访问相对比较低的业务，（纯读可高并发）（锁机制问题）
- 以读为主的业务，图片信息数据库，用户数据库
- 数据修改相对较少的业务；
- 对数据一致性要求不是非常高的业务
- 硬件资源比较差的机器
引擎调优精要：
- 设置合适的索引（缓存机制）
- 调整读写优先级，根据实际需求确保重要操作更优先执行
- 启用延迟插入改善大批量写入性能（降低写入频率，尽可能多条数据一次性写入）
- 尽量顺序操作让insert数据都写入尾部，减少阻塞
- 分别大的时间长的操作，降低单个操作的阻塞时间
- 降低并发数（减少对MySQL访问），某些高并发场景通过应用及逆行排队队列机制Q队列
- 对于相对静态（更改不频繁）的数据库数据，充分利用Query Cache或者memcached缓存服务可以极大的提高访问效
- MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问
- 可以把主库用innodb，从库用MyISAM（不推荐）
query_cache_size=256M
query_cache_limit=1M
query_cache_min_res_unit=2K
Innodb引擎：
- 支持ACID，支持多版本读
- 行级锁定（更新一般是锁定当前行），全表扫面仍是表锁
- 读写阻塞与事务隔离级别相关
- 具有非常高效的缓存特性，能缓存索引和数据
- 整个表和主键以Cluster方式存储，组成一棵平衡树
- 所有secondary index都会保存主键信息
- 支持分区，表空间，类似oracle数据库
- 支持外键索引，全文索引
- 对资源要求比MyISAM要求高
生产场景：
- 需要事务支持的业务
- 行级锁定对高并发由很好的适应能力，但需要确保查询是通过索引完成
- 数据读写及更新都较为频繁的场景
- 数据一致性要求较高的业务
- 硬件设备内存较大，可利用innodb，将数据缓存到内存
innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=2048M
innodb_data_file_path=ibdata1:1024M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size=128M
innodb_log_files_in_group=3
innodb_max_dirty_pages_pct=90
innodb_lock_wait_timeout=120
innodb_file_per_table=0
innodb_file_per_table #每张表一个文件
innodb_data_home_dir=/data/xxx
innodb_log_group_home_dir=/data/xxx
innodb引擎调优精要：
- 主键尽可能小，避免给secondary index带来过大的空间负担
- 避免全表扫描，会表锁
- 尽可能缓存所有的索引和数据，提高响应速度，减少磁盘IO
- 在大批量插入的时候，不要使用autocommmit自动提交，有开关可以控制提交方式
- 合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性
- 如果innodb_flush_log_at_trx_commit的值为0，log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作
- 避免主键更新，因为会带来大量的数据移动
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
生产环境中如何批量修改MySQL引擎
1、alter table account ENGINE=MyISAM;
mysql> show create table account; +---------+-----------------------------------+ | Table | Create Table | +---------+-----------------------------------+ | account | CREATE TABLE `account` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +---------+-----------------------------------+ 1 row in set (0.00 sec) mysql> alter table account ENGINE=MyISAM; Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> show create table account; +---------+-----------------------------------+ | Table | Create Table | +---------+-----------------------------------+ | account | CREATE TABLE `account` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 | +---------+-----------------------------------+ 1 row in set (0.00 sec)
2、使用sed对备份内容进行引擎转换（适合小数据量）
mysqldump > abbott.sql nohup sed -e 's/MyISAM/InnoDB/g' abbott2.sql > abbott.sql & mysql < abbott.sql
3、如下脚本
SET @DATABASE_NAME = 'abbott'; SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables AS tb WHERE table_schema = @DATABASE_NAME AND `ENGINE` = 'MyISAM' AND `TABLE_TYPE` = 'BASE TABLE' ORDER BY table_name DESC;