常用命令
service mysqld start
删除用户
drop user'your-user'@'your-host';
报错：ERROR 1396 (HY000): Operation DROP USER failed for 'li4'@'%'
flush privileges; 刷新权限试试
修改密码
update mysql.user set authentication_string=password('111111') where user='zhang3';
**flush privileges;** #所有通过user表的修改，必须用该命令才能生效。
查看用户表
select host,user,authentication_string,select_priv,insert_priv,drop_priv from mysql.user;
查看字符集
select schema_name,default_character_set_name from information_schema.schemata;
mysql8.0的默认密码验证不再是password
所以
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'oceanswu';
mysql 高级第一天：
1. 简介 -- 了解
2. mysql 高手怎么练成！
3. mysql 安装 linux
第一种：源码解压！
第二种: yum 安装！
第三种: rpm 安装！
第四种：docker docker pull mysql docker run -it ...
依次执行：
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
启动服务：
systemctl start/stop/restart/status mysqld;
初始化：
mysqld --initialize --user=mysql
mysql 的核心配置文件
/etc/my.cnf;
/var/log/mysqld.log;
4. mysql 的字符集：
默认配置：latin1
改成utf8格式：
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
5. mysql 的用户与权限：
a. create user zhang3 identified by '123123';
使用root 用户 创建用户同时赋予权限！
b. grant all privileges on *.* to joe@'%' identified by '123'; flush privileges;
c. REVOKE select,insert,update,delete ON *.* FROM joe@'%';
6. 利用远程工具访问mysql 数据！
grant all privileges on *.* to root@'%' identified by 'root';
7. 有关于mysql 的配置 sql_mode
修改sql_mode = 'ONLY_FULL_GROUP_BY';
mysql 5.7 的sql_mode = 有 ONLY_FULL_GROUP_BY 说明：在查询的时候，使用group by 要遵循一定的规则！
a. 查询项 必须是分组字段其中之一！
b. 查询项 可以有组函数的出现！
综上所述：
使用group by 进行分组的时候，查询项目，要么是组函数，要么是分组的字段！
改 :my.cnf 配置文件！
8. mysql 的逻辑架构
连接层：
服务层：
引擎层：
存储层：
开启查询缓存：
vim /etc/my.cnf;
query_cache_type=1
每一条sql 语句执行的时候：至少用到一个索引！ 主键！
9. 数据结构：
线性结构：
顺序存储：
链式存储：
集合能够体现！
非线性结构：
多维数组：
时间复杂度：
最好hash！次之平衡树！
程序注重的时间复杂度！
10. 索引块！
底层应该使用树来存储！
B树：
即叶子节点和非叶子节点都存放数据。
面试题：
为啥使用的B+Tree？
数据都存储在叶子节点！
MySQL的InnoDB引擎的存储方式也是B+树机制。
11. 存储引擎：
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
12. 7种join！
重点：
1. mysql 安装！
2. 授权
3. 核心配置文件my.cnf
4. group by 的用法！
5. hash 效率最高 平衡树次之！
mysql innodb 默认存储引擎 采用的B+Tree！
mysql 高级第二天：
回顾：
mysql 的逻辑结构：
连接层：
服务层：
引擎层：
存储层：
select * from stu where id = 1;
MySQL索引底层结构为什么是B+tree?
BTree : 数据是存储在叶子节点与非叶子节点中！
B+Tree : 将所有的数据都存在在了叶子节点！
mysql5.5 以后底层使用B+tree
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
时间复杂度：
hash 次之平衡树！
-
7 中join
mysql 不支持 full outer 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; -
索引优化：
索引都是存储在磁盘上的！
聚簇索引：通常指主键 并且推荐主键使用int 类型自增！ 不建议使用varchar类型！mysql 的索引分类： 单值 复合 唯一 主键 select id ,name from stu where id =? and name = ? order by id; 语法： name varchar(20); 张三123安慰而啊啊的去，张三张阿嘎啊123安慰而啊啊的去 create index idx_name on tableName(name length(10)); 这个name 可以小于当前字段的长度;
drop index idx_name on customer;
SHOW INDEX FROM table_name;
*****:
创建索引：
1)主键自动建立唯一索引
2)频繁作为查询条件的字段应该创建索引
3)查询中与其它表关联的字段，外键关系建立索引
4)单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)
5)查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6)查询中统计或者分组字段不建议创建索引： 1)表记录太少 2)经常增删改的表 3)where条件里用不到的字段不创建索引 在实际的业务中，根据需要适当的创建索引！ -
索引优化分析_explain
使用方法：
Explain + sql会产生笛卡尔积！ explain select * from t_emp a ,t_dept b where a.deptid = b.id; 不会产生笛卡尔积！ explain select * from t_emp a inner join t_dept b on a.deptid = b.id; 但是：mysql 这个优化器 自动优化了！
上午总结：
1. 七种join 链接方式：需要弄懂！
2. 多表关联查询：
# 步骤1：先找出相关的表！t_dept，t_emp
# 步骤2：找关联方式：LEFT JOIN ,RIGHT JOIN, INNER JOIN , UNION , 自关联 ON 关联条件！a.deptId = b.id , a.id = b.CEO
# 步骤3：是否有筛选条件
# 步骤4：调试过程,将这个sql 语句 写对！
必会！
SELECT e1.name,e2.name FROM t_emp e1
LEFT JOIN t_dept d ON e1.deptId = d.id
LEFT JOIN t_emp e2 ON d.ceo = e2.id;
3. 如何创建索引,删除
单一，复合
唯一，主键
4. explain 使用
explain + sql！
*** mysql 这个优化器 自动优化了！
下午：
explain + sql 具体描述！
-
数据准备：
explain 的各个字段表示的意思：
id: 表示查询的顺序，这个id 出现的个数越少越好！
type：
system>const>eq_ref>ref>range>index>ALL
开发中最优选择：ref，rangeindex: 表示遍历了整个索引树！ All：遍历整个磁盘的数据！ 建立索引的情况： 1)主键自动建立唯一索引 2)频繁作为查询条件的字段应该创建索引 3)查询中与其它表关联的字段，外键关系建立索引 4)单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引) 5)查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度 6)查询中统计或者分组字段 key_len： 使用到索引的长度，通常这个值越大越好！ rows：列显示MySQL认为它执行查询时必须检查的行数。值越小越好 Extra； Using filesort： 如果额外的列中出现了这个标识 ， 则表示排序没有使用到索引！ 解决方案：给排序字段添加索引 create index idx_name on tableName(); Using temporary: 表示分组没有使用到索引！
Using index：表示使用了覆盖索引 ：覆盖索引【后面的检索条件，正好在查询项中也存在！】
using join buffer ：表示关联字段没有使用索引！ -
模拟数据优化：
mysql 高级第三天：
回顾：
1. 7 种join 的链接写法！
扩展掌门人的练习！
总结：写多表关联的步骤！2. 索引的类型： 单一索引： 主键索引： 符合索引： 唯一索引： 3. explain + sql id: 判断表的读取顺序！ type： system>const>eq_ref>ref>range>index>ALL key_len： 使用到索引的长度，通常这个值越大越好！ 公式： rows：列显示MySQL认为它执行查询时必须检查的行数。值越小越好 Extra； Using filesort： 如果额外的列中出现了这个标识 ， 则表示排序没有使用到索引！ 解决方案：给排序字段添加索引 create index idx_name on tableName(); Using temporary: 表示分组没有使用到索引！ Using index：表示使用了覆盖索引 ：覆盖索引【后面的检索条件，正好在查询项中也存在！】 using join buffer ：表示关联字段没有使用索引！ 4. 函数，存储过程！ 最好能记住！ java ： 有返回值的方法！ 函数定义:CREATE [OR REPLACE] FUNCTION <过程名>[(参数列表)] RETURN 数据类型 IS [局部变量声明] BEGIN 可执行语句 【普通的sql】 EXCEPTION 异常处理语句 END [<过程名>]; java ：没用返回值的方法！ 存储过程定义:CREATE [OR REPLACE] PROCEDURE <过程名>[(参数列表)] IS [局部变量声明] BEGIN 可执行语句 EXCEPTION 异常处理语句 END [<过程名>];重点：
1. 删除索引的存储过程！{了解}
emp： 50万
dept: 1万删除：drop index idx_name on tname; # 功能： public void del(String table_schema ，String table_name){ List<String> sList = SELECT index_name FROM information_schema.STATISTICS WHERE table_name=table_name AND table_schema=table_schema; for(int i=0;i<sList.size();i++){ DROP INDEX sList.get(i) ON emp; } } 2. 索引使用：必须记住！ a. 全值匹配我最爱 CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME); EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'; b. 最佳左前缀法则 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd'; # 跳过了deptId 导致后面的name 不能使用索引！ 或者 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd'; # 直接跳过了age ,后面的都不能使用索引！ # 创建的索引列都存在！ mysql 的优化器会自动给你排序！ EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid=4 AND emp.age=30 AND emp.name = 'abcd'; EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid=4 AND emp.name = 'abcd' AND emp.age=30; EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name = 'abcd' AND deptid=4 AND emp.age=30; c. 计算、函数导致索引失效 d. 范围条件右边的列索引失效 SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ; SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abc' AND emp.deptId>20 ; 如果有范围条件查询的话，那么我们将其放在后面：建立对应的复合索引即可! e. 不等于(!= 或者<>)索引失效 f. is not null无法使用索引，is null可使用索引 g. like以通配符%开头索引失效 h. 类型转换导致索引失效 3. 关联查询优化 left Join EXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card; 优化应该在on 条件后面！ 情况一：在class 上添加索引 create index idx_card on class(card); 情况二：在book 上添加索引 create index idx_card on book(card); 综上所述： 建立索引的时候，应该在从表{被驱动表}上建立！
inner Join
EXPLAIN SELECT SQL_NO_CACHE * FROM class INNER JOIN book ON class.card=book.card;
会自动选择驱动表！mysql 没有full join ！ 4. 子查询优化： 优化方案： 看where 筛选条件 关联条件 on
所有的条件：
id : 主键索引
ceo : dept.ceo 对应的掌门人
deptid = 部门Id 忽略！
优化方案： 优化ceo！子查询： explain SELECT a.name ,(SELECT c.name FROM t_emp c WHERE c.id=b.ceo) AS ceoname FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id ; left Join explain SELECT a.name,c.name AS 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 ; 临时表： explain SELECT ab.name,c.name AS 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 ; 能使用left join 就不要使用子查询！ 能够使用not in 的 就尽量替换成 LEFT JOIN xxx ON xx WHERE xx IS NULL NOT IN -->LEFT JOIN xxx ON xx WHERE xx IS NULL
-
排序、分组优化
order bya. 无过滤 不索引！ b. 顺序错，必排序！ c. 方向反 必排序 当在查询数据的时候，如果将排序的字段用上了索引，那么效率必然会高！ 总结： where on order by 索引的选择： 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量， 如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。
双路排序和单路排序 了解：
双路排序：两次io
单路排序：一次io 但是需要调整sort_buffer_size 的容量，避免发生多次io！
提高order by 的排序速度：
增大sort_buffer_size参数的设置 1M-8M
增大max_length_for_sort_data参数的设置 1024-8192字节
减少select 后面的查询的字段。 禁止使用select *修改配置文件！GROUP BY关键字优化
group by 先排序再分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置
where高于having,能写在where限定的条件就不要写在having中了
group by没有过滤条件，也可以用上索引。Order By 必须有过滤条件才能使用上索引。 -
覆盖索引：
使用explain + sql ，在额外的选项中出现了 Using index ，则就是使用到了覆盖索引！select * from emp where id = 1; 表示只查询一次就可以找到数据！ select id,name from emp where name = 'zhangsan'; name 不是索引！ 表示查询两次！ 上述这个过程叫回表！ 【了解】 引出覆盖索引！ select sql_no_cache id,age,deptid from emp where name like '%abc'; # 只需要查询一次！ 结论： 查询的时候 尽量 使用覆盖索引！{查询字段，尽量是索引字段} -
慢查询日志 {了解}
a. sql 语句执行的过程中超出了预定时间！
b. 修改my.cnf
slow_query_log =1
slow_query_log_file=/var/lib/mysql/localhost-slow.log
long_query_time=3
log_output=FILEc. 直接使用mysqldumpslow 进行分析： mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log
总结：
上午
a. 针对是where 条件后面的索引优化！哪些能用索引，哪些不能用索引！b. 关联查询优化 on 条件 ，在被驱动表上建立索引！ c. 能用left join 就不使用子查询！ d. order by group by
下午：
1. 排序、分组优化
a. 无过滤 不索引！
b. 顺序错，必排序！c. 方向反 必排序 2. 覆盖索引 如何使用！ 尽量把查询项，与帅选项做成索引！ select id,name,sex,age,addr,stuNo,email,createTimne,updateTime from stu where name = 'zs'; 3. 慢查询 - 了解！
mysql 高级第四天：
回顾：
1. 索引的使用
a. 全值匹配我最爱
b. 最佳左前缀原则
c. 计算、函数导致索引失效
d. 范围条件右边的列索引失效
e. 不等于(!= 或者<>)索引失效
f. is not null无法使用索引，is null可使用索引
g. like以通配符%开头索引失效
h. 类型转换导致索引失效
2. 关联优化： left join 在从表{被驱动表}建立索引！ inner join mysql 优化器会自动识别谁是被驱动表！ 3. 排序、分组优化
总结：
my.cnf 的配置文件中 mysql5.7 使用的是单路
提高order by 的排序速度：
增大sort_buffer_size参数的设置 1M-8M
增大max_length_for_sort_data参数的设置 1024-8192字节
减少select 后面的查询的字段。 禁止使用select *
4. 如果有子查询和left Join
优先选择 left Join
NOT IN --> LEFT JOIN xxx ON xx WHERE xx IS NULL5. 索引覆盖 见图： 6. 如何优化sql 语句： 步骤： 看where 后面的条件; 尽量添加索引 看on 条件 在被驱动表建立索引，尽量少使用子查询尽量使用左关联 group by order by 尽量使用索引！ 细节问题：如果在where出现范围的话，与排序字段有冲突的情况下！ 参考索引使用进行修改！ 7. 慢查询了解重点：
1. 完成mysql 高级讲解2. 讲mycat
1. 视图；
a. 有sql 语句封装的虚拟表
b. 提高复用性
c. CREATE [OR REPLACE] VIEW <view_name>
AS
<SELECT 语句>;
OR REPLACE --表示替换以有的视图
2. 主从复制；
基本原理:
io线程从主服务器读取二进制日志文件，sql 线程读取中继日志文件，并执行。保证主服务器与从服务器的数据一致性！
步骤：
1. 需要一个主机，一个从机！
我拷贝的！
修改ip 地址！
LINUX 有自己的uuid 唯一标识！ 如果我们是拷贝的！那么这个uuid 是不会发生变化的！
一台：166 从
一台：168 主2. 在主机添加配置数据 3. 从机添加配置数据 4. 重启服务器并关闭防火墙 5. 在主机上建立帐户并授权slave并记录当前文件名并且记录位置！ 6. 在Linux从机上配置需要复制的主机 CHANGE MASTER TO MASTER_HOST='主机ip地址', MASTER_USER='X',MASTER_PASSWORD='X', MASTER_LOG_FILE='mysqlbin.具体数字',MASTER_LOG_POS=具体值; 修改之后： CHANGE MASTER TO MASTER_HOST='192.168.200.168', MASTER_USER='slave',MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysqlbin.000001',MASTER_LOG_POS=590;
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
these UUIDs must be different for replication to work.
如果出现这个错误，需要改一下uuid 值即可!因为修改了uuid，所以我们mysql 主机的文件名称可能发生变化！ 注意文件名称：课件有坑，大家下载最新的课件！ start slave; 开启主从复制！ show slave status \G; 主要看IO,SQL 线程是否是yes！ stop slave; 停止主从复制！
7. 测试
3. mycat：
数据库中间件
mycat：读写分离，数据分片，整合多个数据源
原理：拦截sql 语句，进行分析！
4. mycat 安装：
168：主 mycat
主要配置文件：
①schema.xml：定义逻辑库，表、分片节点等内容
②rule.xml：定义分片规则
③server.xml：定义用户以及系统相关变量，如端口等
配置：
server.xml：
schema.xml启动mycat： ./mycat console : 推荐这种方式启动 登录mycat！ 管理者：mysql -umycat -p123456 -P 9066 -h 192.168.200.168 服务器：mysql -umycat -p123456 -P 8066 -h 192.168.200.168 <property name="serverPort">8066</property> <property name="managerPort">9066</property> 5. mycat 进行读写分离 a. 搭建好主从！ b. 修改配置文件！ 6. mycat 垂直拆分： 垂直 分库 ，水平 分表！ a. 先停止主从复制，停止mycat！ b. 修改配置文件！ schema.xml c. 在两个节点上创建两个数据库 dn1 ：168 dn2 : 166 CREATE DATABASE orders; 在dn1 上创建： #订单表 rows:600万 CREATE TABLE orders( id INT AUTO_INCREMENT, order_type INT, customer_id INT, amount DECIMAL(10,2), PRIMARY KEY(id) ); #订单详细表 rows:600万 CREATE TABLE orders_detail( id INT AUTO_INCREMENT, detail VARCHAR(2000), order_id INT, PRIMARY KEY(id) ); #订单状态字典表 rows:20条 CREATE TABLE dict_order_type( id INT AUTO_INCREMENT, order_type VARCHAR(200), PRIMARY KEY(id) );
在dn2 上创建：
CREATE TABLE customer(
id INT AUTO_INCREMENT,
NAME VARCHAR(200),
PRIMARY KEY(id)
);
d. 启动mycat！
dn1,dn2 都在mycat中配置好！7. 水平分表： 分表：按照某个字段的某种规则来分散到多个库之中！ 讨论，研究一下分表的规则！ 修改配置文件： ①schema.xml：定义逻辑库，表、分片节点等内容 ②rule.xml：定义分片规则 保证两个节点中都有表{orders} 重启：mycat 使用mycat 进行链接 在mycat 的客户端中进行插入数据！ INSERT INTO orders(id,order_type,customer_id,amount) VALUES(1,101,100,100100); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400); INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020); 总结： 垂直分库： 1. 在配置文件中进行配置： 2. 在两个节点上创建两个数据库 3. 相关的表在一个数据库中。没用相关的表在另一个库中！ 水平分表： 1. 找到分表的规则！ 客户Id 2. 在每个节点上 创建对应的表 3. 修改配置文件 ①schema.xml ②rule.xml
E-R 表操作：
可以使用join 关联！
1. 配置schema.xml
2. 每个节点上都应该有对应的表3. 重启mycat，插入数据，测试！ 全局表： 字典表： 有可能跟客户表有关系，也有可能跟订单表有关系! 称之为全局表！ 总结： 1. 在每个节点上都应该有这个表的存储！ 2. 每个节点中的数据都应该保持一致！
mycat 要想做关联查询： 要么E-R 表操作！要么就做全局表！ 通过全局表+基于 E-R 关系的分片策略
全局序列：
建议使用数据库！
总结： 1. mysql 视图； 2. mysql 主从复制 3. 会使用mycat 做读写分离 4. mycat 垂直分库，水平分表： 5. E-R表，全局表 6. 使用数据方式生成全局序列！
-
评论区