mysql myisam vs innodb

作者: 大龄程序员在帝都 | 来源:发表于2016-07-29 13:54 被阅读69次

mysql myisam vs innodb
2018-03-19
系统原理-乐观锁与悲观锁
MySQL
MyISAM和InnoDB的区别
引擎 | ARCHIVE
myisam innodb 区别
MyISAM和InnoDB的区别
浅谈MySQL分布式事物解决方案
MySql数据库学习笔记

问题：有一张表A记录用户的一些操作信息，每天晚上12点执行批量对这张表中当天的用户操作进行统计，方便后期应用决策。

思路：为了统计比较快，对表A进行处理，每天统计完成以后，记录当天的统计信息到表B，对外界统一提供B表信息，同时把A表的信息备份到A_HISTORY表中，这样A表中只存储一天的数据，即使数据量比较大，统计也比较快。B表中只存储统计过的信息，A_HISTORY存储除当天的信息。所以后期对A_HISTORY建立分区，每一年一个分区，方便后期按年统计，出年度报表。这是从设计上进行优化。当然从表上也可以进行优化：
1、是选择myisam表存储引擎还是innodb存储引擎
2、由于查询都是count类型加where条件，如何建立索引，才能让速度更快

1、对于以上需求，准备采用myisam存储引擎原因如下；

应用程序对查询性能要求较高，就要使用MYISAM了。MYISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MYISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。

MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用

InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表

MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

基于以上三点原因，选择myisam作为存储引擎。

1、创建一个新库导入数据库创建脚本执行如下

脚本内容在文末

MariaDB [(none)]> create database  perf;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use perf;
Database changed
MariaDB [perf]> delimiter //
MariaDB [perf]> source /root/tt/test.sql//
Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected (0.10 sec)

调用存储过程执行对myisam插入100万1条数据：
耗时：53秒


MariaDB [perf]> delimiter ;
MariaDB [perf]> call add_data_myisam;
Query OK, 1 row affected, 1 warning (53.75 sec)

MariaDB [perf]> select count(1) from test_myisam;
+----------+
| count(1) |
+----------+
|  1000001 |
+----------+
1 row in set (0.00 sec)

调用存储过程执行对innodb引擎插入100万
直接处于这种状态30分钟了，也没有执行完。为什么innodb这么慢？？？


MariaDB [perf]> call add_data_innodb();

Innodb需要扫描数据才能确定总行数，而myisam直接存储在数据库管理表中，所以更快：

MariaDB [perf]> select count(*) from test_myisam;
+----------+
| count(*) |
+----------+
|  1000001 |
+----------+
1 row in set (0.00 sec)

MariaDB [perf]> select count(*) from test_innodb;
+----------+
| count(*) |
+----------+
|  1000001 |
+----------+
1 row in set (0.36 sec)

带有where条件的查询myisam和innodb还是有区别的

MariaDB [perf]> SELECT COUNT(1)  FROM test_myisam WHERE account = 'cloudp' and '2016-07-29'=date(create_time);
+----------+
| COUNT(1) |
+----------+
|  1000001 |
+----------+
1 row in set, 1 warning (1.33 sec)

MariaDB [perf]> SELECT COUNT(1)  FROM test_innodb WHERE account = 'cloudp' and '2016-07-29'=date(create_time);
+----------+
| COUNT(1) |
+----------+
|  1000001 |
+----------+
1 row in set, 1 warning (1.76 sec)

MariaDB [perf]> SELECT COUNT(1)   FROM test_myisam WHERE account = 'cloudp' and  status = 3 and '2016-07-29'=date(create_time);
+----------+
| COUNT(1) |
+----------+
|   167198 |
+----------+
1 row in set, 1 warning (0.48 sec)

MariaDB [perf]> SELECT COUNT(1)   FROM test_innodb WHERE account = 'cloudp' and  status = 3 and '2016-07-29'=date(create_time);
+----------+
| COUNT(1) |
+----------+
|   166586 |
+----------+
1 row in set, 1 warning (0.80 sec)

未建立索引之前的操作时间

MariaDB [perf]> explain SELECT COUNT(1)  FROM test_myisam WHERE account = 'cloudp' and '2016-07-29'=date(create_time);
+------+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | test_myisam | ALL  | NULL          | NULL | NULL    | NULL | 1000001 | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

MariaDB [perf]> explain SELECT COUNT(1)  FROM test_innodb WHERE account = 'cloudp' and '2016-07-29'=date(create_time);
+------+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | test_innodb | ALL  | NULL          | NULL | NULL    | NULL | 997001 | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

MariaDB [perf]> explain SELECT COUNT(1)   FROM test_myisam WHERE account = 'cloudp' and  status = 3 and '2016-07-29'=date(create_time);
+------+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | test_myisam | ALL  | NULL          | NULL | NULL    | NULL | 1000001 | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

MariaDB [perf]> explain  SELECT COUNT(1)   FROM test_innodb WHERE account = 'cloudp' and  status = 3 and '2016-07-29'=date(create_time);
+------+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | test_innodb | ALL  | NULL          | NULL | NULL    | NULL | 997001 | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

根据索引的最左前缀原则：建立索引如下

create index test_index on test_myisam(account,create_time,status); 
--这里的索引建立的也不合理，因为首先我们把日期放在最前面，然后是account，最后status，我们要取当天的数据，所以日期放最前面可以一次性定位扫描更少的数据。

create index test_index on test_innodb(account,create_time,status);

建立完索引以后来看下再次查询：
加完以后更慢了，为什么呢？来看下查询计划 explain一下：
扫描行数依然是200万行，依然是全扫描，并且200万索引扫描后，还要和具体表内容结合，所以更慢了。理论上，我们扫描的行数会减少，如果索引真正生效。

MariaDB [perf]> select count(*) from test_myisam where account='cloudp' and status=1 and '2016-07-29'= date(create_time);//
+----------+
| count(*) |
+----------+
|   332220 |
+----------+
1 row in set, 1 warning (1.86 sec)

MariaDB [perf]> explain select count(*) from test_myisam where account='cloudp' and status=1 and '2016-07-29'= date(create_time);//
+------+-------------+-------------+-------+---------------+------------+---------+------+---------+--------------------------+
| id   | select_type | table       | type  | possible_keys | key        | key_len | ref  | rows    | Extra                    |
+------+-------------+-------------+-------+---------------+------------+---------+------+---------+--------------------------+
|    1 | SIMPLE      | test_myisam | index | NULL          | test_index | 14      | NULL | 2000002 | Using where; Using index |
+------+-------------+-------------+-------+---------------+------------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)

下面需要解决如何让索引真正生效的问题？

下面提供一种思路：修改索引的顺序，让account，status、前两个生效，create_time上有函数所以create_time没有办法用索引。这是一个次优的方案，最优的方案是三个都走索引！

create index test_f_index on test_myisam(account,status,create_time);

下面是索引使用情况：节省了0.7秒，已经是很大的提升了，下面我们再修改索引顺序。

MariaDB [perf]> select count(*) from test_myisam where account='cloudp' and status=1 and '2016-07-29'= date(create_time);//
+----------+
| count(*) |
+----------+
|   332220 |
+----------+
1 row in set, 1 warning (1.86 sec)

MariaDB [perf]> explain select count(*) from test_myisam where account='cloudp' and status=1 and '2016-07-29'= date(create_time);//
+------+-------------+-------------+-------+---------------+------------+---------+------+---------+--------------------------+
| id   | select_type | table       | type  | possible_keys | key        | key_len | ref  | rows    | Extra                    |
+------+-------------+-------------+-------+---------------+------------+---------+------+---------+--------------------------+
|    1 | SIMPLE      | test_myisam | index | NULL          | test_index | 14      | NULL | 2000002 | Using where; Using index |
+------+-------------+-------------+-------+---------------+------------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)

MariaDB [perf]> explain select count(*) from test_myisam where account='cloudp' and status=1 and '2016-07-29'= date(create_time);//
+------+-------------+-------------+-------+---------------+------------+---------+------+---------+--------------------------+
| id   | select_type | table       | type  | possible_keys | key        | key_len | ref  | rows    | Extra                    |
+------+-------------+-------------+-------+---------------+------------+---------+------+---------+--------------------------+
|    1 | SIMPLE      | test_myisam | index | NULL          | test_index | 14      | NULL | 2000002 | Using where; Using index |
+------+-------------+-------------+-------+---------------+------------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)

MariaDB [perf]> create index test_f_index on test_myisam(account,status,create_time);//
Query OK, 2000002 rows affected (14.12 sec)
Records: 2000002  Duplicates: 0  Warnings: 0

MariaDB [perf]> explain select count(*) from test_myisam where account='cloudp' and status=1 and '2016-07-29'= date(create_time);//
+------+-------------+-------------+------+---------------+--------------+---------+-------------+--------+--------------------------+
| id   | select_type | table       | type | possible_keys | key          | key_len | ref         | rows   | Extra                    |
+------+-------------+-------------+------+---------------+--------------+---------+-------------+--------+--------------------------+
|    1 | SIMPLE      | test_myisam | ref  | test_f_index  | test_f_index | 10      | const,const | 660048 | Using where; Using index |
+------+-------------+-------------+------+---------------+--------------+---------+-------------+--------+--------------------------+
1 row in set (0.06 sec)

MariaDB [perf]> select count(*) from test_myisam where account='cloudp' and status=1 and '2016-07-29'= date(create_time);//
+----------+
| count(*) |
+----------+
|   332220 |
+----------+
1 row in set (1.13 sec)

如何让create_time的索引也用上呢：如下：对create_time用like 'xxx%'走这个索引：这次优化变为了0.56秒，又加速了一倍。



MariaDB [perf]> select count(*) from test_myisam where account='cloudp' and status=3 and create_time like '2016-07-29%';//
+----------+
| count(*) |
+----------+
|   167198 |
+----------+
1 row in set (0.56 sec)

MariaDB [perf]> select count(*) from test_myisam where account='cloudp' and status=1 and '2016-07-29'= date(create_time);//
+----------+
| count(*) |
+----------+
|   332220 |
+----------+
1 row in set (1.11 sec)

MariaDB [perf]> explain select count(*) from test_myisam where account='cloudp' and status=3 and create_time like '2016-07-29%';//
+------+-------------+-------------+------+-------------------------+--------------+---------+-------------+--------+--------------------------+
| id   | select_type | table       | type | possible_keys           | key          | key_len | ref         | rows   | Extra                    |
+------+-------------+-------------+------+-------------------------+--------------+---------+-------------+--------+--------------------------+
|    1 | SIMPLE      | test_myisam | ref  | test_index,test_f_index | test_f_index | 10      | const,const | 347582 | Using where; Using index |
+------+-------------+-------------+------+-------------------------+--------------+---------+-------------+--------+--------------------------+
1 row in set (0.00 sec)

MariaDB [perf]> explain select count(*) from test_myisam where account='cloudp' and status=1 and '2016-07-29'= date(create_time);//
+------+-------------+-------------+------+---------------+--------------+---------+-------------+--------+--------------------------+
| id   | select_type | table       | type | possible_keys | key          | key_len | ref         | rows   | Extra                    |
+------+-------------+-------------+------+---------------+--------------+---------+-------------+--------+--------------------------+
|    1 | SIMPLE      | test_myisam | ref  | test_f_index  | test_f_index | 10      | const,const | 660048 | Using where; Using index |
+------+-------------+-------------+------+---------------+--------------+---------+-------------+--------+--------------------------+
1 row in set (0.00 sec)

reference

mysql myisam vs innodb
问题：有一张表A记录用户的一些操作信息，每天晚上12点执行批量对这张表中当天的用户操作进行统计，方便后期应用决策...
2018-03-19
mysql MyISAM引擎和InnoDB的介绍 1、MyISAM MyISAM是MySQL的默认存储引擎，基于...
系统原理-乐观锁与悲观锁
InnoDB与MyISAM Mysql 在5.5之前默认使用 MyISAM 存储引擎，之后使用 InnoDB 。查...
MySQL
InnoDB vs MyISAM InnoDB是聚簇索引（叶子节点存数据），MyISAM是非聚簇索引（叶子节点存指...
MyISAM和InnoDB的区别
MySQL默认采用的是MyISAM。 MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMI...
引擎 | ARCHIVE
mysql常用引擎MyISAM和InnoDB MyISAM：插入快查询快 InnoDB：修改快支持事务 ARC...
myisam innodb 区别
MySQL默认采用的是MyISAM。 MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMI...
MyISAM和InnoDB的区别
MySQL默认采用的是MyISAM。 MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMI...
浅谈MySQL分布式事物解决方案
引言：MySQL存储引擎主要分为InnoDB、MyISAM，它们的主要区别是InnoDB支持事物，而MyISAM不...
MySql数据库学习笔记
MySql分层结构： 1.MySQL中InnoDB与MyISAM的区别： a.InnoDB引擎是MySql的默认引...