轻商城 Mysql 慢查询 监控指标

1 Mysql常用监控指标

慢查询SQL

• 慢查询：指执行速度低于设置的阀值的SQL语句
• 作用：帮助定位查询速度较慢的SQL语句，方便更好的优化数据库系统的性能

2 开启MySQL慢查询日志

参数说明：

• slow_query_log: 慢查询日志开启状态[ON:开启，OFF:关闭]
• slow_query_log_file: 慢查询日志存放位置
• long_query_time: 慢查询时长设置（超过该时长才会被记录，单位：秒）

设置步骤：

1. 查询相关参数配置

mysql> show variables like 'slow_query%'

mysql> show variables like 'long_query_time';

2. 开启慢查询并配置

# 开启慢查询日志

mysql> set global slow_query_log='ON';

# 设置慢查询日志存放位置

mysql> set global slow_query_log_file='/data/slow_query.log';

# 设置慢查询时间标准，设置之后会在下次会话才生效

mysql> set global long_query_time=num;  （num：设置的时长）

 

3.数据库造数据

注意：

数据太少查询sql执行比较快，达不到慢查询预制

我们通过python代码批量添加数据

user_insert.py

import pymysql

# 建立连接
conn = pymysql.connect(host="192.168.136.138", user="root", password="123456", database="litemall", port=3306)
# 获取游标
cursor = conn.cursor()

user_sql = "INSERT INTO `litemall`.`litemall_user`(`id`,`username`, `password`, `gender`, `birthday`, `last_login_time`, `last_login_ip`, `user_level`, `nickname`, `mobile`, `avatar`, `weixin_openid`, `session_key`, `status`, `add_time`, `update_time`, `deleted`) VALUES (%s, %s, '$2a$10$lTu9qi0hr19OC800Db.eludFr0AXuJUSrMHi/iPYhKRlPFeqJxlye', 1, NULL, '2024-01-21 23:02:16', '192.168.136.1', 0, %s, %s, '', '', '', 0, '2019-04-20 22:17:43', '2024-01-21 23:02:16', 0);"

user_start = 100000
batch_size = 1000
total_records = 100000

for i in range(0, total_records, batch_size):
    records = []
    for j in range(i, min(i + batch_size, total_records)):
        user_id = str(user_start + j)
        user_name = "test" + user_id
        user_mobile = "17371" + user_id
        records.append((user_id, user_name, user_name, user_mobile))

    cursor.executemany(user_sql, records)
    conn.commit()
    print("插入第{}条数据到第{}条数据".format(i + 1, i + len(records)))

cursor.close()
conn.close()

goods_insert.py

# 导包
import pymysql

# 建立连接
conn = pymysql.connect(host="192.168.136.138", user="root", password="123456", database="litemall", port=3306)
# 获取游标
cursor = conn.cursor()

# 编写SQL
goods_sql = "INSERT INTO `litemall`.`litemall_goods`(`id`, `goods_sn`, `name`, `category_id`, `brand_id`, `gallery`, `keywords`, `brief`, `is_on_sale`, `sort_order`, `pic_url`, `share_url`, `is_new`, `is_hot`, `unit`, `counter_price`, `retail_price`, `detail`, `add_time`, `update_time`, `deleted`) VALUES ('{}', '{}', '色织精梳AB纱格纹空调被-{}', 1036000, 1001000, '[\"http://yanxuan.nosdn.127.net/f7e77331229098060bbacf2fc6c1708b.jpg\", \"http://yanxuan.nosdn.127.net/2720383ea168872acc8d492de9573cc6.jpg\", \"http://yanxuan.nosdn.127.net/45e4c14029626178419c82f2837f51ca.jpg\", \"http://yanxuan.nosdn.127.net/bb6c28d502704d5c1645d066f79bf61d.jpg\"]', '', '加大加厚，双色精彩', 1, 2, 'http://yanxuan.nosdn.127.net/0984c9388a2c3fd2335779da904be393.png', '', 0, 1, '件', 219.00, 199.00, '<p><img src=\"http://yanxuan.nosdn.127.net/28680639193b939b5d93cd77b3272a1e.jpg\" _src=\"http://yanxuan.nosdn.127.net/28680639193b939b5d93cd77b3272a1e.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/990c9a7781a8ae2b2a08c65c9af7afc7.jpg\" _src=\"http://yanxuan.nosdn.127.net/990c9a7781a8ae2b2a08c65c9af7afc7.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/b7ccc07b50f491e94b6187bf49b48820.jpg\" _src=\"http://yanxuan.nosdn.127.net/b7ccc07b50f491e94b6187bf49b48820.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/69eb85fd08c9ebce511d13d899d96659.jpg\" _src=\"http://yanxuan.nosdn.127.net/69eb85fd08c9ebce511d13d899d96659.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/a96f2f48217b913dc012671ed511d223.jpg\" _src=\"http://yanxuan.nosdn.127.net/a96f2f48217b913dc012671ed511d223.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/c94a22b8644fafec3ff27f87dd12771b.jpg\" _src=\"http://yanxuan.nosdn.127.net/c94a22b8644fafec3ff27f87dd12771b.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/721b4749caada515bb5e616f41a410d1.jpg\" _src=\"http://yanxuan.nosdn.127.net/721b4749caada515bb5e616f41a410d1.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/1215d6badb5a922efaeadd36911a4a3e.jpg\" _src=\"http://yanxuan.nosdn.127.net/1215d6badb5a922efaeadd36911a4a3e.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/749a3c6e9228d304806a06e478644ca1.jpg\" _src=\"http://yanxuan.nosdn.127.net/749a3c6e9228d304806a06e478644ca1.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/2cc7fd2eadbef5ab061fdf3550962d4e.jpg\" _src=\"http://yanxuan.nosdn.127.net/2cc7fd2eadbef5ab061fdf3550962d4e.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/710b9318f02863fe79209e7e6c822a5a.jpg\" _src=\"http://yanxuan.nosdn.127.net/710b9318f02863fe79209e7e6c822a5a.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/aaf8500212ec5bf783cb2bc63afbf7f8.jpg\" _src=\"http://yanxuan.nosdn.127.net/aaf8500212ec5bf783cb2bc63afbf7f8.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/f9e03dfb8cacda0f8fbbcf0ef20f8a62.jpg\" _src=\"http://yanxuan.nosdn.127.net/f9e03dfb8cacda0f8fbbcf0ef20f8a62.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/3fa109d21bda840ac7040b196c13a871.jpg\" _src=\"http://yanxuan.nosdn.127.net/3fa109d21bda840ac7040b196c13a871.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/76bfa90446c129fa3eb9f05104e9e778.jpg\" _src=\"http://yanxuan.nosdn.127.net/76bfa90446c129fa3eb9f05104e9e778.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/a3b94053478ceb6912a28597c51a3bcb.jpg\" _src=\"http://yanxuan.nosdn.127.net/a3b94053478ceb6912a28597c51a3bcb.jpg\" style=\"\"/></p><p><img src=\"http://yanxuan.nosdn.127.net/a25bca01f9ad835cd79b1c11ffdb400e.jpg\" _src=\"http://yanxuan.nosdn.127.net/a25bca01f9ad835cd79b1c11ffdb400e.jpg\" style=\"\"/></p><p><br/></p>', '2018-02-01 00:00:00', '2018-02-01 00:00:00', 0);"
goods_attr_sql = "INSERT INTO `litemall`.`litemall_goods_attribute`(`goods_id`, `attribute`, `value`, `add_time`, `update_time`, `deleted`) VALUES ('{}', '执行标准', 'GB18401-2010 B类', '2018-02-01 00:00:00', '2018-02-01 00:00:00', 0);"
goods_product_sql = "INSERT INTO `litemall`.`litemall_goods_product`(`goods_id`, `specifications`, `price`, `number`, `url`, `add_time`, `update_time`, `deleted`) VALUES ('{}', '[\"标准\"]', 899.00, 100, 'http://yanxuan.nosdn.127.net/8ab2d3287af0cefa2cc539e40600621d.png', '2018-02-01 00:00:00', '2018-02-01 00:00:00', 0);"
goods_spec_sql = "INSERT INTO `litemall`.`litemall_goods_specification` (`goods_id`, `specification`, `value`, `pic_url`, `add_time`, `update_time`, `deleted`) VALUES ('{}', '规格', '1.8m床垫*1+枕头*2', '', '2018-02-01 00:00:00', '2018-02-01 00:00:00', '0');"
#循环执行SQL语句并提交事务
goods_start = 100000
for i in range(100000):
    goods_id = goods_start + i
    print("插入第{}条数据，ID为{}".format(i+1,goods_id))
    sql = goods_sql.format(goods_id,goods_id,goods_id)
    cursor.execute(sql)

    sql = goods_attr_sql.format(goods_id)
    cursor.execute(sql)

    sql = goods_product_sql.format(goods_id)
    cursor.execute(sql)

    sql = goods_spec_sql.format(goods_id)
    cursor.execute(sql)

    conn.commit()

#关闭游标
cursor.close()
#关闭连接
conn.close()

address_insert.py

# 导包
from datetime import time

import pymysql

# 建立连接
conn = pymysql.connect(host="192.168.136.135", user="root", password="123456", database="litemall", port=3306)
# 获取游标
cursor = conn.cursor()

addr_sql = "INSERT INTO `litemall`.`litemall_address`(`id`, `name`, `user_id`, `province`, `city`, `county`, `address_detail`, `area_code`, `postal_code`, `tel`, `is_default`, `add_time`, `update_time`, `deleted`) VALUES ('{}', '{}', '{}', '北京市', '市辖区', '东城区', '6666', '110101', '', '{}', 0, '2024-01-21 22:26:49', '2024-01-21 22:26:49', 0);"


addr_start = 100000
num = 0
for i in range(100000):
    id = str(addr_start + i)
    addr_id = str(addr_start + i)
    addr_name = "test" + addr_id
    addr_mobile = "17371" + addr_id
    sql = addr_sql.format(id,addr_name,addr_id , addr_mobile)

    print("插入第{}条数据ID为：{}".format(i+1, addr_id))

    cursor.execute(sql)
    conn.commit()


cursor.close()
conn.close()

4.访问服务端

这里我们用jmeter编写好的脚本 持续访问轻商城首页

5.服务端（linux）查看日志

使用git连接linux

根据之前查询到的日志路径打开

静态查看：

 cat /var/lib/mysql/192-slow.log

动态查看：

tail -f /var/lib/mysql/192-slow.log

里面有详细的运行数据