mySQL学习笔记：mysql背后架构设计，索引设计及失效情况

一、mysq架构设计

innodb 后缀 .idb myisam 后缀 .myi
存储引擎;不同数据文件在磁盘的不同组织形式。

内存跟磁盘通过io进行交互，因此提高效率可以从减少量或者减少次数方面入手。

分块读取

磁盘预读：磁盘跟内存在进行交互的时候，一个最小逻辑单位 页(dataPage),大小是4kb或者8kb,具体和操作系统相关，读取是页的整数倍。

innodb存储引擎中，每次默认读取16kb（innodb_page_size），每次在进行数据读取时，可以选择页的整数倍来操作。

二、mysql的索引系统设计

索引储存在磁盘上。
索引以k-v格式进行存储，key:索引对应字段的值 key:整行数据(存储位置，地址，偏移量)。
OLAP(联机分析处理)->数据仓库->对历史数据进行分析，产生决策性的影响，但是不要求效率。
OLTP(联机事务处理)->数据库->支撑业务系统需要，及时返回数据。

索引为什么不用哈希表存储？
1,哈希表易造成冲突和碰撞，除非设计好的算法。
2,哈希表是无序的，范围查找时效率低。
mysql中的memory存储引擎支持哈希索引。
innodb支持自适应哈希。

索引为什么使用B+ Tree 存储?
非叶子节点的data全部放到叶子节点中；
1.B+ Tree每个节点可以包含更多.
2.非叶子节点只存储key,叶子结点存储key,data;
3.叶子节点相互连接，符合磁盘预读，顺序查询性能更高。

一般3-4层的B + Tree 足以支持千万级别；满足业务系统的情况下，主键能自增就自增，（不自增可能会出现磁盘块分裂）

索引分类：
主键索引
唯一索引
普通二级索引
全文索引 类似于Elasticsearch的功能
组合索引(联合索引)

聚簇索引和非聚簇索引区别？
数据是否跟索引存储子在一起来区分，在一起为聚簇索引，反之为非聚簇索引。

结：一个表理论可有无限个索引，每个索引都是一个B+ Tree ,无论多少个索引只有一份数据。
多个索引存在时：innodb在数据存储时，必须跟一个索引放在一起依次为：
主键索引->唯一索引->6字节RowId

参数 innodb_file_pat_size on->每一个表一个空间
off->idbatal

索引设计注意问题：
1，索引字段空间越小越好
2，索引个数不要太多
3，不要选择经核查那个修改的字段做索引
4，满足业务需求下，id尽可能自增
5，索引选择性 唯一值/count *100%>=80%适合当索引
6，索引字段尽量不要允许为null

索引失效的情况：
1，索引字段使用表达式
2，like左边有%
3，隐式乐行转换
4，使用函数
5，组合索引时，不要在开始字段添加 >或者< ，会导致后续字段索引失效
6，or,and可能失效，情况较复杂
7，组合索引，最左原则。

关注微信公众号【川蜀程序猿】，领取海量简历模板、Java精品书籍！！
![在这里插入图片描述](https://img-blog.csdnimg.cn/88916c334ff2465495c404efa03098c9.jpeg