Database(三) mysql常用操作

2020-05-09 本文已影响0人 joshuaXin

一：简介

主要想写一下常见的一些操作的原理，比如order by、group by、join、union等等；

二：排序 order by

1.对数据进行排序是一种常见的操作，用于将更关心的数据放到最前面；排序若是用的不够好，也常带来很差的效率，比如filesort等；当然在这里索引也是起到了至关重要的作用，比如排序时，排序的字段是索引、覆盖索引等情况；
2.排序的三种算法，排序字段为col，查询字段为cola、colb、colc：
1）将符合条件的数据，组合成(primary,col)的样式，然后放入sort buffer中去排序，若是sort buffer不能放下，则使用file sort进行归并排序；最后再根据排序后的(primary,col)，去DB里面找(cola,colb,colc)；
2）优化排序：将符合条件的数据组织成(cola,colb,colc,col)的形式，然后再根据col进行排序；
3）针对limit进行优化：5.6 版本针对 Order by limit M，N 进行优化，采用堆来进行优化；
3.排序的优化：索引
在查询的SQL中，效率问题很难离开索引，这里利用索引来优化的方式有两种：
1）排序字段(col1,col2,col3)是一个索引，并且顺序保持最左匹配，这样就可以利用索引的有序性；
2）覆盖索引，select的字段(cola,colb,colc)是一个索引，这样排序后就不用再次回表；
4. sort_buffer_size参数：
该参数是排序Buffer的大小，由上面可知，当Buffer小于需要排序的内容时，就会文件排序；但该参数是connection级别的参数，也就是说增大该参数后，所有的连接的buffer都会增大，这会很耗内存；
5.总结
order by的情况有很多，优化的思路也很多，比如调参、减少不必要的参数、索引、排序的顺序ASC、DESC等，在这里只能简要介绍下，如果需要调参，还需要更细的去看；

三：Group By

1.含义：按照规则对数据进行分组，并且常会和聚合函数、having一起使用；
性能：受索引的影响也很大，如果不能使用索引的话，只能使用全表扫描进行分组了；

SELECT column, group_function,...FROM table
[WHERE condition]
GROUP BY group_by_expression
[HAVING group_condition];

2.having 、where、order by、group by 、select的顺序和限制
掌握了执行的流程，这些就会顺其自然；
1）where 语句是最先执行的，用在过滤原始数据上面；这里再多说一下，对于组合索引(sid,cid)，select sid,cid from xxx而言, order by update_time的type是ALL，order by sid，type是index；就是说mysql引擎会根据语句的情况选择，会找到一个包含select、order by的记录；
2）其次执行 group by语句，即根据对应的字段进行分组；
3）利用having进行过滤，这里过滤的纬度是组，将不合适的组过滤掉；
4）select，从当前的记录集temp中选出对应的column，或者做一些聚合计算；
5）由于在第一步中，选择出来的记录集temp中是有order by信息的，虽然在第四步没有展示，这时候再根据字段排序；
3.使用group by时select列的限制
1）在5.7版本之前，select后面可以跟任何合法的列；
2）在oracle、sqlserver、db2和5.7之后，分组中select后面的列只能有2种：出现在group by后面的列、使用聚合函数的列；
3）ONLY_FULL_GROUP_BY表示严格限制group by的列。
4）如果不严格限制的问题：对应的数据是第一条数据，某种意义上是随机的，所以选择出来的字段的值也没有意义；
3.group by的三种形式：松散的索引、紧凑的索引、全表扫描
Group by的过程，其实是一个找到所有元素，然后进行分组的一个过程；那么就需要获得column的所有数据，然后再进行分组，再进行聚合等操作；所以索引很关键，分为用到索引、全表(临时表)两种，用到索引又可以细分：
1）松散的索引：是指分组时，可以直接利用索引，并且利用索引的排序，只访问索引中满足条件的，而不需要访问索引中的所有值；有点类似于explain中的ref类型；
要使用松散索引有一些条件，感兴趣的可以查找相关资料：见【参考】2
2）紧凑的索引：可以是全索引扫描或者是索引范围扫描，这种情况下用不到最左前缀的索引；
3）全表：这种情况下，可能会用到temporary或者filesort，所以会比较耗时，比较耗内存

四：distinct

1）功能：distinct是在查找的时候，某一列只存在一个值，这里和Group by有些共同之处，都是将其根据col来分组，distinct只取唯一的，group by会做一些聚合动作；
2）实现：distinct因为只需要取col字段，它会将全部内容存储在Hash结构里面，最后计算key的个数，是以空间换时间的方式，时间复杂度为；
3）Group by：因为Group是需要做聚合的，所以它需要先进行排序，然后再进行分组；
4）count distinct 、count group by效率对比：对不同的字段类型，效率各有优劣：首先数据越是离散，DISTINCT需要消耗的空间越大，效率也就越低，此时GROUP BY的空间优势就显现了；相反，数据越是集中，DISTINCT空间占用变小，时间优势就显现出来了

五：or、in、union

1. or用来求多个条件的合集，如 select id from student where grade > 90 or grade < 60;
2. union也可用来求多个条件的合集，其中union会对结果进行去重，union all不去重；
3. in也算是一种多条件的合集，比如select id from student where grade in (99,100);
4. or使用索引的情况，在Mysql的index merged出现之前，是不会使用索引的，所以在之前or的性能会比较差，我在项目中就遇到过一个or查询7秒钟的惊人SQL，改成union all之后，缩短到了100ms；
但是引入了index merged之后，会优先考虑索引，并在索引之后对条件进行过滤；
5. union 对于索引字段，是可以使用索引的，所以对于一些or不能使用索引的情况下，可以考虑使用union；
6. 对于简单的in查询，mysql是会走索引查询的，但是in条件过多的时候，索引就会失效，当然因为mysql版本众多，也不断的优化，实际还是要以explain的结果为准；

六：join

1. join是数据库表与表直接，进行连接取值的一种方式，分为inner 、outer、full join；
2. join和inner join是内连接，是取两个表的交集；
3.outer分为left和right： left join是取两个表的交集之后，加上左表剩余的数据，right与left类同；
4.full join全连接，mysql不支持，在join的基础上增加2表剩余的数据，缺少的用null补充；
5.连接操作的语法：

select <row_list> from <left_table>
<inner|left|right> join <right_table>
on <join condition> where <where_condition>

6. 这几种 join 的操作，都是在笛卡尔积的基础上，进行扩充和筛选的：
1）from ：对left_table和right_table进行笛卡尔积，生成vtable1，行数left*right；
2）on：根据 on 的条件，对vtable1进行过滤，然后生成vtable2；
3）join：根据join类型，对vtable2进行改造，inner不需要，outer需要将处理，比如left join需要将不存在于vtable2中的记录，插入vtable2中，生成vtable3，其他字段补null；
4）where：对vtable3进行过滤，生成vtable4；
7.原理：join的实现是用Nested Loop Join算法，通常以前表为驱动表，来作为过滤条件到下一个表的查询数据；
8.优化：
1）驱动表要选择数据量小的表；
2）对驱动表和被驱动表的字段，加上相应的索引；
3）如果被驱动表不能加索引，考虑加大join buffer size

七：exists、in、not exists、not in

1.exists和in都是集合查询的一种，用来判断某一行是都在集合之中；他们的实现原理是对表A的数据遍历，然后再去表B中去判断是否满足条件，它们的区别就在于怎么去表B；
2. 用法：select column from tableA where id in(exists) (select id from tableB);
3. in的场景：tableB的数据较小，在对表A遍历之后，对将表B的被人加载到内存，所以如果tableB太大的话，是很难加载到内存的；
4. exists场景：tableA的数据较小，因为遍历A之后，对表B进行exists，并不对exists进行内存加载；所以当tableA较大的时候，比较耗时；
5. not in 、not exists，尽量选择 not exists，因为 not in 很少走索引；

八：not、 != 、< 、> 、between

对于这些操作，也可能会使用多音，关键还是看数据量等因素，mysql会去选择，而不是不会使用索引；

九：limit

1.limit的常用用法为 limit offset rows，用来分页操作；
2.limit的原理：因为数据在磁盘上的存储是随机的，所以去磁盘读取数据是随机读取的：
1）从数据库表中读取M条数据，放入数据集中；(M主要受数据页等因素影响)
2）重复第一步，直到数据集中存在 offset + rows 条数据；
3）根据offset抛弃前offset条数据；
4）返回剩下的rows条数据；
3.limit的性能：由上可知，limit的性能受制于 offset + rows的数量，当offset越大，性能越差；
4.性能优化：主要思路有下面几种：对于 select id from table limit 10000，10
1）减少 offset的数目，比如，select id from table where id >= 10000 limit 10;
2) 对于非主键的，利用索引和子查询，先利用查出一部分主键，然后利用主键去查找剩下的数据，比如：
select * from table where id in (select id from table where (name = xxx )) limit 10000 ,10;
3）利用exists、join等对上述的in进行优化，解决in在数据量大的时候的效率问题；

十：主键

1. mysql的主键在记录的存储是非常重要的，因为mysql在磁盘上的存储是聚簇索引；
2. Innodb对于不存在主键的处理办法：会使用第一个非空的唯一索引作为聚簇索引，如果没有的话，Innodb会生成一个不可见的名为ROW_ID的列名为GEN_CLUSTER_INDEX的聚簇索引，它为一个6字节的自增数值；
在没有聚簇索引的情况下，会共享全局的序列，导致效率低下，所以一些DBA都会要求要有一个自增的Id作为聚簇索引；
3.自增Id：Innodb是在内存中存放下一个id，只有在重启的时候，才重新进行计算；MyIsam是将下一个Id放在物理文件中；

十一：子查询和join

1. 一个常见的优化原则是：使用join来代替子查询，连接的效率要好于子查询；
2.子查询：select column from table where id in (select goods_id from goods)
3.子查询效率慢的原因：子查询会创建、销毁临时表；
4.但事情也不是绝对的，类似于in和exist，join使用笛卡尔积，然后进行过滤，而子查询是对内存中的数据进行遍历，如果子查询的数量很少，还是不错的；
5.原则上，对可用join替换的，尽量用join替换子查询；

十一：count(*)、count(1)、count(column)

1、count(1) and count(*)
从执行计划上来说,两者是一致的，我这里的type都是index；但是count(*)是SQL的标准，也是做了优化的，所以应优先选择count(*)；
他们在语义上应该是一致的，应该都会选择一个开销比较小的索引，来计算数量；
2、count(1)、 count(column)
它们的区别是count(1)会统计表中所有的记录数，包含字段为null的记录，而count(column)只记录在表中出现的非null的次数；
他们的效率其实在column是索引的情况下，差距很小，因为使用索引，都是内存类的操作，在非索引的情况下，差距就大了

参考文章：

1.https://database.51cto.com/art/201710/555357.htm
2.https://www.jianshu.com/p/e5d962b13f44
3.https://juejin.im/post/5a449d3a518825519408eb1e
4.https://my.oschina.net/hosee/blog/1142926
5.https://www.jianshu.com/p/6864abb4d885
6.https://www.jianshu.com/p/efecd0b66c55