访问日志表
2020-09-07 18:04:49    52    0    0
junjie

背景

表结构

  1. CREATE TABLE `zb_interface_logs_35` (
  2. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  3. `url` varchar(255) NOT NULL COMMENT '接口地址',
  4. `param` text NOT NULL COMMENT '提交参数',
  5. `response` longtext NOT NULL COMMENT '返回信息',
  6. `interface_type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '接口类型:0.发送短信, 10.百度, 20.阿里, 30.纳米',
  7. `ip` varchar(45) NOT NULL DEFAULT '' COMMENT '请求ip',
  8. `created_at` timestamp NULL DEFAULT NULL,
  9. `updated_at` timestamp NULL DEFAULT NULL,
  10. PRIMARY KEY (`id`),
  11. KEY `interface_logs_35_interface_type_index` (`interface_type`)
  12. ) ENGINE=InnoDB AUTO_INCREMENT=6910328 DEFAULT CHARSET=utf8

#

数据3718534条,13.52G

分析

  1. EXPLAIN SELECT id, created_at, response FROM `zb_interface_logs_31` WHERE interface_type=32 AND id BETWEEN 3484981 AND 3560959 LIMIT 3

结果

title

实际时间

title

分析

  1. EXPLAIN SELECT id, created_at, response FROM `zb_interface_logs_31` WHERE interface_type=32 AND id > 3484981 LIMIT 3

结果

title

实际时间

title

  1. CREATE TABLE `cmf_users` (
  2. `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  3. `user_login` varchar(60) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '用户名',
  4. `user_pass` varchar(64) NOT NULL DEFAULT '' COMMENT '登录密码;sp_password加密',
  5. `user_nicename` varchar(50) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '用户美名',
  6. `user_email` varchar(100) NOT NULL DEFAULT '' COMMENT '登录邮箱',
  7. `user_url` varchar(100) NOT NULL DEFAULT '' COMMENT '用户个人网站',
  8. `avatar` varchar(255) NOT NULL DEFAULT '' COMMENT '用户头像',
  9. `avatar_thumb` varchar(255) NOT NULL DEFAULT '' COMMENT '小头像',
  10. `sex` smallint(1) DEFAULT '2' COMMENT '性别;0:保密,1:男;2:女',
  11. `birthday` varchar(50) DEFAULT '' COMMENT '生日',
  12. `signature` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '个性签名',
  13. `last_login_ip` varchar(16) NOT NULL DEFAULT '' COMMENT '最后登录ip',
  14. `last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  15. `create_time` datetime NOT NULL COMMENT '注册时间',
  16. `user_activation_key` varchar(60) NOT NULL DEFAULT '' COMMENT '激活码',
  17. `user_status` int(11) NOT NULL DEFAULT '1' COMMENT '用户状态 0:禁用; 1:正常 ;2:未验证',
  18. `score` int(11) NOT NULL DEFAULT '0' COMMENT '用户积分',
  19. `user_type` smallint(1) NOT NULL DEFAULT '1' COMMENT '用户类型,1:admin ;2:会员',
  20. `coin` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '金币',
  21. `mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '手机号',
  22. `token` varchar(50) NOT NULL DEFAULT '' COMMENT '授权token',
  23. `expiretime` int(12) NOT NULL DEFAULT '0' COMMENT 'token 到期时间',
  24. `consumption` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '消费总额',
  25. `votes` decimal(20,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '映票余额',
  26. `votestotal` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '映票总额',
  27. `province` varchar(255) NOT NULL DEFAULT '' COMMENT '省份',
  28. `city` varchar(255) NOT NULL DEFAULT '' COMMENT '城市',
  29. `isrecommend` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 未推荐 1 推荐',
  30. `openid` varchar(255) NOT NULL DEFAULT '' COMMENT '三方标识',
  31. `login_type` varchar(20) NOT NULL DEFAULT 'phone' COMMENT '注册方式',
  32. `iszombie` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否开启僵尸粉',
  33. `isrecord` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否开起回放',
  34. `iszombiep` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否僵尸粉',
  35. `issuper` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否超管',
  36. `ishot` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否热门显示',
  37. `goodnum` varchar(255) NOT NULL DEFAULT '0' COMMENT '当前装备靓号',
  38. `source` varchar(255) NOT NULL DEFAULT 'pc' COMMENT '注册来源',
  39. `location` varchar(255) NOT NULL DEFAULT '' COMMENT '所在地',
  40. `modify_num` tinyint(10) unsigned NOT NULL DEFAULT '0' COMMENT '修改用户名的次数',
  41. `cash_password` varchar(50) NOT NULL DEFAULT '' COMMENT '提现密码',
  42. `channel` varchar(255) NOT NULL DEFAULT '' COMMENT '渠道',
  43. `district` varchar(255) NOT NULL DEFAULT '' COMMENT '区',
  44. `wechat_nicename` varchar(255) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '微信昵称',
  45. PRIMARY KEY (`id`),
  46. UNIQUE KEY `user_login` (`user_login`),
  47. KEY `index_ishot_isrecommend` (`ishot`,`isrecommend`),
  48. KEY `user_nicename` (`user_nicename`),
  49. KEY `idx_token_usertype_expiretime` (`token`,`user_type`,`expiretime`)
  50. ) ENGINE=InnoDB AUTO_INCREMENT=80260 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
  1. SELECT token, expiretime FROM cmf_users WHERE token = ? AND user_type = ? LIMIT ?
  2. ALTER TABLE `yunbao`.`cmf_users` ADD INDEX `idx_token_usertype_expiretime` (`token`, `user_type`, `expiretime`);

上一篇: MySQL Explain详解

下一篇: Web 通信 之 长连接、长轮询(long polling)

Table of content