精华内容
下载资源
问答
  • mysql null值排序

    2018-05-02 14:14:14
    MySQL中order by 排序遇到NULL值的问题 MySQL数据库,在order by排序的时候,...MySQL数据库在设计的时候,如果字段允许NULL值,那么对该字段进行排序的时候需要注意那些值为NULL的行。我们知道NULL的意思表示什么都...

    MySQL中order by 排序遇到NULL值的问题 MySQL数据库,在order by排序的时候,如果存在NULL值,那么NULL是最小的,ASC正序排序的话,NULL值是在最前面的。 如果我们想让NULL排在后面,让非NULL的行排在前面该怎么做呢?


    MySQL数据库在设计的时候,如果字段允许NULL值,那么对该字段进行排序的时候需要注意那些值为NULL的行。


    我们知道NULL的意思表示什么都不是,或者理解成“未知”也可以,它与任何值比较的结果都是false,


    默认情况下,MySQL会认为NULL值比其他类型的数据小, 也就是说,在order by排序的时候,NULL是最小的,ASC正序排序的话,NULL值是在最前面的。 如果我们想让NULL排在后面,让非NULL的行排在前面该怎么做呢?


    下面我们通过一个小例子,来说明这个情况。


    首先,创建一个测试数据表 test_user,


    mysql> create table test_user(-> id int unsigned not null auto_increment,-> username varchar(10) not null,-> age int,-> primary key (id))-> engine=myisam-> default charset=utf8-> ; Query OK, 0 rows affected (0.14 sec)


    填充6条数据,其中3条设置了age值,另外3条age值为NULL


    mysql> insert into test_user values(1,'hutuseng1',28),(2,'hutuseng2',30); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0


    mysql> insert into test_user(username) values('hutuseng3'),('hutuseng4'),('hutuseng5'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0


    mysql> insert into test_user values(6,'hutuseng6',23); Query OK, 1 row affected (0.00 sec)


    mysql> select * from test_user; +----+-----------+------+ | id | username | age | +----+-----------+------+ | 1 | hutuseng1 | 28 | | 2 | hutuseng2 | 30 | | 3 | hutuseng3 | NULL | | 4 | hutuseng4 | NULL | | 5 | hutuseng5 | NULL | | 6 | hutuseng6 | 23 | +----+-----------+------+ 6 rows in set (0.00 sec)


    我们按照age字段从小到大排序,我们看到NULL值是最小的,排在了最前面


    mysql> select * from test_user order by age; +----+-----------+------+ | id | username | age | +----+-----------+------+ | 3 | hutuseng3 | NULL | | 4 | hutuseng4 | NULL | | 5 | hutuseng5 | NULL | | 6 | hutuseng6 | 23 | | 1 | hutuseng1 | 28 | | 2 | hutuseng2 | 30 | +----+-----------+------+ 6 rows in set (0.00 sec)


    按照age字段从大到小排序,我们看到NULL值确实是最小的,排在了最后面


    mysql> select * from test_user order by age desc; +----+-----------+------+ | id | username | age | +----+-----------+------+ | 2 | hutuseng2 | 30 | | 1 | hutuseng1 | 28 | | 6 | hutuseng6 | 23 | | 3 | hutuseng3 | NULL | | 4 | hutuseng4 | NULL | | 5 | hutuseng5 | NULL | +----+-----------+------+ 6 rows in set (0.00 sec)


    这里有个需要注意的事项,就是NULL值本身是无法排序的,也就是说一个NULL是无法和另外一个NULL比较的。 你可能已经发现了,那三个NULL值的行,不管正序还是倒序,顺序都是一致的,当然也可能不一致。


    那如果在对age进行正序排序的时候,我们希望NULL值的记录在最后面显示,那该如何做呢?


    1、重新生成一列,比如agenull,利用is null操作符,把NULL值的行变成1,非NULL值的行变成0,先对该字段排序,再对age排序


    mysql> select *,age is null as agenull from test_user order by agenull,age; +----+-----------+------+---------+ | id | username | age | agenull | +----+-----------+------+---------+ | 6 | hutuseng6 | 23 | 0 | | 1 | hutuseng1 | 28 | 0 | | 2 | hutuseng2 | 30 | 0 | | 3 | hutuseng3 | NULL | 1 | | 4 | hutuseng4 | NULL | 1 | | 5 | hutuseng5 | NULL | 1 | +----+-----------+------+---------+ 6 rows in set (0.01 sec)


    2、直接利用isnull函数对age列求值,跟第一种方法的道理是一样的


    mysql> select * from test_user order by isnull(age),age; +----+-----------+------+ | id | username | age | +----+-----------+------+ | 6 | hutuseng6 | 23 | | 1 | hutuseng1 | 28 | | 2 | hutuseng2 | 30 | | 3 | hutuseng3 | NULL | | 4 | hutuseng4 | NULL | | 5 | hutuseng5 | NULL | +----+-----------+------+ 6 rows in set (0.00 sec)


    3、还可以利用MySQL中的一个小技巧,在字段前面加上一个负号,也就是减号,ASC改成DESC ,DESC改成ASC


    mysql> select * from test_user order by -age desc; +----+-----------+------+ | id | username | age | +----+-----------+------+ | 6 | hutuseng6 | 23 | | 1 | hutuseng1 | 28 | | 2 | hutuseng2 | 30 | | 3 | hutuseng3 | NULL | | 4 | hutuseng4 | NULL | | 5 | hutuseng5 | NULL | +----+-----------+------+ 6 rows in set (0.00 sec)


    所以,在设计数据库的时候,如果某个字段要进行排序的话,最好不要为NULL。




    SELECT handle_person, create_date,handle_person IS NULL AS handlePersonNull FROM tb_event_collect WHERE 1 = 1 ORDER BY handlePersonNull DESC, create_date DESC 

    展开全文
  • MySQL中order by 排序遇到NULL值的问题 MySQL数据库,在order by排序的时候,...MySQL数据库在设计的时候,如果字段允许NULL值,那么对该字段进行排序的时候需要注意那些值为NULL的行。我们知道NULL的意思表示什么都...

    MySQL中order by 排序遇到NULL值的问题 MySQL数据库,在order by排序的时候,如果存在NULL值,那么NULL是最小的,ASC正序排序的话,NULL值是在最前面的。 如果我们想让NULL排在后面,让非NULL的行排在前面该怎么做呢?


    MySQL数据库在设计的时候,如果字段允许NULL值,那么对该字段进行排序的时候需要注意那些值为NULL的行。


    我们知道NULL的意思表示什么都不是,或者理解成“未知”也可以,它与任何值比较的结果都是false,


    默认情况下,MySQL会认为NULL值比其他类型的数据小, 也就是说,在order by排序的时候,NULL是最小的,ASC正序排序的话,NULL值是在最前面的。 如果我们想让NULL排在后面,让非NULL的行排在前面该怎么做呢?


    下面我们通过一个小例子,来说明这个情况。


    首先,创建一个测试数据表 test_user,


    mysql> create table test_user(

    id int unsigned not null auto_increment,

    username varchar(10) not null,

    age int,

    primary key (id))

    engine=myisam default charset=utf8 ;

    Query OK, 0 rows affected (0.14 sec)


    填充6条数据,其中3条设置了age值,另外3条age值为NULL


    mysql> insert into test_user values(1,'user1',28),(2,'user2',30);

    Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0


    mysql> insert into test_user(username) values('user3'),('user4'),('user5');

    Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0


    mysql> insert into test_user values(6,'user6',23);

    Query OK, 1 row affected (0.00 sec)


    mysql> select * from test_user;

    +----+-----------+------+ | id | username | age | +----+-----------+------+

    | 1 | user1 | 28 |

    | 2 | user2 | 30 |

    | 3 | user3 | NULL |

    | 4 | user4 | NULL |

    | 5 | user5 | NULL |

    | 6 | user6 | 23 |

    +----+-----------+------+ 6 rows in set (0.00 sec)


    我们按照age字段从小到大排序,我们看到NULL值是最小的,排在了最前面


    mysql> select * from test_user order by age;

    +----+-----------+------+ | id | username | age | +----+-----------+------+

    | 3 | user3 | NULL | | 4 | user4 | NULL |

    | 5 | user5 | NULL |

    | 6 | user6 | 23 |

    | 1 | user1 | 28 |

    | 2 | user2 | 30 |

    +----+-----------+------+ 6 rows in set (0.00 sec)


    按照age字段从大到小排序,我们看到NULL值确实是最小的,排在了最后面


    mysql> select * from test_user order by age desc;

    +----+-----------+------+ | id | username | age | +----+-----------+------+

    | 2 | user2 | 30 |

    | 1 | user1 | 28 |

    | 6 | user6 | 23 |

    | 3 | user3 | NULL |

    | 4 | user4 | NULL |

    | 5 | user5 | NULL |

    +----+-----------+------+ 6 rows in set (0.00 sec)


    这里有个需要注意的事项,就是NULL值本身是无法排序的,也就是说一个NULL是无法和另外一个NULL比较的。 你可能已经发现了,那三个NULL值的行,不管正序还是倒序,顺序都是一致的,当然也可能不一致。


    那如果在对age进行正序排序的时候,我们希望NULL值的记录在最后面显示,那该如何做呢?


    1、重新生成一列,比如agenull,利用is null操作符,把NULL值的行变成1,非NULL值的行变成0,先对该字段排序,再对age排序


    mysql> select *,age is null as agenull from test_user order by agenull,age; +----+-----------+------+---------+ | id | username | age | agenull | +----+-----------+------+---------+ | 6 | user6 | 23 | 0 | | 1 | user1 | 28 | 0 | | 2 | user2 | 30 | 0 | | 3 | user3 | NULL | 1 | | 4 | user4 | NULL | 1 | | 5 | user5 | NULL | 1 | +----+-----------+------+---------+ 6 rows in set (0.01 sec)


    2、直接利用isnull函数对age列求值,跟第一种方法的道理是一样的


    mysql> select * from test_user order by isnull(age),age; +----+-----------+------+ | id | username | age | +----+-----------+------+ | 6 | user6 | 23 | | 1 | user1 | 28 | | 2 | user2 | 30 | | 3 | user3 | NULL | | 4 | user4 | NULL | | 5 | user5 | NULL | +----+-----------+------+ 6 rows in set (0.00 sec)


    3、还可以利用MySQL中的一个小技巧,在字段前面加上一个负号,也就是减号,ASC改成DESC ,DESC改成ASC


    mysql> select * from test_user order by -age desc; +----+-----------+------+ | id | username | age | +----+-----------+------+ | 6 | user6 | 23 | | 1 | user1 | 28 | | 2 | user2 | 30 | | 3 | user3 | NULL | | 4 | user4 | NULL | | 5 | user5 | NULL | +----+-----------+------+ 6 rows in set (0.00 sec)


    所以,在设计数据库的时候,如果某个字段要进行排序的话,最好不要为NULL。






    展开全文
  • 数据库允许空值,往往是悲剧的开始》一文通过explain来分析SQL的执行计划,来分析null对索引命中情况的影响,有不少朋友留言,问explain结果中的type字段,ref,ALL等不一样的究竟是什么含义。 今天花1分钟简单...

    目录

    1. explain结果中的type字段

    1.1 最为常见的扫描方式

    1.2 system

    1.3 const

    1.4 eq_ref

    1.5 ref

    1.6 range

    1.7 index

    1.8 ALL


    数据库允许空值,往往是悲剧的开始》一文通过explain来分析SQL的执行计划,来分析null对索引命中情况的影响,有不少朋友留言,问explain结果中的type字段,ref,ALL等不一样的值究竟是什么含义。

    今天花1分钟简单说下,常见的type结果及代表的含义,并且通过同一个SQL语句的性能差异,说明建对索引多么重要。

     

    1. explain结果中的type字段

    explain结果中的type字段代表什么意思?

    Image

    MySQL的官网解释非常简洁,只用了3个单词:连接类型(the join type)。它描述了找到所需数据使用的扫描方式。

     

    1.1 最为常见的扫描方式

    • system:系统表,少量数据,往往不需要进行磁盘IO;

    • const:常量连接;

    • eq_ref:主键索引(primary key)或者非空唯一索引(unique not null)等值扫描;

    • ref:非主键非唯一索引等值扫描;

    • range:范围扫描;

    • index:索引树扫描;

    • ALL:全表扫描(full table scan);

    画外音:这些是最常见的,大家去explain自己工作中的SQL语句,95%都是上面这些类型。

     

    上面各类扫描方式由快到慢

    system > const > eq_ref > ref > range > index > ALL

    下面一一举例说明。

     

    1.2 system

    Image

    explain select * from mysql.time_zone;

    上例中,从系统库mysql的系统表time_zone里查询数据,扫码类型为system,这些数据已经加载到内存里,不需要进行磁盘IO。

    这类扫描是速度最快的。

    Image

    explain select * from (select * from user where id=1) tmp;

    再举一个例子,内层嵌套(const)返回了一个临时表,外层嵌套从临时表查询,其扫描类型也是system,也不需要走磁盘IO,速度超快。

     

    1.3 const

    数据准备:

    create table user (
        id int primary key,
        name varchar(20)
    )engine=innodb;
    
    insert into user values(1,'shenjian');
    insert into user values(2,'zhangsan');
    insert into user values(3,'lisi');

    Image

    const扫描的条件为:

    (1)命中主键(primary key)或者唯一(unique)索引;

    (2)被连接的部分是一个常量(const)值;

     

    explain select * from user where id=1;

    如上例,id是PK,连接部分是常量1。

    画外音:别搞什么类型转换的幺蛾子。

     

    这类扫描效率极高,返回数据量少,速度非常快。

     

    1.4 eq_ref

    数据准备:

    create table user (
        id int primary key,
        name varchar(20)
    )engine=innodb;
    
    insert into user values(1,'shenjian');
    insert into user values(2,'zhangsan');
    insert into user values(3,'lisi');
    
    create table user_ex (
        id int primary key,
        age int
    )engine=innodb;
    
    insert into user_ex values(1,18);
    insert into user_ex values(2,20);
    insert into user_ex values(3,30);
    insert into user_ex values(4,40);
    insert into user_ex values(5,50);

    Image

    eq_ref扫描的条件为,对于前表的每一行(row),后表只有一行被扫描。

     

    再细化一点:

    (1)join查询;

    (2)命中主键(primary key)或者非空唯一(unique not null)索引;

    (3)等值连接;

     

    explain select * from user,user_ex where user.id=user_ex.id;

    如上例,id是主键,该join查询为eq_ref扫描。

    这类扫描的速度也异常之快。

     

    1.5 ref

    数据准备:

    create table user (
       id int,
       name varchar(20) ,
       index(id)
    )engine=innodb;
    
    insert into user values(1,'shenjian');
    insert into user values(2,'zhangsan');
    insert into user values(3,'lisi');
    
    create table user_ex (
       id int,
       age int,
       index(id)
    )engine=innodb;
    
    insert into user_ex values(1,18);
    insert into user_ex values(2,20);
    insert into user_ex values(3,30);
    insert into user_ex values(4,40);
    insert into user_ex values(5,50);

    Image

    如果把上例eq_ref案例中的主键索引,改为普通非唯一(non unique)索引。

    explain select * from user,user_ex where user.id=user_ex.id;

    就由eq_ref降级为了ref,此时对于前表的每一行(row),后表可能有多于一行的数据被扫描。

     

    Image

    explain select * from user where id=1;

    当id改为普通非唯一索引后,常量的连接查询,也由const降级为了ref,因为也可能有多于一行的数据被扫描。

     

    ref扫描,可能出现在join里,也可能出现在单表普通索引里,每一次匹配可能有多行数据返回,虽然它比eq_ref要慢,但它仍然是一个很快的join类型。

     

    1.6 range

    数据准备:

    create table user (
        id int primary key,
        name varchar(20)
    )engine=innodb;
    
    insert into user values(1,'shenjian');
    insert into user values(2,'zhangsan');
    insert into user values(3,'lisi');
    insert into user values(4,'wangwu');
    insert into user values(5,'zhaoliu');

    Image

    range扫描就比较好理解了,它是索引上的范围查询,它会在索引上扫码特定范围内的值。

    explain select * from user where id between 1 and 4;
    explain select * from user where idin(1,2,3);
    explain select * from user where id>3;

    像上例中的between,in,>都是典型的范围(range)查询。

    画外音:必须是索引,否则不能批量"跳过"。

     

    1.7 index

    Image

    index类型,需要扫描索引上的全部数据。

     

    explain count (*) from user;

    如上例,id是主键,该count查询需要通过扫描索引上的全部数据来计数。

    画外音:此表为InnoDB引擎。

     

    它仅比全表扫描快一点。

     

    1.8 ALL

    数据准备:

    create table user (
       id int,
       name varchar(20)
    )engine=innodb;
    
    insert into user values(1,'shenjian');
    insert into user values(2,'zhangsan');
    insert into user values(3,'lisi');
    
    create table user_ex (
       id int,
       age int
    )engine=innodb;
    
    insert into user_ex values(1,18);
    insert into user_ex values(2,20);
    insert into user_ex values(3,30);
    insert into user_ex values(4,40);
    insert into user_ex values(5,50);

    Image

    explain select * from user,user_ex where user.id=user_ex.id;

    如果id上不建索引,对于前表的每一行(row),后表都要被全表扫描。

     

    今天这篇文章中,这个相同的join语句出现了三次:

    (1)扫描类型为eq_ref,此时id为主键;

    (2)扫描类型为ref,此时id为非唯一普通索引;

    (3)扫描类型为ALL,全表扫描,此时id上无索引;

     

    有此可见,建立正确的索引,对数据库性能的提升是多么重要。

     

    另外,《两类非常隐蔽的全表扫描,不能命中索引》中,也提到不正确的SQL语句,可能导致全表扫描。

     

    全表扫描代价极大,性能很低,是应当极力避免的,通过explain分析SQL语句,非常有必要。

     

    总结

    (1)explain结果中的type字段,表示(广义)连接类型,它描述了找到所需数据使用的扫描方式;

    (2)常见的扫描类型有:

    system>const>eq_ref>ref>range>index>ALL

    其扫描速度由快到慢;

    (3)各类扫描类型的要点是:

    • system最快:不进行磁盘IO

    • const:PK或者unique上的等值查询

    • eq_ref:PK或者unique上的join查询,等值匹配,对于前表的每一行(row),后表只有一行命中

    • ref:非唯一索引,等值匹配,可能有多行命中

    • range:索引上的范围扫描,例如:between/in/>

    • index:索引上的全集扫描,例如:InnoDB的count

    • ALL最慢:全表扫描(full table scan)

    (4)建立正确的索引(index),非常重要;

    (5)使用explain了解并优化执行计划,非常重要;

     

    思路比结论重要,希望大家有收获。

    画外音:本文测试于MySQL5.6。

    展开全文
  • 数据库允许空值,往往是悲剧的开始》一文通过explain来分析SQL的执行计划,来分析null对索引命中情况的影响,有不少朋友留言,问explain结果中的type字段,ref,ALL等不一样的究竟是什么含义。 今天花1分钟...

    数据库允许空值,往往是悲剧的开始》一文通过explain来分析SQL的执行计划,来分析null对索引命中情况的影响,有不少朋友留言,问explain结果中的type字段,ref,ALL等不一样的值究竟是什么含义。

     

    今天花1分钟简单说下,常见的type结果及代表的含义,并且通过同一个SQL语句的性能差异,说明建对索引多么重要。

     

    explain结果中的type字段代表什么意思?

    MySQL的官网解释非常简洁,只用了3个单词:连接类型(the join type)。它描述了找到所需数据使用的扫描方式。

     

    最为常见的扫描方式有:

    • system:系统表,少量数据,往往不需要进行磁盘IO;

    • const:常量连接;

    • eq_ref:主键索引(primary key)或者非空唯一索引(unique not null)等值扫描;

    • ref:非主键非唯一索引等值扫描;

    • range:范围扫描;

    • index:索引树扫描;

    • ALL:全表扫描(full table scan);

    画外音:这些是最常见的,大家去explain自己工作中的SQL语句,95%都是上面这些类型。

     

    上面各类扫描方式由快到慢

    system > const > eq_ref > ref > range > index > ALL

    下面一一举例说明。

     

    一、system

    explain select * from mysql.time_zone;

    上例中,从系统库mysql的系统表time_zone里查询数据,扫码类型为system,这些数据已经加载到内存里,不需要进行磁盘IO。

     

    这类扫描是速度最快的。

     

    explain select * from (select * from user where id=1) tmp;

    再举一个例子,内层嵌套(const)返回了一个临时表,外层嵌套从临时表查询,其扫描类型也是system,也不需要走磁盘IO,速度超快。

     

    二、const

    数据准备:

    create table user (

    id int primary key,

    name varchar(20)

    )engine=innodb;

     

    insert into user values(1,'shenjian');

    insert into user values(2,'zhangsan');

    insert into user values(3,'lisi');

    const扫描的条件为:

    (1)命中主键(primary key)或者唯一(unique)索引;

    (2)被连接的部分是一个常量(const)值;

     

    explain select * from user where id=1;

    如上例,id是PK,连接部分是常量1。

    画外音:别搞什么类型转换的幺蛾子。

     

    这类扫描效率极高,返回数据量少,速度非常快。

     

    三、eq_ref

    数据准备:

    create table user (

    id int primary key,

    name varchar(20)

    )engine=innodb;

     

    insert into user values(1,'shenjian');

    insert into user values(2,'zhangsan');

    insert into user values(3,'lisi');

     

    create table user_ex (

    id int primary key,

    age int

    )engine=innodb;

     

    insert into user_ex values(1,18);

    insert into user_ex values(2,20);

    insert into user_ex values(3,30);

    insert into user_ex values(4,40);

    insert into user_ex values(5,50);

    eq_ref扫描的条件为,对于前表的每一行(row),后表只有一行被扫描。

     

    再细化一点:

    (1)join查询;

    (2)命中主键(primary key)或者非空唯一(unique not null)索引;

    (3)等值连接;

     

    explain select * from user,user_ex where user.id=user_ex.id;

    如上例,id是主键,该join查询为eq_ref扫描。

     

     

     

    这类扫描的速度也异常之快。

     

    四、ref

    数据准备:

    create table user (

    id int,

    name varchar(20) ,

    index(id)

    )engine=innodb;

     

    insert into user values(1,'shenjian');

    insert into user values(2,'zhangsan');

    insert into user values(3,'lisi');

     

    create table user_ex (

    id int,

    age int,

    index(id)

    )engine=innodb;

     

    insert into user_ex values(1,18);

    insert into user_ex values(2,20);

    insert into user_ex values(3,30);

    insert into user_ex values(4,40);

    insert into user_ex values(5,50);

    如果把上例eq_ref案例中的主键索引,改为普通非唯一(non unique)索引。

     

    explain select * from user,user_ex where user.id=user_ex.id;

    就由eq_ref降级为了ref,此时对于前表的每一行(row),后表可能有多于一行的数据被扫描。

     

    explain select * from user where id=1;

    当id改为普通非唯一索引后,常量的连接查询,也由const降级为了ref,因为也可能有多于一行的数据被扫描。

     

    ref扫描,可能出现在join里,也可能出现在单表普通索引里,每一次匹配可能有多行数据返回,虽然它比eq_ref要慢,但它仍然是一个很快的join类型。

     

    五、range

    数据准备:

    create table user (

    id int primary key,

    name varchar(20)

    )engine=innodb;

     

    insert into user values(1,'shenjian');

    insert into user values(2,'zhangsan');

    insert into user values(3,'lisi');

    insert into user values(4,'wangwu');

    insert into user values(5,'zhaoliu');

    range扫描就比较好理解了,它是索引上的范围查询,它会在索引上扫码特定范围内的值。

     

    explain select * from user where id between 1 and 4;

    explain select * from user where idin(1,2,3);

    explain select * from user where id>3;

    像上例中的between,in,>都是典型的范围(range)查询。

    画外音:必须是索引,否则不能批量"跳过"。

     

    六、index

    index类型,需要扫描索引上的全部数据。

     

    explain count (*) from user;

    如上例,id是主键,该count查询需要通过扫描索引上的全部数据来计数。

    画外音:此表为InnoDB引擎。

     

    它仅比全表扫描快一点。

     

    七、ALL

    数据准备:

    create table user (

    id int,

    name varchar(20)

    )engine=innodb;

     

    insert into user values(1,'shenjian');

    insert into user values(2,'zhangsan');

    insert into user values(3,'lisi');

     

    create table user_ex (

    id int,

    age int

    )engine=innodb;

     

    insert into user_ex values(1,18);

    insert into user_ex values(2,20);

    insert into user_ex values(3,30);

    insert into user_ex values(4,40);

    insert into user_ex values(5,50);

    explain select * from user,user_ex where user.id=user_ex.id;

    如果id上不建索引,对于前表的每一行(row),后表都要被全表扫描。

     

    今天这篇文章中,这个相同的join语句出现了三次:

    (1)扫描类型为eq_ref,此时id为主键;

    (2)扫描类型为ref,此时id为非唯一普通索引;

    (3)扫描类型为ALL,全表扫描,此时id上无索引;

     

    有此可见,建立正确的索引,对数据库性能的提升是多么重要。

     

    另外,《类型转换带来的大坑》中,也提到不正确的SQL语句,可能导致全表扫描。

     

    全表扫描代价极大,性能很低,是应当极力避免的,通过explain分析SQL语句,非常有必要。

     

    总结

    (1)explain结果中的type字段,表示(广义)连接类型,它描述了找到所需数据使用的扫描方式;

    (2)常见的扫描类型有:

    system>const>eq_ref>ref>range>index>ALL

    其扫描速度由快到慢;

    (3)各类扫描类型的要点是:

    • system最快:不进行磁盘IO

    • const:PK或者unique上的等值查询

    • eq_ref:PK或者unique上的join查询,等值匹配,对于前表的每一行(row),后表只有一行命中

    • ref:非唯一索引,等值匹配,可能有多行命中

    • range:索引上的范围扫描,例如:between/in/>

    • index:索引上的全集扫描,例如:InnoDB的count

    • ALL最慢:全表扫描(full table scan)

    (4)建立正确的索引(index),非常重要;

    (5)使用explain了解并优化执行计划,非常重要;

    展开全文
  • 数据库允许空值,往往是悲剧的开始》一文通过explain来分析SQL的执行计划,来分析null对索引命中情况的影响,有不少朋友留言,问explain结果中的type字段,ref,ALL等不一样的究竟是什么含义。 今天花1分钟...
  • MySQL命令大全

    2018-01-15 11:19:17
    mysqldump -u 用名 -p –default-character-set=latin1 数据库名 > 导出的文件名(数据库默认编码latin1) mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql 2.导出一个表 mysqldump -u 用户名 -p 数据库名 表名...
  • MYSQL常用命令大全

    2011-05-30 13:31:24
    mysqldump -u 用户名 -p --default-character-set=latin1 数据库名 > 导出的文件名(数据库默认编码latin1) mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql 2.导出一个表 mysqldump -u 用户名 -p 数据库名 表名> ...
  • JAVA面试题最全集

    2010-03-13 13:09:10
    87.UNIX中QT是什么意思? 88.在软件开发生命周期中的哪个阶段开始测试? 89.dotnet与J2EE的比较? 90.什么是ActiveX? 91.Java中IDL是什么? 92.ISO9000和CMM是什么?IS09000和CMM(软件能力成熟度模型)认证是国际上...
  • k最后的值是? 答:60,此题考察优先级,实际写成: k*=(i+j);,赋值运算符优先级最低 21.对数据库的一张表进行操作,同时要对另一张表进行操作,如何实现? 答:将操作多个表的操作放入到事务中进行处理 22.TCP/IP ...
  • 4.C#中的委托是什么?事件是不是一种委托? 答 : 委托可以把一个方法作为参数代入另一个方法。 委托可以理解为指向一个函数的引用。 是,是一种特殊的委托 5.override与重载的区别 答 : override 与重载的...
  • 27、GC是什么? 为什么要有GC?  GC是垃圾收集的意思(Gabage Collection),内存处理是编程人员容易出现问题的地方,忘记或者错误的内存回收会导致程序或系统的不稳定甚至崩溃,Java提供的GC功能可以自动监测对象...
  • java 面试题 总结

    2009-09-16 08:45:34
    24、GC是什么? 为什么要有GC?  GC是垃圾收集的意思(Gabage Collection),内存处理是编程人员容易出现问题的地方,忘记或者错误的内存回收会导致程序或系统的不稳定甚至崩溃,Java提供的GC功能可以自动监测对象...
  • 经过跟踪变量值发现循环变量i的阀值pSysHead->dbf_count的数值为0xFFFFFFFF,该值是从被破坏的内存数据库中获取的,正常情况下该值小于127。而pDBFat是数据库的起始地址,如果pSysHead->dbf_count值异常过大,将...
  • 3.1 保证了不同线程对这个变量进行操作时的可见性,即一个线程修改了某个变量的,这新对其他线程来说立即可见的。(保证可见性) 3.2 禁止进行指令重排序。(保证有序性) 划个重点:volatile 关键字能保证...
  • " 是什么意思呢? javascript: 表示在触发默认动作时,执行一段 JavaScript 代码; javascript:; 表示什么都不执行,这样点击时就没有任何反应,相当于去掉 a 标签的默认行为。 select - option 绑定 Vue 实例 ...

空空如也

空空如也

1
收藏数 20
精华内容 8
关键字:

数据库允许null值是什么意思