合理设置联合主键,避免无效索引

设计有效的 MySQL 联合主键。

以 MySQL 存储用户笔记为例,设计两种数据库 —— 表结构一致、联合主键顺序不一致。

联合主键表结构

t_note_no 表结构

联合主键表结构

t_note_yes 表结构

可以看出两个数据库在数据存储上是一致的,唯一区别是联合主键顺序不一致。

查询笔记

查询指定用户的正常状态的笔记:

select * from t_note_no where user_id=1 and status=1;
select * from t_note_yes where user_id=1 and status=1;

以上两条语句在表数据量不大的情况下无法看出区别。

当数据量达到十万级(实际情况跟机器性能有关)时差异开始体现出来,第一种查询会明显慢。

联合主键 explain

使用 explain 语句解释可以发现 “select * from t_note_no where user_id=1 and status=1” 并没有用到主键索引。而 “select * from t_note_yes where user_id=1 and status=1” 是用到了索引。

explain 语句说明:

id select 查询的序列号
select_type select 查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询
table 表示引用的表
type 表示查询类型
possible_keys 表示 MySQL 可能使用的索引,没有使用索引时为 NULL
key 表示 MySQL 实际使用的索引,没有使用索引时为 NULL
key_len 表示 MySQL 使用的索引的长度,越短越好
ref 表示使用索引的哪一列
Extra 额外信息
  • select_type
  • SIMPLE 简单 select,没有使用 union 或子查询
    PRIMARY 最外面的 select
    UNION union 中的第二个或后面的 select 语句
    DEPENDENT UNION union 中的第二个或后面的 select 语句,取决于外面的查询
    UNION RESULT union 的结果
    SUBQUERY 子查询中的第一个 select
    DEPENDENT SUBQUERY 子查询中的第一个 select,取决于外面的查询
    DERIVED 导出表的 select,是 from 子句的子查询
  • type
  • system 系统表,仅有一行,是 const 联接类型的一个特例
    const 表最多有一个匹配行,在该行的列值被认为是常数,只会读取一次,非常快速
    eq_ref 从每个来自前面的表的行组合中读取一行,可能是除了 const 之外最好的联接类型
    ref 从对于每个来自前面的表的行组合读取有匹配索引值的行
    ref_or_null 添加 MySQL 可以专门搜索包含 NULL 值的行,可以理解如同 ref
    index_merge 表示使用了索引合并优化方法
    unique_subquery 表示替换了指定形式【value IN (SELECT primary_key FROM single_table WHERE some_expr)】的 IN 子查询的 ref,是一个索引查找函数,可以完全替换子查询,效率更高
    index_subquery 类似于 unique_subquery。可以替换指定形式【value IN (SELECT key_column FROM single_table WHERE some_expr)】的 IN 子查询,并且是非唯一索引
    range 只检索给定范围的行,使用一个索引来选择行
    index 表示进行完整的索引扫描,因为索引文件通常比数据文件小,所以通常比 ALL 快
    ALL 表示进行完整的表扫描,此为最差的情况

    最好保证查询能达到 ref,再不济也要缩小到一定范围,达到 range。

  • Extra
  • Distinct 匹配到第1行之后停止匹配
    Not exists MySQL 对查询进行 LEFT JOIN 优化,发现1个匹配 LEFT JOIN 标准的行后,不再为前面的的行组合在该表内检查更多的行
    range checked for each record (index map: #) MySQL 没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用
    Using filesort MySQL 需要额外的一次传递,以找出如何按排序顺序检索行
    Using index 从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息
    Using temporary 为了解决查询,MySQL 创建一个临时表来容纳结果
    Using where WHERE 子句用于限制哪一个行匹配下一个表或发送到客户
    Using sort_union(…), Using union(…), Using intersect(…) 这些函数说明如何为 index_merge 联接类型合并索引扫描
    Using index for group-by 类似于访问表的 Using index 方式,Using index for group-by 表示 MySQL 发现了一个索引,可以用来查询 GROUP BY 或 DISTINCT 查询的所有列,而不要额外搜索硬盘访问实际的表

    设计联合主键尽可能根据实际应用考虑。

不合理的查询导致索引失效

SQL语句不合理也会导致索引失效,例如:

select * from t_note_yes where user_id=1 or status=2;

where 查询条件中虽然 user_id 设置了索引,但是 status 没有设置索引,导致索引失效。而且对 status tinyint 类型的字段设置索引是没有必要为其设置索引。

知晓了 explain 各个参数的含义,可以快速帮助提高数据库查询性能。

但是在很多情况下也会导致索引失效。

如果 where 子句中使用 like,并且在左侧使用了 %,那便没有使用索引。

SQL WHERE LIKE

如果 where 子句中使用 or,并且 or 的各个条件只要有一个没有使用到索引,那也会导致整个 SQL 查询无法使用索引。

SQL WHERE OR

可以看出 mysql 按照最差情况处理。

在软件开发或者检查数据库性能的过程中,如果无法确认 mysql 是否使用了索引,立即使用 explain 解释 SQL 语句,就能够看到本质。

使用 Chrome 在 Android 设备上进行网页调试

原文地址:Remote Debugging on Android with Chrome

上述地址若打不开请另存为 PDF 文件网页截图

远程调试支持:

  • 调试浏览器网页
  • 调试应用内 WebView
  • 实时同步投影并响应操作
  • 通过端口转发在设备上直接访问开发机

调试准备:

  • Chrome 32+
  • 数据线 —— 连接 Android 和 电脑
  • 调试浏览器网页 —— Android 4.0+、Chrome for Android
  • 调试应用内 WebView —— Android 4.4+、设置 WebView 支持调试

注1::电脑上的 Chrome 需要比 Android 上的 Chrome 版本高,可以使用 Chrome Canary 或者 Chrome Dev

注2:通过 webview 的 setWebContentsDebuggingEnabled 方法设置 WebView 支持调试,该方法在 Build.VERSION_CODES.KITKAT (含)以上即 Android 4.4+ 有效,此方法与 Android 应用的 manifest 文件中的 debuggable 参数无关。

调试示例:

远程调试示例

开启远程调试步骤:

  1. 激活 Android 设备 USB 调试:进入 设置 -> 开发者选项,勾选 USB 调试,紧接着可能会有弹框提示,询问是否允许使用USB调试,点击确定。
  2. 使用数据线连接 Android 设备和电脑,第一次连接会提示正在安装驱动,可以稍等一段时间。
  3. 激活 Chrome 检查设备功能,进入 菜单 -> 更多工具 -> 检查设备,如果此时在 Android 设备上打开了 Chrome 浏览器,就可以在电脑上看到设备列表。

注3:中文版本的 Android 应该都可以在设置里面看到开发者选项,英文版本的 Android 设备原生系统 4.2+ 默认隐藏了 Developer options,请进入 Settings -> About phone, 连续轻击 Build number 7 次,再返回设置界面,可以看到 Developer options

检查设备示例:

Chrome 检查设备

在 Inspect 界面有几个按钮 inspectfocus tabreloadclose,点击可以进行操作 Android 浏览器,其中主要的功能 inspect 与电脑上的 Inspect element 功能是一样的。

特别说明:如果点击 inspect 按钮打开的 Chrome DevTools 对话框一直显示空白,那多半是 appspot.com 被墙了。请使用代理在新窗口访问 appspot.com,之后会重定向到 Google 登录界面,登录 Google 账户之后可以取消代理,再次回到检查设备界面,点击 inspect 即可。

设备上访问站点的效果:

设备截图

如果开启了 WebView 调试,在 Inspect 界面可以看到类似 WebView in com.zhengxianjun.dinner7字样。

设备上 Inspect 效果图:

Inspect 效果图

注4:如果鼠标点击无法展开/合并标签,不妨换光标右键/左键试一下。

实时投影:

某些场景可能并不方便同时注视手机屏幕和电脑屏幕,此时可以使用实时投影把手机屏幕投射到电脑上。在一台设备上的操作都会实时响应到另外一台设备。点击 Screencast 按钮,开启实时投影(在低配手机上效果非常差,不要抱太大期望)。Android 4.4.3+ 支持应用内 WebView 实时投影。该功能性能消耗较高。以下为截图示例:

实时投影按钮

实时投影示例

视频演示地址:https://www.youtube.com/watch?v=Q7rEFEMpwe4。可能打不开。

以上的例子都是 Android 设备直接访问公网地址,但很有可能需要在开发机上调试,此时 出于某些原因,Android 设备可能不能迅速方便地访问到开发机。Chrome 提供一种叫做端口转发的功能 —— 在手机上访问手机本机的指定端口,效果等同于手机直接访问指定 IP 和端口。

开启端口转发步骤:

  1. 进入 chrome://inspect
  2. 点击 Port Forwarding
  3. 在弹出对话框的左侧输入 Android 设备能够访问的端口,在右侧输入需要转发到的端口
  4. 勾选 Enable port forwarding 并确定

端口转发示例图:

端口转发按钮

端口转发对话框

端口转发效果图:

端口转发效果图

在手机上访问 localhost:8080 实际上是访问到开发机的 127.0.0.1:8080 端口。如果无法访问 8080 端口,请在 Web 服务器配置文件中增加监听 8080 端口。例如在 Apache 的 httpd.conf 文件中增加 Listen 8080

端口转发设备效果

最后一步重点:

在实际应用中,并不仅仅只访问 localhost,可能还需要通过自定义域名(这种域名一般在非开发机无法访问)访问到开发机。因此需要临时设置手机 WiFi,使手机网络的所有访问均通过 WiFi 指定端口代理访问,从而达到在手机上直接访问自定义域名。

进入 Settings -> WiFi,选择已连接的网络,修改代理为手动,并且填写 127.0.0.1 作为主机名、10000 作为端口。

设置 WiFi 代理示例:

设置WiFi代理

在 Android 设备上访问效果图:

Android 设备直接访问自定义域名

至此就可以在 Android 设备上较为方便地访问开发机。

如果想要在 iPhone 上进行调试,则需要使用 Mac 版本的 Chrome 进行操作。

注5:目前在 Android 上除了可以使用 Chrome for Android 进行调试,也可以使用 UC 浏览器开发者版进行调试,操作类似。