内蒙古城乡建设厅网站资质公告,龙口网站建设公司报价,京东网页版,app开发公司查询文章目录0.什么是索引1.常用索引分类逻辑维度底层数据结构维度物理维度#xff08;InnoDB#xff09;2.为什么底层是B树平衡二叉查找树红黑树B树#xff08;多叉#xff09;B树#xff08;多叉#xff09;3.MySQL索引优化SQL性能分析之explainQ.MySQL如何查看查询是否用到…
文章目录0.什么是索引1.常用索引分类逻辑维度底层数据结构维度物理维度InnoDB2.为什么底层是B树平衡二叉查找树红黑树B树多叉B树多叉3.MySQL索引优化SQL性能分析之explainQ.MySQL如何查看查询是否用到了索引优化一为表添加自增主键优化二添加唯一索引优化三添加联合索引Q.索引失效场景联合索引非连续优化四针对order by的优化Q.索引失效场景order by同时使用ASC 和 DESCQ.性别字段适合添加索引吗参考本博客实战部分仓库点击跳转github
0.什么是索引
索引是帮助MySQL高效获取数据的数据结构。简单来讲数据库索引就像是书前面的目录能加快数据库的查询速度。 对于海量数据来说它的目录也是很大的不可能全部存储在内存中因此索引往往是存储在磁盘上的文件中
1.常用索引分类
逻辑维度
- 唯一索引、主键索引值唯一前者可以为null后者不能为null
- 联合(组合)索引为多个字段创建的索引遵循左前缀原则即从最左边的字段开始匹配
- 普通索引没什么限制可重复可为空都行底层数据结构维度
hash索引适用于 ! IN几种情况精确查找。仅Memory搜索引擎支持。
B树索引适用于范围查找。InnoDB和MyISAM支持。物理维度InnoDB
InnoDB提供的一种分类方式InnoDB的每张表都会有一个聚集索引有且仅有一个这也是该表的物理存储方式非聚集索引可以有多个。
- 聚簇索引主键索引
根据主键构建的索引叫做聚簇索引。将数据存储与索引放到了一块找到索引也就找到了数据。
优点检索速度很快排序查找、范围查找都很不赖没有回表查询现象。
缺点主键最好是自增的因为连续的主键索引性能更好主键最好设置为不可变改变主键会导致聚集索引的维护代价很高。- 非聚簇索引辅助索引
不是根据主键构建的索引叫做非聚集索引或者二级索引或者辅助索引。
将数据与索引分开存储索引结构的叶子节点指向了数据对应的位置。
缺点存在回表查询现象2.为什么底层是B树
在探讨这个问题前我们先明确一件事 对于索引的树结构每个节点称为页页就是我们上面说的磁盘块是MySQL数据读取的基本单位。因此一个节点的读取对应于一次IO操作底层数据结构的设计应该往减少IO次数的方向进行优化。
如果不知道这几种数据结构的建议先面向百度学习一下这不是本文的重点。
平衡二叉查找树
- 缺点
路数太少树太深检索性能有限而且会导致查找过程中IO次数很多。红黑树
- 缺点
1 同样的路数太少树太深检索性能有限
2 另外红黑树不是绝对平衡IO次数会不稳定。
其实所有的二叉树都有类似的局限性。B树多叉
- 优点
1 路数比较多成功地将二叉树的瘦长结构优化成了矮胖结构极大地减少了IO查询次数。
2 另外叶节点都在同一层上IO次数是比较稳定的。
其实B树已经比较理想了那为啥MySQL索引没用B树呢
- 不足
1 每个非叶节点存储的是索引数据其中数据是一条记录
试想一下如果一条记录属性非常多那么B树的每个节点能存储的数据就会变少
面对海量的数据最后B树就会从矮胖型变成瘦长型IO次数势必无法得到优化
2 注意看叶节点层不同节点不是连续的当我们需要进行快速范围检索的时候B树恐怕无法满足我们的要求。 B树多叉
- 优点
1 非叶节点仅存储索引不存储数据每个节点可存储较多索引值因此可保证树是矮胖型的IO次数得到优化
2 叶节点全部在同一层IO次数十分稳定
3 叶节点保存索引数据并且增加了双循环链表的支持可支持快速范围检索为什么底层是B树现在知道原因了吧这种设计是不是很巧妙
3.MySQL索引优化
光了解理论也没什么意思直接实战。这部分请 下载我在文章一开始提供的github仓库源码里面有必要的数据。
由于频繁插入数据会导致索引维护代价很大因此我没在创建表时添加索引而是在插入大量数据结束后再手动添加索引。
SQL性能分析之explain
explain可以分析一条sql的优劣通过mysql反馈我们需要我们自己去读懂explain的sql执行结果来判断是否要进行优化。
使用
explain sql语句;建议先看看这个老哥的博客了解下explain方便我们调优。
Q.MySQL如何查看查询是否用到了索引
执行explain语句查看结果中的type字段使用到的索引类型、possible_keys字段可用索引未必是最终使用的、key字段实际使用的索引。
优化一为表添加自增主键
我的三张表都是使用的InnoDB存储引擎上面提到了InnoDB会默认为每张表建立一个聚集索引主键索引 这个聚集索引会以主键为键。因此主键建议自增且最好不要修改否则索引维护代价很大。
优化必要性设置自增主键更加符合主键索引的底层特性有序、范围查找使其发挥最高检索效率。并且自增主键后期不建议进行修改。
考虑到我的三张表都没有设置主键自增因此这是我针对数据库需要优化的第一个地方。 表结构修改思路原id重命名重新添加自增主键
仅展示一张表
-- ----------------------------
-- 优化1设置主键自增
-- ----------------------------
DROP TABLE IF EXISTS claim;
CREATE TABLE claim (id int(0) NOT NULL AUTO_INCREMENT,app_no char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,tree text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,PRIMARY KEY (id) USING BTREE
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic;SET FOREIGN_KEY_CHECKS 1;重构表结构之后再执行data.sql插入数据。
优化二添加唯一索引
注意插入大量数据之后再设置索引可以避免索引的频繁维护。
请看claim这张表这个app_no字段是唯一的考虑到我需要经常查询这个字段我给它设置一个唯一索引。
ALTER TABLE claim
ADD UNIQUE INDEX(app_no);text表中的index字段也是同理
ALTER TABLE text
ADD UNIQUE INDEX(index);优化三添加联合索引
联合索引数据存储方式先对索引中第一列的数据进行排序而后在满足第一列数据排序的前提下再对第二列数据进行排序以此类推。
优化规则
1 考虑选择性选择性count(distinct 字段名)/count(*)
将选择性最高的列放到索引最前列但是不是绝对的。
2 把经常同时出现在where and子句中的字段设置成联合索引ALTER TABLE text ADD INDEX id_app_no(id,application_no,date);SELECT * FROM text
WHERE id1 AND application_noEP2567834 AND date20170614;Q.索引失效场景联合索引非连续
建立联合索引(a,b,c)where c 5是否会用到索引为什么 用不到因为联合索引遵循左匹配原则where c5子句中联合索引直接从a断开了所以用不到该联合索引。
优化四针对order by的优化
可优化的情况
# 查询语句
explain select * from text
where application_noEP2567834 and date20170614
order by id;针对这种情况建立联合索引(application_no,date,id)能命中索引
注意where子句中出现的字段放前面order by中的字段放后面效率会更高。
alter table text add index app_no_id(application_no,date,id);Q.索引失效场景order by同时使用ASC 和 DESC
以下情况索引会无法命中
SELECT FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;Q.性别字段适合添加索引吗
不适合
性别字段因为可重复肯定只能建立非聚集索引然而因为非聚集索引叶子节点存储的是索引值和聚集索引值需要回表。所以在性别这种辨别度较低的字段上建立索引索引树可能只有两个节点跟线性查找没有太大区别并且因为回表的存在导致在聚集索引树和非聚集索引树来回切换反而导致查询时间更慢。并且维护该索引还要一定的开销。
参考
博客1 博客2 博客3
