一 简述
1)MySQL内核
2)SQL优化工程师
3)MySQL服务器的优化
4)各种参数常量的设定
5)查询语句优化
6)主从复制
7)软硬件升级
8)容灾备份
9)SQL编程
二 存储引擎
1)1)mysql中的常用的存储引擎有:InnoDB,MyISAM
# 显示出mysql中支持的存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
2)两者比较
对比项 | MyISAM | InnoDB |
---|---|---|
主外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
行表锁 | 表锁,即是操作一条记录也会锁住整张表,不适合高并发 | 行锁,操作时只锁着某一行,不会对其他行有影响,适合高并发操作 |
缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引,还缓存真实数据,对内存有要求,内存大小对性能有影响 |
关注点 | 读性能 | 并发写,事务,资源 |