Mysql性能优化-3.存储引擎

1.存储引擎选择

早期的问题 ：Innodb > MyISAM ? Innodb : MyISAM
发展到现在：随着Innodb不断的发展完善，成为了主推的存储引擎

2.存储引擎的种类

mysql> show engines\G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.01 sec)

3.Innodb和MyISAM的差异

create table myisam1 (
    id int auto_increment primary key,
    title varchar(16)
) engine=myisam charset=utf8;

create table innodb1 (
    id int auto_increment primary key,
    title varchar(16)
) engine=innodb charset=utf8;

mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| innodb1              |
| myisam1              |
+----------------------+
2 rows in set (0.00 sec)

mysql> insert into myisam1 values (23, '张无忌');
Query OK, 1 row affected (0.01 sec)

mysql> insert into myisam1 values (16, '李莫愁');
Query OK, 1 row affected (0.00 sec)

mysql> insert into myisam1 values (11, '令狐冲');
Query OK, 1 row affected (0.00 sec)

mysql> insert into myisam1 values (27, '风清扬');
Query OK, 1 row affected (0.00 sec)

mysql> insert into innodb1 values (23, '张无忌');
Query OK, 1 row affected (0.01 sec)

mysql> insert into innodb1 values (16, '李莫愁');
Query OK, 1 row affected (0.00 sec)

mysql> insert into innodb1 values (11, '令狐冲');
Query OK, 1 row affected (0.00 sec)

mysql> insert into innodb1 values (27, '风清扬');
Query OK, 1 row affected (0.00 sec)

mysql> select * from myisam1;
+----+--------+
| id | title  |
+----+--------+
| 23 | 张无忌 |
| 16 | 李莫愁 |
| 11 | 令狐冲 |
| 27 | 风清扬 |
+----+--------+
4 rows in set (0.00 sec)

mysql> select * from innodb1;
+----+--------+
| id | title  |
+----+--------+
| 11 | 令狐冲 |
| 16 | 李莫愁 |
| 23 | 张无忌 |
| 27 | 风清扬 |
+----+--------+
4 rows in set (0.00 sec)

mysql> insert into myisam1 select null, title from myisam1;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> flush table myisam1;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from myisam1 where title in ('张无忌','李莫愁');
Query OK, 16384 rows affected (0.37 sec)

mysql> flush table myisam1;
Query OK, 0 rows affected (0.00 sec)

mysql> optimize table myisam1;
+--------------------+----------+----------+----------+
| Table              | Op       | Msg_type | Msg_text |
+--------------------+----------+----------+----------+
| mysql_test.myisam1 | optimize | status   | OK       |
+--------------------+----------+----------+----------+
1 row in set (0.03 sec)

myisam优化前.png
myisam优化后.png

4.选择结论

• 没有特殊需求，选择Innodb
• MyISAM：以读写插入为主的应用程序。例如，博客、新闻发布、门户。
• Innodb：更新（删除）操作为主要操作，或者要求数据的完整性较强。并发性好，支持事务和外键保证数据完整性。例如，OA办公自动化系统