标签 MySQL 下的文章

数据库设计三范式

三范式第一范式：任何一张表都应该有主键，每个字段是原子性的不能再分。第二范式：建立在第一范式基础上，另外要求所有非主键字段完全依赖主键，不能产生部分依赖。学生编号(PK)教师编号(PK)学生姓名教师姓名10011张三王老师10022李四赵老师10031王五王老师10012张三赵老师上表虽然符合第一范式，但违背第二范式，学生姓名、老师姓名都产生了部分依赖，导致数据冗余。第三范式：建立在第二范式基础上，非主键字段不能传递依赖于主键字段。学生编号(PK)学生姓名班级编号班级姓名1001张三1一年一班1002李四2一年二班1003王五3一年三班1004赵六3一年三班上表因为产生了传递依赖，导致班级名称冗余。表设计一对多（学校 - 学生）：一对多两张表，多的表加外键。多对多（学生 - 课程）：多对多三张表，关系表加外键。一对一第一种方案：主键共享。hid(主键)hname1张三2李四3王五wid(主键+外键)wname1小花2小兰3小红一对一第二种方案：外键唯一。hid(主键)hname1张三2李四3王五wid(主键)wnamehid(外键+unique)1小花12小兰23小红3最终设计以满足客

编程 · 05-17 · 250 人浏览

MySQL内置函数学习笔记

编程 · 05-17 · 351 人浏览

MySQL存储引擎InnoDB索引页

一个InnoDB索引页的存储空间大致被划分成了7个部分：名称中文名占用空间大小简单描述File Header文件头部38字节页的一些通用信息Page Header页面头部56字节索引页专有的一些信息Infimum + Supremum最小记录和最大记录26字节两个虚拟的行记录User Records用户记录不确定实际存储的行记录内容Free Space空闲空间不确定页中尚未使用的空间Page Directory页面目录不确定页中的某些记录的相对位置File Trailer文件尾部8字节校验页是否完整其实，在一开始生成页的时候，并没有User Records这个部分，每当插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了。

编程 · 02-22 · 329 人浏览

MySQL数据类型

整数类型tinyint：1个字节（微小整数）smallint：2个字节（小整数）mediumint：3个字节（中等大小整数）int（integer）：4个字节（普通大小整数）bigint：8个字节（大整数）浮点数类型float：4个字节，单精度（最多5位小数）double：8个字节，双精度（最多16位小数）定点数类型decimal：定点数类型，底层实际上采用字符串形式存储数字。语法：decimal(m, d)，例如：decimal(3, 2)表示3个有效数字，2个小数（有效数字最多65个，小数位最多30个）。日期时间类型year：1个字节，只存储年，格式YYYYtime：3个字节，只存储时间，格式HH:MM:SS / HHMMSSdate：3个字节，只存储年月日，格式：YYYY-MM-DDdatetime：8个字节，存储年月日+时分秒，格式：YYYY-MM-DD HH:MM:SS（范围从公元1000年~公元9999年）timestamp：4个字节，存储年月日+时分秒，格式：YYYY-MM-DD HH:MM:SS（范围从公元1970年~公元2038年，TIMESTAMP会受时区影响）或者

编程 · 02-22 · 328 人浏览

MySQL架构

两台或以上数据库实例，通过二进制日志，实现数据的“同步"关系。主从复制实例准备：两台以上MySQL实例，server_id不同。主库主库开启二进制日志主库建立复制用户：grant replication slave on . to repl@'10.0.0.%' identified by '123';主库备份：mysqldump -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql告知从库复制信息，找到复制开始起点：grep "\-- CHANGE MASTER TO" /tmp/full.sql从库恢复到从库：mysql </tmp/full.sql从帮助中获取change master to语句进行修改：mysql> help change master to执行CHANGE MASTER TO命令从库开启专用复制线程：start slave;验证主从状态：show slave status \G主从复制原理文件主库：binlog文件从库：relay-l

编程 · 02-22 · 302 人浏览

MySQL索引自优化算法

InnoDB架构图：查看优化器算法开关：select @@optimizer_switch;修改，例如：set global optimizer_switch='batched_key_access=on';，退出客户端，重新进入客户端查看修改结果。设置方法也可以使用优化器提示Hints的形式，或者设置my.cnf配置文件。索引自优化AHIAdaptive Hash Index，即自适应哈希索引，作用是自动评估热内存索引page，生成Hash索引表，帮助InnoDB快速读取索引页，加快索引读取的速度，相当于索引的索引。Change Buffer比如insert、update、delete数据，对于聚簇索引会立即更新，而对于辅助索引不是实时更新的。在InnoDB内存结构中，加入了Insert Buffer，现在版本叫Change Buffer。Change Buffer功能是临时缓冲辅助索引需要的数据更新。当我们需要査询新insert的数据，会在内存中进行merge合并操作，此时辅助索引就是最新的。优化器算法ICPIndex Condition Pushdown，即索引下推，作用是解决联

编程 · 02-22 · 280 人浏览

MySQL备份与恢复

备份mysqldump是逻辑备份工具，备份的是SQL语句。备份方式:InnoDB表可采取快照备份的方式，开启一个独立事务，获取当前最新的一致性快照。将快照数据，放在临时表中，转换成SQL，保存到sql文件中非InnoDB表需要锁表备份。触发FTWRL，全局锁表。转换成SQL，保存到sql文件中备份参数：-A 全备：mysqldump -A > /backup/full.sql-B 备份1或多个库：mysqldump -B test1 test2 > /backup/db.sql备份单表或多表：mysqldump database_name table1 table2 > /backup/tab.sql加不加-B参数的区别：加-B参数，例如，-B test备份的是：create database test;use test;没加-B参数，备份的是表，在应用时如果test库不存在，需要手工创建，并且use到test库下再恢复备份高级参数：--master-data，功能：备份时自动记录binlog信息自动锁表和解锁配合--single-transaction可以减少锁表时

编程 · 02-02 · 337 人浏览

MySQL日志管理

错误日志记录MySQL从启动以来，所有的状态、警告、错误。为我们定位数据库问题，提供帮助。查看数据库日志路径：select @@log_error;默认是开启状态，文件位于/datadir/hostname.err，也可以通过my.cnf自定义文件位置：log_error=/tmp/mysql.log，重启MySQL生效。二进制日志作用是记录数据库变化（DDL，DCL，DML）性质的日志，是逻辑层性质日志。应用在数据恢复，主从复制中。8.0版本以前，默认没有开启，建议生产开启，且一定要和数据盘分开。配置方法：vim /etc/my.cnfserver_id=6 # 主机编号。主从中使用，5.7以后开binlog要加此参数 log_bin=/data/binlog/mysql-bin # 日志存放目录+日志名前缀，例如mysgl-bin.000001、mysq1-bin.000002 sync_binlog=1 # binlog日志刷盘策略，1是每次事务提交立即刷写binlog到磁盘 binlog_format=row # binlog记录格式为row模式记录内容bin1og是SQL层的

编程 · 02-02 · 305 人浏览

MySQL存储引擎InnoDB行格式

InnoDB规划了26种行格式，分别对应26种动物，首字母由A至Z：Antelope, Barracuda, Cheetah, Dragon, Elk, Fox, Gazelle, Hornet, Impala, Jaguar, Kangaroo, Leopard, Moose, Nautilus, Ocelot, Porpoise, Quail, Rabbit, Shark, Tiger, Urchin, Viper, Whale, Xenops, Yak, Zebra。目前InnoDB支持的行格式只有Antelope, Barracuda。而Antelope又具体细分为Redundant和Compact，Barracuda也具体细分为Dynamic和Compressed。可以在创建或修改表的语句中指定行格式：CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称 ALTER TABLE 表名 ROW_FORMAT=行格式名称Compact一条完整的记录可以被分为记录额外信息和记录真实数据两大部分。记录额外信息记录额外信息是服务器为了描述这条记录而不得不额外添

编程 · 01-29 · 282 人浏览

MySQL字符集和校对规则

纯文本在计算机底层会转换为二进制保存，将字符转换为二进制码的过程，称为编码，将二进制码转换为字符的过程，称为解码。字符集编码和解码时所采用的规则，称为字符集。常见字符集有：ASCII、GBK、Unicode等。编写程序时，如果发现程序代码出现乱码的情况，就要马上去检查字符集是否正确。ASCII字符集共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可使用1个字节来进行编码。ISO 8859-1字符集共收录256个字符，是在ASCII字符集基础上又扩充了128个西欧常用字符，也可以使用1个字节来进行编码。这个字符集也有一个别名latin1。GB2312字符集收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母，其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容ASCII字符集，所以在编码方式上显得有些奇怪：如果该字符在ASCII字符集中，则采用1字节编码，否则采用2字节编码。这种表示一个字符需要字节数可能不同的编码方式称为变长编码方式。如果某个字节是在0～127之内的，就意味着一个字节代表一个单独字符

编程 · 01-29 · 319 人浏览

MySQL启动选项和配置文件

命令行中使用选项--skip-networking：禁止客户端使用TCP/IP网络进⾏通信，可在启动服务器程序的命令行⾥添加该选项：mysqld --skip-networking--default-storage-engine：修改表的默认存储引擎，例如：mysqld --default-storage-engine=MyISAM--help：查看程序⽀持的全部启动选项以及默认值，例如：mysqld_safe --help看到mysqld_safe程序⽀持的启动选项。查看mysqld⽀持的启动选项有些特别，需要使⽤mysqld--verbose --help选项长形式和短形式短形式的选项名只有⼀个字⺟，与使用长形式选项时需要在选项名前加两个短划线--不同的是，使⽤短形式选项时在选项名前只加⼀个短划线-前缀。使用短形式指定启动选项时，选项名和选项值之间可以没有间隙，或者用空⽩字符隔开。配置文件中使用选项在命令行中设置启动选项只对当次启动⽣效，也就是说如果下⼀次重启程序的时候我们还想保留这些启动选项的话，还得重复把这些选项写到启动命令行中。可以把需要设置的启动选项都写在配置⽂件中，每次启

编程 · 01-29 · 280 人浏览

MySQL超详细安装配置卸载教程

版本选择在企业生产场景下，数据库是重中之重，因此选择MySQL数据库一定要慎重。下面是给出的一些选择建议：选稳定版版本，即选择开源的社区版的稳定版建议选择第二条产品线中的5.5或5.6版本，目前互联网公司主流版本是5.5和5.6选择MySOL数据库至少发布半年以上的稳定版本尽可能选择前后几个月没有大BUG修复的版本，而不是大量修复BUG的集中版本最好选择向后较长时间没有更新发布的版本要考虑开发人员开发程序使用的版本是否兼容你所选的版本首先作为内部开发测试数据库环境，测试运行几个月的时间优先对企业非核心业务采用新的数据库稳定版本Linux版本rpm安装rpm安装（二进制安装）须提前准备好rpm软件包，且无法满足定制化需求、编译参数、修改路径、依赖冲突问题，不建议使用。下载Linux版MySQL安装包：https://dev.mysql.com/downloads/mysql/Product Version: 8.0.32Operating System: Red Hat Enterprise Linux / Oracle LinuxOS Version: Red Hat Enterpri

编程 · 01-24 · 376 人浏览

MySQL学习笔记进阶

存储引擎其实存储引擎以前叫做表处理器，后来可能⼈们觉得太土，就改成了存储引擎的叫法。存储引擎是基于表的，所以也可被称为表类型。查询当前数据库支持的存储引擎：show engines;存储引擎微观结构：包括数据内存区域和日志内存区域。数据内存区域：共享内存缓冲区：buffer pool缓冲池，用来缓冲数据页+索引页，查看参数：select @@innodb_buffer_pool_size;会话内存缓冲区：join_buffer_size、key_buffer_size、read_buffer_size、read_rnd_buffer_size、sort_buffer_size日志内存区域，负责Redo日志缓冲。InnoDBInnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL 5.5版本之后，被设置为默认的MySQL存储引擎。table.ibd：InnoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构、索引和数据。系统变量innodb_file_per_table：show variables like 'innodb_file_per_table';查看tab

编程 · 2023-07-30 · 232 人浏览

MySQL学习笔记基础

SQL各种名词sql_mode即SQL模式，作用是规范SQL语句书写方式，查看命令：select @@sql_mode;。MySQL通过设定sql_mode参数，规范比如除法运算，从而保证不会出现违背现实数学逻辑的SQL语句。核心SQL语句要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对``（着重号）引起来。通用语法：SQL语句可以单行或多行书写，以分号结尾SQL语句可以使用空格/缩进来增强语句的可读性SQL语句不区分大小写，关键字建议使用大写单行注释：-- 注释内容或# 注释内容(MySQL特有)。多行注释：/* 注释内容 */分类：DDL，Data Definition Language，数据定义语言，用来定义数据库对象（数据库，表，字段）DCL，Data Control Language，数据控制语言，用来创建数据库用户、控制数据库访问权限DML，Data Manipulation Language，数据操作语言，用来对数据库表中的数据进行增删改DQL，Data Query Language，数据查询语言，用来查询数据库中表记

编程 · 2023-07-30 · 265 人浏览