mysql优化

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