当前位置 博文首页 > JavaEdge全是干货的技术号:有了InnoDB,Memory存储引擎还有意义
两个group by 语句都用了order by null,为什么使用内存临时表得到的语句结果里,0这个值在最后一行;而使用磁盘临时表得到的结果里,0这个值在第一行?
mysql> select *
-> from t1;
+----+------+
| id | c |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 0 | 0 |
+----+------+
10 rows in set (0.00 sec)
mysql> select *
-> from t2;
+----+------+
| id | c |
+----+------+
| 0 | 0 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
+----+------+
10 rows in set (0.00 sec)
可见:
InnoDB表的数据就放在主键索引树:t2的数据组织
主键索引上的值有序存储。执行select *
时,就会按照叶子节点从左到右扫描,所以0在第一行。
Memory引擎的数据和索引是分开的:t1中的数据组织
内存表的数据以数组存放,而主键id索引里,存的是每个数据的位置。主键id是hash索引,所以索引的key并非有序。
在内存表中,执行select *
时,是全表扫描:顺序扫描该数组。因此,0就是最后一个被读到,并放入结果集的数据。
所以InnoDB、Memory数据组织方式不同:
由于内存表的这些特性,每个数据行被删除后,空出位置都可被接下来要插入的数据复用。比如,如果要在表t1中执行:
可见,id=10这行出现在id=4之后,即原来id=5这行数据位置。
t1的这个主键索引是哈希索引,因此若执行范围查询:
select * from t1 where id<5;
用不上主键索引的,而走全表扫描。
若要让内存表支持范围扫描,应该怎么办呢 ?
内存表其实也支持B-Tree索引。在id列上创建一个B-Tree索引,SQL语句可以这么写:
此时t1的数据组织形式:
这就类似InnoDB的b+树索引了。
但仍然不推荐在生产环境上使用内存表,因为有如下严重问题:
内存表不支持行锁,只支持表锁。因此,一张表只要有更新,就会堵住其他所有在这个表上的读写。
这里的表锁和MDL锁不同,但都是表级锁。
mysql> show processlist;
+----+-----------------+-----------------+-----------------+---------+--------+------------------------------+---------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+-----------------+---------+--------+------------------------------+---------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 390719 | Waiting on empty queue | NULL |
| 41 | root | localhost | common_mistakes | Query | 8 | User sleep | update t1 set id=sleep(10) where id=1 |
| 47 | root | localhost | common_mistakes | Query | 4 | Waiting for table level lock | select * from t1 where id=2 |
| 49 | root | localhost:56378 | common_mistakes | Sleep | 100 | | NULL |
| 51 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-----------------+-----------------+-----------------+---------+--------+------------------------------+---------------------------------------+
5 rows in set (0.00 sec)
表锁限制了并发访问。所以,内存表的锁粒度问题,决定了它在处理并发事务时,性能也不好。
数据放在内存中,是内存表优势,但也是劣势。数据库重启时,所有内存表会被清空。
若数据库异常重启,内存表被清空也就清空了,好像也不会有啥问题呀!但在高可用架构下,内存表的这个特点就是个bug!
M-S架构下内存表的问题。
这就会导致主备同步停止。当然了,若此时发生主备切换,客户端会看到,t1的数据“丢失”了。
在有proxy的架构,默认主备切换的逻辑由数据库系统自己维护。这样对客户端来说,就是“网络断开,重连之后,发现内存表数据丢失了”。
这也还好呀,毕竟主备发生切换,连接会断开,业务端能够感知到异常!
但接下来内存表会让现象更“诡异”。由于MySQL知道重启之后,会丢失内存表数据。所以,担心主库重启之后,出现主备不一致,MySQL会在数据库重启后,往binlog写一行DELETE FROM t1。
此时若使用的双M架构:
备库重启时,备库binlog里的delete语句就会传到主库,然后把主库内存表删除。这样你在使用时,就会发现主库的内存表数据突然被清空。
综上,内存表不适合在生产环境使用。
但内存表执行速度就是快呀?!
所以,推荐普通内存表都用InnoDB表替代。
but!有个场景是例外:用户临时表,在数据量可控,不会耗费过多内存的情况下,你可以考虑使用内存表。
内存临时表刚好可以无视内存表的两个不足,主要因为:
看看join语句优化案例,推荐创建一个InnoDB临时表,使用的语句序列是:
create temporary table temp_t
(
id int primary key,
a int,
b int,
index (b)
) engine = innodb;
insert into temp_t
select *
from t2
where b >= 1
and b <= 2000;
select *
from t1
join temp_t on (t1.b = temp_t.b);
这里使用内存临时表的效果更好:
因此,可以将临时表temp_t改成内存临时表,并且在字段b上创建一个hash索引。
create temporary table temp_t
(
id int primary key,
a int,
b int,
index (b)
) engine = memory;
insert into temp_t
select *
from t2
where b >= 1
and b <= 2000;
select *
from t1
join temp_t on (t1.b = temp_t.b);