MySQL深入浅出2

MySQL深入浅出

TEXT 与BLOB

一般在保存少量字符串的时候，我们会选择CHAR 或者VARCHAR；而在保存较大文本时，
通常会选择使用TEXT 或者BLOB，二者之间的主要差别是BLOB 能用来保存二进制数据，比
如照片；而TEXT 只能保存字符数据，比如一篇文章或者日记。

删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上
会有影响。为了提高性能，建议定期使用OPTIMIZE TABLE 功能对这类表进行碎片整理，避
免因为“空洞”导致性能问题。

• 可以使用合成的（Synthetic）索引来提高大文本字段（BLOB 或TEXT）的查询性能。
  简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的
  数据列中，接下来就可以通过检索散列值找到数据行了。但是，要注意这种技术只能用于精
  确匹配的查询（散列值对于类似<或>=等范围搜索操作符是没有用处的）。可以使用MD5()
  函数生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列
  值。请记住数值型散列值可以很高效率地存储。

上面的例子展示了合成索引的用法，由于这种技术只能用于精确匹配，在一定程度上减
少I/O，从而提高查询效率。如果需要对BLOB 或者CLOB 字段进行模糊查询，MySQL 提供了
前缀索引，也就是只为字段的前n 列创建索引，举例如下：

mysql> create index idx_blob on t(context(100));

可以发现，对context 前100 个字符进行模糊查询，就可以用到前缀索引。请注意，这里的
查询条件中，“%”不能放在最前面，否则索引将不会被使用。

浮点数和定点数

浮点数一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型后，如果插入
128
数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插
入，四舍五入的过程不会报错。在MySQL 中float、double（或real）用来表示浮点数。

定点数不同于浮点数，定点数实际上是以字符串形式存放的，所以定点数可以更加精确
的保存数据。如果实际插入的数值精度大于实际定义的精度，则MySQL 会进行警告（默认
的SQLMode 下），但是数据按照实际精度四舍五入后插入；如果SQLMode 是在TRADITIONAL
（传统模式）下，则系统会直接报错，导致数据无法插入。在MySQL中，decima（l 或numberic）
用来表示定点数。

注意：在今后关于浮点数和定点数的应用中，用户要考虑到以下几个原则：
- 浮点数存在误差问题；
- 对货币等对精度敏感的数据，应该用定点数表示或存储；
- 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；
- 要注意浮点数中一些特殊值的处理。

索引的设计和使用

索引在创建表的时候可以同时创建，也可以随时增加新的索引。创建新索引的语法为：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[USING index_type]

ON tbl_name (index_col_name,…)

—————————————————

index_col_name:

col_name [(length)] [ASC | DESC]

例如，要为city 表创建了10 个字节的前缀索引，语法是：

mysql> create index cityname on city (city(10));

索引的删除语法为：
DROP INDEX index_name ON tbl_name

设计索引的原则

• 最适合索引的列是出现在WHERE
  子句中的列，或连接子句中指定的列，而不是出现在SELECT 关键字后的选择列表中的列。
• 使用惟一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。例
  如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“ M”
  和“F”，则对此列进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行。

• 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应
  该这样做。例如，如果有一个CHAR(200)列，如果在前10 个或20 个字符内，多数值是惟一
  的，那么就不要对整个列进行索引。

• 利用最左前缀。在创建一个n 列的索引时，实际是创建了MySQL 可利用的n 个索引。
  多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为
  最左前缀。

**而对于BTREE 索引，当使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE ‘pattern’（其
中’pattern’不以通配符开始）操作符时，都可以使用相关列上的索引。**

当对索引字段进行范围查询的时候，只有BTREE 索引可以通过索引访问。

mysql> explain SELECT * FROM city WHERE country_id > 1 and country_id < 10 
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
type: range
possible_keys: idx_fk_country_id
key: idx_fk_country_id
143
key_len: 2
ref: NULL
rows: 24
Extra: Using where
1 row in set (0.00 sec)

触发器

创建触发器

创建触发器的语法如下：

CREATE TRIGGER trigger_name 

trigger_time 

trigger_event

ON tbl_name 

FOR EACH ROW trigger_stmt

其中trigger_time 是触发器的触发时间，可以是BEFORE 或者AFTER，BEFORE 的含义指在检
查约束前触发，而AFTER 是在检查约束后触发。
而trigger_event 就是触发器的触发事件，可以是INSERT、UPDATE 或者DELETE。

对同一个表相同触发时间的相同触发事件，只能定义一个触发器。例如，对某个表的不同字
段的AFTER 更新触发器，在使用Oracle 数据库的时候，可以定义成两个不同的UPDATE 触发
器，更新不同的字段时触发单独的触发器，但是在MYSQL 数据库中，只能定义成一个触发
器，在触发器中通过判断更新的字段进行对应的处理。

使用别名OLD 和NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。
现在触发器还只支持行级触发的，不支持语句级触发。

在样例数据库中，为film 表创建了AFTER INSERT 的触发器，具体如下：
DELIMITER $$
CREATE TRIGGER ins_film
AFTER INSERT ON film FOR EACH ROW BEGIN
INSERT INTO film_text (film_id, title, description)
VALUES (new.film_id, new.title, new.description);
END;
$$
delimiter ;
插入film 表记录的时候，会向film_text 表中也插入相应的记录。
mysql> INSERT INTO film VALUES
-> (1001,'ACADEMY DINOSAUR',
-> 'A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian
Rockies',
-> 2006,1,NULL,6,'0.99',86,'20.99','PG','Deleted Scenes,Behind the
Scenes','2006-02-15 05:03:42');
Query OK, 1 row affected (0.05 sec)
mysql> select * from film_text where film_id=1001 \G
*************************** 1. row ***************************
film_id: 1001
title: ACADEMY DINOSAUR
description: A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in
The Canadian Rockies
1 row in set (0.00 sec)

删除触发器

一次可以删除一个触发程序，如果没有指定schema_name，默认为当前数据库,具体语法如
下：

DROP TRIGGER [schema_name.]trigger_name

例如，要删除film 表上的触发器ins_film，可以使用以下命令：

mysql> drop trigger ins_film;

查看触发器

可以通过执行SHOW TRIGGERS 命令查看触发器的状态、语法等信息，

MySQL 的触发器是按照BEFORE 触发器、行操作、AFTER 触发器的顺序执行的，其中任何一
步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作，那么会整个作为
一个事务被回滚（Rollback），但是如果是对非事务表进行的操作，那么已经更新的记录将无
法回滚，这也是设计触发器的时候需要注意的问题。

MySQL技巧

使用rand()函数提取随机行

而在MySQL中，产生随机数的方法是RAND()
函数。可以利用这个函数与ORDER BY子句一起完成随机抽取某些行的功能。它的原理其实
就是ORDER BY RAND()能够把数据随机排序。

例如，可按照随机顺序检索数据行：

mysql> select * from sales2 order by rand();

如果想随机抽取一部分样本的时候，就可以把数据随机排序后再抽取前n 条记录就可以了，比如：

mysql> select * from sales2 order by rand() limit 5;

sql优化

通过show status 命令了解各种SQL 的执行频率

MySQL 客户端连接成功后，通过show [session|global] status 命令可以提供服务器状态信息。show [session|global] status可以根据需要加上参数“session”或者“global”来显示session级（当前连接）的统计结果和global级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

下面的命令显示了当前session 中所有统计参数的值：

mysql> show status like 'Com_%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_event | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
……

Com_xxx 表示每个xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。
- Com_select：执行select 操作的次数，一次查询只累加1。
- Com_insert：执行INSERT 操作的次数，对于批量插入的INSERT 操作，只累加一次。
- Com_update：执行UPDATE 操作的次数。
- Com_delete：执行DELETE 操作的次数。
上面这些参数对于所有存储引擎的表操作都会进行累计。

通过EXPLAIN 分析低效SQL 的执行计划

mysql> explain select sum(moneys) from sales a,company b where a.company_id = b.id and a.year
= 2006\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: ind_company_id
key: ind_company_id
key_len: 5
ref: sakila.a.company_id
rows: 1
Extra: Using where; Using index
2 rows in set (0.00 sec)

每个列的简单解释如下：
- select_type：表示SELECT 的类型，常见的取值有SIMPLE（简单表，即不使用表连接
或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或
者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。
- table：输出结果集的表。
- type：表示表的连接类型，性能由好到差的连接类型为system（表中仅有一行，即
常量表）、const（单表中最多有一个匹配行，例如primary key 或者unique index）、
eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接
中使用primary key或者unique index）、re（f 与eq_ref类似，区别在于不是使用primary
key 或者unique index，而是使用普通的索引）、ref_or_null（与ref 类似，区别在于
条件中包含对NULL 的查询）、index_merge(索引合并优化)、unique_subquery（in
的后面是一个查询主键字段的子查询）、index_subquery（与unique_subquery 类似，
区别在于in 的后面是查询非唯一索引字段的子查询）、range（单表中的范围查询）、
index（对于前面的每一行，都通过查询索引来得到数据）、all（对于前面的每一行，都通过全表扫描来得到数据）。
- possible_keys：表示查询时，可能使用的索引。
- key：表示实际使用的索引。
- key_len：索引字段的长度。
- rows：扫描行的数量。
- Extra：执行情况的说明和描述。

MySQL如何使用索引

在MySQL 中，下列几种情况下有可能使用到索引。

1. 对于创建的多列索引，只要查询的条件中用到了最左边的列，索引一般就会被使用，
   举例说明如下。

首先按company_id，moneys 的顺序创建一个复合索引，具体如下：

mysql> create index ind_sales2_companyid_moneys on sales2(company_id,moneys);

然后按company_id 进行表查询，具体如下：

mysql> explain select * from sales2 where company_id = 2006;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ref
possible_keys: ind_sales2_companyid_moneys
key: ind_sales2_companyid_moneys
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

可以发现即便where 条件中不是用的company_id 与moneys 的组合条件，索引仍然能
用到，这就是索引的前缀特性。但是如果只按moneys 条件查询表，那么索引就不会
被用到。
2. 对于使用like 的查询，后面如果是常量并且只有％号不在第一个字符，索引才可能会
被使用。
3. 如果列名是索引，使用column_name is null 将使用索引。如下例中查询name 为null
的记录就用到了索引：

mysql> explain select * from company2 where name is null\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

在下列情况下，虽然存在索引，但是MySQL 并不会使用相应的索引。
1. 如果MySQL 估计使用索引比全表扫描更慢，则不使用索引。
2. 如果使用MEMORY/HEAP 表并且where 条件中不使用“=”进行索引列，那么
不会用到索引。heap 表只有在“=”的条件下才会使用索引。
3. 用or 分割开的条件，如果or 前的条件中的列有索引，而后面的列中没有索引，
那么涉及到的索引都不会被用到
4. 如果不是索引列的第一部分
5. 如果like 是以％开始
6. 如果列类型是字符串，那么一定记得在where 条件中把字符常量值用引号引
起来，否则的话即便这个列上有索引，MySQL 也不会用到的，因为，MySQL 默认把输入的
常量值进行转换以后才进行检索。

查看索引使用情况

如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的
次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值
的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，
Handler_read_rnd_next 的值较高，则通常说明表索引不正确或写入的查询没有利用索引，具
体如下。

mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 5 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 2055 |
+-----------------------+-------+
6 rows in set (0.00 sec)

从上面的例子中可以看出，目前使用的MySQL 数据库的索引情况并不理想。

通过拆分提高表的访问效率

第一种方法是垂直拆分，即把主码和一些列放到一个表，然后把主码和另外的列放
到另一个表中。

如果一个表中某些列常用，而另外一些列不常用，则可以采用垂直拆分，另外垂直
拆分可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少I/O 次
数。其缺点是需要管理冗余列，查询所有数据需要联合（JOIN）操作。

第二种方法是水平拆分，即根据一列或多列数据的值把数据行放到两个独立的表中。
水平拆分通常在以下几种情况下使用：
- 表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了
索引的层数，提高查询速度。
- 表中的数据本来就有独立性，例如，表中分别记录各个地区的数据或不同时期
的数据，特别是有些数据常用，而另外一些数据不常用。
- 需要把数据存放到多个介质上。
例如，移动电话的账单表就可以分成两个表或多个表。最近3 个月的账单数据存在一
个表中，3 个月前的历史账单存放在另外一个表中，超过1 年的历史账单可以存储到
单独的存储介质上，这种拆分是最常使用的水平拆分方法。

水平拆分会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需
要UNION 操作。