做网站那些好,wordpress的登录安全认证,推广找客户平台,建e网室内设计网网址文章目录说明#xff1a;索引篇一、索引常见面试题按数据结构按物理存储分类按字段特性分类按字段个数分类索引缺点#xff1a;什么时候适用索引#xff1f;什么时候不需要创建索引#xff1f;常见优化索引的方法#xff1a;发生索引失效的情况#xff1a;二、从数据页角…
文章目录说明索引篇一、索引常见面试题按数据结构按物理存储分类按字段特性分类按字段个数分类索引缺点什么时候适用索引什么时候不需要创建索引常见优化索引的方法发生索引失效的情况二、从数据页角度看B树三、为什么 MySQL 采用 B 树作为索引四、单表不要超过2000W行一般靠谱五、索引失效有哪些六、MySQL 使用 like “%x“索引一定会失效吗七、 count(*) 和 count(1) 有什么区别哪个性能最好说明
此类文章是为小林coding的图解MySQL所简写目的在于大家更快抓到小林文章的重点 本文全部由我简化但是其中有部分引用小林的文章内容 希望大家掌握精髓构建知识体系和知识框架
索引篇
一、索引常见面试题
索引底层使用了什么数据结构和算法为什么 MySQL InnoDB 选择 Btree 作为索引的数据结构什么时候适用索引什么时候不需要创建索引什么情况下索引会失效有什么优化索引的方法 索引分类 按数据结构
按「数据结构」分类Btree索引、Hash索引、Full-text索引。按「物理存储」分类聚簇索引主键索引、二级索引辅助索引。按「字段特性」分类主键索引、唯一索引、普通索引、前缀索引。按「字段个数」分类单列索引、联合索引。
InnoDB存储引擎BTree索引类型优势查询效率高查询一个数据的磁盘I/O依然维持在3-4次
1、BTree vs B Tree
BTree 的单个节点的数据量更小只在叶子节点存储数据而…在相同的磁盘 I/O 次数下就能查询更多的节点。 BTree 叶子节点采用的是双链表连接适合 MySQL 中常见的基于范围的顺序查找
2、BTree vs 二叉树
搜索复杂度为O(logdN)节点允许的最大子节点个数为 d 个也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据 所经历的磁盘I/O次数
3、BTree vs Hash
Hash 在做等值查询的时候效率贼快搜索复杂度为 O(1)但不适合做范围查询 主键索引的 BTree 的叶子节点存放的是实际数据所有完整的用户记录都存放在主键索引的 BTree 的叶子节点里 二级索引的 BTree 的叶子节点存放的是主键值而不是实际数据。 按物理存储分类
**覆盖索引**在查询时使用了二级索引如果查询的数据能在二级索引里查询的到那么就不需要回表这个过程就是覆盖索引 **回表**如果查询的数据不在二级索引里就会先检索二级索引找到对应的叶子节点获取到主键值后然后再检索主键索引就能查询到数据了这个过程就是回表
按字段特性分类
PRIMARY KEY (index_column_1) USING BTREE # 主键索引
UNIQUE KEY(index_column_1,index_column_2,...) # 唯一索引
# 建表后如果要创建唯一索引
CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);
# 普通索引
INDEX(index_column_1,index_column_2,...)
# 前缀索引
column_list,
INDEX(column_name(length))
# 建表后如果要创建前缀索引
CREATE INDEX index_name
ON table_name(column_name(length)); 按字段个数分类
建立在单列上的索引称为单列索引比如主键索引建立在多列上的索引称为联合索引
使用联合索引时存在最左匹配原则 CREATE INDEX index_product_no_name ON product(product_no, name);
索引缺点
需要占用物理空间数量越大占用空间越大创建索引和维护索引要耗费时间这种时间随着数据量的增加而增大会降低表的增删改的效率因为每次增删改索引B 树为了维护索引有序性都需要进行动态维护。
什么时候适用索引
字段有唯一性限制的经常用于 WHERE 查询条件的字段这样能够提高整个表的查询速度如果查询条件不是一个字段可以建立联合索引。经常用于 GROUP BY 和 ORDER BY 的字段这样在查询的时候就不需要再去做一次排序了因为我们都已经知道了建立索引之后在 BTree 中的记录都是排序好的
什么时候不需要创建索引
WHERE 条件GROUP BYORDER BY 里用不到的字段索引的价值是快速定位如果起不到定位的字段通常是不需要创建索引的因为索引是会占用物理空间的。字段中存在大量重复数据比如性别字段只有男女表数据太少的时候不需要创建索引经常更新的字段不用创建索引比如不要对电商项目的用户余额建立索引因为索引字段频繁修改由于要维护 BTree的有序性那么就需要频繁的重建索引这个过程是会影响数据库性能的。
常见优化索引的方法
前缀索引优化 为了减小索引字段大小 order by 就无法使用前缀索引 无法把前缀索引用作覆盖索引覆盖索引优化 避免回表的操作 假设我们只需要查询商品的名称、价格 建立一个联合索引即「商品ID、名称、价格」作为一个联合索引主键索引最好是自增的 如果我们使用自增主键每次插入一条新记录都是追加操作不需要重新移动数据防止索引失效 索引最好设置为 NOT NULL否则优化器在做索引选择的时候更加复杂更加难以优化比如进行索引统计时count 会省略值为NULL 的行 没意义的值但是它会占用物理空间
发生索引失效的情况
使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效在查询条件中对索引列做了计算、函数、类型转换操作联合索引要能正确使用需要遵循最左匹配原则否则就会导致索引失效。在 WHERE 子句中 索引列 OR 不是索引列
执行效率从低到高的顺序为
All全表扫描index全索引扫描range索引范围扫描ref非唯一索引扫描eq_ref唯一索引扫描const结果只有一条的主键或唯一索引扫描。 二、从数据页角度看B树
B树节点存放的是数据页 File Header 中有两个指针双向的链表 采用链表的结构是让数据页之间不需要是物理上的连续的而是逻辑上的连续。 User Records 是怎么组织数据的 槽
第一个分组中的记录只能有 1 条记录最后一个分组中的记录条数范围只能在 1-8 条之间剩下的分组中记录条数范围只能在 4-8 条之间。 一张表只能有一个聚簇索引
小林总结
nnoDB 的数据是按「数据页」为单位来读写的默认数据页大小为 16 KB。每个数据页之间通过双向链表的形式组织起来物理上不连续但是逻辑上连续。
数据页内包含用户记录每个记录之间用单向链表的方式组织起来为了加快在数据页内高效查询记录设计了一个页目录页目录存储各个槽分组且主键值是有序的于是可以通过二分查找法的方式进行检索从而提高效率。
为了高效查询记录所在的数据页InnoDB 采用 b 树作为索引每个节点都是一个数据页。
如果叶子节点存储的是实际数据的就是聚簇索引一个表只能有一个聚簇索引如果叶子节点存储的不是实际数据而是主键值则就是二级索引一个表中可以有多个二级索引。
在使用二级索引进行查找数据时如果查询的数据能在二级索引找到那么就是「索引覆盖」操作如果查询的数据不在二级索引里就需要先在二级索引找到主键值需要去聚簇索引中获得数据行这个过程就叫作「回表」。
三、为什么 MySQL 采用 B 树作为索引
怎样的索引的数据结构是好的 什么是二分查找 什么是二分查找树 什么是B树 什么是B树
MySQL 的数据是持久化的意味着数据索引记录是保存到磁盘上的断电数据不丢失
内存的访问速度是纳秒级别的磁盘访问的速度是毫秒级别的磁盘慢上万倍 磁盘读写最小单位是扇区52B操作系统最小读写单位是块linux块大小是4KB一次磁盘I/O读写8个扇区
二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点右子树的所有节点都大于这个节点 问题1、当每次插入的元素都是二叉查找树中最大的元素二叉查找树就会退化成了一条链表查找数据的时间复杂度变成了 O(n) 问题2、高度是I/O次数太高了影响查询性能 问题3、不能范围查询
平衡二叉查找树AVL 树 问题1、只要是二叉树高度都太高
B树 再限制一个节点就只能有 2 个子节点而是允许 M 个子节点 (M2)从而降低树的高度简单说就是多叉树 问题1、每个节点都包含了索引记录数据要莫没用上要莫就要花费更多磁盘I/O次数 问题2、用来范围查询需要用中序遍历设计多个节点的磁盘I/O问题
B 树与 B 树差异的点主要是以下这几点
叶子节点最底部的节点才会存放实际数据索引记录非叶子节点只会存放索引所有索引都会在叶子节点出现叶子节点之间构成一个有序链表非叶子节点的索引也会同时存在在子节点中并且是在子节点中所有索引的最大或最小。非叶子节点中有多少个子节点就有多少个索引
下面通过三个方面比较下 B 和 B 树的性能区别。
1、单点查询 节点存放索引可以存放更多索引可以比B树更加矮胖查询磁盘I/O次数更少
2、插入和删除效率 删除一个节点直接删除叶子节点不用动非叶子节点结构更稳定删除更快 会自平衡因为只涉及一条路径不需要复杂的算法
3、范围查询 为啥不说等值查询呢因为基本一样而范围查询就不一样了
B树叶子节点有双向链表连接 节点内容是数据页 四、单表不要超过2000W行一般靠谱
假设
非叶子节点内指向其他页的数量为 x叶子节点内能容纳的数据行数为 yB 数的层数为 z
如下图中所示Total x^(z-1) *y 也就是说总数会等于 x 的 z-1 次方 与 Y 的乘积。 X 1k存标识15k存数据一条数据按12bytex15*1024/12≈1280 行
页和索引结构差不多都会有 File Header (38 byte)、Page Header (56 Byte)、Infimum Supermum26 byte、File Trailer8byte, 再加上页目录大概 1k 左右。
索引页中主要记录的是主键与页号主键我们假设是 Bigint (8 byte), 而页号也是固定的4Byte, 那么索引页中的一条数据也就是 12byte。
所以 x15*1024/12≈1280 行。 Y 按一条行数据 1k 来算那一页就能存下 15 条Y 15*1024/1000 ≈15。 假设 B 树是两层那就是 z 2 Total 1280 ^1 *15 19200 假设 B 树是三层那就是 z 3 Total 1280 ^2 *15 24576000 约 2.45kw 我们刚刚在说 Y 的值时候假设的是 1K 那比如我实际当行的数据占用空间不是 1K , 而是 5K, 那么单个数据页最多只能放下 3 条数据。 同样还是按照 z 3 的值来计算那 Total 1280 ^2 *3 4915200 近 500w 行数据大小不同最大建议值不同 影响查询性能的还有很多其他因素比如数据库版本服务器配置sql 的编写等等
五、索引失效有哪些 1、使用左模糊|| 左右模糊
因为索引 B 树是按照「索引值」有序排列存储的只能根据前缀进行比较。
2、对索引使用了函数
因为索引保存的是索引字段的原始值而不是经过函数计算后的值自然就没办法走索引了 从 MySQL 8.0 开始索引特性增加了函数索引
3、对索引进行表达式计算
因为索引保存的是索引字段的原始值而不是 id 1 表达式计算后的值所以无法走索引
4、对索引隐式类型转换
索引字段是字符串但是输入的是整形 但是如果索引字段是整形输入字段是字符串时候会用索引因为会自动转化 MySQL 在遇到字符串和数字比较的时候会自动把字符串转为数字然后再进行比较。
5、联合索引非最左匹配
6、 WHERE 子句中的 OR
索引 OR 非索引字段那么就是失效
六、MySQL 使用 like “%x“索引一定会失效吗
使用左模糊匹配like “%xx”并不一定会走全表扫描关键还是看数据表中的字段。
数据库表中的字段只有主键二级索引 全扫描二级索引树 typeindex
如果数据库表中的字段都是索引的话即使查询过程中没有遵循最左匹配原则也是走全扫描二级索引树(typeindex)
七、 count(*) 和 count(1) 有什么区别哪个性能最好 count该函数作用是统计符合查询条件的记录中函数指定的参数不为 NULL 的记录有多少个 count(1)、 count(*)、 count(主键字段)在执行的时候如果表里存在二级索引优化器就会选择二级索引进行扫描。尽量建立二级索引
count(字段) 来统计记录个数效率最差全表扫描
通常在没有任何查询条件下的 count(*)MyISAM 的查询速度要明显快于 InnoDB MyISAM只维护一个 row_count 变量
面对大表的记录统计解决方法
1、近似值计算 使用 show table status 或者 explain 命令来表进行估算像谷歌统计的
2、额外表保存计数值 当我们在数据表插入一条记录的同时将计数表中的计数字段 1 新增和删除操作时我们需要额外维护这个计数表。
