NIO4444

目录机器性能举例解决方法备库繁忙举例解决方法大事务举例解决方法机器性能举例备库用的机器性能较差解决方法改为主备同等机器备库繁忙举例通常备库会用于各种大数据的查询，导致大家都不重视查询效率。解决方法通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力大事务举例比如：一次性delete很多的数据。解决方法拆分为多个小事务...

双M结构生产上使用比较多的是双M结构（节点 A 和 B 之间总是互为主备关系）双 M 结构还有一个问题需要解决业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行 完这条更新语句后也会生成 binlog。（我建议你把参数 log_slave_updates 设置为 on， 表示备库执行 relay log 后生成 binlog）。 那么，如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来.

binlog 可以用来归档，也可以用来做主备同步备库设计备库设计成只读（readonly）模式有以下几个考虑： 1. 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作； 2. 防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；3. 可以用 readonly 状态，来判断节点的角色。 readonly设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限。主备流程s...

binlog存储是以statement或者row格式存储的redo log是以page页格式存储的。page格式，天生就是共有的，而row格式，只跟当前事务相关。

binlog 的写入逻辑事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到 binlog 文件中。参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘每个线程有自己 binlog cache，但是共用同一份 binlog 文件。图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化..

短连接风暴 正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再 重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。 MySQL 建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。max_connections 参数，用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个 值，系统就会拒绝接下来的连接请求，并报错提示“Too many c..

目录自增值的保存策略插入的值和当前自增值的大小关系批量申请自增 id 的策略自增值的保存策略MyISAM 引擎的自增值保存在数据文件中。 InnoDB 引擎的自增值，其实是保存在了内存里：MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第 一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个 表当前的自增值。 MySQL 8.0 版本后，才有 了“自增值持久化”的能力，也就是

lock in share mode 只锁覆盖索引for update主键索引上满足条件的行加上行锁

目录查询条件原因分析mysql> CREATE TABLE `table_a` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `b` (`b`)) ENGINE=InnoDB;表里面，有 100 万行数据，其中有 10 万行数据的 b 的值是’1234567890’。查询条件现在要查询如下SQL： select * from table_.

需求查询城市是“杭州”的所有人名字，并且按照姓名排序返回前 1000 个人的姓名、年龄CREATE TABLE `t` ( `id` int(11) NOT NULL, `city` varchar(16) NOT NULL, `name` varchar(16) NOT NULL, `age` int(11) NOT NULL, `addr` varchar(128) DEFAULT NULL, PRIMARY KEY (`id`), KEY `city` (`city..

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但 磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以 复用，而没有被使用的空间，看起来就像是“空洞”。...

目录3中方法每种方法分析3中方法第一种直接执行 delete from T limit 10000; 第二种在一个连接中循环执行 20 次 delete from T limit 500;第三种在 20 个连接中同时执行 delete from T limit 500。每种方法分析方案一事务相对较长，则占用锁的时间较长，会导致其他客户端等待资源时间较长。 方案二串行化执行，将相对长的事务分成多次相对...

给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大 表操作的时候，你肯定会特别小心，以免对线上服务造成影响。而实际上，即使是小表，操 作不慎也会出问题。...

一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库 还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。 二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开， 那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设 置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这 样会导致整个库长时间处于不可写状态，风险较高。 ...

连接器 第一步，你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建 立连接、获取权限、维持和管理连接。mysql -h$ip -P$port -u$user -p如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里 面的权限判断逻辑，都将依赖于此时读到的权限。一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改， 也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。 ...

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。 因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大 的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才 会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。 好在 MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置 成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。...

使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因 为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断 开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉 （OOM），从现象看就是 MySQL 异常重启了。...

tools：https://github.com/jeremycole/innodb_diagrams

开启收集数据库服务器性能参数SHOW VARIABLES LIKE 'performance_schema'查看表的内容SELECT * FROM `performance_schema`.`table_io_waits_summary_by_index_usage`

目录获得当前日期、时间当前时间戳时间戳转日期字符串获得当前日期、时间select NOW()select curdate() ;select curtime()当前时间戳时间戳转日期字符串

mysql5set global validate_password_policy=0;set global validate_password_length=1;mysql8set global validate_password.policy=0;set global validate_password.length=1;

原始数据表问题现象原因分析字符串字段传入数字时，并没有使用索引。全表对比数字时，数字做对比相等总结字符串字段传入字符串时，走索引，是字符串类型的对比字符串字段传入数字时，不走索引，全表扫描，是数字类型的对比。...

原始数据问题现象字符串数字原理分析（隐式转换）以数字开头的字符串，+为算术操作符arithmetic operator，当遇到非数字式，全部按0处理。 以数字开头的字符串，因为'a'+'b'的结果为0，c也会隐式转化为0，1在MySQL中可以理解为TRUE非数字开头，按0处理：现象解释1=1+‘d’，以数字开头的字符串总结如果字符串的第一个字符就是非数字的字符，那...

死锁原因容易发生死锁的几种情况如下:多个并发事务（2个或者以上）按照不同的顺序同时访问多张表比如一个程序锁定了A表，然后去申请锁定B表，另外一个程序先锁定的B表，需要同时修改A表InnoDB也支持显式锁，例如以下语句：避免死锁1）类似的业务逻辑以固定的顺序访问表和行。2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。 3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概 率。4）降低隔离级别，如果业务允许，将隔离级别调低也...

1.char（n）和varchar（n）中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候(UTF8)意味着可以插入m个中文，但是实际会占用m*3个字节。2.同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1<=n。(varchar中的另外1个字节记录字符串的长度)3.超过char和varchar的n设置后，字符串会被截断。4.char的上限为255字节，...

目录垂直拆分优点缺点水平拆分总结数据拆分前其实是要首先做准备工作的，然后才是开始数据拆分，我先讲拆分前需要做的事情：第一步：采用分布式缓存redis、memcached等降低对数据库的读操作。 第二步：如果缓存使用过后，数据库访问量还是非常大，可以考虑数据库读、写分离原则。 第三步：当我们使用读写分离、缓存后，数据库的压力还是很大的时候，这就需要使用到数据库拆分了。数据库拆分原则：就是指通过某种特定的条件，按照某个维度，将我们存放在同一个数据库中的数据分散存放...

聚集索引和非聚集索引聚集索引就是索引存放的物理顺序和数据列中的顺序一样（字典的拼音查找目录）。非聚集索引索引项顺序存储，但索引项对应的内容却是随机存储的（字典的笔画、部首查找目录）一般设置主键索引就为聚集索引。非聚集索引和聚集索引的区别在于：通过聚集索引可以一次查到需要查找的数据， 而通过非聚集索引第一次只能查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。聚集索引一张表只能有一个（因为主键的作用就是把表的数据格式转换成索引（平衡树）的格式放置。），而非聚..

目录不同引擎的锁表锁与行锁的区别INNODB的行级锁（2种）InnoDB锁的类型行锁的算法锁机制锁是用于管理不同事务对共享资源的并发访问不同引擎的锁InnoDB：表锁和行锁MyiSam：表锁表锁与行锁的区别表锁：对一整张表加锁，并发能力低下（即使有分读锁、写锁），一般在DDL处理时使用不利于并发，不会死锁行锁：通过给索引上的索引项加锁来实现的（执行的SQL要用到索引，InnoDB才使用行级锁，否则，InnoDB 将...

一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？InnoDB新增一条记录，不重启mysql id是18；重启MySQL ID是15。因为InnoDB表只把自增主键的最大ID记录到内存中，所以重启数据库或者对表OPTIMIZE操作，都会使最大ID丢失。 MylSAMID就是18。因为MylSAM表会把自增主键的最大ID记录到数据...

data_free选项代表数据碎片。针对MySQL的不同数据库存储引擎，在optimize使用清除碎片，回收闲置的数据库空间，把分散存储（fragmented）的数据和索引重新挪到一起(defragmentation)，对I/O速度有好处。optimize在对表进行操作的时候，会加锁，所以不宜经常在程序中调用。...

目录为什么要强调最左前缀原则那么什么时候才能用到呢?为什么要强调最左前缀原则以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的：mysql创建复合索引的规则是首先会对复合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。所以：第一个name字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用

目录什么是索引为什么使用索引磁盘IO与预读B 树b+树b+树性质一个表最多可有16个索引。最大索引长度是256个字节什么是索引索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构为什么使用索引索引的目的在于提高查询效率索引能极大的减少存储引擎需要扫描的数据量索引可以把随机IO变成顺序IO索引可以帮助我们在进行分组、排序等操作时，避免使用临时表索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B+树的形式保存。.

在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。B、如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。C、如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。D、其他情况下，应该控制临时表和表变量的使用。E、关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现， (1)主要考虑需要放在临...

目录MySQL数据库两个瓶颈MySQL优化方案架构层面SQL索引优化数据库表优化MySQL数据库两个瓶颈CPU和I/O的瓶颈MySQL优化方案Mysql的优化，大体可以分为三部分：架构层面、索引sql语句的优化，表的优化架构层面这一类调优包括读写分离、多从库负载均衡、水平和垂直分库分表等方面，一般需要的改动较大，但是频率没有SQL调优高，而且一般需要DBA来配合参与。那么什么时候需要做这些事情？我们可以通过内部监控报警系统（比如Zabbix）.

目录主键索引唯一索引组合索引普通索引全文索引 主键索引 是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引 唯一索引 与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。 组合索引 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。 普通索引 是最基本的索引，它没有任何限制。 ...

show full processlist; 可以看到正在执行的语句Explain 分析SQL效率show variables like '%slowquerylog%' 必要的时候查看慢日志

目录表锁状态行级锁状态表锁状态-- 表级锁的争用状态变量show status like 'table%';行级锁状态-- 行级锁争用状态变量show status like 'innodb_row_lock%';

目录数据库表使用方法idselect_typetabletypepossible_keysKeykey_lenrefrowsExtra总结数据库表使用方法explain select * from student1 where pid like 'i_8' ;Select_type：指定所使用select查询类型，simple表示简单的select，不使用union或子查询。其他可能的取值有：primary、uni..

目录基础数据库表失效情况基础Explain查看执行SQL计划数据库表失效情况1、避免在where子句中使用or来连接条件,因为引擎会放弃索引而产生全表扫描，即使两个都有索引2、新建的表还没来得及生成统计信息，分析一下就好了3、mysql基于cost的成本分析，访问的表过小，使用全表扫描的消耗小于使用索引。4、在索引列上使用函数。如SUBSTR,DECODE,INSTR等，对索引列进行运算.需要建立函数索引就可以解决了。5、单独的...

目录MyISAM索引实现InnoDB索引实现最佳实践MyISAM索引实现索引文件和数据文件分开，所以非聚集。主键索引=索引+数据地址辅助索引=索引+数据地址demoselect count(*) from table 不需要扫表计算。MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：这里设表一共有三列，假设我们以Col1为主键，图是一个MyISAM表的主索引（Primary ke