Mysql
库昊天
这个作者很懒,什么都没留下…
展开
-
SQL耗时统计方法
SQL耗时统计方法方案1:AOP方案2:Mybatis插件方案3:使用连接池内置的监控方案1:AOP@Aspect@Component@Slf4jpublic class MapperAspect { @AfterReturning("execution(* cn.xbmchina.mybatissqltime.mapper.*Mapper.*(..))") public void logServiceAccess(JoinPoint joinPoint) { log.info(原创 2020-12-30 20:15:00 · 1340 阅读 · 0 评论 -
InnoDB事务隔离级别
前因后果由来:为了解决并发事务带来的问题,隔离级别定义了并发事务间的相互影响程度;结果:不同的隔离级别应用于不同场景,常用RR和RC;实现:基于MVCC和锁实现;原创 2020-06-19 11:03:58 · 324 阅读 · 0 评论 -
InnoDB并发控制技术之MVCC
MVCC是什么? MVCC (Multiversion Concurrency Control),即多版本并发控制技术,它使得大部分支持行锁的事务引擎,不再单纯的使用行锁来进行数据库的并发控制,取而代之的是把数据库的行锁与行的多个版本结合起来,只需要很小的开销,就可以实现非锁定读,从而大大提高数据库系统的并发性能。MVCC的作用读不阻塞写,提高并发性; MVCC实现原理(Innodb引擎)核心思原创 2018-04-15 16:58:34 · 441 阅读 · 0 评论 -
主从数据库一致性问题
原因 数据库集群的主从数据同步存在延时,延迟期间从库上的读请求会读取到不一致的数据,如下图所示。(不管同步还是异步,只要存在数据冗余就存在数据一致性问题)解决方案 整体思路:数据库的主从同步可以实现数据的最终一致性,延迟时间一般在几毫秒以内,因此可以按照业务对数据不一致时间的容忍程度设计对应的方案。对于绝大部分业务来说,比如订单、消息、搜索等场景,这些业务场景下是可以容忍几毫秒的延时,因此可以直接忽略数据不一致问题;对于不能容忍几毫秒的不一致性的业务,则需要强制性读主。强制性读主太过暴力,原创 2020-06-28 09:38:12 · 459 阅读 · 0 评论 -
InnoDB并发控制技术之锁
锁模式Lock Mode共享锁(Lock Mode S):行级锁,可以与S锁共存,SQL语句比如select…lock in share mode会加S锁;互斥锁(Lock Mode X):行级锁,不能与其他锁共存,SQL语句比如select…for update及写操作都会加X锁;意向共享锁(Lock Mode IS):表级锁,事务向表中的数据加S锁时必须先获取表上的IS锁;意向互斥锁...原创 2019-04-28 17:15:57 · 233 阅读 · 0 评论 -
数据库索引与B+树
Hash 索引优点:对等比较时(例如=和<=>操作符),查询速度很快,时间复杂度O(1); 缺点:只能用于对等比较,不能被用于像<这样的范围查询条件。假如系统只需要使用像“键值对”的这样的存储结构,尽量使用hash类型索引。优化器不能用hash索引来为ORDER BY操作符加速。只有完整的键才能被用于搜索一行数据(使用了B-Tree索引,那么任何一个键的前缀都可以...原创 2018-05-22 20:02:42 · 259 阅读 · 0 评论 -
SQL语句执行过程
SQL执行过程 SQL 语句在 MySQL 中的流程是:SQL 语句→缓存查询→解析器→优化器→执行器,如下图所示:连接器 作用: 身份认证、权限校验、连接管理、以及安全管理;查看连接SHOW PROCESSLIST;Id:连接id;db:连接使用的数据库名称;Command:线程正在执行的命令类型;Sleep:线程正在等待客户端发送命令;Query:线程正在执行查询;Locked:线程正在等待锁释放;Sorting Data:线程正在对结果排序;Sending Dat原创 2020-06-23 08:45:31 · 210 阅读 · 0 评论 -
大字段优化
大字段类型 Mysql中的text/blob/longtext/longblob/varchar(8192)等占用较大存储空间的字段类型;影响占用大量的存储空间。因为每个大字段溢出的数据都会存储在单独页中(存储独享),现象就是占用的存储空间远超过实际的数据量;读写性能下降。相同的内存能够缓存的记录变少(占用的页变多);优化方式方式1: 压缩&压缩:将多个大字段合并成一个,...原创 2019-11-14 19:47:17 · 548 阅读 · 0 评论 -
InnoDB VS MyISAM
区别事务:InnoDB支持事务,而MyISAM不支持;行锁:InnoDB支持行锁,而MyISAM不支持;索引:InnoDB是聚簇索引,数据和索引在同一个文件;而MyISAM是非聚簇索引,数据和索引是独立的;全文索引:InnoDB不支持全文索引,而MyISAM支持;统计行数:InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描;而MyI...原创 2018-07-29 09:46:14 · 343 阅读 · 0 评论 -
LSM树
背景知识无论是机械硬盘还是固态硬盘,顺序操作要比随机操作快,相差大约三个数量级;当代数据存储引擎主要基于两类数据结构:B+树和LSM树。传统的SQL数据库,如MySQL、Oracle等主要基于B+树;当前流行的HBase,Cassandra,LevelDB等NoSQL数据库的核心存储引擎都是基于LSM树的思想发展而来的。LSM树思想 牺牲部分读性能,提升写性能,将随机写转换...转载 2018-08-21 15:43:40 · 803 阅读 · 0 评论 -
INSERT ON DUPLICATE KEY UPDATE
作用 主键或唯一索引重复时,执行UPDATE操作;语法INSERT INTO table (column_list)VALUES (value_list)ON DUPLICATE KEY UPDATE c1 = v1, c2 = v2, ...;返回值说明If the new row is inserted, the number of affected-ro...原创 2018-11-27 19:05:58 · 390 阅读 · 1 评论 -
INSERT/UPDATE IGNORE
作用 insert/update数据时,忽略抛出的错误。比如,批量插入时,有部分数据主键重复或者类型不对等等,无效的数据被忽略,有效的数据被插入;语法INSERT IGNORE INTO table(column_list)VALUES( value_list), ( value_list), ...示例建表,email字段为为唯一索引CREATE T...原创 2018-11-27 19:28:53 · 5438 阅读 · 1 评论 -
索引长度
长度限制 默认情况下,InnoDB 引擎单一字段索引的长度最大为 767 字节,启用服务器选项 innodb_large_prefix 后可以达到 3072 字节;对于 UTF-8 字符集,每个字符使用 3 字节来存储,VARCHAR 或者 TEXT 类型的字段的索引不能超过767/3= 255 个字符;对于 GBK 字符集,每个字符使用2字节来存储,VARCHAR 或者 TEXT 类型的字段的...原创 2018-11-27 22:40:20 · 794 阅读 · 0 评论 -
MySQL死锁日志
查看数据库死锁日志 查看命令:show engine innodb status \G 说明:通过show engine innodb status 查看的日志是最新一次记录死锁的日志,但是查看不到完整的事务的sql,通常显示当前正在等待锁的sql;死锁日志解析lock_mode X locks rec but not gap:表示不带gap锁的排它行锁;lock_mode X l...原创 2019-04-27 16:26:07 · 3479 阅读 · 0 评论 -
数据库相关各种超时时间
事务超时时间Transaction Timeout 事务超时时间规定了事务执行时间的上限,通常包含两大部分:SQL执行时间,以及业务处理时间,即Transaction timeout = Statement Timeout * N(所有SQL执行总时间) + 业务处理时间;@Transactional(timeout = 3) 单位秒 事务超时的报错信息:org.springframe...原创 2019-04-27 19:58:26 · 8674 阅读 · 0 评论 -
主键设计:自增序列
背景:对于Innodb引擎,每个表都必须显示设置一个主键;设计原则主键越短越好,最好是单向自增型;主键最好由一个字段构成,最多不能超过3个字段;作为主键的列在插入后不能修改或者极少修改,否则考虑自增列作为主键;自增型主键优点(使用id为主键原因)1. 自增型主键可以避免重排序,提高写性能; 2. 自增型主键可以降低二级索引的空间,提升二级索引的内存命中率; 3. 自...原创 2018-05-21 20:53:50 · 703 阅读 · 0 评论 -
Mysql-注意事项
查询最大id单库单表: select max(id) from t1,数据库引擎会进行优化走索引id; 分库分表: 执行select max(id) from t1会产生全表扫描的问题,实际执行的语句是select max(id) from t1 where item=数字,不会再走索引id。解决方案:select id form t1 where item=数字 order by id desc原创 2017-11-06 15:23:53 · 226 阅读 · 0 评论 -
数据备份与恢复
数据备份分类全量备份与增量备份全量备份:某一时刻,数据库所有数据的备份(MySQL提供了mysqldump工具);增量备份:某个时间段内,数据变化的备份(开启binlog);本地备份与远程备份本地备份:备份数据与原始数据在同一台机器上;远程备份:备份数据与原始数据不在同一台机器上;物理备份与逻辑备份物理备份:直接备份保存数据库数据的文件和目录;逻辑备份:备份数据库的元信息和数据信息;原创 2018-04-14 11:39:47 · 321 阅读 · 0 评论 -
DATETIME VS TIMESTAMP
区别点TIMESTAMP占用存储空间少:TIMESTAMP占用4个字节,DATETIME占用5个字节(5.6.4之后);DATETIME时间范围大:TIMESTAMP最大时间只能到2038-01-19,DATETIME最大时间到9999-12-31,相当于没有上限;自动初始化定义:列的默认值; 对于DATETIME和TIMESTAMP类型,使用DEFAULT CURRENT_TIMESTAM原创 2018-04-09 16:23:11 · 448 阅读 · 0 评论 -
truncate 、delete与drop区别
truncate作用:清空表中的数据;truncate之后,自增属性列的值被重置为初始值; Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.仅...原创 2018-04-10 19:28:33 · 838 阅读 · 0 评论 -
分页查询优化
LIMIT性能问题 当偏移量m很大时,普通limit m,n的分页写法,存在严重的性能问题(速度原来越慢)。原因是mysql执行策略是会先扫描m+n行数据,然后扔掉前m行数据,最后返回剩余n行数据。因此,m越大,性能越差。优化方案思路:前后端相结合方案。前端已查询的每个页面保存第一个值和最后一个值,查询相邻页时,根据当前页的值进行查询过滤即可;后端的SQL语句使用inner jo...原创 2018-04-11 14:27:41 · 317 阅读 · 0 评论 -
表的垂直拆分和水平拆分
转载:https://www.kancloud.cn/thinkphp/mysql-design-optimalize/39326垂直拆分定义 垂直拆分是指数据表列的拆分,把一张列比较多的表拆分为多张表,如下图所示: 拆分原则把不常用的字段单独放在一张表;把text,blob等大字段拆分出来放在附表中;经常组合查询的列放在一张表中;水平拆分定义 水平拆分是指数据表行的拆分,表的行数超过转载 2018-05-10 10:45:31 · 311 阅读 · 0 评论 -
TDDL之全局唯一ID
背景对于单表可以利用id的自增属性,生成全局唯一ID;对于分库分表来说,需要某种手段保证全局唯一ID(分表间);生成策略基于第三方生成最大的问题在于需要依赖第三方;分区思想:每个分库分表划分不同的区间或者设置不同的起始值; 缺点:运维成本太高,扩容时需要重新设置;映射表思想:每个分库设置不同的起始值,间隔1000,步长3000,每次请求批量ID到内存,分库对应的n加1,内存中的ID使用完之后再进行转载 2018-05-10 15:15:36 · 1580 阅读 · 0 评论 -
分库分表策略详解
转载:http://825635381.iteye.com/blog/2368838分库分表字段的选择 原则:区分度高,高频查询的字段,常用的字段有主键ID,用户ID,时间,商户ID,产品ID,业务类型等等。分库分表策略分区思想:按照ID区间、时间范围等策略分库分表; 优点:方便水平扩展,无数据迁移; 缺点:存在数据分布不均匀,查询时数据聚合困难等问题;路由表思想:专门维护一张或多张表存储映射转载 2018-05-10 11:26:59 · 849 阅读 · 0 评论 -
MySQL索引
概念聚簇索引:数据的存储顺序与主索引的顺序保持一致;非聚簇索引:索引顺序与物理存储顺序无关;聚簇索引聚簇索引具有唯一性,因为聚簇索引的顺序就是数据的物理存储顺序。优点:读速度比非聚簇索引快,因为少一次IO;缺点:如果涉及到重排序,聚簇索引的写速度会很慢; It is generally faster to read from a clustered index ...原创 2018-05-21 20:41:38 · 253 阅读 · 0 评论 -
SELECT..FOR UPDATE和LOCK IN SHARE MODE替代方案
说明 SELECT..FOR UPDATE是互斥锁,LOCK IN SHARE MODE是读写锁,都是为了解决“select+update并发更新的问题”,但是存在行锁升级为表锁的风险,影响并发,不建议使用。Innodb行锁原理 InnoDB行锁是通过给索引上的索引项加锁来实现的,这意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!但是,...原创 2017-10-11 11:06:01 · 1930 阅读 · 0 评论