![](https://img-blog.csdnimg.cn/20201014180756927.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
MySQL
MySQL数据库
developer@liyong
GISer
展开
-
Mysql-性能优化-常见优化手段
show profiles :以列表形式显示最近发送到服务器上执行的语句的资源使用情况,显示的记录数由变量:profiling_history_size 控制,默认15条。执行子查询时,MYSQL需要创建临时表,查询完毕后再删除这些临时表,所以子查询的速度会受到一定的影响。:建议使用left join时,以小表关联大表,因为使用join的话,第一张表是必须全扫描的,以少关联多就可以减少这个扫描次数。1 如果我们已知查询的数据是唯一的,最好在查询的最后就加LIMIT 1,这样可以避免全表扫描。转载 2024-02-08 16:01:38 · 52 阅读 · 0 评论 -
深入学习MYSQL-使用存储过程(游标)
非常重要的是存储过程中局部变量必须在游标之前定义,而句柄必须在游标后定义,它指定了游标到某个状态可以做一个操作,通常用来结束循环。注:mysql 中的变量都必须以@开始,这里就是调用存储过程给这三个变量复制,后面就可以进行使用。注:当我们完成存储过程的创建后我们需要还原分割符号为;输入delimiter;综上:存储过程的功能非常的强大,灵活的应用需要在实战中不断积累心得。再写一个等价的例子,这个例子比较符合编程习惯,循环用while。再看一个例子我们用存储过程来建一个表并把现有的数据进行插入。原创 2023-05-11 21:50:16 · 505 阅读 · 0 评论 -
Mysql-日志介绍 && 日志配置
binlog记录了数据库所有的ddl语句和dml语句,但不包括select和show语句内容因为不是更新操作(这些日志由General query log 来记录),语句以事件的形式保存,描述了数据的变更顺序,binlog还包括每个更新语句的执行时间信息。general query log 默认关闭,由于通用查询日志会记录用户的所有操作,其中还包含增删查改等信息,在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO,会影响MySQL的性能的。log_warnings 为0, 表示不记录告警信息。原创 2024-01-26 21:56:53 · 3073 阅读 · 0 评论 -
Mysql-数据库压力测试
可以看到最大连接数30和20差距不会特别大,20和10之前的TPS还是有一些差距的,可以初步得出结论不是最大连接数越大越好,事实我测试的是云服务器,带宽也就10M所以可能还是被带宽卡主了,如果是生产环境就需要配置不同的连接参数来压测,从而得到一个合理值。1 在配置连接池的时候可以选用隔离级别,如果不选默认就是数据库的隔离级别。2 配置Jdbc Request的时候可以写带有参数的SQL。原创 2024-02-06 15:06:04 · 1760 阅读 · 0 评论 -
深入学习MYSQL-权限管理
这条语句标识给demo这个用户给liyong这个库的选择权限。可以看到表列有很多权限Y代表拥有该权限,N标识没有权限。需要注意当前用户要有给其它用户开权限的权限。用这个用户登录没有权限操作。给所以的权限给demo。原创 2023-05-12 18:15:01 · 104 阅读 · 1 评论 -
数据库-主从同步(延迟,全库与部分同步)
在我们搭建的这个主从集群中,有一个比较隐藏的问题,就是这样的主从复制之间会有延迟。当然,这在我们目前的这个集群中是很难出现的,但是在大型集群中会很容易出现。之前提到,我们目前配置的主从同步是针对全库配置的,而实际环境中,一般并不需要针对全库做备份,而只需要对一些特别重要的库或者表来进行同步。首先在Master端:在my.cnf中,可以通过以下这些属性指定需要针对哪些库或者哪些表记录binlog。然后在Slave端:在my.cnf中,需要配置备份库与主服务的库的对应关系。转载 2024-03-03 16:50:44 · 69 阅读 · 0 评论 -
深入学习MYSQL-数据操纵及视图
本博客中的例子和文字大部分来源于书籍《mysql必会知识》,后续会根据更多的书籍不断完善此笔记。原创 2023-05-11 11:29:26 · 516 阅读 · 0 评论 -
Mysql-索引创建,索引失效案例
可以看到走了最左索引name和age这两个字段是可以走组合索引的,age是查询的范围不是等值比较后面的pos就走不了索引了,但是这里可以索引下推,就是后面这个条件可以直接在查询的时候利用起来做一进一步筛选,可以减少服务端筛选的工作量。总结:我们使用组合索引的时候要遵循最左匹配原则,组合索引最左的索引如果没有则使用不到索引,如果有最左字段,但是中间字段没有,那么这个时候只会应用最左的字段。可以使用了组合索引的一部分,key_len是768(最左匹配),ref中也只是引用了一个字段。3 不能再索引上做计算。原创 2024-01-30 10:52:56 · 1058 阅读 · 0 评论 -
Mysql-Binlog
因为binlog日志文件:mysql-bin.000005是二进制文件,没法用vi等打开,这时就需要mysql的自带的mysqlbinlog工具进行解码,执行: mysqlbinlog mysql-bin.000005 可以将二进制文件转为可阅读的sql语句。sync_binlog=0或sync_binlog大于1,事务被提交,而尚未同步到磁盘。1:每提交一次事务,存储引擎调用文件系统的sync操作进行一次缓存的刷新,这种方式最安全,但性能较低。方式一:使用mysqlbinlog查看binlog日志文件。转载 2024-03-01 13:39:08 · 37 阅读 · 0 评论 -
深入学习MYSQL-事务
保留点(savepoint)指事务处理中设置的临时占位符(place- holder),你可以对它发布回退(与回退整个事务处理不同)。首先在开始介绍使用事务之前,我们先说明一下。在建表是时候如果需要使用事务的话最好要使用InnoDB这个引擎,更好的支持事务。 提交(commit)指将未存储的SQL语句结果写入数据库表; 回退(rollback)指撤销指定SQL语句的过程; 事务(transaction)指一组SQL语句;上面相当于没有对表进行删除和插入操作。未完更多知识待补充。原创 2023-05-12 17:31:13 · 88 阅读 · 0 评论 -
Mysql-性能优化-数据库优化
但是需要注意点 是开启慢查询日志会对系统性能有一定的影响,可以考虑打开其中一台从服务器的慢查询日志,这样既可以监控慢查询,对系统性能影响又小。对于需要经常联合查询的表,可以建立中间表以提高查询效率。通过建立中间表,将需要通过联合查询的数据插入到中间表中,然后将原来的联合查询改为对中间表的查询。通常都是在统计当中使用,每次统计报表的时候都是离线统计,后台有有一个线程对你这统计查询结果放入一个中间表,然后你对这个中间表查询。表的规范化程度越高,表和表之间的关系越多,需要连接查询的情况也就越多,性能也就越差。转载 2024-02-08 20:20:42 · 61 阅读 · 0 评论 -
深入学习MYSQL-命令行的使用
注意:在使用命令行的时候,需要加分号,单纯的敲击回车是不会执行命令的。总共8条数据,我们limit5,3返回5行后的三条数据。原创 2023-05-08 22:40:27 · 90 阅读 · 0 评论 -
Msql-数据库死锁
我们按照表中是执行顺序,首先事务1给1这条记录加了记录锁,然后事务2给5这条记录加了记录锁,这个时候事务1又想取更新记录5就需要等待事务2提交释放5的记录锁,同样事务2想要删除记录为1的数据也需要等待事务1提交释放1记录的锁。这样事务1等待事务2,事务2等待事务1就造成了死锁。事务1持有了这条记录的写锁,然后插入的时候由于需要先当前读最新的数据,所以需要加读锁,但是写锁是排它的就需要等待写锁释放,但是写锁只有在事务提价的时候才会释放,因此产生了死锁。等另一事务执行完成之后,再重新执行该事务。原创 2024-02-06 11:20:52 · 801 阅读 · 1 评论 -
Mysql-数据库优化-连接参数
这个参数是管理连接池数量的,和线程池的思想一样,spring.datasource.druid.min-idle这个相当于核心线程数,无论怎样连接池始终保持这么多的连接数量。数据库连接池化的思想也就是提前将连接创建好并保存用的时候直接拿来用,同时要进行管理,也就有了上面这些相关的参数,如果不适用池化的思想,数据库会有一个最大的连接数,如果超过这个最大连接数,数据库直接无法处客户端的查询请求。可以看到一旦达到最大连接数就会报错,异常比例是相当高的,但是使用连接池就不会有这种大量报错的现象。原创 2024-02-06 15:51:20 · 1239 阅读 · 0 评论 -
Mysql-存储引擎-InnoDB
传统的LRU算法存在问题,举个例子,比如我们现在访问了某些数据,数据比较多,这个时候这些数据页就会被写到队列头部,但是这些数据我们之后不再访问,那我们之前频繁访问的数据已经被挤到队尾了,这个时候之前频繁访问的数据就会进行刷盘操作,而这些只访问过一次的数据留在了缓冲区,那岂不是我们再访问频繁的数据又会产生IO重新将它加载进来,所以为了解决这个问题MySQL基于传统的LRU做了冷热分离的LUR。系统表空间与独立表空间中的表可以向通用表空间移动,反之亦可,但系统表空间中的表无法直接与独立表空间中的表相互转化。原创 2024-01-28 13:45:12 · 1184 阅读 · 0 评论 -
数据库-Mysql-读写分离(Atalas,Mysql-Router,Mysql-Proxy)
至此使用mycat 搭建读写分离就完成了,这里要注意是基于已经搭建好的一主两从,如果之前没有搭建虽然实现了读写分离,但是主库的数据不会同步。MySQL Router最早是作为MySQL-Proxy的替代方案出现的。不推荐使用,使用MySQL Router代替。这个是很老的了,了解即可。原创 2024-03-06 08:06:38 · 1007 阅读 · 0 评论 -
Mysql-事务(隔离级别,事务底层原理,MVCC)
事务:事务指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败。原子性(Atomicity): 原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。一致性(Consistency):事务前后数据的完整性必须保持一致隔离性(Isolation):多个用户并发访问数据库时,一个用户的事务不能被其它用户的事务所干扰,多个并发事务之间数据要相互隔离。隔离性由隔离级别保障。原创 2024-01-29 14:19:09 · 1027 阅读 · 0 评论 -
Mysql-Explain-使用说明
使用了文件排序,说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。这种操作需要优化sql。表示相应的SELECT查询中使用到了索引,避免访问表的数据行,这种查询的效率很高!原创 2024-02-08 12:22:38 · 1049 阅读 · 0 评论 -
深入学习MYSQL-使用触发器
注:这里还有一点在使用before更新值的时候,我们这个列不能是外键,外键是不能更新成功的。我们可以看到执行结果,我们插入里面写的是5.99但是最终存储是111.0是因为在插入之前我们更新了这个值。在update之前我们也可以new中的值,这个用法和insert一样这里哦不在演示。每个表最多支持6个触发器,(insert,update,delete)之前和之后。这个在删除之前和删除之后都可以通过old虚拟表拿到值。同时我们是可以在插入之前修改值的。原创 2023-05-12 16:21:07 · 1982 阅读 · 0 评论 -
Mysql-锁-案例分析
不支持ICP,因此Index Filter在MySQL Server层过滤,不满足Index Filter的记录,也需要加上记录写锁;而Table Filter对应的过滤条件,则在聚簇索引中读取后,在MySQL Server层面过滤,因此聚簇索引上也需要写锁。支持了ICP,则在index上过滤,则不满足Index Filter的记录,无需加记录写锁;等值条件,命中,命中记录的辅助索引项 + 主键索引项加记录锁,辅助索引项两侧加间隙锁。范围条件,命中,包含where条件的临键区间,加临键锁。原创 2024-01-31 17:16:30 · 931 阅读 · 0 评论 -
数据库-执行计划
用于 primary key 或 unique key 的所有列与常数比较时,所以表最多有一个匹配行,读取1次,速度比较快。Using index:这发生在对表的请求列都是同一索引的部分的时候,返回的列数据只使用了索引中的信息,而没有再去访问表中的行记录。4.ref * 相比eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行。15.key_len 这一列显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。转载 2024-07-16 22:06:07 · 23 阅读 · 0 评论 -
Mysql-主从架构篇(一主多从,半同步案例搭建)
从 MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。GTID即全局事务ID (Global TransactionIdentifier),其保证每个主节点上提交的事务,在从节点可以一致性的复制。这种方式强化了数据库的主备一致性,故障恢复以及容错能力。原创 2024-03-03 16:11:38 · 2267 阅读 · 0 评论 -
Mysql-ReadView + MVCC-RR 与 RC
这时候广东这个版本不符合可见要求因为它在m_ids中所以看下一个版本,发现西安同样不满足要求,接着成都,重庆都不符合要求所以最终取的是杭州这个版本,因为这个是之前的事务做的更改,事务id小于最小的限制。由于事务1,2都提交了生成的时候1,2不在m_ids中,所以找到广东已经是可以见的版本,所以最终结果为广东。后面T8,T10都不生产ReadView,那么每次查询可见的版本都是杭州,这样就保证了事务RR隔级别。由于事务1提交了生成的时候1不在m_ids中,所以找到成都已经是可以见的版本,所以最终结果为成都。原创 2024-01-29 13:25:00 · 637 阅读 · 0 评论 -
Mysql-InnoDB-数据落盘
这个时候我们不能通过Redo log来恢复,重做日志中记录的是对页的物理操作,而不是页面的全量记录,而如果发生partial page write(部分页写入)问题时,出现问题的是未修改过的数据,此时重做日志(Redo Log)无能为力。:要求数据的变更写入到磁盘前,首先必须将内存中的日志写入到磁盘;2 可以解决缓冲池不够用问题,缓冲池不够用时,将脏页刷新到磁盘:当缓冲池不够用时,根据LRU算法会溢出最近最少使用的页,若此页为脏页,那么需要强制执行Checkpoint,将脏页也就是页的新版本刷回磁盘。原创 2024-01-28 17:51:59 · 1005 阅读 · 0 评论 -
Mysql-锁
记录锁锁住的永远是索引,而非记录本身,即使该表上没有任何显示索引,那么innodb会在后台创建一个隐藏的聚簇索引索引,那么锁住的就是这个隐藏的聚簇索引索引。使用场景是做全库的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的完整性,但是对于innodb我们不这样用,这样会影响业务,因为所有的更新操作被阻塞。看下面这个例子,我们想要插入60和70但是在插入60的时候锁间隙的话会把11-99都锁起来,这个时候70的插入被阻塞,但是其实他们之前本来不存在竞争的,这样锁的范围太大了。原创 2024-01-30 16:54:17 · 1223 阅读 · 0 评论 -
深入学习MYSQL-数据检索
由于大部分基础知识都已经学过了,这里只把觉得应该记录一下的知识点做个笔记。然后以下笔记和sql均来自书籍(MYSQL必会知识)原创 2023-05-09 13:31:46 · 1338 阅读 · 1 评论 -
Mysql-索引
索引树中节点中的索引项按照(a,b,c)的顺序从大到小排列,先按照a列排序,a列相同时按照b列排序,b列相同按照c列排序。在最底层的叶子节点中,如果两个索引项的a,b,c三列都相同,索引项按照主键id排序。覆盖索引主要是为了解决回表的问题,如果我们查的辅助措意或者组合索引已经包含了全部我们要投影的列数据,这个时候其实是没有必要进行回表查询索引的,这个是不需要我们建立的,MySql会做相应的工作。在 MyISAM 中,辅助索引和主键索引的结构是一样的,没有任何区别,叶子节点的数据存储的都是行记录的磁盘地址。原创 2024-01-30 08:14:12 · 1025 阅读 · 0 评论