复习mysql + 高级

最新推荐文章于 2023-02-09 22:31:52 发布

熊猫盖子

最新推荐文章于 2023-02-09 22:31:52 发布

阅读量1k

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/xiongmaogaizi/article/details/114927882

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySql高级

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 Mysql是开源的，所以你不需要支付额外的费用。Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL使用标准的SQL数据语言形式。Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。

查看安装目录ps -ef|grep mysql

修改字符集
show varialbes like '$char%';

set character_set_server=’gbk’;

自带优化器：Optimizer

连接、服务、引擎、存储

从from开始

复习join

由于mysql不支持full join但能通过外连接 + union

索引（查找和排序）

二叉树弊端之一：二叉树很可能会发生两边不平衡的情况。

B-TREE: (B:balance) 会自动根据两边的情况自动调节，使两端无限趋近于平衡状态。可以使性能最稳定。(myisam使用的方式) B-TREE弊端：(插入/修改操作多时，B-TREE会不断调整平衡，消耗性能)从侧面说明了索引不是越多越好。

B+TREE:Innodb 所使用的索引

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，
复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。

主键索引

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id)
);
unsigned (无符号的)使用 AUTO_INCREMENT 关键字的列必须有索引(只要有索引就行)。
CREATE TABLE customer2 (
id INT(10) UNSIGNED ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id)
);
单独建主键索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no);
删除建主键索引：
ALTER TABLE customer drop PRIMARY KEY ;
修改建主键索引：必须先删除掉(drop)原索引，再新建(add)索引

单值索引

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)
);
随表一起建立的索引索引名同列名(customer_name)单独建单值索引：
CREATE INDEX idx_customer_name ON customer(customer_name);
删除索引：
DROP INDEX idx_customer_name ;

唯一索引

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_no)
);
建立唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。
单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
删除索引：
DROP INDEX idx_customer_no on customer ;

复合索引（姓+名、姓）（不可有名无行姓）

例如：col1 = A and col2 = B and col3 = C 如果是复合索引，就会先通过col1筛选，再通过col2筛选，最后通过col3筛选，不必检索全文

在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)当表的行数远大于索引列的数目时可以使用复合索引

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_name),
KEY (customer_no,customer_name)
);
单独建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);
查询：
SELECT * FROM customer where customer_no = "" and customer_name = "";
删除索引：
DROP INDEX idx_no_name on customer ;

复合索引与单值索引有什么区别？
复合索引：
create index idx_no_name on emp(no,name); // no 与 name 有同一个索引 idx_no_name
单值索引：
create index idx_no on emp(no);
create index idx_name on emp(name);

疑惑：同一数据引擎，都是采用一样的索引类型(B-TREE或B+TREE),复合索引与单值索引的区别是什么？该怎么使用？

推荐用索引：1、主键索引 2、作为查询条件where后 3、外键索引 4、频繁更新不建议永索引 5、排序字段 6、统计分组

A 表关联 B 表：A join B 。 on 后面的连接条件既 A 表查询 B 表的条件。所以 B 表被关联的字段建立索引能大大提高查询效率因为在 join 中，join 左边的表会用每一个字段去遍历 B 表的所有的关联数据，相当于一个查询操作

group by 和 order by 后面的字段有索引大大提高效率

不推荐用索引：1、表记录少 2、经常改的表 3、where里不用的字段 4、重复数据多+经常排序的表字段

mysql一般300万后性能开始下降，索引建多了影响增删改的效率

mysql查询优化器

处理常用表达式、查询条件简化、去掉无用条件、结构调整

cpu: 最大的压力在于比较

Explain

id

select_type

SIMPLE --- 简单查询
PRIMARY --- 有子查询的外层查询
DERIVED --- FROM中的子查询
SUBQUERY --- SELECT/WHERE中的子查询
UNION --- 出现在UNION之后的SELECT查询

type

通过type可以看出查询语句读优化程度，最好达到ref

细分

system：只有一行对表
const：一次索引就能找出
eq_ref：唯一值

ref :查询结果值非唯一，用一条索引查询多条数据

对某一列建了索引后，explain查询type为 ref

range

index：和All都是读全表. index读索引树，all树读所有值（比All快）

possible_keys：理论上需要用到的索引

key：实际上用到的索引

特殊情况：当创建当索引数和查询数相同时,即创建索引时标注了多少列，查询索引时查询多少列，就会导致possible_keys为null，但key不为null （覆盖索引）

key_len：表中索引使用字节数

ref：

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

表示第二条数据 type 为 ref，而ref为 t2.col1，表示中t1引用了t2的col1，而ref为const表示中引用了常量 'ac'

问题：奇怪：同一张表，对两列deptName，address创建了复合索引，但是explain分别关键字查询，返回但只有前面的address，才显示出使用索引查询rows为1

当交换复合索引列为deptName放前面，使用explain，只有前面的deptName，才显示出使用索引查询rows为1

解决：（复合索引查询时必须有最前面定义的一个索引列）

当deptName 的列有多个相同行时，此时就可配合address进行复合索引查找

rows

通过对t_dept建立复合索引，可以减少全文检索次数

extra

Using filesort 用索引时，未按内部顺序进行索引（产生内排序，必须遵守内排序）

创建复合索引 col1,col2,col3 查询时如果不按顺序来，就相当于使用了一个外部索引排序

Using temprary 用索引时，使用 order by / group by 创建了临时表

Using index 表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！

如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

覆盖索引（select 索引行，而不select*）

explain 测试题

按id排序，查出最大的一个 order by id desc limit 1;

索引优化

未优化

-----------------------

两种创建索引的方式

ALTER TABLE `article` ADD INDEX idx_artle_ccv(`category_id`,`comments`,`views`);
create index idx_article_ccv on article(category_id,comments,views);

show index from article;

---------------------------

复合索引，创建的内置顺序，如果where中的某个查询条件为范围（>、<）就会认为顺序失效，报Using filesort

---------------------

--------------------

---------------------

两表优化案例

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL auto_increment,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL auto_increment,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(bookid)
);
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));

INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));

索引左连接加在右边效果好

索引左连接加在左边效果不好

因为左连接包含全部的左边表，右边需要搜索，所以加索引效果好

同理：索引右连接加在左边效果好

三表查询

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL auto_increment,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(phoneid)
);
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
create index Y on book(`card`);
create index Z on phone(`card`);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;

未建索引

表多了就会用连接缓存（Using buffer）

建索引后

总结：

1、LEFT JOIN 左连接：左表是驱动表，右表是被驱动表
2、驱动表加索引无效，索引加到被驱动表上（左连接，查的是右表，被驱动表，加索引能快）

测试：A表140多条数据，B表20万左右的数据量
select * from A a left join B b on a.code=b.code
执行时间：7.5s

select * from B b left join A a on a.code=b.code
执行时间：19s

结论：小表驱动大表优于大表驱动小表

https://blog.csdn.net/qq_20891495/article/details/93744495

desc tbl_user; 查看表结构

索引优化

 CREATE TABLE staffs (
	 id INT PRIMARY KEY AUTO_INCREMENT,
	 NAME VARCHAR (24)  NULL DEFAULT '' COMMENT '姓名',
	 age INT NOT NULL DEFAULT 0 COMMENT '年龄',
	 pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
	 add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
 ) CHARSET utf8 COMMENT '员工记录表';
 INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
 INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
 INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
 INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());
 SELECT * FROM staffs; 
 ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

索引失效

1. 全职匹配：
个数+顺序（带头大哥不能死）

2. 最左前缀法则
当不按 name、age、pos的顺序查询，就会导致索引失效（age + pos、name + pos）（中间兄弟不能断）

3. 不要在索引列上做任何操作（索引列上无计算）
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描，即便两条查询都能正常执行，但会导致索引失效。

4 .范围之外全失效
存储引擎不能使用索引中范围条件右边的列（当索引条件中当某个索引列为范围<、>、bet），使用范围时，就是排序，不是通过索引查询

5. 尽量使用覆盖索引
使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

当出现范围当时候，就不会进行索引查询，（where name='July' and age>25 and pos='dev'）name后面的都不会进行索引查询

6. 使用不等于无法使用索引
不等于（!=或<>）导致无法使用索引而全表扫描

7. is null 或 is not null也无法使用索引

8. like %加右边

但是like %加右边搜不到%xx%的数据，只能搜到 xx、xx%的数据
并且 like %xx% 使得索引失效

问题：如何使得 like %xx% 索引不失效

解决：创建（name，age）索引，使用覆盖索引，只要符合覆盖索引，like %xx%，就能生效

注意：覆盖索引（name、age）但 id 为主键索引，也能生效

9. 字符串不加单引号索引失效（字符串里有引号）

varchar类型必须加单引号，否则进行了隐式类型转换（即在索引列上做了操作，导致索引失效）

10. 少用or

练习：

索引题分析

group by c3,c2临时表，未按内部顺序

关键：
1. 看索引使用个数 : (1) 顺序执行索引的个数 (满足覆盖索引就算无序也行) 例如：索引:(c1234) 查询:where c1,c2,c3,c4 c4,c3,c2,c1
（2）顺序执行有 < 、> 范围等，算一个索引(range) 例如：查询 where c1= , c2< 算使用了2个索引
（3）order by/group by 不算一个索引查询例如： where c1= , order by c2 算使用了1个索引
当有order by c2，那么c3就算出现在前面，也不会用索引例如： where c1= ,c3= , order by c2 算使用1个索引（只有c1用了）

2. 看是否有Using filesort (1) 当有 order by 但未按顺序查询（order by c3,c2）特殊情况：where c1= ,c2= , order by c3,c2 （此时虽然c3,c2无须，但整体查询索引有序）
(2) 当有order by 但中间某个索引列为范围(<,>)

mysql 5.7 出错：ONLY_FULL_GROUP_BY 报错

explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;

MySQL 5.7.5及以上功能依赖检测功能。https://www.cnblogs.com/chancy/p/10026097.html

出现：
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bigData.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

group by 分组之前必排序

练习：

索引优化

：：覆盖索引最好不用*

VAR引号不可去，SQL高级也不难！

查询截取分析

永远小表驱动大表

关联查询优化

子查询优化

有索引：inner join > in > exists
无索引：exists > in > join ( inner join > left join )

有索引的情况下用 inner join 是最好的其次是 in ，exists最糟糕
无索引的情况下用小表驱动大表因为join 方式需要distinct ，没有索引distinct消耗性能较大所以 exists性能最佳 in其次 join性能最差？
无索引的情况下大表驱动小表in 和 exists 的性能应该是接近的都比较糟糕 exists稍微好一点超不过5% 但是inner join 优于使用了 join buffer 所以快很多如果left join 则最慢

in: select * from A where id in (select id from B) A 大 in B 小

exists: select * from A where exists (select 常量 from B where B.id = A.id) A 小 exists B 大

为排序使用索引 Order by 优化

尽可能使用index方式排序

Using filesort : 使用order by时，索引列顺序出错

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

第二种中，where a = const and b > const order by b , c
不会出现 using filesort ,b , c 两个衔接上了
但是：where a = const and b > const order by c 将会出现 using filesort 。因为 b 用了范围索引，断了。而上一个 order by 后的b 用到了索引，所以能衔接上c

如果不在索引列上，filesort有两种算法：

mysql就要启动双路排序和单路排序

多路的问题：

多路排序需要借助磁盘来进行排序。所以取数据，排好了取数据。两次 io操作。比较慢
单路排序，将排好的数据存在内存中，省去了一次 io 操作，所以比较快，但是需要内存空间足够。

单路的问题：

在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略：

提高Order By的速度：

1. Order by时select * 是一个大忌，只Query需要的字段，这点非常重要。在这里的影响是：
1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。

2. 尝试提高 sort_buffer_size不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的

3. 尝试提高 max_length_for_sort_data提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率.

Order by排序使用索引 where 后第一个为常量，则order by 可以用索引

limit优化

EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY deptno LIMIT 10000,40
优化：先利用覆盖索引把要取的数据行的主键取到，然后再用这个主键列与数据表做关联：(查询的数据量小了后)EXPLAIN SELECT SQL_NO_CACHE * FROM emp INNER JOIN (SELECT id FROM emp e ORDER BY deptno LIMIT 10000,40) a ON a.id=emp.id
最后比较一下查询速度：优化前：优化后：实践证明：
①、order by 后的字段（XXX）有索引
②、sql 中有 limit 时，当 select id 或 XXX字段索引包含字段时，显示 using index 当 select 后的字段含有 order by 字段索引不包含的字段时，将显示 using filesort

group by优化

慢查询日志

* MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，

* 具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

* 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。当然，

如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。
全局变量设置
对当前连接不影响对当前连接立刻生效

如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
修改my.cnf文件，[mysqld]下增加或修改参数
slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件
slow_query_log =1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
关于慢查询的参数slow_query_log_file ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）

开启了慢查询后，什么样的sql才会被记录到慢查询日志？

这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，
命令：SHOW VARIABLES LIKE 'long_query_time%';
可以使用命令修改，也可以在my.cnf参数里面修改。假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。

使用命令 set global long_query_time=1修改为阙值到1秒钟的就是慢sql 修改后发现long_query_time并没有改变。
需要重新连接或新开一个会话才能看到修改值。 SHOW VARIABLES LIKE 'long_query_time%';
或者通过set session long_query_time=1来改变当前session变量;

# 查看慢查询状态
   SHOW VARIABLES LIKE '%slow_query_log%';
# 设置慢查询状态
   set global slow_query_log=1;
# 查看慢查询判定时间
   SHOW VARIABLES LIKE 'long_query_time%';
# 设置慢查询判定时间
   set global long_query_time=3;
# 查询睡眠4秒
   SELECT SLEEP(4);
# 查看当前慢查询数量
   show global status like '%Slow_queries%';

【mysqld】下配置：
slow_query_log=1;
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3;
log_output=FILE

mysqldumpslow

mysql日志分析工具
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
另外建议在使用这些命令时结合 | 和more 使用，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

批量数据脚本

建表

设置 function 参数（函数）

创建函数，假如报错：This function has none of DETERMINISTIC......# 由于开启过慢查询日志，因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

# 这样添加了参数以后，如果mysqld重启，上述参数又会消失，

永久方法：
windows下my.ini
[mysqld]加上log_bin_trust_function_creators=1
linux下 /etc/my.cnf下my.cnf
[mysqld]加上log_bin_trust_function_creators=1

查看某个表结构： desc tablename；

创建函数

delimiter $$
...
end $$ 暂时将；结尾执行变为 $$结尾执行

创建存储过程

调用存储过程

show Profile

show variables like 'profiling'; 显示profiling状态
set profiling=on; 设置profiling
show PROFILES; 展示sql执行时间
show profile cpu,block io for query 250; 针对某条sql，显示详细时间花费清单

全局查询日志

在mysql的my.cnf中，设置如下：
#开启general_log=1 # 记录日志文件的路径general_log_file=/path/logfile
#输出格式log_output=FILE
命令set global general_log=1; #全局日志可以存放到日志文件中，也可以存放到Mysql系统表中。存放到日志中性能更好一些，
存储到表中set global log_output='TABLE';
此后，你所编写的sql语句，将会记录到mysql库里的general_log表，
可以用下面的命令查看 select * from mysql.general_log;

mysql锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

分类

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。一种提高共享资源并发发性的方式是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

表锁（偏读）

加表锁的表，建表时，指定myisam搜索引擎

【手动增加表锁】
lock table 表名字1 read(write)，表名字2 read(write)，其它;
【查看表上加过的锁】
show open tables;
【释放表锁】
unlock tables;

加读锁（读阻塞写读例子）
我们为mylock表加read锁(读阻塞写例子) session_1session_2获得表mylock的READ锁定
连接终端当前session可以查询该表记录
其他session也可以查询该表的记录
当前session不能查询其它没有锁定的表
其他session可以查询或者更新未锁定的表
当前session中插入或者更新锁定的表都会提示错误：
其他session插入或者更新锁定表会一直等待获得锁：
释放锁 Session2获得锁，插入操作完成：

（我能读，他能读，我不能读其他，他能读其他，我不能改，他一改就阻塞，我释放锁，其他读修改才会成功）

我加了a表读锁，我只能读a表，不能改a表，不能读其他表
别人可以读a表，别人可以读其他表，别人改a表就会阻塞，直到我解锁a表位置。

加写锁
mylockwrite(MyISAM) session_1session_2获得表mylock的WRITE锁定，待Session1开启写锁后，
session2再连接终端
当前session对锁定表的查询+更新+插入操作都可以执行：
其他session对锁定表的查询被阻塞，需要等待锁被释放：
在锁表前，如果session2有数据缓存，
锁表以后，在锁住的表不发生改变的情况下session2可以读出缓存数据，
一旦数据发生改变，缓存将失效，操作将被阻塞住。释放锁 Session2获得锁，查询返回：

（我能读、能改，他一读就阻塞，他可以读缓存，数据发生改变，缓存失效）

小结：

结论：结合上表，所以对MyISAM表进行操作，会有以下情况：
1、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
2、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞

事务ACID

更新丢失(Lost Update)

脏读(Dirty Reads) 一个事务中，读了未提交的修改读数据

不可重复读(Non-Repeatable Reads) 一个事务中，读了已经提交但变更但数据

幻读(Phantom Reads) 一个事务中，读了新增数据

事务隔离级别

可重复读：保证一个事务内，读到的数据是一样的，就算过程中修改了，也不影响。

mysql默认事务隔离机制：可重复读（读到的数据是一致的，可以重复读，就算改变了也不影响）

行锁

由于innodb默认操作自动提交
测试行锁，

set autocommit - 0;关闭自动提交，没有提交commit，就会一直在行锁内。
在seesion1对行修改过程中，其他session对行的修改，就会被阻塞。

默认行锁---在update 某行时

操作不同行，无影响

索引使用不当，会使得行锁升级为表锁（操作行的索引失效，导致行锁变表锁）

Session_1Session_2正常情况，各自锁定各自的行，互相不影响，一个2000另一个3000由于在column字段b上面建了索引，如果没有正常使用，会导致行锁变表锁比如没加单引号导致索引失效，行锁变表锁被阻塞，等待。只到Session_1提交后才阻塞解除，完成更新

当session1对行修改，该行b为varchar未加 ' 导致索引失效，表锁升级为行锁，此时，只要session1不commit，session2就修改不了。

间隙锁危害

间隙锁带来的插入问题 Session_1Session_2阻塞产生，暂时不能插入commit;阻塞解除，完成插入

【什么是间隙锁】当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP Lock）。

【危害】因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

当一个session范围查询多个行，但当这个范围中不存在的键值记录，Innodb也会对其加锁（间隙锁），被锁定的不存在的监视记录，会造成无法插入的情况。

锁定一行：
select ... ... lock in share mode (读锁)
select ... .... for updtae (写锁)

select 也能手动加行锁

共享锁
select * from table1 where a=8 lock in share mode;

共享锁(Share Lock) 共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。用法SELECT ... LOCK IN SHARE MODE;
在查询语句后面增加 LOCK IN SHARE MODE ，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表（行？），而且这些线程读取的是同一个版本的数据。

排他锁
select * from table1 where a=8 for update;

排他锁（eXclusive Lock）共享锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。用法SELECT ... FOR UPDATE;
在查询语句后面增加 FOR UPDATE ，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

【如何分析行锁定】通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

mysql>show status like 'innodb_row_lock%';

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。
最后可以通过SELECT * FROM information_schema.INNODB_TRX\G;来查询正在被锁阻塞的sql语句。

优化建议

mysql主从复制

docker mysql:5.7.19
mysql配置文件： /etc/mysql/

my.cnf :主配置文件
打开文件夹 mysql.conf.d / mysqld.cnf

主
启用二进制日志
从

主机上建立账户并授权：

主机
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.17.0.7' IDENTIFIED BY '123456';

执行完此步，不动主机，防止主机状态变化

从机
CHANGE MASTER TO MASTER_HOST='172.17.0.6',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysqlbin.000001',MASTER_LOG_POS=892;

必须同时Yes，才算成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

排错集锦：
show slave status\G 报错：
1. Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

解决思路：
1. stop slave; 2.reset slave; 3. start slave;

问题： docker mysql 错误日志在？

主从复制的使用

主机新建库、新建表、insert记录，从机复制