分类 Mysql 下的文章

Mysql的count函数用于统计符合条件的记录数,常用的方式有:

1、count(*)
2、count(1)
3、count(id)
4、count(col)

首先需要明确一点:count函数对于返回的结果集,一行行地判断,不会统计null值。

初学者经常会纠结到底应该使用哪种方式做计数,实际上这四种计数方式是有很大差别的。
count()的原理:

    1. count(*)
      遍历整张表,不需要取出数据来计算,直接按行累计。
    1. count(1)
      遍历整张表,不需要取数,按行计数。
    2. count(id)
      遍历整张表,取出id,按行计数。
    3. count(col)
      遍历整张表,取出col,如果字段定义不为null,取出col之后,按行计数。如果字段定义可以为null,循环对col进行判断是否为null值,再计数。

    这四种计数方式遍历整张表的方式也有不同:

    1. count(*)

      会找任意较小的索引遍历,如果没有二级索引,就会直接遍历主键索引,因为主键索引包含了全表数据,所以在字段比较大的时候,可能会需要频繁去磁盘取数据,导致count(*) 效率低,耗时长,结局方案是给一个小字段加个二级索引,这样count(*) 的时候就会遍历这个二级索引,快速进行计数。
    2. count(1)

          使用索引遍历的选择和 count(*)一致。
    3. count(id)

          使用主键索引遍历
    4. count(col)

          如果col建立了二级索引,则会遍历二级索引,否则主键索引
      

    所以,性能上排序为:count(*) > count(1) > count(id) > count(col)。

    在不考虑是否对null计数得区别的前提下,性能优化的方向,除了使用count(*) 外,就是适当使用小的二级索引。

    mysql的count函数可以计算符合条件的记录条数,比如:

    select count(*) from users;

    执行结果:
    在这里插入图片描述

    上面的sql只是将查询到的记录总数输出,count函数本身还可以配合if函数实现更复杂的计数:

    select count(if(status = 1, 1, null)) from users

    注意,count会将所有非null值计数,所以if里面不符合条件应该返回null。

    如果需要按某个字段计算去重后的数量,则需使用 distinct 关键字:

    select count(distinct last_name) from users

    当需要对1:n的关联查询做统计时,以上简单的count使用方式就不足以实现需求了,比如有以下两个表:

    CREATE TABLE `articles` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `title` varchar(255) NOT NULL COMMENT '标题',
      PRIMARY KEY (`id`)
    );
    
    CREATE TABLE `posts` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `status` tinyint(4) NOT NULL ,
       `article_id` int(10) unsigned NOT NULL
      PRIMARY KEY (`id`)
    ) 

    要在一条sql中查询出所有有帖子(posts)的文章(articles)的数量has_post_cnt、无帖子的文章数量not_post_cnt
    首先必定要做连接来关联文章和帖子:

    select * from articles left join posts on posts.article_id = articles.id 

    一般的思路是连接后再按artiles.id 分组,再在外层对posts.id判断是否等于null:

    select count(if(p_id is not null, 1, null)) has_post_cnt, count(if(p_id is null, 1, null)) not_post_cnt from (
        select articles.id a_id, posts.id p_id from articles left join posts on posts.article_id = artiles.id  group by articles.id
    ) tmp 

    但这样的话子查询效率低,考虑不使用子查询的方式实现,首先就必须去掉分组,不然查询结果只能是按分组聚合的结果,出不了所需的计数,首先看这个sql:

    select count(if(posts.id is not null, 1, null)) has_post_cnt, 
        count(if(posts.id is null, 1, null)) not_post_cnt
        from articles left join posts on posts.article_id = artiles.id

    查询出来的not_post_cnt肯定是对的,因为一条没有帖子的文章和帖子表的连接也就是和null连接,肯定是1:1的,不会有重复记录。
    has_post_cnt由于没有对artiles.id做分组,所以是1:n的,这个数是文章的帖子记录数,考虑同一个文章的帖子记录的article_id字段是唯一的,所以使用distinct来做去重处理:

        select count(distinct if(posts.id is not null, posts.article_id, null)) has_post_cnt, 
        count(if(posts.id is null, 1, null)) not_post_cnt
        from articles left join posts on posts.article_id = artiles.id  group by articles.id

    这里的关键是count(distinct if(p_id is not null, posts.article_id, null)),先做了去重再做了计数,得到的结果即是有帖子记录的文章数。

    我的个人博客:逐步前行STEP

    在mysql应用中常常需要通过别的表的查询结果来更新本表,但很少会本表的查询结果再来更新本表的,下面就看看从本表查询结果更新本表应该怎么做吧。

    表classify:

    字段属性
    idint(11)
    namestring(255)

    表production:

    字段属性
    idint(11)
    classify_idint(11)

    现在表classify中新增pro_cnt字段来统计一个分类下的商品数量:

    字段属性
    idint(11)
    namestring(255)
    pro_cntint(11)

    新增字段之后,需要初始更新一下pro_cnt的值为当前分类下的商品数量,首先,通过leftjoin查询出每个分类下的商品数

        select count(production.id) pro_cnt from classify 
        left join production on classify.id = production.classify_id

    然后将查询结果与classify再做一个连接,并且使用update...set语法做字段你更新:

        update classify inner join 
        (    
            select classify.id, count(production.id) pro_cnt from classify 
            left join production on classify.id = production.classify_id
        ) as tmp
        on using(id)
        set classify.pro_cnt = tmp.pro_cnt

    1、如果是使用Eloquent ORM操作数据库的话,在sql查询时可以调用toSql()方法来获取sql:

        App\User::where('name','like','%hezehua%')->toSql();

    2、如果是执行原生Sql查询,则不能使用toSql()方法了,而是开启查询日志:

        DB::enableQueryLog();
        DB::sselect("select * from users limit 10");
        $log = DB::getQueryLog();

    如果当前请求可能会建立多个连接,可以指定在某一个连接中获取查询日志,未指定的话将从最近一个连接中获取查询日志:

        DB::connection($con)->enableQueryLog();

    我的个人博客:逐步前行STEP

    mysql加锁语句:

    selct * from table_name where id=1 for update

    使用方式以及注意事项:

    1、该语句必须在事务中执行才生效
    2、如果该语句中的查询未正确使用索引(不一定是主键),则该语句会锁全表
    3、如果该语句的查询中索引生效了,只会锁定查询的行
    4、该锁会在事务提交的时候释放