weixin_42672777的博客

innodb和 memory的区别InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。InnoDB 支持变

内部临时表和sort buffer,join buffer一样，都用来存放语句执行过程中的中间数据，辅助语句的执行。使用用法 using temporary。使用场景数据一边查询，一边直接得到结果，不需要额外内存。比如：group by 需要计算。join_buffer 是无序数组（单字段，可以重复），sort_buffer 有序数组，内部临时表是二维结构用到二维表的特性需要用到内部临时表，比如 distinct ，group by优化group by 字段加索引

临时表内存表和临时表的区别内存表:是用memory引擎的表,这表的数据都放在内存中,重启时会清空数据,但是表结构还在临时表:可以使用各种引擎,如果使用innodb或myisam的话,数据是存到磁盘的,临时表的特性不同session的临时表是可以重名的,有多个session同时创建临时表,并不会因为名字一样而冲突,因为是session级别的(线程独有)不需要担心数据问题,当断开连接或异常重启的时候,临时表会自动被回收临时表的应用分库分表的情况下,用proxy,然后如果使用到每个分区都

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,就是使用到了

如何使用join如果能用到被驱动表的索引,则使用join比拆成多个单表执行的sql来的快使用join的话,让小表做驱动表在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。如果使用Block Nasted-Loop join算法,有可能导致join buffer不够大,导致被驱动表做多次全表扫描,所以在explain的时候Extra字段里面出现Block Nasted-Loop的话,就尽

kill语句kill不掉的第一种情况,innodb_thread_concurrency的上限了用户在执行kill的时候,处理kill的线程做了两件事把被kill的实例 的运行状态改成 THD::KILL_QUERY(将变量 killed 赋值为 THD::KILL_QUERY)；给 被kill的实例 的执行线程发一个信号。一个语句执行过程中有多处“埋点”，在这些“埋点”的地方判断线程状态，如果发现线程状态是 THD::KILL_QUERY，才开始进入语句终止逻辑；如果处于等待状态，必须

误删数据之后怎么办误删行使用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记录修改前和修改后的语句,两者调换即可建议在备库上执行回滚操作

如何判断主库的健康select 1:说明进程还在,但是不能说明主库没问题- 有可能被占满了,设置innodb_thread_concurrency控制innodb的并发线程上限,并发连接如果线程的查询进入等待,则并发连接的计数会-1,所以使用select 1可能线程已经超过innodb_thread_concurrency,全被阻塞住了,但是select 1可用查表判断:mysql> select * from mysql.health_check; 空间满了,binlog在写数据写不

读写分离基本的读写分离有两种模式一种是client主动做负载均衡一种是在client和mysql之间有一个proxy来做代理层,两者的区别客户端直连的方式,查询的性能好一点点,架构简单,排查问题容易些,但是数据迁移,主备切换等,都会被感知且调整数据库连接信息带proxy的方式,就后端只需要注意在开发逻辑上,连接维护,后端信息维护等,都直接proxy做,但是对维护团队要求比较高有时候还是会从从库那边读到主库过期的数据,解决方案有以下几种方案强制走主库方案将查询分类,有的强一致的就

主库产生问题之后怎么办,切换备库后,要指定其他的从库的主库为新上来的主库(之前的备库)需要执行一条命令,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.

主备并发同步版本5.5并发复制策略(个人策略)按表分发:每个worker都维护一个hashtable,表示自己正在执行的事务中涉及到哪些表,如果分发的事务中的表,和在worker中大于1个worker的表有事务冲突(有相同的表在执行),就等待,等到只有一个worker有冲突的时候,就把这个事务丢到这个worker如果热点表被多次操作,这样子就只有一个worker执行,相当于单线程按行分发binlog 必须是row,所以必须维护一个hashtable,里面的key为库名+表名+索引a的名称

同步延迟t1为主库a完成事务,写完binlog的时间t2为备库b完成从a传输过来binlog的时间t3为备库执行完binlog的时间t3-t1就是备库和主库之间的延迟时间,在备库执行命令show slave status,会显示seconds_behind_master,就是延迟几秒主备延迟的原因备库所在的服务器比主库的服务器性能来的差,但是他们写入的文件是一样多的,所以尽量保证主备性能一致备库压力大,有些读操作在备库上执行,没有优化,直接大批量读取数据,导致io飙升,从而主备延迟,解决

数据库主备一致都是用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的三个格式

提高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

binlog的写入机制事务执行过程先把日志写到binlog cache,事务提交时,从binlog cache写入到binlog文件里事务无法被拆分,无论事务多大,都要保证一次写入每个线程都有一个系统分配的binlog cache的大小,由binlog_cache_size控制,超过则写到磁盘中多个binlog cache公用一个binlog文件,从binlog cache到binlog文件有两个步骤write:把日志写到page cache,没有到磁盘,所以速度比较快fsync:把数

业务遇到问题解决短连接(连接数达到设置的max_connections上限)show processlist查看空闲的连接,直接kill查看infomation_schema中的innodb_trx表和show processlist,查询事务内空闲的连接,kill提高max_connections的值减少连接过程的损耗先确认是否被连接行为打挂了,是的话重启的时候加上-skip-grant-tables命令跳过权限校验查询慢的问题索引设计问题紧急上线索引的时候,可以先备库b设置s

间隙锁如果在查询的后面加上for update表示查询为当前读,而不是读快照,有可能会导致幻读的问题问题重现:在一个事务a中锁住了name为5的数据,然后再事务a期间,另外一个事务b把另外一条数据的name修改为5,此时,事务a再读一次name为5的数据,数据多了一条,导致一个事务里面,前后读到的数据是不一致的,(当前读才会有幻读的问题)只要在这事务里面修改或新增为当前被锁的数据,就会产生幻读为了解决幻读,引入了间隙锁间隙锁锁的是数据之间的间隙,也就是不能新增或删除间隙锁和行锁合成next-

如果查询一个简单的语句,却长时间不回的话,可能出现的问题mdl锁:使用show processlist,可以在state看得到是否被锁住了设置performance_schema=on(比设置off会有10%的损耗),通过查询sys.schema_table_lock_waits,然后kill掉即可等flush有可能存在的问题就是有一个表的sleep语句,然后刚好遇到了flush语句(被sleep阻塞了),flush阻塞了后面的select语句,使用show processlist查看

索引用不上的原因在查询条件那边用函数,或在=前面计算会导致用不上索引,查询条件做类型转化也用不上索引(因为类型转化用上了数据库中的函数CAST(column AS signed int))查询条件中字符集不同也会导致用不上索引,CONVERT(traideid USING utf8mb4)//整改方案是把字符集转换写在=的后面,而不用默认的字符集转换...

按照效率排序的话count(字段)<count(主键id)<count(1)≈count(*)建议使用count(*)count(主键id),先取出到service层,判断不为null,在+1count(1),判断这行有值,取1到service,判断不为null,+1count(字段)设置为不能为null:就直接按行取值,然后按行累加设置可为null:取值到service层,判断不为null,再+1count(*)如果是myISAM,没有条件的话,就直接从文件.

如何设置合理的刷页速度脏页:redo.log的页上的数据和磁盘的数据还没同步干净页:redo.log页上的数据已经同步到磁盘上了redo.log写满了,要flush脏页到磁盘的时候,这时候所有的更新和写入操作都暂停,等flush操作写完之后才能继续执行内存不够了,要先写脏页到磁盘中,会先淘汰最先的页,如果是干净页就直接复用,如果是脏页,就先同步到磁盘,之后再变成干净页了,再复用影响性能的操作:一个查询需要淘汰的脏页太多了,导致查询响应时间长日志写满了,更新都堵住了,写能力为0innod

如何正确的删除数据和空间数据删除了,但是表占的空间却没变?有一个参数是innodb_file_per_table,在版本5.6之后就默认为on,off的时候表数据在共享表空间,即使表删除了,空间也不会回收,on的话,是独立占据一个文件需要在删除之后,再alter table重建表才能让空间变小造成的其他原因索引空洞:在索引删除了之后,这个索引留下的空间是能被复用的,没有被回收,如果没能被复用,就造成了索引空洞,空间浪费,索引新增的时候也会造成索引空洞解决:重建表在5.

字符串索引的选择前缀索引通过指定适当的索引长度,又不损耗太多查询次数,又能减少空间的存储如何判断索引的长度,可以用区分度来指定,区分度越高越好,基本大于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但是前缀索引就用不上覆盖索引的好处了,倒序索引有些字符串长度长,但是前面一部分的字符区分度又

关于优化器的使用索引有一个基数的概念,也就是该索引上不同数值的数越多,该索引的区分度越大,基数越大.这个索引一下子能区分的数据就越多.优化器查询sql执行所要扫描的索引基数,用的就是随机取样法先取索引树上N页的数据,判断上面有多少基数时,再乘以多少页,就是基数当索引树上的数据更新量达到1/M的时候,会重新触发一次统计基数,存储索引统计的方式可以通过innodb_stats_persistent,设置为on,表示统计信息会持久化存储,N是20,M是10设置为off,表示统计信息会保存在内存

排序底层做了啥MySQL会为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size如果排序的数据量小于sort_buffer_size，排序将会在内存中完成如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序在使用外部排序时，MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件mysql会通过遍历索引将满足条件的数据读取到sort_buffer，并且按照排序字段进行快

普通索引和唯一性索引的选择两者之间读操作的区别普通索引:查询操作时,在根据查询条件在索引树上寻找相同的值后,会再往下找一个值判断是否相等,(查,判断)唯一性索引:直接查,查到之后直接返回,不需要判断change buffer:当更新数据时,如果数据也在内存中就直接更新,如果数据页不在内存中,在不影响数据一次性原则,就把相关操作操作到change buffer中,等到下次有访问这个数据页的时候,再把这个数据页读到内存中执行change buffer的操作(叫merge,后台会定期更新merge,数据

事务的启动方法:当启动star transaction时 并没有马上开始事务,(一致性读的情况),要到第一个执行innordb的语句才会开启事务如果是马上开启事务,star transaction with consistent snapshot.视图的两个概念:一个是view,是查询语句定义的虚拟表,就是在执行查询结果时生成结果,create view…,查询和普通语句一致innodb在mvcc中用到的一致性视图,consistent read view,支持读提交和可重复读的隔离级别实现

全局锁做全局逻辑备份:整个库只能只读无法更新和新增删除命令是Flush tables with read lock一致性读需要引擎支持隔离级别,myisam只能使用ftwrl,(在ftwrl之前会保证所有的事务都是提交状态)官方自带逻辑备份日记mysqldump,当mysqldump使用参数-single-transaction的时候.开启一个事务来保证视图的一致性,mvcc的支持所以这时候是可以更新的,但是这个方法只适用于所有表使用事务引擎的库,之所以不用read-only,是因为readon

索引的作用提高数据查询效率常见索引模型哈希表、有序数组、搜索树哈希表键 - 值(key - value)。哈希思路把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置哈希冲突的处理办法链表哈希表适用场景只有等值查询的场景有序数组按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))有序数组查询效率高，更新效率低有序数组的适用场景：静态存储引擎。二叉搜索树每个节点的左儿子小于父节点，父节

事务的概念在一组操作中,要么全部成功要么全部失败事务级别mysql的事务隔离级别读未提交, 读已提交, 可重复读, 串行读未提交:你的事务中,已经更改了,但是还没提交,在我的事务中,我能读到你的改变读已提交:你的事务中,已经提交了,在我的事务中,才能读到你的改变可重复读:你的事务中,已经提交了,但是在我的事务中,读不到你改变后的内容串行:你的事务还没结束,我的事务别想开始事务和视图的关系读已提交:视图是在sql执行的时候生成的,所以都是已经提交的可重复读:视图是在事务开始时生成

简单来说,如果只是读操作的话,以上这幅图就够了,但是如果是写操作的话,就涉及到缓存和两段式提交了两种日志redo log当一条数据更新时,innordb(特有的)会将记录先写到redo-log里面,并更新到内存里(这时候已经算是完成了更新),在找个空闲的时间把记录保存到磁盘中.redo-log大小是固定的.,然后分成几份文件,连起来 ,从头写,写到结尾,再把开头一部分删掉,接着写write ps是当前记录的位置,check point是清除的位置,两者之间的就是空白可以写的位置,每次清..

mycatmycat的优势:实现后端数据库的读写分离和负载均衡支持写DB高可用,作用数据库进行垂直切分,进行水平切分控制数据库连接的数量mycat 基本元素逻辑库:即后端切分后的数据库对应后端多个物理数据库逻辑库中不保存数据逻辑表对应用来说相当于mysql的数据表逻辑表可对应后端多个物理数据库的表逻辑表中不保存数据逻辑表的分类分片...

mysql高级特性分区表概念:分区表是一个独立的逻辑表,但是底层是多个物理子表组成,实现分区的代码实际上是对一组底层表的句柄对象转化为对存储引擎的接口调用,分区对sql层来说是一个完全封装底层实现的黑盒子.对应用是透明的,但是从底层文件系统来看是很容易发现的,每一个分区表都有一个使用#分隔命名的表文件实现方式:对底层表的封装,索引也是按照分区的子表定义的.没有全局索引.目的:江数据按照一...

查询性能优化为什么查询会慢一个查询从客户端,到服务器,然后进行解析,生成执行计划,执行,并返回结果给客户端,最重要就是执行,然而查询需要在不同的地方花费时间,包括网络,CPU计算,生成统计信息和执行计划,锁等待等操作,根据存储引擎不同,可能还会产生大量的上下文切换和系统调用,所以,了解这些信息,才能知道如何优化查询慢查询基础:优化数据访问是否请求了不需要的数据查询了不必要的数据,比...

高性能索引1.索引的分类1.1 BTREE:基本的索引B-Tree意味着所有值都是按照顺序来存储,而且每个叶子页到根的距离都是相同,检索:不需要全文检索,二十从索引的根节点开始搜索,根据根节点存储的子节点的指针往下走,知道找到数据或者数据不存在,指针定义了子节点页中值的上限和下限(所以是按照顺序),叶子节点存的是指向数据的地址.存储:索引列是顺序组织存储,适合查找范围数据,索引对多个值...

schema和数据类型优化选择优化的数据类型小的更好:使用可以正确存储数据的最小数据类型越简单越好:简单数据类型的操作需要更是的cpu周期避免null:除非真的需要存储null,不然设置为not null,因为查询时,可为null的列使得索引和值比较,索引统计更复杂(timestamp只使用datetime一半的存储空间,但是允许的时间范围比较小)整数类型整数类型就是数字,分为...

mysql架构逻辑架构第一层大部分是连接处理,授权认证,安全等第二层则是核心服务:查询解析,分析,优化,缓存和所有内置函数,所有跨存储引擎的功能都在这一层:存过过程,触发器,视图等第三层包含了存储引擎,不同存储引擎之间不会相互通信,简单的响应上层服务器的请求而已连接管理和安全性简单的在服务器进程里面拥有一个线程,连接查询只会在这个单独的线程里面执行,不需要为每个新建的连接创建或销...