mysql~高级学习~初

这是一篇比较长的关于Mysql高级的一部分相关知识点，里边有很多小细节，需要注意的点，希望对大家有所帮助，有耐心的小伙伴可以借鉴看一看，欢迎指出不足，多多评论！！！

小细节
行锁会出现死锁
csv 简单数据传输
一直在用左外连接，很少用又外连接
BTree平衡树
mysql的底层是B+Tree
=@@字符串 sql语句这种格式是系统变量

key_len
range 范围查询 命中效率越高 越好查询越快

rows 物理扫描函数 越小越快
char 可变
varchar不可变

quit退出mysql

mysql -uroot -p123456 登录mysql

区别为什么不加分号
mysql> create database mydb;这是sql语句
Query OK, 1 row affected (0.00 sec)

mysql> use mydb 这是 命令
Database changed
mysql>
=Mysql简介=====
MySQL是一个关系型数据库管理系统,支持大型的数据库。可以处理拥有上千万条记录的大型数据库。使用标准的SQL数据语言形式。
=Mysql_Linux版本的安装=====
=5.7=
①检查工作【
*检查当前系统是否安装过mysql
rpm -qa|grep mariadb
如果存在如下：
mariadb- libs-5.5.56-2.el7.x86_64
请先执行卸载命令：rpm -e --nodeps mariadb-libs
*检查当前mysql依赖环境
rpm -qa|grep libaio 存在如下

rpm -qa|grep net-tools 存在如下

*检查/tmp文件夹权限
LL / 查看根目录 看见 tmp 就行
】
②安装【
在mysql的安装文件目录下执行：（必须按照顺序执行）
rpm -ivh mysql-community-common-5.7.16-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.16-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.16-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.16-1.el7.x86_64.rpm

如在检查工作时，没有检查mysql依赖环境在安装mysql-community-server会报错
】
③查看MySQL安装版本【
执行 mysqladmin --version命令
出现： mysqladmin Ver 8.42 Distrib 5.7.16, for Linux on x86_64
】
④mysql服务的初始化【
为了保证数据库目录为与文件的所有者为 mysql 登陆用户，如果你是以 root 身份运行 mysql 服务，需要执行下面的命令初始化
mysqld --initialize --user=mysql

另外 --initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将该密码标记为过期，登陆后你需要设置一个新的密码

查看密码：cat /var/log/mysqld.log

root@localhost: 后面就是初始化的密码
】
⑤mysql服务的启+停【
启动：systemctl start mysqld.service
】
⑥
⑦自启动mysql服务【
是不是自启 如果不是 得设置
[root@study ~]# systemctl list-unit-files |grep mysqld 查看自启软件
mysqld.service enabled 代表自启
&如不是enabled可以运行如下命令设置自启动
启动：systemctl enable mysqld.sercice
关闭：systemctl stop mysqld.service
】
⑧修改字符集问题【
navicat 链接不到虚拟机（ubuntu）的mysql 怎么办
修改mysql字符集
vim /etc/my.cnf 进入配置文件
character_set_Server=utf8 最后一行
systemctl restart mysqld 重启mysql
systemctl status mysqld 查看
alter database mydb character set ‘utf8’; 修改库字符集
alter table mytbl convert to character set ‘utf8’; 修改表字符集
】

=Mysql用户权限与管理=====

use mysql 修改远程访问
select * from user\G; 列出表
select host,user,authentication_string from user; 查看远程访问用户 （localhost 代表自己访问）
create user ljw01 identified by ‘123456’; 创建一个远程用户访问 （%代表所有的远程访问）
必须是在某一数据库下 重点 必须关一遍数据库 然后在按地底下步骤 就可以连接
注意：修改完用户的密码后 一定要执行这一句 flush privileges 才会生效

创建一个root用户并给它授权
grant all privileges on . to root@’%’ identified by ‘123456’;

关闭防火墙
systemctl stop firewalld.service 查看防火墙
firewall-cmd --state 查看状态 not running 表示没有运行
systemctl disable firewalld.service 关闭防火墙

=============================================

=Mysql一些杂项配置（了解）=====
1.show variables like ‘sql_mode’; 查看 常用值

ONLY_FULL_GROUP_BY：
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

NO_AUTO_VALUE_ON_ZERO：
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

STRICT_TRANS_TABLES：
在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制

NO_ZERO_IN_DATE：
在严格模式下，不允许日期和月份为零

NO_ZERO_DATE：
设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO：
在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL

NO_AUTO_CREATE_USER：
禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION：
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

PIPES_AS_CONCAT：
将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

ANSI_QUOTES：
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符
ORACLE：
设置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.
=Mysql逻辑架构介绍（重）=====
sal执行流程 ----重点

①先和连接池沟通连接
②做缓存，缓冲查询
③SQL接口分析sql
④解析器解析复杂sql
⑤优化器，不影响结果进行优化，生成执行计划
⑥存入引擎按计划分类型执行
⑦存入缓存，并返回结果
==============利用show profile 查看sql的执行周期=
①先进入配置文件 vim /etc/my.cnf
②添加这一句话 query_cache_type=1
③然后重启 systemctl restart mysqld
④查看启动成功没有 systemctl status mysqld
⑤查看是否开启 show variables like ‘%profiling%’;
±-----------------------±------+
| Variable_name | Value |
±-----------------------±------+
| have_profiling | YES |
| profiling | OFF | 代表未开
| profiling_history_size | 15 |
±-----------------------±------+
⑥开启密令 set profiling=1;
⑦可以使用 查表了
⑧显示最近几次查询 show Profiles
⑨查看程序的执行步骤 （查更详细 但是得带上id） show profile cpu,block io for query id(?);
注意：命中缓冲 sql 得一样
mysql执行顺序
手写 和 机读 是不一样的
Distinct 去重
=Mysql存储引擎=
1.查看命令：
看你的mysql现在已提供什么存储引擎: mysql> show engines;
看你的mysql当前默认的存储引擎: mysql> show variables like ‘%storage_engine%’;
2.
对比项 MyIsAM InnoDB(默认使用)
外键 不支持 支持
事物 不支持 支持
表 行 锁 表锁，即使操作一条记录，整个表都会被锁住，不适合高并发 行锁 操作是只会锁定某一行，不会影响其它行，适合高并发
缓存 只缓存索引，不缓存真是数据 缓存索引，而且缓存真是数据，对内存要求比较高，内存大小对性能有直接的影响
关注点 节省资源，消耗少，简单业务 并发写，事物，更大资源
默认安装 Y Y
默认使用 N Y
自带系统表使用 Y N
各个引擎简介（了解）
1、InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

2、MyISAM存储引擎
MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

3、Archive引擎
Archive档案存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。
Archive表适合日志和数据采集类应用。
根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。

4、Blackhole引擎
Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

5、CSV引擎 以逗号的形式展现
CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
CSV引擎可以作为一种数据交换的机制，非常有用。
CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。

6、Memory引擎
如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。

7、Federated 联合引擎
Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的

=索引优化分析=====
1.SQL JOINS 一共7种 全连接mysql 没有 orcal有
注意：面试问道 有哪些连接的时候，顺带加一句话，我们一般用左外连接

内连接（INNER JOIN）
外连接（OUTER JOIN）【
左外链接（LEFT JOIN）
右外连接（RIGHT JOIN）
全连接（FULL JOIN）包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。不符合条件的，以空值代替
】
当查询上下结果都一样时 用 UNION ALL 快
==建表=
CREATE TABLE t_dept (
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
address VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE t_emp (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(20) DEFAULT NULL,
age INT(3) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (id),
KEY idx_dept_id (deptId)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

举例：
1 所有有门派的人员信息
（ A、B两表共有）
select * from t_emp a inner join t_dept
on a.deptid=b.id;
2.列出所有用户，并显示其机构信息
（A的全集）
select * from t_emp a left join t_dept b
on a.deptid=b.id;
3 列出所有门派
（B的全集）
select * from t_dept b

4 所有不入门派的人员
（A的独有）
SELECT * FROM t_emp a LEFT JOIN t_dept b
ON a.deptId=b.id WHERE b.id IS NULL;
5 所有没人入的门派
（B的独有）
SELECT * FROM t_emp a RIGHT JOIN t_dept b
ON a.deptId = b.id WHERE a.deptId IS NULL;
6 列出所有人员和机构的对照关系
(AB全有)
MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join

SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id

7 列出所有没入派的人员和没人入的门派
（A的独有+B的独有）
SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId = b.id WHERE b.id IS NULL
UNION
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id WHERE a.deptId IS NULL;

增加掌门人字段
ALTER TABLE t_dept
add CEO INT(11) ;

update t_dept set CEO=2 where id=1;
update t_dept set CEO=4 where id=2;
update t_dept set CEO=6 where id=3;
update t_dept set CEO=8 where id=4;
update t_dept set CEO=9 where id=5;

8.求各个门派对应的掌门人名称:
SELECT * FROM t_emp a INNER JOIN t_dept b
ON b.CEO=a.id;
9.求所有当上掌门人的平均年龄
SELECT AVG(a.age) FROM t_emp a INNER JOIN t_dept b
ON b.CEO=a.id;
10.求所有人物对应的掌门名称:
SELECT c.name,ab.name ceoname FROM t_emp c LEFT JOIN
(SELECT b.id,a.name FROM t_emp a INNER JOIN t_dept b
ON b.CEO=a.id)ab
ON c.deptId=ab.id;

SELECT ab.name,c.name ceoname FROM
(SELECT a.name,b.ceo FROM t_emp a LEFT JOIN t_dept b
ON a.deptId=b.id)ab
LEFT JOIN t_emp c
ON ab.ceo=c.id;

SELECT a.name,c.name ceoname FROM t_emp a LEFT JOIN t_dept b
ON a.deptId=b.id
LEFT JOIN t_emp c
ON b.CEO=c.id;

SELECT a.name,(SELECT c.name FROM t_emp c WHERE c.id=b.CEO) ceoname
FROM t_emp a LEFT JOIN t_dept b
ON a.deptid=b.id;

索引的简介=
1.索引是什么？
①索引（Index）是帮助MySQL高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构。
②可以简单理解为“排好序的快速查找数据结构”。
③一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上
2.优缺点？
优：
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
缺：
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，
都会调整因为更新所带来的键值变化后的索引信息

3.mysql的索引结构？

①BTree索引：无限的内存下，使用它比较好
数据
向下的指针
指向数据指针

②B+Tree索引 ：有限内存下，使用它
数据
向下的指针
③B树和B+树的区别
B+树的查询效率更加稳定
B+树的磁盘读写代价更低
④聚簇和非聚簇索引
是一种数据存储方式。
‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。
注意点：
对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。
为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。
4.mysql索引的分类
查看索引：show index from 表名
单独增加单值索引：
CREATE INDEX idx_customer_name ON customer(customer_name);

删除索引：
DROP INDEX idx_customer_name on customer;

单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

删除索引：
DROP INDEX idx_customer_no on customer ;
使用ALTER 命令创建索引
有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
什么情况下创建索引

什么情况下不创建索引

性能分析==
1.查看执行计划：使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是
如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
2.能干啥？
①哪些索引被实际使用
②每张表有多少行被物理查询
③表的读取顺序
④哪些索引可以使用
⑤数据读取操作的操作类型
⑥表之间的引用

3.使用方式
Explain + SQL语句
4.各字段解释
id【
相同 ： 自上而下
不相同：id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
相同不同 同时存在：不相同 先从大到小，相同 从上到下
关注点：id号每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。
】
type【
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref
】
possible_keys【
显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
】
key_len【
数值越大越好
】
rows【
ows列显示MySQL认为它执行查询时必须检查的行数。 越小越好
】

Extar重【
group by 包含order by
Using filesort 文件排序
Using temporary
using join buffer
】
impossible where【
表达式错误
】