mysql一对多根据多条数据排序_后台需要掌握的mysql优化

先从索引结构说&＃xff1a;

mysql 的索引结构是 B&＃43;Tree

- 非叶子节点不存储指针&＃xff1b;

- 叶子节点存储数据&＃xff1b;

- mysql 的 b&＃43;树 叶子节点有双向指针&＃xff08;适合范围查询&＃xff09;&＃xff1b;

不同存储引擎 索引结构有点不同&＃xff0c;

MyISAM 索引&＃xff08;主键和非主键&＃xff09;叶子节点存储记录所在页的指针&＃xff0c;这种索引为非聚簇索引&＃xff1b;

InnoDB 主键索引叶子节点存储完整的数据记录&＃xff0c;这种索引为聚簇索引&＃xff0c;非主键索引叶子节点存储主键值(节省空间&＃xff0c;以防数据修改时维护困难)&＃xff1b;

InnoDB 必须含有主键索引&＃xff08;如果没有&＃xff0c;innodb会自己新建一个&＃xff09;&＃xff0c;并且建议主键索引为自增主键(节省存储空间&＃xff0c;插入的时候排序和分页也方便)&＃xff1b;

InnoDB 支持事务&＃xff0c;使用的锁粒度为行级锁。

explian 列的信息的含义:

1. id 列&＃xff0c;id列越大执行优先级越高&＃xff0c;id相同从上往下执行&＃xff0c;id为null最后执行&＃xff1b;

2.select_type 列&＃xff0c;表示对应行是简单还是复杂的查询(复杂查询分为三类&＃xff1a;简单子查询、派生表&＃xff08;from语句中的子查询&＃xff09;、union 查询。)

3.table列&＃xff0c; 这一列表示 explain 的一行正在访问哪个表。

当 from 子句中有子查询时&＃xff0c;table列是 格式&＃xff0c;表示当前查询依赖 id&＃61;N 的查询&＃xff0c;于是先执行 id&＃61;N 的查询。

当有 union 时&＃xff0c;UNION RESULT 的 table 列的值为&＃xff0c;1和2表示参与 union 的 select 行id。

4. type列

这一列表示关联类型或访问类型&＃xff0c;即MySQL决定如何查找表中的行&＃xff0c;查找数据行记录的大概范围。

依次从最优到最差分别为&＃xff1a;system > const > eq_ref > ref > range > index > ALL

1&＃xff09;system为const1个特例&＃xff0c;即表中只有1条记录。这个是及特殊的情况

2&＃xff09;const &＃xff0c; explain select * from h_user_info where id &＃61; 112;

3&＃xff09;eq_ref , explain SELECT * from b_user_info a LEFT JOIN b_device_info b on a.udid &＃61; b.udid

4) ref, EXPLAIN SELECT * from b_order_info a LEFT JOIN b_user_info b on a.phone &＃61; b.phone

5&＃xff09;range, EXPLAIN SELECT * from b_order_info where order_id > 2

6) index

7) all, 全表扫描&＃xff0c;mysql需要从头到尾去查找所需要的行。

5.possible_keys列

这一列显示查询可能使用哪些索引来查找。

explain 时可能出现 possible_keys 有列&＃xff0c;而 key 显示 NULL 的情况&＃xff0c;这种情况是因为表中数据不多&＃xff0c;mysql认为索引对此查询帮助不大&＃xff0c;选择了全表查询。

6. key 列

这一列显示mysql实际采用哪个索引来优化对该表的访问。

如果没有使用索引&＃xff0c;则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引&＃xff0c;在查询中使用 force index、ignore index。

7. key_len 列

这一列显示了mysql在索引里使用的字节数&＃xff0c;通过这个值可以算出具体使用了索引中的哪些列。如果字段允许为 NULL&＃xff0c;需要1字节记录是否为 NULL

8.ref

在使用索引时&＃xff0c;表查找值所用到的列或常量&＃xff0c;常见的有&＃xff1a;const&＃xff08;常量&＃xff09;&＃xff0c;字段名&＃xff08;例&＃xff1a;film.id&＃xff09;

9.rows 列

这一列是mysql估计要读取并检测的行数&＃xff0c;注意这个不是结果集里的行数。

10.Extra列

1)Using index

查询的列被索引覆盖&＃xff0c;并且where筛选条件是索引的前导列&＃xff0c;是性能高的表现。一般是使用了覆盖索引(索引包含了所有查询的字段)。

2&＃xff09;Using where&＃xff1a;查询的列未被索引覆盖&＃xff0c;where筛选条件非索引的前导列

3&＃xff09;Using where Using index&＃xff1a;查询的列被索引覆盖

explain SELECT user_id from b_user_info where user_id > 200

4&＃xff09;Using temporary&＃xff1a;mysql需要创建一张临时表来处理查询。

5&＃xff09;NULL&＃xff1a;查询的列未被索引覆盖&＃xff0c;并且where筛选条件是索引的前导列&＃xff0c;意味着用到了索引&＃xff0c;但是部分字段未被索引覆盖&＃xff0c;必须通过“回表”来实现&＃xff0c;不是纯粹地用到了索引&＃xff0c;也不是完全没用到索引

6&＃xff09;Using filesort&＃xff1a;mysql 会对结果使用一个外部索引排序&＃xff0c;而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录&＃xff0c;并保存排序关键字和行指针&＃xff0c;然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。