MySQL优化1

1.1识别性能问题

1.1.1 寻找运行缓慢的SQL语句

命令：

Show full processlist

1.1.2 确认低效查询

1 运行SQL语句并记录执行时间

Select * from inventory where item_id = 16102176;

2.生成查询执行计划QEP

QEP查询执行计划决定了从低层存储引擎中获取信息的方式

加入EXPLAIN 前缀

EXPLAIN select * from person where id = 1

Key是索引

Rows是数据量

1.2 优化查询

Show create table inventory 查看表结构

Show table status like inventory 查看表大小

Alter table inventory add index（item_id）添加索引

第二章基本的分析命令

命令作用：

用户可以重复获取信息分析信息然后进行最终优化

2.1 EXPLAIN命令：

用户可以通过这条命令了解MYSQL的基本开销的优化器，还可以获得很多可能被优化器考虑到的访问策略，以及mysql语句时哪种策略预计会被优化器采用

2.1.2 EXPLAIN PARTITIONS命令

EXPLAIN PARTITIONS SELECT * FROM audit_log where yr in (2011,2012)

查看partitions（分隔，分区）列中查询特定表分区提供的附加信息

2.1.3 EXPLAIN EXTENDED命令

EXPLAIN EXTENDED SELECT t1.name from test1 t1 inner join test2 t2

Using(uid)

Mysql优化器可能会在运行时候简化或重写用户的SQL命令

还要用到show warnings命令联合使用

2.2 show create table 命令

Show create table 命令以一种便于阅读和操作的格式向用户呈现基本表中的当前列和索引定义的全部细节

Show create table wp_options

2.3 show indexes 命令

Show indexes命令查看索引信息，这些信息包括索引的类型和包括当前索引基数

Cardinality基数

该值表示每一列唯一值的数量估计值

2.4 show table status 命令

Show table status查看数据库大小以及表结构

2.5 show status 命令

Show globel status

Show session status

可以查看MYSQL服务器的当前内部状态信息

Show globel status like ‘created_tmp_%tables’’

2.6 show variables命令

Show globel variables

Show session variables

检查MYSQL系统当前变量值

2.7 INFORMATION_SCHEMA

第三章深入理解MYSQL的索引

3.1 示例表

3.2 MYSQL索引的用法

3.2.1 数据的完整性

MYSQL用主键和唯一键（unique key）来执行每个表中存储数据的唯一性等级

主键：

每个表只有一个主键

主键不能包含NULL值

唯一键：

表可以有多个主键

唯一键可以包含null值

都不可以重复添加

外键其实不是索引，它们属于约束

3.2.2 优化数据访问

索引可以让优化器在执行查询的时候不必检索表中的所有数据

3.2.3 表连接

索引的另一个主要用途就是快捷高效地在相关的表之间做连接操作

3.2.4 结果排序

3.2.5 聚合操作

3.3 关于存储引擎

有三种存储引擎：

MyISAM:一种非事务性的存储引擎，

InnoDB：最流行的事务性存储引擎，

Memory：基于内存的存储引擎

第四章：创建MYSQL索引

4.2 已有的索引

1.先查看普通SQL语句，获取指定艺人的信息

Select artist_id,type,founded from artist

Where name = ‘Coldplay’;

首先在SELECT语句前，加EXPLAIN查看QEP

EXPLAIN Select artist_id,type,founded from artist

Where name = ‘Coldplay’\G；

查看表结构，索引以及使用的存储引擎

Show create table

Name 已经在索引列上了

4.3 单列索引

4.3.1 创建单列索引的语法

单列索引是最基础的索引

ALTER TABLE <TABLE> ADD PRIMARY KEY [INDEX-NAME] (<COLUMN>);

ALTER TABLE <TABLE>ADD [UNIQUE] KEY|INDEX [INDEX-NAME](<COLUMN>);

4.3.2 利用索引限制查询读取的行数

我们通过创建索引来避免每次扫描整张表，

如果EXPLAIN的结果看到type=ALL或者KEY=null，

则可判断这条查询扫描了整张表

EXPLAIN SELECT artist_id ,type,founded from artist where found = 1942

可以用founded列上创建一个索引：

ALTER TABLE artist ADD INDEX (founded);

重新执行EXPLAIN SELECT语句查看索引是否被使用关了

EXPLAIN SELECT artist_id ,type,founded from artist where found = 1942

如果创建了重复索引：

ALTER TABLE artist ADD INDEX （founded）;

EXPLAIN EXPLAIN SELECT artist_id ,type,founded from artist where found = 1942

4.3.3 使用索引连接表

索引的另一个好处是提高关系表连接操作性能

获取指定艺人的专辑信息：

EXPLAIN SELECT ar.name ,ar.founded,al.name,al.first_released

From artist ar

Inner join alum al USING (artist_id)

Where ar.name = ‘QUEEN’\G;

这条结果显示album表执行全表查询

通过连接条件添加索引重复EXPLAIN命令来解决问题

ALTER TABLE album ADD INDEX (artist_id)；

EXPLAIN SELECT ar.name ,ar.founded,al.name,al.first_released

From artist ar

Inner join alum al USING (artist_id)

Where ar.name = ‘QUEEN’\G;

4.3.4 理解索引的基数

当创建不止一个索引的时候，MYSQL视图会找到一个最搞笑的索引

例如：

查询1980年的所有品牌

我们在artist 表type列创建一个索引

ALTER TABLE ARTIST ADD INDEX (type);

SET @@SESSION.optimizer_switch = ‘index_mergeintersection=off’;

EXPLAIN SELECT artist_id,name,country_id from artist

Where type=’Band’

And founded = 1980\G;

MYSQL 会在Possible_keys来列出索引中做出选择

优化器会根据最少工作量开销来选择索引

利用索引基数确定可能选中索引

Show indexes from artist\G;

这些信息表明founded列拥有更高的基数，也就是说该列中唯一值的数量越多，那么越有可能在选用这个索引时以更少的读操作中找到需要的记录。这些统计信息只是估计值。从数据分析中我们可以知道，artist表中type只有4个唯一值，但在统计信息中则不是这样。

关于基数不得不提的一点就是选择性。仅仅知道索引中唯一值的数目意义并不大，重要的是将这个数值和索引中的总行数做比较。选择性就是表中明确值的数量和表中包含的记录的总数的关系。理想情况下，选择性值为1，且每一个值都是一个非空唯一值。一个有着优秀选择性的索引意味着有更少的相同值的行。当某一列中仅仅有少数不同的值的时候就会有较差的选择性——例如性别或者状态列。当查询需要用到所有列时，这些信息不但可以帮助我们判断索引是否高效，还可以告诉我们如何在多列索引中对列进行排序。

结果中显示的索引基数提供了一些简单的线索。下面的两个查询想要查找20世纪80年代的乐队和组合。

mysql> EXPLAIN SELECT artist_id, name, country_id

-> FROM artist

-> WHERE founded BETWEEN 1980 AND 1989 AND

type='Band'\G

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

...

possible keys: founded,founded_2,type

key: founded

key_len: 2

ref: NULL

rows: 18690

Extra: Using where

mysql> EXPLAIN SELECT artist_id, name, country_id

-> FROM artist

-> WHERE founded BETWEEN 1980 AND 1989 AND

type='Combination'\G

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

possible keys: founded,founded_2,type

key: type

key_len: 1

ref: const

rows: 19414

Extra: Using where

这两个查询看起来很简单，但它们却根据列信息分布的详细统计信息选择了不同的索引路径。

4.3.5 利用索引进行模式匹配

利用通配符可以通过索引来做模式匹配的工作。请看下面的示例：

mysql> EXPLAIN SELECT artist_id, type, founded

-> FROM artist

-> WHERE name LIKE 'Queen%'\G

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

id: 1

select_type: SIMPLE

table: artist

type: range

possible_keys: name

key: name

key_len: 257

ref: NULL

rows: 93

Extra: Using where

如果你查找的词是以通配符开头，则MySQL不会使用索引。请看下面的示例：

mysql> EXPLAIN SELECT artist_id, type, founded

-> FROM artist

-> WHERE name LIKE '%Queen%'\G

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

id: 1

select_type: SIMPLE

table: artist

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 585230

Extra: Using where

技巧

如果你经常需要一个以通配符开头的查询，常用的方法是在数据库中保存需要查询的值的反序值。例如，假设你想要找所有以.com结尾的电子邮件地址，当搜索email Like '%.com' 时MySQL不能使用索引；而搜索reverse_email LIKE REVERSE('%.com')就可以使用定义在reverse_email列上的索引。

MySQL不支持基于索引的函数。如果想创建一个带有列函数的索引将会导致语法错误。不同数据库产品背景知识的开发者在执行下面的语句遇到一个共同问题，希望在name列上的一个索引能够被用来满足这个查询：

mysql> EXPLAIN SELECT artist_id, type, founded

-> FROM artist

-> WHERE UPPER(name) = UPPER('Billy Joel')\G

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

id: 1

select_type: SIMPLE

table: artist

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 585230

Extra: Using where

因为使用了应用到name列上的UPPER函数，MySQL不会使用name上的索引。

4.3.6 选择唯一的行

如果我们想要保证每个艺人都有一个唯一的名字，可以创建唯一索引。唯一索引有两个目的：

●提供数据完整性以保证在列中任何值都只出现一次

●告知优化器对给定的记录最多只可能有一行结果返回；这一点很重要，因为有了这些信息就可以避免额外的索引扫描

可以使用第2章介绍的SHOW STATUS命令来查看一般索引和唯一索引在查询内部造成的不同影响。下面就是一个使用已有的非唯一索引的示例：

mysql> FLUSH STATUS;

mysql> SHOW SESSION STATUS LIKE 'Handler_read_next';

mysql> SELECT name FROM artist WHERE name ='Enya';

mysql> SHOW SESSION STATUS LIKE 'Handler_read_next';

输出结果如下：

+-------------------+--------+

| Variable_name | Value |

+-------------------+--------+

| Handler_read_next | 0 |

+-------------------+--------+

+------+

| name |

+------+

| Enya |

+------+

+-------------------+--------+

| Variable_name | Value |

+-------------------+--------+

| Handler_read_next | 1 |

+-------------------+--------+

在内部，MySQL会去读索引中下一项记录来判断name索引的下一个值不是那个指定的值。创建一个唯一索引并再次运行同一个查询，我们可以看到以下结果：

mysql> ALTER TABLE artist DROP INDEX name,

-> ADD UNIQUE INDEX(name);

+-------------------+--------+

| Variable_name | Value |

+-------------------+--------+

| Handler_read_next | 0 |

+-------------------+--------+

+------+

| name |

+------+

| Enya |

+------+

+-------------------+--------+

| Variable_name | Value |

+-------------------+--------+

| Handler_read_next | 0 |

+-------------------+--------+

对比两个结果可以发现，当使用唯一索引时MySQL知道最多只可能返回一行数据，当找到一个匹配结果之后就不需要继续扫描了。当数据确实是唯一的情况下，把索引定义为唯一索引是非常好的方式。

技巧

在可以为空的列上定义唯一索引也是可行的。这种情况下， NULL的值被认为是一个未知的值，并且NULL!=NULL。这就是三态逻辑的好处，它避免了使用默认值或者一个空字符串值。

4.3.7 结果排序

索引也可以用来对查询结果进行排序。如果没有索引，MySQL会使用内部文件排序算法对返回的行按照指定顺序进行排序。请看下面的示例：

mysql> EXPLAIN SELECT name,founded

-> FROM artist

-> WHERE name like 'AUSTRALIA%'

-> ORDER BY founded\G

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

id: 1

select_type: SIMPLE

table: artist

type: range

possible_keys: name

key: name

key_len: 257

ref: NULL

rows: 22

Extra: Using where; Using filesort

可以看到，通过在Extra的属性中设置了Using filesort信息， MySQL内部使用sort_buffer来对结果进行排序。

也可以通过下面的命令从内部确认上述结论：

mysql> FLUSH STATUS;

mysql> SELECT ...

mysql> SHOW SESSION STATUS LIKE '%sort%';

+-------------------+--------+

| Variable_name | Value |

+-------------------+--------+

| Sort_merge_passes | 0 |

| Sort_range | 1 |

| Sort_rows | 22 |

| Sort_scan | 0 |

+-------------------+--------+

通过使用基于索引的数据排序方法，就可以免去分类的过程了，如下所示：

mysql> EXPLAIN SELECT name,founded

-> FROM artist

-> WHERE name like 'AUSTRALIA%'

-> ORDER BY name\G

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

...

key: name

key_len: 257

ref: NULL

rows: 22

Extra: Using where

mysql> FLUSH STATUS;

mysql> SELECT ...

mysql> SHOW SESSION STATUS LIKE '%sort%';

+-------------------+--------+

| Variable_name | Value |

+-------------------+--------+

| Sort_merge_passes | 0 |

| Sort_range | 0 |

| Sort_rows | 0 |

| Sort_scan | 0 |

+-------------------+--------+

接下来我们将讨论如何使用索引来限制返回的行并使用多列索引对结果排序。

4.4 多列索引

索引可以创建在两列或多列上。多列索引也被称为混合索引或者连接索引。

4.4.1 确定使用何种索引

我们来看一个查询，这个查询根据WHERE语句的限制在表上使用两个不同的索引。我们首先创建这些索引。

mysql> ALTER TABLE album

-> ADD INDEX (country_id),

-> ADD INDEX (album_type_id);

Query OK, 553875 rows affected (18.89 sec)

尽可能地合并给定表DML语句会获得更高的效率。如果选择以两条独立语句的方式分别运行这些ALTER语句，则会有下面这样的结果：

mysql> ALTER TABLE album DROP index country_id, drop

index album_type_id;

Query OK, 553875 rows affected (15.72 sec)

mysql> ALTER TABLE album ADD INDEX (country_id);

Query OK, 553875 rows affected (16.76 sec)

mysql> ALTER TABLE album ADD INDEX (album_type_id);

Query OK, 553875 rows affected (25.23 sec)

如果这是一张生产环境规模的表，而每条ALTER语句运行需要60分钟或者6小时，那么合并ALTER语句会显著地节省时间。

技巧

创建索引是一件非常耗时的工作，并且会阻塞其他操作。你可以使用一条ALTER语句将给定表上多个索引创建的语句合并起来。

mysql> EXPLAIN SELECT al.name, al.first_released,

al.album_type_id

-> FROM album al

-> WHERE al.country_id=221

-> AND album_type_id=1\G

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

id: 1

select_type: SIMPLE

table: al

type: ref

possible_keys: album_type_id,country_id

key: country_id

key_len: 3

ref: const

rows: 154638

Extra: Using where

注意

在这个示例中，根据你使用的MySQL不同版本，优化器可能经过改进而提供不同的QEP。在所有MySQL 5.x版本中使用以下的MySQL向后兼容性系统变量的设置，都可以在以下示例中获得一样的结果：

mysql> SET @@session.optimizer_switch='index_merge_

intersection=off';

但是如果我们对album type的不同值运行同一个查询，那么使用不同的索引：

mysql> EXPLAIN SELECT al.name, al.first_released,

al.album_type_id

-> FROM album al

-> WHERE al.country_id=221

-> AND album_type_id=4\G

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

id: 1

select_type: SIMPLE

table: al

type: ref

possible_keys: album_type_id,country_id

key: album_type_id

key_len: 4

ref: const

rows: 58044

Extra: Using where

为什么MySQL会这样决定？从EXPLAIN语句的rows列我们可以得出这样的结论：基于开销的优化器会选择开销更小的方式，也就是说相对于读取154 000行，优化器选择读取58 000行的方案。

mysql> SHOW INDEXES FROM album\G...

********************* 4. row ***********************

Table: album

Non _unique: 1

Key_name: album_type_id

Seq_in_index: 1

Column_name: album_type_id

Collation: A

Cardinality: 12

...

********************* 6. row ***********************

Table: album

Non_unique: 1

Key_name: country_id

Seq_in_index: 1

Column_name: country_id

Collation: A

Cardinality: 499

...

如果MySQL仅仅使用索引基数，那么你可能会认为QEP总是会使用country_id列，因为该列拥有更多的唯一值并且可以获取更少的行。尽管索引基数是唯一性的一个重要指标，但MySQL也会参考有关唯一值的范围和容量等统计信息。我们可以通过查看实际表分布来确定这些数目。

mysql> SELECT COUNT(*) FROM album where

country_id=221;

+-----------+

| count(*) |

+-----------+

| 92544 |

+-----------+

mysql> SELECT COUNT(*) FROM album where

album_type_id=4;

+-----------+

| count(*) |

+-----------+

| 111908 |

+-----------+

mysql> SELECT COUNT(*) FROM album where

album_type_id=1;

+-----------+

| count(*) |

+-----------+

| 289923 |

+-----------+

第一个查询选择了country_id列上的索引。实际结果显示获取了92 000行，而选择album_type_id则会有289 000行被获取。

第二个查询选择了album_type列上的索引的情况下，实际结果显示获取了111 000行，与之前的92 000行做比较。如果你把实际数量与QEP估算的行数做比较，你会发现一个矛盾——例如对于第二个查询估计有58 000行数据，但实际数据有111 000行之多，几乎是实际行数的两倍。

4．4.2 多列索引的语法

创建多列索引的语法和之前相同，唯一不同的是需要指定该索引是要跨越多列的：

ALTER TABLE <table>

ADD PRIMARY KEY [index-name]

(<column1>,<column2>...);

ALTER TABLE <table>

ADD [UNIQUE] KEY|INDEX [index-name]

(<column1>,<column2>...);

4.4.3 创建更好的索引

我们可以在国家和专辑类型列上创建多列索引，这样优化器就可以得到更多信息。请看下面的示例：
mysql> ALTER TABLE album ADD INDEX m1
(country_id, album_type_id);

然后我们重新运行下面的SQL语句可以得到如下QEP：
mysql> EXPLAIN SELECT al.name, al.first_released,
al.album_type_id
-> FROM album al
-> WHERE al.country_id=221
-> AND album_type_id=4\G
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table: al
type: ref
possible_keys: album_type_id,country_id,m1
key: m1
key_len: 7
ref: const,const
rows: 23800
Extra: Using where

这次优化器选择使用新的索引了。你可能还会注意到key_len =7。这是用来确定索引所使用的列的效率的工具。我们将会在更多示例中讨论这个问题。

按照这样的顺序来创建索引看起来是合理的；但由于你的查询同时使用到两列，你可能会选择使用相反的列序：
mysql> ALTER TABLE album ADD INDEX m2
(album_type_id,country_id);

再次查看QEP可以发现使用了新的索引：
mysql> EXPLAIN SELECT al.name, al.first_released,
al.album_type_id
-> FROM album al
-> WHERE al.country_id=221
-> AND album_type_id=4\G
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table: al
type: ref
possible_keys: album_type_id,country_id,m1,m2
key: m2
key len: 7
ref: const,const
rows: 18264
Extra: Using where

我们可以查看该表的索引基数来找出选择这条索引的原因：
mysql> SHOW INDEXES FROM album\G
...
********************* 7. row ***********************
Table: album
Key name: m1
Seq_in_index: 1
Column_name: country_id
Cardinality: 487
...
********************* 8. row ***********************
Table: album
Key_name: m1
Seq_in_index: 2
Column_name: album_type_id
Cardinality: 960
...
********************* 9. row ***********************
Table: album
Key_name: m2
Seq_in_index: 1
Column_name: album_type_id
Cardinality: 16
...
********************* 10. row **********************
Table: album
Key_name: m2
Seq_in_index: 2
Column_name: country_id
Cardinality: 682
...

如果我们仅仅观察索引基数，我们可能会认为m1将会被选中，因为它提供了唯一行的更高分布；然而，这条信息并没有提供足够的可以用于决定当前执行计划的细节信息。

技巧

当你在对一个交集表使用多列索引时，尤其是在每一列都有指定值时，交换列的顺序可能会创建出更好的索引。

多列索引除了优化限制返回的行之外还有更重要的用途。多列索引中最左边的列也可以被当做单一列索引来高效地使用。当这些列被频繁用于聚合操作(即GROUP BY操作)和排序操作(即ORDER BY操作)时，最左边的列也同样可以显著提升性能。

4.4.4 多个列上的索引

虽然索引可以包含多列，但实际上对索引的效率会有所限制。索引是用于改进性能的关系模型的一部分。索引的行的宽度应该尽可能的短，这样就可以在一个索引数据页面中包含更多的索引记录。这样做的好处是可以读取尽量少的数据，从而尽可能快地遍历索引。你可能还希望你的索引保持这样的高效，这样能使系统内存的使用最大化。

EXPLAIN命令结果中的key_len和ref两个属性的值可以用来判断选中的索引的列利用率。请看下面的示例：

mysql> ALTER TABLE artist ADD index

(type,gender,country_id);

mysql> EXPLAIN SELECT name FROM artist WHERE type=

'Person' AND gender='Male' AND country_id = 13\G

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

id: 1

select_type: SIMPLE

table: artist

type: ref

possible_keys: type,country_id,type_2

key: type_2

key_len: 6

ref: const,const,const

rows: 40

Extra: Using where

从结果中可以看到，ref列的值为3个常量，正好和索引中的3列匹配。key_len的值为6也同样证实了这一点：ENUM长度1字节，SMALLINT类型长度2字节，可以为空占用1字节，ENUM类型1字节，可以为空类型占用1字节。

如果我们不用国家这个条件来限制查询，则会得到以下结果：

mysql> EXPLAIN SELECT name FROM artist WHERE type=

'Person' AND gender='Male'\G

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

...

key: type_2

key_len: 3

ref: const,const

...

这里强调了当使用索引时，多余的列没有被用到查询中。如果没有其他查询用到了第3列，那么这就是一个可优化的点以减少索引行的宽度。

4.4.5 合并WHERE和ORDER BY语句

我们已经通过示例演示了如何使用索引优化数据行的限制条件，以及如何使用索引优化排序结果。MySQL还可以利用多列索引执行上述两种操作。

mysql> ALTER TABLE album ADD INDEX (name);

mysql> EXPLAIN SELECT a.name, ar.name,

a.first_released

-> FROM album a

-> INNER JOIN artist ar USING (artist_id)

-> WHERE a.name = 'Greatest Hits'

-> ORDER BY a.first_released\G

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

id: 1

select_type: SIMPLE

table: a

type: ref

possible_keys: artist_id,name

key: name

key_len: 257

ref: const

rows: 904

Extra: Using where; Using filesort

********************* 2. row ***********************

...

我们可以建立一个能够同时满足WHERE语句和ORDER BY语句的索引：

mysql> ALTER TABLE album

-> ADD INDEX name_release (name,first_released);

mysql> EXPLAIN SELECT a.name, ar.name,

a.first_released

-> FROM album a INNER JOIN artist ar USING

(artist_id)

-> WHERE a.name = 'Greatest Hits'

-> ORDER BY a.first_released\G

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

id: 1

select_type: SIMPLE

table: a

type: ref

possible_keys: artist_id,name,name_release

key: name_release

key_len: 257

ref: const

rows: 904

Extra: Using where

********************* 2. row ***********************

...

注意

优化器也可能为WHERE条件和ORDER BY语句使用一个索引；而这一点是不能从key_len的值看出来的。

技巧

创建一个能够用于对结果排序时的索引是有难度的；然而在某些频繁地(例如每秒100次)对相同数据进行排序的应用程序中，这样做将会带来很多益处。从使用PROCESSLIST命令查看sorting results的症状中，明显可以看出对CPU的影响，以及对一个经过优化的模式和SQL设计的参考方案的强烈需求。

4.4.6 MySQL优化器的特性

MySQL可以在WHERE、ORDER BY以及GROUP BY列中使用索引；然而，一般来说MySQL在一个表上只选择一个索引。从MySQL 5.0开始，在个别例外情况中优化器可能会使用一个以上的索引，但是在早期的版本中这样做会导致查询运行更加缓慢。最常见的索引合并的操作是两个索引取并集，当用户对两个有很高基数的索引执行OR操作时会出现这种这种索引合并操作。请看下面的示例：

mysql> SET @@session.optimizer_switch='

index_merge_intersection=on';

mysql> EXPLAIN SELECT artist_id, name

-> FROM artist

-> WHERE name = 'Queen'

-> OR founded = 1942\G

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

id: 1

select_type: SIMPLE

table: artist

type: index_merge

possible_keys: name,founded,founded_2

key: name,founded

key_len: 257,2

ref: NULL

rows: 500

Extra: Using union(name,founded); Using where

注意

在MySQL 5.1中首次引入了optimizer_switch系统变量，可以通过启用或禁用这个变量来控制这些附加选项。想了解更多信息可以参考以下链接：http://dev.mysql.com/doc/refman/5.1/en/switchable- optimizations.html。

第二种类型的索引合并是对两个有少量唯一值的索引取交集，如下所示：

mysql> SET @@session.optimizer_switch='index_merge_

intersection=on';

mysql> EXPLAIN SELECT artist_id, name

-> FROM artist

-> WHERE type = 'Band'

-> AND founded = 1942\G

...

Extra: Using intersect(founded,type); Using

where

第三种类型的索引合并操作和对两个索引取并集比较类似，但它需要先经过排序：

mysql> EXPLAIN SELECT artist_id, name

-> FROM artist

-> WHERE name = 'Queen'

-> OR (founded BETWEEN 1942 AND 1950)\G

...

Extra: Using sort_union(name,founded); Using

where

可以通过以下链接了解更多关于索引合并的信息：http://dev. mysql.com/doc/refman/5.5/en/index-merge-optimization.html。

在创建这些示例的过程中，笔者发现一种以前在任何客户端的查询中未曾出现过的新情况。以下是三个索引合并的示例：

mysql> EXPLAIN SELECT artist_id, name

-> FROM artist

-> WHERE name = 'Queen'

-> OR (type = 'Band' AND founded = '1942')\G

...

Extra: Using union(name,intersect(founded,

type)); Using where

技巧

应该经常评估多列索引是否比让优化器合并索列效率更高。

多个单列索引和多个多列索引到底哪个更有优势？这个问题只有结合特定应用程序的查询类型和查询容量才能给出答案。在各种不同的查询条件下，将一些高基数列上的那些单列索引进行索引合并能够带来很高的灵活性。数据库写操作的性能参考因素也同样会影响到获取数据的最优的数据访问路径。

4.4.7 查询提示

MySQL中少数几个查询提示会影响性能。这些查询提示有些会影响到整个查询，而有些会影响到每个表索引的用法。

1. 总查询提示

所有总查询提示都会在SELECT关键字之后立刻产生。这些选项包括SQL_CACHE、SQL_NO_CACHE、SQL_SMALL_RESULT、SQL_BIG_RESULT、SQL_BUFFER_RESULT、SQL_CALC_ FOUND _ ROWS以及HIGH_PRIORITY。上述提示不会影响到任何表上索引的使用，所以这里我们就不详细讨论了。

只有STRAIGHT_JOIN查询提示会对查询执行中索引的使用有影响。这个提示会告诉优化器按照查询中指定的表的顺序来执行查询执行计划。请看下面的示例：
mysql> EXPLAIN SELECT album.name, artist.name,
album.first_released
-> FROM artist INNER JOIN album USING (artist_id)
-> WHERE album.name = 'Greatest Hits'\G
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table: album
type: ref
possible_keys: artist_id,name,name_release
key: name
key_len: 257
ref: const
rows: 904
Extra: Using where
********************* 2. row ***********************
id: 1
select_type: SIMPLE
table: artist
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
...
mysql> EXPLAIN SELECT STRAIGHT_JOIN
album.name,artist.name,album.first_released

-> FROM artist INNER JOIN album USING (artist_id)
-> WHERE album.name = 'Greatest Hits'\G
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table: artist
type: index
possible_keys: PRIMARY
key: name
key_len: 257
ref: NULL
rows: 586756
Extra: Using index
********************* 2. row ***********************
id: 1
select_type: SIMPLE
table: album
type: ref
possible_keys: artist_id,name,name_release
key: artist_id
...

在第一个查询中可以看出，优化器选择先在album表上执行连接操作。在第二个有STRAIGHT_JOIN提示的查询中，优化器会强制按照表所指定的顺序对name字段做连接。尽管这个查询在两个表上都使用了索引，但第二个查询需要处理的行数比第一个查询多很多，因此本例中它的效率也更低。

2. 索引提示

除了STRAIGHT_JOIN查询提示以外，所有索引提示都会被连接语句中的表所使用。可以为每张表定义一个索引的USE、IGNORE或者FORCE列表。也可以选择限制索引在查询中JOIN、ORDER BY或者GROUP BY部分的使用。在查询的每个表后面都可以添加下面的语法：
USE {INDEX|KEY}
[{FOR {JOIN|ORDER BY|GROUP BY}} ([index_list])
| IGNORE {INDEX|KEY}
[{FOR {JOIN|ORDER BY|GROUP BY}} (index_list) |
FORCE {INDEX|KEY}
[{FOR {JOIN|ORDER BY|GROUP BY}} (index_list)

mysql> EXPLAIN SELECT artist_id, name, country_id
-> FROM artist WHERE founded = 1980 AND type='Band'\G
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table: artist
type: ref
possible _keys: founded,founded_2,type,type_2
key: founded
key_len: 2
ref: const
rows: 1216
Extra: Using where
1 row in set (0.01 sec)

在这个查询中优化器有多个索引可供选择，但它最终选择了founded索引。

下面的示例会提示优化器使用某个特定的索引：

mysql> EXPLAIN SELECT artist_id, name, country_id
-> FROM artist USE INDEX (type)
-> WHERE founded = 1980 AND type='Band'\G
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table: artist
type: refpossible_
keys: type
key: type
key_len: 1
ref: const
rows: 186720
Extra: Using where

可以看到在这个查询中使用了指定的索引。

同样也可以要求优化器忽略某个索引：
mysql> EXPLAIN SELECT artist_id, name, country_id
-> FROM artist IGNORE INDEX (founded)
-> WHERE founded = 1980 AND type='Band'\G
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table: artist
type: ref
possible_keys: founded_2,type,type_2
key: founded_2
key_len: 2
ref: const
rows: 1216
Extra: Using where

可以提供多个索引名或者多个索引提示：
mysql> EXPLAIN SELECT artist_id, name, country_id
-> FROM artist IGNORE INDEX (founded,founded_2)
-> USE INDEX (type_2)
-> WHERE founded = 1980 AND type='Band'\G
********************* 1. row ***********************
id: 1
select _type: SIMPLE
table: artist
type: ref
possible_keys: type_2
key: type_2
key_len: 1
ref: const
rows: 177016
Extra: Using where

想了解更多信息可以访问http://dev.mysql.com/doc/refman/ 5.5/en/index-hints.html。

使用MySQL提示对更改全部的执行路径不会产生影响，因此你可以指定多个提示。使用USE INDEX提示会让MySQL从指定的索引中选择一个。FORCE INDEX会对基于开销的优化器产生影响，让优化器更倾向于索引扫描而不是全表扫描。

提示

在SQL语句中添加提示是有很大风险的。尽管这可能会对查询有帮助，然而数据量随着时间的推移而变化会改变查询的有效性。添加或者改变表上的索引并不会影响到一个在特定索引中指定的硬编码SQL语句，所以查询提示应该是你最后考虑的方案。

4.4.8 复杂查询

本章的示例并不包含十个表的连接操作或者更复杂的查询。但本章以及第5章和第8章介绍的规则同样可以依次应用到单表中更复杂的查询上去。对于更复杂的查询我们使用同样的分析工具，所要做的就是把SQL语句拆开，分别对每个组成部分进行测试和验证以了解和验证最有可能的优化方法，然后逐步增加查询的复杂度直到满足要求为止。

4.5 添加索引造成的影响

4.5.1 DML影响

4.5 添加索引造成的影响

尽管本章给出了很多示例说明添加索引可以优化SQL语句的性能，但是添加索引同时也会带来不小的开销。

4.5.1 DML影响

在表上添加索引会影响写操作的性能。这一点可以很明显地从本章使用的artist表中看出。查看目前此表的定义可以看到此表上有很多索引：

mysql> SHOW CREATE TABLE album\G

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

Table: album

Create Table: CREATE TABLE `album` (

`album_id` int(10) unsigned NOT NULL,

`artist_id` int(10) unsigned NOT NULL,

`album_type_id` int(10) unsigned NOT NULL,

`name` varchar(255) NOT NULL,

`first_released` year(4) NOT NULL,

`country_id` smallint(5) unsigned DEFAULT NULL,

PRIMARY KEY (`album_id`),

KEY `artist_id` (`artist_id`),

KEY `country_id` (`country_id`),

KEY `album_type_id` (`album_type_id`),

KEY `m1` (`country_id`,`album_type_id`),

KEY `m2` (`album_type_id`,`country_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

通过运行一个简单的基准测试，我们可以测试当前album表在包含较少索引的原始状态的数据插入速率：

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 LIKE album;

INSERT INTO t1 SELECT * FROM album;

DROP TABLE t1;

CREATE TABLE t1 LIKE album;

-- NOTE: Due to indexes created during this chapter,

this may fail.

-- Adjust dropped indexes appropriately

ALTER TABLE t1 DROP INDEX first_released, DROP INDEX

album_type_id,

DROP INDEX name, DROP INDEX country_id, DROP INDEX m1,

DROP INDEX m2;

INSERT INTO t1 SELECT * FROM album;

DROP TABLE t1;

下面是查询返回结果的时间对比：

# Insert with indexes

Query OK, 553875 rows affected (24.77 sec)

# Insert without indexes

Query OK, 553875 rows affected (7.14 sec)

在包含更多索引的表中插入数据比在原始表中插入慢了4倍。当然这只是一次粗略的测试，还有很多其他因素可以导致查询缓慢。然而这已经足够说明在表上添加索引对写操作性能有直接影响。

1. 重复索引

在各种索引优化的技术中最简单的就是删除重复的索引。虽然找到重复的索引很容易，但是还有其他情况发生，例如一个索引与主码或者某些其他索引的子集相匹配。任何包含在其他索引的最左边部分中的索引都属于重复索引，且不会被使用。请看下面的示例：

CREATE TABLE `album` (

...

PRIMARY KEY (`album_id`),

KEY `artist_id` (`artist_id`),

KEY `country_id` (`country_id`),

KEY `m1` (`album_type_id`,`country_id`),

KEY `m2` (`country_id`,`album_type_id`)

...

可以看出country_id实际上是重复索引，因为已存在m2索引。

Maatkit mk-duplicate-key-checker是一个用来找出重复索引的开源工具。也可以对模式表进行桌面人工验证。

2. 索引的使用

MySQL机制的一个缺点是不能确定索引的使用。只有分析完所有SQL语句才能知道哪些索引没有被使用。找出使用到的和没有使用到的索引是很重要的。索引会影响写操作的性能，并且会占用磁盘空间从而影响到你的备份和恢复策略。有些低效的索引还会占用很大的内存资源。

2008年Google发布了SHOW INDEX_STATISTICS命令，这个命令使你可以通过一种更精确的方式获取索引使用情况的信息。很多MySQL发行版都包含了这一特性，但官方MySQL产品还不支持。

想了解更多信息请访问http://code.google.com/p/google-mysql- tools/wiki/UserTableMonitoring。

不管用什么工具来检查是否有没有用到的索引，你都需要分析索引中定义的列的有效性，同时也需要找到索引中无效的部分，这是非常重要的。

4.5.2 DDL影响

随着表大小的不断增长，对性能的影响也不断加大。例如，在主表上添加索引平均需要20～30秒。
mysql> ALTER TABLE album ADD INDEX m1
(album_type_id,country_id);
Query OK, 553875 rows affected (21.05 sec)

在以往版本中，ALTER语句的开销是阻塞其他语句，就像创建一个新版本的表那样。在这期间可以SELECT数据，但根据标准的升级法则，任何DML操作都会导致所有语句被阻塞。当表的大小有1G或者100G，这个阻塞时间可能会非常长。但比较近期的版本在包括MySQL产品方面和创新的解决方案方面都有了很多改进。

添加索引带来的影响并不总是一样，也会有些例外情况。InnoDB提供了快速创建索引的特性，从MySQL 5.1版本开始就可以在InnoDB的插件中使用了，并且在MySQL 5.5或更高版本中已经成为默认设置了。更多信息可参考http://dev.mysql.com/doc/ innodb/1.1/en/innodb-create-index.html。

其他搜索引擎也可以以不同方式来实现执行锁定的快速索引的创建，Tokutek就是其中的一个。更多信息请参考http://tokutek. com/2011/03/hot-column-addition-and-deletion-part-i-performance。

对磁盘空间的影响也是一个重要的考虑因素，尤其是当你在InnoDB中使用默认的公共表空间配置的时候。MySQL会为你的表创建一份副本。如果表的大小有200GB，那么在执行ALTER TABLE时你需要至少200GB额外的磁盘空间。使用InnoDB时，在执行期间这些额外的磁盘空间会被添加到公共表空间中。这部分磁盘空间在命令完成后不会被文件系统回收，而是当InnoDB需要额外磁盘空间时在内部被重复利用。尽管你可以调整策略让每个表用单独的表空间，但对于写操作密集的系统，这也是有影响的。

技巧

有一些技巧可以让阻塞操作减少到最低限度。你可以选择使用一个高可用性的容错度高的主表复制技术来支持在线变更表结构。比如近期Shlomi Noach介绍的oak-online-alter-table工具。更多信息可参见http://code.openark.org/blog/mysql/online-alter-table- now-available-in-openark-kit。Facebook也发布了自己的在线模式变更(OSC)工具，也能够以类似的方式在线运行ALTER操作。更多信息可参见http://www.facebook.com/notes/mysql-at-facebook/online- schema-change-for-mysql/430801045932。

4.6 MySQL的限制和不足

与其他关系型数据库产品相比，MySQL在使用和管理索引方面也有一些限制和不足。

4.6.1 基于开销的优化器

MySQL用基于开销的优化器来调整可能的查询树以创建最优的SQL执行路径。MySQL通过生成的统计信息来辅助优化器的能力是很有限的。MySQL支持数量有限的索引提示用于帮助优化器选择一个合适的路径。

4.6.2 指定QEP

MySQL不支持为给定查询指定QEP。在MySQL中，没有办法为一个数据一直随时间变化的查询定义一个QEP，这也影响了对QEP的选择。也导致了需要为每个查询的执行计划确定QEP。

4.6.3 索引的统计信息

MySQL支持有限的索引统计信息，这些统计信息因存储引擎不同而不同。使用MyISAM存储引擎的话，ANALYZE TABLE命令会为数据库表生成统计信息。目前还没有办法指定采样率。当一个表第一次被打开，然后被某个方法修改了一定比例的行的时候，InnoDB存储引擎会在一个数据页中执行随机采样以生成给定表的统计信息。

目前正在开发的MySQL 5.6版中包含了存储InnoDB统计信息的能力。

4.6.4 基于函数的索引

目前MySQL不支持基于函数的索引。而且，在已有的索引中使用函数会导致性能下降。MySQL支持部分列的索引，实际上就是索引左边的子串。我们将在第5章详细讨论这个问题。

你也不能指定一个索引的相反序列，因为事实上所有数据都是升序排列的。当需要对数据排序时，如果有DESC关键字，MySQL会反向遍历一个已有的索引。

4.6.5 一个表上的多个索引

正如本章介绍过的，默认情况下MySQL会对一个表只使用一个索引，但是有五种例外情况。在设计表、索引以及SQL语句之前认识到这个限制是很有好处的。未来版本的MySQL优化器的改进也会有助于弥补这些限制和不足。

第五章创建更好的MYSQL索引

本章将介绍这两个其他的索引技术：

●创建覆盖索引

●创建局部列的索引

5.1 更好的索引

通过使用索引，查询的执行时间可以从秒的数量级减少到毫秒数量级，这样的性能改进能够为你的应用程序的性能带来飞跃。合理的调整你的索引对优化来说是非常重要的，尤其是对于高吞吐量的应用程序。即使对执行时间的改进仅仅是数毫秒，但对于一个每秒执行1000次的查询来说这也是非常有意义的性能提升。例如，把一个原本需要20毫秒执行的每秒运行1 000次的查询的执行之间缩短4毫秒，这对于优化SQL语句来说是至关重要的。

我们将使用第4章介绍的方法创建多列索引，并在这一基础上创建更好的覆盖索引。

如果我们想查询所有在1969年出道的艺人的名字，可以运行下面的查询：

mysql> SELECT artist_id, name, founded

-> FROM artist

-> WHERE founded=1969;

我们的示例数据库比较小，这个表只有大约500 000行数据，然而我们还是可以借这个示例说明改进索引的影响。

在没有索引的情况下，这个查询耗时190毫秒。从查询执行计划中我们可以看出执行了一次全表扫描(使用第2章介绍的方法判断)。建议添加一个索引加以改进。请看下面的示例：

mysql> ALTER TABLE artist ADD INDEX (founded);

mysql> EXPLAIN SELECT

...

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

id: 1

select_type: SIMPLE

table: artist

type: ref

possible_keys: founded

key: founded

key_len: 2

ref: const

rows: 1035

Extra: Using where

在WHERE条件用到的founded列上添加索引之后，查询耗时减少到了5.9毫秒。这样简单的一个改动就可以让查询执行速度比原来提高了97%。然而，还可以创建一个使这条查询执行起来更快的索引：

mysql> ALTER TABLE artist

-> DROP INDEX founded,

-> ADD INDEX founded_name (founded,name);

mysql> EXPLAIN SELECT

...

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

id: 1 select_

type: SIMPLE

table: artist

type: ref

possible_keys: founded_name

key: founded_name

key_len: 2

ref: const

rows: 3696

Extra: Using where; Using index

使用多列索引之后，查询执行只需要1.2毫秒了。这比刚才的查询快了4倍，查询执行时间比第一次优化时又减少了80%，从总体来看我们节省了99%的查询执行时间。

尽管我们是通过多列索引来获得这样的性能提升的，但改善查询的真正因素并不是因为额外增加的列限制了访问的行数。使用第4章介绍的分析技术，我们可以看到这个多列索引只占用了2字节。可能你会认为这个多列索引中额外的列是无效的，但要注意在Extra这一列中显示了Using index。

当QEP在Extra列中显示Using index时，这并不意味着在访问底层表数据时使用到了索引，这表示只有这个索引才是满足查询所有要求的。这种索引可以为大型查询或者频繁执行的查询带来显著的性能提升，它被称为覆盖索引。

覆盖索引得名于它满足了查询中给定表用到的所有的列。想要创建一个覆盖索引，这个索引必须包含指定表上包括WHERE语句、ORDER BY语句、GROUP BY语句(如果有的话)以及SELECT语句中的所有列。

看了覆盖索引的介绍之后，你可能会好奇为什么artist_id这一列没有在索引中？对那些选择跳过第3章中介绍的索引的理论知识的读者来说，你们可能需要了解InnoDB的非主码索引的一个重要的特性。在InnoDB中，主码的值会被附加在非主码索引的每个对应记录后面，因此没有必要在非主码索引中指定主码。这一重要特性意味着InnoDB引擎中所有非主码索引都隐含主码列了。并且对于那些从MyISAM存储引擎转换过来的表，通常会在它们InnoDB表索引中将主码添加为最后一个元素。

技巧

有很多理由可以说服用户不要在SQL查询中使用SELECT *。上面提到的就是其中一个原因，说明如果在select语句中只包含那些真正需要的列，就能够通过创建合适的索引来获得更好的SQL优化。

然而我们的索引仅能使特殊定义查询性能大幅提升。如果我们想要通过添加某一类特定的艺术家来进一步限制查询，则结果如下：

mysql> EXPLAIN SELECT artist_id, name, founded

-> FROM artist

-> WHERE founded=1969

-> AND type='Person'\G

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

id: 1 select_

type: SIMPLE

table: artist

type: ref

possible_keys: founded_name

key: founded_name

key_len: 2

ref: const

rows: 3696

Extra: Using where

从结果可以看出我们使用了同样的索引，但却没有享受到覆盖索引带来的益处了。执行这个查询耗时5.4毫秒。我们可以根据新加的列来调整索引如下所示：

mysql> ALTER TABLE artist

-> DROP INDEX founded_name,

-> ADD INDEX founded_type_name(founded,type,name);

mysql> EXPLAIN SELECT

...

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

id: 1 select_

type: SIMPLE

table: artist

type: ref

possible_keys: founded_type_name

key: founded_type_name

key_len: 3

ref: const,const

rows: 1860

Extra: Using where; Using index

以上使用了修改之后的索引。我们还可以通过key_len的值为3来确定type列现在也是优化器限制的一部分，并且可以看出founded_type_name是覆盖索引。执行查询现在耗时1.3毫秒。

警告

创建这些索引只是用来描述确认覆盖索引的过程，但在生产环境中它们可能并不是理想的索引。由于数据集大小有限，我们在这些例子中使用了一个长字符列。随着数据容量的增加，尤其是超过内存和磁盘最大容量的时候，为一个大型列创建索引可能会对系统整体性能有影响。覆盖索引对于那些使用了很多较小长度的主码和外键约束的大型规范化模式来说是理想的优化方式。

5.1.1 覆盖索引

如果我们想查询所有在1969年出道的艺人的名字，可以运行下面的查询：

mysql> SELECT artist_id, name, founded

-> FROM artist

-> WHERE founded=1969;

我们的示例数据库比较小，这个表只有大约500 000行数据，然而我们还是可以借这个示例说明改进索引的影响。

mysql> ALTER TABLE artist ADD INDEX (founded);

mysql> EXPLAIN SELECT

...

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

id: 1

select_type: SIMPLE

table: artist

type: ref

possible_keys: founded

key: founded

key_len: 2

ref: const

rows: 1035

Extra: Using where

mysql> ALTER TABLE artist

-> DROP INDEX founded,

-> ADD INDEX founded_name (founded,name);

mysql> EXPLAIN SELECT

...

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

id: 1 select_

type: SIMPLE

table: artist

type: ref

possible_keys: founded_name

key: founded_name

key_len: 2

ref: const

rows: 3696

Extra: Using where; Using index

技巧

然而我们的索引仅能使特殊定义查询性能大幅提升。如果我们想要通过添加某一类特定的艺术家来进一步限制查询，则结果如下：

mysql> EXPLAIN SELECT artist_id, name, founded

-> FROM artist

-> WHERE founded=1969

-> AND type='Person'\G

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

id: 1 select_

type: SIMPLE

table: artist

type: ref

possible_keys: founded_name

key: founded_name

key_len: 2

ref: const

rows: 3696

Extra: Using where

mysql> ALTER TABLE artist

-> DROP INDEX founded_name,

-> ADD INDEX founded_type_name(founded,type,name);

mysql> EXPLAIN SELECT

...

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

id: 1 select_

type: SIMPLE

table: artist

type: ref

possible_keys: founded_type_name

key: founded_type_name

key_len: 3

ref: const,const

rows: 1860

Extra: Using where; Using index

警告

5.1.2 存储引擎的含义

第3章中着重强调了对于InnoDB的非主码索引来说，索引使用了主码的实际值，而不是指向底层数据行的指针。MyISAM使用了一种B-树索引的不同的实现方式，从下面的示例可以看出不同之处：

mysql> ALTER TABLE artist ENGINE=MyISAM;

mysql> EXPLAIN SELECT

...

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

id: 1

select_type: SIMPLE

table: artist

type: ref

possible_keys: founded_type_name

key: founded_type_name

key len: 3

ref: const,const

rows: 1511

Extra: Using where

这个查询并没有使用之前QEP所给出的索引的全部。MyISAM的平均查询时间大约为3.3毫秒，比优化过的InnoDB语句慢，但是比没有使用覆盖索引的查询快。为了确定这个重要的底层架构的不同点，我们修改这个索引来满足MyISAM引擎的全部要求：

mysql> ALTER TABLE artist

-> DROP INDEX founded_type_name,

-> ADD INDEX founded_myisam

(founded,type,name,artist_id);

mysql> EXPLAIN SELECT ...

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

id: 1 select_

type: SIMPLE

table: artist

type: ref

possible_keys: founded_myisam

key: founded_myisam

key_len: 3

ref: const,const

rows: 520

Extra: Using where; Using index

注意

本书不会讨论架构方面的考虑因素以及选择不同存储引擎间的优缺点。Effective MySQL网站：http://EffectiveMySQL.com提供了关于最佳数据架构的很有帮助的信息，它强调了基准测试在优化SQL语句时是一项重要的任务。

在以后的测试中，我们还是会重置表使用的存储引擎：

mysql> ALTER TABLE artist DROP INDEX founded_myisam,

ENGINE=InnoDB;

5.1.3 局部索引

尽管索引可以用来限制需要查询的行数，但如果MySQL需要获取大量行中的更多列的数据，那么创建具有更小行宽度的小型索引则会更加高效。就像在创建覆盖索引的示例里面看到的那样，使用一个索引来做更多的工作可以显著地改善SQL查询的性能。当数据大小超过物理内存资源时，你在选择使用索引的时候就要考虑到物理资源，而不是仅仅考虑查询执行计划。

INFORMATION_SCHEMA查询会检查表数据和索引空间的大小，请看下面的示例：

$ cat tablesize.sql

SET @schema = IFNULL(@schema,DATABASE());

SELECT @schema AS table_schema, CURDATE() AS today;

SELECT table_name,

engine,row_format AS format, table_rows,

avg_row_length AS avg_row,

round((data_length+index_length)/1024/1024

,2) AS total_mb,

round((data_length)/1024/1024,2) AS data_mb,

round((index_length)/1024/1024,2) AS

index_mb

FROM INFORMATION_SCHEMA.TABLES

WHERE table_schema=@schema

AND table_name = @table

首先从album表中删除已有的索引：

mysql> ALTER TABLE album DROP INDEX artist_id;

mysql> SHOW CREATE TABLE album\G

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

Table: album

Create Table: CREATE TABLE `album` (

`album_id` int(10) unsigned NOT NULL,

`artist_id` int(10) unsigned NOT NULL,

`album_type_id` int(10) unsigned NOT NULL,

`name` varchar(255) NOT NULL,

`first_released` year(4) NOT NULL,

`country_id` smallint(5) unsigned DEFAULT NULL,

PRIMARY KEY (`album_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

album表应该只有一个定义好的主码索引。我们可以通过查询定义好的INFORMATION_SCHEMA.TABLES查询来确定表和索引空间的大小：

mysql> SET @table='album';

mysql> SOURCE tablesize.sql

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

table_name: album

engine: InnoDB

format: Compact

table_rows: 541244

avg_row: 65

total_mb: 33.56

data_mb: 33.56

index_mb: 0.00

可以看出这个表的索引不占用空间，因为InnoDB的主码索引是聚集索引，并且与底层数据相结合。如果这个表使用的是MyISAM存储引擎，我们可以看到以下信息重点强调了数据和索引占用的空间：

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

table name: album

engine: MyISAM

format: Dynamic

table rows: 553875

avg row: 44

total mb: 29.09

data mb: 23.66

index_mb: 5.43

现在要在album表的name列上添加一个索引：

mysql> ALTER TABLE album ADD INDEX (name);

mysql> SOURCE tablesize.sql

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

table_name: album

engine: InnoDB

format: Compact

table_rows: 537820

avg_row: 65

total_mb: 64.17

data_mb: 33.56

index_mb: 30.61

在name列上新的索引使用了大约30MB磁盘空间。现在让我们为name列创建一个更紧凑的索引：

mysql> ALTER TABLE album

-> DROP INDEX name,

-> ADD INDEX (name(20));

mysql> SOURCE tablesize.sql

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

table_name: album

engine: InnoDB

format: Compact

table_rows: 552306 a

vg_row: 63

total_mb: 57.14

data_mb: 33.56

index_mb: 23.58

这个更加紧凑的索引只占用了23MB，较之前节省了20%的空间。尽管这看起来很不起眼，但我们的示例表只包含500 000行数据。如果这个表包含500 000 000行数据的话，那么节约的空间将会是6GB。

这里主要考虑的是如何减小索引占用的空间。一个更小的索引意味着更少的磁盘I/O开销，而这又意味着能更快地访问到需要访问的行，尤其是当磁盘上的索引和数据列远大于可用的系统内存时。这样获得的性能改进将会超过一个非唯一的并且拥有低基数的索引带来的影响。

局部索引是否适用取决于数据是如何访问的。之前介绍覆盖索引时，你可以看到记录一个短小版本的name列不会对执行过的SQL语句有任何好处。最大的益处只有当你在被索引的列上添加限制条件时才能体现出来。请看下面的示例：

mysql> ALTER TABLE artist

-> DROP INDEX name,

-> ADD INDEX name_part (name(20));

mysql> EXPLAIN SELECT artist_id,name,founded

-> FROM artist

-> WHERE name LIKE 'Queen%'\G

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

id: 1 select_

type: SIMPLE

table: artist

type: range

possible_keys: name_

part_key: name_

part_key_len: 22

ref: NULL

rows: 92

Extra: Using where

在这个示例中，在索引中记录全名并没有带来额外的益处。而所提供的局部列索引满足了WHERE条件。如何选择合适的长度取决于数据的分布以及访问路径。目前没有准确的方法计算索引的恰当长度。因此对给定范围的列长度内的唯一值数目的比较是必不可少的。

5.2 本章小结

在索引中正确的定义列(包括定义列的顺序和位置)能够改变索引的实际使用效果。好的索引能够为一个执行缓慢的查询带来巨大的性能提升。索引也可能使原来执行很快的查询的执行时间减少若干毫秒。在高并发系统中，将1 000 000条查询减少几毫秒将会显著改善性能，并且获得更大的容量和扩展性。为SQL查询创建最优索引可以认为是一项艺术。

写出好的SQL语句对查询优化和最大化当前以及以后的索引效果也是非常重要的。我们会在第8章进一步讨论如何改进SQL查询。

Effective MySQL网站上可以下载到一个演示文档，里面提供很多详细的关于如何找到并创建更好的索引的示例。想了解更多信息可以访问：http://effectivemysql.com/presentation/improving- performance-with-better-indexes/。

可以从下列地址下载本章所有SQL语句：http://effectivemysql.com/ book/optimizing-sql-statements。

第六章 MYSQL配置选项

6.1 内存相关的系统变量

这些MYSQL系统变量可以在MYSQL配置文件中定义并在启动时加载，

很多MYSQL变量是动态的，也就说可以通过MYSQL客户端的SET命令更改

1. 全局内存缓冲区

2. 全局/会话内存缓冲区

3. 会话缓冲区

6.2 有关基础工具的变量

6.3 其他优化变量

第7章 SQL的生命周期

7.1 截取SQL语句

7.1.1 全面查询日志

Mysql全面日志查询允许你截取所有在这个数据库实例上运行的SQL语句

可以通过MYSQL配置来启用：

[mysqld]

General_log = 1;

General_log_file = /path/to/file

Log_output = FILE

也可以通过动态SQL语句启动全面日志：

SET GLOBAL General_log = 1;

SET GLOBAL General_log_file = /path/to/file

……

SELECT * FROM mysql.general_log/G

7.1.2 慢日志查询

MYSQL慢日志查询提供那些在指定数据库实例上执行时间超过一定限制的那些查询：

可以通过MYSQL配置来启用：

[mysqld]

Slow_query_log=1;

Slow_query_log_file=/path/to/file

Long_query_time=0.2;

Log_output=FILE;

通过SLEEP()函数来方便测试慢日志的有效性:

SELECT SLEEP(0.1);

SELECT SLEEP(0.2);

SELECT SLEEP(0.3);

Select SLEEP(0.1)没有记录到慢查询日志中，因为他小于

Long_query_time的阈值

7.1.3 二进制日志

Mysql的二进制日志覆盖了所有非SELECT语句，其中包括DML和DDL语句

可以通过MYSQL配置来启用：

[mysqld]

Log-bin = /path/to/file

Mysqlbinlog提供了MYSQL二进制信息中纯文本版本

7.1.4 进程列表

这个进程列表可以找到当前运行的执行时间很长的或者常用的查询

Show full processlist

7.1．5 引擎状态

存储引擎特有的SHOW ENGINE[引擎] STATUS命令可以提供一些额外的SQL信息

7.1.6 MYSQL连接器

7．1.7 应用程序代码

7.1.8 INFORMATION_SCHEMA

7.1.9 PERFORMANCE_SCHEMA

7.1.10 SQL语句统计插件

7.1.11 MYSQL PROXY

7.1.12 TCP/IP

7.2 识别有问题的语句

从上一步截取出来的SQL语句中找到有问题的语句，也是有优先级顺序的

在标准安装下，只有mysqldumpslow命令提供了任意级别的集合查找

7.2.1 慢查询日志分析

Mk-query-digest –type slowlog /var/log/mysql/slow.log、

7.2.2 tcp/ip分析

一条DELETE 2次调用占用了超过2000条的执行时间的10%

7.3 确认语句执行

在确认SQL语句时，一定要尽量重现这些工作环境

7.3.1 环境

在确认SQL语句时，应该在SELELCT语句添加一个

SQL_NO_CACHE提示保证没有MYSQL缓存

7.3.2 时间统计

SHOW PROFLIES命令可以提供SQL语句微妙级别力度

7.4 语句分析

确认SQL语句后，可以用命令工具分析SQL语句第二章

7.5 语句优化

通过索引优化SQL语句如第4章第5章

或改善表结构

7.6 结果验证

第八章性能优化之秘籍

8.1 索引管理优化

8.1.1 整合DDL语句

ALTER语句执行时，需要为表创建一个新的副本，

在改变大型表时，这个操作将消耗大量的时间和磁盘存储空间

把多条ALTER 语句整合呈一条SQL语句是一种简单的优化改进

如添加索引，修改索引，添加列

ALTER TABLE test ADD INDEX(username);

ALTER TABLE test DROP INDEX(name),ADD INDEX name(lastname

,firstname);

ALTER TABLE test ADD COLUMN last_visit DATE NULL;

整合为一条SQL优化

ALTER TABLE test

ADD INDEX (username),

DROP INDEX(name),

ADD INDEX name(last_name,first_name),

ADD COLUM last_visit DATE NULL;

8.1.2 去除重复索引

、

8.1.3 删除不用的索引

8.1.4 监控无效的索引

当定义多列索引时，一定要注意所指定的每一列是否真的有效

8.2 索引列的改进

还可以通过其他模式优化的方法改进数据库性能，

包括使用特定的数据类型以及或者列类型

8.2.1 数据类型

1．BIGINT和INT

BIGINT变成INT ，从8个字节变成4个字节，节省空间

2. DATETIME和TIMESTAMP

DATETIME占8个字节，TIMESTAMP占4个字节

3.ENUM

这个数据类型适合存储静态代码值

4. null 和 not null

尽量用not null

8.2.2 列的类型

1.IP地址

用int 而不用varchar可以节省2/3 的字节

8.3 其他SQL优化

最有效的SQL优化方法是删除不需要执行的SQL语句

8.3.1 减少SQL语句

1.删除内容重复的SQL语句

2.删除重复执行的SQL语句

使用分析功能看出这类SQL执行的总开销：

SET PROFILING = 1;

Select、、。。。。

SHOW PROFILES；

3.删除不必要的SQL语句

4.SQL缓存结果

5.MYSQL缓存

使用分析功能展示常规查询和随后被缓存的查询执行时间和复杂性

SET GLOBAL query_cache_size = 1024*1024*16;

SET GLOBAL query_cache_type =1

SET PROFILING = 1;

SELECT NAME FROM FIRMS WHERE ID = 727;

SHOW PROFILES;

6.使用应用程序缓存

如Memcached，redis

8.3.2 简化SQL语句

1改进列

2.改进连接操作

3.重写子查询

使用视图VIEW

posted on 2016-02-24 21:17 Sharpest 阅读(587) 评论(0) 编辑收藏举报

MySQL优化1

1.1识别性能问题

1.1.1 寻找运行缓慢的SQL语句

1.1.2 确认低效查询

1 运行SQL语句并记录执行时间

1.2 优化查询

第二章 基本的分析命令

2.1 EXPLAIN命令：

2.1.2 EXPLAIN PARTITIONS命令

2.1.3 EXPLAIN EXTENDED命令

2.2 show create table 命令

2.3 show indexes 命令

2.4 show table status 命令

2.5 show status 命令

2.6 show variables命令

2.7 INFORMATION_SCHEMA

第三章 深入理解MYSQL的索引

3.1 示例表

3.2 MYSQL索引的用法

3.2.1 数据的完整性

3.2.2 优化数据访问

3.2.3 表连接

3.2.4 结果排序

3.2.5 聚合操作

3.3 关于存储引擎

第 四 章：创建MYSQL索引

4.2 已有的索引

4.3 单列索引

4.3.1 创建单列索引的语法

4.3.2 利用索引限制查询读取的行数

4.3.3 使用索引连接表

4.3.4 理解索引的基数

4.3.5 利用索引进行模式匹配

4.3.6 选择唯一的行

4.3.7 结果排序

4.4 多列索引

4.4.1 确定使用何种索引

4．4.2 多列索引的语法

4.4.3 创建更好的索引

4.4.4 多个列上的索引

4.4.5 合并WHERE和ORDER BY语句

4.4.6 MySQL优化器的特性

4.4.7 查询提示

4.4.8 复杂查询

4.5 添加索引造成的影响

4.5.1 DML影响

4.5.2 DDL影响

4.6 MySQL的限制和不足

4.6 MySQL的限制和不足

4.6.1 基于开销的优化器

4.6.2 指定QEP

4.6.3 索引的统计信息

4.6.4 基于函数的索引

4.6.5 一个表上的多个索引

第 五 章 创建更好的MYSQL索引

5.1 更好的索引

5.1.1 覆盖索引

5.1.2 存储引擎的含义

5.1.3 局部索引

5.2 本章小结

第六章 MYSQL配置选项

6.1 内存相关的系统变量

6.2 有关基础工具的变量

6.3 其他优化变量

第7章 SQL的生命周期

7.1 截取SQL语句

7.1.1 全面查询日志

7.1.2 慢日志查询

7.1.3 二进制日志

7.1.4 进程列表

7.1．5 引擎状态

7.2 识别有问题的语句

7.2.1 慢查询日志分析

7.2.2 tcp/ip分析

7.3 确认语句执行

7.3.1 环境

7.3.2 时间统计

7.4 语句分析

7.5 语句优化

7.6 结果验证

第二章基本的分析命令

第三章深入理解MYSQL的索引

第四章：创建MYSQL索引

第五章创建更好的MYSQL索引

第八章性能优化之秘籍