Chinaunix首页 | 论坛 | 博客

天堂zuoxingyu.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

909413335

  • 博客访问： 492110
  • 博文数量： 99
  • 博客积分： 3621
  • 博客等级： 中校
  • 技术积分： 1089
  • 用 户 组： 普通用户
  • 注册时间： 2010-06-22 16:29
文章分类

全部博文（99）

文章存档

2012年（21）

2011年（28）

2010年（50）

我的朋友
最近访客
推荐博文
相关博文
Mysql Hash index for Memory Engine

分类： Mysql/postgreSQL

2012-04-12 11:16:56

在memory引擎中，只支持hash index,hash index 对于等值查询效率是非常高的，但是对范围查询无能无力，从它的实现原理就能得到，每个值经过hash算法之后，得到一个hash值，通过比较hash值得到具体的数据，很明显hash值是没有任何规律的。
 

点击(此处)折叠或打开

  1. mysql> create table tb(id int) engine=memory;
  2. Query OK, 0 rows affected (0.02 sec)

  4. mysql> insert into tb values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
  5. Query OK, 10 rows affected (0.00 sec)
  6. Records: 10 Duplicates: 0 Warnings: 0

  8. mysql> create index idx_id on tb(id);
  9. Query OK, 10 rows affected (0.01 sec)
  10. Records: 10 Duplicates: 0 Warnings: 0

  12. mysql> explain select * from tb where id=1;
  13. +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
  14. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  15. +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
  16. | 1 | SIMPLE | tb | ref | idx_id | idx_id | 5 | const | 2 | Using where |
  17. +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
  18. 1 row in set (0.00 sec)
  19. #等值查询，用到了索引
  20. mysql> explain select * from tb where id=5;
  21. +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
  22. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  23. +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
  24. | 1 | SIMPLE | tb | ref | idx_id | idx_id | 5 | const | 2 | Using where |
  25. +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
  26. 1 row in set (0.00 sec)

  28. mysql> explain select * from tb where id>5;
  29. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  30. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  31. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  32. | 1 | SIMPLE | tb | ALL | idx_id | NULL | NULL | NULL | 10 | Using where |
  33. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  34. 1 row in set (0.00 sec)
  35. #范围查询，全表扫描了
  36. mysql> explain select * from tb where id<2;
  37. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  38. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  39. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  40. | 1 | SIMPLE | tb | ALL | idx_id | NULL | NULL | NULL | 10 | Using where |
  41. +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  42. 1 row in set (0.00 sec)

  44. mysql> show create table tb;
  45. +-------+---------------------------------------------------------------------------------------------------------------+
  46. | Table | Create Table |
  47. +-------+---------------------------------------------------------------------------------------------------------------+
  48. | tb | CREATE TABLE `tb` (
  49.   `id` int(11) DEFAULT NULL,
  50.   KEY `idx_id` (`id`)
  51. ) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
  52. +-------+---------------------------------------------------------------------------------------------------------------+
  53. 1 row in set (0.00 sec)

mysql> show index from tb;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb    |          1 | idx_id   |            1 | id          | NULL      |           5 |     NULL | NULL   | YES  | HASH       |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

 


阅读(1122) | 评论(1) | 转发(0) |
0

上一篇：MySQL Innodb日志机制深入分析

下一篇：mysql - 更改库名称

给主人留下些什么吧！~~

9094133352012-04-12 14:22:17

mysql> create index idx_id_1 on tb(id) using BTREE
    -> ;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    7
Current database: test

Query OK, 10 rows affected (0.02 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> show index from tb;
+-------+------------+----------+--------------+-------------+-----------+---

回复 | 举报
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6