Mysql
一、安装
1、mysql80安装
- 新建
my.[ini文件]
不要自己新建data文件
[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\Dev\mysql-8.0.36-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\Dev\mysql-8.0.36-winx64\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8
- 管理员身份运行
CMD
# 1、cd 到bin 目录
cd D:\mysql-8.0.27-winx64\bin
# 2、初始化数据库
mysqld --initialize --console
# 3、保存密码，会自动生成data文件
- 遇到错误：找不到VCRUNTIME140.dll
下载并安装下面的运行库即可（64位系统选择X64）
https://docs.microsoft.com/zh-CN/cpp/windows/latest-supported-vc-redist?view=msvc-170
- 安装mysql80服务并修改密码
# 1、管理员身份
mysqld -install MySQL80
# 2、启动服务器
net start mysql
# 3、进入mysql
mysql -u root -p
# 4、修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
2、mysql57安装
- 新建
my.[ini文件]
不要自己新建data文件
[mysqld]
# 设置3305端口
port=3305
# 设置mysql的安装目录
basedir=D:\Dev\mysql-5.7.44-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\Dev\mysql-8.0.36-winx64\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3305
default-character-set=utf8
- 管理员身份运行
CMD
# 1、cd 到bin 目录
cd D:\Dev\mysql-5.7.44-winx64\bin
# 2、初始化数据库
mysqld --initialize --console
# 3、保存密码，会自动生成data文件
- 遇到错误：找不到VCRUNTIME140.dll
下载并安装下面的运行库即可（64位系统选择X64）
https://docs.microsoft.com/zh-CN/cpp/windows/latest-supported-vc-redist?view=msvc-170
- 安装mysql80服务并修改密码
# 1、管理员身份
mysqld -install MySQL57
# 2、启动服务器
net start mysql
# 3、进入mysql
mysql -u root -p
# 4、修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
3、同时安装57 80 两个数据库
下载mysql zip
MySQL :: Download MySQL Community Server (Archived Versions)
my.ini 文件
！！！！！！！不可以自己创建data文件夹
[mysqld]
# 设置3305端口 mysql默认端口为3306，这里为了不和mysql80的服务冲突才设置为3305
port=3305 ←←←←←修改处1
# 设置mysql的安装目录
basedir=D:\mysql\mysql-5.7.36-winx64 ←←←←←修改处2
# 设置mysql数据库的数据的存放目录
datadir=D:\mysql\mysql-5.7.36-winx64\data ←←←←←修改处3
# 允许最大连接数
max_connections=200
# 允许连接失败的次数
max_connect_errors=10
# 服务端使用的字符集默认为utf8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3305 ←←←←←修改处4
default-character-set=utf8
# 设置packet最大允许数据大小
max_allowed_packet=16M
！！！！！！ 必须使用管理员cmd 命令提示符 ，不可以使用终端
# 删除服务 cd 到 bin 目录
mysqld --remove mysql57
# 安装服务 cd 到 bin 目录
mysqld.exe --install mysql57 --defaults-file="D:\Dev\mysql-5.7.32-winx64\my.ini"
# 初始化 数据库 ，记住密码（不然，baidu，使用不用密码进入）
mysqld --initialize --console
# 打开命令行：regedit
# 将mysql57 ImagePath中的 --default-file="D:\mysql\mysql-5.7.36-winx64\my.ini" 删掉
计算机\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\
# 启动 登录
net start mysql57
mysql -u root -p
不记密码登录方式
-
停止MySQL服务器。你可以使用以下命令：
net stop MySQL -
进入MySQL的安装目录，例如：
cd D:\Dev\mysql-5.7.32-winx64\bin。 -
启动MySQL服务器，并跳过权限检查。使用以下命令：
mysqld --skip-grant-tables -
打开另一个命令提示符窗口，并进入MySQL的bin目录。
-
运行以下命令以连接到MySQL服务器：
mysql -u root -
这将连接到MySQL服务器，并将你提升为root用户。然后，你可以执行以下命令来重置root密码：
UPDATE mysql.user SET authentication_string=PASSWORD('new_password') WHERE User='root'; FLUSH PRIVILEGES;将
new_password替换为你想要设置的新密码。 -
完成后，关闭第一个命令提示符窗口中的MySQL服务器（按Ctrl+C）。
-
重新启动MySQL服务器：
net start MySQL -
现在，你应该能够使用新密码连接到MySQL服务器：
mysql -u root -p Enter password: new_password
二、高级
1、事务
-
原子性（Atomicity）：事务中的所有操作要么全部成功执行，要么全部回滚。如果事务中的任何一个操作失败，整个事务将回滚到初始状态。
-
一致性（Consistency）：事务执行前后，数据库的完整性约束没有被破坏。换句话说，事务将把数据库从一个一致的状态转移到另一个一致的状态。
-
隔离性（Isolation）：每个事务的执行都相互隔离，互不干扰。一个事务的修改在提交之前对其他事务是不可见的，保证了数据的一致性。
-
持久性（Durability）：一旦事务提交，其结果就是永久性的，即使系统发生故障或重启，提交的事务结果也不会丢失。
在MySQL中，通过以下方式来管理事务：
-
开始事务：使用
START TRANSACTION或者BEGIN语句来开始一个事务。 -
提交事务：使用
COMMIT语句来提交事务，将事务中的操作永久保存到数据库。 -
回滚事务：使用
ROLLBACK语句来回滚事务，撤销事务中的所有操作。 -
设置保存点：使用
SAVEPOINT语句来设置一个保存点，可以在事务执行过程中回滚到保存点的状态。 -
事务控制命令：除了上述命令，还有一些其他的事务控制命令，如
SET AUTOCOMMIT用于设置是否自动提交事务。
以下是一个简单的MySQL事务示例：
-- 开启事务
start transaction
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;
事务隔离级别
注意：事务隔离级别越高，数据越安全，但是性能越低
- 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
-
读未提交（Read uncommitted）：一个事务可以读取另一个事务未提交的数据。这种隔离级别会导致脏读、不可重复读、幻读等问题。
-
读已提交（Read committed）：一个事务只能读取另一个事务已经提交的数据。这种隔离级别可以避免脏读，但可能出现不可重复读和幻读问题。
-
可重复读（Repeatable read）：一个事务在执行过程中多次读取同一行数据会得到同样的结果，除非该行数据被该事务本身改变。这种隔离级别可以避免脏读、不可重复读问题，但可能出现幻读问题。
-
序列化（Serializable）：最高的隔离级别，所有事务按照顺序依次执行，可以避免脏读、不可重复读和幻读问题，但是可能会出现较高的并发性能问题。
- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL [隔离级别]
2、docker 安装 mysql
创建容器
docker run -d \
-p 3306:3306 \
--name mysql57 \
-v /mysqldata/mysql/log:/var/log/mysql \
-v /mysqldata/mysql/data:/var/lib/mysql \
-v /mysqldata/mysql/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
mysql:5.7
3、索引
一、索引操纵
索引操作
# 查询索引
show index from tb_user;
-- 独立索引
create index idx_user_name on tb_user(name);
-- 唯一索引
create unique index idx_user_phone on tb_user(phone);
-- 联合索引(使用频繁的在左)
create index idx_user_pro_age_sta on tb_user(profession,age,status);
-- email
create index idx_user_email on tb_user(email);
# 删除索引
drop index idx_user_email on tb_user;
查询sql 执行频率
show global status like 'Com_______';
二、最左侧前缀选择
例如，如果有以下复合索引 (column1, column2, column3)，那么以下查询可以充分利用索引：
SELECT * FROM table_name WHERE column1 = 'value' AND column2 = 'value';
而以下查询将无法充分利用索引：
SELECT * FROM table_name WHERE column2 = 'value' AND column1 = 'value';
在第一个查询中，查询条件从索引的最左侧列 column1 开始，并且按照索引的顺序进行匹配。而在第二个查询中，查询条件并没有从最左侧列开始，因此索引无法有效使用，可能会导致查询性能下降。
三、索引失效
在 MySQL 中，索引可能会失效的情况有多种，其中一些常见的情况包括：
-
不使用索引列：当查询条件中的列没有被索引，或者查询条件不满足最左前缀原则时，索引会失效。
-
使用函数操作：如果在查询条件中对索引列进行函数操作（如
CONCAT,UPPER,LOWER等），索引通常会失效。 -
数据量过大：如果表中数据量很大，MySQL 可能会选择全表扫描而不是使用索引，导致索引失效。
-
表结构变更：当对表进行 DDL 操作（如 ALTER TABLE）或者数据更新（如 INSERT, UPDATE, DELETE）时，索引可能会失效，需要重新生成或重建索引。
-
数据类型不匹配：如果查询条件中的数据类型与索引列的数据类型不匹配，索引可能无法被正确使用。
-
统计信息不准确：MySQL 使用统计信息来生成查询计划，如果统计信息不准确或过时，可能导致 MySQL 选择不合适的执行计划而使索引失效。
为避免索引失效，可以采取以下措施：
- 确保查询条件中的列使用了索引；
- 避免在查询条件中对索引列进行函数操作；
- 定期更新索引统计信息；
- 根据实际需求设计合适的索引；
- 避免频繁的表结构变更。
4、锁
一、全局锁
-
对数据库进行进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其他的DDL、
-
DML全部都处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态，而数据备份就是查询操作。
-
那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保证了数据的一致性和完整性。
状态为只读
- 加全局锁
flush tables with read lock;
- 数据备份
mysqldump -h 192.168.101.100 -u root –p root itcast > itcast.sql
- 释放锁
unlock tables;
数据库中加全局锁，是一个比较重的操作，存在以下问题：
-
如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
-
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。
-
不加锁保证数据一致性
在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。
mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql
二、表级锁
- 表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
1）表锁
- 表共享读锁（read lock）
- 只能读
- 表独占写锁（write lock）
- 其他客户端，读和写进入阻塞状态
语法
- 加锁：lock tables 表名… read/write。
- 释放锁：unlock tables / 客户端断开连接 。
# 加读锁
lock tables xx read;
# 加写锁
lock tables xx write;
# 释放表锁
unlock tables;
2）元数据锁
-
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性
-
这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。
3）意向锁:避免表行锁冲突
-
意向共享锁（IS）：与 表锁(read) 兼容，与 表锁(write) 互斥
-
意向排他锁（IX）：与 表锁(write) 及 表锁(read) 互斥。意向锁之间不会互斥。
示例
- 假设有两个事务 A 和 B，它们同时操作一个表中的不同行数据。
- 事务 A 需要对某个特定的行进行排他锁定，而事务 B 则需要对表中的所有行进共享锁定。
- 事务 A 的操作流程如下：
事务A：
START TRANSACTION;
-- 获取意向排他锁
SELECT * FROM table_name WHERE id = 1 FOR UPDATE;
-- 对指定行进行更新操作
UPDATE table_name SET column_name = 'new_value' WHERE id = 1;
COMMIT;
事务B：
START TRANSACTION;
-- 获取意向共享锁
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
-- 对表中的所有行进行查询操作
SELECT * FROM table_name;
COMMIT;
-
在这个示例中，事务 A 在获取排他锁之前会先获取意向排他锁（IX 锁），以指示其对该表的排他锁定操作。事务 B 则在获取共享锁之前会先获取意向共享锁（IS 锁），以指示其对该表的共享锁定操作。
-
意向锁的存在使得事务 A 和事务 B 可以并发地操作数据库表，避免了锁冲突。事务 A 获取排他锁时，虽然事务 B 持有意向共享锁，但不会造成冲突。同样地，事务 B 获取共享锁时，不会与事务 A 的排他锁冲突。
-
通过使用意向锁，MySQL 可以在并发访问时更好地协调和管理锁的获取和释放，提高数据库系统的并发性能。
三、行级锁
- 行锁（Row Lock）是一种数据库管理系统中的锁机制，用于控制对表中行数据的并发访问。行锁是为了解决多个事务同时访问同一行数据时可能出现的读写冲突问题而引入的。
-
共享锁：也称为读锁，允许多个事务对同一行数据进行读取操作，但不允许任何事务对该行数据进行修改操作。多个事务可以同时持有共享锁。
-
排他锁：也称为写锁，只允许一个事务对同一行数据进行修改或删除操作*，其他事务不能对该行数据进行任何读取或修改操作*。排他锁只能由一个事务持有，直到事务结束或显式地释放锁。
739
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
