兩個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) 可見：
- 記憶體表的傳回結果裡面0在最後一行
- InnoDB表的傳回結果裡0在第一行
主鍵索引的組織方式
- InnoDB表的資料就放在主鍵索引樹：t2的資料組織 主鍵索引上的值有序存儲。執行select *時，就會按照葉子節點從左到右掃描，是以0在第一行。
- Memory引擎的資料和索引是分開的：t1中的資料組織 記憶體表的資料以數組存放，而主鍵id索引裡，存的是每個資料的位置。主鍵id是hash索引，是以索引的key并非有序。
在記憶體表中，執行select *時，是全表掃描：順序掃描該數組。是以，0就是最後一個被讀到，并放入結果集的資料。
是以InnoDB、Memory資料組織方式不同：
- InnoDB引擎把資料放在主鍵索引，其他索引上儲存的是主鍵id。這種方式，我們稱之為索引組織表（Index Organizied Table）
- Memory引擎采用的是把資料單獨存放，索引上儲存資料位置的資料組織形式，我們稱之為堆組織表（Heap Organizied Table）
- InnoDB表的資料總是有序存放的，而記憶體表資料按寫入順序存放
- 當資料檔案有空洞時，InnoDB表在插入新資料時，為保證有序性，隻能在固定位置寫入新值，記憶體表找到空位即可插入新值
- 資料位置發生變化時，InnoDB表隻需修改主鍵索引，而記憶體表要修改所有索引
- InnoDB表用主鍵索引查詢時需走一次索引查找，用普通索引查詢時，走兩次索引查找。而記憶體表的所 有索引的“地位”相同
- InnoDB支援變長資料類型，不同記錄的長度可能不同；記憶體表不支援Blob、Text字段。即使定義了varchar(N)，實際也當作char(N)，即固定長度字元串，是以記憶體表的每行資料長度相同。
由于記憶體表的這些特性，每個資料行被删除後，空出位置都可被接下來要插入的資料複用。比如，如果要在表t1中執行：
可見，id=10這行出現在id=4之後，即原來id=5這行資料位置。 t1的這個主鍵索引是哈希索引，是以若執行範圍查詢：
select * from t1 where id<5; 用不上主鍵索引的，而走全表掃描。
若要讓記憶體表支援範圍掃描，應該怎麼辦呢 ？
hash索引和B-Tree索引
記憶體表其實也支援B-Tree索引。在id列上建立一個B-Tree索引，SQL語句可以這麼寫：
此時t1的資料組織形式：
這就類似InnoDB的b+樹索引了。
查詢對比
- 優化器選擇B-Tree索引，傳回結果：0~4
- force index 主鍵id索引，id=0這行在結果集末尾 我們都覺得記憶體表優勢是速度快，因為Memory引擎支援hash索引。更重要的原因是，記憶體表的所有資料都儲存在記憶體，記憶體讀寫速度肯定比磁盤快。
但仍然不推薦在生産環境上使用記憶體表，因為有如下嚴重問題：
記憶體表的鎖
記憶體表不支援行鎖，隻支援表鎖。是以，一張表隻要有更新，就會堵住其他所有在這個表上的讀寫。
這裡的表鎖和MDL鎖不同，但都是表級鎖。
模拟記憶體表的表級鎖
- sessionA的update語句要執行50s
- 該語句執行期間sessionB的查詢會進入鎖等待狀态
- session C的show processlist：
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架構下記憶體表的問題。
- M-S基本架構
- 業務正常通路主庫
- 備庫由于xxx而重新開機，記憶體表t1内容被清空
- 備庫重新開機後，用戶端發送一條update語句，修改t1的資料行，這時備庫應用線程就會報錯“找不到要更新的行”
這就會導緻主備同步停止。當然了，若此時發生主備切換，用戶端會看到，t1的資料“丢失”了。 在有proxy的架構，預設主備切換的邏輯由資料庫系統自己維護。這樣對用戶端來說，就是“網絡斷開，重連之後，發現記憶體表資料丢失了”。
這也還好呀，畢竟主備發生切換，連接配接會斷開，業務端能夠感覺到異常！ 但接下來記憶體表會讓現象更“詭異”。由于MySQL知道重新開機之後，會丢失記憶體表資料。是以，擔心主庫重新開機之後，出現主備不一緻，MySQL會在資料庫重新開機後，往binlog寫一行DELETE FROM t1。 此時若使用的雙M架構：
備庫重新開機時，備庫binlog裡的delete語句就會傳到主庫，然後把主庫記憶體表删除。這樣你在使用時，就會發現主庫的記憶體表資料突然被清空。
綜上，記憶體表不适合在生産環境使用。
但記憶體表執行速度就是快呀？！
- 若你的表更新量大，那麼并發度是個重要名額，InnoDB支援行鎖，并發度就是比記憶體表好
- 能放到記憶體表的資料量都不大。若你考慮的是讀性能，一個讀QPS很高 && 資料量不大的表，即使用InnoDB，資料也都會緩存在 Buffer Pool，讀性能也不會差！
是以，推薦普通記憶體表都用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索引，查找的速度比B-Tree索引快
- 臨時表資料隻有2000行，占用的記憶體有限
是以，可以将臨時表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); - 使用記憶體臨時表的執行效果 不論是導入資料的時間，還是執行join的時間，使用記憶體臨時表的速度都比使用InnoDB臨時表要快。