MySQL从安装到优化

一、安装MySQL

1、MySQL下载地址

2、安装及卸载MySQL

将下载的压缩包进行解压，并在解压获得的文件夹内创建my.ini文件，并编辑该文件输入如下内容

[mysql]
 
# 设置MySQL客户端默认字符集
default-character-set = utf8
 
[mysqld]
 
# 设置MySQL的安装根目录
basedir = 修改为MySQL的安装根目录，例：D:\develop\mysql-8.0.26-winx64
 
# 设置MySQL数据库的数据存放目录
datadir = 修改为MySQL数据库的数据存放目录，例：D:\develop\mysql-8.0.26-winx64\data
 
# 设置端口3306
port = 3306
 
# 允许最大连接数
max_connections = 200
 
# 服务端使用的字符默认为utf8
character-set-server = utf8
 
# 创建新表时默认使用的存储引擎
default-storage-engine = INNODB

配置环境变量

此电脑右键→属性，点击高级系统设置

点击环境变量

在系统变量中新建MYSQL_HOME

在系统变量中找到并双击Path

点击新建并添加%MYSQL_HOME%\bin

初始化MySQL

在cmd窗口中敲入如下命令

mysqld --initialize-insecure

如果出现上面窗口，表示初始化成功

注册MySQL服务

在cmd窗口中敲入如下命令

mysqld -install

如果出现上面窗口，表示安装好了MySQL服务了。

启动MySQL服务

在cmd窗口中敲入如下命令

net start mysql

修改默认账户密码

在cmd窗口中敲入如下命令

# 这里的`1234`就是指默认管理员(即root账户)的密码，可以自行修改成你喜欢的。
mysqladmin -u root password 1234

如果出现上面窗口，表示你的MySQL就安装成功了。

停止MySQL服务

在cmd窗口中敲入如下命令

net stop mysql

如果出现上面窗口，表示你的MySQL服务已经停止。

卸载MySQL

在cmd窗口中敲入如下命令

mysqld -remove mysql

最后删除MySQL目录及相关的环境变量，即可完全卸载MySQL服务。

二、MySQL的基础使用

创建数据库命令CREATE DATABASE …

-- 直接创建数据库
CREATE DATABASE 数据库名称 CHARACTER SET 字符集 COLLATE 校对规则;
-- 先查询后创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名称 CHARACTER SET 字符集 COLLATE 校对规则;

创建表格命令CREATE TABLE …

/*
 * 第一个字段被指定为整数类型（INT），不允许为空值（NOT NULL），并且会自动递增（AUTO_INCREMENT）。
 * 第二个字段被设置为可变字符串类型（VARCHAR），最大长度30，不允许为空值。
 * 第三个字段被设置为整数类型，不允许为空值。
 * PRIMARY KEY (第一个字段)指定第一个字段作为表的主键。
 * ENGINE=InnoDB设置存储引擎为InnoDB。
 * DEFAULT CHARSET=utf8mb3设置默认字符集为utf8mb3。
 */
CREATE TABLE 表名 (
    第一个字段 INT NOT NULL AUTO_INCREMENT,
    第二个字段 VARCHAR(30) NOT NULL,
    第三个字段 INT NOT NULL,
    PRIMARY KEY (第一个字段)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

新增数据命令INSERT

-- 新增单条数据
INSERT INTO 表名 (第一个字段,第二个字段,...) values (第一个值,第二个值,...);
-- 新增多条数据
INSERT INTO 表名 values (第一个值,第二个值,...),(第一个值,第二个值,...);

查询数据命令SELECT

-- 查询当前表中所有数据
SELECT * FROM 表名;
-- 根据条件查询表中指定数据
SELECT 查询字段名,... FROM 表名 WHERE 条件;
-- 去除重复数据查询当前表数据
SELECT DISTINCT 字段列表 FROM 表名;
-- 内连接查询 -> 返回两个表中匹配的行。
SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
-- 左外连接查询 -> 返回左表中的所有行以及与右表匹配的行。
SELECT 表1.字段名称,表2.字段名称,... FROM 表1 LEFT JOIN 表2 ON 表1关联表2条件 WHERE 条件;
-- 右外连接查询 -> 返回右表中的所有行以及与左表匹配的行。
SELECT 表1.字段名称,表2.字段名称,... FROM 表1 RIGHT JOIN 表2 ON 表1关联表2条件 WHERE 条件;

修改数据命令UPDATE

UPDATE 表名 SET 字段名,... = 值,... WHERE 条件;

删除数据命令DELETE

DELETE FROM 表名 WHERE 条件;

分组数据命令GROUP BY，通常与聚合函数（如SUM、AVG、COUNT等）一起使用，将数据按照指定的列进行分组，然后对每个组进行相应的计算。

SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id;

分组数据过滤命令HAVING，通常与GROUP BY子句一起使用，用于对分组后的结果进行筛选。HAVING子句的作用类似于WHERE子句，但WHERE子句用于在分组之前筛选行，而HAVING子句用于在分组之后筛选组。

-- 首先使用 GROUP BY 子句按 product_id 对数据进行分组，然后使用 HAVING 子句筛选出销售总额大于 1000 的分组。
SELECT product_id, SUM(sales_amount) AS total_sales FROM sales GROUP BY product_id HAVING SUM(sales_amount) > 1000;

排序数据命令ORDER BY

-- 按照指定列升序排序(默认)
SELECT * FROM 表名 ORDER BY 指定的排序字段 ASC; 
-- 按照指定列降序排序
SELECT * FROM 表名 ORDER BY 指定的排序字段 DESC;

限制结果集命令LIMIT

-- 获取前指定条数数据
SELECT * FROM 表名 LIMIT 指定条数;
-- 从指定位置获取指定条数数据
SELECT * FROM 表名 LIMIT 指定位置,指定条数;

查询SQL关键字使用位置

SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段 HAVING 分组之后的条件 ORDER BY 排序 LIMIT 分页限定

运算符

符 号	功 能
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN…AND…	在…和…之间（都包含）
IN(…)	在…之中（多选一）
LIKE 占位符	模糊查询 _ 匹配单个字符 % 匹配多个字符
IS NULL	是NULL
IS NOT NULL	不是NULL
AND 或 &&	并且
OR 或 ||	或者
NOT 或 !	非，不是

三、修改表结构

查询当前数据库下所有表名称

SHOW TABLES;

查看表结构

DESCRIBE 表名;

查看表索引

SHOW INDEXES FROM 表名;

添加表字段

ALTER TABLE 表名 ADD 字段名称 数据类型;

修改表名

ALTER TABLE 旧表名 RENAME 新表名;

修改字段数据类型

ALTER TABLE 表名 MODIFY 字段名称 新字段数据类型;

修改字段名称及数据类型

ALTER TABLE 表名 CHANGE 旧字段名称 新字段名称 新字段数据类型;

删除整张表

-- 直接删除整张表
DROP TABLE 表名;
-- 先判断后删除整张表
DROP TABLE IF EXISTS 表名;

删除表字段

ALTER TABLE 表名 DROP 字段名称;

修改主键

-- 添加主键
ALTER TABLE 表名 ADD PRIMARY KEY (字段名称);
-- 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;

四、数据库事务

1、概念

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行，要么都不执行 。事务是一个不可分割的工作逻辑单元事务必须具备以下四个属性，简称 ACID 属性：

原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。

一致性（Consistency）：当事务完成时，数据必须处于一致状态。

隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。

永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性。

2、事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT 保存点名称，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT 保存点名称 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO 保存点名称 把事务回滚到标记点；

SET TRANSACTION 事务隔离级别 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

3、MySQL事务处理两种方法

用 BEGIN、ROLLBACK、COMMIT 来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

直接用 SET 来改变 MySQL 的自动提交模式

-- 禁止自动提交
SET AUTOCOMMIT = 0
-- 开启自动提交
SET AUTOCOMMIT = 1

4、事务的隔离级别

READ UNCOMMITTED

读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。

READ COMMITTED

读已提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

REPEATABLE READ

可重复读，就是在开始读取数据（事务开启）时，不再允许修改操作。

SERIALIZABLE

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

在 MySQL 数据库中，支持上面四种隔离级别，默认的为 Repeatable read (可重复读)。

五、MySQL数据库优化

1、索引

索引（Index）是帮助 MySQL 高效获取数据的数据结构。索引就是加快检索表中数据的方法。在数据库中，索引能使数据库程序迅速地找到表中的数据，而不必扫描整个数据库。MySQL 数据库基本的索引类型：普通索引、唯一索引、主键索引、全文索引、组合索引

索引的使用原则

1、选择唯一性索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

2、为where，group by，order by，on 从句中出现的字段建立索引。根据查询条件，建立索引，如果查询条件不止一个时，使用组合索引。

3、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以 怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

4、尽量使用数据量少的索引：如果索引的值很长，那么查询的速度会受到影响。

5、尽量使用前缀来索引：如果索引字段的值很长，最好使用值的前缀来索引。

6、删除不再使用或者很少使用的索引。

7、遵守最左前缀匹配原则。

8、尽量选择区分度高的列作为索引：区分度的公式是表示字段不重复的比例。

9、索引列不能参与计算，保持列“干净”：带函数的查询不参与索引。

10、尽量的扩展索引，不要新建索引。

11、建立索引时，字段不能有NULL值。

普通索引

普通索引是最基本的索引，它没有任何限制。

直接创建普通索引

CREATE INDEX index_name ON table_name (column_name);

修改表结构的方式添加普通索引

ALTER TABLE table_name ADD INDEX index_name ON (column_name);

创建表的时候同时创建普通索引

CREATE TABLE 表名 (
第一个字段 数据类型,
第二个字段 数据类型,
第三个字段 数据类型,
...,
INDEX index_name (索引字段)
);

删除索引

DROP INDEX index_name ON table_name;

唯一索引

唯一索引索引列的值必须唯一，但允许有空值。 如果是组合索引，则列值的组合必须唯一。

直接创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name(column_name);

修改表结构的方式创建唯一索引

ALTER TABLE table_name ADD UNIQUE index_name ON (column_name);

创建表的时候同时创建普通索引

CREATE TABLE 表名 (
第一个字段 数据类型,
第二个字段 数据类型,
第三个字段 数据类型,
...,
UNIQUE index_name (索引字段)
);

主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

CREATE TABLE 表名 (
第一个字段 数据类型,
第二个字段 数据类型,
第三个字段 数据类型,
...,
PRIMARY KEY (主键字段)
)

组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字 段，索引才会被使用。使用组合索引时遵循最左前缀原则。

ALTER TABLE table_name ADD INDEX index_name (索引字段,索引字段...);

全文索引

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。全文索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的 where 语句 的参数匹配。全文索引配合 match against 操作使用，而不是一般的 where 语句加 like。它可以在 create table，alter table ，create index 使用，不过目前只有 char、varchar、text 列上可以创建全文索引。

在数据量较大时候，先将数据放入一个没有全局索引的表中，然后再用 CREATE index 创建全文索引，要比先为一张表建立全文索引然后再将数据写入的速度快很多。

创建表的时候创建全文索引

CREATE TABLE 表名 (
第一个字段 数据类型,
第二个字段 数据类型,
第三个字段 数据类型,
content text CHARACTER NULL ,
...,
PRIMARY KEY (主键字段)
FULLTEXT (content)
);

直接创建全文索引

CREATE FULLTEXT INDEX index_name ON 表名(content)

修改表结构的方式创建全文索引

ALTER TABLE 表名 ADD FULLTEXT index_name(content)

索引的优点

1、创建唯一性索引，保证数据库表中每一行数据的唯一性。

2、大大加快数据的检索速度，这也是创建索引的最主要的原因。

3、加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

4、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

5、通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

索引的缺点

1、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定 的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

3、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

索引失效时机

1、在where子句中使用!=、<、>操作符。

2、在where子句中使用or来连接条件。

3、在where子句中使用in、not in关键字。

4、在where子句中进行NULL值判断。

5、在查询条件表达式中使用聚合函数及进行计算操作。

6、使用like模糊查询时，两侧使用%，只有使用后置%才能使用索引，例如C%格式能使用索引。

2、分库分表

分库分表有垂直切分和水平切分两种。

垂直切分:即将表按照功能模块、关系密切程度划分出来，部署到不同的库上。如果数据库是因为表太多而造成海量数据，并且项目的各项业务逻辑划分清晰、 低耦合，那么规则简单明了、容易实施的垂直切分必是首选。

水平切分:当一个表中的数据量过大时，我们可以把该表的数据按照某种规则，进行划分，然后存储到多个结构相同的表，和不同的库上。如果数据库中的表并不多，但单表的数据量很大、或数据热度很高，这种情况 之下就应该选择水平切分。

3、读写分离

在实际的应用中，绝大部分情况都是读远大于写。Mysql 提供了读写分离的机制， 所有的写操作都必须对应到 Master，读操作可以在 Master 和 Slave 机器上进行， Slave 与 Master 的结构完全一样，一个 Master 可以有多个 Slave,甚至 Slave 下 还可以挂 Slave,通过此方式可以有效的 DB 集群的每秒查询率。

所有的写操作都是先在 Master 上操作，然后同步更新到 Slave 上，所以从 Master 同步到 Slave 机器有一定的延迟，当系统很繁忙的时候，延迟问题会更 加严重，Slave 机器数量的增加也会使这个问题更加严重。此外，可以看出 Master 是集群的瓶颈，当写操作过多，会严重影响到 Master 的稳定性，如果 Master 挂 掉，整个集群都将不能正常工作。

使用读写分离参考建议

1、当读压力很大的时候，可以考虑添加 Slave 机器的分式解决，但是当 Slave 机器达到一定的数量就得考虑分库了。

2、 当写压力很大的时候，就必须得进行分库操作。

4、代码优化

1、在进行更新操作时，使用updateByPrimaryKeySelective仅更新需要更新的字段。

2、在添加多条数据时，使用批量添加方式添加数据。

5、慢SQL优化

1) 如何定位慢SQL

执行下列命令需要先进登录MySQL

1> 查询是否开启慢SQL日志

// 查询是否开启慢SQL日志
show variables like "%slow%";

2>开启慢SQL日志

临时开启慢SQL日志

set global slow_query_log = on;

查询慢SQL的时间限制

show variables like "long_query_time";

修改慢SQL的时间限制

//设置记录慢SQL的时间限制为2s，即超过设置时间则认定为慢SQL
set long_query_time = 2;

Windows系统永久开启慢SQL日志——在my.ini配置文件中添加下列配置

# 是否开启慢SQL日志 1表示开启；0表示关闭
slow_query_log = 1
# 慢SQL日志存储位置
slow_query_log_file = D:\mysql-8.0.26-winx64\data\DESKTOP-A0OVLVI-slow.log
# 慢SQL的时间限制，2秒以上的查询会被记录
long_query_time = 2  

Linux系统永久开启慢SQL日志——在my.cnf配置文件中添加下列配置

# 是否开启慢SQL日志 1表示开启；0表示关闭
slow_query_log = 1
# 慢SQL日志存储位置
slow_query_log_file = /var/log/mysql/mysql-slow.log
# 慢SQL的时间限制，2秒以上的查询会被记录
long_query_time = 2

2) 慢SQL优化

1> 分析慢SQL

使用explain关键字分析SQL的执行计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
唯一标识	查询类型	表名	匹配的分区	访问方式	查询时可能使用的索引	实际使用的索引	索引的长度	索引引用了那些列	预计要扫描的行数	按条件过滤的数据百分比	执行情况的说明

2> 部分explain执行计划参数详解

select_type——表示查询中每个select子句的类型

参数	含义
SIMPLE	简单查询，不使用连接或者子查询
PRIMARY	子查询中的最外层查询
UNION	UNION中的第二个或者后面的SELECT语句
DEPENDENT UNION	UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT	UNION的结果，UNION语句中第二个SELECT开始后面所有SELECT
SUBQUERY	子查询中的第一个SELECT，结果不依赖于外部查询
DEPENDENT SUBQUERY	子查询中的第一个SELECT，依赖于外部查询
DERIVED	派生表的SELECT, FROM子句的子查询
UNCACHEABLE SUBQUERY	一个子查询的结果不能被缓存，必须重新评估外链接的第一行

type——表示MySQL在表中找到所需行的方式

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

参数	含义
ALL	Full Table Scan， MySQL将遍历全表以找到匹配的行
index	Full Index Scan，index与ALL区别为index类型只遍历索引树
range	只检索给定范围的行，使用一个索引来选择行
ref	表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref	类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system	当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL	MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

Extra——MySQL解决查询的详细信息

参数	含义
Using where	不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary	表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using filesort	当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
Using join buffer	该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where	这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
Select tables optimized away	这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
No tables used	Query语句中使用from dual 或不含任何from子句

=== 注意 ===

explain关键字只能用于select语句执行计划的分析

=== 参考文档 ===

mysql中explain的用法是什么