微微陌上矣 - mysql
type: channel
link: http://www.cynthia99.link/index.php/tag/mysql
items: Mysql 安装配置及数据导入
items: Mysql 全面总结-由浅入深
items: Mysql 性能优化思路
items: Mysql学习笔记

Mysql 安装配置及数据导入
type: item
date: 2022-09-28T17:00:39+08:00
description: 下载安装 https://downloads.mysql.com/archives/installer/ 选择这一个并点击DownLoad 下载完后点击打开文件 Mysql Installer 选择Custom后点击Next 点击Next 选择并执行 install完成后一直点击next直到出现如下页面 一直点击直到 我们在此添加数据库账号密码以及新增user 设置服务的名称，一般还用mysql，还有开机启动服务的开关 安装这些东西 一直点击Next直到Finish 输入密码最后连接成功 一直Next直到点击Finish出现如下页面 Mysql Workbench Mysql也自带了管理工具 在输入密码后即可进入管理页面 点击数据库进入数据库列表 鼠标点击右键 Create Schemas 输入名称后点击Apply Apply就可以看见我们新建的数据库 数据导入 点击此按钮进入Sql导入 选择你要导入的脚本 最后点击这个闪电即可运行 出现该错误表示我们没有选择哪个数据库 我们右键点击我们所需要导入的数据库，右键选择 Set as default Schema,再重新运行 导入成功

Mysql 全面总结-由浅入深
type: item
date: 2022-09-22T20:20:00+08:00
description: 数据库相关概念 三个概念：数据库、数据库管理系统、SQL。 SQL语言是操作关系型数据库的 统一标准 。所以即使我们现在学习的是MySQL， 假如我们以后到了公司，使用的是别的关系型数据库，如：Oracle、DB2、SQLServer，也完全不用 担心，因为操作的方式都是一致的。 下载地址：https://downloads.mysql.com/archives/installe 数据模型 关系型数据库（RDBMS） 概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。 而所谓二维表，指的是由行和列组成的表，如下图 （就类似于Excel表格数据，有表头、有列、有行， 还可以通过一列关联另外一个表格中的某一列数据） 。我们之前提到的MySQL、Oracle、DB2、 SQLServer这些都是属于关系型数据库，里面都是基于二维表存储数据的。简单说，基于二维表存储 数据的数据库就成为关系型数据库，不是基于二维表存储数据的数据库，就是非关系型数据库。 特点 A. 使用表存储数据，格式统一，便于维护。 B. 使用SQL语言操作，标准统一，使用方便。 数据模型 MySQL是关系型数据库，是基于二维表进行数据存储的，具体的结构图下 我们可以通过MySQL客户端连接数据库管理系统DBMS，然后通过DBMS操作数据库。 可以使用SQL语句，通过数据库管理系统操作数据库，以及操作数据库中的表结构及数据。 一个数据库服务器中可以创建多个数据库，一个数据库中也可以包含多张表，而一张表中又可以包 含多行记录。 SQL语法 全称 Structured Query Language，结构化查询语言。 操作关系型数据库的编程语言，定义了 一套操作关系型数据库统一标准 。 SQL通用语法 SQL语句可以单行或多行书写，以分号结尾。 SQL语句可以使用空格/缩进来增强语句的可读性。 MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。 注释： 单行注释：-- 注释内容 或 # 注释内容 多行注释：/ 注释内容 */ Sql分类 DDL DDL Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段) 。 数据库操作 查询所有数据库 show databases ; 查询当前数据库 select database() ; 创建数据库 create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序 规则 ] ; 以数据库名称为hand举例 create database hand; create database if not extists hand; #不存在则创建 create database hand default charset utf8mb4; #指定字符集 drop database [ if exists ] hand; #存在则删除 use hand ; #切换到hand库 表操作 查询当前数据库所有表 show tables; 查看表结构 desc 表名; 查看指定表的建表语句 show create table 表名 ; 创建表结构 CREATE TABLE 表名( 字段1 字段1类型 [ COMMENT 字段1注释 ], 字段2 字段2类型 [COMMENT 字段2注释 ], 字段3 字段3类型 [COMMENT 字段3注释 ], ...... 字段n 字段n类型 [COMMENT 字段n注释 ] ) [ COMMENT 表注释 ] ; 示例 表操作-修改-添加字段 修改数据类型 ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ]; 示例 ALTER TABLE hand ADD nickname varchar(20) COMMENT '昵称'; 表操作-修改-数据类型 ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度); 修改字段名和字段类型 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ]; 删除字段 ALTER TABLE 表名 DROP 字段名; 修改表名 ALTER TABLE 表名 RENAME TO 新表名; ALTER TABLE hand CHANGE nickname username varchar(30) COMMENT '昵称'; #修改字段名称和数据类型 ALTER TABLE hand DROP username; #删除表字段 ALTER TABLE hand RENAME TO employee; #…

Mysql 性能优化思路
type: item
date: 2022-09-08T23:27:00+08:00
description: 执行顺序 连接-配置优化 第一个环节是客户端连接到服务端，连接这一块有可能会出现什么样的性能问题?有可能是服务端连接数不够导致应用程序获取不到连接。比如报了一个 Mysql: error 1040: Too many connections 可以从两个方面来解决连接数不够的问题: 1、从服务端来说，我们可以增加服务端的可用连接数。 如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以: (1）修改配置参数增加可用连接数，修改max_connections的大小: show variables like 'max_connections'; --修改最大连接数，当有多个应用连接的时候 (2）或者，或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，我们可以把这个值调小。 show global variables like 'wait_timeout"':--及时释放不活动的连接，注意不要释放连接池还在使用的连接 2、从客户端来说，可以减少从服务端获取的连接数，如果我们想要不是每一次执行SQL都创建一个新的连接，可以引入连接池，实现连接的重用。 缓存-架构优化 缓存 在应用系统的并发数非常大的情况下，如果没有缓存，会造成两个问题:一方面是会给数据库带来很大的压力。另一方面，从应用的层面来说，操作数据的速度也会受到影响。 我们可以用第三方的缓存服务来解决这个问题，例如Redis。 集群 主从复制 如果单台数据库服务满足不了访问需求，那我没可以做数据库的集群方案。 做了主从复制的方案之后，我们只把数据写入master节点，而读的请求可以分担到slave节点。我们把这种方案叫做读写分离。 读写分离可以一定程度低减轻数据库服务器的访问压力，但是需要特别注意主从数据一致性的问题。 分库分表 垂直分库，减少并发压力。水平分表，解决存储瓶颈。 垂直分库的做法，把一个数据库根据业务拆分成不同的数据库: 单库 垂直分库 水平分库 水平分库分表的做法，把单张表的数据按照一定的规则分布到多个数据库。 优化器-sql语句分析与优化 我们的服务层每天执行了这么多SQL语句，它怎么知道哪些SQL语句比较慢呢?第一步，我们要把SQL执行情况记录下来。 慢查询日志slow query log https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html 打开慢日志开关 因为开启慢查询日志是有代价的（跟bin log.optimizer-trace一样)，所以它默认是关闭的: show variables like 'slow_query%'; 除了这个开关，还有一个参数，控制执行超过多长时间的SQL才记录到慢日志，默认是10秒。如果改成0秒的话就是记录所有的SQL。 show variables like '%long_query%'; 可以直接动态修改参数(重启后失效)。 set a@global.slow_query_log=l;--1开启，О关闭，重启后失效 set a@global.long_query_time=3;--mysql默认的慢查询时间是10秒，另开一个窗口后才会查到最新值 show variables like "%long_query%'; show variables like '%oslow_query%'; 或者修改配置文件 my.cnf。 以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。 slow_query_log = ON long_query_time=2 slow_query_log_file =/var/lib/mysqI/localhost-slow.log 模拟慢查询: select sleep(10); 查询user_innodb表的500万数据（没有索引)。 SELECT* FROM 'user_innodb` where phone = '136'; 慢日志分析 日志内容 show global status like 'slow_queries'; --查看有多少慢查询 show variables like 1%slow_query%'; --获取慢日志目录 mysqldumpslow https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html MySQL提供了mysqldumpslow 的工具，在MySQL的 bin目录下。 mysqldumpslow --help 例如:查询用时最多的10条慢SQL: mysqldumpslow -s t -t 10 -g 'select'/var/lib/mysqI/localho…

Mysql学习笔记
type: item
date: 2022-06-09T21:50:00+08:00
description: 数据库三大范式 数据库范式是设计数据库时，需要遵循的一些规范。各种范式是条件递增的联系，越高的范式数据库冗余越小。常用的数据库三大范式为： 第一范式（1NF）：每个列都不可以再拆分，强调的是列的原子性。第一范式要求数据库中的表都是二维表。 第二范式（2NF）：在第一范式的基础上，一个表必须有一个主键，非主键列 完全依赖 于主键，而不能是依赖于主键的一部分。 第三范式（3NF）：在第二范式的基础上，非主键列只依赖（直接依赖）于主键，不依赖于其他非主键。 触发器 触发器（trigger）是与表相关的数据库对象，是用户定义在关系表上的一类由事件驱动的特殊的存储过程，在满足定义条件时触发，并执行触发器中定义的 语句集合。触发器的这种特性可以协助应用在数据库端确保 数据库的完整性。 使用场景 可以通过数据库中的相关表实现 级联更改； 实时监控某张表中的某个字段的更改，并需要做出相应的处理。 触发器的作用举例： 1.可在写入数据表前，强制检验或转换数据。 2.触发器发生错误时，异动的结果会被撤销。 索引 索引的优点 通过创建 唯一性索引，可以保证数据库表中每一行数据的唯一性； 可以加快数据的 检索速度，这也是创建索引的主要原因； 可以加速表和表之间的连接，特别是在实现 数据的参考完整性 方面特别有意义； 通过使用索引，可以在查询的过程中，使用 优化隐藏器，提高系统性能。 索引的缺点 时间上，创建和维护索引都要耗费时间，这种时间随着数据量的增加而增加，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度； 空间上，索引需要占 物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 索引为什么可以加快查询速度？ 因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据 同时，由于索引底层实现的有序性，使得在进行数据查询时，能够避免在磁盘不同扇区的随机寻址 使用索引后能够通过磁盘预读使得在磁盘上对数据的访问大致呈顺序的寻址。这本质上是依据局部性原理所实现的 总结 索引大大减少了服务器需要扫描的数据量 索引可以帮助服务器避免排序和临时表 索引可以将随机I/O变成顺序I/O（局部性原理） 索引的数据结构 数据库索引根据结构分类，主要有 B 树索引、Hash 索引 和 位图索引 三种。 B 树索引 B 树索引，又称 平衡树索引，是 MySQL 数据库中使用最频繁的索引类型，MySQL、Oracle 和 SQL Server 数据库默认的都是 B 树索引（实际是用 B+ 树实现的，因为在查看表索引时，MySQL 一律打印 BTREE，所以简称为 B 树索引）。 B 树索引以 树结构 组织，它有一个或者多个分支结点，分支结点又指向单级的叶结点。其中，分支结点用于遍历树，叶结点则保存真正的值和位置信息。 B+ 树是在 B 树基础上的一种优化，使其更适合实现外存储索引结构。 一棵 m 阶 B-Tree 的特性如下： 每个结点最多 m 个子结点； 除了根结点和叶子结点外，每个结点最少有 m/2（向上取整）个子结点； 所有的叶子结点都位于同一层； 每个结点都包含 k 个元素（关键字），这里 m/2≤k<m，这里 m/2 向下取整； 每个节点中的元素（关键字）从小到大排列； 每个元素子左结点的值，都小于或等于该元素，右结点的值都大于或等于该元素。 数据库以 B-Tree 的数据结构存储数据的图示如下： B+ Tree 与 B-Tree 的结构很像，但是也有自己的特性： 所有的非叶子结点只存储 关键字信息； 所有具体数据都存在叶子结点中； 所有的叶子结点中包含了全部元素的信息； 所有叶子节点之间都有一个链指针。 数据库以 B+ Tree 的数据结构存储数据的图示如下： Hash 索引 哈希索引采用一定的 哈希算法（常见哈希算法有 直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的 Hash 值，与这条数据的行指针一并存入 Hash 表的对应位置，如果发生 Hash 碰撞（两个不同关键字的 Hash 值相同），则在对应 Hash 键下以 链表形式 存储。 检索时不需要类似 B+ 树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快，平均检索时间为 O(1)。 位图索引 B 树索引擅长于处理包含许多不同值的列，但是在处理基数较小的列时会变得很难使用。如果用户查询的列的基数非常的小， 即只有几个固定值，如性别、婚姻状况、行政区等等，要么不使用索引，查询时一行行扫描所有记录，要么考虑建立位图索引。 位图索引为存储在某列中的每个值生成一个位图。例如针对表中婚…