MySQL 数据库基础

MySQL数据库、Oracle数据库、SQL Server数据库都属于比较常见的传统的关系型数据库，MySQL的优势主要有简单易用、价格、小、支持多个平台

存储引擎

InnoDB

在MySQL5.5开始作为默认的存储引擎，支持事务，行级锁（通过索引，主键就不会锁全表），外键约束，适合高并发场景，XA协议支持分布式事务。一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择

count(0)需要扫描表

InnoD是Inno Database缩写，Innobase Oy是芬兰一家公司

可以有1017个字段

MyISAM

不支持事务，不支持外键，性能优先，表级锁，不适合高并发场景

count(0)不需要扫描表，MyISAM将表的记录数单独存储，性能要优于InnoDB

MySQL Indexed Sequential Access Method (有索引的顺序访问方法)

可以有2598个字段

对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作； MyISAM表的读操作与写操作之间，以及写操作之间是串行的！读锁会阻塞写，但是不会阻塞读。写锁会把读和写都阻塞掉。

不支持事务所以set autocommit = 0;这个是没有用的。

如何选择？

1. 如果要请选择innodb，如果不需要可以考虑MyISAM
2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，使用InnoDB,锁表性能比较低，如果是统计后的数据存储的那种表，都是统一一个时间由一个程序进行更新数据的话还是适用的。

测试：

LOCK TABLE tb_name READ; -- 加读锁，其他session尝试update与select
LOCK TABLE tb_name WRITE; -- 当前session对当前表的查询、更新、插入，都可以。其他session对当前表的查询阻塞，需要等待锁被释放。

查询表锁情况

show status like 'table%';
-- 查看那些表锁了,执行前面的READ锁，可以看到被锁的表
show OPEN TABLES where In_use > 0;
-- 查看进程号
show processlist;
--删除进程
kill 12345;

MyISAM在执行查询前，会自动执行表的加锁、解锁操作，一般情况下不需要用户手动加、解锁，但是有的时候也需要显示加锁。
比如：检索某一个时刻t1，t2表中数据数量。
常用代码如下：

select count(t1.id) as 'sum' from t1;
select count(t2.id) as 'sum' from t2;

其实这是不正确的，很有可能当你在检索t1的那个时间点，t2的数据已经发生了变化，也就是说你检查出的t1和t2数据结果不是在同一个时间点上。
正确的做法是：

lock table t1 read, t2 read;
select count(t1.id) as 'sum' from t1;
select count(t2.id) as 'sum' from t2;
unlock tables;

MEMORY

为得到最快的响应时间，采用的逻辑存储介质是系统内存，当mysqld守护进程崩溃时，所有的Memory数据都会丢失。Memory同时支持散列索引和B树索引

MERGE

Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。使用场景：对于服务器日志这种信息，比如12个月份用12张表表示，要查询1年的信息可以使用MERGE把12张表聚合成一张表。

ARCHIVE

Archive是归档的意思,仅仅支持最基本的插入和查询两种功能,Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用

CSV

CSV格式存储数据，所有列必须不能为NULL，不支持索引，可以直接对数据文件编辑。

.CSV文件存储表内容，.CSM文件存储表元数据如状态、数量，.frm存储表结构信息

适合作为数据交换的中间表

BlackHole

黑洞引擎，写入的任何数据都会消失，用于记录binlog做复制的中继存储。在一主多从环境可以做传递日志用

FEDERATED

访问远程的MySQL数据库而不使用replication或cluster技术(类似于Oracle的dblink)。使用FEDERATED存储引擎的表,本地只存储表的结构信息,数据都存放在远程数据库上,查询时通过建表时指定的连接符去获取远程库的数据返回到本地

PERFORMANCE_SCHEMA

主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。MySQL5.5默认是关闭的，需要手动开启，在配置文件里添加。

查看存储引擎的SQL：

show engines;

各种类型的区别

char与varchar的区别

char：定长，效率高，平常用于固定长度的场景。比如：手机号码，身份证号码，电话号码等固定长度的编码

varchar：长度不固定，一般来说效率低一些

长度M: 长度限定的是输入的字的个数，不是Byte数。比如varchar(2) 可以输入我我也可以输入11但是没办法输入111

varchar

• 存储 varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字节），因此最大长度不能超过65535。
• 编码长度 gbk每个字符最多2个字节，最大长度32766.utf8每个字符最大3个字节，最大长度不超过21845。但是row的限制是65535。所以如果这个row还有其他字段，utf-8类型字符集的数据库varchar就达不到21845
• 行长度限制 MySQL要求一个行的定义长度不能超过65535。也就是MySQL 单条记录长度最大65535

CHAR(M), VARCHAR(M)不同之处

char(M) 定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度

VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间，(VARCHAR的最大有效长度由最大行大小和使用 的字符集确定。整体最大长度是65,532字节）。VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则 使用两个字节)。varchar存储变长数据，但存储效率没有 CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字，把它定义为VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1或者+2。为什么”+1或者+2”呢？这个字节用于保存实际使用了多大的长度

VARCHAR和TEXT、BlOB类型的区别

VARCHAR，BLOB和TEXT类型是变长类型，对于其存储需求取决于列值的实际长度(在前面的表格中用L表示)，而不是取决于类型 的最大可能尺寸。例如，一个VARCHAR(10)列能保存最大长度为10个字符的一个字符串，实际的存储需要是字符串的长度 ，加上1个字节以记录字符串的长度。对于字符串‘abcd’，L是4而存储要求是5个字节。

BLOB和TEXT类型需要1、2、3或4个字节来记录列值的长度，这取决于类型的最大可能长度。VARCHAR需要定义大小，有65535字节的最大限制；TEXT则没有，如果你把一个超过列类型最大长度的值赋给一个BLOB或TEXT列，值被截断以适合它。

一个BLOB是一个能保存可变数量的数据的二进制的大对象。4个BLOB类型TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB仅仅在他们能保存值的最大长度方面有所不同。

BLOB 可以储存图片，TEXT不行，TEXT只能储存纯文本文件。4个TEXT类型TINYTEXT(21个字节、55个字符)、TEXT(64KB、65,535个字符)、MEDIUMTEXT(16MB、16,777,215个字符))和LONGTEXT(4GB、4,294,967,295个字符)对应于 4个BLOB类型，并且有同样的最大长度和存储需求。在BLOB和TEXT类型之间的唯一差别是对BLOB值的排序和比较以大小写敏感方式执行，而对 TEXT值是大小写不敏感的。换句话说，一个TEXT是一个大小写不敏感的BLOB。

总结char，varchar和text的区别

字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766
字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845

如果数据库类型为varchar(1) 就说明可以存储一个字，可以为：1/a/我 等等

长度的区别，char范围是0～255，varchar最长是64k，但是注意这里的64k是整个row的长度，要考虑到其它的 column，还有如果存在not null的时候也会占用一位，对不同的字符集，有效长度还不一样，比如utf8的，最多21845，还要除去别的column，但是varchar在一般 情况下存储都够用了。如果遇到了大文本，考虑使用text，最大能到4G。

效率来说基本是char>varchar>text，但是如果使用的是Innodb引擎的话，推荐使用varchar代替char。

char和varchar可以有默认值，text不能指定默认值。

数据库选择合适的数据类型存储还是很有必要的，对性能有一定影响。对于int类型的，如果不需要存取负值，最好加上unsigned；对于经常出现在where语句中的字段，考虑加索引，整形的尤其适合加索引。

int tinyint

1 bytes = 8 bit ,一个字节最多可以代表的数据长度是2的8次方 11111111 在计算机中也就是-128到127

• BIT[M] 位字段类型，M表示每个值的位数，范围从1到64，如果M被忽略，默认为1
• TINYINT[(M)] [UNSIGNED] [ZEROFILL] M表示每个值的位数，M默认为4，很小的整数。带符号的范围是-128到127。无符号的范围是0到255。之所以默认值是4是因为最小值-128的字(字符)长度为4
• SMALLINT[(M)] [UNSIGNED] [ZEROFILL] M表示每个值的位数，M默认为6 ，小的整数。带符号的范围是-32768到32767。无符号的范围是0到65535。
• MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] M表示每个值的位数，M默认为9，中等大小的整数。带符号的范围是-8388608到8388607。无符号的范围是0到16777215。
• INT[(M)] [UNSIGNED] [ZEROFILL] M表示每个值的位数，M默认为11 普通大小的整数。带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295。
• BIGINT[(M)] [UNSIGNED] [ZEROFILL] M表示每个值的位数，M默认为20 大整数。带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615。

注意：这里的M代表的并不是存储在数据库中的具体的长度，以前总是会误以为int(3)只能存储3个长度的数字，int(11)就会存储11个长度的数字，这就错了。

tinyint(1) 和 tinyint(4) 中的1和4并不表示存储长度，只有字段指定zerofill(零填充)时有用，
如tinyint(4)，如果实际值是2，如果列指定了zerofill(零填充)，查询结果就是0002，左边用0来填充。这个M=3我们可以简单的理解成为, 我们建立这个长度是为了告诉MYSQL数据库我们这个字段的存储的数据的宽度为3位数,当然如果你不是3位数(只要在该类型的存储范围之内)MYSQL也能正常存储。注意：windows下navicate for mysql 零填充不显示，在linux下查看，或者命令行可以正常显示

在MySQL中用来判断是否需要进行对据列类型转换的规则

1. 在一个数据表里，如果每一个数据列的长度都是固定的，那么每一个数据行的长度也将是固定的
2. 只要数据表里有一个数据列的长度的可变的，那么各数据行的长度都是可变的
3. 如果某个数据表里的数据行的长度是可变的，那么，为了节约存储空间，MySQL会把这个数据表里的固定长度类型的数据列转换为相应的可变长度类型

例外：长度小于4个字符的char数据列不会被转换为varchar类型

锁

MySQL 提供了全局锁、行级锁、表级锁。其中 InnoDB 支持表级锁和行级锁，MyISAM 只支持表级锁。
死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁。

如何处理死锁？
通过 innodblockwait_timeout 来设置超时时间，一直等待直到超时；
innodb_deadlock_detect = on 打开死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其它事务继续执行。(默认选项)

共享锁又称读锁 (read lock)，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改(获取数据上的排他锁)，直到已释放所有共享锁。
排他锁 (writer lock)又称写锁。若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。

InnoDB 存储引擎有锁：
Record Lock — 单个行记录上的锁；
Gap Lock — 间隙锁，锁定一个范围，不包括记录本身；
Next-Key Lock — 锁定一个范围，包括记录本身。

SELECT … LOCK IN SHARE MODE，MySQL 会对查询结果集中每行都添加共享锁，前提是当前线程没有对该结果集中的任何行使用排他锁，否则申请会阻塞。
SELECT … FOR UPDATE，MySQL 会对查询结果集中每行都添加排他锁，在事物操作中，任何对记录的更新与删除操作会自动加上排他锁。前提是当前没有线程对该结果集中的任何行使用排他锁或共享锁，否则申请会阻塞。

实验 启动两个窗口执行命令，第二个窗口会被阻塞：

set autocommit = 0; -- 取消自动提交
select * from sys_user for update;
-- commit; -- 手动提交后第二个窗口可以看到数据

GET_LOCK手动加锁。IS_FREE_LOCK 返回结果为0，说明指定名称的锁正在被使用、IS_USED_LOCK 检查锁str是否正在被使用,返回结果为持有锁的连接的连接ID如果没被使用返回null
可以按表名加锁

SELECT GET_LOCK("abc", 10) as locked; -- 在第二个窗口执行这个命令的时候会等待10秒,返回0 说明获取锁失败,返回1说明获取成功
-- SELECT RELEASE_LOCK('abc');  -- 释放锁,成功释放回返回1
-- SELECT IS_FREE_LOCK('abc') from DUAL;
-- SELECT IS_USED_LOCK('abc'); 

查询分析与优化

explain

explain的使用方法很简单，只需要在select查询语句前面加上explain关键字就行

回显的数据的含义：

• id：SELECT识别符，SELECT查询序列号，id越大的先执行
• select_type：select类型 (simple 它表示简单的select,没有union和子查询。primary 最外面的select。union union语句的第二个或者说是后面那一个。DERIVED 派生表SELECT语句中FROM子句中的SELECT语句。UNION RESULT 一个UNION查询的结果。DEPENDENT UNION 首先需要满足UNION的条件，及UNION中第二个以及后面的SELECT语句，同时该语句依赖外部的查询。SUBQUERY 子查询中第一个SELECT语句 )
• table：显示这一行的数据是关于哪张表的。N就是id值，指该id值对应的那一步操作的结果
• type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、 index和ALL
• key_len：使用的索引的长度。长度越短越好
• ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
• rows：MYSQL认为必须检查的用来返回请求数据的行数
• Extra：关于MYSQL如何解析查询的额外信息。坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢
  • Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
  • Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
  • Range checked for each Record:MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用
  • Using filesort: MySQL需要额外的一次传递,以找出如何按排序顺序检索行
  • Using index: 从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息
  • Using temporary 看到这个的时候，查询需要优化了。MYSQL需要创建一个临时表来存储结果
  • Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户
  • Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。
  • Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

!=是否走索引

1. 如果是<、>、BETWEEN，那么很明显是走范围索引range。!=是不是可以理解为<和>的并集呢？
2. 实际使用中，建了索引的字段，一般Cardinality高，意味着区分度高，才有意义。注:show index from tb_name;, Cardinality通过观察它来评估索引是否合理。
3. 而大部分场景!=不走索引，很有可能只是因为不等于的数据占比很高，走索引不如全表扫描。从而导致普遍认为!=不走索引。
4. 找到数据分布不均匀的字段，做实验即可验证。explain select count(1) from test where status!=1;

   mysql -uroot -p test --test 是数据库名
   
   
   
   mysql> explain select count(1)  from test where type!=3;
   +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
   | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
   +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
   |  1 | SIMPLE      | test  | NULL       | index | type          | type | 768     | NULL |    3 |    66.67 | Using where; Using index |
   +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
   1 row in set, 2 warnings (0.00 sec)
   
   mysql> explain select * from test where type!=3;
   +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
   | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
   +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
   |  1 | SIMPLE      | test  | NULL       | ALL  | type          | NULL | NULL    | NULL |    3 |    66.67 | Using where |
   +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
   1 row in set, 2 warnings (0.00 sec)

总结：MySQL会根据具体情况分析是否走索引。

其他手段

数据量太大的查询可以增加只查询当天数据，查询全部数据的话需要计算所有数据的条数，当天只需要计算单天数据的条数。

性能测试

插入数据测试

CREATE TABLE `t` (
`x`  int(11)  ,
`y`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`z`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`x`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=DYNAMIC

-- MySQL5.7 普通i5 8G内存台式机运行18秒执行完。大概每秒钟500条。
DELIMITER $$
DROP PROCEDURE IF EXISTS `proc_auto_insert`$$
CREATE PROCEDURE `proc_auto_insert`()
BEGIN
		DECLARE i INTEGER DEFAULT 1;
		WHILE i <= 10000 DO 
		INSERT INTO t VALUES(i,CONCAT('--', i), i);
		SET i = i + 1;
		END WHILE;
END$$
DELIMITER ;

CALL proc_auto_insert();

select * from t;

-- 存储引擎改成MyISAM插入速度提高，100万条100秒就够
-- 存储引擎改成MEMORY,插入2万条1.2秒

常用运维命令

1. 登录mysql -h127.0.0.1 -uabcd -p123456
2. 导入.sql文件，好像只能root建库后授权普通用户使用

   mysql -u root -p # root 登录
   create user 'abcd@%' IDENTIFIED BY 'Test_123';
   create database test;
   grant all privileges on test.* to abcd@localhost identified by '123456'; # 授权 grant all privileges on test.* to abcd identified by '123456';
   update user set user.Select_priv = 'Y' where user='abcd'; # 根据需要授权服务器相关权限
   flush privileges;
   mysql -u root -p # abcd 登录
   # mysql -uroot -p123456 test < test.sql
   use test
   set names utf8;
   source /home/user1/20201010.sql
   show tables;

3. 允许外部连接

   mysql -u root -p # root 登录
   use mysql;
   update user set host = '%' where user = 'abcd';
   flush privileges;
   select host,user from user;

安装配置Redhat6.5

1. 官方网站下载
   服务端 rpm
   客户端 rpm
2. 安装
   rpm -ivh xxx.rpm
3. 设置密码
   /usr/bin/mysqladmin -u root password ‘xxx’;
   mysql -u root -p
4. 查看字符集：
   show variables like ‘collation_%’
5. 修改字符集
   打开/etc/my.cnf在[client]和[mysqld]下面均加上default-character-set=utf8
6. 卸载
   rpm -qa|grep -i mysql
   rpm -e xxx.rpm
7. 删除残余
   rm -f /etc/my.cnf
   rm -f /var/lib/mysql
8. 复制数据库
   mysqldump source_db -uroot -pxxx –opt | mysql target_db -uroot -pxxx -h target_ip
9. 开启关闭
   service mysqld start
   service mysqld stop

安装配置CentOS7.6 Mysql5.7

1. wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
2. yum -y install mysql57-community-release-el7-10.noarch.rpm
3. yum -y install mysql-community-server
4. systemctl start mysqld.service
5. systemctl status mysqld.service
6. grep "password" /var/log/mysqld.log 获取初始密码
7. mysql -u root -p 登录
8. ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password'; 修改密码
9. show variables like '%password%';查看密码策略,修改密码策略:

   validate_password_policy=0
   validate_password = off

10. 开启远程访问

    grant all privileges on *.* to 'root'@'192.168.1.1' identified by 'password' with grant option;
    flush privileges;
    exit;

11. 打开端口

    firewall-cmd --zone=public --add-port=3306/tcp --permanent
    firewall-cmd --reload

12. 查看修改编码 show variables like '%character%';

    # vi /etc/my.cnf
    [mysqld]
    character_set_server=utf8
    init_connect='SET NAMES utf8'

文件目录

数据库目录：/var/lib/mysql/
配置文件：/usr/share/mysql(mysql.server命令及配置文件)
相关命令：/usr/bin(mysqladmin、mysqldump等命令)(*mysql的一种安全启动方式：/usr/bin/mysqld_safe –user=root &)
启动脚本：/etc/rc.d/init.d/

配置模板：/usr/share/mysql/my-default.cnf
配置路径：/etc/my.cnf

CentOS7.6 rpm包安装MySQL8

如果使用RPM包安装，安装顺序是：

rpm -Uvh net-tools-2.0-0.24.20131004git.el7.x86_64.rpm
rpm -Uvh libtool-ltdl-2.4.2-22.el7_3.x86_64.rpm
rpm -Uvh perl-*.rpm
rpm -Uvh libaio-0.3.109-13.el7.x86_64.rpm


rpm -Uvh mysql-community-common-8.0.26-1.el7.x86_64.rpm
rpm -Uvh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm
rpm -Uvh mysql-community-libs-8.0.26-1.el7.x86_64.rpm
rpm -Uvh mysql-community-client-8.0.26-1.el7.x86_64.rpm
rpm -Uvh mysql-community-server-8.0.26-1.el7.x86_64.rpm

mysqldefaultpwd=`sudo grep "A temporary password" /var/log/mysqld.log|tail -n 1 | awk '{print $NF}'`
echo $mysqldefaultpwd # 获取mysql默认密码
mysql --connect-expired-password -uroot -p$mysqldefaultpwd < ${SHELLPATH}/init.sql # mysql --connect-expired-password -uroot -p$mysqldefaultpwd # 通过临时密码登录


# --- 其他说明init.sql ---
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Infotech_85F#';
create database infotech character set utf8;
CREATE USER 'infotech'@'%' IDENTIFIED BY 'Infotech_81F#'; # mysql加密的密码，可以改成 CREATE USER 'infotech'@'%' IDENTIFIED WITH mysql_native_password  BY 'Infotech_81F#';
grant all privileges on infotech.* to 'infotech'@'%';
flush privileges;
# --- ---

mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password, 解决问题方法有两种,一种是升级navicat驱动,一种是把mysql用户登录密码加密规则还原成mysql_native_password。客户端版本太低会出现1251错误。

ALTER USER 'infotech'@'%' IDENTIFIED WITH mysql_native_password BY 'Infotech_81F#'; -- 修改密码
flush privileges;

应用开发

表是否要主键

Innodb，主键是一个聚集索引。定义了主键，这个主键就是聚集索引，没有定义，第一个唯一非空索引作为聚集索引
如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是6个字节，值会随着数据的插入自增。

中间表是否加主键

1. 第一种策略：统一都加主键id或者表名_id,id的优点是简单，表名_id优点是便于前端识别/可读性高
2. 第二种策略：如果中间表只有外键没有其他附加字段不加id，如果有附加字段加主键id
3. 第三种策略：中间表不加主键
4. 根据具体情况加，比如需要对中间表中的一个字段进行更新操作建议加id,可以通过id快速定位，不然就得根据两个关联表外键进行组合查询，涉及根据外键条件更新建议外键加上索引。根据具体情况分析，比如中间表加了id做中间表更新操作的时候一般涉及只涉及新增与删除
5. 表因为使用UUId作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，所以建议使用int的auto_increment作为主键
6. 中间表的索引：（1）InnoDB通常根据主键值(primary key)进行聚簇。（2）如果没有创建主键，则会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引,（3）上面二个条件都不满足，InnoDB会自己创建一个虚拟的聚集索引

中间表实际应用：
任务人员中间表内容：id,task_id,person_id
有中间表id的情况：附件 更新提交 不存在前端进行左右移动导致id丢失问题，通过是否提交ID进行判断，如果有ID说明在数据库中存在，如果没有ID说明是新增的。数据库操作时先把不存在的ID删除（delete from xxx where id not in()），把新增的项添加进去
没有中间表id的情况：任务人员 更新提交 集合A（前端）和B（数据库）操作，A－B为新增，B－A为删除，A∩B为不变
最暴力的方案(不推荐)：任务人员 删除所有旧数据，重新插入新数据
注意幂等性：界面可以重复提交添加，不导致报错。比如转移功能，把旧的关联解绑，绑定新的关联的时候不是直接插入就行而要判断数据库中是否已经存在关联，存在的话就跳过插入操作。类似场景表单重复提交(随机码等解决方案)

对于加不加主键各有各的优缺点，加了主键删除比较快捷，但是显示的列表就必须有主键。

对地理空间、地图应用

geometry 可以存储点、线、集合。支持一些地理空间操作函数。insert语句使用ST_GeomFromText('POINT(121.111 31.222)')

SQL语句

order by

select * from test order by create_at desc,realname desc

设置自增id

自增ID只能设置成比当前数据库最大自增ID值，更大的值。小于当前值将不生效

alter table user AUTO_INCREMENT=100;

-- 查看表自增ID信息，先取值，然后再自增 +1
select * from information_schema.tables

也可以用Redis实现，登录Redis执行命令(如果redis宕机或者key被删除怎么办?)：

SET auto_incr 20 # 不要有;符号，可以不先进行SET操作，默认从0开始加1
GET auto_incr # INCR 命令是一个针对字符串的操作。 因为 Redis 并没有专用的整数类型， 所以键 key 储存的值在执行 INCR 命令时会被解释为十进制 64 位有符号整数。
INCR auto_incr # 原子操作 结合增加1并返回增加之后的值
INCRBY auto_incr 2

如果用数据库表+函数实现 记得序号增加的函数要加锁

truncate

truncate table user; -- 会把自增id清除从1开始，并且不会记录日志。建议使用delete
-- 有外键约束无法truncate的时候，需要加两句,关闭启用约束检查
SET FOREIGN_KEY_CHECKS = 0;       -- 先归0
TRUNCATE table user;              -- 再清除数据
SET FOREIGN_KEY_CHECKS = 1;       -- 然后设置1

查看表结构

desc table_name;

表字段命名

不能出现RANGE关键字，可以替换成其他的名字，比如加上前缀。还有很多关键字不能使用。

表字段可以简化命名，比如直接用id,name,type。也可以加上表名或者其他详细的前缀助于理解xx_id,yy_name,zz_type。各有优缺点，简化命名就是少敲几个字符，理解上要结合表名，代码里面搜索可能会搜出不同表的同名字段，冗余信息会多一些。

扩展

实现递归

1. 建表

   CREATE TABLE `nodelist` (
   `id`  int(11) NOT NULL ,
   `nodename`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
   `pid`  int(11) NULL DEFAULT NULL ,
   PRIMARY KEY (`id`)
   )
   INSERT INTO `nodelist` VALUES ('1', 'A', null);
   INSERT INTO `nodelist` VALUES ('2', 'B', '1');
   INSERT INTO `nodelist` VALUES ('3', 'C', '1');
   INSERT INTO `nodelist` VALUES ('4', 'D', '2');
   INSERT INTO `nodelist` VALUES ('5', 'E', '3');
   INSERT INTO `nodelist` VALUES ('6', 'F', '3');
   INSERT INTO `nodelist` VALUES ('7', 'G', '5');
   INSERT INTO `nodelist` VALUES ('8', 'H', '7');
   INSERT INTO `nodelist` VALUES ('9', 'I', '7');
   INSERT INTO `nodelist` VALUES ('10', 'J', '8');

2. 建函数

   BEGIN 
      DECLARE pTemp VARCHAR(1000);  
      DECLARE cTemp VARCHAR(1000);  
     
      SET pTemp = '$';  
      SET cTemp =cast(rootId as CHAR);  
     
      WHILE cTemp is not null DO  
        SET pTemp = concat(pTemp,',',cTemp);  
        SELECT group_concat(id) INTO cTemp FROM nodelist   
        WHERE FIND_IN_SET(pid,cTemp)>0; 
               
      END WHILE;  
      RETURN pTemp;  
    END

3. 使用

   SELECT * FROM nodelist WHERE FIND_IN_SET(id, getChildList(7)); 
   
   SELECT getChildList(3)

4. 变形，还可以改造成从子节点往父节点查询，还可以加上模糊查询条件
5. 其他方案

   select id,nodename from(
   select id,nodename,pid,(select pid from nodelist where id=t.pid) pid2
   from nodelist t ) tt
   where ifnull(pid,0)=1 or ifnull(pid2,0)=1

按时间统计、统计

select date(create_at) as date,count(*) from test group by date; -- 按天
select DATE_FORMAT(create_at, '%Y-%m-%d %H:00:00') as date,count(*) from test group by date; -- 按小时
-- 还可以分钟、半小时等

这种数据量大可能还是会慢，可以考虑定时统计生成小时、天表等。
如果需要显示空缺的日期可以建立一个时间表进行表连接。

统计类由于各种条件带进去，索引很难起作用，最好可以弄个混吃实时更新统计值，或者弄个缓存定时统计更新进去就是实时性不好，当然如果可以忍受速度慢那也是可以直接查看的时候调用sql语句统计。

工具结合使用

Navicat

设计表-选项：表选项中可以设置引擎(InnoDB)，字符集，排序规则，自增当前值，行格式(DYNAMIC/COMPACT)等。

可能出现问题

• ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

  • 可能root密码设置了多个，外网访问与localhost使用不同密码
  • 如果在后台操作可以跳过密码，重新设置一个密码
  • 查看mysql.user表有匿名用户，可能匿名用户导致匹配虽然使用的是%的时候优先匹配到localhost,mysql会优先匹配主机名再匹配用户名的记录

• Navicat连接MySQL数据库非常慢

  • 可能是MySQL配置了DNS服务，在配文件添加配置

    [mysqld]
    skip-name-resolve

  • 在Navicat客户端高级配置中，把保存连接间隔设置为30秒
  • 也可能是客户端软件环境其他问题导致的，可以尝试使用Navicat客户端命令界面或者重新安装其他版本Navicat,Navicat命令界面做的挺好的

运维

查看mysql配置文件位置？

mysql --help | grep '.cnf' # /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 这个顺序，配置了datadir、port等

导入大文件提示Got a packet bigger than ‘max_allowed_packet’ bytes

vi /etc/my.cnf # 1.打开配置文件
max_allowed_packet=400M # 2.在my.cnf添加配置项

show VARIABLES like '%max_allowed_packet%'; -- 查看参数

导入数据过慢

# /etc/my.cnf 添加

skip_innodb_doublewrite # 禁用Mysql 的两次写功能
# 当设置该值为1时，每次事务提交都要做一次fsync，这是最安全的配置，即使宕机也不会丢失事务；
# 当设置为2时，则在事务提交时只做write操作，只保证写到系统的page cache，因此实例crash不会丢失事务，但宕机则可能丢失事务；
# 当设置为0时，事务提交不会触发redo写操作，而是留给后台线程每秒一次的刷盘操作，因此实例crash将最多丢失1秒钟内的事务。
innodb_flush_log_at_trx_commit = 0

或者执行

show global variables where variable_name = 'innodb_flush_log_at_trx_commit';

导入命令：

mysql -uxxx -pxxx -Ddb1<xxx.sql

navicat连接操作很卡

# /etc/my.cnf 添加
[mysqld]
skip-name-resolve

原因是由于mysql对连接的客户端进行DNS反向解析。
有2种解决办法：
1，把client的ip写在mysql服务器的/etc/hosts文件里，随便给个名字就可以了。
2，在 my.cnf 中加入 skip-name-resolve 。
对于第一种方法比较笨，也不实用，那么 skip-name-resolve 选项可以禁用dns解析，但是，这样不能在mysql的授权表中使用主机名了，只能使用IP。
我理解mysql是这样来处理客户端解析过程的，
1，当mysql的client连过来的时候，服务器会主动去查client的域名。
2，首先查找 /etc/hosts 文件，搜索域名和IP的对应关系。
3，如果hosts文件没有，则查找DNS设置，如果没有设置DNS服务器，会立刻返回失败，就相当于mysql设置了skip-name-resolve参数，如果设置了DNS服务器，就进行反向解析，直到timeout。

同时，请注意在增加该配置参数后，mysql的授权表中的host字段就不能够使用域名而只能够使用 ip地址了，因为这是禁止了域名解析的结果。

开发

区域转换

给区域加上省市县镇村详细信息，最简单暴力的做法：

INSERT INTO sys_area (
  id,
  parent_id,
  level_type,
  name,
  province,
  city,
  district,
  town,
  village,
  parent_path
)
SELECT a.region_code AS id,a.parent_code AS parent_id,a.region_level AS level_type,a.region_name AS name,
if(a.region_level=1,a.region_name,if(a.region_level=2,b.region_name,if(a.region_level=3,c.region_name,if(a.region_level=4,d.region_name,e.region_name)))) AS province,
if(a.region_level=1,null,if(a.region_level=2,a.region_name,if(a.region_level=3,b.region_name,if(a.region_level=4,c.region_name,d.region_name)))) AS city,
if(a.region_level=1,null,if(a.region_level=2,null,if(a.region_level=3,a.region_name,if(a.region_level=4,b.region_name,c.region_name)))) AS county,
if(a.region_level=1,null,if(a.region_level=2,null,if(a.region_level=3,null,if(a.region_level=4,a.region_name,b.region_name)))) AS town,
if(a.region_level=1,null,if(a.region_level=2,null,if(a.region_level=3,null,if(a.region_level=4,null,a.region_name)))) AS village,
if(a.region_level=1,a.region_code,if(a.region_level=2,CONCAT(a.parent_code,',',a.region_code),if(a.region_level=3,CONCAT(b.parent_code,',',a.parent_code,',',a.region_code),
if(a.region_level=4,CONCAT(c.parent_code,',',b.parent_code,',',a.parent_code,',',a.region_code),CONCAT(d.parent_code,',',c.parent_code,',',b.parent_code,',',a.parent_code,',',a.region_code))))) AS parent_path
    FROM region a
        left JOIN region b ON a.parent_code=b.region_code
        left JOIN region c ON b.parent_code=c.region_code
        left JOIN region d ON c.parent_code=d.region_code
        left JOIN region e ON d.parent_code=e.region_code

查询优化

left join是先过滤还是先左连接？

抛开数据分布、索引、选择度等一系列问题，先筛选后连接会更好，因为你要处理的数据量理论上更少，但是优化器会根据实际情况选择它认为的最优方式。所以如果实在要选，建议先筛选后连接。
在查询分析器中，看看执行计划，不同写法最终效率可能是一样的，最终都转换同样的语句。过滤的条件如果没走索引比如like也可能导致过滤比较慢。

加了索引真实执行语句的时候还不一定会走索引，比如area表(id,name,province,parent_id,parent_path)都是varchar类型,给paren_id加上索引，导入70万条数据，通过explain查询分析

select t1.* from area t1  where t1.parent_id='0' -- 显示不走索引 type=ALL Extra=Using where Rows=70万 0.002秒 31条结果  查询分析器显示没有走索引，实际上肯定是走索引了
select t1.* from area t1  where t1.parent_id=0 -- 不走索引 type=ALL Extra=Using where Rows=70万 0.5秒 31条结果
select t1.* from area t1 where t1.province='北京市' -- 无索引 type=ALL Extra=Using where Rows=70万 0.5秒 7千条结果
select count(t1.id) from area t1 where t1.province='北京市' -- 无索引 type=ALL Extra=Using where Rows=70万 0.27秒

EXPLAIN select t1.* from area t1 left join area t2 on t1.id=t2.id where t1.parent_path like '110000000000%'
-- 1	SIMPLE	t1		ALL					684707	11.11	Using where
-- 1	SIMPLE	t2		eq_ref	PRIMARY	PRIMARY	98	test.t1.id	1	100	Using index
EXPLAIN select t1.* from (select * from area where parent_path like '110000000000%') t1 left join area t2 on t1.id=t2.id  -- 两条的性能是一样的
-- 1	SIMPLE	area		ALL					684707	11.11	Using where
-- 1	SIMPLE	t2		eq_ref	PRIMARY	PRIMARY	98	test.area.id	1	100	Using index
EXPLAIN select t1.* from (select * from area where parent_id = '0') t1 left join area t2 on t1.id=t2.id 
-- 1	SIMPLE	area		ref	i_parent_id	i_parent_id	99	const	31	100	
-- 1	SIMPLE	t2		eq_ref	PRIMARY	PRIMARY	98	test.area.id	1	100	Using index

EXPLAIN xxx -- 要用EXPLAIN命令分析，不要直接看Navicat工具的结果不准，感觉用命令看都还是可能不准确的。

-- type说明:
-- ref 查找条件列使用了索引而且不为主键和unique
-- range 指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index
-- index 这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序
-- all 这便是所谓的“全表扫描”
-- ref_eq ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。
-- const 通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。
-- system mysql的系统表time_zone里查询数据，扫码类型为system，这些数据已经加载到内存里，不需要进行磁盘IO
-- system>const>eq_ref>ref>range>index>ALL

查询分析器会判断是否需要通过索引查找数据。
注意：如果查询条件的类型不匹配也不会走索引。查询分析器显示的也不一定准确。

join exist

连表还是比较豪性能的，有时候可以考虑用子查询等。

一些疑问

单列索引与组合索引的区别？
单列查询的时候基本只会选取性能最佳的一列，组合可以从左到又组合起来匹配性能更佳，比如A、B、C，建立组合索引相当于A、AB、ABC，你条件只有B或者C的话索引是无效的（AC也会用到索引但是实际只用到了A）。

时间索引支持大于、小于等的比较的，当查询出来的数据多时不走索引直接查询全表，当数据少时走索引，比如当前时间2022年，可以查询2023年不存在的数据就会出现走索引情况，时间设置成2021就会走全表。

不同数据库数据互相导

1. 可以使用Navicat导入导出向导，通过xml互相转换。
2. 自己写程序实现

关键字

设计数据库字段的时候尽量不要用关键字。如果真的要用那么SQL语句最好统一字段名加上分隔符避免漏掉，当然测试的时候也会发现问题。

问题

错误 1449

mysql5.7:

mysql -uroot -pxxx

mysql >grant all privileges on *.* to root@"%" identified by "Passwd" # grent all privileges on *.* to test@localhot identified by 'test1' with grant option
mysql >flush privileges;

all privileges ：所有权限
on . ：所有的数据库
test@localhot identified by ‘test1’ ：用户名test，本地登录localhost，密码test1
with grant option：可以将自己的权限授予他人

mysql8 grant all on *.* to 'root'@'%';

执行了还是不行，可以如果是视图的话可以重新创建视图，可能是由于创建者不存在了需要创建创建者，或者修改视图所属者。

导入sql速度慢

mysql 5.7或者8 :

mysql -uroot -pxxxxx<<EOF
use xxx;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 2000;
source $SHELLPATH/xxx.sql;
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
SET GLOBAL sync_binlog = 1;
EOF
exit;

或者直接在SQL脚本前面加set sql_log_bin=0; 不生成binlog 日志 mysql -uroot -p123456 xxxdb<$SQLPATH/xx.sql，只配置这一项效果并不明显。

Access denied for user ‘xxx’@’localhost’ (using password: YES)

大概率是密码错了。

insert user (user, host, ssl_cipher, x509_issuer, x509_subject) values('xxx', 'localhost', '', '', '');

libc.so.6(GLIBC_2.14)(64bit) is needed by mysql-community-client-plugins-8.0.26-1.el7.x86_64

在ARM环境需要下载对应的ARM安装包

安装libmysqlclient.so.18()(64bit) is needed by (installed) postfix-2:2.10.1-7.el7.aarch64

1. 可能是安装rpm包顺序不对
2. 可能是没有删除掉mariadb-libs，执行rpm -qa | grep mariadb,rpm -e --nodeps mariadb-libs

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Public Key Retrieval is not allowed

最简单的解决方法是在连接后面添加 allowPublicKeyRetrieval=true
https://mysql-net.github.io/MySqlConnector/connection-options/
如果用户使用了 sha256_password 认证，密码在传输过程中必须使用 TLS 协议保护，但是如果 RSA 公钥不可用，可以使用服务器提供的公钥；可以在连接中通过 ServerRSAPublicKeyFile 指定服务器的 RSA 公钥，或者AllowPublicKeyRetrieval=True参数以允许客户端从服务器获取公钥；但是需要注意的是 AllowPublicKeyRetrieval=True可能会导致恶意的代理通过中间人攻击(MITM)获取到明文密码，所以默认是关闭的，必须显式开启。

dbeave可以在链接 -> 驱动属性那里修改

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

mysql 支持 socket 和 TCP/IP 连接

增加/etc/my.cnf增加 socket=/var/lib/mysql/mysql.sock 或者使用TCP连接mysql -u test -h 127.0.0.1 -p123456 -P3306
或者使用socket但是需要增加client配置:

[mysqld]
socket=/var/lib/mysql/mysql.sock
[client]
port=3306
socket=/var/lib/mysql/mysql.sock

# /var/lib/mysql/mysql.sock 这个路径不可以修改，有个/tmp/mysql.sock有链接过来，如果mysql是安装在其他目录没有/var/lib等目录可能需要先手动创建

注释

注释要注意要有空格，对于语法检查严格的环境没有空格可能导致执行失败

--一二三  错误注释
-- 一二三  正确注释方法

其他

跳过密码校验

# 临时密码登录
grep 'password'  /var/log/mysqld.log

# 修改配置跳过密码验证
vi /etc/my.cnf
[mysqld]
skip-grant-tables      //加一行

# 重启
systemctl restart mysqld   
mysql #直接进入

update mysql.user set authentication_string=password('SBVD..2022') where user='root';
# 修改密码，删除跳过密码校验

Mysql1251解决办法

ALTER USER 'mysql的用户名'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysql的密码';
ALTER USER 'mysql的用户名'@'%' IDENTIFIED WITH mysql_native_password BY 'mysql的密码';

授权

GRANT
  ALL PRIVILEGES
ON *.*
TO admin@'%'
IDENTIFIED BY 'admin';

删除用户

drop user xxx;
drop user 'xxx'@'localhost';
delete from user where user='xxx' and host='localhost';
delete from user where user='xxx' and host='%';
flush privileges;

版本选择

一般向下兼容容易，向上兼容难。就比如数据库低版本用了一个函数高版本废弃了，你在高版本使用了一个低版本没有的函数都会导致无法互相切换。一般情况是推荐使用高版本，低版本可能再过些天就不维护了，早晚要升级到高版本。旧项目使用5.7，新项目可以使用8.0，随着时间推移，其实后面都是要升级到8.0的。

删除数据

数据量非常大的时候删除数据也是个问题，可能导致锁住超时等。删除的时候是要上锁的，数据量非常大的时候可能非常耗时，其他更新操作的时候可能就得等待锁的释放。合理利用索引，或者定时删除等方法来改进。 这也是导致操作变慢的一个原因。

比如建立两个索引，这样支持时间与应用id查询，时间倒序。也支持只通过appid进行删除的操作

idx_1	date, appid	
idx_2	appid		

查看锁表情况

show OPEN TABLES where In_use > 0;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
show status like 'table%';
show status like 'innodb_row_lock%';
show processlist;

慢查询

show processlist;
select * from information_schema.`PROCESSLIST` where info is not null ORDER BY time desc; --对于查询操作不要看到time是0就觉得非常快了，得把语句拿出来执行看看，说不定非常慢
select * from information_schema.`PROCESSLIST` where command='query' and time>20;
--- time列：显示这个状态持续的时间，单位是秒；可以查询出当前执行非常慢的语句。 该加索引的加索引，该优化sql的优化sql。

select concat('kill ',id,';') from information_schema.`PROCESSLIST` where command='query' and time>20; 
--- 拼接成kill语句执行

把表弄的超级大，然后有左右各种join，会导致非常慢，cpu超过 500%,其他模块功能也会被拖慢。

添加慢查询日志

log_output=file
slow_query_log=on
slow_query_log_file = tmp/mysql-slow.log
log_queries_not_using_indexes=on
long_query_time =5

重启mysql

参考

• mysql普通用户本机无法登录的解决办法
• 多个单列索引和联合索引的区别详解
• left join 查询效率疑问：主表先过滤后关联 是否会比 先关联后过滤 效率高?
• mysql中explain的type的解释
• Shell脚本中执行sql语句操作mysql的5种方法
• linux用rpm包方式安装MySQL8
• mysql8.0.11用户密码设置注意事项
• mysql 导入数据太慢，来教你实用干货