MySQL--视图、触发器、事务、存储过程、内置函数、流程控制、索引

「爱情、让人受尽委屈。」 2023-08-17 16:34 118阅读 0赞

*  视图
 *  触发器
 *  事务
 *  存储过程
 *  内置函数
 *  流程控制
 *  索引

## 视图 ##

#### 1、什么是视图 ####

视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可

#### 2、为什么要用视图 ####

如果要频繁使用一张虚拟表，可以不用重复查询

#### 3、如何用视图 ####

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>create view teacher2course as</code> 
     </div> 
     <div> 
      <code>select&nbsp;</code> 
      <code>*</code>&nbsp; 
      <code>from</code>&nbsp; 
      <code>teacher inner join course on teacher.tid&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>course.teacher_id;</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

**思考:开发过程中会不会去使用视图？**

不会！视图是mysql的功能，如果你的项目里面大量的使用到了视图，那意味着你后期想要扩张某个功能的时候这个功能恰巧又需要对视图进行修改，意味着你需要先在mysql这边将视图先修改一下，然后再去应用程序中修改对应的sql语句，这就涉及到跨部门沟通的问题，所以通常不会使用视图，而是通过重新修改sql语句来扩展功能

## 触发器 ##

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

#### 为何要用触发器 ####

触发器专门针对我们对某一张表数据增insert、删delete、改update的行为，这类行为一旦执行 就会触发触发器的执行，即自动运行另外一段sql代码

#### 创建触发器语法 ####

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> 
    <div>
      14 
    </div> 
    <div>
      15 
    </div> 
    <div>
      16 
    </div> 
    <div>
      17 
    </div> 
    <div>
      18 
    </div> 
    <div>
      19 
    </div> 
    <div>
      20 
    </div> 
    <div>
      21 
    </div> 
    <div>
      22 
    </div> 
    <div>
      23 
    </div> 
    <div>
      24 
    </div> 
    <div>
      25 
    </div> 
    <div>
      26 
    </div> 
    <div>
      27 
    </div> 
    <div>
      28 
    </div> 
    <div>
      29 
    </div> 
    <div>
      30 
    </div> 
    <div>
      31 
    </div> 
    <div>
      32 
    </div> 
    <div>
      33 
    </div> 
    <div>
      34 
    </div> 
    <div>
      35 
    </div> 
    <div>
      36 
    </div> 
    <div>
      37 
    </div> 
    <div>
      38 
    </div> 
    <div>
      39 
    </div> 
    <div>
      40 
    </div> 
    <div>
      41 
    </div> 
    <div>
      42 
    </div> 
    <div>
      43 
    </div> 
    <div>
      44 
    </div> 
    <div>
      45 
    </div> 
    <div>
      46 
    </div> 
    <div>
      47 
    </div> 
    <div>
      48 
    </div> 
    <div>
      49 
    </div> 
    <div>
      50 
    </div> 
    <div>
      51 
    </div> 
    <div>
      52 
    </div> 
    <div>
      53 
    </div> 
    <div>
      54 
    </div> 
    <div>
      55 
    </div> 
    <div>
      56 
    </div> 
    <div>
      57 
    </div> 
    <div>
      58 
    </div> 
    <div>
      59 
    </div> 
    <div>
      60 
    </div> 
    <div>
      61 
    </div> 
    <div>
      62 
    </div> 
    <div>
      63 
    </div> 
    <div>
      64 
    </div> 
    <div>
      65 
    </div> 
    <div>
      66 
    </div> 
    <div>
      67 
    </div> 
    <div>
      68 
    </div> 
    <div>
      69 
    </div> 
    <div>
      70 
    </div> 
    <div>
      71 
    </div> 
    <div>
      72 
    </div> 
    <div>
      73 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code># 针对插入</code> 
     </div> 
     <div> 
      <code>create trigger tri_after_insert_t1 after insert on 表名&nbsp;</code> 
      <code>for</code>&nbsp; 
      <code>each row</code> 
     </div> 
     <div> 
      <code>begin</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>sql代码。。。</code> 
     </div> 
     <div> 
      <code>end</code> 
     </div> 
     <div> 
      <code>create trigger tri_after_insert_t2 before insert on 表名&nbsp;</code> 
      <code>for</code>&nbsp; 
      <code>each row</code> 
     </div> 
     <div> 
      <code>begin</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>sql代码。。。</code> 
     </div> 
     <div> 
      <code>end</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code># 针对删除</code> 
     </div> 
     <div> 
      <code>create trigger tri_after_delete_t1 after delete on 表名&nbsp;</code> 
      <code>for</code>&nbsp; 
      <code>each row</code> 
     </div> 
     <div> 
      <code>begin</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>sql代码。。。</code> 
     </div> 
     <div> 
      <code>end</code> 
     </div> 
     <div> 
      <code>create trigger tri_after_delete_t2 before delete on 表名&nbsp;</code> 
      <code>for</code>&nbsp; 
      <code>each row</code> 
     </div> 
     <div> 
      <code>begin</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>sql代码。。。</code> 
     </div> 
     <div> 
      <code>end</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code># 针对修改</code> 
     </div> 
     <div> 
      <code>create trigger tri_after_update_t1 after update on 表名&nbsp;</code> 
      <code>for</code>&nbsp; 
      <code>each row</code> 
     </div> 
     <div> 
      <code>begin</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>sql代码。。。</code> 
     </div> 
     <div> 
      <code>end</code> 
     </div> 
     <div> 
      <code>create trigger tri_after_update_t2 before update on 表名&nbsp;</code> 
      <code>for</code>&nbsp; 
      <code>each row</code> 
     </div> 
     <div> 
      <code>begin</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>sql代码。。。</code> 
     </div> 
     <div> 
      <code>end</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code># 案例</code> 
     </div> 
     <div> 
      <code>CREATE TABLE cmd (</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>id</code>&nbsp; 
      <code>INT</code>&nbsp; 
      <code>PRIMARY KEY auto_increment,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>USER CHAR (</code> 
      <code>32</code> 
      <code>),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>priv CHAR (</code> 
      <code>10</code> 
      <code>),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>cmd CHAR (</code> 
      <code>64</code> 
      <code>),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>sub_time datetime,&nbsp;</code> 
      <code>#提交时间</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>success enum (</code> 
      <code>'yes'</code> 
      <code>,&nbsp;</code> 
      <code>'no'</code> 
      <code>)&nbsp;</code> 
      <code>#0代表执行失败</code> 
     </div> 
     <div> 
      <code>);</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>CREATE TABLE errlog (</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>id</code>&nbsp; 
      <code>INT</code>&nbsp; 
      <code>PRIMARY KEY auto_increment,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>err_cmd CHAR (</code> 
      <code>64</code> 
      <code>),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>err_time datetime</code> 
     </div> 
     <div> 
      <code>);</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>delimiter $$&nbsp;&nbsp;</code> 
      <code># 将mysql默认的结束符由;换成$$</code> 
     </div> 
     <div> 
      <code>create trigger tri_after_insert_cmd after insert on cmd&nbsp;</code> 
      <code>for</code>&nbsp; 
      <code>each row</code> 
     </div> 
     <div> 
      <code>begin</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>if</code>&nbsp; 
      <code>NEW.success&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'no'</code>&nbsp; 
      <code>then&nbsp;&nbsp;</code> 
      <code># 新记录都会被MySQL封装成NEW对象</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>end&nbsp;</code> 
      <code>if</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>end $$</code> 
     </div> 
     <div> 
      <code>delimiter ;&nbsp;&nbsp;</code> 
      <code># 结束之后记得再改回来，不然后面结束符就都是$$了</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志</code> 
     </div> 
     <div> 
      <code>INSERT INTO cmd (</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>USER,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>priv,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>cmd,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>sub_time,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>success</code> 
     </div> 
     <div> 
      <code>)</code> 
     </div> 
     <div> 
      <code>VALUES</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>(</code> 
      <code>'egon'</code> 
      <code>,</code> 
      <code>'0755'</code> 
      <code>,</code> 
      <code>'ls -l /etc'</code> 
      <code>,NOW(),</code> 
      <code>'yes'</code> 
      <code>),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>(</code> 
      <code>'egon'</code> 
      <code>,</code> 
      <code>'0755'</code> 
      <code>,</code> 
      <code>'cat /etc/passwd'</code> 
      <code>,NOW(),</code> 
      <code>'no'</code> 
      <code>),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>(</code> 
      <code>'egon'</code> 
      <code>,</code> 
      <code>'0755'</code> 
      <code>,</code> 
      <code>'useradd xxx'</code> 
      <code>,NOW(),</code> 
      <code>'no'</code> 
      <code>),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>(</code> 
      <code>'egon'</code> 
      <code>,</code> 
      <code>'0755'</code> 
      <code>,</code> 
      <code>'ps aux'</code> 
      <code>,NOW(),</code> 
      <code>'yes'</code> 
      <code>);</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code># 查询errlog表记录</code> 
     </div> 
     <div> 
      <code>select&nbsp;</code> 
      <code>*</code>&nbsp; 
      <code>from</code>&nbsp; 
      <code>errlog;</code> 
     </div> 
     <div> 
      <code># 删除触发器</code> 
     </div> 
     <div> 
      <code>drop trigger tri_after_insert_cmd;</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

## 事务 ##

#### 什么是事务 ####

开启一个事务可以包含一些sql语句，这些sql语句要么同时成功 要么一个都别想成功，称之为事务的原子性

#### 事务的作用 ####

保证了对数据操作的数据安全性

案例:用交行的卡操作建行ATM机给工商的账户转钱

**事务应该具有4个属性：**原子性、一致性、隔离性、持久性。这四个属性通常称为**ACID特性**。

原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。

一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

#### 如何用 ####

# 存储过程 #

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

#### 三种开发模型 ####

#### 第一种 ####

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>"""</code> 
     </div> 
     <div> 
      <code>应用程序：只需要开发应用程序的逻辑</code> 
     </div> 
     <div> 
      <code>mysql:编写好存储过程，以供应用程序调用</code> 
     </div> 
     <div> 
      <code>优点：开发效率，执行效率都高</code> 
     </div> 
     <div> 
      <code>缺点：考虑到人为因素、跨部门沟通等问题，会导致扩展性差</code> 
     </div> 
     <div> 
      <code>"""</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

#### 第二种 ####

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>"""</code> 
     </div> 
     <div> 
      <code>应用程序：除了开发应用程序的逻辑，还需要编写原生sql</code> 
     </div> 
     <div> 
      <code>优点：比方式1，扩展性高(非技术性的)</code> 
     </div> 
     <div> 
      <code>缺点：</code> 
     </div> 
     <div> 
      <code>1、开发效率，执行效率都不如方式1</code> 
     </div> 
     <div> 
      <code>2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题</code> 
     </div> 
     <div> 
      <code>"""</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

#### 第三种 ####

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>"""</code> 
     </div> 
     <div> 
      <code>应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM</code> 
     </div> 
     <div> 
      <code>优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处</code> 
     </div> 
     <div> 
      <code>缺点：执行效率连方式2都比不过</code> 
     </div> 
     <div> 
      <code>"""</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

#### 创建存储过程 ####

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>delimiter $$</code> 
     </div> 
     <div> 
      <code>create procedure p1(</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>in</code>&nbsp; 
      <code>m&nbsp;</code> 
      <code>int</code> 
      <code>,&nbsp;&nbsp;</code> 
      <code># in表示这个参数必须只能是传入不能被返回出去</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>in</code>&nbsp; 
      <code>n&nbsp;</code> 
      <code>int</code> 
      <code>,&nbsp;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>out res&nbsp;</code> 
      <code>int</code>&nbsp;&nbsp; 
      <code># out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去</code> 
     </div> 
     <div> 
      <code>)</code> 
     </div> 
     <div> 
      <code>begin</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>select tname&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>teacher where tid &gt; m&nbsp;</code> 
      <code>and</code>&nbsp; 
      <code>tid &lt; n;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>set</code>&nbsp; 
      <code>res</code> 
      <code>=</code> 
      <code>0</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>end $$</code> 
     </div> 
     <div> 
      <code>delimiter ;</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

#### 如何用存储过程 ####

## 函数 ##

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

参考博客：[http://www.cnblogs.com/linhaifeng/articles/7495918.html\#\_label2][http_www.cnblogs.com_linhaifeng_articles_7495918.html_label2]

## 流程控制 ##

## 索引与慢查询优化 ##

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

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

*  primary key
 *  unique key
 *  index key

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

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

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

**索引的影响:**

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

#### b+树 ####

[https://images2017.cnblogs.com/blog/1036857/201709/1036857-20170912011123500-158121126.png][https_images2017.cnblogs.com_blog_1036857_201709_1036857-20170912011123500-158121126.png]

只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据

查询次数由树的层级决定，层级越低次数越少

一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项

思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段

#### 聚集索引(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字段，所以还需要利用主键才去查找

## 测试索引 ##

**准备**

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> 
    <div>
      14 
    </div> 
    <div>
      15 
    </div> 
    <div>
      16 
    </div> 
    <div>
      17 
    </div> 
    <div>
      18 
    </div> 
    <div>
      19 
    </div> 
    <div>
      20 
    </div> 
    <div>
      21 
    </div> 
    <div>
      22 
    </div> 
    <div>
      23 
    </div> 
    <div>
      24 
    </div> 
    <div>
      25 
    </div> 
    <div>
      26 
    </div> 
    <div>
      27 
    </div> 
    <div>
      28 
    </div> 
    <div>
      29 
    </div> 
    <div>
      30 
    </div> 
    <div>
      31 
    </div> 
    <div>
      32 
    </div> 
    <div>
      33 
    </div> 
    <div>
      34 
    </div> 
    <div>
      35 
    </div> 
    <div>
      36 
    </div> 
    <div>
      37 
    </div> 
    <div>
      38 
    </div> 
    <div>
      39 
    </div> 
    <div>
      40 
    </div> 
    <div>
      41 
    </div> 
    <div>
      42 
    </div> 
    <div>
      43 
    </div> 
    <div>
      44 
    </div> 
    <div>
      45 
    </div> 
    <div>
      46 
    </div> 
    <div>
      47 
    </div> 
    <div>
      48 
    </div> 
    <div>
      49 
    </div> 
    <div>
      50 
    </div> 
    <div>
      51 
    </div> 
    <div>
      52 
    </div> 
    <div>
      53 
    </div> 
    <div>
      54 
    </div> 
    <div>
      55 
    </div> 
    <div>
      56 
    </div> 
    <div>
      57 
    </div> 
    <div>
      58 
    </div> 
    <div>
      59 
    </div> 
    <div>
      60 
    </div> 
    <div>
      61 
    </div> 
    <div>
      62 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code># 表没有任何索引的情况下</code> 
     </div> 
     <div> 
      <code>select&nbsp;</code> 
      <code>*</code>&nbsp; 
      <code>from</code>&nbsp; 
      <code>s1 where&nbsp;</code> 
      <code>id</code> 
      <code>=</code> 
      <code>30000</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code># 避免打印带来的时间损耗</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where&nbsp;</code> 
      <code>id</code>&nbsp; 
      <code>=</code>&nbsp; 
      <code>30000</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where&nbsp;</code> 
      <code>id</code>&nbsp; 
      <code>=</code>&nbsp; 
      <code>1</code> 
      <code>;</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code># 给id做一个主键</code> 
     </div> 
     <div> 
      <code>alter table s1 add primary key(</code> 
      <code>id</code> 
      <code>);&nbsp;&nbsp;</code> 
      <code># 速度很慢</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where&nbsp;</code> 
      <code>id</code>&nbsp; 
      <code>=</code>&nbsp; 
      <code>1</code> 
      <code>;&nbsp;&nbsp;</code> 
      <code># 速度相较于未建索引之前两者差着数量级</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'jason'</code>&nbsp;&nbsp; 
      <code># 速度仍然很慢</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>"""</code> 
     </div> 
     <div> 
      <code>范围问题</code> 
     </div> 
     <div> 
      <code>"""</code> 
     </div> 
     <div> 
      <code># 并不是加了索引，以后查询的时候按照这个字段速度就一定快&nbsp;&nbsp;</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where&nbsp;</code> 
      <code>id</code>&nbsp; 
      <code>&gt;&nbsp;</code> 
      <code>1</code> 
      <code>;&nbsp;&nbsp;</code> 
      <code># 速度相较于id = 1慢了很多</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where&nbsp;</code> 
      <code>id</code>&nbsp; 
      <code>&gt;</code> 
      <code>1</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>id</code>&nbsp; 
      <code>&lt;&nbsp;</code> 
      <code>3</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where&nbsp;</code> 
      <code>id</code>&nbsp; 
      <code>&gt;&nbsp;</code> 
      <code>1</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>id</code>&nbsp; 
      <code>&lt;&nbsp;</code> 
      <code>10000</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where&nbsp;</code> 
      <code>id</code>&nbsp; 
      <code>!</code> 
      <code>=</code>&nbsp; 
      <code>3</code> 
      <code>;</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>alter table s1 drop primary key;&nbsp;&nbsp;</code> 
      <code># 删除主键 单独再来研究name字段</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'jason'</code> 
      <code>;&nbsp;&nbsp;</code> 
      <code># 又慢了</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>create index idx_name on s1(name);&nbsp;&nbsp;</code> 
      <code># 给s1表的name字段创建索引</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'jason'</code>&nbsp;&nbsp; 
      <code># 仍然很慢！！！</code> 
     </div> 
     <div> 
      <code>"""</code> 
     </div> 
     <div> 
      <code>再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分</code> 
     </div> 
     <div> 
      <code>那这个树其实就建成了“一根棍子”</code> 
     </div> 
     <div> 
      <code>"""</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'xxx'</code> 
      <code>;&nbsp;</code> 
     </div> 
     <div> 
      <code># 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name like&nbsp;</code> 
      <code>'xxx'</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name like&nbsp;</code> 
      <code>'xxx%'</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name like&nbsp;</code> 
      <code>'%xxx'</code> 
      <code>;&nbsp;&nbsp;</code> 
      <code># 慢 最左匹配特性</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code># 区分度低的字段不能建索引</code> 
     </div> 
     <div> 
      <code>drop index idx_name on s1;</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code># 给id字段建普通的索引</code> 
     </div> 
     <div> 
      <code>create index idx_id on s1(</code> 
      <code>id</code> 
      <code>);</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where&nbsp;</code> 
      <code>id</code>&nbsp; 
      <code>=</code>&nbsp; 
      <code>3</code> 
      <code>;&nbsp;&nbsp;</code> 
      <code># 快了</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where&nbsp;</code> 
      <code>id</code> 
      <code>*</code> 
      <code>12</code>&nbsp; 
      <code>=</code>&nbsp; 
      <code>3</code> 
      <code>;&nbsp;&nbsp;</code> 
      <code># 慢了&nbsp; 索引的字段一定不要参与计算</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>drop index idx_id on s1;</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name</code> 
      <code>=</code> 
      <code>'jason'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>gender&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'male'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>id</code>&nbsp; 
      <code>=</code>&nbsp; 
      <code>3</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>email&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'xxx'</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code># 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件</code> 
     </div> 
     <div> 
      <code>create index idx_name on s1(name);</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name</code> 
      <code>=</code> 
      <code>'jason'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>gender&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'male'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>id</code>&nbsp; 
      <code>=</code>&nbsp; 
      <code>3</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>email&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'xxx'</code> 
      <code>;&nbsp;&nbsp;</code> 
      <code># 并没有加速</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>drop index idx_name on s1;</code> 
     </div> 
     <div> 
      <code># 给name，gender这种区分度不高的字段加上索引并不难加快查询速度</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>create index idx_id on s1(</code> 
      <code>id</code> 
      <code>);</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name</code> 
      <code>=</code> 
      <code>'jason'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>gender&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'male'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>id</code>&nbsp; 
      <code>=</code>&nbsp; 
      <code>3</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>email&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'xxx'</code> 
      <code>;&nbsp;&nbsp;</code> 
      <code># 快了&nbsp; 先通过id已经讲数据快速锁定成了一条了</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name</code> 
      <code>=</code> 
      <code>'jason'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>gender&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'male'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>id</code>&nbsp; 
      <code>&gt;&nbsp;</code> 
      <code>3</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>email&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'xxx'</code> 
      <code>;&nbsp;&nbsp;</code> 
      <code># 慢了&nbsp; 基于id查出来的数据仍然很多，然后还要去比较其他字段</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>drop index idx_id on s1</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>create index idx_email on s1(email);</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name</code> 
      <code>=</code> 
      <code>'jason'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>gender&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'male'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>id</code>&nbsp; 
      <code>&gt;&nbsp;</code> 
      <code>3</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>email&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'xxx'</code> 
      <code>;&nbsp;&nbsp;</code> 
      <code># 快 通过email字段一剑封喉</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name</code> 
      <code>=</code> 
      <code>'jason'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>gender&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'male'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>id</code>&nbsp; 
      <code>&gt;&nbsp;</code> 
      <code>3</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>email&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'xxx'</code> 
      <code>;&nbsp;</code> 
     </div> 
     <div> 
      <code># 如果上述四个字段区分度都很高，那给谁建都能加速查询</code> 
     </div> 
     <div> 
      <code># 给email加然而不用email字段</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name</code> 
      <code>=</code> 
      <code>'jason'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>gender&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'male'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>id</code>&nbsp; 
      <code>&gt;&nbsp;</code> 
      <code>3</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code># 给name加然而不用name字段</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where gender&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'male'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>id</code>&nbsp; 
      <code>&gt;&nbsp;</code> 
      <code>3</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code># 给gender加然而不用gender字段</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where&nbsp;</code> 
      <code>id</code>&nbsp; 
      <code>&gt;&nbsp;</code> 
      <code>3</code> 
      <code>;</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code># 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间</code> 
     </div> 
     <div> 
      <code>create index idx_all on s1(email,name,gender,</code> 
      <code>id</code> 
      <code>);&nbsp;&nbsp;</code> 
      <code># 最左匹配原则，区分度高的往左放</code> 
     </div> 
     <div> 
      <code>select count(</code> 
      <code>id</code> 
      <code>)&nbsp;</code> 
      <code>from</code>&nbsp; 
      <code>s1 where name</code> 
      <code>=</code> 
      <code>'jason'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>gender&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'male'</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>id</code>&nbsp; 
      <code>&gt;&nbsp;</code> 
      <code>3</code>&nbsp; 
      <code>and</code>&nbsp; 
      <code>email&nbsp;</code> 
      <code>=</code>&nbsp; 
      <code>'xxx'</code> 
      <code>;&nbsp;&nbsp;</code> 
      <code># 速度变快</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

总结:上面这些操作，你感兴趣可以敲一敲，不感兴趣你就可以不用敲了，权当看个乐呵。理论掌握了就行了

慢查询日志

设定一个时间检测所有超出改时间的sql语句，然后针对性的进行优化

转载于:https://www.cnblogs.com/tulintao/p/11407909.html

[http_www.cnblogs.com_linhaifeng_articles_7495918.html_label2]: http://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2
[https_images2017.cnblogs.com_blog_1036857_201709_1036857-20170912011123500-158121126.png]: https://images2017.cnblogs.com/blog/1036857/201709/1036857-20170912011123500-158121126.png