- 博客(151)
- 收藏
- 关注
RSS订阅
原创 假装会优化之数据库优化
数据库优化优化步骤:1:explain关键字,查询sql执行效率2:system > const > eq_ref > ref >range>index>allsystem和const是只有一条数据符合数据的情况eq_ref是使用唯一性索引ref是非唯一性索引检索range是范围索引检索index是全局索引检索3:show profil...
2019-12-12 09:37:01
75
原创 假装会优化-总结篇
性能优化往往是看一个技术人员经验和基础知识是否扎实的点,而且你需要知道大部分市面上主流的技术,和他们所代表的各自的优劣势,以及该在上面情况下使用他们.大概总结下自己知道的,所学的一些东西
2019-10-14 09:37:56
119
原创 innodb和 memory的区别
innodb和 memory的区别InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。InnoDB 支持变
2020-06-30 16:22:25
734
原创 内部临时表
内部临时表和sort buffer,join buffer一样，都用来存放语句执行过程中的中间数据，辅助语句的执行。使用用法 using temporary。使用场景数据一边查询，一边直接得到结果，不需要额外内存。比如：group by 需要计算。join_buffer 是无序数组（单字段，可以重复），sort_buffer 有序数组，内部临时表是二维结构用到二维表的特性需要用到内部临时表，比如 distinct ，group by优化group by 字段加索引
2020-06-30 16:20:33
230
原创 临时表
临时表内存表和临时表的区别内存表:是用memory引擎的表,这表的数据都放在内存中,重启时会清空数据,但是表结构还在临时表:可以使用各种引擎,如果使用innodb或myisam的话,数据是存到磁盘的,临时表的特性不同session的临时表是可以重名的,有多个session同时创建临时表,并不会因为名字一样而冲突,因为是session级别的(线程独有)不需要担心数据问题,当断开连接或异常重启的时候,临时表会自动被回收临时表的应用分库分表的情况下,用proxy,然后如果使用到每个分区都
2020-06-30 16:18:14
314
原创 join的优化
join的优化multi_range read优化(mrr)大多数的数据都是按照顺序来新增的,如果按照顺序对主键进行访问,接近于磁盘的顺序读,提高性能根据索引定位到满足条件的记录,将id放入read_rnd_buffer将read_rnd_buffer的id进行递增排序排序后的id数组,依次到主键查记录,作为结果返回稳定性的使用mrr,要用 set optimizer_switch=“mrr_cost_based=off”用explain时,在extra有using MRR,就是使用到了
2020-06-30 16:15:22
1534
原创 如何使用join
如何使用join如果能用到被驱动表的索引,则使用join比拆成多个单表执行的sql来的快使用join的话,让小表做驱动表在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。如果使用Block Nasted-Loop join算法,有可能导致join buffer不够大,导致被驱动表做多次全表扫描,所以在explain的时候Extra字段里面出现Block Nasted-Loop的话,就尽
2020-06-30 16:11:36
290
原创 kill不生效
kill语句kill不掉的第一种情况,innodb_thread_concurrency的上限了用户在执行kill的时候,处理kill的线程做了两件事把被kill的实例 的运行状态改成 THD::KILL_QUERY(将变量 killed 赋值为 THD::KILL_QUERY)；给 被kill的实例 的执行线程发一个信号。一个语句执行过程中有多处“埋点”，在这些“埋点”的地方判断线程状态，如果发现线程状态是 THD::KILL_QUERY，才开始进入语句终止逻辑；如果处于等待状态，必须
2020-06-30 15:48:31
1048
原创 误删数据之后怎么办
误删数据之后怎么办误删行使用delete之后,用Flashback工具闪回,(前提:binlog_format= row和binlog_row_image=Full)单事务的情况:insert的语句,对应的binlog event类型是write_rows event改成Delete_rows eventdelete的语句,把Delete_rows event改成write_rows eventupdate的语句,binlog记录修改前和修改后的语句,两者调换即可建议在备库上执行回滚操作
2020-06-30 15:25:08
185
1
原创 如何判断主库的健康
如何判断主库的健康select 1:说明进程还在,但是不能说明主库没问题- 有可能被占满了,设置innodb_thread_concurrency控制innodb的并发线程上限,并发连接如果线程的查询进入等待,则并发连接的计数会-1,所以使用select 1可能线程已经超过innodb_thread_concurrency,全被阻塞住了,但是select 1可用查表判断:mysql> select * from mysql.health_check; 空间满了,binlog在写数据写不
2020-06-30 15:22:46
117
原创 读写分离导致的问题
读写分离基本的读写分离有两种模式一种是client主动做负载均衡一种是在client和mysql之间有一个proxy来做代理层,两者的区别客户端直连的方式,查询的性能好一点点,架构简单,排查问题容易些,但是数据迁移,主备切换等,都会被感知且调整数据库连接信息带proxy的方式,就后端只需要注意在开发逻辑上,连接维护,后端信息维护等,都直接proxy做,但是对维护团队要求比较高有时候还是会从从库那边读到主库过期的数据,解决方案有以下几种方案强制走主库方案将查询分类,有的强一致的就
2020-06-30 15:14:41
514
原创 主备切换
主库产生问题之后怎么办,切换备库后,要指定其他的从库的主库为新上来的主库(之前的备库)需要执行一条命令,change masterCHANGE MASTER TO MASTER_HOST=$host_name MASTER_PORT=$port MASTER_USER=$user_name MASTER_PASSWORD=$password //以上是新主库的ip 端口 用户名和密码MASTER_LOG_FILE=$master_log_name MASTER_LOG_POS=$mas.
2020-06-30 15:04:24
898
原创 数据同步时的并发策略
主备并发同步版本5.5并发复制策略(个人策略)按表分发:每个worker都维护一个hashtable,表示自己正在执行的事务中涉及到哪些表,如果分发的事务中的表,和在worker中大于1个worker的表有事务冲突(有相同的表在执行),就等待,等到只有一个worker有冲突的时候,就把这个事务丢到这个worker如果热点表被多次操作,这样子就只有一个worker执行,相当于单线程按行分发binlog 必须是row,所以必须维护一个hashtable,里面的key为库名+表名+索引a的名称
2020-06-30 14:58:52
143
原创 数据同步延迟原因及主备切换策略
同步延迟t1为主库a完成事务,写完binlog的时间t2为备库b完成从a传输过来binlog的时间t3为备库执行完binlog的时间t3-t1就是备库和主库之间的延迟时间,在备库执行命令show slave status,会显示seconds_behind_master,就是延迟几秒主备延迟的原因备库所在的服务器比主库的服务器性能来的差,但是他们写入的文件是一样多的,所以尽量保证主备性能一致备库压力大,有些读操作在备库上执行,没有优化,直接大批量读取数据,导致io飙升,从而主备延迟,解决
2020-06-30 14:52:47
505
原创 数据库的数据一致
数据库主备一致都是用binlog来保证数据库主备一致的流程备库b通过change master命令,设置主库的ip host,用户名,密码,偏移量等,备库b执行start slave命令,备库开启两个线程,io-thread和sql-thread,io负责连接主库a验证用户名,密码之后,去binlog以偏移量为起点开始读,发给b备库b那个binlog之后,写到本地文件,为relay log(中转日志)备库的sql-thread开始读relay-log,解析执行binlog的三个格式
2020-06-30 14:48:35
228
原创 提高redolog和binlog的写入效率
提高redolog和binlog的写入效率LSNLSN是日志逻辑序列号,单调递增,对应redo log的写入点,每次wirte都是写redo log长度为从LSN到LSN+lengthgroup commit组提交当第一个事务a进到redo log buffer时,他作为组长,然后a提交的时候,整个buffer中的长度length为事务abc加起来的长度,所以一起把事务abc现在的redo log都wirte到page cache中,所以说组员越多,单次提交的文件越多,越节省磁盘的IO
2020-06-30 14:43:34
435
原创 binlog和redolog的写入机制
binlog的写入机制事务执行过程先把日志写到binlog cache,事务提交时,从binlog cache写入到binlog文件里事务无法被拆分,无论事务多大,都要保证一次写入每个线程都有一个系统分配的binlog cache的大小,由binlog_cache_size控制,超过则写到磁盘中多个binlog cache公用一个binlog文件,从binlog cache到binlog文件有两个步骤write:把日志写到page cache,没有到磁盘,所以速度比较快fsync:把数
2020-06-30 14:40:13
498
原创 常见问题
业务遇到问题解决短连接(连接数达到设置的max_connections上限)show processlist查看空闲的连接,直接kill查看infomation_schema中的innodb_trx表和show processlist,查询事务内空闲的连接,kill提高max_connections的值减少连接过程的损耗先确认是否被连接行为打挂了,是的话重启的时候加上-skip-grant-tables命令跳过权限校验查询慢的问题索引设计问题紧急上线索引的时候,可以先备库b设置s
2020-06-30 14:04:02
84
原创 间隙锁
间隙锁如果在查询的后面加上for update表示查询为当前读,而不是读快照,有可能会导致幻读的问题问题重现:在一个事务a中锁住了name为5的数据,然后再事务a期间,另外一个事务b把另外一条数据的name修改为5,此时,事务a再读一次name为5的数据,数据多了一条,导致一个事务里面,前后读到的数据是不一致的,(当前读才会有幻读的问题)只要在这事务里面修改或新增为当前被锁的数据,就会产生幻读为了解决幻读,引入了间隙锁间隙锁锁的是数据之间的间隙,也就是不能新增或删除间隙锁和行锁合成next-
2020-06-30 13:45:49
567
原创 查询简单语句,却卡住了
如果查询一个简单的语句,却长时间不回的话,可能出现的问题mdl锁:使用show processlist,可以在state看得到是否被锁住了设置performance_schema=on(比设置off会有10%的损耗),通过查询sys.schema_table_lock_waits,然后kill掉即可等flush有可能存在的问题就是有一个表的sleep语句,然后刚好遇到了flush语句(被sleep阻塞了),flush阻塞了后面的select语句,使用show processlist查看
2020-06-30 11:36:47
309
1
原创 索引用不上的原因
索引用不上的原因在查询条件那边用函数,或在=前面计算会导致用不上索引,查询条件做类型转化也用不上索引(因为类型转化用上了数据库中的函数CAST(column AS signed int))查询条件中字符集不同也会导致用不上索引,CONVERT(traideid USING utf8mb4)//整改方案是把字符集转换写在=的后面,而不用默认的字符集转换...
2020-06-30 11:30:13
334
原创 count()用啥好?
按照效率排序的话count(字段)<count(主键id)<count(1)≈count(*)建议使用count(*)count(主键id),先取出到service层,判断不为null,在+1count(1),判断这行有值,取1到service,判断不为null,+1count(字段)设置为不能为null:就直接按行取值,然后按行累加设置可为null:取值到service层,判断不为null,再+1count(*)如果是myISAM,没有条件的话,就直接从文件.
2020-06-30 11:27:07
127
原创 设置合理的刷页速度
如何设置合理的刷页速度脏页:redo.log的页上的数据和磁盘的数据还没同步干净页:redo.log页上的数据已经同步到磁盘上了redo.log写满了,要flush脏页到磁盘的时候,这时候所有的更新和写入操作都暂停,等flush操作写完之后才能继续执行内存不够了,要先写脏页到磁盘中,会先淘汰最先的页,如果是干净页就直接复用,如果是脏页,就先同步到磁盘,之后再变成干净页了,再复用影响性能的操作:一个查询需要淘汰的脏页太多了,导致查询响应时间长日志写满了,更新都堵住了,写能力为0innod
2020-06-30 11:14:56
125
原创 如何正确的删除数据和空间
如何正确的删除数据和空间数据删除了,但是表占的空间却没变?有一个参数是innodb_file_per_table,在版本5.6之后就默认为on,off的时候表数据在共享表空间,即使表删除了,空间也不会回收,on的话,是独立占据一个文件需要在删除之后,再alter table重建表才能让空间变小造成的其他原因索引空洞:在索引删除了之后,这个索引留下的空间是能被复用的,没有被回收,如果没能被复用,就造成了索引空洞,空间浪费,索引新增的时候也会造成索引空洞解决:重建表在5.
2020-06-30 10:58:59
250
原创 字符串索引的选择
字符串索引的选择前缀索引通过指定适当的索引长度,又不损耗太多查询次数,又能减少空间的存储如何判断索引的长度,可以用区分度来指定,区分度越高越好,基本大于95%,select count(distinct left(A,3)) as L3,count(distinct left(A,4)) as L4,count(distinct left(A,5)) as L5, ...from table但是前缀索引就用不上覆盖索引的好处了,倒序索引有些字符串长度长,但是前面一部分的字符区分度又
2020-06-30 10:46:40
475
原创 优化器的使用
关于优化器的使用索引有一个基数的概念,也就是该索引上不同数值的数越多,该索引的区分度越大,基数越大.这个索引一下子能区分的数据就越多.优化器查询sql执行所要扫描的索引基数,用的就是随机取样法先取索引树上N页的数据,判断上面有多少基数时,再乘以多少页,就是基数当索引树上的数据更新量达到1/M的时候,会重新触发一次统计基数,存储索引统计的方式可以通过innodb_stats_persistent,设置为on,表示统计信息会持久化存储,N是20,M是10设置为off,表示统计信息会保存在内存
2020-06-30 10:44:34
240
原创 mysql的order by做了啥?
排序底层做了啥MySQL会为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size如果排序的数据量小于sort_buffer_size，排序将会在内存中完成如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序在使用外部排序时，MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件mysql会通过遍历索引将满足条件的数据读取到sort_buffer，并且按照排序字段进行快
2020-06-30 10:30:23
115
原创 普通索引和唯一性索引的区别
普通索引和唯一性索引的选择两者之间读操作的区别普通索引:查询操作时,在根据查询条件在索引树上寻找相同的值后,会再往下找一个值判断是否相等,(查,判断)唯一性索引:直接查,查到之后直接返回,不需要判断change buffer:当更新数据时,如果数据也在内存中就直接更新,如果数据页不在内存中,在不影响数据一次性原则,就把相关操作操作到change buffer中,等到下次有访问这个数据页的时候,再把这个数据页读到内存中执行change buffer的操作(叫merge,后台会定期更新merge,数据
2020-06-30 10:20:41
400
原创 隔离级别的实现
事务的启动方法:当启动star transaction时 并没有马上开始事务,(一致性读的情况),要到第一个执行innordb的语句才会开启事务如果是马上开启事务,star transaction with consistent snapshot.视图的两个概念:一个是view,是查询语句定义的虚拟表,就是在执行查询结果时生成结果,create view…,查询和普通语句一致innodb在mvcc中用到的一致性视图,consistent read view,支持读提交和可重复读的隔离级别实现
2020-06-30 10:14:39
99
原创 锁
全局锁做全局逻辑备份:整个库只能只读无法更新和新增删除命令是Flush tables with read lock一致性读需要引擎支持隔离级别,myisam只能使用ftwrl,(在ftwrl之前会保证所有的事务都是提交状态)官方自带逻辑备份日记mysqldump,当mysqldump使用参数-single-transaction的时候.开启一个事务来保证视图的一致性,mvcc的支持所以这时候是可以更新的,但是这个方法只适用于所有表使用事务引擎的库,之所以不用read-only,是因为readon
2020-06-30 10:09:41
90
原创 索引
索引的作用提高数据查询效率常见索引模型哈希表、有序数组、搜索树哈希表键 - 值(key - value)。哈希思路把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置哈希冲突的处理办法链表哈希表适用场景只有等值查询的场景有序数组按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))有序数组查询效率高，更新效率低有序数组的适用场景：静态存储引擎。二叉搜索树每个节点的左儿子小于父节点，父节
2020-06-30 10:00:03
86
原创 数据库事务
事务的概念在一组操作中,要么全部成功要么全部失败事务级别mysql的事务隔离级别读未提交, 读已提交, 可重复读, 串行读未提交:你的事务中,已经更改了,但是还没提交,在我的事务中,我能读到你的改变读已提交:你的事务中,已经提交了,在我的事务中,才能读到你的改变可重复读:你的事务中,已经提交了,但是在我的事务中,读不到你改变后的内容串行:你的事务还没结束,我的事务别想开始事务和视图的关系读已提交:视图是在sql执行的时候生成的,所以都是已经提交的可重复读:视图是在事务开始时生成
2020-06-30 09:50:10
527
原创 一条sql的执行过程
简单来说,如果只是读操作的话,以上这幅图就够了,但是如果是写操作的话,就涉及到缓存和两段式提交了两种日志redo log当一条数据更新时,innordb(特有的)会将记录先写到redo-log里面,并更新到内存里(这时候已经算是完成了更新),在找个空闲的时间把记录保存到磁盘中.redo-log大小是固定的.,然后分成几份文件,连起来 ,从头写,写到结尾,再把开头一部分删掉,接着写write ps是当前记录的位置,check point是清除的位置,两者之间的就是空白可以写的位置,每次清..
2020-06-30 09:40:44
137
原创 基础面试题之运算符二
求下面输出的是什么 static { int x = 5; } static int x,y; public static void main(String[] args) { x --; myMthod(); System.out.println(x + y++ + x); } pri...
2020-03-02 10:18:15
245
原创 基础面试题之运算符
来看看这个面试题,也是挺有趣的 public static void main(String[] args) { int j = 0; for(int i= 0; i< 100 ; i++){ j = j++; } System.out.println(j); }先思考几秒钟,在做出答案答案是0...
2020-03-02 10:11:04
130
原创 sharding-jdbc之执行原理
執行原理基本概念邏輯表:水平拆分的數據表的總稱.比如訂單表拆為10張表order_9,order_8…,邏輯表就是order真實表:在分片的數據庫中正式存在的物理表,就是order_9,order_8數據節點:數據分片的最小物理單元,數據源名稱和數據表組成綁定表:分片規則一致的主表和子表,相關聯的兩張表,分片的字段和規則應該一致,不然連表查的時候就會有笛卡爾積的情況廣播表:所有分片中...
2020-01-17 17:59:04
314
原创 MongoDB之集群安全认证
集群安全認證只需要在主節點添加用戶,副本集會自動同步主節點添加管理員賬號 //切換到admin庫 use admin //創建系統超級用戶myroot密碼123456,設置角色root db.createUser({user:"myroot",pwd:"123456",roles:[{"role":"root","db":"admin"]}]}) 或 ...
2020-01-17 16:06:41
232
原创 MongoDB之单机安全认证
安全認證用戶和角色權限簡介為了安全,不讓所有人都能對mongodb操作,自然就需要建立權限步驟使用新的端口,默認的27017端口一點知道了ip,就能連接上設置mongodb的網絡環境,最好將mongodb部署到服務器內網,公司內部訪問使用vpn等開啟安全認證,認證要同時設置服務器之間的內部認證方式,要設置客戶端連接到集群的賬號密碼認證方式為了強制開啟用戶驗證,在啟動mongo...
2020-01-17 14:56:51
142
原创 MongoDB之分片
分片集群分片概念mongodb使用分片來支持具有非常大的數據集合高存儲量湊在哦的部署分片是將數據拆分,分散到不同機器的過程,也能叫做分區mongodb支持通過分片進行水平擴展組件說明分片(存儲):每個分片包含分片數據的自己,每個分片都可以部署副本集mongos(路由):mongos充當查詢路由器,在客戶端引用程序和分片集群之間提供接口config servers(調度的配置)...
2020-01-17 12:02:50
379
原创 MongoDB之副本集
副本集簡介就是一組維護相同數據集的mongod服務,類似主從集群作用冗餘和數據可用性:提供了多個數據副本,複製可提供容錯功能實現副本服務器做只讀服務器,實現讀寫分離,提高負載提高了系統可用性,有選舉策略,就算主節點掛掉了,也還有從節點MongoDB的複製在副本集中有多個數據承載節點(從節點)和可選的一個仲裁節點,有且僅有一個是主節點主節點接受所有寫操作,副本集只能有一個...
2020-01-17 11:17:50
160
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人