mysql初级入门学习

JAVA官网

已于 2023-04-04 14:36:46 修改

阅读量86

点赞数

文章标签： mysql 数据库

于 2023-04-04 14:28:30 首次发布

本文链接：https://blog.csdn.net/qq_41619534/article/details/129952226

版权

mysql Innodb 二叉树

16kb页 - 目录项（双向链表） - 目录页（链表链接）（包含数据就是索引值）

页：1.文件头 2.页头 3.最大最小记录数 4.用户记录 5.空闲空间 6.页目录 7.文件尾

行--页--区--段--表空间

二分法查找

索引名词

聚族（集）索引（myIsam没有）非聚族（集）索引

ID主键：自增主键除了简单都是缺点

可靠不高安全不高性能差交互多局部唯一性（非核心业务使用）

时间+去重字段+用户ID后6位（建议）

UUID 32字节全局唯一性性能差可能导致也分裂无序的 8.0优化UUID16字节

聚族索引（存储记录是物理上连续存在）

非聚族索引（逻辑上的连续，物理存储并不连续

页就是索引的值查其他数据就要回表）

索引分类：单列索引：普通索引唯一索引主键索引全文索引

多列索引：组合索引联合索引（最左前缀原则）

（过多索引占用内存空间修改会占用更多时间）

ID主键（聚族索引）: 保存所有的值

优点：访问更快排序范围性减少IO ；

缺点：插入慢可能导致也分裂（如果改变主键的值）

数据结构对比（复杂度）

hash结构等值优势大例如redis hash表即：函数散列函数

hash值取模对相等的值使用链表或者二叉树/多叉树排列 hash是等值查询对于比较就无能为力了

Innodb自适应hash索引

链表结构单向链表和环形链表

本身的信息，称为“数据域” 指向直接后继的指针，称为“指针域”

二叉树结构（链表发展过来）

AVL树平衡二叉树

B-Tree 多路平衡二叉树（三叉树）页根据结构拆分挂载结构有数据

B+Tree 页不拆分矮胖

适合创建索引情况

>唯一的限制

>频繁作为WHERE查询条件的字段或者 UPDATE DETELE>经常Group by Order by分组或排序

>Order by 1.使用前面必须有查询条件where 2.尽量全索引字段排序

3.禁止多字段排序不统一 4.explan Extra 禁止出现 useing filesort(文件排序)

>DISTINCT 去重的字段

>Join多表查询 on使用的字段

>1.数据类型要优先选择内存小的作为索引 2.字段区分度要高大于90%

>字段长的可以使用字符串前缀创建索引

>频繁的列放到多列索引最左测

>多列索引优于单列索引

优化步骤：观察查询用时大的sql 开启慢查询 slow-query-log

explain 执行sql 分析 type:访问方法 key:使用的索引 key-len:使用的长度 rows:读条数 filted:百分比 Extra:额外信息

最左前缀索引多列索引排序优化
主键插入顺序不要使用无序
sql中使用函数或者类型转换会导致索引失效
范围条件会导致右边的索引失效解决方案：把范围条件放到最右
不等于！= >< (只需要回表范围超过全部记录的20%)索引失效
is null 可以使用索引 is not null (只需要回表范围超过全部记录的20%)索引失效
like 使用通配符以%开头的失效（原因是，通配符“%”表示匹配0个或多个字符，使得MySQL无法使用B树索引来快速定位符合条件的行，而需要扫描整个索引树和数据表）
or 前后存在非索引的列索引失效前后有一个无索引另一个失效
数据库和表字段字符集使用不统一索引失效
in()适合子表比主表数据小的情况

exists()适合子表比主表数据大的情况

当主表数据与子表数据一样大时,in与exists效率差不多,可任选一个使用

覆盖索引和索引下推 useing index condition

覆盖索引：如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据

索引下推：5.6版本前

select * from user2 where username='1' and age=99;

首先 MySQL 的 server 层调用存储引擎获取 username=‘1’ 的第一条记录。

存储引擎找到 username=‘1’ 的第一条记录后，在 B+Tree 的叶子结点中保存着主键 id，此时通过回表操作，去主键索引中找到该条记录的完整数据，并返回给 server 层。

server 层拿到数据之后，判断该条记录的 age 是否为 99，如果 age=99，就把该条记录返回给客户端，如果 age!=99，那就就丢弃该记录。

由于 username+age 组成的复合索引只是一个普通索引，并不是唯一索引（如果是唯一索引，那么这个查询就到此结束了），所以还需要继续去搜索有没有满足条件的记录。

5.6版本

select * from user2 where username like 'j%' and age=99;

MySQL 的 server 层首先调用存储引擎定位到第一个以 j 开头的 username。

找到记录后，存储引擎并不急着回表，而是继续判断这条记录的 age 是否等于 99，如果 age=99，再去回表，如果 age 不等于 99，就不去回表了，直接继续读取下一条记录。

存储引擎将读取到的数据行返回给 server 层，此时如果还有其他非索引的查询条件，server 层再去继续过滤，在我们上面的案例中，此时没有其他查询条件了。假设 server 层还有其他的过滤条件，并且这个过滤条件把刚刚查到的记录过滤掉了，那么就会通过记录的 next_record 属性读取下一条记录，然后重复第二步。

7 数据库设计规范

范式一：字段解耦原子性不可拆分

范式二：行字段数解耦解决字段数据冗余行和列的冗余

范式三：表与表解耦（相互独立）不要多个业务对象一张表构成

反范式：业务优先原则