MySQL查询优化

加速查询的方法：

1、为数据表创建索引，并利用索引，explain 分析查询语句是否真正最大限度用到索引；；

2、编写查询来影响服务器的调度机制，从而使来自多个客户端程序的查询能够更好地协作；

3、修改服务器的操作参数以提高它的工作效率；

4、分析底层硬件，解决物理限制，从而提升性能。

1  使用索引

用来加快查询的技术有很多，其中最重要的是索引。通常，能够造成查询速度最大差异的是索引的正确使用。

1.1 索引的优点

索引提高搜索速率的原因：

• 可以指导数据行再什么位置结束

• 可以直接找到第一个匹配项（例如，二进制搜索就比扫描快得多）。

不同的存储引擎索引实现的方式不同：

• MyISAM数据表的数据再数据文件里，所有的索引在同一个索引文件里。索引文件里的每一个索引都是由分类的关键记录数组组成的，这些数组用于快速访问数据文件。

• InnoDB数据表的数据和索引都在一个表空间里，索引数值分类放置。可以同过配置让InnoDB为每个数据表分别创建自己的表空间，即便如此，一个给定的数据表的数据和索引也是保存在同一个表空间里。

MySQL使用索引的方式：

• 在查询操作中把与where自己所给出的条件相匹配的数据行尽快找出来。

• 在关联查出中把与其它数据表里的数据行相匹配的数据行尽快找出来。

• 对于min()和max()函数的查询，如果数据列带索引，值会被迅速找到。

• 使用索引完成order by和group by操作。

• 有时，MySQL可以通过使用索引来避免为一个查询整体读取数据行。加入你从MyISAM数据表只读取一个有索引的数据列的值，MySQL自己从索引中取值。

1.2 索引的缺点

• 降低了在带索引的数据列里插入，删除和更新的速度。

• 索引要占据磁盘空间。

1.3 挑选索引

• 尽量为用来搜索（where字句，联结），排序（order by），分组（group by）的数据列编制索引，不要为作为输出显示（select）的数据列编制索引。

• 综合考虑各数据列的维势度。数据列的“维度”等于它所容纳的非重复值的个数。一般来说，当查询优化程序确定出某一个数值在数据表的数据行中出现的频率超过30%时，查询优化程序通常会跳过索引，而进行全表扫描。

• 对短小的值进行索引。应尽量选用比较“小”的数据类型。比如能用mediumint，就不用bigint。比较短小的值可以在以下方面提高索引的速度：

  • 短小的值可以让比较操作更快地完成，加快索引查找速度。

  • 短小的值可以让索引的“体积”更小，减少磁盘I/O活动。

  • 短小的键意味着键缓存里的索引块可以容纳更多的键值，让MySQL可以在内存里同时容纳更多的键，这可以加大在不需要从磁盘里读取更多索引块的前提下在内存里找到键值的概率。

  • 对InnoDB存储引擎而言，因为它使用的是聚集索引，所以让主键尽量短小将更有好处。所谓“聚集索引”是指把数据行和主键值集中保存在一起的情况。其它的索引都是二级索引——它们保存这主键值和二级索引值。现在二级索引里找到一个主键值，再通过它找到相应的数据行。这意味着主键值在每一个二级索引里都会重复出现，如果主键值比较长的话，就会导致每一个二级索引将需要占据更多的存储空间。

• 为字符串的前缀编索引。

• 充分利用复合索引最左边的前缀。

• 适可而止，不要建立过多的索引。

• 让索引的类型与你打算进行的比较操作的类型保持匹配。InnoDB总是使用“B树”索引，MyISAM使用“B树”索引和在遇到空间数据类型时会改用“R树”索引。MEMORY存储引擎默认使用散列索引，也可选用B树所以。挑选索引类型的时候，一定要考虑你打算再被索引的数据列上进行什么类型的比较操作。

  • 对于散列索引，会有一个散列函数来依次处理每一个数据列值。结果该索引将被存入改索引并用来进行查询。散列值在使用“=”或“<=>”操作符进行的精确匹配比较操作里速度极快。但它用来查找一个范围的比较操作里表现不佳。

  • “B树”索引在使用<、<=、=、>=、>、<>、!=和between操作符进行的精确比较操作或范围比较操作里都很有效率。非通配符开头的字符串，“B树”索引还可以用于like查询。

                如果使用的MEMORY数据表只用于精确查找，散列索引会是一个很好的选择。如果是范围查询，应该为它创建一个“B树”索引：在索引定义里加上USING BTREE字样。MEMORY数据表可以同时哟拥有散列索引和“B树”索引，它们可以用于同一个数据列。

 

• 利用“慢查询”日志找出性能低劣的查询。

2  MySQL的查询优化程序。

2.1 查询优化器的工作原理

查询优化程序有好几个目标，主要的目的是是要有可能就要使用索引，并且要使用条件最严格的索引来尽可能多，尽可能快地排除那些不符合索引条件的数据行。之所以用排除法是因为从范围搜索中排除数据行的速度越快，找到那些与搜索条件匹配的数据行就越快。条件最严格，可以看这个例子：

SELECT c3

FROM mytable

WHERE c1 = 'some value' AND c2 = 'some other value';

假设检c1时有900个匹配的数据行，检验c2时有300个匹配的数据行，同时检验两个数据列时有30个匹配的数据行。如果首先检验c1，就要从900个数据行中找到其中与c2匹配的30个，也就是配出870个数据行，如果首先检验c2,则排除270个数据行，由此，查询优化程序会首先检验c2，这样做工作量最小。

小面给出的指导意见可以帮助优化器充分利用索引。

• 对数据表进行分析。

        这将生成关于索引值分布情况的统计数据，它可以帮助优化器对索引的使用效果作出更准确的评估。再默认的情况下，当你把一个有索引的数据列里的值与一个常数相比较的时候，优化器会假设相关索引里的减值是均匀分布的。在判断是否应该把某个索引用于常数比较的时候，优化器还会对该索引进行一次快速检查以估算需要用到多少个索引项。对于InnoDB和MyISAM数据表，可以主动地使用analyse table table_name语句让服务器对键值进行一次分析。

        如果某个数据表在填充好数据之后不再发生变化，只需在加载后对它做一次分析就够了。如果某个数据表经常国内更新，就应该时不时地根据数据更新的频繁程度来对它进行分析。

• 使用explain语句来验证优化器操作。

        explain语句可以告诉你某给定查询有没有使用索引。

• 向优化器提供提示或在必要时屏蔽之。

        在廉洁操作中，你可以再数据表列表中的某个数据表名字的后面利用force index，use index，ignore index限定词告诉服务器你想使用那些索引。

• 利用straght_join强制优化器按特定的顺序使用数据表。

        将迫使数据表按照他们在from字句出现的先后顺序相互关联。应该在列出数据表时把将被选取的数据行个数最好的数据表放在第一个。如果对数据没有把握，就把数据行个数最多的那个数据表放在第一个。这里的原则是，安排数据表的顺序是为了让限制性最强的选取操作最先执行。候选数据行的个数缩减得越快、越早，查询的的性能越高。

        straght_join可能不起作用，用explain验证一下

• 尽量使用数据类型相同的数据列进行比较。

        比如，int不同于bigint。

• 使带索引的数据列在表达式中单独出现。

        如果再函数值或者算术表达式中使用了一个数据列，MySQL将不能使用素银，因为它必须要对每一个数据行计算出表达式的值。一般可以重新写出查询来使带索引的数据列单独出现。

where mycol * 2 < 4  -- 不适用索引

where mycol < 4 / 2 -- 使用索引

• 不要再like模式的开始位置使用通配符

        “%”放开头索引会失效

• 利用优化器的长处

        许多场合，优化器对联结的优化效果要比对子查询的优化效果更好一些。如果子查询比较慢，可以尝试改为联结方式。

• 试验各种查询的变化格式，而且要多次运行它们。

        减弱磁盘缓存带来的误差。

• 避免过多地使用MySQL的自动类型转换功能。

        例如：

select * from mytbl where str_col = 4;

因为在str_col数据列里可能会有许多不同的字符串值在转换为一个数值后等于4（例如'4'、'4.0'、'4th'等），要想把它们都找出来，就要依次读取，转换，在进行比较。所以索引将无法使用。

2.2 用explain句检查优化器操作

explain语句提供的信息可以帮助我们了解优化器为处理各种语句而生成的执行计划。

3 为提高查询效率而挑选数据类型

这里提供了一些挑选数据类型的建议，可以帮助查询命令运行得更快。

• 尽量使用数字操作，少使用字符串操作。

        数值运算通常要比字符串运算快得多。以比较操作为例，数值之间的比较只用一个操作就可以完成，而字符串之间的比较一般需要进行多次字节与字节或字符与字符的比较才能完成，而且字符串越长，比较的次数就越多。

        如果字符串的格式是有限的，可以选用enum或者set类型，这些类型再MySQL内部是以数值形式表示的。

• 尽量选用“小”类型。

        “小”类型比“大”类型处理速度更快。尤其是字符串。比如mediumint小于bigint，char(40)小于char(255),blob使用2个字段记录长度，而longblob使用4个字节记录长度。

• 如果你能选择数据行的存储格式，就应该尽量选用最使用与你的存储引擎的格式。

        对于MyISAM数据表，应该尽量选用固定长度的数据列而不是可变长度的数据列。（时间换空间）。

        MEMORY数据表目前使用固定长度的格式来存储数据行，索引选用char还是varchar都无关紧要，都被当做char类型来对待。

        InnoDB数据表的数据行内部存储格式对固定长度的数据列和可变长度的数据列不加区分（每个数据行有一个标头，存放着指向有关数据列的指针），所以没啥区别。但是可变长度更省空间，减少磁盘I/O操作次数，所以会好一些。

• 尽量把数据列声明为not null。

• 考虑使用enum数据列。

• 利用procedure analyse()语句

        可以使用procedure analyse()语句分析数据表，看它会对数据列的声明提出哪些建议：

select * from table_name procedure analyse();

select * form table_name procedure analyse(16, 256); --如果数据列的不同取值在16个以上或者长度超过了256个字节，就不提出使用enum类型的建议。

 

 

• 对容易产生碎片的数据表进行整理。

    数据表，尤其是包含可变长度数据列（VARCHAR, BLOB或TEXT）的数据表，往往会因为大量的更新而产生碎片。定期使用optimize table table_name来清理碎片。也可以使用mysqldump工具来清理：

mysqldump db_name table_name > dump.sql;

mysql db_name < dump.sql;

• 把数据压缩到blob或text数据列里。

• 使用人造索引。

        可以先根据数据表里的其它数据列来计算出一个散列值并把它另外保存到一个数据列里，然后通过搜索散列值去检索你想要的行。这种技巧只适用于精确查找。这种办法对blob或text非常使用。

• 尽量避免对很大的blob或text值进行检索（除非万不得已）。

• 把blob或text数据列剥离到剥离单独的数据表里。

        技能减少原始数据的碎片，有能是原始数据表上的select *不会把大的blob或text值通过网络传输给你。

4 有效加载数据到数据库

    加载数据的几个原值：

• 批量加载的效率比单数据行加载的效率高。

        键缓存在每一次输入的记录加载后都不需要刷新，他可以在批量记录结束时再刷新。越是减少键缓存的刷新次数，数据加载也就越快。（对索引的修改是在键缓存区里进行的，然后才会在适当时机写到硬盘上。）

• 加载有索引的数据表比加载无索引的数据表要慢一些。

        如果有一些索引，不但数据行要添加到数据表里，每一个索引也必须得到修改来反映出所添加的行。

• 较短的sql语句的数据加载比较长的快。

        因为它们在服务器中包含较少的语法分析，同时也因为它们能够更快地从客户程序经过网络送到服务器。

为此，我们得出几个结论：

• 使用load data（各种格式）语句比使用insert语句效率高。

        因为她批量加载数据行，服务器只需要对一个语句（而不是几个语句）进行语法分析和解释。索引只有在所有的数据行都处理完毕后才需要刷新，而不是每处理一行都刷新。

• 使用load data 比使用load data local语句效率高。

        load data 比load  data local省去了网络传输的时间。

• 如果只能使用insert语句，那就使用批量insert格式： insert into table_name values(...), (...), ...;这样就减少了索引刷新的次数。

• 如果使用多条insert语句，对事务性存储引擎，在一个事务内发出insert语句，对于非事务性存储引擎，先锁定表的写锁定，insert完之后再解锁，这样索引在所有的语句执行完之后才刷新。

• 对于MyISAM数据表，可以使用delay_key_write选项。使用这个选项数据行扔像平时一样立刻写入数据文件，但是键魂村将只在必要时才刷新一次。要想在服务器全局范围内获得上述delay_key_write效果，必须在启动mysql程序时给出--delay-key-write=ALL选项。这样，数据表的索引块写操作将被推迟到发生以下事件时才进行：必须在刷新某个索引库以便为其他索引值腾出空间的时候，执行完flush tables语句的时候，数据表被关闭的时候。开启了这个选项如果服务器意外关机将有导致索引值的丢失，但是MyISAM数据表的索引可以根据它的数据行得到修复，所以不是致命问题。为了确保修复，应该在启动服务器时给出--myisam-recover=FORCE选项。这个选项将强制服务器在打开MyISAM数据表时对它们进行检查并自动地进行必要的修复。

• 使用压缩的客户/服务器协议来减少通过网络的数据量。

        对大部分的MySQL客户程序，可以使用--compress 命令行选项来指定这一点。通常情况下，这种做法只适用于速度较低的网络上，因为压缩过程要展一部分处理器时间。

• 定义默认值

        这样可以减短语句，减少网络传输，也只需介绍的语法分析和值替换。

• 对应MyISAM数据表，如果大量插入数据，先加载数据，再创建索引，要快一些。

5 调度和锁定问题

5.1 执行优先级

MySQL默认的调度策略如下：

• 写入比读取有更高的优先权。

• 写操作按照先来后到的顺序进行。

• 读一个数据表的读操作可以同时进行。

MySQL提供了一些语句修饰符来改变它的调度策略。

• low_priority

        用在insert，update，delete，replace，load data语句中。

        假如一个数据表正在被读取时来了一个写入操作，写入者就会等待（因为一旦读取开始，就不会被干扰，所以要等待读取者完成读取）。如果写入请求是一个low_priority请求，那么等待时后面来的读取操作都会排在写操作前面去了。

        开启默认的low_priority，启动服务器是给出--low-priority-updats选项。插入时，insert high_priority就可以临时取消默认选项结果

• high_priority

        用在select ，insert中。

        优先级：带有higth_priority的select>写操作>普通select

• delayed

        用在insert和replace中。

        当delayed请求到达数据表时，服务器将数据行排队并迅速地返回客户程序的状态，从而客户程序可以做其它事。服务器在没有读操作时插入数据，如果中途有读请求，将会挂起来等读操作完成后，接着插入。

low_priority和high_priority限定符只对支持数据表锁定功能的存储引擎（MyISAM、MERGE、MEMORY）有效果。delayed限定符用在MyISAM、MEMORY、ARCHIVE和BLACKHOLE存储引擎上都可以使用。

5.2 使用并发插入

MyISAM存储引擎对读取者锁定写入者的通用原值有一个特例。这种情况发生在MyISAM数据表在数据文件里没有空洞的时候（例如当删除或更新数据行时），在这种情况下，insert语句只能再数据行的末尾而不是中间位置添加数据行。这是，这是，一个客户程序读的同时另一个客户可以写。因为这些操作可以在检索没有被锁定时进行，因为被称为“并发插入”。使用并发插入时，需要注意一下的问题。

• 不要再insert语句中使用low_priority。

• 那些需要显示锁定数据表但不想允许并发插入的读者应该是用lock tables ... read local，而不是lock tables ... read。关键字local会使你得到一个允许并发插入的锁，因为它只用于在数据表中已经存在的数据行，并不锁定被添加到数据表末尾的新数据行。

• 因该给load data操作加上concurrent限定符，让给定数据表上的select语句在该数据表正在加载数据的同时扔可以执行。

• 内部有空洞的MyISAM数据表不能用于并发插入。不过，可以用optimize table语句对有空洞的数据表进行碎片整理，这将消除那些空洞。

5.3 锁定级别与竞争性

MyISAM、MERGE、MEMORY存储引擎处理数据表级别的锁定。

InnoDB可以处理行级别的锁定。

航锁定比表锁定有更好的并发性。表锁定没有死锁问题，行锁定有死锁问题。

6 系统管理员所完成的优化

完成管理优化时，主要原则如下：

• 从内存中访问数据比从磁盘上访问数据快。

• 在内存中尽可能长地保存数据可以减少磁盘的活动量。

• 保留索引的信息要比表里数据行的内容更加重要。

应用这些原则最常见的做法就是增加服务器缓存的容量。

• 为了让文件打开操作的次数最小化，服务器在打开数据表文件之后会尽量让它们继续保持在打开状态。具体地说，它会把已经打开的信息保存在数据表缓存里。table_open_catch控制着这个缓存的大小。如果缓存被填满，就会关闭一下最近没有使用的数据表，为新表腾出空间。要想了解数据表缓存的情况，请查看Opend_tables状态变量。

 

Opened_tables指示出一个数据表曾经被打开多少次后就没有被打开过。如果这个树保持稳定或者缓慢增长，就说明设置可能是正确的。如果这个数增加很快，就意味着缓存已满。如果你有文件描述符，增加数据表缓存大小会减小打开数据表的次数。

• MyISAM键缓存区用来保持MyISAM数据表有关索引的操作的索引块，有key_buffer_size控制。默认是8MB。如果内存足够，建议加大。将看到基于索引的检索和对索引的

创建和修改操作都会有明显的性能改善。

 

• InnoDB的数据和索引值的缓存，由innodb_buffer_pool_size控制。InnoDB还维护着一个日志缓冲区，由innodb_log_buffer_size变量控制。

 

 

• 另外一个缓存是查询缓存。

还有一些可以提高服务器效率的方法：

• 禁用不需要的存储引擎。被禁用的存储引擎不会分配内存。

• 让权限表尽可能简单。

• 如果是在源点构建MySQL服务器，要用静态库而不是共享库来配置它。

6.1 使用MyISAM键缓存

由于键缓存容量有限，且默认由所有MyISAM数据表共享。如果没能在缓存里找到想要的值并且缓存已经满了，就将导致竞争。

MySQL提供了一个解决方案：建立多个键缓存。我们可以为某个数据表分配一个键缓存并把该数据表的索引提前加载到缓存。

以sampdb库中的member数据表创建一个键缓存为例：

（1）创建一个新的键缓存，让它大到足以容纳来自member数据表的索引：

 

（2）吧member数据表指定这个键缓存：

 

（3）把member数据表的索引提前加载到它的键缓存里去：

 

如果你想把其它数据表加载到同一个缓存或者为其它的数据表创建其它的缓存，套用以上步骤即可。

上述步骤和语句创建的专用键缓存在服务器启动之后就消失了。可以把语句放在一个文件里并用--init-file服务器选项来加载。

6.2 使用查询缓存

MySQL服务器可以使用查询缓存来加快重复执行的select语句的过程。性能改善经常十分明显。查询缓存有以下特点：

• 一个给定的select第一次执行时，服务器记下了它查询的文本和返回的结果。

• 服务器下一次看到这个查询时就不再执行它，而是直从查询缓存汇总将查询结果取出并返回。

• 查询缓存以服务器接受的哪些查询字符串的文字文本为基础。如果查询文本是完全一样的，查询就被看成是相同的。

• 如果某个查询命令返回的结果不确定，这个查询就不会被缓存。比如说，一个使用了now()函数的查询会随着时间的变化而返回不同的结果，所以它不会缓存。

• 当一个数据表被更新时，所有与之相关的缓存着的查询就会全部失效，并被删除。

查询缓存是默认设置，如果不想使用这个缓存，可以在运行configure脚本时使用--without-query-cache选项来构建不带查询缓存的服务器。

查询是否支持查询缓存，查看一下have_query_cache系统变量：

 

 

对于支持查询缓存的服务器来说，缓存操作会受到以下3个系统变量值的影响。

• query_cache_type决定查询缓存的操作模式。下面显示了可能的模式值。

• query_cache_size决定了为查询缓存分配的内存的大小，以字节为单位。

• query_cache_limit设置能够缓存的最大结果集的大小，比这个值大的查询结果将不能被缓存。

例如，为了是查询缓存能用个，为它分配16MB的内存，设置如下：

 

即使query_cache_type为0，query_cache_size指定的内存也会被分配到。为了避免浪费内存，要将大小设置为0，除非要启用缓存。如果query_cache_type非0，query_cache_size为0是也会禁用缓存。

每个客户程序都以服务器默认的缓存方式所指示的状态开始查询缓存行为。下面语句改变默认缓存方式：

set query_cache_type = val;(0,1,2，也可分别对应off, on, demand)。

在select后面加一个修饰符，客户程序也可以控制各个查询的缓存。如果缓存模式是on或demond，那么select sql_cache将使可缓存查询的结果被缓存。select sql_no_cache使结果不被缓存。

对于那些从不断变化的表中检索信息的查询来说，建议禁用缓存。

6.3 硬件优化

通过硬件改善性能的几点建议：

• 在机器里安装更多的内存。

        这会增加服务器的缓存和缓冲区的容量，从而允许数据再内存中能够保持更长的时间，较少需要从磁盘上获取信息。

• 如果你有足够的RAM可以让所有的数据交换的发生在一个内存文件系统里，可以重新配置你的系统来删除所有的磁盘数据交换设备。

• 添加更快的磁盘来改善I/O等待时间。

• 在物理设备之间分散磁盘读写活动，提高并行度。

• 使用多处理器硬件。