Mysql的基本用法和函数_mysql similarity-CSDN博客

本文链接：https://blog.csdn.net/qq_36236038/article/details/104558771

学习网站

mysql研究院：http://wp1i.cn/archives/76063.html

函数

Mysql同步方式

semi replication//待定

批量插入数据的问题

show variables like '%max_allowed_packet%'

批量插入的数据过大，会报错，PacketTooBigException

使用时间分表和使用requestId或者用户id分表的优缺点分析

理论上订单表更适合使用时间做分表, 这样一来时间越老的数据访问的频率越小, 旧的分表逐渐就会成为冷表, 不再被访问到.
当时负责人的说法是, 由于这个表读写频率都高(而且场景中经常需要读主库), 用orderId分表可以均衡写负载和读负载.。另外采用时间点进行分表，容易处理账单生成问题，因为能够在一个分表中集中某个月份的所有账单。

枚举值用varchar代替的好处

时区问题

show variables like '%time_zone%'

全局参数system_time_zone系统时区，在MySQL启动时会检查当前系统的时区并根据系统时区设置全局参数system_time_zone的值。

全局参数time_zone 表示的是MySQL 采用的是系统的时区。也就是说，如果在连接时没有设置时区信息，就会采用这个时区配置用来设置每个连接会话的时区，默认为system时，使用全局参数system_time_zone的值。

一次因“CST”时区协商问题导致数据库时间戳错误的 debug 经历一次因“CST”时区协商问题导致数据库时间戳错误的 debug 经历 - 简书

JDBC与mysql同为CST时区导致数据库时间和客户端时间差13或者14小时 - 简书

字符集问题（utf8,utf8mb4）

mysql在5.5.3版本增加了utf8mb4字符集，mb4的含义是most byte 4,即最大4个字节，专门用来兼容4个字节的unicode，utf8mb4是utf8的超集，基本上除了将编码方式改为utf8mb4之外，不需要做其他转换。那上面说了既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面(BMP)。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上)，和很多不常用的汉字，以及任何新增的 Unicode 字符等等(utf8的缺点)。

校对会对

show collation;//显示所有可用的校对以及适用的字符集，有的字符集不止一个校对

show character set;//显示所有可用的字符集、描述，以及默认的校对

show variables like '%char%';//查询数据乱码，可以通过这个搜索这个，来查询数据库客户端使用的字符和数据库使用的字符集是不是相同的。如果想hcaracter_set_client都是latin1，说明连接没有设置成正确的字符集，需要在mysql连接的时候命令中添加--default-character-set=utf8。（好像设置的时候不支持utf8mb4）或者在mysql中使用set names utf8也可以；

注意：

1、校对可以对order by的子句检索出来的数据在排序上会有影响，collate 除了可以在order by 子句中使用外，还可以用于 group by、having、聚集函数、别名等

2、如果指定CHARACTER SET 和COLLATE 两者，则使用这些值

3、如果只指定character set ,则使用此字符集及其默认的校对（也就是 show character set 结果中显示的）

4、如果既不指定character set 也不指定 collater 则使用数据库默认

5、在不区分大小写的表上进行区分大小写搜索可以，反之也可以

6、如果觉得需要可以使用 Cast() 或 Convert() 函数，将串与字符集进行转换

-- 对一个表以及一个特定的列指定了character set 和 collate  
mysql>  create table obgestu.obge_table
    -> (
    ->  columns int,
    ->  column1 varchar(10),
    ->  column2 varchar(10) character set utf8 collate  utf8_general_ci
    -> )default character set hebrew collate hebrew_general_ci;

数据库常见问题分析

明明字段是xx_time 字段not null default current_timestamp 但是仍然报错 column xx_time can not be null

explicit_defaults_for_timestamp 这个my.ini里面的东西，MySQL 5.6版本引入，explicit_defaults_for_timestamp 来控制对timestamp NULL值的处理

首先在mysql里面查一下show variables like '%explicit_defaults_for_timestamp%';

ON的话就会报Column 'create_time' cannot be null，

数据库插入数据没成功，但是也不报错

1）当sql语句语法完全没问题，Java代码逻辑和过程也没问题，有可能原因在数据表的名字有问题。我的数据表名是order，而order在MySQL中是关键字，所以一直插入不进去，也不报错。将数据表的名字改为orders就可以了。因此需要注意，数据表命名时与mysql中关键字的冲突，以前一直没有意识到这个问题，只是在写后台和前端代码时，在注意关键字的问题。【MySQL中使用关键字作为数据表名时不会报错的，会让人摸不着头脑。如果你遇到这种问题，就考虑一下是不是这个原因】

取消外键约束

set FOREIGN_KEY_CHECKS=0; //取消外键约束

set FOREIGN_KEY_CHECKS=1; //

如果表与表之间有外键约束，则无法删除或者修改表字段，解决办法是可以通过取消外键约束，将越来的表数据导出到sql语句，然后重新建表，然后将表数据通过sql脚本导入到新表中。

建立新表

一般建立新表的时候，在建表语句之前需要删除旧表,不管旧表存不存在。

DROP TABLE IF EXISTS 'xx旧表';

explain命令

查看sql查询的执行过程

优化表结构辅助分析语句

procedure analyse();//TODO

@变量和记录行号

Mysql查询结果带行号【带解析】_非典型程序媛-CSDN博客_mysql 行号

select @rowNum:=@rowNum+1 as row,a.* from teacher a,(select @rowNum:=0)b;//可以显示所有记录

update多个字段不能使用and，只能使用逗号

update table tablexx set columnx=dd and columny=tgf where ......

可以发现update不会报错，但可能更新数据也有可能不会更新数据,如下例子所示

CREATE TABLE `user` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(500) NOT NULL DEFAULT '',
  `age` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO user(user_name,age) VALUES ('zhangsan',20);

//实际结果是user_name字段没有被更新，age字段则被更新成了0
UPDATE user SET age = 30 AND user_name = 'lisi' WHERE id = 1

//这次执行的语句与之前唯一不同的是这次要更新的user_name与当前行中的user_name值相同,age字段变成了1
UPDATE user SET age = 30 AND user_name = 'zhangsan' WHERE id = 1

MySQL的解析逻辑 30 与 user_name = 'zhangsan'
第一次user_name = 'lisi'，假，所以更新为0
第二次user_name = 'zhangsan'，真，所以更新为1

mysql中的三个时间类型date,datetime,timestamp

1.timestamp类型的字段必须要有默认值，不能default null

mysql事务和重复插入

在事务未提交时，已经对唯一索引值 "a" 进行了锁定，其他事务插入相同索引会报错:Lock wait timeout exceedes;try restarting transaction；插入其他不存在的索引值，如“c”，未被锁定，一样可以插入（图中序号7）

2、当第一个事务提交后，另一个事务再次执行插入“a”的语句（图中序号10），此时报错变成 duplicate entry “a” for key “unique_test”

3、如图序号5和6，插入两次都失败，浪费了两次id，所以图中序号8，插入的id为4；可见自增id也是获取完就不会进行回收了，不管使用成功与否。

函数分类：注意mysql是忽视大小写的

查看数据库的相关系统信息

select version() //查看数据库的系统版本

时间类的函数

now()=current_timestamp=current_timestamp() curdate()=current_date=current_date()

dayName（date）指定时间的名称例如星期三，类似函数monthname

weekofyear(curdate())本周是今年的第几周，类似的函数dayofyear(date)1-365

quarter(date)本季度是今年的第几季度

hour(time),second(time),minute(time)指定时间的时分秒

extract(unit from date)抽取指定时间的时间单位，unit包括SECOND ,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR等

其他函数

coalesce(a,b,c)或者coalesce(a,b) 返回第一个非null的字段

查询某一段时间的数据

查询今天的数据

select * from table where to_days(column_time_field)=to_days(now())

查询昨天的数据

select * from table where to_days(column_time_field)-to_days(now())<=1

查询近7天的数据

select * from table where date_sub(now(),internal 7 DAY)<=date(CURDATE())

查询本周的数据

select * from table where YEARWEEK(date_format(column_time_field,"%Y-%m-%d"))=YEARWEEK(now())

查询本月的数据

select * from table where date_format(column_time_field,"%Y%m")=date_format(CURDATE(),"%Y%m")

.查询上一个月的数据

select * from table where period_diff(date_format(now(),"%Y%m"),date_format(column_time_field,"%Y%m"))=1

查询本季度的数据

select * from table where QUARIER(column_time_field)=QUARIER(now())

查询上个季度的数据

select * from table where QUARIER(column_time_field)=QUARIER(date_sub(now()) , internal 1 QUARIER)

查询本年度的数据

select * from table where YEAR(column_time_field)=YEAR(now())

查询上个年度的数据

select * from table where YEAR(column_time_field)=YEAR(DATA_SUB(now(), internal 1 YEAR))

关键字

on duplicate key update

此语句语法如下：

INSERT INTO tablename(field1,field2, field3, ...) 
VALUES(value1, value2, value3, ...) 
ON DUPLICATEKEY UPDATE field1=value1,field2=value2, field3=value3, ...;

当我们数据库表中含有unique key或者primary key，并且我们往往会在数据库中插入一条已有相同key的数据的时候，此时使用on duplicate key update语句，就会产生如下效果：

如果数据库表中没有此相同key的数据，就会插入，如果有则更新某些字段的数据。

insert ignore

insert ignore into表示：会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过当前插入的这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

replace和replace into

replace into的语法如下：

insert into  test(title,uid) VALUES ('123465','1001');
REPLACE INTO test(title,uid) VALUES ('1234657','1003');

用法基本上和insert into相同，当有unique key或者primary key的重复旧数据，replace into会删除旧数据，再插入新数据，如果没有则直接插入新数据，注意replace into必须使用在含有primary key或者unique key的表中，并且影响2行

--新建一个test表，三个字段，id，title，uid,  id是自增的主键，uid是唯一索引；
insert into  test(title,uid) VALUES ('123465','1001');
insert into  test(title,uid) VALUES ('123465','1002');

--执行单条插入数据可以看到，执行结果如下：
[SQL]insert into  test(title,uid) VALUES ('123465','1001');
受影响的行: 1
时间: 0.175s



--使用 replace into插入数据时：
REPLACE INTO test(title,uid) VALUES ('1234657','1003');

执行结果：
[SQL]REPLACE INTO test(title,uid) VALUES ('1234657','1003');
受影响的行: 1
时间: 0.035s




--当有重复key旧数据存在，使用replace into 语句
REPLACE INTO test(title,uid) VALUES ('1234657','1001');

[SQL]REPLACE INTO test(title,uid) VALUES ('1234657','1001');
受影响的行: 2
时间: 0.140s

set case when then条件选择语句

如果需要针对某个字段的值针对不同条件设置不同值的时候，使用set 语法，该语法例子如下：

update table_name
set column_name=(
case
    when condition1 then value1
    when condition2 then value2
end
);

数据库转义

如果表名是sql里面的关键字，sql脚本可以加上双引号机型转义

授权

MySQL 数据库下user表中，Host和User为两个主键列（primary key），已经各版本下非空未设置默认字段。

GRANT 权限 ON 数据库.* TO 用户名@'登录主机' IDENTIFIED BY '密码' [with grant option]

权限： ALL,ALTER,CREATE,DROP,SELECT,UPDATE,DELETE
新增用户：权限为USAGE,即为："无权限",想要创建一个没有权限的用户时,可以指定USAGE
•with grant option：表示允许用户将自己的权限授权给其它用户

例子：grant all privileges on *.* to 'yangxin'@'%' identified by 'yangxin123456' with grant option;

GRANT SELECT ON test.user TO kaka@'10.155.123.55' IDENTIFIED BY '123456';

GRANT USAGE ON *.* TO 'kaka'@'10.155.123.55' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

对用户做了权限变更之后，一定记得重新加载一下权限，将权限信息从内存中写入数据库。

flush privileges;

四、回收权限

删除yangxin这个用户的create权限，该用户将不能创建数据库和表。

mysql> revoke create on *.* from 'yangxin@localhost';

mysql> flush privileges;

六、用户重命名

shell> rename user 'test3'@'%' to 'test1'@'%';

七、修改密码

1> 更新mysql.user表

mysql> use mysql;

# mysql5.7之前

mysql> update user set password=password('123456') where user='root';

# mysql5.7之后

mysql> update user set authentication_string=password('123456') where user='root';

mysql> flush privileges;

2> 用set password命令

语法：set password for ‘用户名'@'登录地址'=password(‘密码')

mysql> set password for 'root'@'localhost'=password('123456');

3> mysqladmin

语法：mysqladmin -u用户名 -p旧的密码 password 新密码

mysql> mysqladmin -uroot -p123456 password 1234abcd

注意：mysqladmin位于mysql安装目录的bin目录下

忘记密码无法登陆怎么处理

1> 添加登录跳过权限检查配置

修改my.cnf，在mysqld配置节点添加skip-grant-tables配置

[mysqld]

skip-grant-tables

2> 重新启动mysql服务

shell> service mysqld restart

3> 修改密码

此时在终端用mysql命令登录时不需要用户密码，然后按照修改密码的第一种方式将密码修改即可

在5.7之前修改密码：（使用update修改user表）

update mysql.user set authentication_string=password('123456') where host='localhost' and user='root';
在5.7修改密码：（因为废除了password字段，需要使用authentication_string）

update mysql.user set password=password('123456') where host='localhost' and user='root';
而在8.0已经不能用possword函数和set...语句，只能用：

alter user 'root'@'localhost' identified with mysql_native_password by '123456';
##如遇报错，先执行flush privileges

在mysql5.7.9版本之后废弃了password字段和password（）函数，并且加密方式默认不是使用mysql_native_password.

  在mysql8.0以上版本中caching_sha2_password和sha256_password认证插件比mysql_native_password插件提供的密码加密更安全，并且前者加密性能更好。由于caching_sha2_password这样优秀的安全和性能特征，让他作为mysql8.0的默认首选认证插件，而并不是mysql_native_password.

   所以mysql8.0默认是caching_sha2_password加密,5.7.9版本后的默认是mysql_native_password.

更改
vim /data/mysql/mysql_3306/my_3306.cnf

# 在[mysqld]中添加下边的代码

default_authentication_plugin=mysql_native_password

这个需要重启服务才生效。
//貌似mysql_native_password是个单向加密的算法，md5???

MySQL :: MySQL 8.0 Reference Manual :: 12.14 Encryption and Compression Functions

varchar 自动截取机制

如果分配给CHAR或VARCHAR列的值超过列的最大长度，则对值进行裁剪以使其适合。如果被裁掉的字符不是空格，则会产生一条警告。如果裁剪非空格字符，则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。

但是navicate可以自动截断超长数据（自测过），但在项目中执行sql语句的话反而会报错，考虑到是同一个数据库，不存在模式不同，那么可能性应该出现在jdbcDriver上

mysql大表增加字段问题和加锁问题

mysql在5.x版本时候对大表增加字段的时候可能会造成线上查询或者插入数据的死锁情况，Deadlock found when trying to get lock;try restarting transaction....

同时在批量更新时，因为表中没有加索引，会检索整个表导致更新非常慢，而每条更新sql提交事务都有个超时限制，后提交的事务等待前面处理的事务释放锁，但是在等待的时候超过了mysql的锁等待时间，就会引发异常从而导致锁表

锁相关知识：MySQL 全局锁和表锁 - keme - 博客园

update 走索引后会不会锁全表？_有关心情-CSDN博客

MySQL的innoDB锁机制以及死锁处理_net@core的博客-CSDN博客

事实上，MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了在InnoDB中发生死锁是可能的

//查看锁等待时间，单位为秒
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'

二级索引：叶子节点中存储主键值，每次查找数据时，根据索引找到叶子节点中的主键值，根据主键值再到聚簇索引中得到完整的一行记录。其实二级索引就是普通索引

更新记录加锁相关总结：更新表记录的时候，如果where使用到了二级索引的话，当MySQL预估扫描行数超过全表总数约 20% ～ 30% 时，即便有二级索引，也会直接升级为全表扫描。

这个结论的原因并不难理解，二级索引的顺序和主键顺序一般来说是不一样的，根据二级索引的顺序回表读数据时，实际上对于主键则很可能是随机扫描，因此当需要随机扫描的数量超过一定比例时（一般是20% ～ 30%），则优化器会决定直接改成全表扫描。

原因：

解决办法：

意向锁关系：

mysql的共享锁(S)、排他锁(X)、意向共享锁(IS)、意向排他锁(IX)的关系_通往架构师的成长学习之路-CSDN博客

mysql update 会自动加锁吗_「成都校区」浅析mysql的锁_weixin_39643336的博客-CSDN博客

行格式《MySQL是怎么运行的：从根儿上理解MySQL》——InnoDB存储引擎_ignorewarnings的博客-CSDN博客_从根上理解mysql

mysql update 会自动加锁吗_超全面的MySQL语句加锁分析_weixin_39710462的博客-CSDN博客

MySQL死锁系列-常见加锁场景分析 ☆☆MySQL死锁系列-常见加锁场景分析 - 程序员历小冰 - 博客园

mysql的锁类型 ☆☆☆mysql常见的死锁场景_qw790707988的博客-CSDN博客_mysql 死锁场景

mysql 如何避免死锁 mysql如何避免死锁_沉下心来，戒骄戒躁-CSDN博客

一分钟深入Mysql的意向锁——《深究Mysql锁》 ☆☆☆一分钟深入Mysql的意向锁——《深究Mysql锁》_爱雨轩-CSDN博客_mysql的意向锁

Innodb锁机制：Next-Key Lock 浅谈 - jyzhou - 博客园

☆☆☆☆☆☆ 【MySQL】MySQL中的锁机制 - 周二鸭 - 博客园

MDL 锁（metadata lock)

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

MDL 锁是系统默认会加的，但却是你不能忽略的一个机制。给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响

一般行锁都有锁超时时间。但是MDL锁没有超时时间的限制，只要事务没有提交就会一直锁注。

在rr级别下，(必须是select for update)默认的加锁类型就是next-key lock。这样可以防止新插入的数据导致的幻读问题。因为加的是next-key lock，所以锁定的范围变大了，相应得，并发性能也会降低，这需要权衡。

在其他级别下加的是行锁。比如我们在rc级别下，就可以用锁定读复现幻读问题：

因为InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围，按照这个方法是会和第一次测试结果一样。但是，当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。对于使用唯一索引来搜索唯一行的语句，只加记录锁不加间隙锁(这并不包括组合唯一索引）。InnoDB存储引擎还会对辅助索引下一个键值加上gap lock，

注意：通过主键或则唯一索引来锁定不存在的值，也会产生GAP锁定

而事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

一般的锁在事务执行过程中，如果有加锁操作，这个锁需要等事务提交时释放。

史上最详细MySQL全局锁和表锁_eygle's life-CSDN博客

mysqldump --single-transaction

MVCC多版本并发控制

正确的理解MySQL的MVCC及实现原理☆☆【MySQL笔记】正确的理解MySQL的MVCC及实现原理_长路漫漫的歇脚处-CSDN博客

延迟关联优化limit分页查询以及回表，覆盖索引，延迟关联等概念

InnoDB有两大类索引：

聚集索引(clustered index)
普通索引(secondary index)

InnoDB聚集索引和普通索引有什么差异？

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

（1）如果表定义了PK，则PK就是聚集索引；

（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；

（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

画外音：所以PK查询非常快，直接定位行记录。

InnoDB普通索引的叶子节点存储主键值。

当通过普通索引查找数据的时候，先从普通索引的B+树查找到对应的主键，然后通过聚焦索引去查找对应的记录。

上诉的描述过程基本上就是回表，如何避免回表，就要将需要查询的所有字段放入到联合索引中，通过联合索引找到id就不会回表查询了

不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。因为如果查询的字段没有在索引中，那么就需要在聚焦索引中找到行记录，然后找到值。用 explain 的结果，extra 列会出现：using index。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引（在MySQL中，只有Memory引擎显示支持哈希索引）、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引

延迟关联的原理是借助了覆盖索引，实际场景如下：

①select * from 表 A where 条件；（select * 中的*也可以是很多列，如a，b，c，d，e……）

假如表中数据有2000行，而经过where条件过滤后，符合的数据有50行，那么上边的sql写法存在对不需要的行中的数据先取出然后由于不符合where条件，所以最终舍弃。这里的关键点在于：提前取出了不需要数据行中的列。

这个问题如何解决？

改写sql为如下形式：

②select * from 表 A join （select B.id form 表 B where 条件）on 表A.id = 表B.id; （表A和表B为同一张表的不同别名）

这里的关键在于：通过使用覆盖索引先获取需要的键（一般为主键），再根据取到的键关联原表获得需要的行，这可以减少MYSQL扫描那些不符合条件的行。

但不是所有如sql①的写法改写为sql②后会提升性能，应用不对反而会下降，具体问题需要具体分析。

大概可以分三种情况：

总数据为4万行，符合条件的为3万行。改写后提升不是特别明显；

总数据为4万行，符合条件的为300行。改写后性能提升很明显；

总数据为50行，符合条件的为40行，改写后反而性能下降。

此外，一般分页时，如limit n，m，当n特别大时，通常需要用延迟关联来解决性能问题。

索引条件下推ICP

定义：索引条件下推(ICP), Index condition pushdown,简单的来讲，使用索引查询后，不立即进行回表查询，通过where条件中的字段(该字段也是位于索引中)进行过滤，将过滤之后的结果进行回表查询。相对于没有开启ICP,减少了回表查询的记录数

mysql-索引-索引条件下推(ICP) mysql-索引-索引条件下推(ICP)_面朝大海，春暖花开-CSDN博客

一起学习Mysql索引三（ICP,索引条件下推）_强哥叨逼叨-CSDN博客

全文索引

概念

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。

你可能会说，用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。

你可能没有注意过全文索引，不过至少应该对一种全文索引技术比较熟悉：各种的搜索引擎。虽然搜索引擎的索引对象是超大量的数据，并且通常其背后都不是关系型数据库，不过全文索引的基本原理是一样的。

版本支持

开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

MySQL 之全文索引 MySQL 之全文索引_潜心做事的博客-CSDN博客_全文索引

哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
在MySQL中，只有Memory引擎显示支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-Tree索引。值得一提的是，Memory引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

https://blog.csdn.net/yirentianran/article/details/79360920

explaiin分析

show full processlist和show processlist分析，事务以及锁超时

Mysql数据库采用InnoDB模式，默认参数:innodb_lock_wait_timeout设置锁等待的时间是50s，一旦数据库锁超过这个时间就会报错。

SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

SET GLOBAL innodb_lock_wait_timeout=500; //设置为500秒

SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

查询进程查询数据库线程情况，看是否有执行很慢的SQL show processlist;

在执行结果中可以看到是否有表锁等待或者死锁，如果有死锁发生，可以通过下面的命令来杀掉当前运行的事务：kill id // KILL 后面的数字指的是 trx_mysql_thread_id 值

再去查看innodb的事务表INNODB_TRX，看下里面是否有正在锁定的事务线程，看看ID是否在show full processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。

processlist命令的输出结果显示了有哪些线程在运行，不仅可以查看当前所有的连接数，还可以查看当前的连接状态帮助识别出有问题的查询语句等。

如果是root帐号，能看到所有用户的当前连接。如果是其他普通帐号，则只能看到自己占用的连接。showprocesslist只能列出当前100条。如果想全部列出，可以使用SHOW FULL PROCESSLIST命令

1、id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
2、user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
3、host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
4、db列，显示这个进程目前连接的是哪个数据库
5、command列，显示当前连接的执行的命令，一般就是休眠或空闲（sleep），查询（query），连接（connect）
6、time列，显示这个状态持续的时间，单位是秒
7、state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
8、info列，显示这个sql语句，是判断问题语句的一个重要依据，一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist。

这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：

Checking table ：正在检查数据表（这是自动的）。
Closing tables ：正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out ：复制从服务器正在连接主服务器。
Copying to tmp table on disk ：由于临时结果集大于 tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table ：正在创建临时表以存放部分查询结果。
deleting from main table ：服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables ：服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables ：正在执行 FLUSH TABLES，等待其他线程关闭数据表。
Killed ：发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked ：被其他查询锁住了。
Sending data ：正在处理 SELECT 查询的记录，同时正在把结果发送给客户端。
Sorting for group ：正在为 GROUP BY 做排序。
Sorting for order ：正在为 ORDER BY 做排序。
Opening tables ：这个过程应该会很快，除非受到其他因素的干扰。例如，在执 ALTER TABLE 或 LOCK TABLE 语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates ：正在执行一个 SELECT DISTINCT 方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table ：获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting ：修复指令正在排序以创建索引。
Repair with keycache ：修复指令正在利用索引缓存一个一个地创建新索引。它会比 Repair by sorting 慢些。
Searching rows for update ：正在讲符合条件的记录找出来以备更新。它必须在 UPDATE 要修改相关的记录之前就完成了。
Sleeping ：正在等待客户端发送新请求.
System lock ：正在等待取得一个外部的系统锁。如果当前没有运行多个 mysqld 服务器同时请求同一个表，那么可以通过增加 --skip-external-locking参数来禁止外部系统锁。
U pgrading lock ：INSERT DELAYED 正在尝试取得一个锁表以插入新记录。
Updating ：正在搜索匹配的记录，并且修改它们。
User Lock ：正在等待 GET_LOCK()。
Waiting for tables ：该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或 OPTIMIZE TABLE。
waiting for handler insert ：INSERT DELAYED 已经处理完了所有待处理的插入操作，正在等待新的请求。
大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。

show engine innodb status;

隐式提交

批量插入，遇到唯一主键冲突全失败失败还是部分失败

当批量插入的时候，遇到唯一键冲突的时候，不管冲突的数据是在前面插入还是在后面插入，都会导致插入失败，因此可以认为mysql的插入是原子性的和事务性的。

参考文章

高并发下接口幂等性解决方案高并发下接口幂等性解决方案_Gandoph的博客-CSDN博客_接口幂等性解决方案

DB主从一致性架构优化4种方法DB主从一致性架构优化4种方法

mysqldump备份数据

全局锁，有两种加全局锁的方式

flush tables with read lock FTWRL，解锁语句也是unlock tables

set global readonly=true

既然要全库只读，为什么不使用 set global readonly=true 的方式呢？确实 readonly 方式也可以让全库进入只读状态，但我还是会建议你用 FTWRL 方式，主要有几个原因：

一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。

二是，在异常处理机制上有差异。如果执行FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

三是，readonly 对super用户权限无效

在flush tables with read lock成功获得锁之前，必须等待所有语句执行完成（包括SELECT）。所以如果有个慢查询在执行，或者一个打开的事务，或者其他进程拿着表锁，flush tables with read lock就会被阻塞，直到所有的锁被释放。

当flush tables with read lock拿到锁后，必定flush data。对于MyISAM引擎，不光是刷新它自己的data，也刷新操作系统的data到disk上，有待验证

全局锁的典型使用场景是，做全库逻辑备份（mysqldump）。重新做主从时候
也就是把整库每个表都 select 出来存成文本。
以前有一种做法，是通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态。

数据库只读状态的危险性：

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就能停止。

如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

Mysql备份原理与工具mysqldump之--single-transaction，--master-data Mysql备份原理与工具mysqldump之--single-transaction，--master-data_小楼一夜听春雨，深巷明朝卖杏花-CSDN博客

mysqldump在备份数据的时候默认--lock-all-tables（会锁住整个mysql数据库中的所有表），加上--single-transaction 的原理是导数据之前会加上事务，不会锁表，来确保拿到一致性快照视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的

但是--single-transaction有使用要求：
1 只对支持事务的存储引擎有效果
2 不能执行ddl操作，因为 ddl操作会改变字典表，而字典表多数是myisam的
3 事务隔离级别为 RR

所以如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法，myISAM这种引擎只能使用FTWRL全局锁，这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一

mysql最左优先匹配原则

最左匹配原则最左匹配原则 - Mr-blue - 博客园

比如mysql 单列索引是将该列数据当做关键字构建一颗b+tree，但是组合索引是如何实现的呢?比如两个字段的组合索引，SELECT * FROM TABLE1 WHERE A=22 AND b=33 ; 是要建两棵树吗？如果只建立一颗树，那b列是如何存放的？

一个索引，创建一个树，也只能创建一个树，不管是单列，还是多列联合。

一棵树，如果是单列，就按这列数据进行排序，如果是多列，就按多列数据排序，首先根据第一列排序，在第一列一样的情况下，第二列再排序。例如有（1,1）（2,2）（2,1）（1,2）

那在索引中的叶子节点的数据顺序就是（1,1）（1,2）（2,1）（2,2）

mysql联合主键和联合唯一索引

问题

当Mysql中建立的联合索引，只要索引中的某一列的值为空时(NULL)，即便其他的字段完全相同，也不会引起唯一索引冲突。但是联合主键是不允许内部的字段为空的，所以可空的字段不能作为联合主键来使用

原因

Mysql官方文档中有这样的解释

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.

唯一约束对NULL值不适用。原因可以这样解释：比如我们有一个单列的唯一索引，既然实际会有空置的情况，那么这列一定不是NOT NULL的，如果唯一约束对空值也有起作用，就会导致仅有一行数据可以为空，这可能会和实际的业务需求想冲突的，所以通常Mysql的存储引擎的唯一索引对NULL值是不适用的。这也就倒是联合唯一索引的情况下，只要某一列为空，就不会报唯一索引冲突。