常用命令
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. 使用数据方式生成全局序列!
-
评论区