mysql分组取每组前几条记录(排序)

  -- ---------------------------- -- Table structure for mygoods -- ---------------------------- DROP TABLE IF EXISTS `mygoods`; CREATE TABLE `mygoods` ( `goods_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `cat_id` int(11) NOT NULL DEFAULT '0', `price` tinyint(3) NOT NULL DEFAULT '0', `status` tinyint(3) DEFAULT '1', PRIMARY KEY (`goods_id`), KEY `icatid` (`cat_id`) ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of mygoods -- ---------------------------- INSERT INTO `mygoods` VALUES ('1', '101', '90', '0'); INSERT INTO `mygoods` VALUES ('2', '101', '99', '1'); INSERT INTO `mygoods` VALUES ('3', '102', '98', '0'); INSERT INTO `mygoods` VALUES ('4', '103', '96', '0'); INSERT INTO `mygoods` VALUES ('5', '102', '95', '0'); INSERT INTO `mygoods` VALUES ('6', '102', '94', '1'); INSERT INTO `mygoods` VALUES ('7', '102', '93', '1'); INSERT INTO `mygoods` VALUES ('8', '103', '99', '1'); INSERT INTO `mygoods` VALUES ('9', '103', '98', '1'); INSERT INTO `mygoods` VALUES ('10', '103', '97', '1'); INSERT INTO `mygoods` VALUES ('11', '104', '96', '1'); INSERT INTO `mygoods` VALUES ('12', '104', '95', '1'); INSERT INTO `mygoods` VALUES ('13', '104', '94', '1'); INSERT INTO `mygoods` VALUES ('15', '101', '92', '1'); INSERT INTO `mygoods` VALUES ('16', '101', '93', '1'); INSERT INTO `mygoods` VALUES ('17', '101', '94', '0'); INSERT INTO `mygoods` VALUES ('18', '102', '99', '1'); INSERT INTO `mygoods` VALUES ('19', '105', '85', '1'); INSERT INTO `mygoods` VALUES ('20', '105', '89', '0'); INSERT INTO `mygoods` VALUES ('21', '105', '99', '1');
说明: 表mygoods为商品表,cat_id为分类id,goods_id为商品id,status为商品当前的状态位(1:有效,0:无效)。 需求:每个分类下,找出两个价格最高的有效的商品。 1.每个分类找出价格最高的两个商品
mysql> select a.*   
    -> from mygoods a   
    -> where (select count(*) 
    -> from mygoods 
    -> where cat_id = a.cat_id and price > a.price  ) <2 
    -> order by a.cat_id,a.price desc;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|        2 |    101 |    99 |      1 |
|       17 |    101 |    94 |      0 |
|       18 |    102 |    99 |      1 |
|        3 |    102 |    98 |      0 |
|        8 |    103 |    99 |      1 |
|        9 |    103 |    98 |      1 |
|       11 |    104 |    96 |      1 |
|       12 |    104 |    95 |      1 |
|       21 |    105 |    99 |      1 |
|       20 |    105 |    89 |      0 |
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
2.每个分类找出价格最高的有效的两个商品(正确)
mysql> select a.* 
    -> from mygoods a 
    -> where (select count(*) from mygoods 
    -> where cat_id = a.cat_id and price > a.price and status=1  ) <2 
    -> and status=1 
    -> order by a.cat_id,a.price desc ;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|        2 |    101 |    99 |      1 |
|       16 |    101 |    93 |      1 |
|       18 |    102 |    99 |      1 |
|        6 |    102 |    94 |      1 |
|        8 |    103 |    99 |      1 |
|        9 |    103 |    98 |      1 |
|       11 |    104 |    96 |      1 |
|       12 |    104 |    95 |      1 |
|       21 |    105 |    99 |      1 |
|       19 |    105 |    85 |      1 |
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
3.每个分类找出价格最高的有效的两个商品(正确)
mysql> select a.* 
    -> from mygoods a 
    -> left join mygoods b 
    -> on a.cat_id = b.cat_id and a.price < b.price and b.status=1
    -> where a.status=1
    -> group by a.goods_id,a.cat_id,a.price
    -> having count(b.goods_id) < 2
    -> order by a.cat_id,a.price desc;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|        2 |    101 |    99 |      1 | 
|       16 |    101 |    93 |      1 | 
|       18 |    102 |    99 |      1 | 
|        6 |    102 |    94 |      1 | 
|        8 |    103 |    99 |      1 | 
|        9 |    103 |    98 |      1 | 
|       11 |    104 |    96 |      1 | 
|       12 |    104 |    95 |      1 | 
|       21 |    105 |    99 |      1 | 
|       19 |    105 |    85 |      1 | 
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
4.每个分类找出价格最高的有效的两个商品(错误)
mysql> select a.* 
    -> from mygoods a 
    -> where (select count(*) from mygoods 
    -> where cat_id = a.cat_id and price > a.price  ) <2 and status=1 
    -> order by a.cat_id,a.price desc;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|        2 |    101 |    99 |      1 |
|       18 |    102 |    99 |      1 |
|        8 |    103 |    99 |      1 |
|        9 |    103 |    98 |      1 |
|       11 |    104 |    96 |      1 |
|       12 |    104 |    95 |      1 |
|       21 |    105 |    99 |      1 |
+----------+--------+-------+--------+
7 rows in set (0.00 sec)
由上可知,如果需要增加条件的话,需要在两处增加条件。
可以将每个分组下的goods_id合并。
mysql> select cat_id,GROUP_CONCAT(goods_id) from mygoods group by cat_id;
+--------+------------------------+
| cat_id | GROUP_CONCAT(goods_id) |
+--------+------------------------+
|    101 | 1,2,15,16,17           |
|    102 | 3,5,6,7,18             |
|    103 | 4,8,9,10               |
|    104 | 11,12,13               |
|    105 | 19,20,21               |
+--------+------------------------+
5 rows in set (0.00 sec)
转自 http://blog.sina.com.cn/s/blog_4c197d420101e408.html

Win7下安装Oracle 10g常见错误的解决方案

Win7下安装Oracle 10g数据库时,常常会因为一些失误导致Oracle数据库安装失败。本文我们主要就介绍了Win7下安装Oracle 10g常见错误及其解决方案,接下来就让我们一起来了解一下这部分内容。 首先下对版本,Oracle 10g支持Win7版 错误A: 正在检查操作系统要求… 要求的结果: 5.0,5.1,5.2,6.0 之一 实际结果: 6.1 检查完成。此次检查的总体结果为: 失败 <<<< 问题: Oracle Database 11g 未在当前操作系统中经过认证。 建议案: 确保在正确的平台上安装软件。 解决方案: 1、在安装目录中搜索refhost.xml,然后在适当位置添加以下内容,注意括号配对 1.<!--Microsoft Windows 7--> 2.<OPERATING_SYSTEM> 3.<VERSION VALUE="6.1"/> 4.</OPERATING_SYSTEM> 2、在安装目录中搜索oraparam.ini,找到以下相似内容,然后修改为 1. #You can customise error message shown for failure, provide value for CERTIFIED_VERSION_FAILURE_MESSAGE 2. #Windows=5.0,5.1,5.2,6.0,6.1 然后在适当位置添加 1. #[Windows-6.1-required] 2. #Minimum display colours for OUI to run 3. MIN_DISPLAY_COLORS=256 4. #Minimum CPU speed required for OUI 5. #CPU=3006. #[Windows-6.1-optional] 错误B: 正在检查网络配置要求… 检查完成。此次检查的总体结果为: 失败 <<<< 问题: 安装检测到系统的主 IP 地址是 DHCP 分配的地址。 建议案: Oracle 支持在具有 DHCP 分配的 IP 地址的系统上进行安装。但在安装之前, 必须将 Microsoft LoopBack Adapter 配置为系统的主网络适配器。有关在配置有 DHCP 的系统上安装软件的详细信息, 请参阅Installation Guide. 解决方案: 1、按CTRL+R(或者"开始"-"运行"),输入hdwwiz.exe打开硬件添加界面。 2、选择手动添加(第二项) 3、选择"网络适配器" 4、左边选择"Microsoft",右边选择"Loopback Adapter",确定 5、打开网络连接那里的"更改适配器设置",给新添加的假网卡"Microsoft Loopback Adapter"随便配置一个IP,例如"192.168.0.5/24",行了。 关于Win7下安装Oracle 10g常见错误分析及解决方案就介绍到这里了,希望本次的介绍能够对您有所收获!

Redis系列-安装部署维护篇 (转)

Redis是个高性能的key-value数据库,它的key具有丰富的数据结构:string,hash,list set和sorted set。作为NOSQL,比起memcache之类,不仅仅key数据结构丰富,而且具有持久化的功能,并且能够支持主从复制,很方便构建集群。redis高性能很大程度上源于它是个内存型数据库,它的高性能表现在:set操作11w/s,get操作8.1w/s,与其他类型数据库性能差异,可以而参考:http://timyang.net/data/mcdb-tt-redis/   。为了进一步加深对redis的理解总结,我打算写个redis系列的博客。这里主要谈谈redis安装部署及运维维护。 1、下载安装  
  1. [root@xsf003 tool]# wget -c http://redis.googlecode.com/files/redis-2.4.17.tar.gz    #下载
  2. [root@xsf003 tool]# tar -zxvf redis-2.4.17.tar.gz   #解压
  3. [root@xsf003 tool]# cd redis-2.4.17
  4. [root@xsf003 redis-2.4.17]# make  #编译
  5. [root@xsf003 redis-2.4.17]# make install #安装
安装完毕,常用工具会自动拷贝到/user/loca/bin目录下。做为服务器,我们常常还需要把redis设置成开机自启动,源码包中有个很好用的脚本,执行脚步根据提示输入即可。    
  1. [root@xsf003 redis-2.4.17]# cd utils/
  2. [root@xsf003 utils]# ./install_server.sh
  3. Welcome to the redis service installer
  4. This script will help you easily set up a running redis server
  5. Please select the redis port for this instance: [6379]
  6. Selecting default: 6379
  7. Please select the redis config file name [/etc/redis/6379.conf]
  8. Selected default - /etc/redis/6379.conf
  9. Please select the redis log file name [/var/log/redis_6379.log]
  10. Selected default - /var/log/redis_6379.log
  11. Please select the data directory for this instance [/var/lib/redis/6379]
  12. Selected default - /var/lib/redis/6379
  13. Please select the redis executable path [/usr/local/bin/redis-server]
  14. Copied /tmp/6379.conf => /etc/init.d/redis_6379
  15. Installing service...
  16. Successfully added to chkconfig!
  17. Successfully added to runlevels 345!
  18. Starting Redis server...
  19. Installation successful!
  注意执行install_server.sh,需要先进入utils目录,不然脚本会报错,提示找不到相应文件。安装完服务,redis自动启动,可以用ps命令查看到相关信息:  
  1. [root@xsf003 utils]# ps -ef | grep redis
  2. root      4554     1  0 10:55 ?        00:00:02 /usr/local/bin/redis-server /etc/redis/6379.conf
  3. root      4564  2808  0 10:59 pts/0    00:00:00 grep redis
2、手动启动关闭服务    
  1. [root@xsf003 utils]# /etc/init.d/redis_6379 stop   #关闭
  2. [root@xsf003 utils]# /etc/init.d/redis_6379 start  #启动
也可以用下面类似的命令直接启动关闭redis服务:  
  1. /usr/local/bin/redis-server /etc/redis/redis.conf   #指定配置文件 启动
  2. /usr/local/bin/redis-cli -p 6379 shutdown   # 关闭,如果默认端口6379 可以直接 /usr/local/bin/redis-cli shutdown
  3、通过客户端命令行工具连接redis服务查看redis相关信息   a)连接  
  1. [root@xsf003 utils]# redis-cli
  2. redis 127.0.0.1:6379>
b)其他指令    
  1. redis 127.0.0.1:6379> info  #查看server版本内存使用连接等信息
  2. redis 127.0.0.1:6379> client list  #获取客户连接列表
  3. redis 127.0.0.1:6379> client kill 127.0.0.1:33441 #终止某个客户端连接
  4. redis 127.0.0.1:6379> dbsize #当前保存key的数量
  5. redis 127.0.0.1:6379> save #立即保存数据到硬盘
  6. redis 127.0.0.1:6379> bgsave #异步保存数据到硬盘
  7. redis 127.0.0.1:6379> flushdb #当前库中移除所有key
  8. redis 127.0.0.1:6379> flushall #移除所有key从所有库中
  9. redis 127.0.0.1:6379> lastsave #获取上次成功保存到硬盘的unix时间戳
  10. redis 127.0.0.1:6379> monitor #实时监测服务器接收到的请求
  11. redis 127.0.0.1:6379> slowlog len #查询慢查询日志条数
  12. (integer) 3
  13. redis 127.0.0.1:6379> slowlog get #返回所有的慢查询日志,最大值取决于slowlog-max-len配置
  14. redis 127.0.0.1:6379> slowlog get 2 #打印两条慢查询日志
  15. redis 127.0.0.1:6379> slowlog reset #清空慢查询日志信息
通过以上操作,单台服务器基本跑起来了,不过后面的路还很长很长。。。。    

Redis系列-远程连接redis并给redis加锁

本篇其实是可以和上篇合并的,但由于blog太长编辑麻烦,阅读累人,打算新开一篇, 方便阅读查找。 假设两台redis服务器,ip分别为:192.168.1.101和192.168.1.103,如何在101上通过redis-cli访问103上的redis呢?在远程连接103之前,先讲下redis-cli的几个关键参数: 用法:redis-cli [OPTIONS] [cmd [arg [arg ...]]] -h <主机ip>,默认是127.0.0.1 -p <端口>,默认是6379 -a <密码>,如果redis加锁,需要传递密码 --help,显示帮助信息 通过对rendis-cli用法介绍,在101上连接103应该很简单:  
  1. [root@xsf001 ~]# redis-cli -h 192.168.1.103 -p 6379
  2. redis 192.168.1.103:6379>
在101上对103设置个个string值 user.1.name=zhangsan    
  1. redis 192.168.1.103:6379> set user.1.name zhangsan
  2. OK
看到ok,表明设置成功了。然后直接在103上登陆,看能不能获取到这个值。    
  1. [root@xsf003 utils]# redis-cli
  2. redis 127.0.0.1:6379> get user.1.name
  3. "zhangsan"
木错吧,确实是zhangsan,这说明101上连的是103上的redis服务器。当然能够成功连接103是有基本条件的,101上可以喝103上的6379端口通信。   人人都可以连接redis服务器是很危险的,我们需要给103上的redis设置个密码,怎么设置呢,需要编辑redis的配置文件/etc/redis/6379.conf  
  1. [root@xsf003 utils]# vim /etc/redis/6379.conf
找到# requirepass foobared 去掉前面的注释#,并把foobared 替换为你自己的密码:hi, coder    
  1. requirepass "hi, coder"
保存配置文件之后,重启redis服务    
  1. [root@xsf003 utils]# /etc/init.d/redis_6379 stop
  2. Stopping ...
  3. Waiting for Redis to shutdown ...
  4. Redis stopped
  5. [root@xsf003 utils]# /etc/init.d/redis_6379 start
  6. Starting Redis server...
101上重新连接103并获取user.1.name的值    
  1. [root@xsf001 ~]# redis-cli -h 192.168.1.103 -p 6379
  2. redis 192.168.1.103:6379> get user.1.name
  3. (error) ERR operation not permitted
  4. redis 192.168.1.103:6379>
为什么是error呢,当然是因为连接103时没传递密码了,退出重新连    
  1. redis 192.168.1.103:6379> quit
  2. [root@xsf001 ~]# redis-cli -h 192.168.1.103 -p 6379 -a "hi, coder"
  3. redis 192.168.1.103:6379> get user.1.name
  4. "zhangsan"
看到zhangsan,说明你已经连接成功了。关于get、set    用法,在下个blog中讲,没有耐心的观众可以直接看这里:http://redis.io/commands#string   原文地址

MySQL日志设置及查看

MySQL有以下几种日志: 错误日志: -log-err 查询日志: -log 慢查询日志: -log-slow-queries 更新日志: -log-update 二进制日志: -log-bin 默认情况下,所有日志创建于mysqld数据目录中。通过刷新日志,你可以强制 mysqld来关闭和重新打开日志文件(或者在某些情况下切换到一个新的日志)。当你执行一个FLUSH LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时,出现日志刷新 1. 错误日志 用--log- error[=file_name]选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name值,mysqld使用错误日志名 host_name.err 并在数据目录中写入日志文件。如果你执行FLUSH LOGS,错误日志用-old重新命名后缀并且mysqld创建一个新的空日志文件。(如果未给出--log-error选项,则不会重新命名)。 如果不指定--log-error,或者(在Windows中)如果你使用--console选项,错误被写入标准错误输出stderr。通常标准输出为你的终端。 2. 通用查询日志 用--log[=file_name]或-l [file_name]选项启动它。如果没有给定file_name的值,默认名是host_name.log。 3. 慢速查询日志 用--log-slow-queries[=file_name]选项启动时,mysqld 写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件.如果没有给出file_name值,默认未主机名,后缀为 -slow.log。如果给出了文件名,但不是绝对路径名,文件则写入数据目录。 3. 更新日志 用--log-update[=file_name]选项启动,不推荐使用. 是否启用了日志 mysql>show variables like 'log_%'; 怎样知道当前的日志 mysql> show master status; 显示二进制日志数目 mysql> show master logs; 看二进制日志文件用mysqlbinlog shell>mysqlbinlog mail-bin.000001 或者shell>mysqlbinlog mail-bin.000001 | tail 在配置文件中指定log的输出位置. Windows:Windows 的配置文件为 my.ini,一般在 MySQL 的安装目录下或者 c:\Windows 下。 Linux:Linux 的配置文件为 my.cnf ,一般在 /etc 下。 在linux下: Sql代码 # 在[mysqld] 中输入 #log log-error=/usr/local/mysql/log/error.log log=/usr/local/mysql/log/mysql.log long_query_time=2 log-slow-queries= /usr/local/mysql/log/slowquery.log # 在[mysqld] 中输入 #log log-error=/usr/local/mysql/log/error.log log=/usr/local/mysql/log/mysql.log long_query_time=2 log-slow-queries= /usr/local/mysql/log/slowquery.log windows下: Sql代码 # 在[mysqld] 中输入 #log log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log" log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log" long_query_time=2 log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log" # 在[mysqld] 中输入 #log log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log" log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log" long_query_time=2 log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log" 开启慢查询 long_query_time =2 --是指执行超过多久的sql会被log下来,这里是2秒 log-slow-queries= /usr/local/mysql/log/slowquery.log --将查询返回较慢的语句进行记录 log-queries-not-using-indexes = nouseindex.log --就是字面意思,log下来没有使用索引的query log=mylog.log --对所有执行语句进行记录 windows下开启mysql日志: 在[mysql]下加入这些(基本上等于加在最后面): log-error= #Enter a name for the query log file. Otherwise a default name will be used. #注:(写成txt文件editplus可以及时重载,不过有时要放在C盘下editplus才可以及时重载) log= c:/mysql_query.log.txt #Enter a name for the slow query log file. Otherwise a default name will be used. log-slow-queries= #Enter a name for the update log file. Otherwise a default name will be used. log-update= #Enter a name for the binary log. Otherwise a default name will be used. log-bin=

MySQL检查表是否存在

1. SHOW TABLES LIKE '%tb_bp_d_case%'; 2. select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='dbname' and `TABLE_NAME`='tablename' ; 3. 如果表不存在就建立这个表,那么可以直接用 create table if not exists tablename 这样的指令来建立,不需要先去查询表是否存在。 4. 从模板表创建表:CREATE TABLE IF NOT EXISTS new_table_name  LIKE old_table_name

MySQL 创建主键,外键和复合主键的语句

由于项目需求  所以直接找了段 创建联合主键的方法
1.创建主键语法 ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(列名); 2.创建外键语法 ALTER TABLE news_info[子表名] ADD CONSTRAINT FK_news_info_news_type[约束名] FOREIGN KEY (info_id)[子表列] REFERENCES news_type[主表名] (id)[主表列] ; 3.使用组合主键 如果一列不能唯一区分一个表里的记录时,可以考虑多个列组合起来达到区分表记录的唯一性,形式 ①创建时:create table sc ( studentno int, courseid int, score int, primary key (studentno,courseid) ); ②修改时:alter table tb_name add primary key (字段1,字段2,字段3);

Mysql中count(*),DISTINCT的使用方法和效率研究

 
在处理一个大数据量数据库的时候 突然发现mysql对于count(*)的不同处理会造成不同的结果 比如执行 SELECT count(*) FROM tablename 即使对于千万级别的数据mysql也能非常迅速的返回结果 而对于 SELECT count(*) FROM tablename WHERE..... mysql的查询时间开始攀升 仔细查阅累下手册,发现当没有WHERE语句对于整个mysql的表进行count运算的时候 MyISAM类型的表中保存有总的行数,而当添加有WHERE限定语句的时候Mysql需要对整个表进行检索 从而得出count的数值 突然又想起来看到的不少新兴的php程序对于count的处理并没有很好的意识到这点 记录下 顺便提下mysql的DISTINCT的关键字有很多你想不到的用处 1.在count 不重复的记录的时候能用到 比如SELECT COUNT( DISTINCT id ) FROM tablename; 就是计算talbebname表中id不同的记录有多少条 2,在需要返回记录不同的id的具体值的时候可以用 比如SELECT DISTINCT id FROM tablename; 返回talbebname表中不同的id的具体的值 3.上面的情况2对于需要返回mysql表中2列以上的结果时会有歧义 比如SELECT DISTINCT id, type FROM tablename; 实际上返回的是 id与type同时不相同的结果,也就是DISTINCT同时作用了两个字段,必须得id与tyoe都相同的才被排除了,与我们期望的结果不一样 4.这时候可以考虑使用group_concat函数来进行排除,不过这个mysql函数是在mysql4.1以上才支持的 5.其实还有另外一种解决方式,就是使用 SELECT id, type, count(DISTINCT id) FROM tablename 虽然这样的返回结果多了一列无用的count数据(或许你就需要这个我说的无用数据) 返回的结果是 只有id不同的所有结果和上面的4类型可以互补使用,就是看你需要什么样的数据了

MySQL优化之COUNT(*)效率

MySQL优化之COUNT(*)效率

刚给一个朋友解决他写的Discuz!插件的问题,说到MySQL的COUNT(*)的效率,发现越说越说不清楚,干脆写下来,分享给大家。 COUNT(*)与COUNT(COL) 网上搜索了下,发现各种说法都有: 比如认为COUNT(COL)比COUNT(*)快的; 认为COUNT(*)比COUNT(COL)快的; 还有朋友很搞笑的说到这个其实是看人品的。 在不加WHERE限制条件的情况下,COUNT(*)与COUNT(COL)基本可以认为是等价的; 但是在有WHERE限制条件的情况下,COUNT(*)会比COUNT(COL)快非常多; 具体的数据参考如下:   mysql> SELECT COUNT(*) FROM cdb_posts where fid = 604; +————+ | COUNT(fid) | +————+ | 79000 | +————+ 1 row in set (0.03 sec) mysql> SELECT COUNT(tid) FROM cdb_posts where fid = 604; +————+ | COUNT(tid) | +————+ | 79000 | +————+ 1 row in set (0.33 sec) mysql> SELECT COUNT(pid) FROM cdb_posts where fid = 604; +————+ | COUNT(pid) | +————+ | 79000 | +————+ 1 row in set (0.33 sec) COUNT(*)通常是对主键进行索引扫描,而COUNT(COL)就不一定了,另外前者是统计表中的所有符合的纪录总数,而后者是计算表中所有符合的COL的纪录数。还有有区别的。 COUNT时的WHERE 简单说下,就是COUNT的时候,如果没有WHERE限制的话,MySQL直接返回保存有总的行数 而在有WHERE限制的情况下,总是需要对MySQL进行全表遍历。 优化总结: 1.任何情况下SELECT COUNT(*) FROM tablename是最优选择; 2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 这种查询; 3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。

如果表没有主键,那么count(1)比count(*)快。 如果有主键,那么count(主键,联合主键)比count(*)快。 如果表只有一个字段,count(*)最快。

WordPress 数据接口

数据库接口介绍 WordPress包含一个操作数据库的类——wpdb,该类基于ezSQL(由Justin Vincent维护的数据库操作项目)编写,包含了其基本的功能。 使用说明 请不要直接调用wpdb类中的方法。WordPress定义了$wpdb的全局变量,所以请直接调用该全局变量$wpdb的实例来操作数据库。(调用之前不要忘了声明引用全局变量$wpdb。参考globalize) $wpdb对象可以用来操作WordPress数据库中的每一个表,不仅仅是WordPress自动创建的基本表。例如,你有一个自定义的表叫做mytable,那么可以使用如下语句来查询: [php] $myrows = $wpdb->get_results( "SELECT id, name FROM mytable" ); [/php] $wpdb对象可以读取多个表,但是其只针对WordPress的数据库。如果你需要连接其他数据库,那么你应该使用你自己的数据库连接信息,并调用wpdb类来创建一个你自己的数据库操作实例。如果你有多个数据库需要连接,那么你可以考虑使用hyperdb来替代$wpdb。 在数据库上运行任务查询 这个查询函数允许你在wordpress的数据库里运行任何SQL查询。当然了,最好能利用如下的特定函数, [php] <!--?php $wpdb--->query('query'); ?> [/php] query (string) The SQL query you wish to execute. The function returns an integer corresponding to the number of rows affected/selected. If there is a MySQL error, the function will return FALSE. (Note: since both 0 and FALSE can be returned, make sure you use the correct comparison operator: equality == vs. identicality ===). Note: As with all functions in this class that execute SQL queries, you must SQL escape all inputs (e.g., wpdb->escape($user_entered_data_string)). See the section entitled Protect Queries Against SQL Injection Attacks below. Examples Delete the 'gargle' meta key and value from Post 13. [php] $wpdb->query(" DELETE FROM $wpdb->postmeta WHERE post_id = '13' AND meta_key = 'gargle'"); [/php] Performed in WordPress by delete_post_meta(). Set the parent of Page 15 to Page 7. [php]$wpdb->query(" UPDATE $wpdb->posts SET post_parent = 7 WHERE ID = 15 AND post_status = 'static'");[/php] SELECT a Variable The get_var function returns a single variable from the database. Though only one variable is returned, the entire result of the query is cached for later use. Returns NULL if no result is found. query (string) The query you wish to run. Setting this parameter to null will return the specified variable from the cached results of the previous query. column_offset (integer) The desired column (0 being the first). Defaults to 0. row_offset (integer) The desired row (0 being the first). Defaults to 0. Examples Retrieve and display the number of users. [php] <!--?php $user_count = $wpdb--->get_var($wpdb->prepare("SELECT COUNT(*) FROM $wpdb->users;")); echo ' User count is ' . $user_count . ' '; ?> [php] Retrieve and display the sum of a Custom Field value. [php] <!--?php $meta_key = 'miles';//set this to appropriate custom field meta key $allmiles=$wpdb--->get_var($wpdb->prepare("SELECT sum(meta_value) FROM $wpdb->postmeta WHERE meta_key = %s", $meta_key)); echo ' Total miles is '.$allmiles . ' '; ?> [php] SELECT a Row To retrieve an entire row from a query, use get_row. The function can return the row as an object, an associative array, or as a numerically indexed array. If more than one row is returned by the query, only the specified row is returned by the function, but all rows are cached for later use. Returns NULL if no result is found. query (string) The query you wish to run. output_type One of three pre-defined constants. Defaults to OBJECT. OBJECT - result will be output as an object. ARRAY_A - result will be output as an associative array. ARRAY_N - result will be output as a numerically indexed array. row_offset (integer) The desired row (0 being the first). Defaults to 0. Examples Get all the information about Link 10. [php] $mylink = $wpdb->get_row("SELECT * FROM $wpdb->links WHERE link_id = 10"); [/php] The properties of the $mylink object are the column names of the result from the SQL query (in this all of the columns from the $wpdb->links table). [php] echo $mylink->link_id; // prints "10" [/php] In contrast, using [php] $mylink = $wpdb->get_row("SELECT * FROM $wpdb->links WHERE link_id = 10", ARRAY_A); would result in an associative array: echo $mylink['link_id']; // prints "10" and $mylink = $wpdb->get_row("SELECT * FROM $wpdb->links WHERE link_id = 10", ARRAY_N); would result in a numerically indexed array: echo $mylink[1]; // prints "10" [/php] SELECT a Column To SELECT a column, use get_col. This function outputs a dimensional array. If more than one column is returned by the query, only the specified column will be returned by the function, but the entire result is cached for later use. Returns an empty array if no result is found. query (string) the query you wish to execute. Setting this parameter to null will return the specified column from the cached results of the previous query. column_offset (integer) The desired column (0 being the first). Defaults to 0. Examples For this example, assume the blog is devoted to information about automobiles. Each post describes a particular car (e.g. 1969 Ford Mustang), and three Custom Fields, manufacturer, model, and year, are assigned to each post. This example will display the post titles, filtered by a particular manufacturer (Ford), and sorted by model and year. The get_col form of the wpdb Class is used to return an array of all the post ids meeting the criteria and sorted in the correct order. Then a foreach construct is used to iterate through that array of post ids, displaying the title of each post. Note that the SQL for this example was created by Andomar. [php] <!--?php $meta_key1 = 'model'; $meta_key2 = 'year'; $meta_key3 = 'manufacturer'; $meta_key3_value = 'Ford'; $postids=$wpdb--->get_col($wpdb->prepare(" SELECT key3.post_id FROM $wpdb->postmeta key3 INNER JOIN $wpdb->postmeta key1 on key1.post_id = key3.post_id and key1.meta_key = %s INNER JOIN $wpdb->postmeta key2 on key2.post_id = key3.post_id and key2.meta_key = %s WHERE key3.meta_key = %s and key3.meta_value = %s ORDER BY key1.meta_value, key2.meta_value",$meta_key1, $meta_key2, $meta_key3, $meta_key3_value)); if ($postids) { echo 'List of ' . $meta_key3_value . '(s), sorted by ' . $meta_key1 . ', ' . $meta_key2; foreach ($postids as $id) { $post=get_post(intval($id)); setup_postdata($post);?> [/php] This example lists all posts that contain a particular custom field, but sorted by the value of a second custom field. [php] <!--?php //List all posts with custom field Color, sorted by the value of custom field Display_Order //does not exclude any 'post_type' //assumes each post has just one custom field for Color, and one for Display_Order $meta_key1 = 'Color'; $meta_key2 = 'Display_Order'; $postids=$wpdb--->get_col($wpdb->prepare(" SELECT key1.post_id FROM $wpdb->postmeta key1 INNER JOIN $wpdb->postmeta key2 on key2.post_id = key1.post_id and key2.meta_key = %s WHERE key1.meta_key = %s ORDER BY key2.meta_value+(0) ASC", $meta_key2,$meta_key1)); if ($postids) { echo 'List of '. $meta_key1 . ' posts, sorted by ' . $meta_key2 ; foreach ($postids as $id) { $post=get_post(intval($id)); setup_postdata($post);?> [/php] SELECT Generic Results Generic, mulitple row results can be pulled from the database with get_results. The function returns the entire query result as an array. Each element of this array corresponds to one row of the query result and, like get_row, can be an object, an associative array, or a numbered array. query (string) The query you wish to run. Setting this parameter to null will return the data from the cached results of the previous query. output_type One of four pre-defined constants. Defaults to OBJECT. See SELECT a Row and its examples for more information. OBJECT - result will be output as a numerically indexed array of row objects. OBJECT_K - result will be output as an associative array of row objects, using first column's values as keys (duplicates will be discarded). ARRAY_A - result will be output as an numerically indexed array of associative arrays, using column names as keys. ARRAY_N - result will be output as a numerically indexed array of numerically indexed arrays. Since this function uses the '$wpdb->query()' function all the class variables are properly set. The results count for a 'SELECT' query will be stored in $wpdb->num_rows. Examples Get the IDs and Titles of all the Drafts by User 5 and echo the Titles. [php] $fivesdrafts = $wpdb->get_results("SELECT ID, post_title FROM $wpdb->posts WHERE post_status = 'draft' AND post_author = 5"); foreach ($fivesdrafts as $fivesdraft) { echo $fivesdraft->post_title; } [/php] Get all information on the Drafts by User 5. [php] <!--?php $fivesdrafts = $wpdb--->get_results("SELECT * FROM $wpdb->posts WHERE post_status = 'draft' AND post_author = 5"); if ($fivesdrafts) : foreach ($fivesdrafts as $post) : setup_postdata($post); ?> [/php]

title="Permanent Link to ">

Not Found

INSERT rows Insert a row into a table. table (string) The name of the table to insert data into. data (array) Data to insert (in column => value pairs). Both $data columns and $data values should be "raw" (neither should be SQL escaped). format (array|string) (optional) An array of formats to be mapped to each of the value in $data. If string, that format will be used for all of the values in $data. If omitted, all values in $data will be treated as strings unless otherwise specified in wpdb::$field_types. Possible format values: %s as string; %d as decimal number; and %f as float. After insert, the ID generated for the AUTO_INCREMENT column can be accessed with: $wpdb->insert_id This function returns false if the row could not be inserted. Examples Insert two columns in a row, the first value being a string and the second a number: [php]$wpdb->insert( 'table', array( 'column1' => 'value1', 'column2' => 123 ), array( '%s', '%d' ) )[/php] UPDATE rows 更新记录 更新数据库的记录。 table (string) 要更新的表名称。 data (array) 需要更新的数据(使用格式:column => value)。Both $data columns and $data values should be "raw" (neither should be SQL escaped). where (array) A named array of WHERE clauses (in column => value pairs). Multiple clauses will be joined with ANDs. Both $where columns and $where values should be "raw". format (array|string) (optional) An array of formats to be mapped to each of the values in $data. If string, that format will be used for all of the values in $data. where_format (array|string) (optional) An array of formats to be mapped to each of the values in $where. If string, that format will be used for all of the items in $where. Possible format values: %s as string; %d as decimal number and %f as float. If omitted, all values in $where will be treated as strings. 范例 Update a row, where the ID is 1, the value in the first column is a string and the value in the second column is a number: $wpdb->update( 'table', array( 'column1' => 'value1', 'column2' => 'value2' ), array( 'ID' => 1 ), array( '%s', '%d' ), array( '%d' ) ) Protect Queries Against SQL Injection Attacks For a more complete overview of SQL escaping in WordPress, see database Data Validation. That Data Validation article is a must-read for all WordPress code contributors and plugin authors. Briefly, though, all data in SQL queries must be SQL-escaped before the SQL query is executed to prevent against SQL injection attacks. This can be conveniently done with the prepare method, which supports both a sprintf()-like and vsprintf()-like syntax. query (string) The SQL query you wish to execute, with %s and %d placeholders. Any other % characters may cause parsing errors unless they are escaped. All % characters inside SQL string literals, including LIKE wildcards, must be double-% escaped as %%. value_parameter (int|string|array) The value to substitute into the placeholder. Many values may be passed by simply passing more arguments in a sprintf()-like fashion. Alternatively the second argument can be an array containing the values as in PHP's vsprintf() function. Care must be taken not to allow direct user input to this parameter, which would enable array manipulation of any query with multiple placeholders. Values must not already be SQL-escaped. Examples Add Meta key => value pair "Harriet's Adages" => "WordPress' database interface is like Sunday Morning: Easy." to Post 10. $metakey = "Harriet's Adages"; $metavalue = "WordPress' database interface is like Sunday Morning: Easy."; [php]$wpdb->query( $wpdb->prepare( " INSERT INTO $wpdb->postmeta ( post_id, meta_key, meta_value ) VALUES ( %d, %s, %s )", 10, $metakey, $metavalue ) );[/php] Performed in WordPress by add_meta(). The same query using vsprintf()-like syntax. $metakey = "Harriet's Adages"; $metavalue = "WordPress' database interface is like Sunday Morning: Easy."; [php]$wpdb->query( $wpdb->prepare( " INSERT INTO $wpdb->postmeta ( post_id, meta_key, meta_value ) VALUES ( %d, %s, %s )", array(10, $metakey, $metavalue) ) );[/php] Note that in this example we pack the values together in an array. This can be useful when we don't know the number of arguments we need to pass until runtime. Notice that you do not have to worry about quoting strings. Instead of passing the variables directly into the SQL query, use a %s placeholder for strings and a %d placedolder for integers. You can pass as many values as you like, each as a new parameter in the prepare() method. Show and Hide SQL Errors You can turn error echoing on and off with the show_errors and hide_errors, respectively. [php] <!--?php $wpdb--->show_errors(); ?> <!--?php $wpdb--->hide_errors(); ?>[/php] You can also print the error (if any) generated by the most recent query with print_error. print_error(); ?> Getting Column Information You can retrieve information about the columns of the most recent query result with get_col_info. This can be useful when a function has returned an OBJECT whose properties you don't know. The function will output the desired information from the specified column, or an array with information on all columns from the query result if no column is specified. type (string) What information you wish to retrieve. May take on any of the following values (list taken from the ezSQL docs). Defaults to name. name - column name. Default. table - name of the table the column belongs to max_length - maximum length of the column not_null - 1 if the column cannot be NULL primary_key - 1 if the column is a primary key unique_key - 1 if the column is a unique key multiple_key - 1 if the column is a non-unique key numeric - 1 if the column is numeric blob - 1 if the column is a BLOB type - the type of the column unsigned - 1 if the column is unsigned zerofill - 1 if the column is zero-filled offset (integer) Specify the column from which to retrieve information (with 0 being the first column). Defaults to -1. -1 - Retrieve information from all columns. Output as array. Default. Non-negative integer - Retrieve information from specified column (0 being the first). Clearing the Cache You can clear the SQL result cache with flush. This clears $wpdb->last_result, $wpdb->last_query, and $wpdb->col_info. Class Variables $show_errors Whether or not Error echoing is turned on. Defaults to TRUE. $num_queries The number of queries that have been executed. $last_query The most recent query to have been executed. $queries You may save all of the queries run on the database and their stop times by setting the SAVEQUERIES constant to TRUE (this constant defaults to FALSE). If SAVEQUERIES is TRUE, your queries will be stored in this variable as an array. $last_result The most recent query results. $col_info The column information for the most recent query results. See Getting Column Information. $insert_id ID generated for an AUTO_INCREMENT column by the most recent INSERT query. $num_rows The number of rows returned by the last query. $prefix The assigned WordPress table prefix for the site. Multi-Site Variables If you are using Multi-Site, you also have access to the following: $blogid The id of the current site (blog). Tables The WordPress database tables are easily referenced in the wpdb class. $posts The table of Posts. $postmeta The Meta Content (a.k.a. Custom Fields) table. $comments The Comments table. $commentmeta The table contains additional comment information. $terms The terms table contains the 'description' of Categories, Link Categories, Tags. $term_taxonomy The term_taxonomy table describes the various taxonomies (classes of terms). Categories, Link Categories, and Tags are taxonomies. $term_relationships The term relationships table contains link between the term and the object that uses that term, meaning this file point to each Category used for each Post. $users The table of Users. $usermeta The usermeta table contains additional user information, such as nicknames, descriptions and permissions. $links The table of Links. $options The Options table. 来自手册 :wordpress中文手册

MySQL数据库查询优化

上两周一直想办法提高查询速度,取得一点效果,解决了部分问题,记下来以便将来自己查看。 由于公司没有专门的DBA,我自己对mysql数据库也不是很熟悉,而且这个JAVA开发的网络审计系统的管理系统,是经过了N多人几年时间的修修改改,今天到我们手里,要改成能支持大流量情况的版本,所以对我们这个只有几个人的JAVA组来说,确实是个难题。 这个大流量的情况在以前的文章里也提到过,就是要支持每秒钟处理1G左右的网络数据包,HTTP协议的数据包最多,因此HTTP协议分析模块的流水日志表记录最大,据估算可能到达一天4000万条记录,采用一天一张表,那也是很大的,我看了.MYD文件大小,已经是8G多了。 而我们管理系统查询日志记录时,对好几个字段都要进行条件查询,而且有几个字段长度达到256,在8G这么大的表里查询一个字符串,如果找不到,那必定从头要查到尾,速度慢得根本受不了。客户还要好几个字段一起设置条件来查询,这样基本上是二三十分钟都出不来,系统可用性极差。 我采用的方法是以测试为主,同时看JAVA代码,通过Log4j和Perf4j日志,看每个sql语句使用的时间,寻找性能瓶颈,然后有的放矢地进行优化。 对查询最有效果的优化,自然是建立索引了,ID自然是自增、主键,这个前人已经做了;从where语句分析,时间字段作为查询条件很多,时间是8字节,而且不重复,设置索引比较适合。我把时间设置为索引,有一点效果,但不大,估算一下:8 * 4000 0000 = 320 000 000 字节,4000万记录的表仅仅时间一个字段的索引将是320M,这还仅仅是我们上百张表的一张表而已(客户要求我们至少保存3个月记录)。 建立索引能起到一定作用,但还是解决不了我们的问题。物理表建立不能再缩短时间了,因为一天一张表,3个月就91~92张表,30个协议模块就得2700多,这仅仅是协议流水日志表,还有其它表呢。 也不能把客户要求做成条件的字段都设置成索引,那索引表将和原表差不多大,索引就失去意义了。在数据库本身上优化,想去想来实在一下子想不到好办法,感觉数据量大了,就算在Oracle上也没有什么神奇办法吧。 我最后采用分段查询的方法,就是4000万条数据,我不管你设置什么条件来查询,我都是平均划为成N段来查询,比如400万为一段,在页面上提供一个下拉单:0~400万,400~800万,…,3600~4000万,虽然查询比较麻烦一点,但每段查询的速度大大提高,控制在30秒左右,牺牲一些可用性,总比30分钟还查不出来好吧。 流水日志可以采用分段查询解决,但客户要求的各种统计呢,这不能说分段统计,别人要统计2天的,你分开是不行的。 以前已经采用了一次预统计,预先定时在后台对流水日志表进行统计一次,保存到预统计表,等用户来查询时,从预统计表进行各种查询—-这个做法好,不得不夸下前任开发人员。 但现在形势不同了,因为预统计表是采用一个月一张的,就现在流水日志表的规模,那预统计表可能一张表超过4000万,具体看客户网络数据的分布情况,不好估计。 最后我和同事们对统计模式详细分析,一个同事提出再在预统计表基础上进行二次预统计,我们估算了一下,基本上等用户来查询时,所面对的表已经很小了,最多几千条记录,很快了。 解决统计查询过程中,让我体会到详细分析业务流程细节,作出相应的优化,有时是可以解决问题的。 总体上来说,对数据库查询的优化,我们采取了一些常规的优化之后,如果还没有取得想要的效果,我们有时候不必硬碰硬去优化查询本身,改变一下使用模式,找找业务处理流程是否还有可修改的,说不定就轻松解决了存在的难题。 还有就是主管要把整个开发组积极性调动起来,大家一起测试、分析、想办法、验证,最后一致确定一个可行的方案,然后大家分头去不打折扣的实现。 转自php100