MySQL03

﹏ヽ暗。殇╰゛Y 2021-09-25 08:48 325阅读 0赞

### 文章目录 ###

*  存储引擎
 *  索引
 *   *  什么是索引：为了加快数据库查询速度而创建的一种存储结构，需要存储空间
     *  创建索引
     *  删除索引:drop index 索引名 on 表名
     *  显示查看索引:show index from 表名
     *  索引的设计原则
     *  索引的使用误区
     *  索引的使用步骤
     *  Btree和Hash
     *  不可见索引
     *   *  创建不可见索引
         *  切换可见性
     *  倒序索引
 *  案例
 *  视图
 *  存储过程
 *  游标
 *  函数
 *  触发器

# 存储引擎 #

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
    )

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5cGVydGV4dDEyMw_size_16_color_FFFFFF_t_70_pic_center]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5cGVydGV4dDEyMw_size_16_color_FFFFFF_t_70_pic_center 1]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5cGVydGV4dDEyMw_size_16_color_FFFFFF_t_70_pic_center 2]

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;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5cGVydGV4dDEyMw_size_16_color_FFFFFF_t_70_pic_center]: /images/20210923/695f705f034b4ff08a3c52635b34e94c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5cGVydGV4dDEyMw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20210923/f06c7c10b4ab41808d0d5849bbfbf5e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h5cGVydGV4dDEyMw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20210923/abdc66ae3d0e4045ba003babba412377.png