• notice
  • Congratulations on the launch of the Sought Tech site

Disadvantages of MySQL Memory Tables

MySQL memory table is not a universal tool to improve read performance, and it also has certain drawbacks. The following section will analyze the shortcomings of MySQL memory table for your reference and learning.

MySQL memory table is often used by us, but MySQL memory table is not a universal tool to improve read performance. In some cases, MySQL memory table may be slower than the actual table type B-TREE.

 
  1. CREATE TABLE `mem_test` (                                                               

  2.             `id` int(10) unsigned NOT NULL DEFAULT '0',                                           

  3.             `name` varchar(10) DEFAULT NULL,                                                      

  4.             `first` varchar(10) DEFAULT NULL,                                                     

  5.             PRIMARY KEY (`id`),                                                                   

  6.             KEY `NewIndex1` (`name`,`first`)                                                      

  7.           ) ENGINE=MEMORY ;  

  8.  

  9. CREATE TABLE `innodb_test` (                     

  10.                `id` int(10) unsigned NOT NULL DEFAULT '0',   

  11.                `name` varchar(10) DEFAULT NULL,               

  12.                `first` varchar(10) DEFAULT NULL,              

  13.                PRIMARY KEY (`id`),                            

  14.                KEY `NewIndex1` (`name`,`first`)               

  15.              ) ENGINE=InnoDB;  

  16.  

Such as:
1: In the case of = or <=>, it is fast, but in the case of < or >, it does not use the index

 
  1. mysql--root@localhost:17db 07:33:45>>explain select * from mem_test where id>3;  

  2. +----+-------------+------------+------+------------ ---+------+---------+------+------+-------------+  

  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |  

  4. +----+-------------+------------+------+------------ ---+------+---------+------+------+-------------+  

  5. | 1 | SIMPLE | mem_test | ALL | PRIMARY | NULL | NULL | NULL | 15 | Using where |  

  6. +----+-------------+------------+------+------------ ---+------+---------+------+------+-------------+  

  7. 1 row in set (0.00 sec)  

  8.  

  9. mysql--root@localhost:17db 07:33:49>>explain select * from innodb_test where id>3;  

  10. +----+-------------+-------------+-------+-------- -------+---------+---------+------+------+-------- -----+  

  11. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |  

  12. +----+-------------+-------------+-------+-------- -------+---------+---------+------+------+-------- -----+  

  13. | 1 | SIMPLE | innodb_test | range | PRIMARY | PRIMARY | 4 | NULL | 7 | Using where |  

  14. +----+-------------+-------------+-------+-------- -------+---------+---------+------+------+-------- -----+  

  15. 1 row in set (0.00 sec)  

  16.  

2: It cannot be used to improve the speed in the case of order by

 
  1. mysql--root@localhost:17db 07:33:55>>explain select * from innodb_test order by id;  

  2. +----+-------------+-------------+-------+-------- -------+---------+---------+------+------+-------+  

  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |  

  4. +----+-------------+-------------+-------+-------- -------+---------+---------+------+------+-------+  

  5. | 1 | SIMPLE | innodb_test | index | NULL | PRIMARY | 4 | NULL | 15 | |  

  6. +----+-------------+-------------+-------+-------- -------+---------+---------+------+------+-------+  

  7. 1 row in set (0.00 sec)  

  8.  

  9. mysql--root@localhost:17db 07:34:27>>explain select * from mem_test order by id;  

  10. +----+-------------+------------+------+------------ ---+------+---------+------+------+--------------- -+  

  11. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |  

  12. +----+-------------+------------+------+------------ ---+------+---------+------+------+--------------- -+  

  13. | 1 | SIMPLE | mem_test | ALL | NULL | NULL | NULL | NULL | 15 | Using filesort |  

  14. +----+-------------+------------+------+------------ ---+------+---------+------+------+--------------- -+  

  15. 1 row in set (0.00 sec)  

  16.  

3: Not sure how many rows are between the two values

 
  1. mysql--root@localhost:17db 07:37:14>>explain select count(1) from mem_test where id>3 and id<6;  

  2. +----+-------------+------------+------+------------ ---+------+---------+------+------+-------------+  

  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |  

  4. +----+-------------+------------+------+------------ ---+------+---------+------+------+-------------+  

  5. | 1 | SIMPLE | mem_test | ALL | PRIMARY | NULL | NULL | NULL | 20 | Using where |  

  6. +----+-------------+------------+------+------------ ---+------+---------+------+------+-------------+  

  7. 1 row in set (0.00 sec)  

  8.  

  9. mysql--root@localhost:17db 07:40:35>>explain select count(1) from innodb_test where id>3 and id<6;  

  10. +----+-------------+-------------+-------+-------- -------+---------+---------+------+------+-------- ------------------+  

  11. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |  

  12. +----+-------------+-------------+-------+-------- -------+---------+---------+------+------+-------- ------------------+  

  13. | 1 | SIMPLE | innodb_test | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where; Using index |  

  14. +----+-------------+-------------+-------+-------- -------+---------+---------+------+------+-------- ------------------+  

  15. 1 row in set (0.00 sec)  

  16.  

4: In the case of a multi-column index, hash scan can only be used if all specified, and B-tree can indeed use the leftmost end of the index to find

 
  1. mysql--root@localhost:17db 07:37:07>>explain select * from innodb_test where name='b';  

  2. +----+-------------+-------------+------+--------- ------+------------+---------+-------+------+------ --------------------+  

  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |  

  4. +----+-------------+-------------+------+--------- ------+------------+---------+-------+------+------ --------------------+  

  5. | 1 | SIMPLE | innodb_test | ref | NewIndex1 | NewIndex1 | 33 | const | 8 | Using where; Using index |  

  6. +----+-------------+-------------+------+--------- ------+------------+---------+-------+------+------ --------------------+  

  7. 1 row in set (0.00 sec)  

  8.  

  9. mysql--root@localhost:17db 07:37:10>>explain select * from mem_test where name='b';  

  10. +----+-------------+------------+------+------------ ---+------+---------+------+------+-------------+  

  11. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |  

  12. +----+-------------+------------+------+------------ ---+------+---------+------+------+-------------+  

  13. | 1 | SIMPLE | mem_test | ALL | NewIndex1 | NULL | NULL | NULL | 20 | Using where |  

  14. +----+-------------+------------+------+------------ ---+------+---------+------+------+-------------+  

  15.  

Of course, the memory table can also manually add btree

 
  1. CREATE INDEX BTREE_index USING BTREE on mem_test(name,first)  

  2. mysql--root@localhost:17db 03:36:41>>explain select * from mem_test where name='b';  

  3. +----+-------------+------------+------+------------ -------------+-------------+---------+-------+------ +-------------+  

  4. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |  

  5. +----+-------------+------------+------+------------ -------------+-------------+---------+-------+------ +-------------+  

  6. | 1 | SIMPLE | mem_test | ref | NewIndex1,BTREE_index | BTREE_index | 33 | const | 9 | Using where |  

  7. +----+-------------+------------+------+------------ -------------+-------------+---------+-------+------ +-------------+  

  8. 1 row in set (0.00 sec)  

Haha, it also uses the index.
So it is very important to choose the right storage engine.


Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

atorvastatin 10mg ca & lt;a href="https://lipiws.top/"& gt;lipitor order& lt;/a& gt; brand atorvastatin 10mg

Iiwryl

2024-03-09

Leave a Reply

+