erman_康康

MySQL索引类型及优化

2012 年 06 月 24 日作者：erman
暂无评论

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。

在数据库表中，对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表：

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL

);

我们随机向里面插入了10000条记录，其中有一条：5555, admin。

在查找username=”admin”的记录 SELECT * FROM mytable WHERE username=’admin’;时，如果在username上已经建立了索引，MySQL无须任何扫描，即准确可找到该记录。相反，MySQL会扫描所有记录，即要查询10000条记录。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

MySQL索引类型包括：

（1）普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

◆创建索引

CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

◆修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))
◆创建表的时候直接指定

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

INDEX [indexName] (username(length))

);
删除索引的语法：

DROP INDEX [indexName] ON mytable;
（2）唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

◆创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))
◆修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))
◆创建表的时候直接指定

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

UNIQUE [indexName] (username(length))

);
（3）主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

PRIMARY KEY(ID)

);
当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

（4）组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

city VARCHAR(50) NOT NULL,

age INT NOT NULL

);
为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age

usernname,city

usernname
为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

SELECT * FROM mytable WHREE username=”admin” AND city=”郑州”

SELECT * FROM mytable WHREE username=”admin”
而下面几个则不会用到：

SELECT * FROM mytable WHREE age=20 AND city=”郑州”

SELECT * FROM mytable WHREE city=”郑州”
（5）建立索引的时机

到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：

SELECT t.Name

FROM mytable t LEFT JOIN mytable m

ON t.Name=m.username WHERE m.age=20 AND m.city=’郑州’
此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：

SELECT * FROM mytable WHERE username like’admin%’
而下句就不会使用：

SELECT * FROM mytable WHEREt Name like’%admin’
因此，在使用LIKE时应注意以上的区别。

（6）索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

◆虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

（7）使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

◆索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

◆使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

◆索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

◆like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

◆不要在列上进行运算

select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select * from users where adddate<‘2007-01-01’;
◆不使用NOT IN和<>操作

以上，就对其中MySQL索引类型进行了介绍。

索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4 个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要 100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。进入正题：

第二章、索引与优化

1、选择索引的数据类型

MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：

(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
(3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

1.1、选择标识符
选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。
(1)   整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT。

(2)   字符串：尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。

2、索引入门
对于任何DBMS，索引都是进行优化的最主要的因素。对于少量的数据，没有合适的索引影响不是很大，但是，当随着数据量的增加，性能会急剧下降。
如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。例如：
假设存在组合索引it1c1c2(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。

2.1、索引的类型
索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。
2.1.1、B-Tree索引
假设有如下一个表：

CREATE TABLE People (

last_name varchar(50)    not null,

first_name varchar(50)    not null,

dob        date           not null,

gender     enum(‘m’, ‘f’) not null,

key(last_name, first_name, dob)

);

其索引包含表中每一行的last_name、first_name和dob列。其结构大致如下：

索引存储的值按索引列中的顺序排列。可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询，当然，如果想使用索引，你必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。
(1)匹配全值(Match the full value)：对索引中的所有列都指定具体的值。例如，上图中索引可以帮助你查找出生于1960-01-01的Cuba Allen。
(2)匹配最左前缀(Match a leftmost prefix)：你可以利用索引查找last name为Allen的人，仅仅使用索引中的第1列。
(3)匹配列前缀(Match a column prefix)：例如，你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列。
(4)匹配值的范围查询(Match a range of values)：可以利用索引查找last name在Allen和Barrymore之间的人，仅仅使用索引中第1列。
(5)匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part)：可以利用索引查找last name为Allen，而first name以字母K开始的人。
(6)仅对索引进行查询(Index-only queries)：如果查询的列都位于索引中，则不需要读取元组的值。
由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行ORDER BY。当然，使用B-tree索引有以下一些限制：
(1) 查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。
(2) 不能跳过某一索引列。例如，你不能利用索引查找last name为Smith且出生于某一天的人。
(3) 存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERE last_name=”Smith” AND first_name LIKE ‘J%’ AND dob=’1976-12-23’，则该查询只会使用索引中的前两列，因为LIKE是范围查询。

2.1.2、Hash索引
MySQL 中，只有Memory存储引擎显示支持hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B-Tree索引。Memory存储引擎支持非唯一hash索引，这在数据库领域是罕见的，如果多个值有相同的hash code，索引把它们的行指针用链表保存到同一个hash表项中。
假设创建如下一个表：
CREATE TABLE testhash (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
KEY USING HASH(fname)
) ENGINE=MEMORY;
包含的数据如下：

假设索引使用hash函数f( )，如下：

f(‘Arjen’) = 2323

f(‘Baron’) = 7437

f(‘Peter’) = 8784

f(‘Vadim’) = 2458

此时，索引的结构大概如下：

Slots是有序的，但是记录不是有序的。当你执行
mysql> SELECT lname FROM testhash WHERE fname=’Peter’;
MySQL会计算’Peter’的hash值，然后通过它来查询索引的行指针。因为f(‘Peter’) = 8784，MySQL会在索引中查找8784，得到指向记录3的指针。
因为索引自己仅仅存储很短的值，所以，索引非常紧凑。Hash值不取决于列的数据类型，一个TINYINT列的索引与一个长字符串列的索引一样大。

Hash索引有以下一些限制：
(1)由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的，不会对性造成太大的影响。
(2)不能使用hash索引排序。
(3)Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。
(4)Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。
2.1.3、空间(R-Tree)索引
MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。
2.1.4、全文(Full-text)索引
全文索引是MyISAM的一个特殊索引类型，主要用于全文检索。

3、高性能的索引策略
3.1、聚簇索引(Clustered Indexes)
聚簇索引保证关键字的值相近的元组存储的物理位置也相同（所以字符串类型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表只能有一个聚簇索引。因为由存储引擎实现索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。
聚簇索引的结构大致如下：

注：叶子页面包含完整的元组，而内节点页面仅包含索引的列(索引的列为整型)。一些DBMS允许用户指定聚簇索引，但是MySQL的存储引擎到目前为止都不支持。InnoDB对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，DBMS都会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

3.1.1、InnoDB和MyISAM的数据布局的比较
为了更加理解聚簇索引和非聚簇索引，或者primary索引和second索引(MyISAM不支持聚簇索引)，来比较一下InnoDB和MyISAM的数据布局，对于如下表：

CREATE TABLE layout_test (

col1 int NOT NULL,

col2 int NOT NULL,

PRIMARY KEY(col1),

KEY(col2)

);

假设主键的值位于1—10,000之间，且按随机顺序插入，然后用OPTIMIZE TABLE进行优化。col2随机赋予1—100之间的值，所以会存在许多重复的值。
(1)   MyISAM的数据布局
其布局十分简单，MyISAM按照插入的顺序在磁盘上存储数据，如下：

注：左边为行号(row number)，从0开始。因为元组的大小固定，所以MyISAM可以很容易的从表的开始位置找到某一字节的位置。
据些建立的primary key的索引结构大致如下：

注：MyISAM不支持聚簇索引，索引中每一个叶子节点仅仅包含行号(row number)，且叶子节点按照col1的顺序存储。
来看看col2的索引结构：

实际上，在MyISAM中，primary key和其它索引没有什么区别。Primary key仅仅只是一个叫做PRIMARY的唯一，非空的索引而已。

(2)   InnoDB的数据布局
InnoDB按聚簇索引的形式存储数据，所以它的数据布局有着很大的不同。它存储表的结构大致如下：

注：聚簇索引中的每个叶子节点包含primary key的值，事务ID和回滚指针(rollback pointer)——用于事务和MVCC，和余下的列(如col2)。

相对于MyISAM，二级索引与聚簇索引有很大的不同。InnoDB的二级索引的叶子包含primary key的值，而不是行指针(row pointers)，这减小了移动数据或者数据页面分裂时维护二级索引的开销，因为InnoDB不需要更新索引的行指针。其结构大致如下：

聚簇索引和非聚簇索引表的对比：

3.1.2、按primary key的顺序插入行(InnoDB)

如果你用InnoDB，而且不需要特殊的聚簇索引，一个好的做法就是使用代理主键(surrogate key)——独立于你的应用中的数据。最简单的做法就是使用一个AUTO_INCREMENT的列，这会保证记录按照顺序插入，而且能提高使用 primary key进行连接的查询的性能。应该尽量避免随机的聚簇主键，例如，字符串主键就是一个不好的选择，它使得插入操作变得随机。

3.2、覆盖索引(Covering Indexes)
如果索引包含满足查询的所有数据，就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点：
(1)索引项通常比记录要小，所以MySQL访问更少的数据；
(2)索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；
(3)大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。
(4)覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。
覆盖索引不能是任何索引，只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。
对于索引覆盖查询(index-covered query)，使用EXPLAIN时，可以在Extra一列中看到“Using index”。例如，在sakila的inventory表中，有一个组合索引(store_id,film_id)，对于只需要访问这两列的查询，MySQL就可以使用索引，如下：

mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: inventory

type: index

possible_keys: NULL

key: idx_store_id_film_id

key_len: 3

ref: NULL

rows: 5007

Extra: Using index

1 row in set (0.17 sec)

在大多数引擎中，只有当查询语句所访问的列是索引的一部分时，索引才会覆盖。但是，InnoDB不限于此，InnoDB的二级索引在叶子节点中存储了 primary key的值。因此，sakila.actor表使用InnoDB，而且对于是last_name上有索引，所以，索引能覆盖那些访问actor_id的查询，如：

mysql> EXPLAIN SELECT actor_id, last_name

-> FROM sakila.actor WHERE last_name = ‘HOPPER’\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: actor

type: ref

possible_keys: idx_actor_last_name

key: idx_actor_last_name

key_len: 137

ref: const

rows: 2

Extra: Using where; Using index

3.3、利用索引进行排序
MySQL 中，有两种方式生成有序结果集：一是使用filesort，二是按索引顺序扫描。利用索引进行排序操作是非常快的，而且可以利用同一索引同时进行查找和排序操作。当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序。如果查询是连接多个表，仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort。

create table actor(

actor_id int unsigned NOT NULL AUTO_INCREMENT,

name      varchar(16) NOT NULL DEFAULT ”,

password        varchar(16) NOT NULL DEFAULT ”,

PRIMARY KEY(actor_id),

KEY     (name)

) ENGINE=InnoDB

insert into actor(name,password) values(‘cat01′,’1234567’);

insert into actor(name,password) values(‘cat02′,’1234567’);

insert into actor(name,password) values(‘ddddd’,’1234567′);

insert into actor(name,password) values(‘aaaaa’,’1234567′);

mysql> explain select actor_id from actor order by actor_id \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: actor

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 4

Extra: Using index

1 row in set (0.00 sec)

mysql> explain select actor_id from actor order by password \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: actor

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 4

Extra: Using filesort

1 row in set (0.00 sec)

mysql> explain select actor_id from actor order by name \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: actor

type: index

possible_keys: NULL

key: name

key_len: 18

ref: NULL

rows: 4

Extra: Using index

1 row in set (0.00 sec)

当 MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）。对于filesort，MySQL有两种排序算法。
(1)两遍扫描算法(Two passes)
实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。
注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小。
(3)   一次扫描算法(single pass)
该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。
注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。

当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出 “Using temporary;Using filesort”。

3.4、索引与加锁
索引对于InnoDB非常重要，因为它可以让查询锁更少的元组。这点十分重要，因为MySQL 5.0中，InnoDB直到事务提交时才会解锁。有两个方面的原因：首先，即使InnoDB行级锁的开销非常高效，内存开销也较小，但不管怎么样，还是存在开销。其次，对不需要的元组的加锁，会增加锁的开销，降低并发性。
InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是，只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即达不到过滤的目的），MySQL服务器只能对 InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了：InnoDB已经锁住那些元组，服务器无法解锁了。
来看个例子：

create table actor(

actor_id int unsigned NOT NULL AUTO_INCREMENT,

name      varchar(16) NOT NULL DEFAULT ”,

password        varchar(16) NOT NULL DEFAULT ”,

PRIMARY KEY(actor_id),

KEY     (name)

) ENGINE=InnoDB

insert into actor(name,password) values(‘cat01′,’1234567’);

insert into actor(name,password) values(‘cat02′,’1234567’);

insert into actor(name,password) values(‘ddddd’,’1234567′);

insert into actor(name,password) values(‘aaaaa’,’1234567′);

SET AUTOCOMMIT=0;

BEGIN;

SELECT actor_id FROM actor WHERE actor_id < 4

AND actor_id <> 1 FOR UPDATE;

该查询仅仅返回2—3的数据，实际已经对1—3的数据加上排它锁了。InnoDB锁住元组1是因为MySQL的查询计划仅使用索引进行范围查询（而没有进行过滤操作，WHERE中第二个条件已经无法使用索引了）：

mysql> EXPLAIN SELECT actor_id FROM test.actor

-> WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: actor

type: index

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: NULL

rows: 4

Extra: Using where; Using index

1 row in set (0.00 sec)

mysql>

表明存储引擎从索引的起始处开始，获取所有的行，直到actor_id<4为假，服务器无法告诉InnoDB去掉元组1。
为了证明row 1已经被锁住，我们另外建一个连接，执行如下操作：

SET AUTOCOMMIT=0;

BEGIN;

SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE;

该查询会被挂起，直到第一个连接的事务提交释放锁时，才会执行（这种行为对于基于语句的复制(statement-based replication)是必要的）。
如上所示，当使用索引时，InnoDB会锁住它不需要的元组。更糟糕的是，如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。
predis操作大全

2012 年 06 月 23 日作者：erman
暂无评论

redis是php连接redis的操作库，由于它完全使用php编写，大量使用命名空间以及闭包等功能，只支持php5.3以上版本，故实测性能一般，每秒25000次读写，相信改换c语言编写的php扩展后性能会大幅提升(比如使用C扩展phpredis https://github.com/owlient/phpredis)。

将session数据存放到redis也很简单:
session.save_handler = redis
session.save_path = “tcp://127.0.0.1:6379″

以下是汇总一些操作，并不断更新。

//使用autoload加载相关库,这边重点就是为了require $file;
spl_autoload_register(function($class) {
$file = __DIR__.’/lib/Predis/’.$class.’.php’;
if (file_exists($file)) {
require $file;
return true;
}
});

//配置连接的IP、端口、以及相应的数据库
$server = array(
‘host’     => ‘127.0.0.1’,
‘port’     => 6379,
‘database’ => 15
);
$redis = new Client($server);

//普通set/get操作
$redis->set(‘library’, ‘predis’);
$retval = $redis->get(‘library’);
echo $retval; //显示 ‘predis’

//setex set一个存储时效
$redis->setex(‘str’, 10, ‘bar’); //表示存储有效期为10秒

//setnx/msetnx相当于add操作,不会覆盖已有值
$redis->setnx(‘foo’,12); //true
$redis->setnx(‘foo’,34); //false

//getset操作,set的变种,结果返回替换前的值
$redis->getset(‘foo’,56);//返回34

// incrby/incr/decrby/decr 对值的递增和递减
$redis->incr(‘foo’); //foo为57
$redis->incrby(‘foo’,2); //foo为59

//exists检测是否存在某值
$redis->exists(‘foo’);//true

//del 删除
$redis->del(‘foo’);//true

//type 类型检测,字符串返回string,列表返回 list,set表返回set/zset,hash表返回hash
$redis->type(‘foo’);//不存在,返回none
$redis->set(‘str’,’test’);
$redis->type(‘str’); //字符串，返回string

//append 连接到已存在字符串
$redis->append(‘str’,’_123′); //返回累加后的字符串长度8,此进str为 ‘test_123’

//setrange 部分替换操作
$redis->setrange(‘str’,0,’abc’); //返回3,参数2为0时等同于set操作
$redis->setrange(‘str’,2,’cd’);//返回4,表示从第2个字符后替换,这时’str’为’abcd’

//substr 部分获取操作
$redis->substr(‘str’,0,2);//表示从第0个起，取到第2个字符，共3个，返回’abc’

//strlen 获取字符串长度
$redis->strlen(‘str’); //返回4

//setbit/getbit 位存储和获取
$redis->setbit(‘binary’,31,1);  //表示在第31位存入1,这边可能会有大小端问题?不过没关系,getbit 应该不会有问题
$redis->getbit(‘binary’,31);   //返回1

//keys 模糊查找功能,支持*号以及?号(匹配一个字符)
$redis->set(‘foo1’,123);
$redis->set(‘foo2’,456);
$redis->keys(‘foo*’); //返回foo1和foo2的array
$redis->keys(‘f?o?’);  //同上

//randomkey 随机返回一个key
$redis->randomkey(); //可能是返回 ‘foo1’或者是’foo2’及其它任何一存在redis的key

//rename/renamenx 对key进行改名,所不同的是renamenx不允许改成已存在的key
$redis->rename(‘str’,’str2′); //把原先命名为’str’的key改成了’str2′

//expire 设置key-value的时效性,ttl 获取剩余有效期,persist 重新设置为永久存储
$redis->expire(‘foo’, 1); //设置有效期为1秒
$redis->ttl(‘foo’); //返回有效期值1s
$redis->expire(‘foo’); //取消expire行为

//dbsize 返回redis当前数据库的记录总数
$redis->dbsize();

//rpush/rpushx 有序列表操作,从队列后插入元素
//lpush/lpushx 和rpush/rpushx的区别是插入到队列的头部,同上,’x’含义是只对已存在的key进行操作
$redis->rpush(‘fooList’, ‘bar1’); //返回一个列表的长度1
$redis->lpush(‘fooList’, ‘bar0’); //返回一个列表的长度2
$redis->rpushx(‘fooList’, ‘bar2’); //返回3,rpushx只对已存在的队列做添加,否则返回0
//llen返回当前列表长度
$redis->llen(‘fooList’);//3

//lrange 返回队列中一个区间的元素
$redis->lrange(‘fooList’,0,1); //返回数组包含第0个至第1个共2个元素
$redis->lrange(‘fooList’,0,-1);//返回第0个至倒数第一个,相当于返回所有元素,注意redis中很多时候会用到负数,下同

//lindex 返回指定顺序位置的list元素
$redis->lindex(‘fooList’,1); //返回’bar1′

//lset 修改队列中指定位置的value
$redis->lset(‘fooList’,1,’123′);//修改位置1的元素,返回true

//lrem 删除队列中左起指定数量的字符
$redis->lrem(‘fooList’,1,’_’); //删除队列中左起(右起使用-1)1个字符’_'(若有)

//lpop/rpop 类似栈结构地弹出(并删除)最左或最右的一个元素
$redis->lpop(‘fooList’); //’bar0′
$redis->rpop(‘fooList’); //’bar2′

//ltrim 队列修改，保留左边起若干元素，其余删除
$redis->ltrim(‘fooList’, 0,1); //保留左边起第0个至第1个元素

//rpoplpush 从一个队列中pop出元素并push到另一个队列
$redis->rpush(‘list1′,’ab0’);
$redis->rpush(‘list1′,’ab1’);
$redis->rpush(‘list2′,’ab2’);
$redis->rpush(‘list2′,’ab3’);
$redis->rpoplpush(‘list1′,’list2’);//结果list1 =>array(‘ab0’),list2 =>array(‘ab1′,’ab2′,’ab3’)
$redis->rpoplpush(‘list2′,’list2’);//也适用于同一个队列,把最后一个元素移到头部list2 =>array(‘ab3′,’ab1′,’ab2’)

//linsert 在队列的中间指定元素前或后插入元素
$redis->linsert(‘list2’, ‘before’,’ab1′,’123′); //表示在元素’ab1’之前插入’123′
$redis->linsert(‘list2’, ‘after’,’ab1′,’456′);   //表示在元素’ab1’之后插入’456′

//blpop/brpop 阻塞并等待一个列队不为空时，再pop出最左或最右的一个元素（这个功能在php以外可以说非常好用）
//brpoplpush 同样是阻塞并等待操作，结果同rpoplpush一样
$redis->blpop(‘list3’,10); //如果list3为空则一直等待,直到不为空时将第一元素弹出,10秒后超时
linux 指定文件类型批量更改目录权限

2012 年 06 月 10 日作者：erman
暂无评论

find . type d -exec chmod 755 {} \; 更改所有的目录权限

find . -type f -exec chmod 644 {} \; 更改所有文件权限

linux下如何删除（中文）乱码文件名的文件或者文件夹

2012 年 06 月 10 日作者：erman

暂无评论

linux下：

如果FTP上传没有选用 BIN（二进制），解压就会出现乱码

如果有中文，有些就会出现乱码

ls -i 列出乱码文件，前面的文件的 i 节点号
find ./ -inum 123456 -print -exec rm {} -rf \;

（上面的：123456 换成你的乱码文件节点号）

jquery reference

2012 年 06 月 10 日作者：erman

暂无评论

Selector	Example	Selects
*	$(“*”)	All elements
#id	$(“#lastname”)	The element with id=lastname
.class	$(“.intro”)	All elements with
element	$(“p”)	All p elements
.class.class	$(“.intro.demo”)	All elements with the classes “intro” and “demo”

:first	$(“p:first”)	The first p element
:last	$(“p:last”)	The last p element
:even	$(“tr:even”)	All even tr elements
:odd	$(“tr:odd”)	All odd tr elements

:eq(index)	$(“ul li:eq(3)”)	The fourth element in a list (index starts at 0)
:gt(no)	$(“ul li:gt(3)”)	List elements with an index greater than 3
:lt(no)	$(“ul li:lt(3)”)	List elements with an index less than 3
:not(selector)	$(“input:not(:empty)”)	All input elements that are not empty

:header	$(“:header”)	All header elements h1, h2 …
:animated	$(“:animated”)	All animated elements

:contains(text)	$(“:contains(‘W3Schools’)”)	All elements which contains the text
:empty	$(“:empty”)	All elements with no child (elements) nodes
:hidden	$(“p:hidden”)	All hidden p elements
:visible	$(“table:visible”)	All visible tables

s1,s2,s3	$(“th,td,.intro”)	All elements with matching selectors

[attribute]	$(“[href]”)	All elements with a href attribute
[attribute=value]	$(“[href=’default.htm’]”)	All elements with a href attribute value equal to “default.htm”
[attribute!=value]	$(“[href!=’default.htm’]”)	All elements with a href attribute value not equal to “default.htm”
[attribute$=value]	$(“[href$=’.jpg’]”)	All elements with a href attribute value ending with “.jpg”
[attribute^=value]	$(“[href^=’jquery_’]”)	All elements with a href attribute value starting with “jquery_”

:input	$(“:input”)	All input elements
:text	$(“:text”)	All input elements with type=”text”
:password	$(“:password”)	All input elements with type=”password”
:radio	$(“:radio”)	All input elements with type=”radio”
:checkbox	$(“:checkbox”)	All input elements with type=”checkbox”
:submit	$(“:submit”)	All input elements with type=”submit”
:reset	$(“:reset”)	All input elements with type=”reset”
:button	$(“:button”)	All input elements with type=”button”
:image	$(“:image”)	All input elements with type=”image”
:file	$(“:file”)	All input elements with type=”file”

:enabled	$(“:enabled”)	All enabled input elements
:disabled	$(“:disabled”)	All disabled input elements
:selected	$(“:selected”)	All selected input elements
:checked	$(“:checked”)	All checked input elements

一些常用开源软件

2012 年 04 月 13 日作者：erman
暂无评论

操作系统：Centos※,Freebsd,Ubuntu
网站服务：apache※,nginx※,lighttpd,php※,tomcat※,resin※
数据库：Mysql※,PostgreSQL,Mysql-proxy
代理相关：lvs,keepalived,haproxy,nginx,apache,heartbeat（此行都是※）
网站缓存：squid※,nginx※,varnish
内存缓存：memcache※,memcached,TokyoTyrant※,MongoDB,Cassandra※,redis※,tair,CouchDB
存储相关：Nfs※,Moosefs※,Hadoop※,gfs※,lustre,FastDFS
版本管理：cvs,svn※,git※
监控报警：mboy,mrtg,nagios※,cacti※,zbbix,munin※,hyperic※
域名解析：bind※,powerdns,dnsmq※
同步软件：rsync,sersync,drbd,csync2+lsyncd（此行都是※）
分发工具：Secboy※,expect※,puppet※,cfengine※,ssh+rsync+sersync※
虚拟软件：xen※,kvm
内网软件：iptables※,zebra※,iftraf,ntop※,tc※,iftop
邮件软件：qmail※,posfix※,sendmail
远程拨号：openvpn※,pptp,openswan※,ipip※
统一认证：ldap※
队列工具：ActiveMQ※,RabbitMQ
打包发布：mvn※,ants※,jenkins※
测试软件：apache ab,smokeping,siege,JMeter,Webbench,LoadRunner（此行都是※）
日志相关：syslog,rsyslog,Awstats
DB代理：mysql-proxy,amoeba
linux下 crontab文件在哪 crontab文件路径

2012 年 03 月 20 日作者：erman
暂无评论

用户的都在/var/spool/cron/下

/var/spool/cron/crontabs/root
linux三台以上服务器集群间建立ssh信任关系

2012 年 03 月 20 日作者：erman
暂无评论

linux服务器集群之间进行ssh访问时需要输入密码，对进行scp等操作很麻烦，现教大家如何在linux服务器集群之间实现无密码访问。 ssh访问是是需要指定用户的，所以此处建立用户信任关系时以root用户为例，如服务器集群中有三台服务器，分别为A、B、C。现在要实现三台机器之间的root用户的相互无密码访问，操作步骤如下：

（1 ）用root用户登录A服务器，执行ssh-keygen -t rsa 命令，此命令为A机器上的用户root 生成密钥对。当询问保存路径时全部回车采用默认路径即可，当提示要为密钥输入 passphrase 是回车采用空密码。此命令执行完毕生成一对密钥文件 id_rsa （私钥）和 id_rsa.pub （公钥），默认存储在 /root/.ssh 下;
（2 ）把生成的id_ras.pub 重定向到authorized_keys，命令是cat id_ras.pub > authorized_keys，把authorized_keys文件拷贝到B机器上得的同一目录下（命令为：scp /root/.ssh/authorized_keys root@B: /root/.ssh/ 或者 scp ./authorized_keys root@B: pwd）;

（3）用root用户登录B机器，按照要求执行（1）步骤，然后，将生成的 id_ras.pub 追加到authorized_keys文件中，命令是cat id_ras.pub >> authorized_keys，然后将 authorized_keys文件拷贝覆盖掉A级器上的authorized_keys文件，这样A，B机器之间就可以实现相互之间的ssh无密码访问。

（4）对于C机器，同样将生成的生成的 id_ras.pub 追加到authorized_keys文件中，然后用新的authorized_keys文件覆盖A、B机器上的authorized_keys文件，则A、B、C机器之间就可以进行ssh无密码访问了。
注：以上是对root用户而言，如果是普通用户的话，则权限需要修改，.ssh文件夹权限是700，authorized_keys文件的权限是600，执行以下两个语句即可：
chmod 700 .ssh
chmod 600 authorized_keys

利用rsync 从 windows2003⇒linux系统备份

2012 年 03 月 16 日作者：erman

暂无评论

windows端安装rsyncServer(WINDOWS)版本 1.程序目录下配置文件 rsyncd.conf 修改为：

port = 873
use chroot = false
strict modes = false
uid=0
gid=0
list = no
hosts allow = *
log file = rsyncd.log
#rsync连接时需要提供的用户名密码
auth users = rsync_picbak 
secrets file = rsyncd.secrets （跟rsyncd.conf配置文件是同级目录）

[test] #同步目录设置  /cygdrive/d/代表d盘
path = /cygdrive/d/websites
read only = yes
transfer logging = yes

2.rsyncd.secrets的内容为：

rsync_picbak = 111111（密码）

3.启动rsync服务

打开“控制面板”－－＞“管理工具”－－＞“服务”：
找到一个RsyncServer的服务，启动它（防火墙设置记得打开873端口）

linux端安装rsync的client客户端

安装完毕 ,telnet 服务器ip 873 通的就可以执行以下同步操作了

rsync -vzrtopg --progress --delete --password-file=/var2/db_bak/pass_file rsync_picbak@122.225.115.*::test/ /var2/pic_bak/208

/var2/db_bak/pass_file 为存放密码的文件

/var2/pic_bak/208 为本机需要备份的文件夹名称

mysql主从同步过程同步原理

2012 年 02 月 22 日作者：erman
暂无评论

Replication 线程
　　Mysql的 Replication 是一个异步的复制过程，从一个 Mysql instace(我们称之为 Master)复制到另一个 Mysql instance(我们称之 Slave)。在 Master 与 Slave 之间的告终全副复制过程重要由三个线程来告终，其中两个线程(Sql线程和IO线程)在 Slave 端，另外一个线程(IO线程)在 Master 端。
　　要告终 MySQL 的 Replication ，率先定然敞开 Master 端的Binary Log(mysql-bin.******)功能，否则无法告终。因为全副复制过程切实上即便Slave从Master端获得该日志然后再在自己身上全面次序的厉行日志中所登记的各种垄断。敞开 MySQL 的 Binary Log 能够穿越在启用 MySQL Server 的过程中利用 “—log-bin” 参数选项，可能在 my.cnf 搭配文件中的 mysqld 参数组([mysqld]标识后的参数局部)添置 “log-bin” 参数项。
　　MySQL 复制的大约过程如下：
　　1. Slave 上面的IO线程连接上 Master，并哀求从指定日志文件的指定位置(可能从最开始的日志)尔后的日志内容;
　　2. Master 接收到来自 Slave 的 IO 线程的哀求后，穿越负责复制的 IO 线程依据哀求消息读取指定日志指定位置尔后的日志消息，归来给 Slave 端的 IO 线程。归来消息中除非日志所包括的消息之外，还包括鄙碌回的消息在 Master 端的 Binary Log 文件的名目以及在 Binary Log 中的位置;
　　3. Slave 的 IO 线程接收到消息后，将接收到的日志内容顺次写入到 Slave 端的Relay Log文件(mysql-relay-bin.******)的最末路，并将读取到的Master端的bin-log的文件名和位置登记到master- info文件中，以便在下顺次读取的时候能够打听的高速Master“我必需从某个bin-log的哪个位置开始后来的日志内容，请发给我”
　　4. Slave 的 SQL 线程检测到 Relay Log 中新添置了内容后，会即刻解析该 Log 文件中的内容成为在 Master 端诚厉行行时候的那些可厉行的 Query 语句，并在切身厉行这些 Query。这么，切实上即便在 Master 端和 Slave 端厉行了同样的 Query，因而两端的数据是全面一样的。
　　切实上，在老版本中，MySQL 的复制告终在 Slave 端并不是由 SQL 线程和 IO 线程这两个线程共同配合而告终的，而是由独自的一个线程来告终所有的工作。然而 MySQL 的工程师们很快觉察，这么做存在很大的危险和功能问题，重要如下：
　　率先，万一穿越一个单一的线程来自力更生告终这个工作的话，就使复制 Master 端的，Binary Log日志，以及解析这些日志，然后再在切身厉行的这个过程成为一个串行的过程，功能慷慨会受到较大的局限，这种架构下的 Replication 的迟到慷慨就比拟长了。
　　其次，Slave 端的这个复制线程从 Master 端获得 Binary Log 到来尔后，必需随后解析这些内容，还原成 Master 端所厉行的原始 Query，然后在切身厉行。在这个过程中，Master端很可能又曾经发生了许多的改变并生成了许多的 Binary Log 消息。万一在这个阶段 Master 端的存储系统揭示了无法修复的故障，那么在这个阶段所发生的所有改变都将永远的失落，无法再找归来。这种埋伏危险在Slave 端压力比拟大的时候尤其冒尖，因为万一 Slave 压力比拟大，解析日志以及利用这些日志所花费的工夫慷慨就会更长一些，可能失落的数据也就会更多。
　　因而，在后期的改革中，新版本的 MySQL 为了尽量减小这个危险，并长进复制的功能，将 Slave 端的复制改为两个线程来告终，也即便前面所提到的 SQL 线程和 IO 线程。最早提出这个改进计划的是Yahoo!的一位工程师“Jeremy Zawodny”。穿越这么的改革，这么既在很大程度上处理了功能问题，缩小了异步的延随工夫，同时也收缩了埋伏的数据失落量。
　　当然，即便是换成了目前这么两个线程来配合处理尔后，同样也还是存在 Slave 数据延时以及数据失落的可能性的，终究这个复制是异步的。凡是数据的改动不是在一个事务中，这些问题都是存在的。
　　万一要全面避免这些问题，就只能用 MySQL 的 Cluster 来处理了。不过 MySQL的 Cluster 懂得笔者写这局部内容的时候，依旧还是一个内存数据库的处理计划，也即便必需将所有数据包括索引全副都 Load 到内存中，这么就对内存的要求就极其大的大，对于等闲的公众化利用来说可厉行性并不是太大。当然，在之前与 MySQL 的 CTO David 沟通的时候得知，MySQL 目前正在不时改进其 Cluster 的告终，其中极其大的一个修改即便批准数据无须全副 Load 到内存中，而仅仅只是索引全副 Load 到内存中，我想信在告终该项改革尔后的 MySQL Cluster 将会更加受人迎接，可厉行性也会更大

MySQL索引类型及优化

predis操作大全

linux 指定文件类型批量更改目录权限

linux下如何删除（中文）乱码文件名的文件或者文件夹

jquery reference

一些常用开源软件

linux下 crontab文件在哪 crontab文件路径

linux三台以上服务器集群间建立ssh信任关系

利用rsync 从 windows2003⇒linux系统备份

mysql主从同步过程同步原理

Tag

最新发布

功能

链接表