MySQL进阶

MySQL体系结构

存储引擎

引擎 engine

innodb(默认存储引擎)

MyISAM

Memory

存储引擎对比

存储引擎的选择

存储引擎总结

索引

索引文件存储在后缀为.MYI文件中

BTree索引结构

中间值向上提
往前插入就是平均值向前取
往后插入就是平均值向后取

hash索引

不声明主键索引，默认就会自动创建一个。

聚集索引叶子节点储存的是一行的数据 ，聚集索引只能有一个
二级索引，叶子节点不能够存储整行数据，应该存储对应的id值，避免冗余

一页的大小为16k

索引语法

#创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_column_name,...);

#查看索引
SHOW INDEX FROM table_name;

#删除索引
DROP INDEX index_name ON table_name;

#name字段为姓名字段，该字段的值可能会重复,为name字段创建索引
CREATE INDEX idx_user_name ON tb_user(name);

#phone手机号字段的值时非空且唯一的，为phone字段创建索引
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

#为profession、age、status创建联合索引
CREATE INDEX idx_user_pro_age_status ON tb_user(profession,age,status);

#为email创建合适的索引来提升查询效率
CREATE INDEX idx_user_email ON tb_user(email);

#删除idx_user_email索引
DROP INDEX idx_user_email ON tb_user;

SQL索引性能优化——执行频率

查看执行次数

show global status like 'com_______';    一共七条杠

SQL索引性能优化——慢查询

慢查询开关状态

show variables like 'slow_query_log';

慢查询日志在目录为

/var/lib/mysql

名字不唯一，进去看看后缀为log的文件
然后tail -f 文件名打开查看查询慢的sql

SQL索引性能优化——profile详情

查看数据库是否支持profiling

select @@have_profiling;

查看profiling的状态

select @@profiling;

开启profiling

set profling=1;

查看sql语句的执行时间

show profiles;

查看指定id的sql语句各阶段所耗时间

show profiles cpu from query 2;

SQL索引性能优化——explain执行计划

explain select * from 表名；
或者
desc select * from 表名；

SQL索引使用

在未建索引时查询耗时

建完索引后再次查询，耗时0.00s

SQL索引使用最左原则

满足条件必须是聚合索引，
在聚合索引中前一个字段未列出，后面的字段索引也会失效，字段只要存在就行，不需要完全遵守顺序。，中间字段未列出，后面索引失效。索引长度降低一些。
尽量使用》=或者《=

索引使用失效

模糊查找的位置不同，头部不支持索引的使用，尾部支持索引的使用

使用use只是建议使用，mysql用不用看性能。



方案：
创建username与password联合索引，联合索引相当于二级索引，叶子节点存放的是主键id,这样数据齐全，就不用回表查询了。

SQL优化

插入数据优化

大批量导入数据时，需要先创建表结构。

mysql --local-infile -u root -p

set global local_infile=1;

select @@local_infile;

load data local infile '/root/文件名称' into table '表名' files terminated by ',' lines terminated by '\n';

','代表文件中是逗号分隔，'\n'代表的是每一条数据是换行符分隔，主键顺序存储快

主键优化

主键乱序插入，数据库会进行排序，会产生页分裂

order by 优化

group by 优化

limit优化

再查查资料了解了解

count优化

update优化

视图

比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创 建时未指定检查选项。 则在执行检查时，只会检查v2，不会检查v2的关联视图v1。

存储过程

注意
在命令行执行此段代码时，sql语句的结束分号会被当做最后的分号，要使用
1delimiter $$ 来指定结束标志。

set @myname='itcast';
set @myname:='it',@myage:='18';

beigin
declare stu_count int default 0;
select count(*) into stu_count from student;
select stu_count;
end;

create procedure p4(in score int,out result varchar(10))
begin 
if score>=85 then
   set result:='优秀';
elseif score>=60 then
set result:='及格';
else 
set result:='不及格';
end if;
end;



call p4(68,@result);
select @result;

create procedure p5(in month int)
begin
declare result varchar(10);

case 
	when month >=1 and month<=3 then
		set result:='第一季度';
	when month >=4 and month<=36 then
		set result:='第一季度';
	when month >=7 and month<=9 then
		set result:='第一季度';
	when month >=10 and month<=12 then
		set result:='第一季度';
	else 
		set result :='非法参数';
	end case;
select concat('输入的月份为',month,'季度为',result);
end;

call p6(6);

crate procedure p7(in n int)
begin
declare total int default 0;
 while n>0 do
 		set total :=total +n;
 		set n:=n-1;
 end while;
 select total;
end;
call p7(12);

只要满足就会退出循环

create procedure p1(in n int)
begin
declare total int defalut 0;
repeat
set total :=total+n;
set n:=n-1;
until n<=0
end repeat;

end;

触发器

create table user_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型，insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作的ID',
    operate_params varchar(500) comment '操作参数',
    primary key (`id`)
)engine=innodb default charset=utf8;

# 插入数据触发器
create trigger tb_user_insert_trigger
    after insert on tb_user for each row
    begin
        insert into user_logs(id,operation,operate_time,operate_id,operate_params) values
            (null,'insert',now(),new.id, concat('插入的数据内容为:id=',new.id,',name=',new.name,',phone=',new.phone,',email=',new.email,',profession=',new.profession));
    end;

# 跟新数据的触发器
create trigger tb_user_update_trigger
    after update on tb_user for each row
    begin
        insert into user_logs(id, operation, operate_time, operate_id, operate_params) values
            (null,'insert',now(),new.id, concat('更新前的数据内容为:id=',old.id,',name=',old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession,
               '更新后的数据内容为:id=',new.id,',name=',new.name,',phone=',new.phone,',email=',new.email,',profession=',new.profession ));
    end;

# 更新数据的触发器
create trigger tb_user_delete_trigger
    after delete on tb_user for each row
    begin
        insert into user_logs(id, operation, operate_time, operate_id, operate_params) values
            (null,'delete',now(),old.id, concat('更新前的数据内容为:id=',old.id,',name=',old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession ));
    end;

# 查看触发器
show triggers;

# 删除触发器
drop trigger tb_user_insert_trigger;

锁

全局锁

上锁之后，只能读
上全局锁后，执行备份数据操作。

#上全局锁
flush tables with read lock;

mysqldump -h 192.168.200.202 -uroot -p密码 数据库名 >D:/数据库名.sql

#解除锁
unlock tables;

表级锁

读锁
lock tables 表名 read;
解除锁
unlock tables;

写锁
lock tables 表名 write;
解锁
unlock tables;

元数据锁
意向锁
如果有某行数据加上了行锁，但没有加表锁时，加表锁时会逐行检查行锁是否与表锁冲突，如果冲突，则阻塞，等待解除冲突的行锁结束，但如果加了意向锁，就不会逐行检查

行级锁

插入缺失段的数据，会触发间隙锁。

InnoDB引擎

存储架构

事务原理

MVCC

底层原理，不懂再去看看

Mysql管理

mysqldump -h 192.168.200.202 -uroot -p密码 数据库名 >D:/数据库名.sql