1.给表加索引并且显示索引信息
mysql> desc oso_slope_one;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| item_id1 | int(11) | NO | MUL | 0 | |
| item_id2 | int(11) | NO | | 0 | |
| times | int(11) | NO | | 0 | |
| rating | decimal(14,4) | NO | | 0.0000 | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.90 sec)mysql> show index from oso_slope_one;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| oso_slope_one | 1 | item_id1 | 1 | item_id1 | A | 1093 | NULL | NULL | | BTREE | |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.02 sec)mysql> alter table oso_slope_one add index item_id2(item_id2);
Query OK, 21320221 rows affected (4 min 22.46 sec)
Records: 21320221 Duplicates: 0 Warnings: 0mysql> show index from oso_slope_one;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| oso_slope_one | 1 | item_id1 | 1 | item_id1 | A | 66418 | NULL | NULL | | BTREE | |
| oso_slope_one | 1 | item_id2 | 1 | item_id2 | A | 99163 | NULL | NULL | | BTREE | |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.02 sec)2.每个派生表必须有它自己别名
select item_id2 from (select a.item_id as item_id1,b.item_id as item_id2,count(*) as times, sum(a.rating-b.rating) as rating from oso_user_ratings a,oso_user_ratings b where a.item_id = '1' and b.item_id != a.item_id and a.user_id=b.user_id group by a.item_id,b.item_id) AS tmp where item_id1 = '1' group by item_id2 order by sum(rating/times) limit 200每个派生表必须有它自己别名。
Every derived table must have its own alias
例如上面的sql语句必须加上as tmp，否则会报错。3.用select...into outfile导出数据
a. 导出数据时可以给文件加上路径，如d:/song.txt
mysql> select * from song into outfile "d:/song.txt";
Query OK, 7 rows affected (0.02 sec)b. 导出数据时目的文件不能是已经存在的文件，否则需要先删除
mysql> select songid,songname from song into outfile "d:/song.txt";
ERROR 1086 (HY000): File 'd:/song.txt' already exists4.替换字段中某些值为其它的值
替换表special中字段genre中含有'流行摇滚/Pop'的为'流行摇滚/Pop Rock'
UPDATE special SET genre = REPLACE(genre, '流行摇滚/Pop', '流行摇滚/Pop Rock') WHERE specialid > 22593;替换表special中字段singername里面的空格( )为空
UPDATE special SET singername = REPLACE(singername,' ','') WHERE singername LIKE '% %';5.关于mysql在本机及远程主机访问的问题
为了让mysql可以远程访问，host可以用指定主机的ip(只能让指定的主机访问)或用%(可以让所有的主机访问)为了让mysql可以在本机访问，则在数据库mysql的表user中必须有一条记录的字段host为localhost，否则在本机不能访问。
mysql> use mysql;
Database changed
mysql> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| % | root | *8D20E4B2CA17D3AAEC1680514E4694C9A19005E5 |
| localhost | root | *8D20E4B2CA17D3AAEC1680514E4694C9A19005E5 |
| 127.0.0.1 | root | |
| localhost | | |
| debian | | |
+-----------+------+-------------------------------------------+
5 rows in set (0.00 sec)怎样让MySQL能被远程访问？,http://iamcaihuafeng.blog.sohu.com/130492432.html6. 怎样关闭MySQL的bin log
如果想关闭MySQL的bin log功能，则需要将log-bin及binlog_format两个选项均注释起来，否则如果只注释log-bin而不注释binlog_format会报错的。
# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin# binary logging format - mixed recommended
binlog_format=mixedYou need to use --log-bin to make --binlog-format work用下面的配置就没有问题了。
# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin# binary logging format - mixed recommended
#binlog_format=mixed7.FORCE INDEX的用法
表model_analysis_default的结构，默认有一个索引为item_id，此表中有100万条记录。
CREATE TABLE `model_analysis_default` (
`user_id` varchar(32) NOT NULL DEFAULT '' COMMENT '登录后的uid或随机产生的cookie,用于跟踪用户',
`item_id` int(10) NOT NULL DEFAULT '0' COMMENT '元数据id',
`rating` decimal(14,4) NOT NULL DEFAULT '0.0000' COMMENT '用户user_id对item_id的打分的值',
UNIQUE KEY `user_id` (`user_id`,`item_id`),
KEY `item_id` (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8EXPLAIN SELECT * FROM model_analysis_default ORDER BY item_id;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE model_analysis_default ALL \N \N \N \N 1000000 Using filesorttype:all
possible_keys:null
key:null
key_len:null
Extra:Using filesortEXPLAIN SELECT * FROM model_analysis_default FORCE INDEX (item_id) ORDER BY item_id;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE model_analysis_default index \N item_id 4 \N 1000000 type:index
possible_keys:null
key:item_id
key_len:4
Extra:null从以上的分析结果可以看出，用force index有时候可以提高数据库的查询性能，如果数据量很少，可能用不用force index看不出问题。
延伸阅读:
http://www.baidu.com/s?wd=mysql+%BC%BC%C7%C9
http://www.google.com/search?q=mysql+%E6%8A%80%E5%B7%A7
2010-06-15 |
2010-06-01 | MySQL的内存表
2010-06-01 |
2010-04-11 | MySQL中的子查询
2010-01-10 |
2010-01-06 |
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2014-11-10 15:15:56 |
赞(
操作
