wxin/mysql
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
mysql/数据库基础.md
wxin d756f889c1 更新 数据库基础.md
2025-03-12 15:50:01 +08:00

21 KiB
Raw Permalink Blame History

数据库基础

一：数据库简介

1. 数据库技术构成

  • 数据库系统 DBS
    1. 数据库管理系统（DataBase Management System，DBMS）
      • SQL（RDS）：ORACLE、Oracle MySQL、MariaDB、Percona server、DB2
      • NoSQL：Redis、MongoDB、Memcache
    2. 数据库管理员（DBA）
  • SQL语言（Structured Query Language 即结构化查询语言）
    1. DDL语句 数据库定义语言：数据库、表、视图、索引、存储过程、函数，CREATE ALTER
    2. DML语句 数据库操作语言：插入数据 INSERT、输出输入 DELETE，DROP、更新数据 UPDATE
    3. DQL语句 数据库查询语言：查询数据 SELECT
    4. DCL语句 数据库控制语言：例如控制用户的访问权限 GRANT、REVOKE
  • 数据访问技术
    1. ODBC PHP <.php>
    2. JDBC JAVA <.jsp>
    3. settings.py python <.py>

2. 数据库部署

版本

  • mysql：8.0以上
  • mysql：5.7

网址

MySQL :: Developer Zone

yum 安装

下载mysql的yum仓库

[root@mysql ~]# wget https://dev.mysql.com/get/mysql84-community-release-el7-1.noarch.rpm

安装mysql的yum仓库

[root@mysql ~]# rpm -ivh https://dev.mysql.com/get/mysql84-community-release-el7-1.noarch.rpm

安装mysql

[root@mysql ~]# yum -y install mysql-server
[root@mysql ~]# systemctl start mysqld
[root@mysql ~]# systemctl enable mysqld

查看初始密码

[root@mysql ~]# grep 'password' /var/log/mysqld.log 
2025-03-09T08:54:35.000643Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: +oplg.ua>4wR

修改密码

[root@mysql ~]# mysqladmin -u root -p'+oplg.ua>4wR' password 'Wjifja@7412347289'

[root@mysql ~]# mysql -uroot -p'+oplg.ua>4wR'
mysql> alter user 'root'@'localhost' identified by 'Wjifja@7412347289';

rpm 安装

检查系统是否安装过mysql、mariadb

[root@mysql ~]# rpm -qa | grep mariadb
[root@mysql ~]# rpm -qa | grep mysql

卸载安装的mariadb

[root@mysql ~]# rpm -e --nodeps mariadb-libs

下载rpm包

[root@mysql ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar

解压tar文件

[root@mysql ~]# mkdir mysql
[root@mysql ~]# tar xvf mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar -C mysql && cd mysql

安装

[root@mysql mysql]# rpm -i mysql-community-common-5.7.30-1.el7.x86_64.rpm 
[root@mysql mysql]# rpm -i mysql-community-libs-5.7.30-1.el7.x86_64.rpm 
[root@mysql mysql]# rpm -i mysql-community-client-5.7.30-1.el7.x86_64.rpm 
[root@mysql mysql]# rpm -i mysql-community-server-5.7.30-1.el7.x86_64.rpm 
[root@mysql mysql]# rpm -ivh mysql-community-libs-compat-5.7.30-1.el7.x86_64.rpm

启动服务

[root@mysql mysql]# systemctl start mysqld
[root@mysql mysql]# systemctl enable mysqld

查看初始密码

[root@mysql mysql]# grep password /var/log/mysqld.log 
2025-03-09T09:31:55.267389Z 1 [Note] A temporary password is generated for root@localhost: (jbU*oyh-0d2

修改密码

[root@mysql mysql]# mysqladmin -uroot -p'(jbU*oyh-0d2' password 'Wdjia@3462713'

编译安装

清理安装环境：

新环境：
[root@rpm ~]# rpm -qa | grep mariadb
[root@rpm ~]# rpm -e --nodeps mariadb-libs
旧环境：
[root@rpm ~]# yum remove mariadb mariadb-server mariadb-libs mariadb-devel -y
[root@rpm ~]# userdel -r mysql
[root@rpm ~]# rm -rf /etc/my*
[root@rpm ~]# rm -rf /var/lib/mysql

创建mysql用户：

[root@rpm ~]# useradd -r mysql -M -s /sbin/nologin
-M 不创建用户的家目录

从官网下载tar包：

[root@rpm ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.27.tar.gz

安装编译工具：

[root@rpm ~]# yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make
[root@rpm ~]# yum -y install cmake

创建mysql目录：

[root@rpm ~]# mkdir -p /usr/local/{data,mysql,log}

解压：

[root@rpm ~]# tar xzvf mysql-boost-5.7.27.tar.gz -C /usr/local/

编译安装：

cd 解压的mysql目录
[root@rpm ~]# cd /usr/local/mysql-5.7.27/
[root@rpm mysql-5.7.27]# cmake . \
> -DWITH_BOOST=boost/boost_1_59_0/ \
> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
> -DSYSCONFDIR=/etc \
> -DMYSQL_DATADIR=/usr/local/mysql/data \
> -DINSTALL_MANDIR=/usr/share/man \
> -DMYSQL_TCP_PORT=3306 \
> -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
> -DDEFAULT_CHARSET=utf8 \
> -DEXTRA_CHARSETS=all \
> -DDEFAULT_COLLATION=utf8_general_ci \
> -DWITH_READLINE=1 \
> -DWITH_SSL=system \
> -DWITH_EMBEDDED_SERVER=1 \
> -DENABLED_LOCAL_INFILE=1 \
> -DWITH_INNOBASE_STORAGE_ENGINE=1

参数详解:
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \   安装目录
-DSYSCONFDIR=/etc \   配置文件存放 （默认可以不安装配置文件）
-DMYSQL_DATADIR=/usr/local/mysql/data \   数据目录   错误日志文件也会在这个目录
-DINSTALL_MANDIR=/usr/share/man \     帮助文档 
-DMYSQL_TCP_PORT=3306 \     默认端口
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \  sock文件位置，用来做网络通信的，客户端连接服务器的时候用
-DDEFAULT_CHARSET=utf8 \    默认字符集。字符集的支持，可以调
-DEXTRA_CHARSETS=all \   扩展的字符集支持所有的
-DDEFAULT_COLLATION=utf8_general_ci \  支持的
-DWITH_READLINE=1 \    上下翻历史命令
-DWITH_SSL=system \    使用私钥和证书登陆（公钥）  可以加密。 适用与长连接。坏处：速度慢
-DWITH_EMBEDDED_SERVER=1 \   嵌入式数据库
-DENABLED_LOCAL_INFILE=1 \    从本地倒入数据，不是备份和恢复。
-DWITH_INNOBASE_STORAGE_ENGINE=1  默认的存储引擎，支持外键


[root@rpm mysql-5.7.27]# make -j4 && make install
如果安装出错，想重新安装：
    不用重新解压，只需要删除安装目录中的缓存文件CMakeCache.txt

初始化：

[root@rpm mysql-5.7.27]# cd /usr/local/mysql
[root@rpm mysql]# chown -R mysql.mysql .
[root@rpm mysql]# ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
---初始化完成之后，一定要记住提示最后的密码用于登陆或者修改密码

初始化，只需要初始化一次

[root@rpm mysql]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql     #指定安装目录
datadir=/usr/local/mysql/data  #指定数据存放目录

启动mysql：

[root@rpm mysql]# ./bin/mysqld_safe --user=mysql &
启动之后再按一下回车！即可后台运行
[root@rpm mysql]# netstat -lntp | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      28550/mysqld

登入mysql

[root@rpm mysql]# /usr/local/mysql/bin/mysql -uroot -p'dewuqdQxa4&t'

3. mysql基础

编译安装

[root@rpm mysql]# ls
bin      COPYING-test  docs     lib         README       share
COPYING  data          include  mysql-test  README-test  support-files
  • bin目录：用于放置一些可执行文件，如：mysql、mysqld、mysqlbinlog等
  • include目录：用于放置一些头文件，如：mysql.h、mysql_ername.h等
  • lib目录：用于放置一系列库文件
  • share目录：用于存放字符集、语言等信息

yum安装

  • /var/lib/mysql 存放数据文件
  • /usr/share/mysql 用于存放字符集、语言等信息

配置文件基本参数

[root@rpm mysql]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
validate_password=off   添加后可设置弱密码强度
lower_case_table_names=1  不区分大小写

扩展：
key_buffer = 384M  //key_buffer是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)。索引被所有的线程共享，key_buffer的大小视内存大小而定
table_open_cache  = 512  //MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，才会去磁盘上读取。默认值64, 假定系统有200个并发连接，则需将此参数设置为200*N(N为每个连接所需的文件描述符数目)；当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上
max_allowed_packet = 4M  //接受的数据包大小；增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当你发出长查询或MySQLd必须返回大的结果行时MySQLd才会分配更多内存。 该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出
sort_buffer_size = 2M  //MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小
read_buffer_size = 2M  //读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。 如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能
thread_concurrency      = 8  //最大并发线程数，取值为服务器逻辑CPU数量×2
max_connections = 1000 //MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值

4.数据库引擎

​ 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

InnoDB存储引擎

​ InnoDB是事务性型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键；InnoDB是默认的MySQL引擎。

特点：

​ 支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交和回滚。

MyISAM存储引擎

​ MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。

特点：

​ 插入数据块，空间和内存使用比较低。如果表主要是用于插入新纪录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

MEMORY存储引擎

​ MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

特点：

​ 所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMORY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

如何选择引擎

​ 如果要提供提交、回滚、崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择；如果数据表主要用来插入和查询记录，则MyISAM引擎能够提供较高的处理效率；如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎；MySQL中使用该引擎作为临时表，存放查询的中间结果；如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive。

​ 如果哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。

存储引擎查看

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

​ Support列的值表示某种引擎是否使用：YES表示可以使用、NO表示不能使用、DEFAULT表示该引擎为当前默认的存储引擎。

5. mysql事务

​ MySQL事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如：信箱、文章等等，这要，这些数据库操作语句就构成一个事务。

​ 在MySQL中只有使用了InnoDB数据库引擎的数据库或表才支持事务。

​ 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

​ 事务用来管理INSERT、UPDATE、DELETE语句。

满足事务条件

  • 原子性：一个事务（transaction）中的所有操作，要么全部完全，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完成符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

了解

MySQL事务处理主要有两种方法：

  1. 用 BEGIN，ROLLBACK，COMMIT来实现
    • BEGIN 开始一个事务
    • ROLLBACK 事务回滚
    • COMMIT 事务确认
  2. 直接用 SET 来改变 MySQL 的自动提交模式：
    • SET AUTOCOMMIT=0 禁止自动提交
    • SET AUTOCOMMIT=1 开启自动提交
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

注意：在编写应用程序时，最好事务的控制权限交给开发人员

6. 数据库锁

​ 锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂；相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

​ MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。给MyISAM表显示加锁，一般是为了一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。

锁分类

  • 表级锁：开销小，加锁块；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  • 页面锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

案例

​ 有一个订单表orders，其中记录有订单的总金额total，同时还有一个订单明细表order_detail，其中记录有订单每一产品的金额小计subtotal，假设我们需要检查这两个表的金额合计是否相等，可能就需要执行如下两条SQL：

mysql> SELECT SUM(total) FROM orders;   
mysql> SELECT SUM(subtotal) FROM order_detail;

​ 这时，如果不先给这两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中order_detail表可能已经发生了改变。因此，正确的方法应该是：

mysql> LOCK tables orders read local,order_detail read local;
mysql> SELECT SUM(total) FROM orders;
mysql> SELECT SUM(subtotal) FROM order_detail;
mysql> Unlock tables;

​ 上面的例子在LOCK TABLES时加了‘local’选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾插入记录。

​ 在用LOCKTABLES给表显式加表锁是时，必须同时取得所有涉及表的锁，并且MySQL支持锁升级；也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MySQL问题一次获得SQL语句所需要的全部锁。

7. 外键

​ 在实际开发的项目中，一个健壮数据库中的数据一定有很好的参照完整性。例如学生档案和成绩单两张表，如果成绩单中有张三的成绩，学生档案中张三的档案却被删除了，这样就会产生垃圾数据或者错误数据。为了保证数据的完整性，将两张表之间的数据建立关系，因此就需要在成绩表中添加外键约束。

​ 外键是指引用另外一个表中的一列或多列数据，被引用的列应该具有主键约束或者唯一性约束。外键用来建立和加强两个表数据之间的连接。

外键的定义

  • 外键是某个表中的一列，它包含在另一个表的主键中
  • 外键也是索引的一种，是通过一张表中的一列指向另一张表中的主键，来对两张表进行关联
  • 一张表可以有一个外键，也可以存在多个外键，与多张表进行关联

外键的作用

​ 外键的主要作用是保证数据的一致性和完整性，并且减少数据冗余