MySQL学习（索引、引擎、优化）

索引对于查询的速度至关重要，理解索引也是数据库调优的起点。

1. 建立索引前，先设计好建立索引列的数据类型。
1）越小的数据类型性能越好：因为越小的数据类型对于硬盘读取、内存、CPU缓存都需要更少的空间，处理起来更快。
2）简单的数据类型更好：整型比字符型更好。
3）尽量避免使用NULL: 建立索引的列最好是Not Null约束的，如果一定要用NULL，可以用0或者某特殊值替代。因为在MySQL中，对于NULL的值很难进行查询优化，因为他们使得索引的计算更加复杂。

2. 索引概述：
对于任何关系型数据库，索引都是进行优化的最主要因素。对于少量的数据，不必使用索引。
如果对于多列进行索引（组合索引），列的顺序非常中意。MySQL仅对索引最左边的前缀进行有效的查找。
例如：存在组合索引(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列。

索引存储的值按照索引列中的顺序排序（先last_name, 在first_name, 再dob). 可以用B-Tree索引进行全关键字、关键字范围、关键字前缀进行查询。
所以可以对如下情形生效：
1）匹配全值(Match the full value): 对索引中所有列都指定具体的值。例如查找出1960-01-01出生的Cuba Allen.
2）匹配最左前缀(Match a leftmost prefix): 可以利用索引查找last_name为Allen的人。
3）匹配列前缀(Match a column prefix): 可以列用索引查找last_name为J开头的人，仅仅使用索引中的第一列。
4）匹配值的范围查询(Match a range of values): 可以利用索引查找last_name在Allen和Barrymore之间的人，仅仅使用索引中的第一列。
5）匹配部分精确而其他部分进行范围匹配（Match one part exactly and match a range on another part): 可以利用索引查找last_name为Allen, 而first_name为K开头的人。

因为B-Tree中的节点都是顺序存储的，所以可以利用索引进行查找（找到某值），也可以对查询结果order by.

使用B-Tree索引有下面的限制：
1）查询必须从索引的最左边的列开始
2）不能跳过某一列索引：即不能利用索引查询last_name为Allen, 且1980-01-01出生的人。必须先查first_name后再查dob.
3）存储引擎不能使用索引中范围条件右边的列。例如：where last_name='Smith' and first_name like 'J%' and dob='1976-01-02', 则索引只对last_name和first_name有效，因为like是范围查询。

2.1.2 Hash索引
MySQL中，只有Memory存储引擎支持hash索引，是Memory引擎默认的索引类型。尽管Memory表也可以使用B-Tree索引。

3. 高性能的索引策略
3.1 聚簇索引(Clustered Indexes) -- 在MySQL的InnoDB引擎中，主键索引就是聚簇索引。
聚簇索引保证关键字的值相近的元组存储的物理位置也相同（所以字符串类型不适合创建索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表只能有一个聚簇索引。
目前，支持聚簇索引的引擎只有InnoDB和solidDB.

3.1.1 聚簇索引和非聚簇索引的区别：InnoDB引擎支持聚簇索引，而MyISAM不支持聚簇索引。
例如：
create table layout_test (
col1 int NOT NULL,
col2 int NOT NULL,
primary key(col1),
key(col2)
);
假设主键位于1--10000之间，且按随机顺序插入，然后由optimize table进行优化。col2随机赋予1--100之间的值，所以会存在许多重复的值。
1）MyISAM的布局：
MyISAM按照插入的顺序在磁盘上存储数据。
注：MyISAM不支持聚簇索引，索引中每一个叶子节点仅仅包含行号（row number), 且叶子节点按照col1的顺序存储。
2）InnoDB的布局：
InnoDB按聚簇索引的形式存储数据。
注：聚簇索引总每个叶子节点包含primary key的值，事务ID和回滚指针（rollback pointer).

3.2 覆盖索引（Covering Indexes)
如果索引包含满足查询的所有数据，就成为覆盖索引。
覆盖所有有如下好处：
1）索引项通常比记录要小，所以MySQL访问更好的数据。
2）索引都按值的大小顺序存储，相对于随机访问记录，需要更少的IO.
3）大多数数据引擎更好的缓存了索引。比如MySQL只缓存索引。
4）覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚簇索引组织数据，如果二级索引中包含所需的数据，就不需要在索引中查找了。

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;
结果：
id:1
select_type:SIMPLE
table:actor
type:index
possible_keys:NULL
key:PRIMARY
ref:NULL
rows:4
Extra:Using index
1 row in set(0.00 sec)

mysql>explain select actor_id from actor order by password;
结果：
id:1
select_type:SIMPLE
table:actor
type:index
possible_keys:NULL
key:NULL
key_len:NULL
ref:NULL
rows:4
Extra:Unsing filesort
1 row in set(0.00 sec)

mysql>explain select actor_id from actor order by name;
结果：
id:1
select_type:SIMPLE
table:actor
type:index
possible_key:NULL
key:name
key_len:18
ref:NULL
rows:4
Extra:Using index

当MySQL不能使用索引进行排序时，就会利用自己的排序算法（快速排序算法）在内存（sort buffer)中对数据进行排序。如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个数据库合并成有序的结果集。

3.4 索引与加锁
索引对于InnoDB非常重要，因为它可以让查询锁定更少的元组。因为InnoDB直到事务提交时才会解锁。有两个原因：
1. 即使InnoDB行级锁非常高效，内存开销非常小，但还是存在开销。
2. 对不需要的元组加锁，会增加锁的开销，降低并发性能。
InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。

===========================
MyISAM和InnoDB的区别：
1. 事务处理：MyISAM不支持，InnoDB支持
2. 性能：MyISAM好于InnoDB
3. fulltext索引：MyISAM支持，InnoDB不支持
4. select count(*) from table1; MyISAM效率好：MyISAM仅读出保存好的行数，而InnoDB要扫描一遍整个表。
5. auto_increment: MyISAM可以用该列与其他列一起建立联合索引，而InnoDB只支持包含只有该字段的索引。
6. delect from table; InnoDB会一行一行删除，不会重新建立表。

综上，MyISAM和InnoDB的主要区别是，InnoDB支持事务处理和行级锁。而MyISAM不支持，所以MyISAM往往被认为只适合在小项目中应用。
MyISAM的好处：
1. 对于读多写少的情形，MyISAM的性能比InnoDB好很多。
2. MyISAM的索引和数据是分开的，并且索引是有压缩的，这样内存使用率就提高了不少。而InnoDB的索引和数据是紧密捆绑的，并没有使用压缩，从而会造成InnoDB的体积更庞大。

一般来说，MyISAM适合：
1. 做很多count计算
2. 插入不频繁，查询非常频繁
3. 没有事务

InnoDB适合于：
1. 可靠性要求比较高，或者要求事务
2. 表更新和查询都非常的频繁，并且表锁定的机会比较大。

============================

MySQL的explain

explain的作用是显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
使用方法：在select语句前加上explain就可以了。

例如：explain select surname, first_name from a, b where a.id=b.id

分析结果形式如下：
table | type | possible_keys | key | key_len | ref | rows | extra

分别解释如下：
1. table：显示这一行的数据是关于哪张表的
*2. type：显示连接使用了哪种类型。最好到最差的连接类型为：const, eq_reg, ref, range, index, ALL
3. possible_key: 显示可以应用在这张表上的索引。
*4. key: 实际使用的索引。如果为NULL, 则没有使用索引。
5. key_len: 索引的长度
6. ref: 显示索引的哪一列被使用了。
7. rows: MySQL认为必须检查的用来返回请求数据的行数。
*8. extra: 额外信息：
1）distinct: 一旦找到与行相匹配的行，就不再搜索了。
2）not exist: MySQL优化了左连接，一旦它找到了匹配left join标准的行，就不再搜索了。
3）using filesort: 指Mysql将用外部排序而不是按照index顺序排列结果。数据较少时从内存排序，否则从磁盘排序。Explain不会显示的告诉客户端用哪种排序。
4）using index: 表示Mysql使用覆盖索引避免全表扫描，不需要再到表中进行二次查找数据。


已有 0 人发表留言，猛击->> 这里<<-参与讨论


ITeye推荐

• —软件人才免语言低担保 赴美带薪读研！—