分类： 后端

设计有效的 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 语句解释可以发现 “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 语句说明：

• 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，并且在左侧使用了 %，那便没有使用索引。

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

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

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