数据库 Schema 与数据类型优化小技巧

ibugs · August 11, 2015 · Last by vincent4j replied at August 26, 2015 · 7340 hits
Topic has been selected as the excellent topic by the admin.

一、手机号应该用什么类型的存储

int 的最大值为 21 亿,2**31,所以并不能保存手机号的百亿数据。

mysql> create table big_int_test ( int_01 int(11), b_01 bigint(11), v_01 varchar(11));
Query OK, 0 rows affected (0.03 sec)

mysql> show create table big_int_test;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                            |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| big_int_test | CREATE TABLE `big_int_test` (
  `int_01` int(11) DEFAULT NULL,
  `b_01` bigint(11) DEFAULT NULL,
  `v_01` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into big_int_test (int_01, b_01, v_01) value (13522221111, 13522221111,13522221111);
ERROR 1264 (22003): Out of range value for column 'int_01' at row 1
mysql> insert into big_int_test (b_01, v_01) value (13522221111,13522221111);
Query OK, 1 row affected (0.00 sec)

mysql> 

二、尽量避免使用 NULL

  • null 是一个未知的值

  • null 和任何值都不相等,包括它自己

mysql> select null >0, null < 0 from dual;
+---------+----------+
| null >0 | null < 0 |
+---------+----------+
|    NULL |     NULL |
+---------+----------+
1 row in set (0.01 sec)

mysql> select null = null from dual;
+-------------+
| null = null |
+-------------+
|        NULL |
+-------------+
1 row in set (0.07 sec)

  • 将某个值与 Null 进行比较的正确方法是使用 is 关键字,以及 is not 操作符

mysql> select * from areas where id in (3144, 3143, null, 3133);
+------+--------+--------------------------------+--------+
| id   | areaid | name                           | cityid |
+------+--------+--------------------------------+--------+
| 3133 | 654226 | 和布克赛尔蒙古自治县           | 654200 |
| 3143 | 659003 | 图木舒克市                     | 659000 |
| 3144 | 659004 | 五家渠市                       | 659000 |
+------+--------+--------------------------------+--------+
3 rows in set (0.02 sec)

select * from areas where id = 3144 or id = 3143 or id =3133 or id = null;

mysql> select * from areas where id not in (3144, 3143, null, 3133);
Empty set (0.00 sec)

转换成  select * from areas where id != 3144 and id != 3143 and id !=3133 and id != null;


  • Null 与排序

    在排序时,null 值被认为是最大的(Oracle),在降序排序时会让你非常头大,因为 null 值排在了最前面。解决方法是使用 coalesce 进行

    SQLServer 和 MySQL 认为 null 最小,Oracle 认为 null 最大

– 在输出时将 null 转换为 0 :

select name, coalesce(points, 0)

from users

order by 2 desc;

– 输出时保留 null, 但排序时转换为 0 :

select name, points

from users

order by coalesce(points, 0) desc;

sql-null
MYSQL、SQL Server、Oracle 数据库排序空值 null 问题及其解决办法
【NULLS】Oracle 对 SQL 排序后 NULL 值位置的“特殊关照”
order by 语句对 null 字段的默认排序

三、DATETIME 和 TIMESTAMP 的区别

DATETIME:

  • 占用 8 个字节
  • 这个类型能保存大范围的值,从 1001 年到 9999 年,精度为秒。
  • 封存到 'YYYYMMDDHHMMSS' 的整数中,与时区无关。

TIMESTAMP:

  • 占用 4 个字节
  • 保存了 1970.1.1 午夜到现在的秒数,它和 UNIX 时间戳相同。
  • 只能表示从 1970 年到 2038 年。
  • FROM_UNIXTIME() UNIX_TIMESTAMP() 函数把时间戳和日期相互转换。
  • 存储时对当前的时区进行转换,检索时在转换回当前的时区。
  • 如果插入时没有指定第一个 TIMESTAMP 列的值,MySQL 则设置这个列的值为当前时间。在插入一行记录时,MySQL 默认也会更新第一个 TIMESTAMP 的列(除非在 UPDATE 语句中明确指定的值)。
  • TIMESTAMP 列的默认值为 NOT NULL 这和其他数据类型不一样。

四、数据库中价格应该怎么存的,为什么不用浮点数,如何证明浮点数不精确

mysql> CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));
Query OK, 0 rows affected (0.29 sec)

mysql> insert into test values(131072.32,131072.32);
Query OK, 1 row affected (0.07 sec)

mysql> select * from test;
+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+
1 row in set (0.00 sec)

从上面的例子中我们看到c1列的值由131072.32变成了131072.31,这就是浮点数的不精确性造成的。

在MySQL中float、double(或real)是浮点数,decimal(或numberic)是定点数。

>> (10015.8*100.0).to_i
=> 1001579
>> 10015.8*100.0
=> 1001579.9999999999
>>  1001580.0.to_i
=> 1001580

今后关于浮点数和定点数的应用中,大家要记住以下几点:

  • 浮点数存在误差问题。

  • 对货币等对精度敏感的数据,应该用定点数表示或存储;也可以用 BIGINT 代替 DECIMAL,将需要存储的货币单位根据最小的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分,则可以把所有金额乘以一百万。

  • 编程中,如果用到浮点数,要特别注意误差问题,并尽量避免做浮点数比较。

  • 要注意浮点数中一些特殊值的处理。

为 MySQL 选择合适的数据类型
浮点数结构详解
浮点数的二进制表示
浮点数的二进制表示学习笔记

五、int 后面的数值和 varchar 后面的数值的区别

  • MySQL 可以为整数类型指定宽度,例如 INT(11),对大多数应用来说是没有意义的。它不会限制值的合法范围,只是规定了 MySQL 的一些交互工具,用来显示字符的个数。对于存储和计算来说,INT(1) 和 INT(20) 是相同的

  • 但是对于 varchar 类型来说,其后面显示的数值,表示其可以插入多少个字符串。

mysql> create table int_test ( int_01 int(1), int_02 int(20));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into int_test (int_01, int_02) value ( 123456, 123456);
Query OK, 1 row affected (0.04 sec)

mysql> select * from int_test;
+--------+--------+
| int_01 | int_02 |
+--------+--------+
| 123456 | 123456 |
+--------+--------+
1 row in set (0.02 sec)
mysql> create table varchar_test ( varchar_01 varchar(5), varchar_02 varchar(10));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into varchar_test (varchar_01, varchar_02) value ("11111", "1111111");
Query OK, 1 row affected (0.01 sec)

mysql> insert into varchar_test (varchar_01, varchar_02) value ("111112", "1111111");
ERROR 1406 (22001): Data too long for column 'varchar_01' at row 1
mysql> 

六、varchar 和 char 的区别

VARCHAR:

  • VARCHAR 类型用于存储可变长字符串,是最常见的字符串数据类型。 它比定长类型更节省空间,因为它仅使用必要的空间
  • VARCHAR 需要使用 1-2 个额外的字节记录字符串的长度。 如果列的最大长度小于或等于 255 个字节,则只使用 1 个字节来表示长度,否则使用 2 个字节。 所以 VARCHAR(1000) 的列则需要 1002 个字节。
  • 在 5.0 或更好版本,MySQL 在存储和检索时会保留末尾空格。
  • InnoDB 则更灵活,它可以把过长的 VARCHAR 存储为 BLOB

CHAR:

  • CHAR 类型是定长的,MySQL 总是根据定义的字符串长度分配足够的空间。
  • CHAR 适合存储很短的字符串,或者所有值都很接近的一个长度。例如 CHAR 就非常合适存储密码的 MD5 值,因为这个是定长的。
  • 对于经常变更的数据,CHAR 也比 VARCHAR 更好,因为定长的 CHAR 类型不容易产生碎片
  • 对于非常短的列,CHAR 比 VARCHAR 在存储空间上也更有效率。例如用 CHAR(1) 来存储只有 Y 和 N 的只,如果采用单字节字符集只需要一个字节,但是 VARCHAR(1) 却需要两个字节,因为还有一个用来记录长度。
CREATE TABLE char_test(
    char_col1 CHAR(1),
    char_col2 CHAR(10),
    varchar_col1 CHAR(1),
    varchar_col2 CHAR(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO char_test VALUES ('Y', 'string1', "Y", "string1"), ('Y', ' string1', "Y", " string1"), ('Y', 'string1 ', "Y", "string1 ");


SELECT LENGTH(char_col1), LENGTH(varchar_col1), CONCAT("'", char_col2, "'") FROM char_test;
SELECT LENGTH(char_col1), LENGTH(varchar_col1), CONCAT("'", varchar_col2, "'") FROM char_test;

七、varchar 最大的存储空间

字符是指计算机中使用的字母、数字、汉字和符号。

字节是计算机信息技术中存储容量的一种计量单位,也表示一些计算机编程语言中的数据类型和语言字符。

UTF-8 一个汉字=3 个字节,英文是一个字节 GBK 一个汉字=2 个字节,英文是一个字节

由于 MySQL 的记录行长度是有限制的,不是无限长的,这个长度是 64K,即 65535 个字节,对所有的表都是一样的。

在 UTF-8 状态下,汉字最多可以存 21844 个字符串,英文也为 21844 个字符串。
在 GBK 状态下,汉字最多可以存 32766 个字符串,英文也为 32766 个字符串。

varchar(n) 其中 n 表示字符,无论汉字和英文,MySQL 都能存入 n 个字符,但实际存入其中的字节长度有所区别。

  • MySQL 对于变长类型的字段会有 1-2 个字节来保存字符长度。
  • 当字符数小于等于 255 时,MySQL 只用 1 个字节来记录,因为 2 的 8 次方减 1 只能存到 255。 当字符数多余 255 时,就得用 2 个字节来存长度了。
  • 在 UTF-8 状态下的 varchar,最大只能到 (65535 - 2) / 3 = 21844 余 1。
  • 在 GBK 状态下的 varchar, 最大只能到 (65535 - 2) / 2 = 32766 余 1。

MySQL 数据库 varchar 到底可以存多少个汉字,多少个英文呢?我们来搞搞清楚

八、数据库碎片是什么东西,有什么影响

MySQL 具有相当多不同种类的存储引擎来实现列表中的数据存储功能。每当 MySQL 从你的 列表中删除了一行内容,该段空间就会被留空。而在一段时间内的大量删除操作, 会使这种留空的空间变得比存储列表内容所使用的空间更大。 当 MySQL 对数据进行扫描时,它扫描的对象实际是列表的容量需要求的上限 也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作, MySQL 将尝试利用这些留空的区域,但仍然无法将其彻底占用。

这就是为什么不建议删除数据库的数据。但是你可以使用 一个状态来描述,描述该状态是否可用

data_free 表示我们删除后所产生的留空空间。

浅析 MySQL 数据碎片的产生

查看线上数据库中 Table Infomation 时发现有一个日志表数据大小和索引大小有 915M,但实际上行数只有 92 行。 该表需要频繁插入并且会定时去删掉旧的记录。该表上建立了索引,所以应该是产生了大量的碎片 使用 Optimize table 表明,优化后大小变为 2.19M,少了很多,同时可以看出该表上的索引建的多余,因为插入操作比查询操作要多 很多,而且查询不多,查询的数量也变小。

mysql-suipian-youhua

九、什么情况下会使用 binary 查询

BINARY 不是函数,是类型转换运算符,它用来强制它后面的字符串为一个二进制字符串,可以理解为在字符串比较的时候区分大小写

因为有的 MySQL 特别是 4.x 以前以前的对于字符串检索不准确,所以在检索的时候加上 binary

MySQL 中 字符串和字符串的比较是不靠谱的,在二进制层次上比较靠谱

It an efficient have of comparing byte to byte instead of character to character

create table user_test(
  id int(9) unsigned NOT NULL auto_increment,
  username varchar(30) NOT NULL default '',
  primary key (id)
);


INSERT INTO user_test (username) VALUES('美文');
INSERT INTO user_test (username) VALUES('美国项目');
INSERT INTO user_test (username) VALUES('李文');
INSERT INTO user_test (username) VALUES('老唐');
INSERT INTO user_test (username) VALUES('梦漂');
INSERT INTO user_test (username) VALUES('龙武');
INSERT INTO user_test (username) VALUES('夏');


mysql> select * from user_test;
+----+--------------+
| id | username     |
+----+--------------+
|  1 | 美文         |
|  2 | 美国项目     |
|  3 | 李文         |
|  4 | 老唐         |
|  5 | 梦漂         |
|  6 | 龙武         |
|  7 | 夏           |
+----+--------------+
7 rows in set (0.00 sec)


mysql> select * from user_test where username =  '夏';
+----+----------+
| id | username |
+----+----------+
|  7 | 夏       |
+----+----------+
1 row in set (0.00 sec)

mysql> select * from user_test where username =  '夏 ';
+----+----------+
| id | username |
+----+----------+
|  7 | 夏       |
+----+----------+
1 row in set (0.00 sec)

mysql> select * from user_test where username = BINARY '夏';
+----+----------+
| id | username |
+----+----------+
|  7 | 夏       |
+----+----------+
1 row in set (0.00 sec)

mysql> select * from user_test where username = BINARY '夏 ';
Empty set (0.00 sec)


如果有长字符串的比较,的另一种方案。可以适当添加一个字段,来存储该字符串的 crc32 值。 先比较 crc32 值,然后再比较字符串值。提高比较的速度和准确性。

crc = crc32("Hello")
=> 4157704578

MySQL 的 binary 解决 MySQL 数据大小写敏感问题的方法
Why the 'BINARY' in a SELECT statement? charset-binary-op

十、VARCHAR(5)和 VARCHAR(200)存储上的区别,排序上的区别

  • 相比较而言,varchar(5) 所占用的空间相对比较小。
  • 在排序时,varchar(5) 所占用的空间相对比 varchar(200) 要少。

十一、1000w 的数据,如果在 ORDER BY 中使用到 varchar(1000) 这个列,并且查询扫描整个表,为了排序就需要超过 30GB 的临时表

最坏情况下的长度分配对于排序也是一样的。 意思就是在排序的时候使用的是分配长度的最大长度。

1 GB = 1000 MB = 1 000 000 KB = 1 000 000 000 B
1 MB = 1 000KB = 1 000 000 B

3000 * 10 000 000 = 30 000 000 000 B = 30GB

十二、排序 使用 FIND_IN_SET 进行按顺序排序

mysql>  explain
    ->  select id, title
    ->  from happy_for_ni_deals t
    ->  where t.id in (1014694, 1014693, 1014697, 1014691)
    ->  order by FIND_IN_SET(t.id, '1014694,1014693,1014697,1014691');
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t     | range | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------------+
1 row in set (0.01 sec)

4、
mysql>  explain
    ->  select id, title
    ->  from happy_for_ni_deals t
    ->  where t.id in (1014694, 1014693, 1014697, 1014691);
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | range | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.01 sec)

十三、通用的设计实践,在“查找表”时采用整数主键而避免采用基于字符串的值进行关联

由于根据字符串的关联,在查找排序的时候,需要占用更多的空间,消耗更多的资源。 theory-of-mysql-index

十四、选择标识符

  • 标识列选择数据类型时,应该选择跟关联表中的对应列一样的类型。

  • 混用不同数据类型可能导致性能问题,及时没有性能影响,在比较操作时饮食类型转换也可以能导致很难发现的错误。 对于完全“随机”的字符串也需要多加注意,

  • 例如 MD5(),SHA1()或 UUID() 产生的字符串,

这些函数生成的新值会任意分布在很大的空间内,这会导致 INSERT 以及一些 SELECT 语句变得很慢 使用 UUID() 并非一无是处,在分布式数据库中,使用 UUID() 是一个不错的选择。

MySQL 用 UUID 作为主键,实际使用中有什么问题

下面例子中关联查询的时候,使用一个字符串和 int 型主键 ID 进行串联。会导致索引使用不上。

mysql> EXPLAIN SELECT *
    -> FROM happy_for_ni_tb_shops AS t
    -> INNER JOIN candidate_happy_for_ni_deals AS c_d
    -> WHERE t.id = c_d.cid \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c_d
         type: ALL
possible_keys: idx_cid
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Range checked for each record (index map: 0x2)
2 rows in set (0.07 sec)

MySQL 为什么需要一个主键

十五、特殊类型数据

MySQL 提供 INET_ATON() 和 INET_NEOA() 函数在 IP 地址上进行转换

mysql> SELECT INET_ATON('209.207.224.40'), INET_NTOA(3520061480) FROM dual;
+-----------------------------+-----------------------+
| INET_ATON('209.207.224.40') | INET_NTOA(3520061480) |
+-----------------------------+-----------------------+
|                  3520061480 | 209.207.224.40        |
+-----------------------------+-----------------------+
1 row in set (0.02 sec)

十六、JOIN 驱动表

  • inner join 驱动顺序由优化器自己制定,如果优化器选择有误可以使用 straight_join 自己指定驱动顺序以达到优化的目的。

  • left join 驱动顺序是固定的,left join 左边的表为驱动表,右边为匹配表,right join 则刚好相反。

  • 存在 group by 或者 order by 子句的关联查询中,如果引用的字段是驱动表的字段那么分组或者排序是可以使用到索引的。但是如果引用的是其他匹配表的字段,那么分组或者排序动作则无法使用索引。

  • 内连接就是检索出与连接条件完全匹配的数据行;而外连接则保留了所有驱动表的数据,匹配表中无法匹配的数据则以 null 输出。

  • 驱动表的结果集,越小越好。

join-query-in-mysql

十七、从父表冗余一些数据到子表的理由是排序的需要,虽然不符合范式要求。

happy_for_ni_deals 表和 happy_for_ni_tb_shops 表的关联查询,但是最后使用了 happy_for_ni_tb_shops.grade 做 desc

EXPLAIN SELECT  `happy_for_ni_deals`.*
FROM `happy_for_ni_deals`
  INNER JOIN `happy_for_ni_tb_shops`
  ON `happy_for_ni_tb_shops`.`id` = `happy_for_ni_deals`.`happy_for_ni_tb_shop_id`
    LEFT JOIN candidate_happy_for_ni_deals
    on happy_for_ni_deals.candidate_happy_for_ni_deal_id = candidate_happy_for_ni_deals.id
      LEFT JOIN happy_for_ni_deal_infos
      on happy_for_ni_deal_infos.happy_for_ni_deal_id = happy_for_ni_deals.id
WHERE ( happy_for_ni_deals.bg_tag_id > 0 )
  AND (happy_for_ni_deals.complete_status = 1)
ORDER BY happy_for_ni_tb_shops.grade DESC, happy_for_ni_deals.id DESC LIMIT 20 OFFSET 0

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: happy_for_ni_deals
         type: range
possible_keys: index_happy_for_ni_deals_on_happy_for_ni_tb_shop_id_and_id,idx_bg_tag_pub_beg,idx_bg_tag_pub_end
          key: idx_bg_tag_pub_beg
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using index condition; Using where; Using temporary; Using filesort  <- 坏味道,temporary && filesort

happy_for_ni_tb_shops表上的 grade 赋值到 happy_for_ni_deals 上,在代码中将happy_for_ni_tb_shopsgrade 逻辑也同步到happy_for_ni_deals上 然后加索引。这样排序,会快一些。

ALTER TABLE happy_for_ni_deals ADD COLUMN grade int(11) not null;
ALTER TABLE happy_for_ni_deals ADD INDEX `idx_of_grade` (grade, id);
EXPLAIN SELECT  `happy_for_ni_deals`.*
FROM `happy_for_ni_deals`
  INNER JOIN `happy_for_ni_tb_shops`
  ON `happy_for_ni_tb_shops`.`id` = `happy_for_ni_deals`.`happy_for_ni_tb_shop_id`
    LEFT JOIN candidate_happy_for_ni_deals
    on happy_for_ni_deals.candidate_happy_for_ni_deal_id = candidate_happy_for_ni_deals.id
      LEFT JOIN happy_for_ni_deal_infos
      on happy_for_ni_deal_infos.happy_for_ni_deal_id = happy_for_ni_deals.id
WHERE ( happy_for_ni_deals.bg_tag_id > 0 )
  AND (happy_for_ni_deals.complete_status = 1)
ORDER BY happy_for_ni_deals.grade DESC, happy_for_ni_deals.id DESC LIMIT 20 OFFSET 0

*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: happy_for_ni_deals
         type: ref
possible_keys: index_happy_for_ni_deals_on_happy_for_ni_tb_shop_id_and_id,idx_bg_tag_pub_beg,idx_bg_tag_pub_end
          key: index_happy_for_ni_deals_on_happy_for_ni_tb_shop_id_and_id
      key_len: 4
          ref: tao800_test9.happy_for_ni_tb_shops.id
         rows: 1
        Extra: Using where

十八、MySQL 视图

  • 什么是视图?视图是由查询结果形成的一张虚拟表。
  • 什么时候要用到视图?如果某个查询结果出现的非常频繁,也就是说,要经常拿到这个查询结果来做子查询。
  • 视图的创建语法:

    create view 视图名 as select 语句;
    
  • 使用视图有什么好处? ** 1、简化查询语句。将经常用到的子查询做成视图。** ** 2、可以进行权限控制。视图里只开放部分数据列。** ** 3、大数据分表时可以用到。**

MySQL 视图学习总结 MySQL 之视图

十九、更快地读,更慢的写。高性能数据库的设计

为了提升查询的速度,经常会需要见一些额外的索引,增加冗余列,甚至是创建缓存表和汇总表。这些方法会增加写查询的负担,也需要额外的维护任务,但是在设计高性能数据库时,这些都是最常见的技巧。虽然写操作变得更慢了,但更显著地提高了读操作的性能

二十、加快 ALTER TBALE 操作的速度

ALTER TABLE 操作需要花费数小时时间甚至数天才能完成。
大部分 ALTER TABLE 操作将导致 MySQL 服务中断。
不是所有的 ALTER TABLE 操作都会引起表重建。

ALTER TABLE sakila.film MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

所有的MODIFY COLUMN 操作都将导致表重建。所以这种操作是很慢的。

ALTER TABLE sakila.film ALTER COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

这个语句会直接修改.frm 文件而不设计表数据。所以,这个操作是非常快的。

ALTER TABLE 允许使用 ALTER COLUMN、MODIFY COLUMN 和 CHANGE COLUMN 语句修改列,这三种操作都是不一样的。

二十一、给大表添加索引或字段该如何操作

在构建索引的工作被延迟到数据完全载入以后,这个时候已经可以通过排序来构建索引了。这样做会快很多,并且使得索引树的碎片更少、更紧凑。

1、clone 新表

2、先删除所有的非唯一性索引,然后增加新的列,最后重新创建删除掉的索引。

3、交换新表和旧表

挺实用的总结,多谢楼主分享

– 输出时保留 null, 但排序时转换为 0 :

select name, points

from users

order by coalesce(points, 0) desc;

这样 order by 就无法利用索引了,可能用 union 会好一些。

金钱比较问题的一个 tips 是可以使用 to_d 来比较,比如order.total > params[:total].to_d,前提是 total 已经存成了 decimal。

NULL 排序 楼主可以看看 order 的时候指定 null: last / first 顺便 颇有收获 多谢

#2 楼 @hooopo 在索引列上,排序时使用函数,肯定是不能用到索引的。 这里亦修改成这样。

SELECT name, points
FROM users
WHERE points is not null
ORDER BY points DESC

金钱比较。我还是建议在数据库中,使用整形来存储,显示时可以自定义 format 展示为浮点数格式,比较时,使用原始的整数进行比较即可。

#5 楼 @ibugs 我的意思是:

(SELECT name, points
FROM users
WHERE points IS NOT NULL
ORDER BY points desc)
UNION
(SELECT name, points
FROM users
WHERE points IS  NULL) 

decimal 就是不用自己格式化了,也没其他副作用。

@hooopo 👍,UNION 是一种不错的方式。

#3 楼 @so_zengtao 在排序时,null 值被认为是最大的,这里修改了下 在排序时,null 值被认为是最大的(Oracle)

SQLServer 和 MySQL 认为 null 最小,Oracle 认为 null 最大

3Q , 【NULLS】Oracle 对 SQL 排序后 NULL 值位置的“特殊关照”

NULLS FIRST / NULLS LAST 是 Oracle 的命令,MySQL 并没有类似命令,只能通过其他方式实现。

#8 楼 @ibugs 好的 谢谢指导

@so_zengtao 不用谢。共勉

楼主的笔记很实用。 对 MySQL 无爱,更喜欢 PostgreSQL。

@winnie MySQL 运用挺广泛的

干货,谢谢

#11 楼 @winnie 不错,收货不少。

You need to Sign in before reply, if you don't have an account, please Sign up first.