mysql basic
关注
分享
扫一扫
文章平均质量分 62
林震南
笨小孩，攒钱，投资，争取早日财务自由，回老家教书育人
展开
-
metadata lock学习
之前遇到了这方面的问题，见https://blog.csdn.net/csdnhsh/article/details/119272524，所以抽了点岁时间来了解下这个方面的细节：1、table metadata lock（1）MDL简述为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁（metadata lock），来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。原创 2021-07-31 11:08:10 · 3134 阅读 · 0 评论 -
eq_range_index_dive_limit参数
看官方描述：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_eq_range_index_dive_limit这个变量是全局和局部都可以设置的，默认值是200，从mysql5.6开始引入，会显著的影响查询计划，5.6的时候默认值是10，后来意识到in后面带的多个值，5.7就将这个值调整到200的。目前使用的经验就是：在使用IN或者OR等条件进行查询时，MySQL使用eq...原创 2021-05-09 21:58:35 · 11546 阅读 · 2 评论 -
table_definition_cache和table_open_cache以及table_open_cache_instances
这三者，经常有很多人搞混淆，我们先看官方文档：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_table_definition_cache，他们三个是连在一起的，所以有些相似的地方，但是也有不同。1、table_definition_cache全局参数，默认值为-1，最小是为400，描述的是.frm文件在定义换成里面存储的总量，如果你创建一个比较大的值，会加快你打开表的速度。这个...原创 2021-05-09 19:35:42 · 15094 阅读 · 0 评论 -
MySQL的explicit_defaults_for_timestamp参数问题
先看官方的描述：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html这个参数全局或者sessioin都可以设置，默认是不开启的，可以这样理解：如果一行数据中某些列被更新了，如果这一行中有timestamp类型的列，那么么这个timestamp列的数据也会被自动更新到 更新操作所发生的那个时间点；这个操作是由explicit_defaults_for_timestamp这个变更控制的，大概有有以下几种情况：...原创 2021-05-01 23:37:30 · 12166 阅读 · 0 评论 -
MySQL的count(1)特别慢的解决方案
在mysql里面，统计表的行数，大部分情况下，大家都采用select count（1） from table_name where pkid>0 and xxxxx and xxxxxx;这样的方式，去数据库表查下返回结果。这种情况下，在表小的时候，很快返回，当表越来越大的时候，就会特别慢。特别是查询表总记录数的情况下。select count(1) from table_name where plid>0;我们先来看下count的方案，mysql一般有2种存储引擎：1、My...原创 2021-05-01 14:10:28 · 19936 阅读 · 1 评论 -
MySQL参数lower_case_table_names
先看官方网介绍：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html这是一个全局参数，参数文件里面的命令行模式设置是--lower-case-table-names=1默认是不打开的，值的类型是Integer，默认值为0，最小值1，最大值2.0：表名字是存储给定的大小并且是区分大小写的。1：表名字存储在磁盘是小写的，但是比较的时候不区分大小写。2：存储的时候是按照给定的大小写存储的，比较的时候是...原创 2021-05-01 13:46:06 · 15292 阅读 · 0 评论 -
MySQL的innodb_flush_log_at_trx_commit和sync_binlog参数
一些不是背景的说明 innodb_flush_log_at_trx_commit 这个参数可以说是InnoDB里面最重要的参数之一，它控制了重做日志（redo log）的写盘和落盘策略。 具体的参数意义见手册 简单说来，可选值的安全性从0->2->1递增，分别对应于mysqld 进程crash可能丢失 -> OS crash可能丢失 -> 事务安全。 以上是路人皆知的故事，并且似乎板上钉钉，无可八卦。 sync_binlog这个参数是对于MySQL系...原创 2021-03-26 00:14:44 · 10701 阅读 · 3 评论 -
由A网友的提问说开去
正在回家的路上，突然接到A网友的weixin消息：“truncate操作之后会不会释放磁盘空间？”我瞬间秒回到：当然。A网友接着问：“开发那边反应truncate执行完了，数据库的服务器磁盘空间没有释放反而增加了”我回：业务在运行，还有binlog生成，你看下truncate执行之后的数据增量和日志增量就知道是怎么回事了A网友：我马上去了解下业务，去查下数据增长情况。过了半个小...原创 2019-10-13 22:17:26 · 10204 阅读 · 0 评论 -
Inception 初探
1，安装下载组件wget clone https:/github.com/mysql-inception/inception.git rz ll unzip inception-master.zip cd inception-master 安装基础组件 yum install bison -y yum install cmake -y yum install cmake n...原创 2019-08-03 15:01:27 · 10212 阅读 · 0 评论 -
MySQL 大表在线DML神器--pt-online-schema-change
一个朋友问我在线对大表进行ddl操作，如何做能尽量避免主从延迟以及不影响在线dml操作呢？我想到一个开源的pt-online-schema-change工具，测试了吧，效果还可以。pt-online-schema-change原理1、如果存在外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理。2、创建一个新的表，表结构为修改后的数据...原创 2019-08-03 15:00:08 · 10732 阅读 · 0 评论 -
MySQL 5.7.16 SSL 实践指南
1，关于SSL/VPN/SSH隧道 平常应用程序与MySQL服务器要建立一个安全通道的时候，普通的做法通常是适用VPN/SSH隧道，也就是网络隔绝的方式来实现，来最大可能断绝外界对数据库的攻击探测攻击行为。实际上在MySQL服务器中也有类似的解决方案，通过实施SSL可以加密在服务器与客户机之间来回传输的所有数据，因而防止广域网或数据中心里面可能出现的窃听或数据嗅探行为。此外...原创 2019-07-27 13:03:36 · 10634 阅读 · 0 评论 -
MySQL 瑞士军刀SQLyog升级到最新版本
1、导出connections进“Tools”-> “Export/Import Connections Details”-> “ExportConnections Details”，如下01_1.png所示：之后，在弹出的界面中，选择“Select All”打勾，在“File name”一栏填写好导出文件名sqlyog.sys，之后点击“Export”导出。如...原创 2019-07-27 13:02:36 · 12259 阅读 · 0 评论 -
MySQL 使用自增ID主键和UUID 作为主键的优劣比较详细过程（从百万到千万表记录测试）
测试缘由一个开发同事做了一个框架，里面主键是uuid，我跟他建议说mysql不要用uuid用自增主键，自增主键效率高，他说不一定高，我说innodb的索引特性导致了自增id做主键是效率最好的，为了拿实际的案例来说服他，所以准备做一个详细的测试。作为互联网公司，一定有用户表，而且用户表UC_USER基本会有百万记录，所以在这个表基础上准测试数据来进行测试。...原创 2019-07-24 11:12:20 · 10627 阅读 · 0 评论 -
MySQL 使用自增ID主键和UUID 作为主键的优劣比较详细过程（500W单表）
测试缘由一个开发同事做了一个框架，里面主键是uuid，我跟他建议说mysql不要用uuid用自增主键，自增主键效率高，他说不一定高，我说innodb的索引特性导致了自增id做主键是效率最好的，为了说服他，所以准备做一个详细的测试。作为互联网公司，一定有用户表，而且用户表UC_USER基本会有百万记录，所以在这个表基础上准测试数据来进行测试。 大概环境是：Cen...原创 2019-07-24 11:12:11 · 10835 阅读 · 0 评论 -
历史笔记记录之 MySQL DBA 面试题目 答疑记
MySQL DBA 面试题解惑一个朋友发了帖子，询问一些mysql dba面试题，回答的人比较少，他把地址给了我，只是我没有那个网站的账号，所以就整理下发在我的blog里面，大家可以参考下，也欢迎提出更加合理更加高效的处理方案。1、对于一台DB服务器，有哪些是必须监控的基础指标，如何得到这些值？必须监控的有：cpu负载、内存使用率、磁盘大小、io读写、网络流量、...原创 2019-07-23 09:08:53 · 10226 阅读 · 0 评论 -
MySQL 使用pt-table-checksum 检查主从数据一致性
1、基本环境：Mysql版本：5.6.12-logPercona-toolkit：2.2.18Linux：centos6.52、安装源码安装： # 一些依赖包 yum install perl perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Time-HiRes -y ...原创 2019-07-23 09:08:19 · 10376 阅读 · 0 评论 -
MySQL 体系结构以及各种文件类型学习汇总
1，mysql体系结构由数据库和数据库实例组成，是单进场多线程架构。数据库：物理操作系统文件或者其它文件的集合，在mysql中，数据库文件可以是frm、myd、myi、ibd等结尾的文件，当使用ndb存储引擎时候，不是os文件，是存放于内存中的文件。数据库实例：由数据库后台进程/线程以及一个共享内存区组成，共享内存可以被运行的后台进程/线程所共享。2，mysql文件类型...原创 2019-07-03 12:42:33 · 10484 阅读 · 0 评论 -
MySQL 数据库设计初步规范V1.0
数据库设计规范：1，表设计规范1.1关于表设计a) 表名、列名必须有注释。b)命名应使用富有意义的英文词汇或者缩写，多个单词组成的，全部大写，以"_"分隔开来，只能使用英文字母，数字和下划线，不留空格。比如USER_DETALL，不允许使用关键字TYPE或者STATUS等来作为字段名。c)命名长度不...原创 2019-06-30 00:56:05 · 10482 阅读 · 0 评论 -
MySQL 用户权限详细汇总
1，MySQL权限体系mysql 的权限体系大致分为5个层级:全局层级：全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限。数据库层级：数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db表中。GRANT ALL ON db_name.*和REV...原创 2019-06-30 00:18:15 · 10818 阅读 · 0 评论 -
MySQL 高可用架构在业务层面的分析研究
前言： 相对于传统行业的相对服务时间9x9x6或者9x12x5，因为互联网电子商务以及互联网游戏的实时性，所以服务要求7*24小时，业务架构不管是应用还是数据库，都需要容灾互备，在mysql的体系中，最好通过在最开始阶段的数据库架构阶段来实现容灾系统。所以这里从业务宏观角度阐述下mysql架构的方方面面。一，MySQL架构设计—业务分析（1）读多写少虚线表示...原创 2019-06-29 08:46:49 · 10187 阅读 · 0 评论 -
MySQL 最有意思的视图view优化过程，从30分钟到0.08秒
开发人员写了一个view，select要30分钟，让我优化下，view如下：CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW view_offer_label ASSELECT ol.OFFER_ID AS OFFER_ID,ol.EFFECTIVE_DATE AS EFFECTIVE_DATEFROM offer_label ...原创 2019-06-22 10:50:29 · 13576 阅读 · 1 评论 -
MySQL存储过程中使用动态SQL与静态SQL的区别
存储过程中使用动态SQL 实例DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`t1`$$CREATE /** [DEFINER = { user | CURRENT_USER }]*/ PROCEDURE `test`.`t1`() /*LANGUAGE SQL | [NOT] DETERMINISTIC...原创 2019-06-20 13:41:41 · 10416 阅读 · 0 评论 -
[MySQL 工具] percona-toolkit， pt-variable-advisor
pt-variable-advisor名称 NAME¶pt-variable-advisor-分析MySQL变量并且就可能问题提出建议。概要 SYNOPSIS使用Usagept-variable-advisor [OPTIONS] [DSN]pt-variable-advisor分析MySQL变量并且就可能问题提出建议。从本地获得SHOWVARIABLE...原创 2019-06-19 21:35:45 · 10544 阅读 · 0 评论 -
[MySQL 工具] pt-index-usage使用 -- 对查询中的索引进行分析
pt-index-usage¶pt-index-usage从日志里面读取查询,并且分析它们是如何使用索引的.概述：使用pt-index-usage [OPTIONS] [FILES]pt-index-usage ：从日志里面读取查询,并且分析它们是如何使用索引的.分析查询会在slow.log里面并且打印报告：pt-index-usag...原创 2019-06-19 21:35:39 · 10866 阅读 · 0 评论 -
[MySQL 工具] pt-mysql-summary使用分析mysq数据库
pt-mysql-summary：本地安装、然后远程收集MySQL Server的基本信息在安装percona-toolkit的时候，这个组件也一起安装完成了：安装参考：http://blog.csdn.net/mchdba/article/details/14209765要先在数据库里面建立监控帐号：grant all on *.* to pt@''%" identified...原创 2019-06-19 21:34:30 · 10911 阅读 · 0 评论 -
[MySQL 工具] pt-query-digest使用出错问题分析Can't locate Time/HiRes.pm in @INC
pt-query-digest ：分析查询执行日志，并产生一个查询报告[root@472322 percona-toolkit-2.2.5]# pt-query-digest --helpCan't locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 ...原创 2019-06-19 21:34:24 · 10558 阅读 · 0 评论 -
[MySQL 工具]percona-toolkit使用(1)源码安装
1、percona-toolkit简介percona-toolkit是一组高级命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的mysql和系统任务，这些任务包括：l 检查master和slave数据的一致性l 有效地对记录进行归档l 查找重复的索引l 对服务器信息进行汇总l 分析来自日志和tcpdump的查询l 当系统出问题的时候收集重要的系统信息percon...原创 2019-06-19 21:30:26 · 10480 阅读 · 0 评论 -
关于MySQL Query Cache的一些交流心得
今天线上MySQL 出现内存使用率报警，就去查了下mysql内存使用的参数，重点是缓存，关于innodb_buffer_pool_size以及query cache的使用。query_cache_type 默认是打开的，而且缓存区query_cache_size默认大小是32M，通常建议不超过256M大小，可以用过查询cache参数来看具体值：mysql> show var...原创 2019-06-18 13:56:15 · 10381 阅读 · 0 评论 -
MySQL事务学习 -- 分布式事务
7 分布式事务Innodb存储引擎支持XA事务，通过XA事务可以支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源（transac tional resources）参与一个全局的事务中。事务资源通常是关系型数据库系统，也可以是其它类型的资源。全局事务要求在其中所有参与的事务要么全部提交，要么全部回滚，这对于事务原有的ACID要求又有了提高。另外，在使用分布...原创 2019-06-18 13:55:52 · 10221 阅读 · 0 评论 -
MySQL事务学习 -- 隔离级别
6 事务的隔离级别设置的目的在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。数据库是要被广大客户所共享访问的，那么在数据库操作过程中很可能出现以下几种不确定情况。更新丢失两个事务都同时更新一行数据，但是第二个事务却中途失败退出，导致对数据的两个修改都失效了。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。脏读一个事务开始读取了...原创 2019-06-17 19:32:10 · 10266 阅读 · 0 评论 -
MySQL 事务的学习整理
事务是数据库区别文件系统的重要特征之一。在文件系统中，如果你正在写文件，但是操作系统突然崩溃了，这个时候文件有可能会被损坏的，当然也会有一些机制让文件恢复到某一个时间点，比如依靠原有的备份等。1 引入事务的目的数据库系统引入事务的主要目的：事务会把数据库从一种一致状态转换成另外一种状态。在数据库提交工作时，可以确保其要么所有修改都已经保存了，要么所有修改都不保存。2 ...原创 2019-06-17 19:08:31 · 10305 阅读 · 0 评论 -
笔记整理之 Kill掉MySQL中所有sleep的client线程
写了一个脚本，run这个脚本，就可以kill掉MySQL中所有sleep的client线程vim killsleep.sh#It is used to kill processlist of mysql sleep#!/bin/shwhile :do n=`mysqladmin processlist -uadmin -pxxxxx|grep -i sleep |wc -l`...原创 2019-06-15 14:57:08 · 10329 阅读 · 0 评论 -
自增字段 auto_commit的研究分析
MySQL自增字段，自增字段计数器在主存储里面，不在硬盘上(This counter is stored only in main memory, not on disk)。1，添加表，设立自增主键字段create table t(id int primary key auto_increment, name varchar(3000)) engine=innodb;2，可以让系统自增，也可以...原创 2019-06-15 14:55:55 · 10350 阅读 · 0 评论 -
MySQL 分区表 partition线上修改分区字段，后续进一步学习partition (1)
公司线上在用partition，有一个表的分区字段错了，需要重建，结果发现没有办法像修改主键字段或者修改索引字段那样直接一条sql搞定。而是需要建临时表，有down time，所以去仔细看了文档，研究下partition的细节问题。自己公司线上采取的时候，凌晨1点业务低峰期，执行：建立临时表CREATE TABLE tbname_TMP ( SHARD_ID INT NOT N...原创 2019-06-09 00:13:46 · 11871 阅读 · 0 评论 -
笔记整理之 SSD磁盘，CPU居高不下,高并发的情况下,是不是mysql解析器耗费的cpu资源高?
你看看我做的实验，这个user表是300多W纪录，普通磁盘下，消耗时间最多的是Copy to tmp table 0.81秒，当然在ssd下，这个可以减少很多很多的，第二高就是sending data在0.009秒，第三才是optimizing解析器优化部分，才0.0003秒，是sending data的1/30不到。所以我从此可以理解成mysql解析...原创 2019-06-09 00:10:47 · 10189 阅读 · 0 评论 -
load data 方式导入的数据不可以用binlog日志进行恢复，因为binlog里面不产生insert sql语句
QQ群里面有人问起这个问题: 用loaddata导入数据的时候，在binlog文件中记录的不是insert语句,这样的话，如果用loaddata导入数据，当需要恢复数据库的时候binlog恢复就不行了load data local infile '/root/table.txt' into table test.table ;生成binlog 日志然后用mys...原创 2019-06-09 00:10:27 · 10555 阅读 · 0 评论 -
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
ERROR 1045 (28000): Access denied for user'root'@'localhost' (using password: NO)朋友问我问题:mysql 升级了 5.0 -- 5.5导入的数据没问题, grant all privileges on *.* to test@'12.12.12.12';报错ERROR 1045 (28000): Acce...原创 2019-06-08 10:01:39 · 10289 阅读 · 0 评论