mysql 索引 using temporary场景

底层结构

B+Tree和B-Tree

• B-Tree 数据与记录放在一起，中间节点存储数据，B+Tree中间节点只存储索引不存储数据，具体数据都在底层

• B-Tree 读取速度不稳定，越接近顶层速度越快，B+Tree读取时间稳定，数据都放在底层。

  由于上述特性B+Tree有如下优势：

  • 由于中间节点不存放数据，所以同样层数，B+Tree存储的索引更多容纳的数据量更多
  • 叶子节点连在一起方便范围查询

聚簇索引：数据存储方式，为数据顺序存储在磁盘innodb支持，由于聚簇索引只能选取一个主要因素来排序因此通常以主键为聚簇索引排序条件，因此主键选用有序的顺序ID而不是无序的UUID

mysql using temporary

Temporary tables can be created under conditions such as these:

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

DISTINCT combined with ORDER BY may require a temporary table.

If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.

1. order by 和group by 后条件不同
2. order by /group by 在关联查询中使用除了第一张表以外的列
3. SQL_SMALL_RESULT 选项使用临时表保存缓存

索引有效判断

count(distinct col)/count(*)