假设postgresql安装位置
然后,使用dos窗口
进入这个位置
导入(本地和默认端口可以不用属性)
psql -d 数据库名 -h ip地址 -p 数据库端口 -U 用户名 -f 文件地址
完成
知识只有共享才能传播,才能推崇出新的知识,才能学到更多,这里写的每一篇文字/博客,基本都是从网上查询了一下资料然后记录下来,也有些是原滋原味搬了过来,也有时加了一些自己的想法
假设postgresql安装位置
然后,使用dos窗口
进入这个位置
导入(本地和默认端口可以不用属性)
psql -d 数据库名 -h ip地址 -p 数据库端口 -U 用户名 -f 文件地址
完成
知识只有共享才能传播,才能推崇出新的知识,才能学到更多,这里写的每一篇文字/博客,基本都是从网上查询了一下资料然后记录下来,也有些是原滋原味搬了过来,也有时加了一些自己的想法
以下内容来自mysql手册:
13.5.5.3. KILL语法
KILL [CONNECTION | QUERY] thread_id
每个与mysqld的连接都在一个独立的线程里运行,您可以使用SHOW PROCESSLIST语句查看哪些线程正在运行,并使用KILL thread_id语句终止一个线程。
KILL允许自选的CONNECTION或QUERY修改符:
KILL QUERY会终止连接当前正在执行的语句,但是会保持连接的原状。
如果您拥有PROCESS权限,则您可以查看所有线程。如果您拥有SUPER权限,您可以终止所有线程和语句。否则,您只能查看和终止您自己的线程和语句。
您也可以使用mysqladmin processlist和mysqladmin kill命令来检查和终止线程。
注释:您不能同时使用KILL和Embedded MySQL Server库,因为内植的服务器只运行主机应用程序的线程。它不能创建任何自身的连接线程。
当您进行一个KILL时,对线程设置一个特有的终止标记。在多数情况下,线程终止可能要花一些时间,这是因为终止标记只会在在特定的间隔被检查:
在SELECT, ORDER BY和GROUP BY循环中,在读取一组行后检查标记。如果设置了终止标记,则该语句被放弃。
在ALTER TABLE过程中,在每组行从原来的表中被读取前,检查终止标记。如果设置了终止标记,则语句被放弃,临时表被删除。
在UPDATE或DELETE运行期间,在每个组读取之后以及每个已更行或已删除的行之后,检查终止标记。如果终止标记被设置,则该语句被放弃。注意,如果您正在使用事务,则变更不会被 回滚。
GET_LOCK()会放弃和返回NULL。
INSERT DELAYED线程会快速地刷新(插入)它在存储器中的所有的行,然后终止。
如果线程在表锁定管理程序中(状态:锁定),则表锁定被快速地放弃。
如果在写入调用中,线程正在等待空闲的磁盘空间,则写入被放弃,并伴随”disk full”错误消息。
警告:对MyISAM表终止一个REPAIR TABLE或OPTIMIZE TABLE操作会导致出现一个被损坏的没有用的表。对这样的表的任何读取或写入都会失败,直到您再次优化或修复它(不中断)。
1、通过information_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件,然后执行临时文件中生成的指令
mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root';
+------------------------+| concat('KILL ',id,';')
+------------------------+| KILL 3101;
| KILL 2946;
+------------------------+2 rows in set (0.00 sec)
mysql>select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';
Query OK, 2 rows affected (0.00 sec)
mysql>source /tmp/a.txt;
Query OK, 0 rows affected (0.00 sec)
2、
杀掉当前所有的MySQL连接
mysqladmin -uroot -p processlist|awk -F "|" '{print $2}'|xargs -n 1 mysqladmin -uroot -p kill
杀掉指定用户运行的连接,这里为Mike
mysqladmin -uroot -p processlist|awk -F "|" '{if($3 == "Mike")print $2}'|xargs -n 1 mysqladmin -uroot -p kill
3、通过SHEL脚本实现
#杀掉锁定的MySQL连接
for id in mysqladmin processlist|grep -i locked|awk '{print $1}'
do
mysqladmin kill ${id}
done
4、通过Maatkit工具集中提供的mk-kill命令进行
#杀掉超过60秒的sql
mk-kill -busy-time 60 -kill
#如果你想先不杀,先看看有哪些sql运行超过60秒
mk-kill -busy-time 60 -print
#如果你想杀掉,同时输出杀掉了哪些进程
mk-kill -busy-time 60 -print –kill
网站数据达到一定的数量级,就会有页面卡,出现50x等各种问题,mysql服务器CPU居高不下等症状。因为sql缓慢导致php缓慢,导致超时。这个时候应该先从mysql入手。就像看病一样,要先找病根,记录慢查询日志,就是观察期,观察到迹象我们就可以对症下药了。
主要靠三个参数来配置,我们用通俗的话来解释。
1、开关(不多说,关闭就不记录了)
slow_query_log
2、log存储位置(记录下来,我得知道你在哪吧,一般将它存放到mysql数据目录,也就是和你创建的数据库在同级目录)
slow_query_log_file
3、超时时间 (我得让你知道多慢我忍不了)
long_query_time
方法一:全局变量设置
将 slow_query_log 全局变量设置为“ON”状态
mysql> set global slow_query_log='ON';
设置慢查询日志存放的位置(默认在 mysql data 目录下)
mysql> set global slow_query_log_file='slow.log';
记录超时1秒的sql语句
mysql> set global long_query_time=1;
注意:无需重启即可生效,但重启会失效。set global 改的参数是暂时的,想重启不变请配合修改mysql配置文件。
方法二:配置文件设置
修改配置文件my.cnf(Windows 下是my.ini),在[mysqld]下的下方加入
slow_query_log = ON
slow_query_log_file = slow.log
long_query_time = 1
注意:修改配置文件之后需要重启mysql服务。
mysql> show variables like 'slow_query%';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| slow_query_log | ON |
| slow_query_log_file | slow.log |
+---------------------+----------+
2 rows in set
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set
mysql> select sleep(2);
从log文件中发下慢查询
# Query_time: 2.062500 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use test;
SET timestamp=1515638101;
select sleep(2);
有时候log文件过大需要清空,直接删除log文件,本以为下次再有慢查询会自动生成文件并记录,其实并不会生成。当然重启mysql是可以生成的,但是生产环境重启mysql一定要慎重,不到万不得已不要重启mysql。在删除log文件之后,重新生成log文件需要运行 flush logs 命令。
mysql> flush logs;
Query OK, 0 rows affected
好了,mysql记录慢查询的步骤就这些了,通过记录慢的 sql 语句来开始你的优化之旅吧!
RBAC(Role-Based Access Control,基于角色的访问控制),用户基于角色的访问权限控制。简单地说,一个用户拥有若干角色,每一个角色拥有若干权限。这样,就构造成“用户-角色-权限”的授权模型。在这种模型中,用户与角色之间,角色与权限之间,一般都是多对多的关系。如图所示:
在本案例中,采用的就是这种权限设计的方式。具体的sql语句脚本如下:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
AUTO_INCREMENT=2
ROW_FORMAT=COMPACT
;
CREATE TABLE role(
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(255) CHARACTER SET latin5 NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
AUTO_INCREMENT=2
ROW_FORMAT=COMPACT
;
CREATE TABLE permission(
`id` int(11) NOT NULL AUTO_INCREMENT ,
`permission` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
AUTO_INCREMENT=3
ROW_FORMAT=COMPACT
;
CREATE TABLE user_role(
`id` int(11) NOT NULL AUTO_INCREMENT ,
`user_id` int(11) NULL DEFAULT NULL ,
`role_id` int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
AUTO_INCREMENT=2
ROW_FORMAT=COMPACT
;
CREATE TABLE role_permission(
`id` int(11) NOT NULL AUTO_INCREMENT ,
`role_id` int(11) NULL DEFAULT NULL ,
`permission_id` int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
AUTO_INCREMENT=3
ROW_FORMAT=COMPACT
;
初始化以下的sql脚本,即给用户id为1的用户关联角色,角色并关联权限:
INSERT INTO `permission` VALUES ('1', '/user/orgs');
INSERT INTO `role` VALUES ('1', 'user');
INSERT INTO `role_permission` VALUES ('1', '1', '1');
INSERT INTO `user` VALUES ('1', 'forezp');
INSERT INTO `user_role` VALUES ('1', '1', '1');
在本案例中,需要根据user表中的Id获取该Id对应的权限。首先根据userId获取该用户对应的角色,再根据根据该角色获取相应的权限,往往一个用户具有多个角色,而角色又有多个权限。比如查询userId为1 的用户的权限的sql语句如下:
SELECT a.id,a.permission from permission a ,role_permission b,role c,user_role d,user e WHERE a.id=b.permission_id and c.id=b.role_id and d.role_id=c.id and d.user_id=e.id and e.id=1"
在Openresty中怎么连接数据库,怎么查询sql语句,在之前的文章已将讲述过了。根据用户id获取用户的权限的功能是一个使用率极高的功能,所以考虑将这个功能模块化。
vim /usr/example/lualib/sql_tool.lua ,编辑加入以下的代码:
local mysql = require("resty.mysql")
local function close_db(db)
if not db then
return
end
db:close()
end
local function select_user_permission(user_id)
local db, err = mysql:new()
if not db then
ngx.say("new mysql error : ", err)
return
end
db:set_timeout(1000)
local props = {
host = "127.0.0.1",
port = 3306,
database = "test",
user = "root",
password = "123"
}
local res, err, errno, sqlstate = db:connect(props)
if not res then
ngx.say("connect to mysql error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate)
close_db(db)
end
local select_sql = "SELECT a.id,a.permission from permission a ,role_permission b,role c,user_role d,user e WHERE a.id=b.permission_id and c.id=b.role_id and d.role_id=c.id and d.user_id=e.id and e.id="..user_id
res, err, errno, sqlstate = db:query(select_sql)
if not res then
ngx.say("select error : ", err, " , errno : ", errno, " , sqlstate : ", sqlstate)
return close_db(db)
end
local permissions={}
for i, row in ipairs(res) do
for name, value in pairs(row) do
if name == "permission" then
table.insert(permissions, 1, value)
end
end
end
return permissions
end
local _M = {
select_user_permission= select_user_permission
}
return _M
在上面的代码中,有一个select_user_permission(user_id)方法,该方法根据用户名获取该用户的权限。查出来存在一个table 类型的 local permissions={}中。
vim /usr/example/example.conf 加上以下的代码:
location ~ /sql_tool{
default_type 'text/html';
content_by_lua_file /usr/example/lua/test_sql_tool.lua;
}
在浏览器上访问http://116.196.177.123/sql_tool,浏览器显示如下的内容:
/user/orgs
在之前的文章讲述了如何使用Openresty连接redis,并操作redis。 这小节将讲述如何使用openresty连接redis,并写几个方法,用于存储用户的token等,并将这些信息模块化,主要有以下几个方法:
vim /usr/example/lualib/tokentool.lua 编辑一下内容:
module("tokentool", package.seeall)
local redis = require "resty.redis"
local str = require "resty.string"
local cjson = require("cjson")
local redis_host = "127.0.0.1"
local redis_port = 6379
local function close_redis(red)
if not red then
return
end
local pool_max_idle_time = 10000 --毫秒
local pool_size = 100 --连接池大小
local ok, err = red:set_keepalive(pool_max_idle_time, pool_size)
if not ok then
ngx.say("set keepalive error : ", err)
end
end
local function connect()
local red = redis:new()
red:set_timeout(1000)
local ok, err = red:connect(redis_host, redis_port)
if not ok then
return false
end
--local res, err = red:auth("xiaoantimes")
--if not res then
-- ngx.say("failed to authenticate: ", err)
-- return false
--end
--ok, err = red:select(1)
--if not ok then
-- return false
--end
return red
end
function has_token(token)
local red = connect()
if red == false then
return false
end
local res, err = red:get(token)
if not res then
return false
end
close_redis(red)
return true
end
function set_permissions(user_id,permissions)
if (permissions==null) or( permissions==ngx.null) then
return false
end
local str = cjson.encode(permissions)
ngx.log(ngx.ERR,"set redis p:"..str)
local red=connect()
if red== false then
return false
end
local ok, err = red:set(user_id,str)
if not ok then
return false
end
return true
end
function get_permissions(user_id)
local red=connect()
if red== false then
return false
end
local res, err = red:get(user_id)
if (not res) or (res == ngx.null) then
return
end
ngx.log(ngx.ERR,"get redis p:"..res);
local permissions=cjson.decode(res)
return permissions
end
function get_user_id(token)
local red = connect()
local resp, err = red:get(token)
if not resp then
ngx.say("get msg error : ", err)
return close_redis(red)
end
close_redis(red)
return resp
end
vim /usr/example/lua/test_token_tool.lua,加上以下的内容:
local tokentool= require "tokentool"
local ret = tokentool.has_token("msg")
ngx.log(ngx.ERR,ret)
if ret == true then
ngx.say("ok")
else
ngx.say("oops,error")
end
在/usr/example/example.conf加上以下的内容:
location ~ /token_tool{
default_type 'text/html';
lua_code_cache on;
content_by_lua_file /usr/example/lua/test_token_tool.lua;
}
打开浏览器访问http://116.196.177.123/token_tool,浏览器显示:
ok
以下有两个表,他们的结构完全相同,请通过SQL找出值不同的列。
Student_1
Student_2
方法一 — NOT EXISTS:
SELECT *
FROM Student_1 S1
WHERE NOT EXISTS
(SELECT *
FROM Student_2 S2
WHERE S1.name = S2.name
AND S1.age = S2.age
AND S1.score = S2.score
)
UNION ALL
SELECT *
FROM STUDENT_2 S2
WHERE NOT EXISTS
(SELECT *
FROM STUDENT_1 S1
WHERE S1.name = S2.name
AND S1.age = S2.age
AND S1.score = S2.score
);
方法二 — MINUS
(SELECT * FROM Student_1
MINUS
SELECT * FROM Student_2)
UNION ALL
(SELECT * FROM Student_2
MINUS
SELECT * FROM Student_1)
方法三 — HAVING GROUP BY
SELECT DISTINCT name, age, score FROM (
SELECT * FROM Student_1
UNION ALL
SELECT * FROM Student_2
)GROUP BY name, age, score HAVING COUNT(*)=1 ;
客户提出需求,针对某一列分组加上小计,合计汇总。网上找了一些有关SQL加合计的语句。都不是很理想。决定自己动手写。
思路有三个:
1、很多用GROUPPING和ROLLUP来实现。
优点:实现代码简洁,要求对GROUPPING和ROLLUP很深的理解。
缺点:低版本的Sql Server不支持。
2、游标实现。
优点:思路逻辑简洁。
缺点:复杂和低效。
3、利用临时表。
优点:思路逻辑简洁,执行效率高。SQL实现简单。
缺点:数据量大时耗用内存.
综合三种情况,决定“利用临时表”实现。
实现效果
原始表TB
加上小计,合计后效果
SQL语句
select * into #TB from TB
select * into #TB1 from #TB where 1<>1
select distinct zcxt into #TBype from #TB order by zcxt
select identity(int,1,1) fid,zcxt into #TBype1 from #TBype
DECLARE @i int
DECLARE @k int
select @i=COUNT(*) from #TBype
set @k=0
DECLARE @strfname varchar(50)
WHILE @k < @i
BEGIN
Set @k =@k +1
select @strfname=zcxt from #TBype1 where fid =@k
set IDENTITY_INSERT #TB1 ON
insert into #TB1(fid,qldid,fa_cardid,ztbz,fa_name,model,i_number,gzrq,zcyz,ljzj,jz,sybm,zcxt,fa_ljjzzb)
select fid,qldid,fa_cardid,ztbz,fa_name,model,i_number,gzrq,zcyz,ljzj,jz,sybm,zcxt,fa_ljjzzb from
(
select * from #TB where zcxt=@strfname
union all
select 0 fid,'' qldid,'' fa_cardid,'' ztbz,'小计' fa_name,'' model,sum(i_number) as i_number,'' gzrq,sum(CAST(zcyz as money)) as zcyz,sum(CAST(ljzj as money)) as ljzj,sum(CAST(jz as money)) as jz,'' sybm,'' zcxt,Sum(fa_ljjzzb) as fa_ljjzzb
from #TB where zcxt=@strfname
group by ztbz
) as B
set IDENTITY_INSERT #TB1 off
END
select qldid,fa_cardid,zcxt,fa_name,model,i_number,gzrq,zcyz,ljzj,jz,sybm,ztbz,fa_ljjzzb from #TB1
union all
select '' qldid,'' fa_cardid,'' ztbz,'合计' fa_name,'' model,sum(i_number) as i_number,'' gzrq,sum(CAST(zcyz as money)) as zcyz,sum(CAST(ljzj as money)) as ljzj,sum(CAST(jz as money)) as jz,'' sybm,'' zcxt,Sum(fa_ljjzzb) as fa_ljjzzb
from #TB
drop table #TB1
drop table #TBype1
drop table #TBype
drop table #TB
扩展改进
可以改写成一个通用的添加合计小计的存储过程。
SUM是对符合条件的记录的数值列求和
COUNT 是对查询中符合条件的结果(或记录)的个数
例如:
表fruit
id name price
1 apple 3.00
2 pear 4.00
select count(price) from fruit; —-执行之后结果为:2 (表示有2条记录)
select sum(price) from fruit;—执行之后结果为:7:00(表示各记录price字段之和为7.00)
先看一下arena_match_index的表结构,大家注意表的索引结构
CREATE TABLE `arena_match_index` (
`tid` int(10) unsigned NOT NULL DEFAULT '0',
`mid` int(10) unsigned NOT NULL DEFAULT '0',
`group` int(10) unsigned NOT NULL DEFAULT '0',
`round` tinyint(3) unsigned NOT NULL DEFAULT '0',
`day` date NOT NULL DEFAULT '0000-00-00',
`begintime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
UNIQUE KEY `tm` (`tid`,`mid`),
KEY `mid` (`mid`),
KEY `begintime` (`begintime`),
KEY `dg` (`day`,`group`),
KEY `td` (`tid`,`day`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
接着看下面的sql:
SELECT round FROM arena_match_index WHERE `day` = '2010-12-31' AND `group` = 18 AND `begintime` < '2010-12-31 12:14:28' order by begintime LIMIT 1;
这条sql的查询条件显示可能使用的索引有`begintime`和`dg`,但是由于使用了order by begintime排序mysql最后选择使用`begintime`索引,explain的结果为:
mysql> explain SELECT round FROM arena_match_index WHERE `day` = '2010-12-31' AND `group` = 18 AND `begintime` < '2010-12-31 12:14:28' order by begintime LIMIT 1;
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+
| 1 | SIMPLE | arena_match_index | range | begintime,dg |<STRONG> </STRONG>begintime<STRONG> </STRONG>| 8 | NULL | 226480 | Using where |
+----+-------------+-------------------+-------+---------------+-----------+---------+------+--------+-------------+
explain的结果显示使用`begintime`索引要扫描22w条记录,这样的查询性能是非常糟糕的,实际的执行情况也是初次执行(还未有缓存数据时)时需要30秒以上的时间。
实际上这个查询使用`dg`联合索引的性能更好,因为同一天同一个小组内也就几十场比赛,因此应该优先使用`dg`索引定位到匹配的数据集合再进行排序,那么如何告诉mysql使用指定索引呢?使用use index语句:
mysql> explain SELECT round FROM arena_match_index use index (dg) WHERE `day` = '2010-12-31' AND `group` = 18 AND `begintime` < '2010-12-31 12:14:28' order by begintime LIMIT 1;
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+
| 1 | SIMPLE | arena_match_index | ref | dg | dg | 7 | const,const | 757 | Using where; Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-----------------------------+
explain结果显示使用`dg`联合索引只需要扫描757条数据,性能直接提升了上百倍,实际的执行情况也是几乎立即就返回了查询结果。
在最初的查询语句中只要把order by begintime去掉,mysql就会使用`dg`索引了,再次印证了order by会影响mysql的索引选择策略!
mysql> explain SELECT round FROM arena_match_index WHERE `day` = '2010-12-31' AND `group` = 18 AND `begintime` < '2010-12-31 12:14:28' LIMIT 1;
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-------------+
| 1 | SIMPLE | arena_match_index | ref | begintime,dg | dg | 7 | const,const | 717 | Using where |
+----+-------------+-------------------+------+---------------+------+---------+-------------+------+-------------+
通过上面的例子说mysql有时候也并不聪明,并非总能做出最优选择,还是需要我们开发者对它进行“调教”!