1
MYSQL索引优化.doc
免费下载
写在前面：索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。考虑如下
情况，假设数据库中一个表有
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
是范围
查询。
of 19
免费下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论