mysql介绍及实操_mysqlcsdn

MYSQL官网
MYSQL内置的函数

1：介绍

1.1：简介

MySQL 软件提供了一个非常快速、多线程、多用户和强大的 SQL（结构化查询语言）数据库服务器。MySQL Server 旨在用于关键任务、重负载生产系统以及嵌入到大规模部署的软件中

1：存储引擎

InnoDB是 MySQL 中的默认存储引擎。

1.2：安装

1.3：mysql服务命令操作

2：使用

2.1：数据类型

2.1.1：数据类型

整数类型（精确值）——INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT
定点类型（精确值）——DECIMAL、NUMERIC
浮点类型（近似值）——FLOAT、DOUBLE
字符串数据类型为CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM和 SET。
二进制类型 
表示时间值的日期和时间数据类型有 DATE、 TIME、 DATETIME、 TIMESTAMP和 YEAR
枚举 ENUM
集合 set
json json
空间数据类型存储坐标等：GEOMETRY、POINT、 LINESTRING、POLYGON以及集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION 。
1
2
3
4
5
6
7
8
9
10

1、数值类型：

2、时间类型简介：

3、字符串类型：文本类型和二进制类型

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
VARCHAR（M）
在utf8mb4字符集中，最多需要4个字节来表示一个字符，所以 65535 / 4 = 16383 。而在utf8字符集中，最多需要3个字节来表示一个字符，所以 65535 / 3 = 21845。

由此来看，在设置 M 的大小时，起决定作用的并不是 M 的有效值范围（0 ~ 65535），而是 M * 字符集的最大字节数不能超过65535个字节。

有 4 种 TEXT 类型（可以用存储新闻、文章博客等。可对应前端的markdown富文本类型）：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

以utf8编码计算的话
LANGTEXT：4294967295/3=1431655765个汉字，14亿，存储空间占用：4294967295/1024/1024/1024=4G的数据；
MEDIUMTEXT:16777215/3=5592405个汉字，560万，存储空间占用：16777215/1024/1024=16M的数据；
TEXT：65535/3=21845个汉字，约20000，存储空间占用：65535/1024=64K的数据；
1
2
3
4

4、BLOB 是一个二进制大对象，比如可以存储图片、音频和视频等二进制数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

1：mysql支持的数据类型

2.1.2：java如何选择合适的mysql数据类型

2.2：函数和运算符

• 1：不同类型的转换： CAST(字段名 AS 被转的类型)

SELECT CAST(字段名 AS SIGNED) FROM DUAL; :
1

• 2：group by :对某列值进行去重后展示
• 3：count数据统计
  count（） 、count（1）会统计为null的个数，也会统计值为空的个数
  count（列名）不会统计值为null的个数，也会统计值为空的个数
  总结：count()会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。

举例：

内置的所有函数和运算符

2.3：sql使用

2.3.1：数据定义语句DDL

数据定义语句
对表、视图等的增删改查

1：表操作

1:创建表示例

CREATE TABLE `xv_configuration_t` (
  `uuid` varchar(50) NOT NULL COMMENT 'uuid',
  `target_id` varchar(50) NOT NULL COMMENT '关联uuid',
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5

PRIMARY KEY：主键唯一索引，其中所有键列必须定义为 NOT NULL. 
KEY | INDEX：KEY通常是 的同义词 INDEX。索引
FOREIGN KEY：外键
UNIQUE：索引UNIQUE创建一个约束，使得索引中的所有值都必须不同
1
2
3
4

2：视图操作

3：索引

创建索引的关键字[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY，其中任一个都可以，意思一样

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

index_type:  //索引的类型，可以选择B数和hash
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

方式 1：
CREATE TABLE t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);
方式 2：
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

1
2
3
4
5
6
7
8
9

2.3.2：数据操作语句DML

对表中数据的操作

2.4：数据库备份

为了保证数据安全和数据丢失，比如黑客勒索。
数据库备份脚本吗，数据会备份到.sql文件中

#!/bin/bash

#保存备份个数，备份10天数据
number=10
#备份保存路径
backup_dir=/opt/mount/mysql8/backup
#日期
dd=`date +%Y-%m-%d-%H-%M-%S`
#备份工具
tool=mysqldump

#mysql容器名，我的是docker安装的
name=mysql8
#用户名。如admin
username=**
#密码，你的喵喵
password=**
#将要备份的数据库,为所有的数据库，也可以指定具体的数据库名称，多个用空格隔开即可
database_name=--all-databases 

#2、如果备份文件夹不存在则创建
if [ ! -d $backup_dir ];
then     
            mkdir -p $backup_dir;
fi

#简单写法 mysqldump -u 用户 -p 密码 users > /root/mysqlbackup/users-$filename.sql
#$tool -u $username -p$password $database_name > $backup_dir/$database_name-$dd.sql
sudo docker exec ${name} sh -c 'exec mysqldump $database_name -u$username -p$password  ' > $backup_dir/$database_name-$dd.sql


#写创建备份日志
echo "create $backup_dir/$database_name-$dd.dupm" >> $backup_dir/log.txt

#找出需要删除的备份
delfile=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | head -1`

#判断现在的备份数量是否大于$number
count=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | wc -l`

if [ $count -gt $number ]
then
          #删除最早生成的备份，只保留number数量的备份
            rm $delfile
              #写删除文件日志
                echo "delete $delfile" >> $backup_dir/log.txt
fi
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

将脚本放到crontab中定期执行即可。比如

0 2 * * * /opt/mount/mysql8/backup.sh >> /opt/mount/mysql8/backup/backup_`date +\%F`.log
1

数据恢复

#1、复制备份文件.sql到容器中任一路径
#2、进入mysql客户端mysql-uroot -p 
#3、加载文件即可source /path/filename
#4、数据库查询是否恢复
1
2
3
4

3：优化

数据库性能取决于数据库级别的多个因素，例如表、查询和配置设置。这些软件结构会导致硬件级别的 CPU 和 I/O 操作，您必须将其最小化并尽可能提高效率。在处理数据库性能时，您首先要学习软件方面的高级规则和指南，并使用挂钟时间测量性能。当您成为专家时，您会更多地了解内部发生的事情，并开始衡量诸如 CPU 周期和 I/O 操作之类的事情。

典型用户的目标是从他们现有的软件和硬件配置中获得最佳的数据库性能。高级用户寻找机会改进 MySQL 软件本身，或开发自己的存储引擎和硬件设备以扩展 MySQL 生态系统。

• 在数据库级别进行优化

• 在硬件层面进行优化

• 平衡便携性和性能

3.1:在数据库级别进行优化

1、表的列数据结构是否正常，表结构是否正确
2、表是否建了合适的索引，索引是否被使用
3、表的存储引擎是否正确，选择事务存储引擎（例如 ） InnoDB 或非事务存储引擎（例如 ） MyISAM 对于性能和可伸缩性非常重要。
4、要配置的主要内存区域是InnoDB缓冲池和MyISAM键缓存。内存大小是否合适

3.2：优化SELECT sql语句

1、使用where语句。尽量减少返回的列和数据行数，对有索引的使用where索引提升速率。

1、范围优化。可使用range，or，and，between，in/not in，大于小于等缩小范围
尽量使用in而不是not in。
2、索引连接优化。多个查询索引的直接进行and或or连接，需要排序直接排序，只在内存进行一次排序。
3、join hash优化。用在多表连接上，可使用explain analyze对sql语句进行分析，查看执行过程中的相关 hash join等字眼。
开启相关参数hash_join=on或 hash_join=off，
增加join_buffer_size以使散列连接不会溢出到磁盘。
增加open_files_limit。
4、join优化。使用left join等表关联时小表在前，尽可能的返回更少的匹配数据，对于不合适的外连接考虑是否可以转为内连接（如外连接的where条件中部分条件一直为false）
5、is null优化：一个语句中where后的多个is null 判断优化器只对第一个进行优化。
1
2
3
4
5
6
7
8
9

3.2 索引优化

提高操作性能的最佳方法 SELECT是在查询中测试的一个或多个列上创建索引。索引条目就像指向表行的指针，允许查询快速确定哪些行与子句中的条件匹配WHERE，并检索这些行的其他列值。可以索引所有 MySQL 数据类型。
索引用于快速查找具有特定列值的行。如果没有索引，MySQL 必须从第一行开始，然后通读整个表以找到相关行。表越大，成本就越高。如果表有相关列的索引，MySQL 可以快速确定要在数据文件中间查找的位置，而无需查看所有数据。这比顺序读取每一行要快得多。

大多数 MySQL 索引（PRIMARY KEY、 UNIQUE、INDEX和 FULLTEXT）都存储在 B 树中。例外：空间数据类型的索引使用 R 树；MEMORY 表也​​支持散列索引；InnoDB使用倒排列表作为FULLTEXT索引。

创建索引示例：可以在建表和建表后添加两种方式

1：CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
2：ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;
1
2

索引是可以创建多列的

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);
1
2
3
4
5
6
7

3.2.1 主键优化

主键是唯一的，它有一个关联索引，用于快速查询性能。查询性能受益于NOT NULL优化，因为它不能包含任何NULL值。借助InnoDB存储引擎，表数据在物理上得到组织，可以根据主键列进行超快速查找和排序。主键可以使用单独的一列或者是自增主键。

3.2.2 外键优化

建立合适的外键用于多个表之间的优化查询。建立外键查询时会在一个磁盘进行返回，减少IO。

3.2.3 多列和单列索引优化

如果您在 上有一个三列索引(col1, col2, col3)，则您在 、 和 上具有索引 (col1)搜索(col1, col2)功能 (col1, col2, col3)。
如果列不构成索引的最左前缀，则 MySQL 无法使用索引执行查找

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
1
2
3
4
5

如果 上存在索引(col1, col2, col3)，则只有前两个查询使用该索引。第三个和第四个查询确实涉及索引列，但不使用索引来执行查找，因为(col2)和 (col2, col3)不是 的最左边前缀 (col1, col2, col3)。

3.2.4 索引存储引擎优化

B-Tree和哈希索引
B 树索引可用于在使用 =、 >、 >=、 <、 <=或BETWEEN运算符的表达式中进行列比较。LIKE 如果参数 LIKE是不以通配符开头的常量字符串，索引也可用于比较
哈希索引它们仅用于使用 =or<=> 运算符（但速度非常快）的相等比较。它们不用于比较运算符，例如 <查找值范围的运算符。依赖这种类型的单值查找的系统被称为“键值存储”；要将 MySQL 用于此类应用程序，请尽可能使用哈希索引。哈希索引不会再order by中使用索引。

3.2.5 索引排序优化

DESC在索引定义中不再被忽略，而是导致按降序存储键值。以前，可以按相反顺序扫描索引，但会降低性能。
可以使用 EXPLAIN FORMAT=TREE开启降序排序提高性能。

EXPLAIN FORMAT=TREE SELECT * FROM t1 ORDER BY a DESC
1

3.3：数据库结构优化

1：选择合适数据类型，减少空间浪费，降低io。
2：表的并发性设置。
3：行列限制：MySQL 对每个表有 4096列限制，65,535 字节的最大行大小限制
4：字段非 null优化

3.4：优化器的参数优化

参数很多不列举。
索引合并标志

index_merge（默认 on）

控制所有索引合并优化。

index_merge_intersection （默认on）

控制索引合并交叉访问优化。

index_merge_sort_union （默认on）

控制索引合并排序联合访问优化。

index_merge_union （默认on）

控制索引合并联合访问优化。

3.2:在硬件层面进行优化

1、磁盘寻道。磁盘找到一条数据需要时间。对于现代磁盘，平均时间通常低于 10 毫秒，因此理论上我们每秒可以进行大约 100 次寻道。这个时间随着新磁盘的增加而缓慢，并且很难针对单个表进行优化。优化寻道时间的方法是将数据分布到多个磁盘上。

2、磁盘读写。当磁盘处于正确的位置时，我们需要读取或写入数据。使用现代磁盘，一个磁盘至少提供 10–20MB/s 的吞吐量。这比查找更容易优化，因为您可以从多个磁盘并行读取。

3、CPU 周期。当数据在主存中时，我们必须对其进行处理以获得结果。拥有比内存量大的表是最常见的限制因素。但是对于小桌子，速度通常不是问题。

4、内存带宽。当 CPU 需要的数据多于 CPU 高速缓存所能容纳的数据时，主存带宽就成为瓶颈。对于大多数系统来说，这是一个不常见的瓶颈，但需要注意。

3.3:平衡便携性和性能

3.4：EXPLAIN查看执行计划

EXPLAIN适用于 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE语句

key（JSON 名称key：）：该key列表示 MySQL 实际决定使用的键（索引）
possible_keys（JSON 名称 possible_keys：）：该possible_keys列指示 MySQL 可以从中选择的索引来查找该表中的行
rows（JSON 名称 rows：）:该rows列指示 MySQL 认为它必须检查以执行查询的行数。
filtered（JSON 名称 filtered：）:该filtered列指示按表条件过滤的表行的估计百分比。最大值为 100，这意味着没有发生行过滤,rows代表过滤掉行数。

4：常见问题

4.1：[28000][1045] Access denied for user ‘root’@‘ip’ (using password: YES)

使用springboot等java程序连接数据库时报改问题一般是连接数据库的权限问题。

1：查询mysql目前的权限。
use mysql；
select host, user from user;
1
2
3

其中的host为%代表所有客户端服务器可连接，localhost仅仅代表本机。
若不对则进行修改user 后面的为自己的用户

update user set host = '%' where user = 'root';
1

改完刷新缓存生效重试。

FLUSH   PRIVILEGES;
1

4.2：authentication plugin加密插件连接问题

由4.3黑客勒索引起

4.3：云主机mysql数据库被黑

我的云数据库被黑掉勒索了。过年回来发现程序报错，nivacat无法登陆数据库。
程序中数据库连接报了权限问题（[28000][1045] Access denied for user ‘root’@‘your ip’ (using password: YES)）。授权过后，报表不存在。查看数据库发现只有一个README，额被黑掉勒索了。

I have backed up all your databases. To recover them you must pay 0.0114 BTC (Bitcoin) to this address: 169YgqevmncVU72drXmenvgoJopZhQW6vr . Backup List: dict-flowable, luochuan, mobile, ry-flowable-plus. After your payment email me at dbrestore4583@onionmail.org with your server IP (36.139.241.126) and transaction ID and you will get a download link to your backup. Emails without transaction ID and server IP will be ignored. | 169YgqevmncVU72drXmenvgoJopZhQW6vr