map7表结构:mysql> desc map17;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| col | int(5) | NO | MUL | 0 | |
| row | int(5) | NO | MUL | 0 | |
| lat | double | NO | | 0 | |
| lon | double | NO | | 0 | |
| x | double | YES | | NULL | |
| y | double | YES | | NULL | |
| picname | varchar(20) | NO | | NULL | |
| pic | blob | NO | | NULL | |
+---------+-------------+------+-----+---------+----------------+
9 rows in set
索引:mysql> show index from map17;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| map17 | 0 | PRIMARY | 1 | id | A | 227523 | NULL | NULL | | BTREE | |
| map17 | 1 | row_col | 1 | row | A | 98 | NULL | NULL | | BTREE | |
| map17 | 1 | row_col | 2 | col | A | 75841 | NULL | NULL | | BTREE | |
| map17 | 1 | col_row | 1 | col | A | 1710 | NULL | NULL | | BTREE | |
| map17 | 1 | col_row | 2 | row | A | 75841 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set
select row,col,pic from map17 where row<8 and col<8(64条记录)
第一次查询时间为:4469 第二次查询:时间为:547
mysql> explain select row,col,pic from map17 where row<8 and col<8;
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | map17 | range | row_col,col_row | col_row | 4 | NULL | 336 | Using where |
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
1 row in set
select row,col,pic from map17 where row<38 and row>=30 and col>=200 and col <208(64条记录)
第一次查询时间为:3891 第二次查询时间为:563
怎样提高select速度?
map15表结构mysql> desc map15;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| col | int(5) | NO | MUL | NULL | |
| row | int(5) | NO | MUL | NULL | |
| lat | double | NO | | NULL | |
| lon | double | NO | | NULL | |
| x | double | YES | | NULL | |
| y | double | YES | | NULL | |
| picname | varchar(20) | NO | | NULL | |
| pic | blob | NO | | NULL | |
+---------+-------------+------+-----+---------+----------------+
9 rows in set索引:mysql> show index from map15;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| map15 | 0 | PRIMARY | 1 | id | A | 214578 | NULL | NULL | | BTREE | |
| map15 | 1 | row_col | 1 | row | A | 260 | NULL | NULL | | BTREE | |
| map15 | 1 | row_col | 2 | col | A | 71526 | NULL | NULL | | BTREE | |
| map15 | 1 | col_row | 1 | col | A | 476 | NULL | NULL | | BTREE | |
| map15 | 1 | col_row | 2 | row | A | 71526 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in setselect row,col,pic from map15 where row<8 and col<8(64条记录)
第一次查询时间为:15172 第二次查询时间为:14938 第三次查询时间为:14922
mysql> explain select row,col,pic from map15 where row<8 and col<8;
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | map15 | range | row_col,col_row | col_row | 4 | NULL | 1199 | Using where |
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
1 row in set
map17记录数: 32940 map15记录数: 29800
map17和map15在统一数据库中,这两个表结构一样,索引一样,为什么查询出来这么大区别?目的是:用这两种select语句查询出64条记录,怎样提高select速度MySQLselect索引数据库
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| col | int(5) | NO | MUL | 0 | |
| row | int(5) | NO | MUL | 0 | |
| lat | double | NO | | 0 | |
| lon | double | NO | | 0 | |
| x | double | YES | | NULL | |
| y | double | YES | | NULL | |
| picname | varchar(20) | NO | | NULL | |
| pic | blob | NO | | NULL | |
+---------+-------------+------+-----+---------+----------------+
9 rows in set
索引:mysql> show index from map17;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| map17 | 0 | PRIMARY | 1 | id | A | 227523 | NULL | NULL | | BTREE | |
| map17 | 1 | row_col | 1 | row | A | 98 | NULL | NULL | | BTREE | |
| map17 | 1 | row_col | 2 | col | A | 75841 | NULL | NULL | | BTREE | |
| map17 | 1 | col_row | 1 | col | A | 1710 | NULL | NULL | | BTREE | |
| map17 | 1 | col_row | 2 | row | A | 75841 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set
select row,col,pic from map17 where row<8 and col<8(64条记录)
第一次查询时间为:4469 第二次查询:时间为:547
mysql> explain select row,col,pic from map17 where row<8 and col<8;
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | map17 | range | row_col,col_row | col_row | 4 | NULL | 336 | Using where |
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
1 row in set
select row,col,pic from map17 where row<38 and row>=30 and col>=200 and col <208(64条记录)
第一次查询时间为:3891 第二次查询时间为:563
怎样提高select速度?
map15表结构mysql> desc map15;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| col | int(5) | NO | MUL | NULL | |
| row | int(5) | NO | MUL | NULL | |
| lat | double | NO | | NULL | |
| lon | double | NO | | NULL | |
| x | double | YES | | NULL | |
| y | double | YES | | NULL | |
| picname | varchar(20) | NO | | NULL | |
| pic | blob | NO | | NULL | |
+---------+-------------+------+-----+---------+----------------+
9 rows in set索引:mysql> show index from map15;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| map15 | 0 | PRIMARY | 1 | id | A | 214578 | NULL | NULL | | BTREE | |
| map15 | 1 | row_col | 1 | row | A | 260 | NULL | NULL | | BTREE | |
| map15 | 1 | row_col | 2 | col | A | 71526 | NULL | NULL | | BTREE | |
| map15 | 1 | col_row | 1 | col | A | 476 | NULL | NULL | | BTREE | |
| map15 | 1 | col_row | 2 | row | A | 71526 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in setselect row,col,pic from map15 where row<8 and col<8(64条记录)
第一次查询时间为:15172 第二次查询时间为:14938 第三次查询时间为:14922
mysql> explain select row,col,pic from map15 where row<8 and col<8;
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | map15 | range | row_col,col_row | col_row | 4 | NULL | 1199 | Using where |
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
1 row in set
map17记录数: 32940 map15记录数: 29800
map17和map15在统一数据库中,这两个表结构一样,索引一样,为什么查询出来这么大区别?目的是:用这两种select语句查询出64条记录,怎样提高select速度MySQLselect索引数据库
解决方案 »
- Mysql 的rank 函数实现的问题
- 实现下面功能的select语句(mysql),究竟该如何写呢?
- update语句的询问!
- 手机号码中筛选出AABB,ABAB,AAABB等类型的号码,求SQL语句
- 数据库函数依赖的问题
- 我用mysql的工具,不小心把数据库里的所有的表全部删除,有没有恢复的方法,
- sos:有关linux下postgres的JDBC程序配置问题
- 在phpmyadmin中的以下显示,其中的多余空间,是指什么啊,
- 求助——求一款免费的 比较好的mysql 监控工具
- 请教slave机器不执行binlog语句
- MYSQ查询问题
- 用 mysql events 每周日,晚上23点整。执行一个sql查询语句大致怎么写呢
但是问题是必须要取出pic啊,要动态加载图片的,还有弱弱的问下,select的速度集体与哪些因素有关呢?(是否能够解决速度问题?)
或者改整个设计,不要把图片存放在数据库中,而把把图片单独做为一个个文件直接存放在磁盘上,表中仅保存文件名。