Mysql数据库知识学习

最新推荐文章于 2023-07-14 19:16:38 发布

予我欢喜

最新推荐文章于 2023-07-14 19:16:38 发布

阅读量275

点赞数 1

分类专栏： sql

本文链接：https://blog.csdn.net/weixin_43611545/article/details/98972282

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

数据库的范式规范：
第一范式：保持每一列的原子特性，即每一列不能在分割
第二范式：属性完全依赖于主键（针对联合组件）
第三范式：属性不依赖与其他主属性（不传递）
Mysql的存储引擎：
Mysql区别于其他关系型数据库的一个特点，就是其插件式的存储引擎。二维表存储在磁盘上，都要存储user.frm（desc user/show create table user），user表的数据，user表的索引。不同的存储引擎，指的是上面三个内容的存储方式不同。
MyISAM的特点：
1.把数据和索引是分开存放的，user.frm,user.MYD,user.MYI三个文件
2.所以MyISAM支持的索引结构是：非聚集索引
3.MyISAM是不支持事务、外键
4.MyISAM支持的事务并发操作锁的粒度：表锁，所以并发效率不高，但是它比较安全，不会出现高并发带来的死锁问题

InnoDB特点： 1.把数据和索引放在一起的，user.frm,user.ibd两个文件，在ibd文件中，存储了数据和索引
2.所以InnoDB支持的索引结构是：聚集的索引
3.InnoDB支持事务处理、外键设置 4.InnoDB支持的事务并发操作锁的粒度：行锁，所以并发效率高，但是有可能产生死锁问题，需要在事务处理的过程中，仔细的考虑事务处理具体过程

Memory：基于内存存储的存储引擎（frm-磁盘，数据和索引-内存）
Mysql的日志系统：
二进制日志（binlog）：记录了当前数据库操作过程中所有的CURD语句。
查询日志：记录了所有select语句的操作。错误日志：主要mysql server启动、关闭、运行过程中出现的一些严重的error。mysql server:3306 redis:6379
慢查询日志：slow_query_time 表示慢查询的时间设置，所有查询时间超过slow_query_time 的select 语句，都会被记录在慢查询日志当中，然后可以通过explain + SQL语句，来查看SQL语句的执行计划，根据打印的结果，查看索引是否使用正确，有没有额外的ﬁle sort等等，然后进行SQL语句优化。
Mysql的索引：
索引的底层实现原理
MySQL的Memory存储引擎，采用哈希索引（哈希索引大的缺点，就是对于SQL语句的区间范围查找，只能做整表搜索）
MyISAM和InnoDB存储引擎，采用的都是B+树索引。

聚集索引：MySQL采用的存储引擎是InnoDB，数据和索引是放在一个文件当中，可以理解为数据就直接放在索引树上

非聚集索引：MySQL采用的存储引擎是MyISAM，数据和索引是分开放在不同的文件当中，可以理解为索引单独存储在索引的B+树上，而数据是要单独访问的

主键索引：因为设置primary key本身就会给主键创建索引，由主键构成的索引树，就称作主键索引 select * from user where id=3

InnoDB：通过搜索主键索引树，找到id=3的索引了，主键所在的该行记录的值都被拿到了 MyISAM：通过搜索主键索引树，找到id=3的索引了，此时拿不到数据，只能得到数据所在的地址（磁盘的位置MYD），再去读取相应的数据记录。

辅助索引：给除主键之外的其它的列创建索引。辅助索引树上，存储的是辅助索引+主键值
id name(name_index) age sex select * from user where name=“zhang san”

InnoDB：通过搜索辅助索引树，找到name="zhang san"的索引了，就得到这一行记录的主键值id，然后再去主键索引树上，搜索对应id的用户的所有信息 select id from user where name=“zhang san” select id,age from user where name=“zhang san”
MyISAM：通过搜索辅助索引树，找到id=3的索引了，此时拿不到数据，只能得到数据所在的地址（磁盘的位置MYD），再去读取相应的数据记录。

InnoDB存储引擎：主键索引和辅助索引的存储方式是有区别的
MyISAM存储引擎：主键索引和辅助索引的存储方式是一样的

       **MySQL最终为什么要采用B+树存储索引结构呢**，

那么看看B-树和B+树在存储结构上有什么不同？ 1.B-树的每一个节点，存了关键字和对应的数据地址，而B+树的非叶子节点只存关键字，不存数据地址。因此B+树的每一个非叶子节点存储的关键字是远远多于B-树的，B+树的叶子节点存放关键字和数据，因此，从树的高度上来说，B+树的高度要小于B-树，使用的磁盘I/O次数少，因此查询会更快一些。
2. B-树由于每个节点都存储关键字和数据，因此离根节点近的数据，查询的就快，离根节点远的数据，查询的就慢；B+树所有的数据都存在叶子节点上，因此在B+树上搜索关键字，找到对应数据的时间是比较平均的，没有快慢之分。
3. 在B-树上如果做区间查找，遍历的节点是非常多的；B+树所有叶子节点被连接成了有序链表结构，因此做整表遍历和区间查找是非常容易的。

**MySQL为什么不采用红黑树/AVL树做底层索引存储结构呢？而选用B+树！**

AVL树：2阶的平衡树 1个数据域+2个地址域
B+树：m阶平衡树 m-1个数据域+m个地址域（m：300-500之间的 16K 4K 一般一次磁盘I/O读取一个磁盘block块，就刚好放在了B树的一个节点上）
10000000 AVL 24 差的情况下，从AVL上读取一个索引，需要花费24次磁盘I/O B+树 m：400 3层(400^3) 从B+树上读取一个索引，多花费3次I/O

Mysql的事务处理：
事务：表示一组SQL语句，一个事务中的所有的SQL语句执行成功，终事务的结果才能提交，如果一部分成功，一部分失败，事务终要回滚（binlog重做日志）到事务开始之前的状态。
事务的ACID特性

        **1.原子性：**

一个事务中的所有的SQL语句执行成功，终事务的结果才能提交，如果一部分成功，一部分失败，事务终要回滚（binlog重做日志）到事务开始之前的状态。

         **2.一致性：**

事务处理完成后，数据库的数据状态从状态A转成状态B，但是数据的总量是不变的。事务并发操作带来的数据不一致性，主要体现在三个方面：脏读，不可重复度，幻读。
脏读：事务B读取了事务A还未提交的数据。（一定要防止的）
不可重复读：一个事务的执行过程中，两次相同条件的查询，但是查询的结果不一样，因为两次查询的中间，有其它事务对相同条件的数据进行了更新。（oracle）
幻读：同一个事务的两次查询，第二次查询出现了之前没有的数据，说明中间有其它事务增加了满足相同条件的记录。

      ** 3.隔离性：**

为了让事务的并发执行更安全，MySQL给事务提供了以下隔离级别的定义：
事务隔离级别脏读不可重复度幻读
未提交读 x x x
已提交读 ok x x
oracle的事务隔离级别默认工作在“已提交读”级别上
可重复度 ok ok x
MySQL的事务隔离级别默认工作在“可重复度”级别上
串行化 ok ok ok

事务的隔离级别越高，效率越低，但是也更安全；隔离级别越低，效率越高，但是数据越不安全

       **4.持久性：**

事务更新数据成功以后，不管MySQL数据库发生任何异常，都要保证数据是能够恢复的

隔离级别的原理 - S锁，X锁，间隙锁
S锁 - 共享锁（读） X锁 - 排它锁（写）
串行化完全利用的就是S锁和X锁。已提交读和可重复度都是通过MVCC（多版本并发控制，给所有的记录都打了版本标签）
间隙锁是给表的后一行记录的后面，称作“间隙”的地方加锁，防止串行化事务隔离级别下幻读的发生。
SQL和索引优化和实现原理
项目中SQL或索引优化？具体怎么做的？
开启慢查询日志，设置一个项目中能接受、合适的慢查询时间，那么运行项目一段时间，查看慢查询日志里面所有查询超值指定时间的select语句，然后用explain查看以下SQL语句的执行计划，再做具体的分析，是做了整表搜索（那就要考虑是否要创建索引），或者索引没用到（考虑SQL语句的书写是否正确），再进行问题修改。

在业务执行过程中，查看CPU或者磁盘I/O如果过高，考虑表的索引创建太多了（需要优化索引结构），另外考虑表的数据过大了（考虑分表操作）。

1.对于单个的SQL语句，给作为条件过滤的列创建索引，如果当前SQL语句，除了where子句，还有排序order by或者分组group by，考虑创建联合索引。 2.创建的索引是不是一定会用到，MySQL Server会进行语句优化，如果使用索引查询的结果过多，就直接做整表查询。
3.select * from student where score >=90.0 or score <60.0 如果score创建过索引，是否能用到？也可能会用到，因为MySQL Server会把上面的or子句，优化成 select * from student where score>=90.0 union select * from student where score<60.0 合并查询 4.select * from student where id in (1,2,3,4,5,6,7,8)
select * from student where id in (select uid from studentscore where score >= 60.0) 带in的子查询，可以使用索引
select * from student where id not in (select uid from studentscore where score >= 60.0) not in用不到索引，一般把上面的语句优化成in子查询，或者优化成外连接查询
5.select * from student where age>20 and score>80.0 and sex=‘女’;
注意：依次查询一张表，只能使用一个索引，MySQL Server首先会通过age , score, sex的索引进行过滤数据，哪个过滤出来的数据少，就用哪个索引
6.select * from user where score between 60.0 and 80.0 可以用索引
7.select * from user where address like ‘陕西省*’; // * % select * from user where address like '东大街’;
like+通配符，如果通配符在前面，无法使用索引；如果通配符在后面，可以使用索引。 select * from usermessage where message like ’ 旅游：旅游 * '; 8.select * from user where cellphone = 18256781234; 如果SQL语句涉及了类型转换，那么索引就用不上了。
9.多表的查询

                     id  name pwd  sex  addressid
                                            1                                       
                 id  country  provice  city    street
                 1  中国      陕西省    西安市    曲江街道 
                 查询uid = 3 人的所有信息 
   select a.*,b.country,b.provice,b.city.b.street from user a 
   inner join address b on a.addressid = b.id where a.id=3     // a 20(过滤以后成5行) b 10

多表连接查询的索引是怎么使用的？
在多表连接查询的时候，先区分大表和小表（这里的大小指的就是表的记录的个数），小表是整表搜索的，上面假设a是10行，b是20行，那么a就是小表了，把a进行整表搜索，找到所有的addressid ，然后在大表b里面进行查询，找符合a.addressid = b.id条件的b表的记录。
小表决定查询次数，大表决定查询时间，小表已经要整表搜索了，所以没必要创建所以，大表相应的条件过滤字段一定要创建索引！！！！！通过explain具体分析以下SQL的执行计划！！！
MyISAM与InnoDB的主要区别：

条目：https://blog.csdn.net/silyvin/article/details/79332879
具体：https://blog.csdn.net/wjtlht928/article/details/46641865

予我欢喜

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql数据库知识学习

数据库的范式规范：第一范式：保持每一列的原子特性，即每一列不能在分割第二范式：属性完全依赖于主键（针对联合组件）第三范式：属性不依赖与其他主属性（不传递）Mysql的存储引擎：Mysql区别于其他关系型数据库的一个特点，就是其插件式的存储引擎。二维表存储在磁盘上，都要存储user.frm（desc user/show create table user），user表的数据，user表的索...
复制链接

扫一扫