mysql回顾

一个问题引发的学习: mysql大表skip,limit访问尾部数据性能下降问题，对比结果如下

select * from big_table order by id asc LIMIT 5000000,5;
/* Affected rows: 0  Found rows: 5  Warnings: 0  Duration for 1 query: 4.968 sec. */
select * from big_table a inner join (select id from big_table order by id ASC  limit 5000000,5) b on a.id=b.id;
/* Affected rows: 0  Found rows: 5  Warnings: 0  Duration for 1 query: 1.172 sec. */

子查询方式，当排序方式为主键时，可以避免访问数据块，快速得到id；

这里回顾一下mysql基本数据结构 https://blog.csdn.net/caijunsen/article/details/83045985?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task

提高搜索效率，二叉排序树，红黑树，B树，B+树（一般的叶节点可包含数据，也可仅为指针，叶子节点也可以包含到相邻叶子节点的指针提升范围查询的效率），总之就是首先数据维持一个便于搜索的结构，搜索的时候尽量将根节点加载如内存提升效率；

磁盘的特性是需要物理移动磁头，尽管有预加载页等功能加速，但相比内存还是很慢，红黑树的问题是深度太大，所以MYSQL选用B+树实现，让顶层节点都加载到内存里加速查找

MyIsam:B+树，非聚集方式，索引文件和数据文件分离，叶子节点存储的是指向记录物理内存的指针；主索引（唯一）+辅助索引（非唯一）

Innodb:B+树，聚集方式，主键索引Primary（索引文件和数据文件是同一个，树的叶子节点保存了真实的数据），辅助索引（索引文件叶子节点存储的是主键的值）

聚集方式按主键索引非常高效，辅助索引需要多一次查找，先辅助索引找到主键值，再去主键索引找出记录值（所以当主键太长会使辅助索引过大，可以使用类似方法left(last_name, 3)缩减，此时查询索引无法避免回表并且无法使用groupby,orderby），多个索引的使用遵循最左前缀，字段名使用函数的无法使用索引，由于优化器的原因，and的顺序在平等的情况下执行效率相同，“%前缀”的无法使用索引

索引也需要维护，占用空间，插入删除数据时需要更新所以增加开销；执行计划是基于代价计算，考虑可选择性，访问数据量大小等因素

主键设置一般采用自增id，由于聚集，数据本身被存储在主索引的一叶子节点，顺序插入效率高，减少维护索引的开销；如果使用业务主键，将会产生数据移动，导致有些页可能已经被写回磁盘，清除缓存，又需要再次加载，频繁移动可能产生更多碎片

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

https://www.cnblogs.com/LQBlog/p/10723158.html

执行计划查看，id标号越大越先执行，同标号按先后顺序执行

select_type：SIMPLE简单查询  PRIMARY内含复杂查询的外部   SUBQUERY select/where中包含子查询   DERIVED from中包含子查询，mysql会递归查询，结果放在临时表；union

system > const 比较主键> eq_ref 查询非驱动表唯一索引或者主键> ref非驱动表非唯一索引 > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range 范围查询 常见于between 和>, >=,<, <= 前提是字段有建立btree索引> index 全表扫描扫描的是索引页而不是数据行 > ALL 全表扫描

key-使用到的索引，key_len-根据表定义给出索引的长度，rows-行数预估

Using Index:表示使用索引找到了全部数据，不需要访问数据块

Using where:比较一般的where查询

Using filesort: 表示使用了非索引字段排序

Using tempary: 表示使用临时表储存数据，一般groupby非索引字段