MySQL
文章平均质量分 70
wanger61
这个作者很懒,什么都没留下…
展开
-
MySQL表结构设计规范
一、表设计1. 命名规范表名由小写英文字母和下划线组成表必须填写描述信息表名中的英文单词应该使用单数形式临时表以 tmp 为前缀,以日期为后缀备份表以 bak 为前缀,以日期为后缀使用hash、md5 进行散表,表名后缀使用16进制2. 设计规范必须定义主键,一般默认为id,整型自增主键不允许修改,一般采用业务无关字段表必须包含 gmt_create 和 gmt_modified 字段记录创建时间和修改时间禁止使用外键慎用触发器和存储过程。将业务逻辑放到应用层更合适单条记录原创 2023-09-08 16:40:14 · 388 阅读 · 0 评论 -
MySQL知识点总结
一. 通用模块1. MySQL体系结构连接器:管理连接,权限验证(连接分为长连接和短连接)解析器:词法分析,语法分析查询缓存:解析完后若命中则直接返回结果(由于对一个表的更新,这个表上所有的查询缓存都会被清空,导致命中率低,MySQL 8.0 后废除)优化器:执行计划生成,索引选择存储引擎:真正负责数据的存取(底层物理结构),是基于表而不是基于库2. ¥MyISAM和InnoDB存储引擎是否支持行级锁 : MyISAM 只有表级锁(table-level locking),而InnoD原创 2020-12-25 20:30:30 · 788 阅读 · 0 评论 -
(十六)分区表,自增id用完
35 分区表对一个表进行n个分区后,对于引擎层来说,这是 n 个表;对于 Server 层来说,这是 1 个表。分区表的特点MySQL 在第一次打开分区表的时候,需要访问所有的分区;在 server 层,认为这是同一张表,因此所有分区共用同一个 MDL 锁;(因此分区表,在做 DDL 的时候,影响会更大。)在引擎层,认为这是不同的表,因此 MDL 锁之后的执行过程,会根据分区表规则,只访问必要的分区。分区表的应用场景分区表的一个显而易见的优势是对业务透明,相对于用户分表来说,使用原创 2020-08-06 20:08:59 · 367 阅读 · 0 评论 -
(十五)特殊insert语句加锁规则,快速复制一张表
33 特殊insert语句加锁规则insert … selectinsert into t2(c,d) select c,d from t;可重复读隔离级别下,binlog_format=statement 时执行该语句,会对表 t 的所有行和间隙加锁这是为了保证日志和数据的一致性:否则在复制的过程中,如果一个线程向t中插入了数据,可能会因为binlog记录顺序不同导致复制的行数不同对目标表t2不会锁全表,而是只锁住需要访问的资源。insert into t(c,d) (select c原创 2020-08-06 19:20:28 · 301 阅读 · 0 评论 -
(十四)临时表,内存表,自增主键
临时表临时表的特点:建表语法是 create temporary table …。一个临时表只能被创建它的 session 访问,对其他线程不可见。所以,图中 session A 创建的临时表 t,对于 session B 就是不可见的。临时表可以与普通表同名。session A 内有同名的临时表和普通表的时候,show create 语句,以及增删改查语句访问的是临时表。show tables 命令不显示临时表。由于临时表只能被创建它的 session 访问,所以在这个 session原创 2020-08-05 20:35:12 · 249 阅读 · 0 评论 -
(十三)Join原理,Join优化
29 JoinIndex Nested-Loop Join联结两个表时,如果第二个表上有联结键索引,那么就会根据第一个表中查出的联结键值搜索索引,然后根据索引得到的主键id去第二张表找在这种情况下:使用 join 语句,性能比强行拆成多个单表执行 SQL 语句的性能要好;如果使用 join 语句的话,需要让小表做驱动表。Simple Nested-Loop Join联结两个表时,如果第二个表上没有联结键索引,那么就会根据第一个表中查出的联结键值,去第二张表上作全表扫描Block原创 2020-08-05 20:18:15 · 248 阅读 · 1 评论 -
(十二)数据库误删,Kill不掉的语句,查询数据与内存
26 数据库误删预防误删账号分离。这样做的目的是,避免写错命令只给业务开发同学 DML 权限,而不给 truncate/drop 权限。而如果业务开发人员有 DDL 需求的话,也可以通过开发管理系统得到支持。制定操作规范,避免写错要删除的表名把 sql_safe_updates 参数设置为 on。这样一来,如果忘记在 delete 或者 update 语句中写 where 条件,或者 where 条件里面没有包含索引字段的话,这条语句的执行就会报错。代码上线前,必须经过 SQL 审计原创 2020-08-05 19:11:35 · 212 阅读 · 0 评论 -
关于next-key范围查询加锁规则的实验
本实验基于MySQL8.0.18版本在《MySQL45讲》定义了如下加锁规则:原则 1:加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。原则 2:查找过程中访问到的对象才会加锁。优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。初始化原创 2020-08-05 17:28:44 · 1299 阅读 · 5 评论 -
(十一)读写分离,过期读,判断数据库出现问题
读写分离由于从库延迟是不能100%避免的,因此客户端执行完一个更新事务后马上发起查询,如果查询选择的是从库的话,就有可能读到刚刚的事务更新之前的状态,即过期读。过期读的解决方案:强制走主库方案:将查询请求分为这么两类:1)对于必须要拿到最新结果的请求,强制将其发到主库上。2)对于可以读到旧数据的请求,才将其发到从库上。该方案是用的最多的,但这个方案最大的问题在于,有时候你会碰到“所有查询都不能是过期读”的需求,比如一些金融类的业务。这样的话,你就要放弃读写分离,所有读写压力都在主库,等同于原创 2020-08-04 20:53:04 · 186 阅读 · 0 评论 -
(十)主从复制,主备延迟,并行复制
21 主从复制binlog的三种格式statement: binlog 里面记录的就是 SQL 语句的原文,该格式下主从库有可能在执行同一条SQL时采用了不同的执行逻辑导致数据不一致row: binlog 里面记录了真正执行的行的主键 id和操作内容mixed:由于row占用空间太大,mixed 格式,MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致,如果有可能,就用 row 格式,否则就用 statement 格式。因此,如果你的线上 MySQL 设置的 binlog 格式原创 2020-08-04 20:02:50 · 454 阅读 · 0 评论 -
(九)临时提升性能,数据可靠性
19 临时提升性能的方法短连接风暴如果使用的是短连接,在业务高峰期的时候,就可能出现连接数突然暴涨的情况解决方案:先处理掉那些占着连接但是不工作的线程,并且应该优先断开事务外空闲的连接步骤:1.先用show processlist找到处于Sleep状态的连接2.在information_schema 库的 innodb_trx 表中找到事务内的连接3.用kill connection + id命令来断开事务外的连接注意:一个客户端处于 sleep 状态时,它的连接被服务端主动断开原创 2020-08-04 18:28:02 · 1554 阅读 · 0 评论 -
(八)索引失效,间隙锁,加锁规则
16 一些索引失效情况条件字段函数操作隐式类型转换如 select * from tradelog where tradeid=110717;如果tredeid是字符串类型,那么在比较时会隐性地将字符串转为数字,也就不会走索引来隐式字符编码转换当两个表的字符集不同,在关联时作字符集转换实际也是一个隐性类型转换,此时也不会走索引17 间隙锁为了避免幻读带来的数据不一致问题,当执行 select * from t where d=5 for update 的时候,就不止是给数据库中已有的原创 2020-08-03 20:41:38 · 590 阅读 · 0 评论 -
(七)排序原理
14 排序全字段排序:包括在内存中排序和利用磁盘临时文件排序可以用下面的方法,来确定一个排序语句是否使用了临时文件。/* 打开optimizer_trace,只对本线程有效 */SET optimizer_trace='enabled=on'; /* @a保存Innodb_rows_read的初始值 */select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'I原创 2020-08-02 21:04:58 · 185 阅读 · 0 评论 -
(六)重建表与count(*)原理
12 重建表数据删除流程delete 命令其实只是把记录的位置,或者数据页标记为了“可复用”,但磁盘文件的大小是不会变的。也就是说,通过 delete 命令是不能回收表空间的。这些可以复用,而没有被使用的空间,看起来就像是“空洞”。不止是删除数据会造成空洞,插入数据也会。经过大量增删改的表,都是可能是存在空洞的。所以,如果能够把这些空洞去掉,就能达到收缩表空间的目的。而重建表,就可以达到这样的目的。重建表可以使用 alter table A engine=InnoDB 命令来重建表:其本原创 2020-08-02 20:11:42 · 241 阅读 · 0 评论 -
(五)字符串建索引,SQL 时快时慢
10 给字符串建索引直接创建完整索引,这样可能比较占用空间;创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引;倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题;创建 hash 字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,都不支持范围扫描。11 SQL 语句执行时快时慢InnoDB 在处理更新语句的时候,包括写入内存和写redo log磁盘操作。当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。内存数据写入到磁盘后原创 2020-08-02 19:17:52 · 262 阅读 · 0 评论 -
(四)普通索引和唯一索引,选错索引
08 普通索引和唯一索引change buffer当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InnoDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。将 change buffer 中的操作应用到原数据页,得到最新结果的原创 2020-08-02 18:40:46 · 95 阅读 · 0 评论 -
(三)全局锁,表锁,行锁
05 全局锁顾名思义,全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。以前有一种做法,是通过 FTWRL 确保不会有其他线程对数据库做原创 2020-08-01 20:57:49 · 246 阅读 · 0 评论 -
(二)事务隔离,索引
03 事务隔离事务隔离的实现在 MySQL 中,实际上每条记录在更新的时候都会同时记录一条回滚操作(undo log)。记录上的最新值,通过回滚操作,都可以得到前一个状态的值。假设一个值从 1 被按顺序改成了 2、3、4,在回滚日志里面就会有类似下面的记录。当前值是 4,但是在查询这条记录的时候,不同时刻启动的事务会有不同的 read-view。如图中看到的,同一条记录在系统中可以存在多个版本,就是数据库的多版本并发控制(MVCC)。对于 read-view A,要得到 1,就必须将当前值依次原创 2020-08-01 20:27:43 · 125 阅读 · 0 评论 -
(一)基础架构与日志系统
01 基础架构连接器连接完成后,如果没有后续的动作,连接就处于空闲状态,可以在 show processlist 命令中查看状态。客户端如果太长时间没动静,连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的,默认值是 8 小时。数据库里面,长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个,应尽量使用长连接。但是使用长连接后,有些时候 MySQL 占用内存涨得特别快,这是因为 MyS原创 2020-08-01 19:18:55 · 183 阅读 · 0 评论