HTTPS配置

基于Nginx HTTPS配置

我把lvxinwei.com域名下的所有站点弄成了HTTPS访问,配置挺简单的,简要的说下:

1.生成 csr 和private key

[code]
openssl req -new -newkey rsa:2048 -nodes -out lvxinwei.com.csr -keyout lvxinwei.com.key
[/code]

然后 在startssl 中填入csr生成 crt 最后在nginx配置:

[code]
listen 443 ssl;
server_name blog.lvxinwei.com;
ssl_certificate /root/cert/lvxinwei.com.crt
ssl_certificate_key /root/cert/lvxinwei.com.key
ssl_session_cache shared:SSL:1m;
ssl_session_timeout 5m;
[/code]

每次启动会让输入密码 所以对private key 解密

[code]
openssl rsa -in lvxinwei.com -out lvxinwei.com.unsecure
[/code]

生成解密文件重新配置Nginx即可。

MySQL慢查询分析

最近遇见一个 MySQL 的慢查问题,于是排查了下,这里把相关的过程做个总结。
====== 定位原因 ======
我首先查看了 MySQL 的慢查询日志,发现有这样一条 query 耗时非常长(大概在 1 秒多),而且扫描的行数很大(10 多万条数据,差不多是全表了):

    SELECT * FROM tgdemand_demand t1
    WHERE
      (
        t1.id IN
        (
          SELECT t2.demand_id
          FROM tgdemand_job t2
          WHERE (t2.state = 'working' AND t2.wangwang = 'abc')
        )
        AND
        NOT (t1.state = 'needConfirm')
      )
    ORDER BY t1.create_date DESC

这个查询不是很复杂,首先执行一个子查询,取到任务的状态(state)是 ‘working’ 并且任务的关联人(wangwang)是’abc’的所有需求 id(这个设计师进行中的任务对应的需求 id),然后再到主表 tgdemand_demand 中带入刚才的 id 集合,查询出需求状态(state)不是 ‘needConfirm’ 的所有需求,最后进行一个排序。
按道理子查询筛选出 id 后到主表过滤是直接使用到主键,应该是很快的啊。而且,我检查了子查询的 tgdemand_job 表的索引,where中用到的查询条件都已经增加了索引。怎么会这样呢?于是,我对这个 query 执行了一个 explain(输出 sql 语句的执行计划),看看 MySQL 的执行计划是怎样的。输出如下:
我们看到,第一行是 t1 表,type 是 ALL(全表扫描),rows(影响行数)是 157089,没有用到任何索引;第二行是 t2 表,用到了索引。和我之前理解的执行顺序完全不一样!
为什么 MySQL 不是先执行子查询,而是对 t1 表进行了全表扫描呢?我们仔细看第二行的 select_type,发现它的值是 DEPENDENT_SUBQUERY,意思是这个子查询的查询方式依赖外层的查询。这是什么意思?
实际上,MySQL 对于这种子查询会进行改写,上面的 SQL 会被改写成下面的形式:

SELECT * FROM tgdemand_demand t1 WHERE EXISTS (
  SELECT * FROM tgdemand_job t2 WHERE t1.id = t2.demand_id AND (t2.state = 'working' AND t2.wangwang = 'abc')
) AND NOT (t1.state = 'needConfirm')
ORDER BY t1.create_date DESC;

这表示,SQL 会去扫描 tgdemand_demand 表的所有数据,每条数据再传入到子查询中与表 tgdemand_job 进行关联,执行子查询,子查询根本不会先执行,而且子查询会执行 157089 次(外层表的记录数量)。还好我们的子查询加了必要的索引,不然结果会更加惨不忍睹。


既然子查询会被改写,那最简单的解决方案就是不用子查询,将内层获取需求 id 的 SQL 单独拿出来执行,取到结果后再执行一条 SQL 去获取实际的数据。大概像这样(下面的语句是不合法的,只是示意):

ids = SELECT t2.demand_id
FROM tgdemand_job t2
WHERE (t2.state = 'working' AND t2.wangwang = 'abc');
SELECT * FROM tgdemand_demand t1
WHERE
  (
    t1.id IN ids
    AND
    NOT (t1.state = 'needConfirm')
  )
ORDER BY t1.create_date DESC;

实际上,我们也可以对 SQL 进行改写来解决问题:

select * from tgdemand_demand t1, (select t.demand_id from tgdemand_job t where t.state = 'working' and t.wangwang = 'abc') t2
where t1.id=t2.demand_id and not (t1.state = 'needConfirm')
order by t1.create_date DESC

也可以用Join


SELECT t1.* FROM tgdemand_demand t1 INNER JOIN tgdemand_job t2 ON t1.id = t2.demand_id AND t2.state = 'working' AND t2.wangwang = 'abc' WHERE NOT (t1.state = 'needConfirm') ORDER BY t1.create_date DESC