day39 视图触发器事务存储过程函数流程控制索引与慢查询优化

柔光的暖阳◎ 2023-08-17 17:48 105阅读 0赞

# 视图 #

视图  
什么是视图?  
一个查询语句的结果是一张虚拟表,将这种虚拟表保存下来  
它就变成了一个视图

为什么要用视图?  
当频繁需要用到多张表的连表结果,你就可以事先生成好视图  
之后直接调用即可,避免了反复写连表操作的sql语句

如何使用?

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

create view teacher_course as select * from teacher INNER JOIN course
    on teacher.tid = course.teacher_id;

注意:

1.视图只有表结构,视图中的数据还是来源于原来的表
    2.不要改动视图表中的数据,因为其他的表会收到影响
    3.一般情况下不会频繁的使用视图来写业务逻辑

# 触发器 #

到达某个条件自动触发

当你在对数据进行增 删 改的情况下会自动触发触发器的运行

在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器

## 创建触发器语法 ##

六种情况

增前,增后 删前,删后 改前,改后

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

# 针对插入
    create trigger tri_after_insert_t1 after insert on 表名 for each row
    begin
        sql代码。。。
    end 
    create trigger tri_after_insert_t2 before insert on 表名 for each row
    begin
        sql代码。。。
    end
    
    # 针对删除
    create trigger tri_after_delete_t1 after delete on 表名 for each row
    begin
        sql代码。。。
    end
    create trigger tri_after_delete_t2 before delete on 表名 for each row
    begin
        sql代码。。。
    end
    
    # 针对修改
    create trigger tri_after_update_t1 after update on 表名 for each row
    begin
        sql代码。。。
    end
    create trigger tri_after_update_t2 before update on 表名 for each row
    begin
        sql代码。。。
    end
    
    # 案例
    CREATE TABLE cmd (
        id INT PRIMARY KEY auto_increment,
        USER CHAR (32),
        priv CHAR (10),
        cmd CHAR (64),
        sub_time datetime, #提交时间
        success enum ('yes', 'no') #0代表执行失败
    );
    
    CREATE TABLE errlog (
        id INT PRIMARY KEY auto_increment,
        err_cmd CHAR (64),
        err_time datetime
    );
    
    delimiter $$  # 将mysql默认的结束符由;换成$$
    create trigger tri_after_insert_cmd after insert on cmd for each row
    begin
        if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
            insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
        end if;
    end $$
    delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了
    
    #往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
    INSERT INTO cmd (
        USER,
        priv,
        cmd,
        sub_time,
        success
    )
    VALUES
        ('egon','0755','ls -l /etc',NOW(),'yes'),
        ('egon','0755','cat /etc/passwd',NOW(),'no'),
        ('egon','0755','useradd xxx',NOW(),'no'),
        ('egon','0755','ps aux',NOW(),'yes');
    
    # 查询errlog表记录
    select * from errlog;
    # 删除触发器
    drop trigger tri_after_insert_cmd;

# 事务 #

事务（Transaction），一般是指要做的或所做的事情。在计算机[术语][Link 1]中是指访问并可能更新数据库中各种[数据项][Link 2]的一个程序执行单元(unit)。事务通常由[高级数据库][Link 3]操纵语言或编程语言（如SQL，C++或Java）书写的[用户程序][Link 4]的执行所引起，并用形如begin transaction和end transaction语句（或[函数调用][Link 5]）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

[事务][Link 6]是恢复和[并发控制][Link 7]的基本单位。

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为**ACID特性**。
    
    原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
    
    一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
    
    隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
    
    持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

create table user(
    id int primary key auto_increment,
    name char(32),
    balance int
    );
    
    insert into user(name,balance)
    values
    ('wsb',1000),
    ('egon',1000),
    ('ysb',1000);
    
    # 修改数据之前先开启事务操作
    start transaction;
    
    # 修改操作
    update user set balance=900 where name='wsb'; #买支付100元
    update user set balance=1010 where name='egon'; #中介拿走10元
    update user set balance=1090 where name='ysb'; #卖家拿到90元
    
    # 回滚到上一个状态
    rollback;
    
    # 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
    commit;
    """开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""
    
    # 站在python代码的角度，应该实现的伪代码逻辑，
    try:
        update user set balance=900 where name='wsb'; #买支付100元
        update user set balance=1010 where name='egon'; #中介拿走10元
        update user set balance=1090 where name='ysb'; #卖家拿到90元
    except 异常:
        rollback;
    else:
        commit;
    
    # 那如何检测异常？

# 存储过程 #

就类似于python中的自定义函数

内部封装了操作数据库的sql语句,后续想要实现相应的操作 只需要调用存储过程即可

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL，通过调用它的名字可以执行其内部的一堆sql.

## 程序与数据库结合使用的三种方式 ##

#方式一：
        MySQL：存储过程
        程序：调用存储过程
    
    #方式二：
        MySQL：
        程序：纯SQL语句
    
    #方式三：
        MySQL:
        程序：类和对象，即ORM（本质还是纯SQL语句）

## 创建存储过程 ##

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

delimiter $$
    create procedure p1(
        in m int,  # in表示这个参数必须只能是传入不能被返回出去
        in n int,  
        out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
    )
    begin
        select tname from teacher where tid > m and tid < n;
        set res=0;
    end $$
    delimiter ;
    
    # 小知识点补充，当一张表的字段特别多记录也很多的情况下，终端下显示出来会出现显示错乱的问题
    select * from mysql.user\G;

## 如何用存储过程 ##

# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！
    
    # 1、直接在mysql中调用
    set @res=10  # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
    call p1(2,4,10);  # 报错
    call p1(2,4,@res);  
    
    # 查看结果
    select @res;  # 执行成功，@res变量值发生了变化
    
    # 2、在python程序中调用
    pymysql链接mysql
    产生的游表cursor.callproc('p1',(2,4,10))  # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
    cursor.excute('select @_p1_2;')
    
    
    # 3、存储过程与事务使用举例(了解)
    delimiter //
    create PROCEDURE p5(
        OUT p_return_code tinyint
    )
    BEGIN
        DECLARE exit handler for sqlexception
        BEGIN
            -- ERROR
            set p_return_code = 1;
            rollback;
        END;
    
    
      DECLARE exit handler for sqlwarning
      BEGIN
          -- WARNING
          set p_return_code = 2;
          rollback;
      END;
    
      START TRANSACTION;
          update user set balance=900 where id =1;
          update user123 set balance=1010 where id = 2;
          update user set balance=1090 where id =3;
      COMMIT;
    
      -- SUCCESS
      set p_return_code = 0; #0代表执行成功
    
    
    END //
    delimiter ;

mysql调用存储功能

import pymysql
    
    conn = pymysql.connect(
        host = '127.0.0.1',
        port = 3306,
        user = 'root',
        password = '123',
        database = 'day38',
        charset = 'utf8',
        autocommit = True
    )
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    # call p1()  mysql中调用
    # callproc()  pymysql中调用
    cursor.callproc('p1',(1,5,10))
    # 内部自动用变量名存储了对应的值
    print(cursor.fetchall())
    """
    @_p1_0=1
    @_p1_1=5
    @_p1_2=10
    
    
    @_存储过程名_索引值
    """
    
    cursor.execute('select @_p1_0')
    print(cursor.fetchall())
    cursor.execute('select @_p1_1')
    print(cursor.fetchall())
    cursor.execute('select @_p1_2')
    print(cursor.fetchall())

# 函数 #

注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！

参考博客

[https://www.cnblogs.com/linhaifeng/articles/7495918.html\#\_label5][https_www.cnblogs.com_linhaifeng_articles_7495918.html_label5]

一、数学函数
        ROUND(x,y)
            返回参数x的四舍五入的有y位小数的值
            
        RAND()
            返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
    
    二、聚合函数(常用于GROUP BY从句的SELECT查询中)
        AVG(col)返回指定列的平均值
        COUNT(col)返回指定列中非NULL值的个数
        MIN(col)返回指定列的最小值
        MAX(col)返回指定列的最大值
        SUM(col)返回指定列的所有值之和
        GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果    
        
    三、字符串函数
    
        CHAR_LENGTH(str)
            返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
        CONCAT(str1,str2,...)
            字符串拼接
            如有任何一个参数为NULL ，则返回值为 NULL。
        CONCAT_WS(separator,str1,str2,...)
            字符串拼接（自定义连接符）
            CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
    
        CONV(N,from_base,to_base)
            进制转换
            例如：
                SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示
    
        FORMAT(X,D)
            将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
            例如：
                SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
        INSERT(str,pos,len,newstr)
            在str的指定位置插入字符串
                pos：要替换位置其实位置
                len：替换的长度
                newstr：新字符串
            特别的：
                如果pos超过原字符串长度，则返回原字符串
                如果len超过原字符串长度，则由新字符串完全替换
        INSTR(str,substr)
            返回字符串 str 中子字符串的第一个出现位置。
    
        LEFT(str,len)
            返回字符串str 从开始的len位置的子序列字符。
    
        LOWER(str)
            变小写
    
        UPPER(str)
            变大写
       
        REVERSE(str)
            返回字符串 str ，顺序和字符顺序相反。
            
        SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
            不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
    
            mysql> SELECT SUBSTRING('Quadratically',5);
                -> 'ratically'
    
            mysql> SELECT SUBSTRING('foobarbar' FROM 4);
                -> 'barbar'
    
            mysql> SELECT SUBSTRING('Quadratically',5,6);
                -> 'ratica'
    
            mysql> SELECT SUBSTRING('Sakila', -3);
                -> 'ila'
    
            mysql> SELECT SUBSTRING('Sakila', -5, 3);
                -> 'aki'
    
            mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
                -> 'ki'
                
    四、日期和时间函数
        CURDATE()或CURRENT_DATE() 返回当前的日期
        CURTIME()或CURRENT_TIME() 返回当前的时间
        DAYOFWEEK(date)   返回date所代表的一星期中的第几天(1~7)
        DAYOFMONTH(date)  返回date是一个月的第几天(1~31)
        DAYOFYEAR(date)   返回date是一年的第几天(1~366)
        DAYNAME(date)   返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
        FROM_UNIXTIME(ts,fmt)  根据指定的fmt格式，格式化UNIX时间戳ts
        HOUR(time)   返回time的小时值(0~23)
        MINUTE(time)   返回time的分钟值(0~59)
        MONTH(date)   返回date的月份值(1~12)
        MONTHNAME(date)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
        NOW()    返回当前的日期和时间
        QUARTER(date)   返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
        WEEK(date)   返回日期date为一年中第几周(0~53)
        YEAR(date)   返回日期date的年份(1000~9999)
        
        重点:
        DATE_FORMAT(date,format) 根据format字符串格式化date值
    
           mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
            -> 'Sunday October 2009'
           mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
            -> '22:23:00'
           mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
            ->                 '%D %y %a %d %m %b %j');
            -> '4th 00 Thu 04 10 Oct 277'
           mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
            ->                 '%H %k %I %r %T %S %w');
            -> '22 22 10 10:23:00 PM 22:23:00 00 6'
           mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
            -> '1998 52'
           mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
            -> '00'
            
    五、加密函数
        MD5()    
            计算字符串str的MD5校验和
        PASSWORD(str)   
            返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
            
    六、控制流函数            
        CASE WHEN[test1] THEN [result1]...ELSE [default] END
            如果testN是真，则返回resultN，否则返回default
        CASE [test] WHEN[val1] THEN [result]...ELSE [default]END  
            如果test和valN相等，则返回resultN，否则返回default
    
        IF(test,t,f)   
            如果test是真，返回t；否则返回f
    
        IFNULL(arg1,arg2) 
            如果arg1不是空，返回arg1，否则返回arg2
    
        NULLIF(arg1,arg2) 
            如果arg1=arg2返回NULL；否则返回arg1        
            
    七、控制流函数小练习
    #7.1、准备表
    /*
    Navicat MySQL Data Transfer
    
    Source Server         : localhost_3306
    Source Server Version : 50720
    Source Host           : localhost:3306
    Source Database       : student
    
    Target Server Type    : MYSQL
    Target Server Version : 50720
    File Encoding         : 65001
    
    Date: 2018-01-02 12:05:30
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for course
    -- ----------------------------
    DROP TABLE IF EXISTS `course`;
    CREATE TABLE `course` (
      `c_id` int(11) NOT NULL,
      `c_name` varchar(255) DEFAULT NULL,
      `t_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`c_id`),
      KEY `t_id` (`t_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of course
    -- ----------------------------
    INSERT INTO `course` VALUES ('1', 'python', '1');
    INSERT INTO `course` VALUES ('2', 'java', '2');
    INSERT INTO `course` VALUES ('3', 'linux', '3');
    INSERT INTO `course` VALUES ('4', 'web', '2');
    
    -- ----------------------------
    -- Table structure for score
    -- ----------------------------
    DROP TABLE IF EXISTS `score`;
    CREATE TABLE `score` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `s_id` int(10) DEFAULT NULL,
      `c_id` int(11) DEFAULT NULL,
      `num` double DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of score
    -- ----------------------------
    INSERT INTO `score` VALUES ('1', '1', '1', '79');
    INSERT INTO `score` VALUES ('2', '1', '2', '78');
    INSERT INTO `score` VALUES ('3', '1', '3', '35');
    INSERT INTO `score` VALUES ('4', '2', '2', '32');
    INSERT INTO `score` VALUES ('5', '3', '1', '66');
    INSERT INTO `score` VALUES ('6', '4', '2', '77');
    INSERT INTO `score` VALUES ('7', '4', '1', '68');
    INSERT INTO `score` VALUES ('8', '5', '1', '66');
    INSERT INTO `score` VALUES ('9', '2', '1', '69');
    INSERT INTO `score` VALUES ('10', '4', '4', '75');
    INSERT INTO `score` VALUES ('11', '5', '4', '66.7');
    
    -- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student` (
      `s_id` varchar(20) NOT NULL,
      `s_name` varchar(255) DEFAULT NULL,
      `s_age` int(10) DEFAULT NULL,
      `s_sex` char(1) DEFAULT NULL,
      PRIMARY KEY (`s_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES ('1', '鲁班', '12', '男');
    INSERT INTO `student` VALUES ('2', '貂蝉', '20', '女');
    INSERT INTO `student` VALUES ('3', '刘备', '35', '男');
    INSERT INTO `student` VALUES ('4', '关羽', '34', '男');
    INSERT INTO `student` VALUES ('5', '张飞', '33', '女');
    
    -- ----------------------------
    -- Table structure for teacher
    -- ----------------------------
    DROP TABLE IF EXISTS `teacher`;
    CREATE TABLE `teacher` (
      `t_id` int(10) NOT NULL,
      `t_name` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`t_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of teacher
    -- ----------------------------
    INSERT INTO `teacher` VALUES ('1', '大王');
    INSERT INTO `teacher` VALUES ('2', 'alex');
    INSERT INTO `teacher` VALUES ('3', 'egon');
    INSERT INTO `teacher` VALUES ('4', 'peiqi');
    
    #7.2、统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
    
    select  score.c_id,
              course.c_name, 
          sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 END) as '[100-85]',
          sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as '[85-70]',
          sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as '[70-60]',
          sum(CASE WHEN num < 60 THEN 1 ELSE 0 END) as '[ <60]'
    from score,course where score.c_id=course.c_id GROUP BY score.c_id;

CREATE TABLE blog (
        id INT PRIMARY KEY auto_increment,
        NAME CHAR (32),
        sub_time datetime
    );
    
    INSERT INTO blog (NAME, sub_time)
    VALUES
        ('第1篇','2015-03-01 11:31:21'),
        ('第2篇','2015-03-11 16:31:21'),
        ('第3篇','2016-07-01 10:21:31'),
        ('第4篇','2016-07-22 09:23:21'),
        ('第5篇','2016-07-23 10:11:11'),
        ('第6篇','2016-07-25 11:21:31'),
        ('第7篇','2017-03-01 15:33:21'),
        ('第8篇','2017-03-01 17:32:21'),
        ('第9篇','2017-03-01 18:31:21');
    
    select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

# 流程控制 #

# if条件语句
    delimiter //
    CREATE PROCEDURE proc_if ()
    BEGIN
        
        declare i int default 0;
        if i = 1 THEN
            SELECT 1;
        ELSEIF i = 2 THEN
            SELECT 2;
        ELSE
            SELECT 7;
        END IF;
    
    END //
    delimiter ;

# while循环
    delimiter //
    CREATE PROCEDURE proc_while ()
    BEGIN
    
        DECLARE num INT ;
        SET num = 0 ;
        WHILE num < 10 DO
            SELECT
                num ;
            SET num = num + 1 ;
        END WHILE ;
    
    END //
    delimiter ;

repeat

delimiter //
    CREATE PROCEDURE proc_repeat ()
    BEGIN
    
        DECLARE i INT ;
        SET i = 0 ;
        repeat
            select i;
            set i = i + 1;
            until i >= 5
        end repeat;
    
    END //
    delimiter ;
    
     repeat循环

loop

BEGIN
        
        declare i int default 0;
        loop_label: loop
            
            set i=i+1;
            if i<8 then
                iterate loop_label;
            end if;
            if i>=10 then
                leave loop_label;
            end if;
            select i;
        end loop loop_label;
    
    END

# 索引与慢查询优化  #

参考博客

[https://www.cnblogs.com/linhaifeng/articles/7274563.html][https_www.cnblogs.com_linhaifeng_articles_7274563.html]

数据都是存在硬盘上的，那查询数据不可避免的需要进行IO操作

*索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。*

*  primary key
 *  unique key
 *  index key

注意foreign key不是用来加速查询用的，不在我们研究范围之内,上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询

索引就是一种数据结构，类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引的影响:

*  在表中有大量数据的前提下，创建索引速度会很慢
 *  在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低

## b+树 ##

B+树是通过二叉查找树，再由平衡二叉树，B树演化而来

![1659204-20190823172804484-1806099897.png][]

如图:浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），
    如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
    真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，
    如17、35并不真实存在于数据表中。

#### 　b+树的查找过程 ####

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，
    此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，
    通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，
    通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
    真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，
    如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据
    
    查询次数由树的层级决定，层级越低次数越少

## 聚集索引(primary key) ##

聚集索引其实指的就是表的主键，innodb引擎规定一张表中必须要有主键。先来回顾一下存储引擎。
    
    myisam在建表的时候对应到硬盘有几个文件(三个)？
    
    innodb在建表的时候对应到硬盘有几个文件(两个)？frm文件只存放表结构，不可能放索引，也就意味着innodb的索引跟数据都放在idb表数据文件中。
    
    **特点:**叶子结点放的一条条完整的记录

#### 辅助索引(unique,index ####

#### ) ####

辅助索引:查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引
    
    **特点：**叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})
    
    select name from user where name='jason';
    
    上述语句叫覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据
    
    select age from user where name='jason';
    
    上述语句叫非覆盖索引,虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找

### 测试索引 ###

##### 准备 #####

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

#1. 准备表
    create table s1(
    id int,
    name varchar(20),
    gender char(6),
    email varchar(50)
    );
    
    #2. 创建存储过程，实现批量插入记录
    delimiter $$ #声明存储过程的结束符号为$$
    create procedure auto_insert1()
    BEGIN
        declare i int default 1;
        while(i<3000000)do
            insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
            set i=i+1;
        end while;
    END$$ #$$结束
    delimiter ; #重新声明 分号为结束符号
    
    #3. 查看存储过程
    show create procedure auto_insert1\G 
    
    #4. 调用存储过程
    call auto_insert1();

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

# 表没有任何索引的情况下
    select * from s1 where id=30000;
    # 避免打印带来的时间损耗
    select count(id) from s1 where id = 30000;
    select count(id) from s1 where id = 1;
    
    # 给id做一个主键
    alter table s1 add primary key(id);  # 速度很慢
    
    select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
    select count(id) from s1 where name = 'jason'  # 速度仍然很慢
    
    
    """
    范围问题
    """
    # 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
    select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
    select count(id) from s1 where id >1 and id < 3;
    select count(id) from s1 where id > 1 and id < 10000;
    select count(id) from s1 where id != 3;
    
    alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
    select count(id) from s1 where name = 'jason';  # 又慢了
    
    create index idx_name on s1(name);  # 给s1表的name字段创建索引
    select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
    """
    再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
    那这个树其实就建成了“一根棍子”
    """
    select count(id) from s1 where name = 'xxx';  
    # 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
    select count(id) from s1 where name like 'xxx';
    select count(id) from s1 where name like 'xxx%';
    select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性
    
    # 区分度低的字段不能建索引
    drop index idx_name on s1;
    
    # 给id字段建普通的索引
    create index idx_id on s1(id);
    select count(id) from s1 where id = 3;  # 快了
    select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算
    
    drop index idx_id on s1;
    select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
    # 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
    create index idx_name on s1(name);
    select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速
    
    drop index idx_name on s1;
    # 给name，gender这种区分度不高的字段加上索引并不难加快查询速度
    
    create index idx_id on s1(id);
    select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
    select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段
    
    drop index idx_id on s1
    
    create index idx_email on s1(email);
    select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉

#### 联合索引 ####

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
    # 如果上述四个字段区分度都很高，那给谁建都能加速查询
    # 给email加然而不用email字段
    select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
    # 给name加然而不用name字段
    select count(id) from s1 where gender = 'male' and id > 3; 
    # 给gender加然而不用gender字段
    select count(id) from s1 where id > 3; 
    
    # 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
    create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
    select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快

转载于:https://www.cnblogs.com/komorebi/p/11401560.html

[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20230808/7e640cb11e0a4964aaccf334b6c518a8.png
[Link 1]: https://baike.baidu.com/item/%E6%9C%AF%E8%AF%AD
[Link 2]: https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E9%A1%B9/3227309
[Link 3]: https://baike.baidu.com/item/%E9%AB%98%E7%BA%A7%E6%95%B0%E6%8D%AE%E5%BA%93/1439366
[Link 4]: https://baike.baidu.com/item/%E7%94%A8%E6%88%B7%E7%A8%8B%E5%BA%8F/7450916
[Link 5]: https://baike.baidu.com/item/%E5%87%BD%E6%95%B0%E8%B0%83%E7%94%A8/4127405
[Link 6]: https://baike.baidu.com/item/%E4%BA%8B%E5%8A%A1/5945882
[Link 7]: https://baike.baidu.com/item/%E5%B9%B6%E5%8F%91%E6%8E%A7%E5%88%B6
[https_www.cnblogs.com_linhaifeng_articles_7495918.html_label5]: https://www.cnblogs.com/linhaifeng/articles/7495918.html#_label5
[https_www.cnblogs.com_linhaifeng_articles_7274563.html]: https://www.cnblogs.com/linhaifeng/articles/7274563.html
[1659204-20190823172804484-1806099897.png]: /images/20230808/286b71a443d5443c914f489f9f0cde1a.png