MySQL LIMIT语句优化

MYSQL的优化是非常重要的。其他最常用也最需要优化的就是limit。mysql的limit给分页带来了极大的方便,但数据量一大的时候,limit的性能就急剧下降。
同样是取10条数据

select * from yanxue8_visit limit 10000,10

select * from yanxue8_visit limit 0,10
就不是一个数量级别的。

网上也很多关于limit的五条优化准则,都是翻译自mysql手册,虽然正确但不实用。今天发现一篇文章写了些关于limit优化的,很不错。

文中不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据,明显要好于直接使用limit。这里我具体使用数据分两种情况进行测试。(测试环境win2033+p4双核 (3GHZ) +4G内存 mysql 5.0.19)

1、offset比较小的时候。

select * from yanxue8_visit limit 10,10
多次运行,时间保持在0.0004-0.0005之间

Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10,1
) limit 10
多次运行,时间保持在0.0005-0.0006之间,主要是0.0006
结论:偏移offset较小的时候,直接使用limit较优。这个显然是子查询的原因。

2、offset大的时候。

select * from yanxue8_visit limit 10000,10
多次运行,时间保持在0.0187左右

Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10000,1
) limit 10

多次运行,时间保持在0.0061左右,只有前者的1/3。可以预计offset越大,后者越优。

附上原文:

select * from table LIMIT 5,10; #返回第6-15行数据
select * from table LIMIT 5; #返回前5行
select * from table LIMIT 0,5; #返回前5行

性能优化:

基于MySQL5.0中limit的高性能,我对数据分页也重新有了新的认识.

1.
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90001
) As tmp
) limit 100;

2.
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90000,1
) As tmp
) limit 100;

同样是取90000条后100条记录,第1句快还是第2句快?
第1句是先取了前90001条记录,取其中最大一个ID值作为起始标识,然后利用它可以快速定位下100条记录
第2句择是仅仅取90000条记录后1条,然后取ID值作起始标识定位下100条记录
第1句执行结果.100 rows in set (0.23) sec
第2句执行结果.100 rows in set (0.19) sec

很明显第2句胜出.看来limit好像并不完全像我之前想象的那样做全表扫描返回limit offset+length条记录,这样看来limit比起MS-SQL的Top性能还是要提高不少的.

其实第2句完全可以简化成

Select * From cyclopedia Where ID>=(
Select ID From cyclopedia limit 90000,1
)limit 100;

直接利用第90000条记录的ID,不用经过Max运算,这样做理论上效率因该高一些,但在实际使用中几乎看不到效果,因为本身定位ID返回的就是1条记录,Max几乎不用运作就能得到结果,但这样写更清淅明朗,省去了画蛇那一足.

可是,既然MySQL有limit可以直接控制取出记录的位置,为什么不干脆用Select * From cyclopedia limit 90000,1呢?岂不更简洁?
这样想就错了,试了就知道,结果是:1 row in set (8.88) sec,怎么样,够吓人的吧,让我想起了昨天在4.1中比这还有过之的”高分”.Select * 最好不要随便用,要本着用什么,选什么的原则, Select的字段越多,字段数据量越大,速度就越慢. 上面2种分页方式哪种都比单写这1句强多了,虽然看起来好像查询的次数更多一些,但实际上是以较小的代价换取了高效的性能,是非常值得的.

第1种方案同样可用于MS-SQL,而且可能是最好的.因为靠主键ID来定位起始段总是最快的.

Select Top 100 * From cyclopedia Where ID>=(
Select Top 90001 Max(ID) From (
Select ID From cyclopedia Order By ID
) As tmp
)

但不管是实现方式是存贮过程还是直接代码中,瓶颈始终在于MS-SQL的TOP总是要返回前N个记录,这种情况在数据量不大时感受不深,但如果成百上千万,效率肯定会低下的.相比之下MySQL的limit就有优势的多,执行:
Select ID From cyclopedia limit 90000
Select ID From cyclopedia limit 90000,1
的结果分别是:
90000 rows in set (0.36) sec
1 row in set (0.06) sec
而MS-SQL只能用Select Top 90000 ID From cyclopedia 执行时间是390ms,执行同样的操作时间也不及MySQL的360ms.

个人测试分析:

这个例子非常经典,但个人感觉说明不详细,经分析,总结如下(其中,id为主键):
主要值得注意的有好几项:

第一:
SELECT * FROM tbl_name LIMIT 1000000,10
SELECT id FROM tbl_name LIMIT 1000000,10
第一条SQL没有能使用主键进行索引,而第二条则使用了,经测试,使用主键进行索引反而变慢,前者大约快一倍。
如果把第二条改为:
SELECT id FROM tbl_name IGNORE INDEX(primary) LIMIT 1000000,10

速度就一样了,因此,估计是因为多了对索引文件(这里是主键)的相关操作.不使用的话,就直接对主表进行扫描就行。

第二:
在第一点的基础上,加上ORDER BY,情况就完全不同了:
SELECT * FROM tbl_name ORDER BY id DESC LIMIT 1000000,10
SELECT id FROM tbl_name ORDER BY id DESC LIMIT 1000000,10
由于第二条SQL使用了索引,ORDER BY就能使用该索引,效果明显,前者需要5秒多,后者只要 0.6秒多点

第三:
这里解释一下引文中的例子:
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90000,1
) As tmp
) limit 100;
正如引文所说,该语句可以改写为:
Select * From cyclopedia Where ID>=(
Select ID From cyclopedia Order By ID limit 90000,1
) limit 100;
好,为什么这里会比直接使用 LIMIT 快呢?

正如前两点说的,第一子查询中使用了ORDER BY,因此只使用 `ID`使用查询结果的字段,才会使用索引,如果这里在`ID`后加多一个不在索引内的字段(如:`ID`,`other_field`),那么也要5秒多的时间,这就是第一点快的原因。

第二,外层查询这里虽然使用 ‘*’作为要查询的字段,但由于使用了WHERE ID>=n ,因此使用了索引而作出了快速定位。

而单纯的Select * From cyclopedia ORDER BY `ID` DESC LIMIT 90000,1 由于是要查询的字段使用“*”,另外由于ORDER BY子句并不会使用索引,因此就会慢。

如果改定为:Select `ID` From cyclopedia ORDER BY `ID` DESC LIMIT 90000,1就会变快,但奇怪的是强制使用索引子句Select * From cyclopedia FORCE INDEX(PRIMARY) ORDER BY `ID` DESC LIMIT 90000,1居然无效,这样看来,MySQL的内置优先机制的优先级更高。

MySql的加密算法

1、双向加密

就让我们从最简单的加密开始:双向加密。在这里,一段数据通过一个密钥被加密,只能够由知道这个密钥的人来解密。MySQL有两个函数来支持这种类型的加密,分别叫做ENCODE()和DECODE()。下面是一个简单的实例:

mysql> INSERT INTO users (username, password) VALUES (‘joe’, ENCODE(‘guessme’, ‘abracadabra’)); Query OK, 1 row affected (0.14 sec)

其中,Joe的密码是guessme,它通过密钥abracadabra被加密。要注意的是,加密完的结果是一个二进制字符串,如下所示:

mysql> SELECT * FROM users WHERE username=’joe’;
+———-+———-+
| username | password |
+———-+———-+
| joe | ¡?i??!? |
+———-+———-+
1 row in set (0.02 sec)

abracadabra这个密钥对于恢复到原始的字符串至关重要。这个密钥必须被传递给DECODE()函数,以获得原始的、未加密的密码。下面就是它的使用方法:

mysql> SELECT DECODE(password, ‘abracadabra’) FROM users WHERE username=’joe’;
+———————————+
| DECODE(password, ‘abracadabra’) |
+———————————+
| guessme |
+———————————+
1 row in set (0.00 sec)

应该很容易就看到它在Web应用程序里是如何运行的——在验证用户登录的时候,DECODE()会用网站专用的密钥解开保存在数据库里的密码,并和用户输入的内容进行对比。假设您把PHP用作自己的脚本语言,那么可以像下面这样进行查询:

< ?php $query = "SELECT COUNT(*) FROM users WHERE username='$inputUser' AND DECODE(password, 'abracadabra') = '$inputPass'";?>

提示:虽然ENCODE()和DECODE()这两个函数能够满足大多数的要求,但是有的时候您希望使用强度更高的加密手段。在这种情况下,您可以使用AES_ENCRYPT()和AES_DECRYPT()函数,它们的工作方式是相同的,但是加密强度更高。
2、单向加密

单向加密与双向加密不同,一旦数据被加密就没有办法颠倒这一过程。因此密码的验证包括对用户输入内容的重新加密,并将它与保存的密文进行比对,看是否匹配。一种简单的单向加密方式是MD5校验码。MySQL的MD5()函数会为您的数据创建一个“指纹”并将它保存起来,供验证测试使用。下面就是如何使用它的一个简单例子:

mysql> INSERT INTO users (username, password) VALUES (‘joe’, MD5(‘guessme’)); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM users WHERE username=’joe’;
+———-+———————————-+
| username | password |
+———-+———————————-+
| joe | 81a58e89df1f34c5487568e17327a219 |
+———-+———————————-+
1 row in set (0.02 sec)

现在您可以测试用户输入的内容是否与已经保存的密码匹配,方法是取得用户输入密码的MD5校验码,并将它与已经保存的密码进行比对,就像下面这样:

mysql> SELECT COUNT(*) FROM users WHERE username=’joe’ AND password=MD5(‘guessme’);
+———-+
| COUNT(*) |
+———-+
| 1 |
+———-+
1 row in set (0.00 sec)

或者,您考虑一下使用ENCRYPT()函数,它使用系统底层的crypt()系统调用来完成加密。这个函数有两个参数:一个是要被加密的字符串,另一个是双(或者多)字符的“salt”。它然后会用salt加密字符串;这个salt然后可以被用来再次加密用户输入的内容,并将它与先前加密的字符串进行比对。下面一个例子说明了如何使用它:

mysql> INSERT INTO users (username, password) VALUES (‘joe’, ENCRYPT(‘guessme’, ‘ab’)); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM users WHERE username=’joe’;
+———-+—————+
| username | password |
+———-+—————+
| joe | ab/G8gtZdMwak |
+———-+—————+
1 row in set (0.00 sec)

结果是

mysql> SELECT COUNT(*) FROM users WHERE username=’joe’ AND password=ENCRYPT(‘guessme’, ‘ab’);
+———-+
| COUNT(*) |
+———-+
| 1 |
+———-+
1 row in set (0.00 sec)

提示:ENCRYPT()只能用在*NIX系统上,因为它需要用到底层的crypt()库。

幸运的是,上面的例子说明了能够如何利用MySQL对您的数据进行单向和双向的加密,并告诉了您一些关于如何保护数据库和其他敏感数据库信息安全的理念。

MySQL索引的查看创建和删除

1.索引作用
在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。
例如,有3个未索引的表t1、t2、t3,分别只包含列c1、c2、c3,每个表分别含有1000行数据组成,指为1~1000的数值,查找对应值相等行的查询如下所示。

SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3
此查询结果应该为1000行,每行包含3个相等的值。在无索引的情况下处理此查询,必须寻找3个表所有的组合,以便得出与WHERE子句相配的那些行。而可能的组合数目为1000×1000×1000(十亿),显然查询将会非常慢。
如果对每个表进行索引,就能极大地加速查询进程。利用索引的查询处理如下。
(1)从表t1中选择第一行,查看此行所包含的数据。
(2)使用表t2上的索引,直接定位t2中与t1的值匹配的行。类似,利用表t3上的索引,直接定位t3中与来自t1的值匹配的行。
(3)扫描表t1的下一行并重复前面的过程,直到遍历t1中所有的行。
在此情形下,仍然对表t1执行了一个完全扫描,但能够在表t2和t3上进行索引查找直接取出这些表中的行,比未用索引时要快一百万倍。
利用索引,MySQL加速了WHERE子句满足条件行的搜索,而在多表连接查询时,在执行连接时加快了与其他表中的行匹配的速度。
2. 创建索引
在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。
1.ALTER TABLE
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。
2.CREATE INDEX
CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。
3.索引类型
在创建索引时,可以规定索引能否包含重复值。如果不包含,则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引,这保证单列不包含重复的值。对于多列惟一性索引,保证多个值的组合不重复。
PRIMARY KEY索引和UNIQUE索引非常类似。事实上,PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY,因为一个表中不可能具有两个同名的索引。
下面的SQL语句对students表在sid上添加PRIMARY KEY索引。

ALTER TABLE students ADD PRIMARY KEY (sid)

4. 删除索引
可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

其中,前两条语句是等价的,删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。
如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

5.查看索引
mysql> show index from tblname;
mysql> show keys from tblname;
  · Table

  表的名称。

  · Non_unique

  如果索引不能包括重复词,则为0。如果可以,则为1。

  · Key_name

  索引的名称。

  · Seq_in_index

  索引中的列序列号,从1开始。

  · Column_name

  列名称。

  · Collation

  列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。

  · Cardinality

  索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。

  · Sub_part

  如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。

  · Packed

  指示关键字如何被压缩。如果没有被压缩,则为NULL。

  · Null

  如果列含有NULL,则含有YES。如果没有,则该列含有NO。

  · Index_type

  用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

  · Comment

复制表结构和数据的SQL语句

1.复制表结构及数据到新表
CREATE TABLE 新表
SELECT * FROM 旧表

2.只复制表结构到新表
CREATE TABLE 新表
SELECT * FROM 旧表 WHERE 1=2
即:让WHERE条件不成立.
CREATE TABLE 新表
LIKE 旧表

3.复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO 新表
SELECT * FROM 旧表

4.复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO 新表(字段1,字段2,…….)
SELECT 字段1,字段2,…… FROM 旧表

MySQL日志MySql-bin.0000*的删除方法

MySQL,运行一段时间后,在数据库目录下会自动产生mysql-bin.00000*的日志文件,从mysql-bin.000001开始一直排列下来,占用了大量的硬盘空间,这些日志文件要怎样删除呢?

删除方法:

mysql -u root -p #用ROOT身份登录数据库
reset master; #删除日志,完成

禁止生成日志文件:

vi /etc/my.cnf #编辑MySQL配置文件

找到如下两行并加#号注释掉:

#log-bin=mysql-bin
#binlog_format=mixed

重启MySQL服务:

service mysqld restart

MySQL数据库文件夹中的mysql-bin.00001是什么文件?
mysql-bin.000001、mysql- bin.000002等文件是数据库的操作日志,例如UPDATE一个表,或者DELETE一些数据,即使该语句没有匹配的数据,这个命令也会存储到日志 文件中,还包括每个语句执行的时间,也会记录进去的。

MySQL修改用户密码

方法1: 用SET PASSWORD命令

  mysql -u root

  mysql> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘newpass’);

方法2:用mysqladmin

  mysqladmin -u root password “newpass”

  如果root已经设置过密码,采用如下方法

  mysqladmin -u root password oldpass “newpass”

方法3: 用UPDATE直接编辑user表

  mysql -u root

  mysql> use mysql;

  mysql> UPDATE user SET Password = PASSWORD(‘newpass’) WHERE user = ‘root’;

  mysql> FLUSH PRIVILEGES;

在丢失root密码的时候,可以这样

  mysqld_safe –skip-grant-tables&

  mysql -u root mysql

  mysql> UPDATE user SET password=PASSWORD(“new password”) WHERE user=’root’;

  mysql> FLUSH PRIVILEGES;

MYSQL与SQLITE重置AUTO_INCREMENT初始值

MYSQL重置AUTO_INCREMENT初始值的方法很简单
可就是SQLITE的重置方法在国内还极少人有记载(至少本人找了很久没找到)
于是到美国漫游了一下,终于功夫不负有心人……

SQLITE AUTO_INCREMENT 复位:

DELETE FROM sqlite_sequence WHERE name = 'your_table_name'

MYSQL AUTO_INCREMENT 复位:

ALTER TABLE your_table_name AUTO_INCREMENT = 1

利用PHP将MYSQL数据输入成EXCEL格式

<?php 
$DB_Server = “localhost”;   
$DB_Username = “put your user name here”;   
$DB_Password = “put your password here”;   
$DB_DBName = “put your database name here”;   
$DB_TBLName = “put your table name here”;   
  
$savename = date(“YmjHis”);  // excel file name
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die(“Couldn’t connect.”);   
mysql_query(“Set Names ‘utf-8′”);
$file_type = “vnd.ms-excel”;   
$file_ending = “xls”;
header(“Content-Type: application/$file_type;charset=utf-8″);
header(“Content-Disposition: attachment; filename=”.$savename.”.$file_ending”);   
//header(“Pragma: no-cache”);      
  
$now_date = date(“Y-m-j H:i:s”);    
$title = “User Email”;    
  
$sql = “SELECT entity_id, email from $DB_TBLName WHERE entity_id >’0′ AND entity_id<10001″;    //export entity_id from 1 to 1000
$ALT_Db = @mysql_select_db($DB_DBName, $Connect) or die(“Couldn’t select database”);   
$result = @mysql_query($sql,$Connect) or die(mysql_error()); 
  
echo(“$title\n”);    
$sep = “\t”;    
for ($i = 0; $i < mysql_num_fields($result); $i++) {
    echo mysql_field_name($result,$i) . “\t”;    
}    
print(“\n”);    
$i = 0;    
while($row = mysql_fetch_row($result)) {    
    $schema_insert = “”;
    for($j=0; $j<mysql_num_fields($result);$j++) {    
        if(!isset($row[$j]))    
            $schema_insert .= “NULL”.$sep;    
        elseif ($row[$j] != “”)    
            $schema_insert .= “$row[$j]“.$sep;
        else    
            $schema_insert .= “”.$sep;    
    }    
    $schema_insert = str_replace($sep.”$”, “”, $schema_insert);    
    $schema_insert .= “\t”;    
    print(trim($schema_insert));    
    print “\n”;    
    $i++;    
}    
return (true); 
?>

MySQL表设计优化与索引 (十)

Bit-Packed Data Types

MySQL有一些存储类型使用一个值中的一些单个的比特位来紧凑的存储数据。纯技术上将,不管是底层的存储格式还是操作,所有这些类型都是字符串类型。

BIT

MySQL5.0以前, BIT只是TINYINT的同义词而已。但是在MySQL5.0以及之后的版本,BIT是一个完全不同的数据类型了, 有着自己的一些特点, 这里讨论一些新的行为和属性:

可以用BIT字段在单列里面来存储一个或多个true/false值, BIT(1)定义一个了只包含单个比特位的字段, BIT(2)是存储2个比特位的字段, 如此类推;BIT字段的最大长度可以是64个比特。

BIT类型的行为与存储引擎有关。MyISAM把一些列为了存储的目的打包到一起, 所以17个单独的BIT列需要17个比特来存储(假定这些列都不允许NULL),MyISAM会近似算成3个字节来存储。其他的一些存储引擎, 比如Memory和InnoDB, 把每个列都用有足够长度存储这些比特位的最小整数来存储,所以无法节省存储空间。

* TIMESTAMP类型的一些行为的规则比较复杂并随着不同的MySQL版本而变化,所以在使用时应当确认是自己所期望的行为。通常, 在对TIMESTAMP的列做了改变后通过查看SHOW CREATE TABALE的结果来确认是一个的主意。

MySQL把BIT当做字符串类型, 而不是数据类型。当检索BIT(1)列的值, 结果是一个字符串而内容是二进制位0或1, 而不是ASCII值”0″或”1″.然而, 如果在一个数值上下文检索的话, 结果是比特串转化而成的数字。 当需要与另一个值进行比较时, 记住这一点。比如, 如果存储值’00111001′(是57的二进制表示)到一个BIT(8)的字段中然后检索出来,得到的是字符编码值为57字符串, 而这值就是“9”的ASCII编码。但是在数值环境中, 得到的是值57:s

mysql> CREATE TABLE bittest(a bit(8));

mysql> INSERT INTO bittest VALUES(b’00111001′);

mysql> SELECT a, a + 0 FROM bittest;

+——+——-+

| a | a + 0 |

+——+——-+

| 9 | 57 |

+——+——-+

这非常容易引起混淆, 所以我们提醒小心使用BIT类型。对于大多数应用程序来说,避免使用这个类型比较的好。

如果想在单个比特位的存储空间中存储true/false值的另一个选择是使用可以为NULL的CHAR(0)列。这个列能够存储NULL和长度为0的空串。

SET

如果需要存储多个true/false的值, 可以考虑把多个列放到一个MySQL所支持的SET数据类型,而MySQL内部通过一些比特位来表示的。这种类型有效的使用存储空间, MySQL也有一些函数如FIND_IN_SET( )和FIELD( )来方便查询。 最主要的缺点是改变列定义的开销: 需要ALTER TABLE, 而这个操作在一个大表上则是开销非常大的(参考后面有关于替换方法的讨论)。一般来说, 也无法在SET列上使用索引。

Bitwise operations on integer columns

整型列上的位操作

SET类型的一个替代办法是把一个整数当做一些比特位的集合。 比如,可以把一个TINYINT数当做8个比特位, 用位运算来操作其中的比特位, 可以通过在应用程序中位每一个比特位定义命名常数来简化理解。

这种方法相对于SET的主要的好处是可以不需要ALTER TABLE就能改变枚举值。缺点是查询写起来麻烦且不容易理解(当第5个比特位为1是什么意思?)。有些人喜欢使用位操作而有些人不喜欢,因而是否使用这个技巧很大程度上是个人口味的问题。

把比特位打成包的一个例子应用程序是存储权限的访问控制列表(ACL).每个比特或SET元素表示一个CAN_READ, CAN_WRITE或者CAN_DELETE之类的值。 如果使用SET列, 需要在MySQL的列定义中存储比特到值得映射关系; 如果使用整数列, 则需要在应用中存储这个映射关系。下面是一些使用SET列的查询语句:

mysql> CREATE TABLE acl (

-> perms SET(‘CAN_READ’, ‘CAN_WRITE’, ‘CAN_DELETE’) NOT NULL

-> );

mysql> INSERT INTO acl(perms) VALUES (‘CAN_READ,CAN_DELETE’);

mysql> SELECT perms FROM acl WHERE FIND_IN_SET(‘CAN_READ’, perms);

+———————+

| perms |

+———————+

| CAN_READ,CAN_DELETE |

+———————+

如果使用整数, 则大概会以如下的方式处理;

mysql> SET @CAN_READ := 1 << 0,

-> @CAN_WRITE := 1 << 1,

-> @CAN_DELETE := 1 << 2;

mysql> CREATE TABLE acl (

-> perms TINYINT UNSIGNED NOT NULL DEFAULT 0

-> );

mysql> INSERT INTO acl(perms) VALUES(@CAN_READ + @CAN_DELETE);

mysql> SELECT perms FROM acl WHERE perms & @CAN_READ;

+——-+

| perms |

+——-+

| 5 |

+——-+

使用了变量来存储值, 不过在代码中可以使用常数来代替。

 

MySQL表设计优化与索引 (九)

日期和时间类型

MySQL有多个表示各种日期和时间值的数据类型, 比如YEAR和DATE. MySQL存储时间的最精确粒度是秒。 然而, 能做微妙粒度的临时计算, 我们会列出一些绕过存储引擎限制的一些方法。

许多时间类型都没有可替换类型, 因而不存在什么是最佳选择这么一说。 唯一的问题是当需要一起存储日期和时间时如何做?MySQL提供了两个非常类似的数据类型来做这个事情: DATETIME和TIMESTAMP. 多大多数应用来说, 两个都行, 但是在某些场合, 一个可能比另一个更合适:

DATETIME

这个类型能够存储很大范围的值, 从1001年到9999年, 准确到秒的精度。它把日期和时间打包成整数以YYYYMMDDHHMMSS 格式表示, 与时区无关。用8个字节的存储空间。

缺省情况下, MySQL以可排序,无歧义的格式显示DATETIME值, 比如 2008-01-16 22:37:08, 这是ANSI下标准显示日期时间的方式。

TIMESTAMP

如名字所指出的,TIMESTAMP 类型存储从1970年1月1日(格林威治时间)到目前为止经过的秒数 –与UNIX时间戳一样。TIMESTAMP 使用4个字节来存储值, 所能表示的范围以比DATETIME要小: 从1970年到2038年。MySQL提供了 FROM_UNIXTIME( ) 和 UNIX_TIMESTAMP( ) 两个函数来进行Unix时间戳与日期类型的相互转换。

新版本的MySQL中TIMESTAMP 采用了与DATETIME 一样的格式来表示值,但是老版本的MySQL没有在各个部分件显示分隔符。 这只是一个显示格式的区别而已,TIMESTAMP 的存储格式在各个版本与DATETIME一致。

TIMESTAMP 对值的显示与时区相关,MySQL服务器,操作系统和客户端连接都有相应的时区设置。

因此,对于与GMT有5个小时时差的东部时间,一个值为0的TIMESTAMP 类型实际显示的内容可能会是1969-12-31 19:00:00。

TIMESTAMP 也有一些DATETIME所不具备的一些属性。 缺省情况下, 插入记录时,MySQL会把第一个没指定具体值得TIMESTAMP 类型的字段自动设置为当前值,在修改时, 如果没有在修改语句中显式的指定值, 第一个DATESTAMP类型的字段的值也会被更行为当前值。也可以通过配置修改修改和插入记录时对TIMESTAMP 列的处理行为。最后, TIMESTAMP列缺省不为NULL, 这与其他数据类型不一样。

除了一些特殊行为,在一般情况下,如果能够使用TIMESTAMP,就使用它, 因为它比DATETIME的空间效率要高。有时候,有人把Unix时间戳存储成整数, 但是实际上没有任何好处,因为格式转换很不方便,我们不推荐这么做。

如果需要存储比秒精度更高的日期和时间数据怎么办呢?MySQL当前并没有提供合适的数据类型, 但是可以根据需要选择自己的存储格式:可以使用BIGINT类型存储毫秒精度的数据,或者使用DOUBLE类型,把秒后面的数值当做小数点后面的小数部分。 这两种方法都不错。

 

MySQL表设计优化与索引 (八)

使用ENUM代替字符串类型

有时候, 可以通过使用ENUM来代理常规的字符串类型。一个ENUM列能够存储65535个不同的字符串值,MySQL非常紧凑的存储这些值,会根据值列表把这些值存储到1到2个字节中。通过在表的.frm文件中保存一个数字到字符串的对应关系来保存一个”查询表”, 它把每个值存储成一个表示值在字段定义列表中的某个位置的整数。 下面是几个例子;

mysql> CREATE TABLE enum_test(

-> e ENUM(‘fish’, ‘apple’, ‘dog’) NOT NULL

-> );

mysql> INSERT INTO enum_test(e) VALUES(‘fish’), (‘dog’), (‘apple’);

这3列实际上存储的是整数, 而不是字符串。 能够通过在数字检索上下文中查看到这些值的两面属性:

mysql> SELECT e + 0 FROM enum_test;

+——-+

| e + 0 |

+——-+

| 1 |

| 3 |

| 2 |

+——-+

如果指定数字当做ENUM常量的话, 这个两面性太容易引起混淆了。建议不要这么做。

另外一个令人奇怪的是ENUM字段通过整数值来排序, 而不是字符串本省:

mysql> SELECT e FROM enum_test ORDER BY e;

+——-+

| e |

+——-+

| fish |

| apple |

| dog |

+——-+

通过指定期望的ENUM数字的排序顺序来解决这个问题。也可以通过在查询中显式的使用FIELD()来指定排序顺序, 但是这回使得MySQL在排序中无法使用索引:

mysql> SELECT e FROM enum_test ORDER BY FIELD(e, ‘apple’, ‘dog’, ‘fish’);

+——-+

| e |

+——-+

| apple |

| dog |

| fish |

+——-+

ENUM最大的缺点是字符串列表是固定的,增加或者删除一个字符串需要使用ALTER TABLE。因而, 如果列表中允许的字符串值可能会变化的话, 使用ENUM可能不是一个好的主意。MySQL在它自己的权限表中使用了ENUM来表示Y和N.

因为MySQL把每个值存储整数, 不得不在需要的时候做一些查询工作来转化成字符串表示,ENUM有一些开销的。这些有可能会通过他们相对较小的大小来缓冲, 但并不总是这样。特别是, 把CHAR或者VARCHAR列并成一个ENUM列比并成CHAR或者VARCHAR列要慢。

为了演示一下,我们对MySQL在一个应用中的一个表上做join做性能测试. 这个表有一个很宽的主键:

CREATE TABLE webservicecalls (

day date NOT NULL,

account smallint NOT NULL,

service varchar(10) NOT NULL,

method varchar(50) NOT NULL,

calls int NOT NULL,

items int NOT NULL,

time float NOT NULL,

cost decimal(9,5) NOT NULL,

updated datetime,

PRIMARY KEY (day, account, service, method)

) ENGINE=InnoDB;

这个表包含了大概110,000条记录,大小只有10M, 所以能够全部放在内存中。service列包含有5个不同的值, 平均长度是4个字符, method列包含有71个值, 平均长度20个字符。

我们对这个表做了一个拷贝, 把service和method字段转化成ENUM类型,如下:

CREATE TABLE webservicecalls_enum (

… 省略 …

service ENUM(…values omitted…) NOT NULL,

method ENUM(…values omitted…) NOT NULL,

… 省略 …

) ENGINE=InnoDB;

然后测试联合两个表的主键列来测试性能,使用的查询如下:

mysql> SELECT SQL_NO_CACHE COUNT(*)

-> FROM webservicecalls

-> JOIN webservicecalls USING(day, account, service, method);

然后改变一下查询, 以不同的组合来联合VARCHAR和ENUM字段,表3-1中列出了结果。

表3-1. 联合VARCHAR和ENUM列的

Test Queries per second

VARCHAR joined to VARCHAR 2.6

VARCHAR joined to ENUM 1.7

ENUM joined to VARCHAR 1.8

ENUM joined to ENUM 3.5

把列转化成ENUM后, 联合速度变快了。但是联合ENUM和VARCHAR列慢一些。在这个例子中, 只要不需要与VARCHAR列进行联合,看起来转化成ENUM是一个不错的注意。

而且, 做类型转换有另外一个好处: 从SHOW TABLE STATUS结果着那个的Data_length列的值来看,把这两列换成ENUM后, 表只有原来大小的1/3了。 在某些情况下, 即使需要做与VARCHAR列的联合, 也是有好处的。 在转换后,主键的大小也只有原来的的一半。 因为这是一个InnoDB表, 如果还有其他索引的话,减少主键的大小可以使得索引大大减小。

 

MySQL表设计优化与索引 (七)

如何避免磁盘临时表

因为内存储引擎不支持TEXT和BLOB数据类型, 使用到BLOB和TEXT列的查询和使用隐式临时表的查询将不得不使用MyISAM的磁盘临时表, 即使只有很少的几条记录。 这可能导致很严重的性能开销。即使把MySQL配置成在RAM盘上使用临时表, 也会用到一些开销大的系统调用(Maria存储引擎应该能通过把所有的东西, 而不只是索引,缓存在内存中而在一定程度上缓解这个问题)。最好的解决办法是避免使用BLOB和TEXT类型, 除非费用不可。

.

如果无法避免使用BLOB和TEXT类型, 应该能够通过使用ORDER BY SUBSTRING(column, length)

的办法来把这些类型的值转化成字符串,这样不会妨碍使用内存临时表。另外, 确认一下的所使用的子串的长度是否短到不会导致临时表的大小不会超过 max_heap_table_size 或 tmp_table_size, 否则,还是会生成MyISAM的磁盘临时表。

如果EXPLAIN结果中的”Extra”列里面包含”Using temporary”, 那么查询会使用一个隐式的临时表。

 

MySQL表设计优化与索引 (六)

多版本并发控制

Multiversion Concurrency Control

大部分的MySQL的存储 引擎,比如InnoDB,Falcon,以及PBXT并不是简简单单的使用行锁机制。它们都使用了行锁结合一种提高并发的技术,被称为MVCC(多版本并 发控制)。MVCC并不单单应用在MySQL中,其他的数据库如Oracle,PostgreSQL,以及其他数据库也使用这个技术。

MVCC避免了许多需要加锁的情形以及降低消耗。这取决于它实现的方式,它允许非阻塞读取,在写的操作的时候阻塞必要的记录。

MVCC保存了某一时刻数据的一个快照。意思就是无论事物运行了多久,它们都能看到一致的数据。也就是说在相同的时间下,不同的事物看相同表的数据是不同的。如果你从来没有这方面的经验,可能说这些有点令人困惑。但是在以后这个会很容易理解和熟悉的。

每个存储引擎实现MVCC方式都是不同的。有许多种包含了乐观(optimistic)和悲观(pessimistic)的并发控制。我们用简单的InnoDb的行为来举例说明MVCC工作方式。

InnoDB实现MVCC的方法是,它存储了每一行的两个额外的隐藏字段,这两个隐藏字段分别记录了行的创建的时间和删除的时间。在每个事件发生的时 候,每行存储版本号,而不是存储事件实际发生的时间。每次事物的开始这个版本号都会增加。自记录时间开始,每个事物都会保存记录的系统版本号。依照事物的 版本来检查每行的版本号。在事物隔离级别为可重复读的情况下,来看看怎样应用它。

SELECT

InnoDB检查每行,要确定它符合两个标准。

InnoDB必须知道行的版本号,这个行的版本号至少要和事物版本号一样的老。(也就是是说它的版本号可能少于或者和事物版本号相同)。这个既能确定事物开始之前行是存在的,也能确定事物创建或修改了这行。

行的删除操作的版本一定是未定义的或者大于事物的版本号。确定了事物开始之前,行没有被删除。

符合了以上两点。会返回查询结果。

INSERT

InnoDB记录了当前新增行的系统版本号。

DELETE

InnoDB记录的删除行的系统版本号作为行的删除ID。

UPDATE

InnoDB复制了一行。这个新行的版本号使用了系统版本号。它也把系统版本号作为了删除行的版本。

所有其他记录的结果保存是,从未获得锁的查询。这样它们查询的数据就会尽可能的快。要确定查询行要遵循这些标准。缺点是存储引擎要为每一行存储更多的数据,检查行的时候要做更多的处理以及其他内部的一些操作。

MVCC只能在可重复读和可提交读的隔离级别下生效。不可提交读不能使用它的原因是不能读取符合事物版本的行版本。它们总是读取最新的行版本。可序列化不能使用MVCC的原因是,它总是要锁定行。

下面的表说明了在MySQL中不同锁的模式以及并发级别。

 

锁的策略 并发性 开销 引擎
最低 最低 MyISAM,Merge,Memory
NDB Cluster
行和MVCC 最高 最高 InnoDB,Falcon,PBXT,solidD

 

 

MySQL表设计优化与索引 (五)

当存储一个CHAR值时, Mysql会除去尾随空间, 这个行为有点让人困惑, 用一个具体的例子来看一下: 首先 ,创建一个只有一个CHAR(10)字段的表并存储一些值在里面:

mysql> CREATE TABLE char_test( char_col CHAR(10));

mysql> INSERT INTO char_test(char_col) VALUES

-> (’string1′), (‘ string2′), (’string3 ‘);

然后检索这些值, 尾部空间被除掉了:

mysql> SELECT CONCAT(“‘”, char_col, “‘”) FROM char_test;

+—————————-+

| CONCAT(“‘”, char_col, “‘”) |

+—————————-+

| ’string1′ |

| ‘ string2′ |

| ’string3′ |

+—————————-+

如果存储同样的值在VARCHA(10)的字段里, 在检索的时候得到的结果如下:

mysql> SELECT CONCAT(“‘”, varchar_col, “‘”) FROM varchar_test;

+——————————-+

| CONCAT(“‘”, varchar_col, “‘”) |

+——————————-+

| ’string1′ |

| ‘ string2′ |

| ’string3 ‘ |

+——————————-+

数据的存储是存储引擎相关的, 并不是所有的存储引擎用同样的方式来处理定长和变长类型。Memory存储引擎使用定长的行, 因而它分配尽可能多的空间给变长字段来存储数据。 而Falcon则使用变长字段来存储, 即使对于CHAR字段也是如此。 但是填充和去尾是一致de 这是因为这个是 油MySql服务器自己处理的。

CHAR和VCHAR的兄弟类型分别是存储二进制数据的BINARY和VARBINARY, 二进制字符串与普通的字符长相似, 不过他们是以字节存储的, 而不是字符,填充也不一样 , Mysql用\0(0 字节)而不是空格填充二进制数据, 而在检索时不会把后面的填充值去掉。

对于需要存数二进制数据的活着需要Mysql对值进行字节的比较而不是基于字符的比较时, 二进制数据类型非常哟用。基于字节的比较的优势不只是大小写不敏感的好处, Mysql在比较二进制数据时, 通过字节表示的数值比较的方式进行的, 因为二进制数据相对与基于字符的比较更简单, 也更快。

记住长度单位是字符, 而不是字节, 一个多字节字符集要多个字节来存储。

 

MySQL表设计优化与索引 (四)

String Types(字符串类型)

字符串类型

Mysql支持多种字符串类型的变体。 这些数据类型在4.1和5.0版本中有较大的变化, 这使得这些数据类型变得更加复杂。 从Mysql4.1起, 每个字符串类型能有自己的字符集和针对这些字符集的排序规则集 (更多的关于排序规则的主题, 参考第5章), 这对于性能有较大的影响。

VARCHAR和CHAR类型

VARCHAR和CHAR是两种最主要的存储字符串的数据类型, 不幸的是,的确有些困难解释这两种数据类型在磁盘和内存中是如何存储的, 因为这是与存储引擎相关的(比如, Falcon的差不多所有数1是, 请参考相应的存储引擎文档。

先看看VARCHAR和CHAR值的典型的磁盘存储方式。 同一种存储引擎对VARCHAR和CHAR在磁盘和内存中的存储格式可能是不一样的, 当从一个地方读取并存到另一个地方时, 服务器可能需要做一些转换工作。 两种数据类型的一些比较:

VARCHAR存储可变长度的字符串, 它应该是最为常见的数据类型了。 相对于固定长度的数据类型, 它需要的存储恐空间相对较少, 因为它按需分配存储空间 (比如, 存储短字符串的时候就用少的空间). 例外的地方在MyISAM中建立ROW_FORMAT=FIXED的表, 这种表对于每一行都使用相同数量空间来存储, 因而会导致空间浪费。VARCHAR 使用1或2个字节来记录值的长度: 如果字段的长度小于255个字节, 用1个字节, 否则用2个字节。假定使用latin1字符集, 一个VARCHAR(10)的字段值使用11个字节的存储空间, 1个VARCHAR(1000)的字段值使用1002字节的空间, 因为需要2个字节来存储长度信息。

VARCHAR有助于改善性能, 因为能节省空间。 但是, 因为记录的长度是可变的, 当修改的时候因为记录可能变长会导致一些额外的工作。 如果一列增长了而原来的存储空间不能存储修改后的值, 具体的增长策略是与具体的存储引擎相关的。 比如, MyISAM可能导致分行, 而innodb则可能会导致分页来处理。 其他的一些引擎则可能不会在原来的地方修改数据。

一般来说, 当某个字段的最大长度远远大于平均长度时; 或者很少修改字段因为碎片化不会是一个问题; 或者使用一个复杂的字符集合比如UTF-8, 而每个字符使用变长的字节来表示时, 使用VARCHAR来存储时值得的。

CHAR是固定长度的, Mysql总是分配足够的空间来存储指定数目的字符。当存储一个CHAR值时, Mysql会除去尾随空间(在4.1以及之前的版本, VARCHAR也是这么处理的, VARCHAR和CHAR逻辑上市一样的,只是存储格式不一样而已.) 当比较时, 字段后面会根据需要垫加一些空格来进行比较。

当需要存储非常短的字符串时; 或者所有的字段值差不多同样长时, CHAR非常有用。比如, 用CHAR来存储用户密码的md5结果值, 这些结果值是等长的。 当字段值长度变化频繁时, CHAR比VARCHAR要好, 因为固定长度的字段不容易导致碎片化。 对一些字段值很短的字段, CHAR也比VARCHAR高效, 用CHAR(1)来存储Y或者N只需要一个字节长度来存储数据, 而VARCHAR(1)则需要2个字节,因为需要一个字节来表示长度。