mysql优化
1. 什么是索引?
数据结构展示网址
https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
mysql 默认的磁盘大小是16kb
2. myIsam
数据库表中的数据和索引放在哪里? 答:磁盘
放在磁盘的哪个地方?默认放在安装mysql的data目录下面
.frm :表结构
.MYD:存储表中数据
.MYI:存储索引
MyISAM存储引擎,索引对应的叶子节点对应的data,放的是30这个索引所在行的磁盘文件地址。它会把这个磁盘文件地址,拿出来去MYD磁盘文件中快速的把这一行数据找到并加载出来。
3.Innodb
innodb 叶子节点放的是:索引所在行的其他所有列的数据
innodb 非主键索引也是一种非聚集索引,存在回表的多的一次查询
非聚集索引展示如下:
4. 联合索引
5. 索引
5.1 索引的种类
5.2 常见的索引结构和数据区别
MySQL默认的索引结构是B+树
5.2.1 二叉树
磁盘的io次数越多,检索数据效率越低
5.2.2 红黑树
5.2.3 B树(平衡多路查找树)
1个节点上面可以存储多个元素
MySQL实现的是16阶的B+树,1个节点可以放15个元素,最多可以有16个分支。
5.2.4 B+树
1.所有的数据都存储在叶子节点
2.非叶子节点上没有存储数据
3.叶子节点的每一个节点上放置了双向指针:优化B树对范围查询的问题
5.2.5 面试题:为什么Mysql会选择B+树作为默认索引数据结构?
5.2.6 如果一个表没有主键索引还会创建B+树吗
row-id
5.2.7 Hash索引(了解)
5.2.8 聚簇索引和非聚簇索引
聚簇索引,索引和数据都存储在叶子节点
非聚簇索引的叶子节点除了存储索引还存储了数据的存储指针,要查询数据,需要根据这个指针去寻址对应的数据
5.2.9 二级索引
5.2.10 覆盖索引
5.2.11 索引下推
5.2.12 联合索引
5.2.13 单列索引,联合索引适用于什么样的场景?
5.2.14 索引什么时候用?
6. 优化
6.1EXPLAIN
6.1.1 id 列
1、id序号相同
2、id序号不同
3、两种都存在
4、显示为NULL
6.1.2 select_type
1、simple
2、primary
3、subquery
4、dependent subquery
5、derived
6、union
7、union all
8、dependent union
9、union result
6.1.3 type 列
1、system
2、const
3、eq_ref
6.1.4 key_len
6.1.5 ref
7. 索引优化实践
7.1 最左前缀原则
1、
2、
3、
如果想要索引生效,要记住:有序这个规则
1、
2、
如果用到了函数,需要在每个节点上面进行运算,此时需要全表扫描
3、
4、
7.1.2
使用了范围查询,后面的索引条件失效
7.1.3 尽量使用覆盖索引,减少使用select *
7.1.4 不等于,空值,or 会导致索引失效
使用FORCE INDEX (索引) 来强制添加上索引
7.1.5 like 百分号写在最右侧
尽量使用后模糊进行查询
B+树,当我们使用中文进行索引时,会按照首字母来进行有序排序,如果第一个字都无法确认,那只能全表扫描
7.1.6 字符串不加单引号,索引失效
7.1.7 范围查询优化
7.1.8 总结
8. trace 用法
9. SQL优化规范
9.1 避免使用select *
9.2 小表驱动大表
9.3 连接查询代替子查询(具体情况具体分析 )
9.4 提升group by 效率
9.5 批量插入
9.6 使用limit
9.7 使用union all 代替 union