MySQL03

存储引擎

1.mysql存储引擎
1.InnoDB
2.myIsam
3.Memory
4.Merge
2.查看当前默认的存储引擎
show variables like ‘default_storage_engine’
3.查看支持的存储引擎
show engines

索引

什么是索引：为了加快数据库查询速度而创建的一种存储结构，需要存储空间

创建索引

 1.创建表时创建索引：
        create table 表名(
        列名 数据类型 约束,
        index(列名) #创建表时创建索引)

    2.创建表以后创建索引
        create index 索引名称 on 表名(列名,列名)
    alter table 表名 add index 索引名(列名)

alter table 表名 add index 索引名(列名);

删除索引:drop index 索引名 on 表名

显示查看索引:show index from 表名

索引的设计原则

1. 选择where条件列作为索引列
2. 使用唯一索引
3. 使用短索引
4. 利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的行集称为最左前缀。
5. 尽量手工指定主键，主键本身就是唯一索引

索引的使用误区

1. 不是所有的表都必须创建索引
2. 不要过度索引
3. 谨慎创建低选择度的索引

索引的使用步骤

1、整理表上的所有SQL，重点包括select、update、delete操作的where条件所用到的列的组合、关联查询的关联条件等；
2、整理所有查询SQL的预期执行频率；
3、整理所有涉及的列的选择度，列的不同值相比总非空行数的比例较大，选择度越好，比如全部都是唯一值的主键列选择度最高；
4、遵照之前提到的设计原则，给表选择合适的主键；
5、优先给那些执行频率最高的SQL创建索引，执行频率很高的SQL，使用到的索引的效率对整体性能影响也会很大，选择其中选择度最高的列创建索引，如果选择度都不够好，那么应该考虑是否可以使用其他选择度更好的条件，或者选择创建联合索引；
6、按执行频率排序，依次检查是否需要为每个SQL创建索引；
7、索引合并，利用复合索引来降低索引的总数，充分利用最左前缀的原则；
8、上线之后，通过慢查询分析、执行计划分析、索引使用统计，来确定索引的实际使用情况。

Btree和Hash

1. Btree索引对于范围查询有效，Hash索引对于范围失效，走的是全表扫描
2. 对于精确查询where id = 10 Hash索引有效 Btree有效 Hash效率高

不可见索引

索引对于查询分析器不可见，但是存在
对于分析移除该索引的查询分析有用

创建不可见索引

create table t1(
i int,
j int,
index i_idx(i) invisible #创建不可见索引
);
alter table t1 add column k int;
#创建不可见索引
create index j_idx on t1(j) invisible;
#创建不可见索引
alter table t1 add index k_idx(k) invisible;
show index from t1;
show index from t1;

切换可见性

#修改已经存在的索引的可见性
alter table t1 alter index i_idx visible;

倒序索引

Descending index 倒序索引，正序索引的反向
1.创建表

		create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc))

2.查看建表语句

		show create table t1;

案例

1.创建表person:id,name,age,birthday
2.写一个存储过程p_person
(1)接收v_name,v_age,v_birthday
(2)使用insert插入数据库中
3.创建存储过程person_count
(1)接收v_name
(2)使用模糊查询like查询v_name的个数,放到输出参数显示

答案

创建表
create table person(
id int primary key auto_increment,
name varchar(20),
age int,
birthday date
)

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_person`(IN `v_name` varchar(20),IN `v_age` int,IN `v_birthday` date)
BEGIN
	insert into person (name,age,birthday) values(v_name,v_age,v_birthday);
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_count`(IN `v_name` varchar(20),out v_count int)
BEGIN
	select count(*) from person where name like concat('%',v_name,'%');
END

视图

1. 视图存储的是sql语句
2. 创建视图的语法：create view 视图名 as select * from 表名
3. 修改视图的语法是：alter view 视图名 as select * from 表名
4. 删除视图的语法是：drop view 视图名;

存储过程

实现编译存储在数据库的sql组合

1. 创建语法：create procedure 名称(参数)
   Begin
   语句
   End
2. 查看存储过程
   语法
   show create PROCEDURE 名称
   例子
   show create PROCEDURE p_users
   删除存储过程
   语法
   Drop Procedure 存储过程名
   例子
   drop PROCEDURE p_users

游标

声明游标
Declare cursor_name cursor for select语句
打开游标
Open 游标名
抓取数据
Fetch 游标名 into 变量名
关闭游标
Close 游标名

函数

#创建函数之前需要设置为1,否则会报错
show variables  like  'log_bin_trust_function_creators';
set  global log_bin_trust_function_creators = 1 ;

CREATE DEFINER=`root`@`localhost` FUNCTION `f_person_count`(`v_name` varchar(30)) RETURNS int(11)
		BEGIN
			DECLARE n int;
			SELECT count(*) into n from person where name like CONCAT('%',v_name,'%');
			RETURN n;
		END

触发器

	CREATE TRIGGER `trg_users_insert` AFTER INSERT ON `users` FOR EACH ROW begin
		update number set num=(select count(*) from users);
	end;