Mysql开启慢查询记录耗时SQL语句

简述

  网站数据达到一定的数量级,就会有页面卡,出现50x等各种问题,mysql服务器CPU居高不下等症状。因为sql缓慢导致php缓慢,导致超时。这个时候应该先从mysql入手。就像看病一样,要先找病根,记录慢查询日志,就是观察期,观察到迹象我们就可以对症下药了。

配置参数说明

  主要靠三个参数来配置,我们用通俗的话来解释。

  1. 开关(不多说,关闭就不记录了)
    slow_query_log
  2. log存储位置(记录下来,我得知道你在哪吧,一般将它存放到mysql数据目录,也就是和你创建的数据库在同级目录)
    slow_query_log_file
  3. 超时时间 (我得让你知道多慢我忍不了)
    long_query_time

设置方法

方法一:全局变量设置

  1. 将 slow_query_log 全局变量设置为“ON”状态
    mysql> set global slow_query_log='ON'; 
  2. 设置慢查询日志存放的位置(默认在 mysql data 目录下)
    mysql> set global slow_query_log_file='slow.log';
  3. 记录超时1秒的sql语句
    mysql> set global long_query_time=1;

    注意:无需重启即可生效,但重启会失效。set global 改的参数是暂时的,想重启不变请配合修改mysql配置文件。

- 阅读全文 -

数据库水平切分方法

    在大中型项目中,在数据库设计的时候,考虑到数据库最大承受数据量,通常会把数据库或者数据表水平切分,以降低单个库,单个表的压力。


    我这里介绍两个我们项目中常用的数据表切分方法。当然这些方法都是在程序中使用一定的技巧来路由到具体的表的。首先我们要确认根据什么来水平切分?在我们的系统(SNS)中,用户的UID贯穿系统,唯一自增长,根据这个字段分表,再好不过。


方法一:使用MD5哈希

做法是对UID进行md5加密,然后取前几位(我们这里取前两位),然后就可以将不同的UID哈希到不同的用户表(user_xx)中了。

1.png

通过这个技巧,我们可以将不同的UID分散到256中用户表中,分别是user_00,user_01 …… user_ff。因为UID是数字且递增,根据md5的算法,可以将用户数据几乎很均匀的分别到不同的user表中。


但是这里有个问题是,如果我们的系统的用户越来越多,势必单张表的数据量越来越大,而且根据这种算法无法扩展表,这又会回到文章开头出现的问题了。

- 阅读全文 -

mysql 远程连接速度慢的解决方案

    之前我在vps安装mysql之后,开启mysql外连,用本机连接远程mysql特别卡,在服务器上连接不卡,当时没有解决,以为是远程卡,后来想了一下服务器是阿里云的也不该那么卡呀,连接都要5秒钟左右,网上查到了资料,出现这种问题的主要原因是,默认安装的MYSQL开启了DNS的反向解析。

    解决方案:在my.ini(WINDOWS系统下)或my.cnf(UNIX或LINUX系统下)文件的[mysqld]下加入skip- name-resolve这一句。

[mysqld]
skip-name-resolve


重启mysql服务,远程连接速度正常。

注意:到这里还没完呢,mysql.user 表里面的 host 不要用 localhost 之类的,要用127.0.0.1不然都连不上数据库,会报错,% 的不影响。代码里面的config文件记得改成127.0.0.1。

好友关系数据表设计

商城网站需要自己维护好友关系,从知乎看到的思路,记录一下。


数据表三个字段
    主键(自动生成)
    UserID1
    UserID2

注意事项
    <UserID 1, UserID 2> 和 <UserID 2, UserID 1> 是一样的记录,不要重复添加
    为了快速判断两个人是不是好友,可以在程序层插入数据前加一个限制 UserID1 < UserID2
    为了快速得到一个人的好友列表,查询时用 UNION ALL,不是 UNION
    如果为了再高效,加入缓存层(Redis 或 Memcached)

mysql语句:批量更新多条记录的不同值

mysql更新语句很简单,更新一条数据的某个字段,一般这样写:

UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value';

如果更新同一字段为同一个值,mysql也很简单,修改下where即可:

UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values');

这里注意 ‘other_values’ 是一个逗号(,)分隔的字符串,如:1,2,3

那如果更新多条数据为不同的值,可能很多人会这样写:

foreach ($display_order as $id => $ordinal) {
    $sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";
    mysql_query($sql);
}

即是循环一条一条的更新记录。一条记录update一次,这样性能很差,也很容易造成阻塞。

那么能不能一条sql语句实现批量更新呢?mysql并没有提供直接的方法来实现批量更新,但是可以用点小技巧来实现。

UPDATE mytable
    SET myfield = CASE id
        WHEN 1 THEN 'value'
        WHEN 2 THEN 'value'
        WHEN 3 THEN 'value'
    END
WHERE id IN (1,2,3)

- 阅读全文 -