建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網營銷解決方案
MySQL內存表的弊端
MySQL內存表使我們經常會用到的，但是 MySQL內存表的也不是提高讀性能的***工具，在有些情況下，MySQL內存表可能會比其實表類型的B-TREE更慢。
創(chuàng)新互聯(lián)公司2013年開創(chuàng)至今，先為比如等服務建站，比如等地企業(yè)，進行企業(yè)商務咨詢服務。為比如企業(yè)網站制作PC+手機+微官網三網同步一站式服務解決您的所有建站問題。
- CREATE TABLE `mem_test` (
- `id` int(10) unsigned NOT NULL DEFAULT '0',
- `name` varchar(10) DEFAULT NULL,
- `first` varchar(10) DEFAULT NULL,
- PRIMARY KEY (`id`),
- KEY `NewIndex1` (`name`,`first`)
- ) ENGINE=MEMORY ;
- CREATE TABLE `innodb_test` (
- `id` int(10) unsigned NOT NULL DEFAULT '0',
- `name` varchar(10) DEFAULT NULL,
- `first` varchar(10) DEFAULT NULL,
- PRIMARY KEY (`id`),
- KEY `NewIndex1` (`name`,`first`)
- ) ENGINE=InnoDB;
如：
1：在= 或者<=> 情況下，飛快，但是在如< 或>情況下，他是不使用索引
- mysql--root@localhost:17db 07:33:45>>explain select * from mem_test where id>3;
- +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
- | 1 | SIMPLE | mem_test | ALL | PRIMARY | NULL | NULL | NULL | 15 | Using where |
- +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
- 1 row in set (0.00 sec)
- mysql--root@localhost:17db 07:33:49>>explain select * from innodb_test where id>3;
- +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
- | 1 | SIMPLE | innodb_test | range | PRIMARY | PRIMARY | 4 | NULL | 7 | Using where |
- +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
- 1 row in set (0.00 sec)
2：不能用在order by情況下來提高速度
- mysql--root@localhost:17db 07:33:55>>explain select * from innodb_test order by id;
- +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+
- | 1 | SIMPLE | innodb_test | index | NULL | PRIMARY | 4 | NULL | 15 | |
- +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+
- 1 row in set (0.00 sec)
- mysql--root@localhost:17db 07:34:27>>explain select * from mem_test order by id;
- +----+-------------+----------+------+---------------+------+---------+------+------+----------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+----------+------+---------------+------+---------+------+------+----------------+
- | 1 | SIMPLE | mem_test | ALL | NULL | NULL | NULL | NULL | 15 | Using filesort |
- +----+-------------+----------+------+---------------+------+---------+------+------+----------------+
- 1 row in set (0.00 sec)
3：不能確定倆值之間有多少行
- mysql--root@localhost:17db 07:37:14>>explain select count(1) from mem_test where id>3 and id<6;
- +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
- | 1 | SIMPLE | mem_test | ALL | PRIMARY | NULL | NULL | NULL | 20 | Using where |
- +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
- 1 row in set (0.00 sec)
- mysql--root@localhost:17db 07:40:35>>explain select count(1) from innodb_test where id>3 and id<6;
- +----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
- | 1 | SIMPLE | innodb_test | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where; Using index |
- +----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
- 1 row in set (0.00 sec)
4：在多列索引的情況下，只有全部指定才能利用hash掃描，而B-tree確可以利用索引的最左端來查找
- mysql--root@localhost:17db 07:37:07>>explain select * from innodb_test where name='b';
- +----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+
- | 1 | SIMPLE | innodb_test | ref | NewIndex1 | NewIndex1 | 33 | const | 8 | Using where; Using index |
- +----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+
- 1 row in set (0.00 sec)
- mysql--root@localhost:17db 07:37:10>>explain select * from mem_test where name='b';
- +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
- | 1 | SIMPLE | mem_test | ALL | NewIndex1 | NULL | NULL | NULL | 20 | Using where |
- +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
當然內存表也可以手動添加btree
- CREATE INDEX BTREE_index USING BTREE on mem_test(name,first)
- mysql--root@localhost:17db 03:36:41>>explain select * from mem_test where name='b';
- +----+-------------+----------+------+-----------------------+-------------+---------+-------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+----------+------+-----------------------+-------------+---------+-------+------+-------------+
- | 1 | SIMPLE | mem_test | ref | NewIndex1,BTREE_index | BTREE_index | 33 | const | 9 | Using where |
- +----+-------------+----------+------+-----------------------+-------------+---------+-------+------+-------------+
- 1 row in set (0.00 sec)
哈哈，它也用到索引了。
所以要選擇合適的存儲引擎至關重要。
【編輯推薦】
MySQL獨立表空間的優(yōu)缺點
mysql數據庫大小寫的問題討論
MySQL MyISAM表結構的恢復
MySQL InnoDB表結構的恢復
深度解析MySQL創(chuàng)建關聯(lián)表
分享名稱：MySQL內存表的弊端
URL鏈接：http://fisionsoft.com.cn/article/dphooij.html
