mysql索引,存储过程及优化讲解

1，索引

索引：索引是可以是查询速度表快，但是新增加修改的删除会变慢
索引其实是一种数据结构，类似于二叉树，B-Tree，查询速度就变快
索引表示创建在 内存中的，而是通过一张表（文本）维护的，如果新增修改删除表中的数据，除了删除表中的数据，除了删除数据表外，还需要维护索引表，所以比较慢。
单例索引 组合索引
单例索引：主键索引，唯一索引，普通索引
主键索引：当你将一个表中的数据字段指定位主键的时候，自动创建一个主键索引。
唯一索引：创建了唯一约束后，是否自动唯一索引？ 是
        创建唯一索引，是否会创建唯一约束？ 不会
怎么做：设计主键约束和唯一约束即可，不用考虑该列上的索引问题
​
什么时候创建索引?
1.该表中的数据比较多
2.where 条件后面的字段加索引
3.能够过滤更多数据的where 条件后面的加索引
    score 8，9,10   99万 1万  50万
    select * from where sex = 1 and score>10;
4. where 最多索引不能超过三个  and
5. order by 、group by 后面的字段也可以添加索引，可以提升效率
   order by 速度快
   group by 合并分组前，其实是进行了排序
   
SQL：
create index account_index on 表名(字段名称)；
create unique index account_unique_index ON '表名'(字段名)；
drop index indexName ON 'TableName'
​
主键：不能重复，不能有null
唯一约束：不能重复，可以有null
​

2.exists 代替 in

     https://blog.csdn.net/wqc19920906/article/details/79800374
1)exists
外表 （内表条件）
    每次拿出来一个外表的数据和内表进行一次查询
    外表数据10条，进行10次查询
    外表数据比较少，查询效率高
2）in 
外表（内表条件）
先查询，然后外表和内表做一个笛卡尔积，然后在将笛卡尔积进行过滤
先查询出来一个结果集，然后表里外表
内表查询出来的结果比较少，循环次数少。
该情况下，内表数据比较少，查询效率高

3.SQL优化

1）select count(*) from index_demo;
    替换为
        select count(1) from index_demo;

4、触发器[表上创建的]

create trigger tri_order  after insert on index_demo for each row insert into order_log(content)   values('新增一条记录');

5、存储过程【创建在数据库上的】

 贴：https://www.cnblogs.com/mark-chan/p/5384139.html
     DELIMITER //
      CREATE PROCEDURE myproc(OUT s int)
        BEGIN
          SELECT COUNT(*) INTO s FROM students;
        END //
    DELIMITER ;
    
    变量问题：
    
    全局变量
      show  variables;
      show  variables like 'character%';
      select @@变量名字;
    局部变量
      set @变量名 = 值;
      select @变量名;
      
    存储过程：
    根据是否有入参和出参：
    如果有返回值：
    call procedure pro_abc(@name);
    select @name;
    如果入参和出参都是一个类型：
    set @num =1;
    call procedure pro_num(@num); // num=num+10
    select @num;
    
    1)带循环的存储过程
       create procedure pro_testWhile(IN num int,OUT result int)
    -> begin
    -> declare i int default 1;
    -> declare sum int default 0;
    -> WHILE i < num DO
    -> SET sum = sum+ i;
    -> SET i=i+1;
    -> END WHILE;
    -> SET result = sum;
    -> end $;
    调用方法：call pro_testWhile(10,@sum);   
    select @sum;
    2) 带if判断的存储过程
     create procedure testIf(IN num int,OUT str varchar(30))
    -> begin
    -> IF num=1 THEN
    -> set str='星期一';
    -> ELSEIF num=2 THEN
    -> set str='星期二';
    -> ELSE set str='输入错误';
    -> END IF;
    -> end $

6、数据库引擎ENGINE【MYISAM,INNODB】

1、InnoDB支持事务，MyISAM不支持
    2、MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
    3、InnoDB支持外键，MyISAM不支持。
    4、从MySQL5.5.5以后，InnoDB是默认引擎
    5、InnoDB中不保存表的行数，如select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。
       注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表。
    6、InnoDB支持行锁，MyISAM 锁表

7、MySQL如何修改密码

 mysql数据库中的user表
    select password('root');
    update user set password=password('root') where  user='root';