当前位置：Gxlcms > 数据库问题 > python 之 数据库（内置函数、流程控制、索引）

python 之 数据库（内置函数、流程控制、索引）

时间：2021-07-01 10:21:17 帮助过：5人阅读

强调：mysql内置的函数只能在sql语句中使用

  1. <span style="color: #000000;">#数学函数
  2. </span><span style="color: #ff00ff;">round</span><span style="color: #000000;">(x,y)      #返回参数x的四舍五入的有y位小数的值
  3. </span><span style="color: #ff00ff;">rand</span><span style="color: #000000;">()          #返回０到１内的随机值,可以通过提供一个参数(种子)使rand()随机数生成器生成一个指定的值。
  4. ?
  5. #加密函数
  6. md5()           #计算字符串str的MD5校验和
  7. password(</span><span style="color: #ff00ff;">str</span>)   #返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。

date_format:

  1. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> blog (
  2.     id </span><span style="color: #0000ff;">int</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;"> auto_increment,
  3.     name </span><span style="color: #0000ff;">char</span> (<span style="color: #800000; font-weight: bold;">32</span><span style="color: #000000;">),
  4.     sub_time </span><span style="color: #0000ff;">datetime</span><span style="color: #000000;">);
  5. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> blog (name, sub_time) <span style="color: #0000ff;">values</span><span style="color: #000000;">
  6. (</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">第1篇</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2015-03-01 11:31:21</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
  7. (</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">第2篇</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2015-03-11 16:31:21</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
  8. (</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">第3篇</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2017-05-01 18:31:21</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  9. </span><span style="color: #0000ff;">select</span> date_format(sub_time,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">%Y-%m</span><span style="color: #ff0000;">‘</span>),<span style="color: #ff00ff;">count</span>(id) <span style="color: #0000ff;">from</span> blog <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> date_format(sub_time,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">%Y-%m</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  10. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----------------------------+-----------+</span>
  11. <span style="color: #808080;">|</span> date_format(sub_time,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">%Y-%m</span><span style="color: #ff0000;">‘</span>) <span style="color: #808080;">|</span> <span style="color: #ff00ff;">count</span>(id) <span style="color: #808080;">|</span>
  12. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----------------------------+-----------+</span>
  13. <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2015</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">03</span>                       <span style="color: #808080;">|</span>         <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span>
  14. <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">2017</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">05</span>                       <span style="color: #808080;">|</span>         <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">|</span>
  15. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----------------------------+-----------+</span>

10.171 自定义函数

函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能,若要想在begin...end...中写sql，请用存储过程.

  1. delimiter <span style="color: #808080;">//</span>
  2. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">function</span><span style="color: #000000;"> f1(
  3.     i1 </span><span style="color: #0000ff;">int</span><span style="color: #000000;">,
  4.     i2 </span><span style="color: #0000ff;">int</span><span style="color: #000000;">)
  5. </span><span style="color: #0000ff;">returns</span> <span style="color: #0000ff;">int</span>
  6. <span style="color: #0000ff;">BEGIN</span>
  7.     <span style="color: #0000ff;">declare</span> num <span style="color: #0000ff;">int</span><span style="color: #000000;">;
  8.     </span><span style="color: #0000ff;">set</span> num <span style="color: #808080;">=</span> i1 <span style="color: #808080;">+</span><span style="color: #000000;"> i2;
  9.     </span><span style="color: #0000ff;">return</span><span style="color: #000000;">(num);
  10. </span><span style="color: #0000ff;">END</span> <span style="color: #808080;">//</span><span style="color: #000000;">
  11. delimiter ;</span>

删除函数：

  1. <span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">function</span> func_name;

执行函数：

  1. <span style="color: #000000;"># 获取返回值
  2. </span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">UPPER</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">egon</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">into</span> <span style="color: #008000;">@res</span><span style="color: #000000;">;
  3. </span><span style="color: #0000ff;">SELECT</span> <span style="color: #008000;">@res</span><span style="color: #000000;">;
  4. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+</span>
  5. <span style="color: #808080;">|</span> <span style="color: #008000;">@res</span> <span style="color: #808080;">|</span>
  6. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+</span>
  7. <span style="color: #808080;">|</span> EGON <span style="color: #808080;">|</span>
  8. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">----+</span>
  9. <span style="color: #000000;"># 在查询中使用
  10. </span><span style="color: #0000ff;">select</span> f1(<span style="color: #800000; font-weight: bold;">11</span>,nid) ,name <span style="color: #0000ff;">from</span> tb2;

10.18 流程控制

if条件语句:

  1. delimiter <span style="color: #808080;">//</span>
  2. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">function</span> f5(i <span style="color: #0000ff;">int</span><span style="color: #000000;">)
  3. </span><span style="color: #0000ff;">returns</span> <span style="color: #0000ff;">int</span>
  4. <span style="color: #0000ff;">begin</span>
  5.     <span style="color: #0000ff;">declare</span> res <span style="color: #0000ff;">int</span> <span style="color: #0000ff;">default</span> <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">;
  6.     </span><span style="color: #0000ff;">if</span> i <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">10</span> <span style="color: #0000ff;">then</span>
  7.         <span style="color: #0000ff;">set</span> res<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">;
  8.     elseif i </span><span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">20</span> <span style="color: #0000ff;">then</span>
  9.         <span style="color: #0000ff;">set</span> res<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">200</span><span style="color: #000000;">;
  10.     elseif i </span><span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">30</span> <span style="color: #0000ff;">then</span>
  11.         <span style="color: #0000ff;">set</span> res<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">300</span><span style="color: #000000;">;
  12.     </span><span style="color: #0000ff;">else</span>
  13.         <span style="color: #0000ff;">set</span> res<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">400</span><span style="color: #000000;">;
  14.     </span><span style="color: #0000ff;">end</span> <span style="color: #0000ff;">if</span><span style="color: #000000;">;
  15.     </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> res;
  16. </span><span style="color: #0000ff;">end</span> <span style="color: #808080;">//</span><span style="color: #000000;">
  17. delimiter ;</span>

控制流函数：

  1. <span style="color: #0000ff;">select</span> 
  2. <span style="color: #ff00ff;">case</span>
  3.     <span style="color: #0000ff;">when</span> name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">egon</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">then</span><span style="color: #000000;"> name
  4.     </span><span style="color: #0000ff;">when</span> name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">alex</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">then</span> concat(name,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">_BIGSB</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  5.     </span><span style="color: #0000ff;">else</span> concat(name,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">_SB</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  6. </span><span style="color: #0000ff;">end</span>
  7. <span style="color: #0000ff;">from</span> emp;

循环语句:

  1. delimiter <span style="color: #808080;">//</span>
  2. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">procedure</span><span style="color: #000000;"> proc_while ()
  3. </span><span style="color: #0000ff;">begin</span>
  4.     <span style="color: #0000ff;">declare</span> num <span style="color: #0000ff;">int</span><span style="color: #000000;"> ;
  5.     </span><span style="color: #0000ff;">set</span> num <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;"> ;
  6.     </span><span style="color: #0000ff;">while</span> num <span style="color: #808080;"><</span> <span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;"> do
  7.         </span><span style="color: #0000ff;">select</span><span style="color: #000000;">
  8.             num ;
  9.         </span><span style="color: #0000ff;">set</span> num <span style="color: #808080;">=</span> num <span style="color: #808080;">+</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;"> ;
  10.     </span><span style="color: #0000ff;">end</span> <span style="color: #0000ff;">while</span><span style="color: #000000;"> ;
  11. </span><span style="color: #0000ff;">end</span> <span style="color: #808080;">//</span><span style="color: #000000;">
  12. delimiter ;</span>

10.19 索引

索引就相当于书的目录，是mysql中一种专门的数据结构，称为key， 索引的本质原理就是通过不断地缩小查询范围，来降低 i/o 次数从而提升查询性能，一旦为表创建了索引，以后的查询都会先查索引，再根据索引定位的结果去找数据

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

聚集索引(primary key)：就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。特点：叶子节点存放的一整条数据

聚集索引的优点：1.它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录 2.范围查询(range query),即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

辅助索引(unique，index):非聚集索引即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。 特点：辅助索引的叶子节点不包含行记录的全部数据。如果是按照name字段创建的索引，那么叶子节点存放的是：{name：名字所在那条记录的主键的值}

每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作,InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。

  1. <span style="color: #000000;">#覆盖索引：在辅助索引的叶子节点中就已经找到了所有我们想要的数据
  2. </span><span style="color: #0000ff;">select</span> name <span style="color: #0000ff;">from</span> <span style="color: #ff00ff;">user</span> <span style="color: #0000ff;">where</span> name<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">egon</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
  3. #非覆盖索引：命中辅助索引，但是未覆盖索引，还需要从聚集索引中查找age
  4. </span><span style="color: #0000ff;">select</span> age <span style="color: #0000ff;">from</span> <span style="color: #ff00ff;">user</span> <span style="color: #0000ff;">where</span> name<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">egon</span><span style="color: #ff0000;">‘</span>;

联合索引:联合索引指对表上的多个列合起来做一个索引,在第一个键相同的情况下，已经对第二个键进行了排序处理

mysql中的primary key，unique，index ,联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

  1. 普通索引 <span style="color: #0000ff;">index</span><span style="color: #000000;">：加速查找
  2. 唯一索引：
  3.     </span><span style="color: #808080;">-</span>主键索引 <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span>：加速查找<span style="color: #808080;">+</span>约束（<span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span>  <span style="color: #808080;">+</span> <span style="color: #0000ff;">unique</span><span style="color: #000000;">）
  4.     </span><span style="color: #808080;">-</span>唯一索引 <span style="color: #0000ff;">unique</span>:加速查找<span style="color: #808080;">+</span><span style="color: #000000;">约束（不能重复）
  5. 联合索引：
  6.     </span><span style="color: #808080;">-</span><span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span>(id,name):联合主键索引，加速查找<span style="color: #808080;">+</span><span style="color: #000000;">约束
  7.     </span><span style="color: #808080;">-</span><span style="color: #0000ff;">unique</span>(id,name):联合唯一索引，加速查找<span style="color: #808080;">+</span><span style="color: #000000;">约束
  8.     </span><span style="color: #808080;">-</span><span style="color: #0000ff;">index</span>(id,name):联合普通索引

innodb:聚集索引，辅助索引 myisam:辅助索引

10.191 创建和删除索引

  1. <span style="color: #000000;">#方式一
  2. </span><span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> t1(
  3.     id </span><span style="color: #0000ff;">int</span><span style="color: #000000;">,
  4.     name </span><span style="color: #0000ff;">char</span><span style="color: #000000;">,
  5.     age </span><span style="color: #0000ff;">int</span><span style="color: #000000;">,
  6.     sex enum(</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">male</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">female</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),
  7.     </span><span style="color: #0000ff;">unique</span> <span style="color: #0000ff;">key</span><span style="color: #000000;"> uni_id(id),
  8.     </span><span style="color: #0000ff;">index</span><span style="color: #000000;"> ix_name(name)         #index没有key
  9. );
  10. #方式二
  11. </span><span style="color: #0000ff;">create</span> <span style="color: #0000ff;">index</span> ix_age <span style="color: #0000ff;">on</span><span style="color: #000000;"> t1(age);
  12. #方式三
  13. </span><span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span> t1 <span style="color: #0000ff;">add</span> <span style="color: #0000ff;">index</span><span style="color: #000000;"> ix_sex(sex);
  14. </span><span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span> t1 <span style="color: #0000ff;">add</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">(id);
  15. #联合索引
  16. </span><span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span> t1 <span style="color: #0000ff;">add</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">(id，name);
  17. </span><span style="color: #0000ff;">create</span> <span style="color: #0000ff;">index</span> ix_age <span style="color: #0000ff;">on</span><span style="color: #000000;"> t1(id，age);
  18. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+-----------------------+------+-----+---------+-------+</span>
  19. <span style="color: #808080;">|</span> Field <span style="color: #808080;">|</span> Type                  <span style="color: #808080;">|</span> <span style="color: #0000ff;">Null</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Key</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Default</span> <span style="color: #808080;">|</span> Extra <span style="color: #808080;">|</span>
  20. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+-----------------------+------+-----+---------+-------+</span>
  21. <span style="color: #808080;">|</span> id    <span style="color: #808080;">|</span> <span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>)               <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span> UNI <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span>
  22. <span style="color: #808080;">|</span> name  <span style="color: #808080;">|</span> <span style="color: #0000ff;">char</span>(<span style="color: #800000; font-weight: bold;">1</span>)               <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span> MUL <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span>
  23. <span style="color: #808080;">|</span> age   <span style="color: #808080;">|</span> <span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>)               <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span> MUL <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span>
  24. <span style="color: #808080;">|</span> sex   <span style="color: #808080;">|</span> enum(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">male</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">female</span><span style="color: #ff0000;">‘</span>) <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span> MUL <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span>
  25. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+-----------------------+------+-----+---------+-------+</span>
  26. <span style="color: #000000;">#删除索引：
  27. </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">index</span> idx_name <span style="color: #0000ff;">on</span><span style="color: #000000;"> t1;
  28. </span><span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span> t1 <span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">;
  29. ?
  30. and工作原理：对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询
  31. or的工作原理：对于连续多个or：mysql会按照条件的顺序，从左到右依次判断,此时应该按照区分度高低设置联合索引</span>

10.192 正确使用索引

10.1921 索引未命中

并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题

技术图片 
  1. <span style="color: #800000; font-weight: bold;">1</span> 范围问题，或者说条件不明确，条件中出现这些符号或关键字：<span style="color: #808080;">></span>、<span style="color: #808080;">>=</span>、<span style="color: #808080;"><</span>、<span style="color: #808080;"><=</span>、<span style="color: #808080;">!=</span> 、<span style="color: #808080;">between</span>...<span style="color: #808080;">and</span>...、<span style="color: #808080;">like</span><span style="color: #000000;">、
  2. </span><span style="color: #800000; font-weight: bold;">2</span> 尽量选择区分度高的列作为索引,区分度的公式是count(<span style="color: #0000ff;">distinct</span> col)<span style="color: #808080;">/</span><span style="color: #ff00ff;">count</span>(<span style="color: #808080;">*</span><span style="color: #000000;">)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
  3. </span><span style="color: #800000; font-weight: bold;">3</span> <span style="color: #808080;">=</span>和in可以乱序，比如a <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">and</span> b <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">and</span> c <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">3</span><span style="color: #000000;"> 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
  4. </span><span style="color: #800000; font-weight: bold;">4</span> 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) <span style="color: #808080;">=</span> ’<span style="color: #800000; font-weight: bold;">2014</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">05</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">29</span>’就不能使用到索引，原因很简单，b<span style="color: #808080;">+</span>树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time <span style="color: #808080;">=</span> unix_timestamp(’<span style="color: #800000; font-weight: bold;">2014</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">05</span><span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">29</span><span style="color: #000000;">’)
  5. </span><span style="color: #800000; font-weight: bold;">5</span> <span style="color: #808080;">and/or</span><span style="color: #000000;">
  6. #</span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">、and与or的逻辑
  7.     条件1 </span><span style="color: #808080;">and</span><span style="color: #000000;"> 条件2:所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
  8.     条件1 </span><span style="color: #808080;">or</span><span style="color: #000000;"> 条件2:只要有一个条件成立则最终结果就成立
  9. #</span><span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">、and的工作原理
  10.     条件：
  11.         a </span><span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">10</span> <span style="color: #808080;">and</span> b <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">xxx</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">and</span> c <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">3</span> <span style="color: #808080;">and</span> d <span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">4</span><span style="color: #000000;">
  12.     索引：
  13.         制作联合索引(d,a,b,c)
  14.     工作原理:
  15.         对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—</span><span style="color: #808080;">></span>a<span style="color: #808080;">-></span>b<span style="color: #808080;">-></span><span style="color: #000000;">c的顺序
  16. #</span><span style="color: #800000; font-weight: bold;">3</span><span style="color: #000000;">、or的工作原理
  17.     条件：
  18.         a </span><span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">10</span> <span style="color: #808080;">or</span> b <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">xxx</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">or</span> c <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">3</span> <span style="color: #808080;">or</span> d <span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">4</span><span style="color: #000000;">
  19.     索引：
  20.         制作联合索引(d,a,b,c)       
  21.     工作原理:
  22.         对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a</span><span style="color: #808080;">-></span>b<span style="color: #808080;">-></span>c<span style="color: #808080;">-></span><span style="color: #000000;">d,所以应该将区分度高的放在前面
  23.  </span><span style="color: #800000; font-weight: bold;">6</span> 最左前缀匹配原则，非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(<span style="color: #808080;">></span>、<span style="color: #808080;"><</span>、<span style="color: #808080;">between</span>、<span style="color: #808080;">like</span>)就停止匹配(指的是范围大了，有索引速度也慢)，比如 a <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">and</span> b <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">and</span> c <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">3</span> <span style="color: #808080;">and</span> d <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">4</span><span style="color: #000000;"> 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。所以建立索引时将关于范围比较的字段放在后面
  24. </span><span style="color: #800000; font-weight: bold;">7</span><span style="color: #000000;"> 其他情况
  25. </span><span style="color: #808080;">-</span><span style="color: #000000;"> 使用函数
  26.     </span><span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> tb1 <span style="color: #0000ff;">where</span> <span style="color: #ff00ff;">reverse</span>(email) <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">egon</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;           
  27. </span><span style="color: #808080;">-</span><span style="color: #000000;"> 类型不一致
  28.     如果列是字符串类型，传入条件是必须用引号引起来，不然...
  29.     </span><span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> tb1 <span style="color: #0000ff;">where</span> email <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">999</span><span style="color: #000000;">;  
  30. #排序条件为索引，则select字段必须也是索引字段，否则无法命中
  31. </span><span style="color: #808080;">-</span> <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span>
  32.     <span style="color: #0000ff;">select</span> name <span style="color: #0000ff;">from</span> s1 <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> email <span style="color: #0000ff;">desc</span><span style="color: #000000;">;
  33.     当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
  34.     </span><span style="color: #0000ff;">select</span> email <span style="color: #0000ff;">from</span> s1 <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> email <span style="color: #0000ff;">desc</span><span style="color: #000000;">;
  35.     特别的：如果对主键排序，则还是速度很快：
  36.     </span><span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> tb1 <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> nid <span style="color: #0000ff;">desc</span><span style="color: #000000;">;
  37. </span><span style="color: #808080;">-</span><span style="color: #000000;"> 组合索引最左前缀
  38.     如果组合索引为：(name,email)
  39.     name </span><span style="color: #808080;">and</span> email       <span style="color: #008080;">--</span><span style="color: #008080;"> 命中索引</span>
  40.     name                 <span style="color: #008080;">--</span><span style="color: #008080;"> 命中索引</span>
  41.     email                <span style="color: #008080;">--</span><span style="color: #008080;"> 未命中索引</span>
  42. <span style="color: #808080;">-</span> <span style="color: #ff00ff;">count</span>(<span style="color: #800000; font-weight: bold;">1</span>)或count(列)代替count(<span style="color: #808080;">*</span><span style="color: #000000;">)在mysql中没有差别了
  43. </span><span style="color: #808080;">-</span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">index</span> xxxx  <span style="color: #0000ff;">on</span> tb(title(<span style="color: #800000; font-weight: bold;">19</span>)) #text类型，必须制定长度
索引未命中
10.1922 其他注意事项
  1. <span style="color: #800000; font-weight: bold;">1</span> 避免使用select <span style="color: #808080;">*</span>
  2. <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #ff00ff;">count</span>(<span style="color: #800000; font-weight: bold;">1</span>)或count(列) 代替 <span style="color: #ff00ff;">count</span>(<span style="color: #808080;">*</span><span style="color: #000000;">)
  3. </span><span style="color: #800000; font-weight: bold;">3</span> 创建表时尽量时 <span style="color: #0000ff;">char</span> 代替 <span style="color: #0000ff;">varchar</span>
  4. <span style="color: #800000; font-weight: bold;">4</span><span style="color: #000000;"> 表的字段顺序固定长度的字段优先
  5. </span><span style="color: #800000; font-weight: bold;">5</span><span style="color: #000000;"> 组合索引代替多个单列索引（经常使用多个条件查询时）
  6. </span><span style="color: #800000; font-weight: bold;">6</span><span style="color: #000000;"> 尽量使用短索引
  7. </span><span style="color: #800000; font-weight: bold;">7</span> 使用连接（<span style="color: #808080;">JOIN</span>）来代替子查询(Sub<span style="color: #808080;">-</span><span style="color: #000000;">Queries)
  8. </span><span style="color: #800000; font-weight: bold;">8</span><span style="color: #000000;"> 连表时注意条件类型需一致
  9. </span><span style="color: #800000; font-weight: bold;">9</span> 索引散列值不适合建索引，例：性别不适合

总结：

  1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引

  2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快

  3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

10.193 慢查询优化的基本步骤

  1. <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
  2. </span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
  3. </span><span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
  4. </span><span style="color: #800000; font-weight: bold;">3</span>.<span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span><span style="color: #000000;"> limit 形式的sql语句让排序的表优先查
  5. </span><span style="color: #800000; font-weight: bold;">4</span><span style="color: #000000;">.了解业务方使用场景
  6. </span><span style="color: #800000; font-weight: bold;">5</span><span style="color: #000000;">.加索引时参照建索引的几大原则
  7. </span><span style="color: #800000; font-weight: bold;">6</span>.观察结果，不符合预期继续从0分析

python 之 数据库（内置函数、流程控制、索引）

标签：varchar   case   使用   extra   cli   语句   ima   符号   还需要   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！