文章目录

查看执行时间查看效率数据库中的索引实现聚簇索引 和 非聚簇索引MyISAM 和 InnoDB

查看执行时间

show variables; 查看profiling 是否为on如果是off, set profiling =1执行sql语句show profiles; 就可以查询到sql语句的执行时间 (打开profiling后所执行的所有sql语句都会记录其中)

查看效率

explain sql

id : select 查询的序列号select_type : select的查询类型, 主要却别时普通查询和联合查询, 子查询之类的复杂查询table : 输出行所引用的表type : 查询类型, 较为重要的指标 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般来说需要保证达到range, 最好能达到ref直接定位查询 是const, 无限制查询select * from table; 是ALL possible_keys : 指mysql可能通过哪个索引在表中找到目标行, 如果是空, 说明没有相关索引.key : 查询实际决定使用的键key_len : 决定使用的键的长度ref : 现实哪个字段或者常熟联合key一起被使用rows : 现实遍历多少数据才能找到目标extra 如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。如果是where used，就是使用上了where限制。如果是impossible where 表示用不着where，一般就是没查出来啥。

数据库中的索引实现

数据库中索引的实现采用的是B数或者B+树的数据结构.MySQL中索引属于存储引擎级别的概念, 不同的存储引擎对索引的实现方式不同.MyISAM引擎中使用B+ tree作为索引结构, 叶节点的data域存放数据记录的地址. 检索的算法为 首先按照B+树的搜索算法搜索索引, 如果key存在, 取出其data域的值, 然后根据改地址读取相应的数据MyISAM有主索引 和 辅助索引, 两者自结构上没有区别, 只是辅助索引的key可以重复, 主索引是唯一的.MyISAM的索引方式称为 “ 非聚簇索引 ”. InnoDB也是使用B+ tree作为索引结构, 但是具体实现方式和MyISAM不同 InnoDB的数据文件本身就是索引文件 (MyISAM索引文件和数据文件是分离的, 索引文件仅存放数据记录的地址)InnoDB中, B+ tree的叶子结点的data域保存了完整的数据记录. 每个节点的key是数据表的主键. 因此InnoDB数据表文件本身就是主索引 (数据文件按主键聚集)InnoDB的索引方式称为 “ 聚簇索引 ” InnoDB要求表必须有主键, 如果没有现实指明会自动生成尽量在InnoDB上采用自增字段作为主键, 因为InnoDB的索引结构是一个B+ tree, 非单调主键会造成插入时频繁分裂调整以维持B+ tree的特性, 十分抵效.使用自增逐渐, 每次插入新的记录, 就会顺序添加到当前节点的后续位置, 一页写满时, 就会自动开辟一个新页, 效率更高, 不会增加过多的索引维护开销. InnoDB的辅助索引 中的叶子结点data域存放的是相应记录主键的值, 而不是地址. 因此辅助索引需要检索两遍索引 : 受限检索辅助索引获得主键, 然后用主键到主索引中检索获得记录.为什么不实用过长的字段作为主键?因为所有的辅助索引都会引用主索引, 过长的主索引会使得辅助索引变得过大.

聚簇索引 和 非聚簇索引

InnoDB 使用的是聚簇索引, 将主键组织到一颗B+树中, 而行数据就存储在叶子结点上, 若使用主键查找数据, 直接使用B+ tree的检索算法即可找对对应的节点, 获得行数据若使用非主键查询, 先在辅助索引B+ tree中找到叶子节点对应的主键. 然后在主索引中通过主键执行B+ tree的检索算法, 找到叶子结点获得行数据 MyISAM 使用的是 非聚簇索引,非聚簇索引两颗B+ tree结构上没什么区别, 只是存储内容不同. 主索引B+ tree的即节点存储的是 主键, 辅助索引B+ tree的结点存储的是辅助键. 他们的叶子结点都是通过一个地址指向真正的表数据. 两个索引树独立, 通过辅助键检索无需访问主索引. 参考

MyISAM 和 InnoDB

InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败； mysql 如何查看sql语句执行时间和效率B树和B+树的插入、删除图文详解平衡二叉树、B树、B+树、B*树 理解其中一种你就都明白了MySQL索引实现原理分析Mysql 中 MyISAM 和 InnoDB 的区别有哪些？数据库索引的实现原理