MySQL4

MySQL的触发器

MySQL的索引

MySQL的事务

MySQL的锁机制

MySQL的日志

MySQL的优化

-- Mysql 的触发器
-- 操作-创建触发器
-- 数据准备
create database if not exists mydb10_trigger;
use mydb10_trigger;

-- 用户类
create table user(
	uid int primary key,
	username varchar(50) not null,
	password varchar(50) not null 
	);
	
-- 用户信息操作日志表
create table user_logs(
id int primary key auto_increment,
time timestamp,
log_text varchar(255)
);

-- 需求1：当user表添加一行数据，则会自动在user_loh添加日志记录
-- 定义触发器
create trigger trigger_test1 after insert 
on user for each row 
insert into user_logs values(NULL,now(),'有新用户添加');

-- 在user表添加数据，让触发器自动执行
insert into user values(1,'张三','123456');


-- 需求2：当user表数据被修改时，则会自动在user_log 添加日志记录
 delimiter $$
 create trigger trigger_test2 before update 
 on user for each row
 begin 
	insert into user_logs values(NULL,now(),'有用户信息被修改');
end $$
delimiter ;

drop trigger trigger_test2;
-- 在user表中修改数据，让触发器自动执行
update user set password ='888888' where uid=1;

-- new和old

-- insert类型的触发器
create trigger trigger_test3 after insert 
on user for each row 
insert into user_logs values(NULL,now(),concat('有新用户添加,信息为：',NEW.uid,NEW.username,NEW.password));

-- 在user表添加数据，让触发器自动执行
insert into user values(2,'赵六','123456');


-- update类型的触发器

-- old 

create trigger trigger_test4 after update 
on user for each row 
insert into user_logs values(NULL,now(),concat('有用户信息修改，信息修改之前为：',OLD.uid,OLD.uname,OLD.password));

update user set password='888888'  where uid=2;

-- new 
drop trigger trigger_test5;

create trigger trigger_test5 after update 
on user for each row 
insert into user_logs values(NULL,now(),concat_ws(',','有用户信息修改，信息修改之后为：',NEW.uid,NEW.username,NEW.password));

update user set password='000000'  where uid=1;


-- delete类型触发器
create trigger trigger_test6 after delete
on user for each row 
insert into user_logs values(NULL,now(),concat_ws(',','有新用户被删除,被删除用户信息为：',OLD.uid,OLD.username,OLD.password));

delete from user where uid=1;


-- 查看触发器
show triggers;

-- 删除触发器
drop trigger if exists trigger_test0;


-- ----Mysql的索引

-- 索引的操作-创建索引-单列索引-普通索引
-- 单列索引：一个索引只包含单个列，但一个表中可以有多个限制
-- 普通索引：Mysql中基本索引类型，没什么限制，允许在定义索引的列中插入重复值和空值


create database mydb5;
use mydb5;

-- 方式一：创建表的时候直接指定
create table student(
	sid int primary key,
	card_id varchar(20),
	name varchar(20),
	gender varchar(20),
	age int,
	birth date,
	phone_num varchar(20),
	score double,
	index indwx_name(name)
	);

select * from student where name='张三';

-- 方式2：直接创建索引
create index index_gender on student(gender);

-- 方式3：修改表结构（添加索引）
alter table student add index index_age(age);


-- 1.查看数据库所有索引
-- select * from mysql.innodb_index_stats a where a.database_name ='数据库名' ;
select * from mysql.innodb_index_stats a where a.database_name ='mydb5' ;

-- 2.查看表中所有索引
-- select * from mysql.innodb_index_stats a where a.database_name ='数据库名' and a.table_name like '%表名%';

select * from mysql.innodb_index_stats a where a.database_name ='mydb5' and a.table_name like '%student%';

-- 3.查看表中所有索引
-- show index from table_name;
show index from student;


-- 删除索引
drop index index_gender on student;

alter table student drop index index_age;


-- 索引操作-创建索引-单列索引-唯一索引
-- 唯一索引的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一

-- 方式1-创建表时直接创建

create table student2(
	sid int primary key,
	card_id varchar(20),
	name varchar(20),
	gender varchar(20),
	age int,
	birth date,
	phone_num varchar(20),
	score double,
	unique index_card_id(card_id)
	);

-- 方式2-直接创建

create table student0(
	sid int primary key,
	card_id varchar(20),
	name varchar(20),
	gender varchar(20),
	age int,
	birth date,
	phone_num varchar(20),
	score double
	);

create unique index index_card_id on student0(card_id);

-- 方式3-修改表结构
alter table student2 add unique index_phone_num(phone_num);

-- 删除方式同上


-- 主键索引
-- 创建组件自动创建


-- 索引的操作-创建索引-组合索引
-- create index indexname on table_name(column1(length),column2(length));
create index index_phone_name on student(phone_num,name);

-- 删除索引
drop index index_phone_name on student;

-- 创建索引的基本语法-唯一索引
create unique index index_phone_name on student(phone_num,name);

-- 组合索引对左原则



-- 全文索引
-- mysql5.6以前mylsam

show variables like '%ft%';


use mydb5;
-- 创建表的适合添加全文索引
create table t_article(
	id int primary key auto_increment,
	title varchar(255),
	content varchar(1000),
	writing_date date
	-- fulltext (content)-- 创建全文索引
	);

-- 修改表结构添加全文索引
alter table t_article add fulltext index_content(content)

-- 添加全文索引
create fulltext index index_content on t_article(content);

-- 使用全文索引
select * from t_article where match(content) against('yo');-- 没有结果--因为最小搜索长度是3
select * from t_article where match(cntent) against('you');


-- 索引的操作-空间索引
create table shop_info(
	id int primary key auto_increment comment 'id',
	shop_name varchar(64) not null comment '门店名称',
	gemo_point geometry not null comment '经纬度',
	spatail key geom_index(geom_point)
	);


-- 索引原理-相关算法
-- hash算法
/*
优点：通过字段的值计算hash值，定位数据非常快
缺点：不能进行范围查找，因为散列表中的值是无序的，无法进行大小比较
*/

-- B-Trees 中间向上提取



-- mysql存储引擎

show engines;

-- 查看当前默认引擎
show variables like '%storage_engine%';

create database mydb11_engine;

use mydb11_engine;

create table stu1(id int,name varchar(20));

-- 查看创建表时的命令，里面包含存储引擎的信息
show create table stu1;

-- 创建表，指定存储引擎
create table stu2(id int,name varchar(20)) engine =MyISAM;
 
show create table stu2;

-- 通过alter命令来修改表的存储索引
alter table stu1 engine =MyISAM;
alter table stu2 engine =INNODB;


-- 事务
create database if not exists mydb12_transcation;
use mydb12_transcation;
-- 创建账户表
create table account(
	id int primary key,-- 账号id
	name varchar(20),-- 账户名
	money double -- 金额
	);
	
	drop table account;
-- 插入数据
insert into account values(1,'zhangsan',1000);
insert into account values(2,'list',1000);

-- 设置MYSQL的事务为手动提交（关闭自动提交）
select @@autocommit;-- 1自动提交
set autocommit =0;

-- 模拟账户转账
-- 开启事务
-- begin;
update account set money=money -200 where name='zhangsan';
update account set money=money +200 where name='list';

-- 提交事务
commit;

-- 回滚事务
rollback;

select * from account;




-- Mysql的锁机制

drop database if exists mydb14_lock;
create database mydb14_lock;

use mydb14_lock;
drop table ta_book;
create table tb_book (
	id int(11) auto_increment,
	name varchar(50) default null,
	publish_time date default null,
	status char(1) default null,
	primary key(id)
	) engine=myisam default charset=utf8;
	
insert into tb_book (id,name,publish_time,status) values(null,'java编程思想','2088-08-01','1');
insert into tb_book (id,name,publish_time,status) values(null,'solr编程思想','2088-08-01','0');


create table tb_user(
	id int(11) auto_increment,
	name varchar(50) default null,
	primary key (id)
	) engine =myisam default charset=utf8;
	
insert into tb_user(id,name) values(null,'令狐冲');
insert into tb_user(id,name) values(null,'田伯光');	
	
	
-- 	行锁
drop table if exists test_innodb_lock;
create table test_innodb_lock(
	id int(11),
	name varchar(16),
	sex varchar(1)
	) engine =innodb;
	
insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');
	
	

-- --日志

-- 错误日志
show variables like 'log_error%';

-- 二进制日志
-- 查看mysql是否开启了binlog日志
show variables like 'log_bin';

-- 查看binlog日志的格式
show variables like 'binlog_format';

-- 查看所有日志
show binlog events;

-- 查看最新的日志
show master status;

-- 查询指定的binlog日志
show binlog events in 'binlog.000040';

-- 从指定位置开始，查看指定的binlog日志
show binlog events in 'binlog.000040' from 156;

-- 清空所有日志
reset master;

-- 查询日志
-- 查看MySQL是否开启了查询日志
show variables like 'general_log';

-- 开启查询日志
 set global general_log=1;


-- 慢查询日志

-- 查看慢日志查询是否开启
show variables like 'slow_query_log%';

-- 开启慢查询日志
set global slow_query_log =1;
 
-- 查看慢查询的超时时间
show variables like 'long_query_time%';

select sleep(10);




-- mysql的优化
insert into account values(3,'wangwu',1000);
-- 查看sql执行频率
show session status like 'Com_______';

-- 查看全局（自从上次MySQL服务器启动至今）执行类型的统计信息
show global status like 'Com_______';

-- 查看针对Inndb引擎的统计信息
show status like 'Innodb_rows_%';


-- 定位低效率执行sql
-- 查看慢日志配置信息
show variables like '%slow_query_log%';

-- 开启慢日志查询
set global slow_query_log =1;

-- 查看慢日志记录sql的最低阈值时间，默认如果sql的执行时间>=10秒，则算慢查询，则会将该操作记录到慢日志中去
show variables like '%long_query_time%';
select sleep(12);

-- 修改慢日志记录SQL的最低阈值时间
set global long_query_time =5;

-- 通过show processlist查看当前客户端连接服务器的线程执行状态
show processlist;


-- ---- --explain执行计划------------------
create database mydb13_optimize;
use mydb13_optimize;

-- 1.查询执行计划
explain select * from user where uid=1;

-- 2.查询执行计划
explain select * from user where uname='张飞';

-- Explain 分析执行计划-Explain之id
-- 1.id 相同表示加载表的顺序是从上到下
explain select * from user u,user_role ur, role r where uid=1;

-- 2.id 不同id值越大，优先级越高，越先被执行
explain select * from role where rid =(select rid from user_role where uid =(select uid from user where uname='张飞'));

-- 3.id 有相同的，也有不同的，同时存在，id相同的可以认为是一组，从上往下顺序执行：在所有的族中，id值越大，优先级越高
explain select * from role r,(select * from user_role ur where ur.uid=(select uid from user where uname='张飞')) t where r.rid=t.rid;


-- explain的select_type
-- simple没有子查询和union
explain select * from user;

-- PRIMARY:主查询，也就是子查询中的最外层查询
explain select * from role r,(select * from user_role ur where ur.uid=(select uid from user where uname='张飞')) t where r.rid=t.rid;

-- subquery:在select和where中包含子查询
explain select * from role where rid=(select rid from user_role where uid=(select uid from user where uname='张飞'));

-- derived:在from中包含子查询，被标记为衍生表，临时表
explain select * from(select * from user limit 2)t;

-- UNION
-- UNION RESULT
explain select * from user where uid=1 union select * from user where uid =3;



-- explain分析执行计划-explain之type

use mydb13_optimize;
 explain select * from user;
 
--  null:不访问任何表，任何索引，直接返回结果
explain select now();
explain select rand();

-- system :查询系统，直接从内存中获取数据，不会从磁盘读取，但是5.7及以上版本不再显示system，直接显示all
explain select * from mysql.tables_priv;

-- const :主键或者唯一索引
explain select * from user where uid =2;
explain select * from user where uname='张飞';

create unique index index_uname on user(uname);-- 创建唯一索引
drop index index_uname on user(uname);-- 删除索引

create index index_uname on user(uname);-- 添加普通索引--不能

-- eq_ref 左表有主键，而且左表的每一行和右表的每一行刚好匹配
create table user2(
id int,
name varchar(20)
);
insert into user2 values(1,'张三'),(2,'李四'),(3,'王五');

create table user2_ex(
id int,
age int 
);
insert into user2_ex values(1,20),(2,21),(3,22);

-- -----------------
explain select * from user2 a,user2_ex b where a.id=b.id;-- ALL

-- 给user2表添加主键索引
alter table user2 add primary key(id);
alter table user2_ex drop primary key(id);

explain select * from user2 a,user2_ex b where a.id=b.id;

-- ref 坐标有普通索引，和右表配置时可能会匹配多行

-- range:范围查询
explain select * from user2 where id>2;

-- index:把索引列的全部数据都扫描
explain select id from user2;-- INDEX
explain select * from user2;-- all

-- 结果值从最好到最坏：system>const>eq_ref>ref>range>index>ALL

-- explain其他字段指标

-- rows 扫描行

explain select * from user where uid=1;-- key_ien=4 int占4
explain select * from user——role where uid=1;-- key_ien=5  uid可为空 int=4 再+1


show index from user;
explain select * from user order by uname;-- using filesort-- 排序
explain select uname,count(*) from user group by uname;-- using temporary-- group by
explain select uid,count(*) from user group by uid;-- using indx -- 索引数



-- 查看当前的MySQL是否支持profile
select @@have_profiling;
-- 如果不支持，则需要设置打开
set profiling =1;


-- ...
show profiles;

show profile for query 20;

show profile cpu from query 20;


-- -------trace分析优化器执行计划---------------
set optimizer_trace ="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_men_size=10000000;

select * from user a,user_role b,role c where a.uid=b.uid and b.rid=c.rid;

select * from information_scheme.optimizer_trace \G;



-- 全值匹配，和字段匹配成功即可，和顺序无关
explain select * from tb_seller where name-'小米科技' and status='1' and address='北京市';
explain select * from tb_seller where  address='北京市' and name-'小米科技' and status='1' ;

-- 最左前缀原则：只要左在，无关顺序


-- 范围查询右边的列，不能用索引
explain select * from tb_seller where name='小米科技'and status>1 and address='北京市';-- NO
-- 不要再索引列上进行运算操作，索引将失效
explain select * from tb_seller where substring(name,3,2)='科技';

-- 字符串不加单引号，造成索引失效
explain select * from tb_seller where name='小米科技' and status=1;


-- 尽量使用覆盖索引，避免select*
-- 需要从原表及磁盘上读取数据
explain select * from tb_seller where name='小米科技'and address='北京市';-- 效率低
explain select name，status,address from tb_seller where name='小米科技'and address='北京市';-- 效率高
-- 只要被or分割的，都没有索引

-- 以%开头的like模糊查询，索引失效
explain select * from tb_seller where name like '科技%';-- 用索引
explain select * from tb_seller where name like '%科技';-- 不用索引
explain select * from tb_seller where name like '%科技%';-- 不用索引

-- 弥补不足，不用*，使用索引列
explain select name from tb_seller where name like '%科技%';



-- 1.如果mysql评估使用索引比全表更慢，则不使用索引
-- 是有数据本身的特点决定的
create index index_address on tb_seller(adress);
explain select name from tb_seller where address= '北京市';-- 数据多没使用索引
explain select name from tb_seller where address= '西安市';-- 少，使用索引

-- 2.is null,is not null 根据数据时有效

-- 3.in走索引，not in 索引失效

-- 4.主键索引not in /in都使用
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
/*等价于：
NAME
name+STATUS
name+status+address
*/
-- 如果一张表有多个单列索引，即使where中都用了这些索引列，则只有一个最优索引生效



-- MySQL优化
-- 1.首先，检查一个全局系统变量'local_infile'的状态，如果得到如下显示Value=OFF,则说明这个不可用
show global variables like 'local_infile';

-- 2.修改local_infile值为on,开启local_infile
set global local_infile=1;

-- 3.加载数据
-- 当通过load向表里添加数据时尽量保证文件
/*sql1.log ---->主键有序 快 主键无序
*/
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated bu '\n';
 
truncate table tb_user;


-- 优化insert语句


-- 优化order by 
-- 尽量排序方式相同，尽量和组合索引字段顺序相同select后面少用* 用索引列


-- 优化子查询
--  多表查询 效率大于子查询

-- 优化limit
-- 1000000
select * from tb_user limiter 0,10;

explain select * from tb_user limiter 900000,10;-- 0.684

explain select * from tb_user a,(select id from tb_user order by id limiter 900000,10);-- 0.486

explain select * from tb_user where id>900000 limit 10;-- 使用主键




-- MySQL的JDBC操作

create database if not exists mydb16_jdbc;
use mydb16_jdbc;

create table if not exists student(
	sid int primary key auto_increment,
	sname varchar(20),
	age int 
	);

insert into student values(null,'宋江',30),(null,'武松',28),(null,'林冲',26);