了解MySQL-索引与优化
索引树对查询的速度有着关键的影响，如下情况：
数据库中一个表有10^6条记录，DBMS的页面大小为4k，并存储100条记录，如没有索引，
查询将对整个表进行扫描，最坏情况下，如果所有数据页不在内存中，需要读取10^4个
页面，如这10^4个页面在磁盘上随机分布，需要进行10^4次IO，假设磁盘每次IO时间为
10MS(不计数据传输时间)，则总共需要100s(但实际上要好很多的)。如对其建立B-Tree
索引，则需要进行log100(10^6)=3次页面读取，最坏情况耗时30ms。很多时候，如应用
程序进行SQL查询速度很慢时，考虑是否可以建索引；
**索引与优化
1.索引数据类型
1）数据类型要小，小对磁盘，内存和cpu缓存需要更少空间，处理起来更快；
2）整型数据比字符，处理开销小，因字符串的比较复杂；
3）列用NOT NULL 替代 NULL，除非想存储NULL，因它们使得索引，索引统计信息以及比
较运算更加复杂，含有空值的列进行查询优化很难;
2.选择标识符
1）整型，更快处理，用于设置：auto_increment
2)字符串，避免用字符串为标识符，消耗更多空间，处理很慢，且字符串随机的，所以在
索引中的位置也是随机的，会导致页面分裂，随机访问磁盘，聚集索引分裂(对于使用聚集
索引存储引擎)
3.索引入门
如对多列进行索引(组合索引)，列的顺序非常重要，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 不能使用该索引，因没有组合索引的引导列c1,要想使用c2列进行查找，必须
出现c1等于某值.
4.索引的类型
索引是在存储引擎中实现的，而不是在服务器层实现的，所以,每种存储引擎的索引都不一定
完全相同，并不是所有的存储引擎都支持所有索引类型;
1)二级索引:key(last_name,first_name,dob) 大致结构描述：
索引存储的值按索引列中的顺序排列，
-------------
| last_name |
-------------
| first_name |
-------------
| dob |
-------------
2) hash 索引
MySQL中，只有Memory显示支持hash索引，其支持非唯一hash索引，如多个值有相同的hash code，
索引把它们的行指针用链表保存到同一个hash表项中;
key using hash(fname)
假设索引使用hash函数f(),如下：
f('Argen')=2323
f('Baron')=7437
f('Peter')=8784
f('Vadim')=2458
索引结构如下：
slot value
2323 Pointer to row 1
2458 Pointer to row 4
7437 Pointer to row 2
8784 Pointer to row 3
slot是有序的，但是在记录不是有序的，当你执行
select * 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不能加速查询;
5.高性能索引策略
聚集索引(clustered indexes)
聚集索引保证关键字的值相近的元组存储的物理位置相同(所以字符串类型不用于建立聚集索引，特别
是随机字符串，会使得系统进行大量的移动操作)，且一个表只能有一个聚集索引，由存储引擎实现索引，
所以，并不是所有引擎支持聚集索引，只有InnoDB和solidDB
注意：叶子页面包含完整的元组，而内节点页面仅包含索引的列(索引的列位整型)，InnoDB对主键建立聚集
索引，如不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替.如果不存在这样的索引，InnoDB会定
义一个影藏的主键，而后对其建立聚集索引。
InnoDB和Myisam数据布局的比较：如下表:
create table layout_test(
col1 int not null,
col2 int not null,
primary key(col1),
key(col2)
)engine=Innodb charset=utf8;
主键的值位于1---10k之间，且按随机顺序插入,而后optimize table进行优化，col2随机赋予1---100之间
的值，会存在重复的值；
1）myisam的数据布局
myisam按照插入顺序在磁盘上存储数据，如下：
row number col1 col2
0 99 8
1 12 56
2 300 63
....
....
9997 18 8
9998 4700 13
9999 3 93
注意：左边行号(row number),从0开始，因元组的大小固定，所以myisam可很容易从表的开始位置
找到某一字节的位置；
primary key的索引结构描述：
Internal nodes
leaf nodes in col1 orders
注意：myisam不支持聚集索引，索引中每个叶子节点仅仅包含行号(row number),且叶子节点按照col1(主键)的
顺序存储；
看看col2的索引结构：
Internal nodes
leaf nodes in col2(索引) orders
综述:在myisam中，primary key 和其他索引没什么区别，primary key仅仅只是一个叫做primary的唯一，
非空的索引而已;
2)InnoDB数据布局
按照聚集索引的形式存储数据，它的数据布局有很大不同，存储表的结构描述：
primary key columns(col1)
transaction Id
rollback pointer
Non-PK columns(col2)
注:聚集索引中的每个叶子节点包含primary key的值，事务Id和回滚指针(rollback pointer)--用于
事务和mvcc，和余下的列(如col2)
相对于Myisam，二级索引与聚集索引很大不同,InnoDb的二级索引的叶子包含primary key的值，而不
是行指针(row pointers),这减小了移动数据或数据页面分裂时维护二级索引的开销,InnoDB不需要更新
新索引的行指针.
当mysql不能使用索引进行排序时，它会利用自己的排序算法在内存(sort buffer)中对数据进行排序，
如内存装不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，而后将各个块合并成有顺的
结果集.对filesort，MySQL有2种排序算法:
1)2遍扫描法:
先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存(通过参数sort_buffer_size)
中进行排序，完成排序之后再次通过行指针信息取出所需的columns.
6.索引与加锁
索引对innodb非常重要，因可以让查询锁更少的元组，MySQL5.0中，innodb直到事务提交时才会解锁，有2个原因：
1）innodb行锁开销非常高效，内存开销也较少，
2）不需要的元组加锁，会增加锁的开销，减低并发性；
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中第二个条件已经无法使用索引了）
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)
表明存储引擎从索引的起始处开始，获取所有的行，直到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会进行全表扫
描，并锁住每一个元组，不管是否真正需要。
索引树对查询的速度有着关键的影响，如下情况：
数据库中一个表有10^6条记录，DBMS的页面大小为4k，并存储100条记录，如没有索引，
查询将对整个表进行扫描，最坏情况下，如果所有数据页不在内存中，需要读取10^4个
页面，如这10^4个页面在磁盘上随机分布，需要进行10^4次IO，假设磁盘每次IO时间为
10MS(不计数据传输时间)，则总共需要100s(但实际上要好很多的)。如对其建立B-Tree
索引，则需要进行log100(10^6)=3次页面读取，最坏情况耗时30ms。很多时候，如应用
程序进行SQL查询速度很慢时，考虑是否可以建索引；
**索引与优化
1.索引数据类型
1）数据类型要小，小对磁盘，内存和cpu缓存需要更少空间，处理起来更快；
2）整型数据比字符，处理开销小，因字符串的比较复杂；
3）列用NOT NULL 替代 NULL，除非想存储NULL，因它们使得索引，索引统计信息以及比
较运算更加复杂，含有空值的列进行查询优化很难;
2.选择标识符
1）整型，更快处理，用于设置：auto_increment
2)字符串，避免用字符串为标识符，消耗更多空间，处理很慢，且字符串随机的，所以在
索引中的位置也是随机的，会导致页面分裂，随机访问磁盘，聚集索引分裂(对于使用聚集
索引存储引擎)
3.索引入门
如对多列进行索引(组合索引)，列的顺序非常重要，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 不能使用该索引，因没有组合索引的引导列c1,要想使用c2列进行查找，必须
出现c1等于某值.
4.索引的类型
索引是在存储引擎中实现的，而不是在服务器层实现的，所以,每种存储引擎的索引都不一定
完全相同，并不是所有的存储引擎都支持所有索引类型;
1)二级索引:key(last_name,first_name,dob) 大致结构描述：
索引存储的值按索引列中的顺序排列，
-------------
| last_name |
-------------
| first_name |
-------------
| dob |
-------------
2) hash 索引
MySQL中，只有Memory显示支持hash索引，其支持非唯一hash索引，如多个值有相同的hash code，
索引把它们的行指针用链表保存到同一个hash表项中;
key using hash(fname)
假设索引使用hash函数f(),如下：
f('Argen')=2323
f('Baron')=7437
f('Peter')=8784
f('Vadim')=2458
索引结构如下：
slot value
2323 Pointer to row 1
2458 Pointer to row 4
7437 Pointer to row 2
8784 Pointer to row 3
slot是有序的，但是在记录不是有序的，当你执行
select * 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不能加速查询;
5.高性能索引策略
聚集索引(clustered indexes)
聚集索引保证关键字的值相近的元组存储的物理位置相同(所以字符串类型不用于建立聚集索引，特别
是随机字符串，会使得系统进行大量的移动操作)，且一个表只能有一个聚集索引，由存储引擎实现索引，
所以，并不是所有引擎支持聚集索引，只有InnoDB和solidDB
注意：叶子页面包含完整的元组，而内节点页面仅包含索引的列(索引的列位整型)，InnoDB对主键建立聚集
索引，如不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替.如果不存在这样的索引，InnoDB会定
义一个影藏的主键，而后对其建立聚集索引。
InnoDB和Myisam数据布局的比较：如下表:
create table layout_test(
col1 int not null,
col2 int not null,
primary key(col1),
key(col2)
)engine=Innodb charset=utf8;
主键的值位于1---10k之间，且按随机顺序插入,而后optimize table进行优化，col2随机赋予1---100之间
的值，会存在重复的值；
1）myisam的数据布局
myisam按照插入顺序在磁盘上存储数据，如下：
row number col1 col2
0 99 8
1 12 56
2 300 63
....
....
9997 18 8
9998 4700 13
9999 3 93
注意：左边行号(row number),从0开始，因元组的大小固定，所以myisam可很容易从表的开始位置
找到某一字节的位置；
primary key的索引结构描述：
Internal nodes
leaf nodes in col1 orders
注意：myisam不支持聚集索引，索引中每个叶子节点仅仅包含行号(row number),且叶子节点按照col1(主键)的
顺序存储；
看看col2的索引结构：
Internal nodes
leaf nodes in col2(索引) orders
综述:在myisam中，primary key 和其他索引没什么区别，primary key仅仅只是一个叫做primary的唯一，
非空的索引而已;
2)InnoDB数据布局
按照聚集索引的形式存储数据，它的数据布局有很大不同，存储表的结构描述：
primary key columns(col1)
transaction Id
rollback pointer
Non-PK columns(col2)
注:聚集索引中的每个叶子节点包含primary key的值，事务Id和回滚指针(rollback pointer)--用于
事务和mvcc，和余下的列(如col2)
相对于Myisam，二级索引与聚集索引很大不同,InnoDb的二级索引的叶子包含primary key的值，而不
是行指针(row pointers),这减小了移动数据或数据页面分裂时维护二级索引的开销,InnoDB不需要更新
新索引的行指针.
当mysql不能使用索引进行排序时，它会利用自己的排序算法在内存(sort buffer)中对数据进行排序，
如内存装不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，而后将各个块合并成有顺的
结果集.对filesort，MySQL有2种排序算法:
1)2遍扫描法:
先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存(通过参数sort_buffer_size)
中进行排序，完成排序之后再次通过行指针信息取出所需的columns.
6.索引与加锁
索引对innodb非常重要，因可以让查询锁更少的元组，MySQL5.0中，innodb直到事务提交时才会解锁，有2个原因：
1）innodb行锁开销非常高效，内存开销也较少，
2）不需要的元组加锁，会增加锁的开销，减低并发性；
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中第二个条件已经无法使用索引了）
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)
表明存储引擎从索引的起始处开始，获取所有的行，直到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会进行全表扫
描，并锁住每一个元组，不管是否真正需要。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26855487/viewspace-761424/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26855487/viewspace-761424/
3595
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
