007mysql-高级

1.基础命令

查看mysql是否安装成功的命令

 ps -ef | grep mysql
 
 cat /etc/group|grep mysql
mysql:x:128:

$ mysqladmin --version
mysqladmin  Ver 8.42 Distrib 5.7.34, for Linux on x86_64

启动：
service mysql start
service mysql  stop

top

linux下
设置mysql登录密码：

/usr/bin/mysqladmin -u root password hahaha369

设置开机启动

chkconfig --list | grep mysql

chkconfig mysql on // 设置开机启动，但是ubuntu系统下没这个命令

ntsysv 查看开机启动的服务
开机启动的服务前面有一个 星号

create database db01;
use db01;
show tables;
create table user(id int not null,name varchar(20));
insert into user values(1,"zhangshan");
select * from user;

编码使用 utf8mb4,,,,
查看字符集
mysql> show variables like '%char%';
mysql> show variables like 'character%';

建库之后再修改字符集编码，也识别不了，重启也不行，只有再改动字符集之后再新建的库才能识别修改之后的字符集编码，所以一般安装完数据库之后就修改字符集编码，和只是用英文

针对不同的履行路况换不同的车的引擎，
根据不同的数据，切换不同的数据存储引擎

mysql分层概念。插件式的可拔插结构

和其他数据库相比，Mysql架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎将查询处理和其他的系统任务以及数据的存储提取相分离，这种架构可以根据业务的需求和实际需要选择合适的存储引擎
1、连接层：最上层一些客服端和连接服务
2、服务层：核心服务功能
3、引擎层，数据库的存储和提取
4、存储层，将数据存储在硬件的磁盘文件系统上

mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0,00 sec)



mysql> show engines;

阿里淘宝

2.索引优化分析

都使用过哪些招？
top命令查看
问题复现排查
程序员sql写的慢，还是磁盘空间满了

找到是sql慢之后：
性能下降SQL慢
执行时间长
等待时间长
1、查询语句写的烂
2、索引失效（建了索引，没用上）
单值索引，复合索引
3.关联查询太多join（设计缺陷，不得已的需求）
4.服务器调优及各个参数设计（缓冲，线程数）

单值索引

select * from user where name = ' ';
create index ind_user_name on user(name);//单值索引，只给一个表的一个字段建立一个索引

select * from user where name = ' ' and email = '@@@';
create index ind_user_nameEmail on user(name,email);//复合索引，给一个表的多个字段建立一个索引，经常使用这几个字段一起作为条件做查询

3、索引

3.1、什么是索引

索引是帮助mysql高效获取数据的数据结构。
可以理解为：排好序的快速查找的数据结构
索引会影响到where后面的查找，和order by后面的排序。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据。这样就可以在这些数据结构上实现高级查找算法，这种数据结构，就是索引
update更新慢，数据在改，索引也在改。
索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存在于磁盘中、
df -h 查看磁盘空间
我们平时所说的索引，一般指B树（多路搜索树）结构组织的索引、其中聚集索引、次要索引、复合索引、前缀索引、唯一索引，默认都是使用B+树索引。除了B+树索引之外还有哈希索引。

3.2、优势，劣势

优势：提高数据检索效率，降低数据库io成本
通过索引列对数据进行排序，降低数据排序的成本，降低了cpu的消耗

劣势：
索引也是一张表，保存了主键与索引字段，并指向实体表的记录，所以索引列也要占空间，
2.虽然索引提高了查询速度，但是降低了更新表的速度，：增删改。更新表的时候，mysql还要保存一下索引文件，每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
3、索引只是提高效率的一个因素，如果mysql有大数据量的表，就需要研究最优秀的索引。

3.3索引的分类

单值索引，一个索引只有单个列，一个表可以有多个单值索引。
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：一个索引包含多个列，
语法：
创建：
create index indexname on mytable (columnname(length));
alter mytable add index indexname on mytable (columnname(length));
删除
drop index indexname on mytable;
查看
show index from tablename;

如下4种方式更改数据表的索引
alter table tbl_name add primary key (column list);添加一个主键，意味着索引值唯一，切不为空
alter table tbl_name add unique index_name (column_list)创建的索引值必须是唯一的，
alter table tbl_name add index index_name(column_list),添加普通索引，索引值可以出现多次，
alter table tbl_name add fulltext index_name(column_list),添加全文索引

3.4，mysql的索引结构

B树索引（B+树索引，非叶子节点，只存标志指向往哪一路搜索的方向，而不存数据库的数据）
哈希索引
full-text索引
R树索引

3.5，哪些情况需要创建索引，那些情况不需要创建索引

1、主键自动建立索引
2、频繁作为查询条件的字段应建立索引
3、查询中与其他表关联的字段，外键关系建立索引

4、频繁更新的字段不适合创建索引
5、where子句里用不到的字段不要创建索引
6、单值/复合索引？（在高并发下倾向于复合索引）
7、查询中排序字段，排序字段若通过索引去访问，将大大提高排序速度
8、查询中统计或分组的字段，
不建：
1、表记录太少（300万）
2、经常增删改的表，不建
3、数据重复，且分布平均的表字段。

一个索引的选择性越接近1，这个索引的查询效率就越高

3.6.性能分析

一、mysql query optimizer
（mysql自己优化一下，并不见得是dba认为是最优的）
二、mysql常见瓶颈，

• 1、cpu在饱和的时候，一般发生在数据装入内存或从磁盘上读取数据的时候，
• 2、磁盘IO瓶颈，发生在装入数据远远大于内存容量的时候，
• 3、服务区硬件性能瓶颈：top,free,iostat,和vmstat来查看系统的性能瓶颈
  三、Explain
  类似一个插件，可以还原模拟性能优化器如何处理我们写的sql语句，
  怎么用？
  explain + sql语句。
  执行计划包含的信息

能干嘛？
1、表的读取顺序，id相等不相等，如何加载，
2、数据读取操作的读取方式，（select type:simple,primary,subquery,derived（延伸查询）,union,union result）
3、哪些索引可以使用
4、那些索引被实际使用
5、表之间的引用
6、每张表有多少行被优化器查询

mysql无法用索引进行排序的排序称为文件内排序，
sql语句最后使用一个：\G意思是竖着排
\G之后就不能再使用分号了、
extra字段中
1、如果出现filesort（九死一生）就意思排序排的不好，需要优化，没有全部使用给出的索引排序，而是通过文件内mysql优化器，又自己排序了一遍。
2、如果出现 using temporary,就是有死无生了。更严重。得先建立临时表，然后才能处理，最后再把临时表删除
3、如果出现using index,表示相应的select使用了覆盖索引，避免了使用表中的数据，如果同时出现using where,表示索引被用来执行索引键值的查找。如果没有出现using where,表示索引只用来读取数据，而没有执行查找动作。
查询的列只从索引中就可以获得，要查询的列，被索引覆盖。
4、using where,表明使用了where过滤
5、using join buffer,使用了连接缓存
6、impossible where,出错了，不能既等于1，又等于2

范围查询会导致索引失效，order by 导致的索引失效问题，建立索引的时候，不要使用关于范围的字段。

增加索引

alter table 'book' add index Y ('card');

create index idx_test03_c1234 on test03(c1,c2,c3);
删除
drop index Y on 'book';
执行查询的性能识别
explain select * from class left join book on class.card = book.card;

左连接，在右表加索引，因为左表一定是要全局扫描的

三表连接：

select * from class   left join book on class.card = book.card left join plone on book.card = phone.card;

第3张表建索引
alter table 'phone' add index z ('card');
alter table 'book' add index Y ('card');

notes:

因此索引应该放在经常查询的字段当中
1、尽可能减少join语句中的nestedloop 的循环总次数，“永远用小的结果集驱动大的结果集
”
2.优先优化循环里的内层循环
3、保证join语句中被驱动表上的join条件字段已经被索引
4、当无法保证被驱动表的join条件字段被索引且内存资源充足前提下，不要吝啬joinBuffer的设置。

3.7、索引失效

1、全值匹配我最爱
2、最佳左前缀法则
《带头大哥不能死，中间兄弟不能断》
3、不在索引列上做任何操作（计算，函数，自动or手动类型转换）会导致索引失效，而转向全局扫描。
《索引列上少计算》
4。存储引擎不能使用索引中范围条件右边的列。
《范围之后全失效》
5、尽量使用覆盖索引（只访问索引查询（索引列和查询列一致），减少select *
6、mysql 在使用不等于（!= 或者<>），的时候无法使用索引会导致全表扫描
7、is null,is not null,也无法使用索引
8、like 以通配符开头（‘%abc…’）mysql索引失效会变成全表扫描(like查询把%写到右边，能避免全局扫描)
《%百分like加右边》
9、字符串不加单引号，索引失效（varchar类型一定不能失去单引号）
10、少用or,用它来连接时会索引失效

using where,去表里检索，using index,在索引中就能找出来了

explain select name,age,pos from staffs where name like '%july%';

面试题：解决like ‘%字符串%’时，索引不被使用的方法？？
可使用覆盖索引来解决这个问题。
id是主键索引，复合索引B+树叶子结点，会保存主键，用来做回表查询，所以复合索引文件包含主键，所以主键可以被覆盖

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

用到了c1,c2用来查找，c3用来排序
总结：where以索引列为条件时不需要遵循索引列的顺序，但是排序需要按顺序，不然就会filesort.(但是用于排序的字段如果已经是常量之后，也不会出现filesort)

explain select * from test03 where c1 = 'a1' and c3 = 'a4' group by c3, c2;
会出现filesort,和using temporary
group by表面叫分组，分组之前必排序，索引要按顺序，不然会有临时表的产生和删除。 

口诀：
全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
like百分写最右，覆盖索引不写星
不等空值还有or，索引失效要少用
var引号不可丢，sql高级也不难

4. 查询截取分析

4.1查询优化

1.explain
1.复现，观察，至少跑一天，看看生产的慢sql情况
2.开启慢查询日志，设置阈值，比如超过5秒钟就是慢sql，并将它抓取出来。
3.explain + 慢sql分析
4.show profile
5.运维经理，dba,进行sql数据库服务器的参数调优。

总结：
1.慢查询开启，并捕获
2.explain +慢sql分析
3.show profile 查询sql在mysql服务器里面的执行细节和声明周期
4、sql数据库服务器的参数调优

1、小表驱动大表，小的数据集驱动大的数据集

select * from A where id in (select id from B)
等价于：
for select id from B
for select * from A where A.id = B.id
当B表的数据集小于A时候，使用in更优


select * from A where exists(select 1 from B where B.id = A.id)
等价于：
for select * from A 
for select * from B where B.id = A.id
当A表小时候，用exists更优

exists:
select … from table where exists(subquery)
将主查询的数据，放到子查询中做条件验证，根据验证结果（T or F)来决定主查询的数据是否保留
in变成exists

select * from tbl_emp e where e.deptId in(select id from tbl_dept d);

select * from tbl_emp e where exists (select 1 from tbl_dept d where d.id = e.deptId);

2、order by 关键字优化

order by 没有优化，order by 后面的字段应该和建立索引时候的字段顺序保持一致，而where后面的字段只要有带头大哥就好，where后面的顺序可以被mysql优化器优化，而order by 没有优化器优化
order by 也是最佳左前缀匹配，
尽可能索引列上完成排序操作，建立索引的时候就排了一次序，再排的时候尽量使用索引排序就会少一次排序。

如果不在索引列上，filesort有两种算法：双路排序，单路排序
mysql4.1之前是双路排序，两次读取磁盘。从磁盘取排序数据，在buffer进行排序，再从磁盘取其他数据
后来出了单路算法。但也有偷鸡不成蚀把米的情况，如果单路没有解决的话，会比直接双路更糟。不过总体单路优于双路。
单路如果一次能完整获取数据，就好，如果不能就坑爹了。就会变成多路。看mysql的配置文件中的sort_buffer的大小，从磁盘取进来（会创建临时文件，），排序，再取，再排、

提高order by 的速度：
1.不使用select * ,
2.尝试提高sort_buffer_size的大小
3.尝试提高max_length_for_sort_data大小

为排序使用索引
1.mysql两种排序方式：文件排序（using filesort）或扫描有序索引排序(using index)
2.mysql能为排序和查询使用相同的索引

3.group关键字优化

group by 是先排序再分组，遵照索引建的最佳左前缀。
当无法使用索引列，增大max_length_sort_data和sort_buffer_size
where 高于having，能写在where限定的条件就不要去having限定了。

4.2.慢查询日志

1.是什么

是一种日志记录， 用来记录响应时间超过阈值long_query_time的语句，会被记录到慢查询日志中。

2.怎么玩

默认没有开启，需要手动开启，
show variables like ‘%slow_query_log%’;
开启：
set global slow_query_log = 1; 只对当前数据库有效，mysql重启之后就失效了、
想永久生效：
修改配置文件：my.cnf
[mysqld]下增加或修改参数：
slow_query_log和slow_query_log_file后，重启服务
slow_query_log = 1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
存放的路径会是：host_name_slow.log

查看默认设置的慢sql
show variables like ‘long_query_time%’;
设置慢的阈值时间：
set global long_query_time = 3;
为什么修改之后看不到变化？
需要重新开启一个会话才能看到，
show global variables like ‘long_query_time’;
记录慢sql之后并后续分析

查询当前系统中有多少慢查询记录
select sleep(4);
然后在那个记录慢sql的日志文件中查看就好了

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

3.日志分析工具mysqldumpslow

查看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个sql
mysqldumpslow -s t -t 10 -g /var/lib/mysql/atguigu-slow.log
另外建议再使用这些命令时结合|more使用，避免爆屏
mysqldumpslow -s t -t 10 -g /var/lib/mysql/atguigu-slow.log | more

4.3、批量数据脚本

创建函数，加入报错：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;
但是重启之后又会消失，
永久方案：
windows下：my.ini[mysqld]加上log_bin_trust_function_creator=1
linux下：/etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators = 1

select now() from dual;

4.4、show profile

1、是什么？（明细条，4层中哪一层占用的时间长）

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于sql调优的测量
默认关闭，并保存最近15次的运行结果

2、怎么用？

1、开启功能： set profiling = on;
show variables like ‘profiling’;

2、运行sql

3、查看结果：
show profiles;
4、诊断sql，
show profile cpu,block io for query 上一步前面问题的query_ID,

show profile cpu,block io for query 3;

还可以查看这些信息：ALL,block io,context switches,cpu,ipc,memory,page faults,source,swaps

5、开发中出现的问题：
5.1、converting HEAP to MyISAM 查询结果太大，内存都不够用了，往磁盘上搬了
5.2、creating tmp table 创建临时表，拷贝数据到临时表，用完再删除
5.3、Copying to tmp table on disk 把内存中临时表复制到磁盘，危险，
5.4、locked

4.5、全局查询日志

永远不要在生产环境开启这个功能
1、配置启动：
在mysql的my.cnf文件中：
#开启
general_log = 1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE
2、编码启用
set golbal general_log = 1;
set global log_output=‘TABLE’;
此后，编写的sql语句，将会记录到mysql库的general_log表中
使用如下命令查看
select * from mysql.general_log;

四、mysql锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。
所得分类：
对数据操作角度：读写锁，读锁（共享锁），写锁（排它锁）
对数据操作的粒度：表锁，行锁、

开销、加锁速度，死锁，粒度。并发性，具体应用场景
表锁：偏读
行锁：偏写
页锁：

表锁：偏向MyISAM存储引擎，开销小，加锁快，无死锁，粒度大，锁冲突概率大，并发度低，

MyISAM引擎
手动增加表锁

lock table 表名字 read(write),表名字2 read(write),其他，
# 查看锁的命令
show open tables;  # 有1的就是上锁了
# 上锁
lock table mylock read,book write;
#解锁
unlock tables;

# 给一个表增加一个读锁之后，也不能在当前会话中执行修改的操作了
如：update mylock set name = 'a2' where id = 1;
这一句执行不成功

当前对话框的读锁没有解锁之前，当前对话框也不能读取其他表的数据。
当前session对自己加了读锁之后，也不能修改自己的内容。
其他session(对话框)，如果想插入数据，会被阻塞等待

增加写锁：

lock table mylock write;
# 当前session 可以读自己这个表，
# 当前session可以改自己这个表，
# 当前session不可以去读别的表。

总结
读锁会阻塞写，但不会阻塞读，写锁会把读和写都阻塞
myisam读写锁调度是写优先。不适合做写为主表的引擎。

分析表锁定

show open tables;
show status like 'table%';
#两个变量：Table_locks_immediate,Table_locks_waited

行锁：偏innoDB引擎，开销大，加锁慢，会死锁，粒度小，锁冲突概率低，并发性高。innoDB和myisam最大的两个区别是：1.支持事务，2，行级锁

1.事务及其ACID属性
2.并发事务处理带来的问题
更新丢失：
最后一个事务的最后一次更新，把前面事务更新的内容覆盖掉。
解决：一个程序员在完成并提交事务之前，不让其他人访问同一个文件，就可以解决。

脏读
事务A,读取了事务B 已修改尚未提交的事务，如果B回滚，A读取的就无效了。不符合一致性。

不可重复读
事务A读取了事务B已提交的修改数据。不符合隔离性。

幻读
事务A读取了事务B提交的新增数据。不符合隔离性。

脏读是事务B,修改了数据
幻读是事务B,新增了数据。

3.隔离级别

查看隔离级别：

show variables like 'transaction_isolation';
#默认第3级：repeatable read,可重复读。避免了脏读和不可重复读。

目前mysql数据库默认的引擎是innoDB，需要手动把事务提交给关了

# 取消自动提交
set autocommit = 0;
innoDB默认是行锁，
所以：更新
update test_innodb_lock set b='4001' where a = 4;

# 查看一下：
select  * froom test_innodb_lock;

无索引行锁升级为表锁

show index from test_innoDB_lock;

varchar必须加单引号，不加是重罪，会索引失效，接着行锁变表锁。

间隙锁：
背景：数据库的数据缺失id=2的行，
如果：session 1:update test_innoDB_lock set b = ‘1212’ where a>1 and a<6;
session 2 : insert into test_innoDB_lock values(2,‘4321’);
当session1 执行之后，如果没有提交，session2就会阻塞。虽然原始数据库中并没有a=2这一个数据，但是也会发生冲突。
当我们使用范围条件而不是相等条件检索数据时候，innoDB会给复合条件的数据加锁，对于在范围而不存在的记录：叫做“间隙（GAP）”,也会对这个“间隙”加锁，间隙锁（next-key锁） 宁可错杀，不可放过。

面试题：如何锁定一行？

begin;
select * from test_innoDB_lock where a = 8 for update;

commit; #结束操作，然后解锁

innoDB存储引擎实现行锁，索然在锁定机制的实现方面带来了性能损耗可能比表锁更高，但在整体并发处理能力远远优于MyISAM的表锁，当系统并发量高时，innoDB就更多优势了
innoDB使用不当，也会让整体性能变差。
查看：

shatus like 'innodb_row_lock%';

优化建议：
尽可能让所有数据检索都通过索引完成。避免无索引行锁升级为表锁。
合理设计索引，尽量缩小锁的范围
尽可能较少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量的时间长度
尽可能低级别事务隔离

页锁：
开销和加锁时间介于表锁和行锁之间；会出现死锁，锁定粒度在表锁行锁之间，并发度一般。

五、主从复制

1.复制的基本原理

slave会从master读取binlog来进行数据同步
三步骤和原理图

2.复制的基本原则

每个slave只有一个master
每个slave只能有一个唯一的服务器ID
每个master可以有多个salve

3.复制的最大问题

延时

4.一主一从常见配置

mysql版本一致且后台以服务运行
主从都配置在【mysqld】节点下，都是小写
主机修改my.ini配置文件
从机修改my.cnf配置文件
因修改过配置文件，请主机和从机都重启后台mysql服务
主机+从机都关闭防火墙
在windows主机上建立账户并授权slave
在linux从机上配置需要复制的主机
主机新建库，新建表，insert记录，从机复制
如何停止从机服务复制功能