MySQL进阶
MySQL进阶
衣舞晨风
不预测,只应对
展开
-
索引设计的 10 个规范
不要陷入设置单表行数、列数限制的固有印象,其他关系型数据库没有行数、列数限制,MySQL 也没有,大表的缺点不是性能,而是后续的 DDL 管理问题,随着 MySQL 8.0 快速加列功能的上线,大表 DDL 问题基本已解决。MySQL 是索引组织表,表中的数据以 B+ 树索引结构,根据主键逻辑排序,由于 B+ 树索引的特点是树的高度为 3~4 层,所以从数十亿的记录中,通过主键查询一条记录只需要 3、4 次 I/O,当前到 SSD 存储设备设置每秒至少能完成 10000 次的 I/O 查询,不要担心通过.原创 2021-12-18 16:48:57 · 795 阅读 · 0 评论 -
Nginx 代理MySQL
docker-compose.ymlversion: '2.0'services: nginx: restart: always image: nginx:1.13.9-alpine ports: - 63307:63306 volumes: - ./nginx.conf:/etc/nginx/nginx.confnginx配置文件user nginx;worker_processes 1;error_log /var/lo原创 2022-04-28 17:49:59 · 8498 阅读 · 0 评论 -
MySQL 可重复读隔离级别与幻读
在MySQL可重复读的隔离级别下,能很大程度上避免幻读,而不能完全避免。场景复现环境信息:MySQL版本:5.7.23-log隔离级别:REPEATABLE-READ测试数据:SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for app_record_lock_test-- ----------------------------DR.原创 2022-03-16 17:40:25 · 2600 阅读 · 0 评论 -
Nginx 代理mysql
docker-compose.ymlversion: '2.0'services: nginx: restart: always image: registry.haier.net/library/nginx:1.13.9-alpine ports: - 63306:63306 volumes: - ./nginx.conf:/etc/nginx/nginx.confnginx.confuser nginx;worker_proce原创 2022-02-28 17:11:44 · 368 阅读 · 0 评论 -
SQL UNION vs OR 性能
本文整理自:stackoverflow翻译自Bill Karwin回答:要么你读的那篇文章用了一个不好的例子,要么你误解了他们的观点。select username from users where company = 'bbc' or company = 'itv';等价于:select username from users where company IN ('bbc', '...原创 2020-04-12 10:31:18 · 1761 阅读 · 1 评论 -
查看MySQL InnoDB 表索引的高度
在看《MySQL技术内幕:InnoDB存储引擎》B+树索引章节中看到这么一句话:但是B+索引在数据库中有一个特点就是高扇出性,因此在数据库中,B+树的高度一般都在2-4层,也就是说查找某一键值的行记录时最多只需要2-4次IO。因为当前一般的机械磁盘每秒至少可以做100次IO,2-4次的IO意味着查询时间只需要0.02-0.04秒。那么,当一个表很大的时候,索引还是是2-4层吗?那么这是遍历...原创 2019-02-18 21:10:38 · 3870 阅读 · 2 评论 -
MySQL技术内幕 InnoDB存储引擎:事务
一、认识事务InnoDB储存引擎中的事务完全符合ACID的特性。ACID是以下4个词的缩写:原子性(Atomicity):一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚。一致性(consistency):数据库总是从一个一致性的状态转换到另一个一致性的状态。(其实原子性和隔离性间接的保证了一致性)隔离性(isolation):通常来说...原创 2018-10-19 07:49:19 · 542 阅读 · 1 评论 -
MySQL技术内幕 InnoDB存储引擎:阻塞、死锁、锁升级
1、堵塞因为不同锁之间的兼容性关系,在有些时刻一个事务中的锁需要等待另外一个事务中的锁释放它所占用的资源,这就是堵塞。参数innodb_lock_wait_timeout用来控制等待的时间,默认50秒,是可以动态设置的。参数innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作。默认是OFF。(静态参数,无法是mysql运行时修改)在默...原创 2018-10-18 06:53:18 · 906 阅读 · 1 评论 -
MySQL技术内幕 InnoDB存储引擎:锁问题(脏读、不可重复读)
1、脏读在理解脏读(Dirty Read)之前,需要理解脏数据的概念。但是脏数据和之前所介绍的脏页完全是两种不同的概念。脏页指的是在缓冲池中已经被修改的页,但是还没有刷新到磁盘中,即数据库实例内存中的页和磁盘中的页的数据是不一致的,当然在刷新到磁盘之前,日志都已经被写人到了重做日志文件中。而所谓脏数据是指事务对缓冲池中行记录的修改,并且还没有被提交(commit)。对于脏页的读取,是非常正常的...原创 2018-10-18 06:30:36 · 947 阅读 · 2 评论 -
MySQL技术内幕 InnoDB存储引擎:外键与锁
外键主要用于引用完整性的约束检查。在InnoDB存储引擎中,对于一个外键列,如果没有显示地对这个列加索引,InnoDB存储引擎会自动对其加一个索引,因为这样可以避免表锁。 这比Oracle数据库做得好,Oracle数据库不会自动添加索引,用户必须自己手动添加,这也导致了Oracle数据库中可能产生死锁。对于外键值的插入或更新,首先需要检查父表中的记录,既SELECT父表。但是对于父表的SELEC...原创 2018-10-17 08:23:18 · 502 阅读 · 0 评论 -
MySQL技术内幕 InnoDB存储引擎:行锁的3种算法
1、lock与latch在数据库中,lock与latch都可以成为锁,但两者有截然不同的含义latch 一般称为闩锁(轻量级的锁) 因为其要求锁定的时间非常短,若持续时间长,则应用性能非常差,在InnoDB存储引擎中,latch有可以分为mutex(互斥锁)和rwlock(读写锁)其目的用来保证并发线程操作临界资源的正确性,并且没有死锁检测的机制。lock的对象是事务,用来锁定的是数据库中的...原创 2018-10-17 08:17:32 · 2265 阅读 · 0 评论 -
MySQL技术内幕 InnoDB存储引擎:B+树索引的使用
1、联合索引MySQL允许对表上的多个列进行索引,联合索引的创建方法与单个索引创建的方法一样,不同之处仅在于有多个索引列。CREATE TABLE t( a INT, b INT,PRIMARY KEY(a),KEY idx_a_b(a, b))ENGINE=InnoDB多个键值的B+树对于查询SELECT*FROM TABLE WHERE a=xxx and b=xxx,显然...原创 2018-10-16 08:06:10 · 926 阅读 · 0 评论 -
MySQL技术内幕 InnoDB存储引擎:Cardinality
并不是所有在查询条件中出现的列都需要添加索引,对于什么时候添加B+树索引,一般的经验是,在访问表中很少一部分行是使用B+树索引才有意义。查看索引是否是高选择性的,可以通过SHOW INDEX语句中的Cardinality列来观察。Cardinality是一个估计值,在实际中,Cardinality/n_rows_in_table应尽可能接近1,如果非常小,那么需要考虑是否还要建这个索引。索引的更...原创 2018-10-15 19:55:29 · 441 阅读 · 0 评论 -
MySQL技术内幕 InnoDB存储引擎:B+树索引
B+ 树索引并不能找到一个给定键值的具体行。 B+ 树索引能找到的只是被查找数据所在的页。 然后数据库通过把页读入到内存, 再在内存中进行查找, 最后得到要查找的数据。平衡二叉树平衡二叉树的定义如下:首先符合二叉查找树的定义,其次必须满足任何节点的两个字数的高度最大差为1。最好的想能需要建立一颗最优二叉树,但是最优二叉树的建立和维护需要大量的操作,因此,用户一般只需要建立一颗平衡二叉树即...原创 2018-10-15 08:56:40 · 881 阅读 · 0 评论 -
MySQL技术内幕 InnoDB存储引擎:分区表
一、MySQL分区表介绍分区是一种表的设计模式,正确的分区可以极大地提升数据库的查询效率,完成更高质量的SQL编程。但是如果错误地使用分区,那么分区可能带来毁灭性的的结果。分区功能并不是在存储引擎层完成的,因此不只有InnoDB存储引擎支持分区,常见的存储引擎MyISAM、NDB等都支持分区。但是并不是所有的存储引擎都支持,如CSV、FEDORATED、MERGE等就不支持分区。在使用此分区功...原创 2018-10-13 10:20:17 · 1094 阅读 · 0 评论 -
MySQL技术内幕 InnoDB存储引擎:一致性锁定读
在前一小节中讲到,在默认配置下,即事务的隔离级别为 REPEATABLE READ 模式下, InnoDB 存储引擎的 SELECT 操作使用一致性非锁定读。但是在某些情况下,用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句,即使是对于SELECT的只读操作。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读(locking read)操作:S...原创 2018-10-11 21:22:08 · 701 阅读 · 0 评论 -
MySQL技术内幕 InnoDB存储引擎:一致性非锁定读
一致性的非锁定行读(consistent nonlocking read)是指InnoDB存储引擎通过行多版本控制(multi versioning)的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE、UPDATE操作,这是读取操作不会因此而会等待行上锁的释放,相反,InnoDB会去读取行的一个快照数据。下图直观展示了一致性的非锁定行读:之所以称其为非锁定读,因为不需...原创 2018-10-11 21:13:47 · 549 阅读 · 2 评论