月度归档:2018年03月

PHP7太空飞船操作符

太空飞船操作符在比较变量时非常有用,这里说的变量包括(字符串类型、整形、浮点型等)、数组、对象。这个操作符将三个比较符号(==、<、>)打包在一起,可以用于书写清晰易读的用于usort、uasort、uksort的回调函数,具体使用规则如下。

  • 当符号两边相等时返回0
  • 当符号右边大于左边返回-1
  • 当符号左边大于右边返回1

用下面的例子来比较整形、字符串类型、对象与数组。

$int1 = 1;
$int2 = 2;
$int3 = 3;

echo $int1 <=> $int3; //return 0
echo '<br>';
echo $int1 <=> $int2; //return -1
echo '<br>';
echo $int2 <=> $int3;//return 1

这个操作符在进行数组排序时是非常有用的。

function normal_sort():int
{
    if($a == $b)
        return 0;
    if($a < $b)
        return -1;
    return 1;
}

可以简化为:

function space_sort():int
{
   return $a<=>$b;
}
0

查看MySQL表的字符集和校对规则

mysql> show full columns from city\G;
*************************** 1. row ***************************
     Field: ID
      Type: int(11)
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra: auto_increment
Privileges: select,insert,update,references
   Comment:
*************************** 2. row ***************************
     Field: Name
      Type: char(35)
 Collation: latin1_swedish_ci
      Null: NO
       Key:
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 3. row ***************************
     Field: CountryCode
      Type: char(3)
 Collation: latin1_swedish_ci
      Null: NO
       Key: MUL
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 4. row ***************************
     Field: District
      Type: char(20)
 Collation: latin1_swedish_ci
      Null: NO
       Key:
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 5. row ***************************
     Field: Population
      Type: int(11)
 Collation: NULL
      Null: NO
       Key:
   Default: 0
     Extra:
Privileges: select,insert,update,references
   Comment:
5 rows in set (0.00 sec)

ERROR:
No query specified
0

关于MySQL视图

MySQL5.0版本之后开始引入视图。视图本身是一张虚拟表,不存放任何数据。在使用SQL语句访问视图的时候,它返回的数据是从其他表中生成的。视图和表是在同一个命名空间,MySQL在很多地方对于视图和表是同样对待的。不过视图和表也有不同,如不能对视图创建触发器,也不能使用drop table命令删除视图。
以下为简单创建视图的命令(示例数据库可在mysql官网下载):

mysql> create view Oceania as select * from Country where Continent='Oceania' wi
th check option;
Query OK, 0 rows affected (0.08 sec)

check option子句表示通过视图更新的行,都必须符合视图本身的where条件定义。所以不能更新视图定义列以外的行,比如上例中不能更新Continent列,也不能插入不同Continent值的新数据,否则会报错:

mysql> update oceania set Continent='Europe' where code ='GUM';
ERROR 1369 (HY000): CHECK OPTION failed 'world.oceania'

创建视图并指定视图使用临时表算法:

mysql> create algorithm=temptable view v2 as select * from sakila.actor;
Query OK, 0 rows affected (0.14 sec)

如果视图定义中包含了GROUP BY、UNION、聚合函数,以及其他一些特殊情况,就不能被更新了。更新视图的查询也可以是一个关联语句,但必须来自同一个表。另外,所有使用临时表算法实现的视图都无法被更新。MySQL并不会保存视图定义的原如SQL语句,所以如果打算通过执行show create view后再个性其结果的方法重新定义视图,可能会有点失望。show create view出来的视图创建语句将以一种不友好的内部方式呈现,充满了各种转义符,没有代码格式化,没有注释,也没有缩进。

1+

COUNT()的使用

  • COUNT()的作用
    COUNT()是一个特殊的函数,有两种非常不同的作用:它可以统计某个列值的数量,也可以统计行数。在统计列值时要求列值是非空的(不统计NULL)。如果在COUNT()的括号中指定了列或者列的表达式,则统计的不是这个表达式有值的结果数。因为很多人对NULL理解有问题,所以这里很容易产生误解。

COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时,实际上就是在统计行数。最简单的就是当我们使用COUNT(*)的时候,这种情况下通配符*并不会像我们猜想的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计所有的行数。

  • 关于MyISAM的神话
    一个容易产生的误解就是:MyISAM的COUNT()函数总是非常快,不过这是有条件的,即只有没有任何where条件的COUNT(*)才非常快,因为此时无须实际去计算表的行数。MySQL可以利用存储引擎的特性直接获取一个值。如果MySQL知道某个列col不可能为NULL值,那么MySQL内部会将COUNT(col)表达式优化为COUNT(*)。

当统计带Where子句的结果集行数,可以是统计某个列值的数量时,MyISAM的COUNT()和其他存储引擎没有任何不同,就不再有神话般的速度了。

0

UNION的限制

有时,MySQL无法将限制条件从外层“下推”到内层,这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

如果希望UNION各个子句能够根据LIMIT只取部分结果集,或者希望能够先排好序再合并结果集的话,就需要在UNION的各个子句中分别使用这些子句。例如,想将两个子查询结果联合起来,然后再取前20条记录,那么MySQL会将两个表都存放到同个临时表中,然后再取出前20行记录:

(select firstname,lastname 
from sakila.actor order by lastname)
union all
(select firstname,lastname
from sakila.customer
order by lastname)
limit 20;

这条查询将会把actor中的200条记录和customer表中的599条记录存放在一个临时表中,然后再从临时表中取出前20条。可以通过UNION的两个子查询中分别加一个LIMIT 20来减少临时表中的数据:

(select firstname,lastname 
from sakila.actor order by lastname
limit 20) union all
(select firstname,lastname from sakila.customer 
order by lastname limit 20)
limit 20;

现在中间的临时表只包包含40条记录了,除了性能的考虑之外,在这里还需要注意一点:从临时表中取出数据的顺序并不是一定的,所以如果想获得正确的顺序,还需要加一个全局的order by 和limit操作。

0

检查并修复损坏的表

表损坏是很糟糕的事情。对于MyISAM存储引擎,表损坏通常是系统崩溃导致的。其他引擎也会由于硬件问题、MySQL本身的缺陷或者操作系统的问题导致索引损坏。

损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题,严重时甚至还会导致数据库崩溃。如果你发现了一些古怪的问题——例如一些不应该发生的错误,可以尝试运行check table来检查是否发生了损坏(注意有些存储引擎不支持该命令;有些存储引擎则支持以不同的选项来控制完全检查表的方式)。CHECK TABLE通常能够找出大多数表和索引的错误。

mysql> check table userinfo;
+------------------+-------+----------+----------+
| Table            | Op    | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| testphp.userinfo | check | status   | OK       |
+------------------+-------+----------+----------+
1 row in set (8.20 sec)

可以使用repair table命令来修复损坏的表,但同样不是所有的存储引擎都支持该命令。如果存储引擎不支持,也可以通过一个不做任何操作(no-op)的ALTER操作来重建表,例如修改表的存储引擎为当前的引擎。

mysql>ALTER TABLE userinfo ENGINE=INNODB;

此外,也可以使用一些存储引擎相关的离线工具,例如myisamchk;或者将数据导出一份,然后再重新导入。不过如果损坏的是系统区域,或者是表的“行数据”区域,而不是索引,那么上面的办法就没有用了。在这种情况下,可以从备份中恢复表,或者尝试从损坏的数据文件中尽可能的恢复数据。

如果INNODB引擎的表出现了损坏,那么一定是发生了严重的错误,需要立刻调查一下原因。INNODB一般不会出现损坏。INNODB的设计保证了它并不容易被损坏。

0

什么叫索引的选择性

索引的选择性是指,不重复的索引值(也称为基数,cardinality)和数据表的记录总数(#T)的比值,范围从1/#T到1之间。索引的选择性越高则查询效率越高,因为选择性高的索引可以让Mysql在查找时过滤掉更多的行。唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。

0

Mysql使用show profile剖析单条查询语句

show profile命令是MySQL5.1以后的版本中引入的,来源于开源社区中的Jeremy Cole的贡献。默认情况是关闭的。
查看profile是否是开启的可以使用以下语句:

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.08 sec)

首先,mysql>SET profiling =1;
接下来,执行预测试的SQL语句如:

mysql> desc userinfo;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(100)     | YES  | MUL | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
2 rows in set (0.08 sec)

mysql> select * from userinfo where id<5000;
...

使用show profiles查看语句队列:

mysql> show profiles;
+----------+------------+--------------------------------------+
| Query_ID | Duration   | Query                                |
+----------+------------+--------------------------------------+
|        1 | 0.04885725 | select * from userinfo where id<1000 |
|        2 | 0.00020850 | show valiables like 'profiling'      |
|        3 | 0.08174650 | show variables like 'profiling'      |
|        4 | 0.08306875 | desc userinfo                        |
|        5 | 0.00459500 | select * from userinfo where id<5000 |
+----------+------------+--------------------------------------+

接下来,我们使用show profile for query 5;,显示详情(query 5 对应 Query_ID=5的查询)

mysql> show profile for query 5;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000282 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000023 |
| init                 | 0.000031 |
| System lock          | 0.000009 |
| optimizing           | 0.000010 |
| statistics           | 0.000493 |
| preparing            | 0.000013 |
| executing            | 0.000001 |
| Sending data         | 0.003579 |
| end                  | 0.000005 |
| query end            | 0.000014 |
| closing tables       | 0.000006 |
| freeing items        | 0.000106 |
| cleaning up          | 0.000016 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
0

yank0362

2018年3月14日

要理解MySQL中索引是如何工作的,最简单的方法就是去看看一本书的“索引”部分:如果想在一本书中找到某个特定主题,一般会先看书的“索引”,找到对应的页码。

0