高效MYSQL性能优化--小结

 

总结自<高效mysql性能优化>:

第一章:5分钟DBA

一.鉴定性能问题:

1) 查找慢SQL语句:

 >show full processlist

 可以得到当前mysql所有连接中正在运行的操作,此指令将会输出当前运行的所有sql列表以及其耗时信息:

 id:42

 User:***

 Host:****

 Command:QUERY

 Time:3

 Info:select * from table

 

 2) 查询计划(QEP):

  可以通过Explain语句查看SELECT语句执行的情况.

 提示:Alter操作,将会阻塞当前table中所有的其他操作,直到alter操作结束.所以避免在生产环境中对大表进行多次alter,或者避免在业务

 繁忙期,使用alter.

 

 3) >show create table tableName;

 此指令能够直观的查看当前表结构,以及使用索引的情况.

 >show table status like 'tableName';

 此指令能够告诉我们当前表的整体信息,其中包括表的引擎类型,表中行数,数据总尺寸大小,索引总尺寸大小.能够帮助我们分析性能问题是否因为

 数据尺寸过大带来的.

 结果格式:

 Name:tableName

 Engine:Innodb

 Rows:134500

 Data_length:123131123

 Index_length:...

 CheckSum:null.

 

 

第二章:必备的分析指令

 

1) EXPLAN:

SELECT查询计划工具,能够较为详细的告知当前sql执行所涉及到的索引/过滤条件等.

2) EXPLAN EXTENDED:

和EXPLAIN类似,不过额外提供了filtered属性信息,filtered表示当前语句所能过滤掉的数据的百分比,实际参与连接的行数为rows * filtered /100,注意fitered

的信息为不含%的整数部分.

此外,EXTENDED还能对一些select计划生成warning信息,如果当前sql是无索引或者时低效时,将会提示一个warning,我们可以通过>show warning信息查看具体原因.

waring中将会展示出,当前sql最终被"转换/优化"之后的SQL,我们可以从中看到"索引失效"等相关信息.这些是我们使用EXPLAIN所不能得到的.

比如,将两个表进行join,但是on条件中的字段属性在两个表中不一致,将会导致索引失效.例如对name进行关联,但是两个表的name编码不同等.

3) SHOW INDEXES FROM tableName:

查询指定表的索引列表信息,此指令将会输出当前表所关联的所有索引信息,包括"主键".

例如:

**************

Table:tableName

Key_Name:PRIMARY

Seq_in_index:1 //索引顺序?

Column_name: id //索引针对"列"

Index_type:BTREE

Cardinality:2880 //基准,表示当前表中不同索引值的个数,"区分度".

......

**************

4) SHOW TABLE STATUS like 'tableName'

5) SHOW [GLOBAL | SESSION] STATUS like '%read%':

此指令用于查看当前server的有关状态,DBA使用.

6) SHOW [GLOBAL | SESSION] VARIABLES like '%buffer_size%':

查看当前系统的变量值,比如查看mysam表的缓存大小,innodb的查询缓存等.

 

 

第三章:理解索引

1) 索引的作用:维护数据约束,优化数据查询,提升表关联,结果排序,聚集数据

2) 主键特征:

->每张表只能有一个主键

->主键不能包含NULL

->主键提供了直接手段获取指定的行

->如果当前字段为AUTO_INCREAMENT,那么它必须是主键或者主键的一部分(联合主键)

3) 唯一索引:

->一个表中可以有多个唯一索引

->索引值可以为NULL,不过每个NULL值之于它自己相等.(NULL != NULL)

使用索引,可以避免全表扫描,类似于随即IO操作和顺序IO操作一样.

如果sql中存在表关联,那么尽可能为关联字段建立索引.MYSQL中,对索引值以排序的方式存储.这就有利于对数据输出结果排序.

4) BREE:

二叉树,所有数据或者索引节点将会以树状关系分布,对于任何一个节点,其左叶节点总比右叶节点小.

Node {

key;

Node left;

Node right;

}

在数据节点较多时,如果查询一个数据,有可能导致较深的递归比较.这种结构,存在"树深"问题,不能作为"数据库索引结构".

B-TREE:

是BTREE的"改进版",用于解决"树深"问题,并提供可靠的数据查询策略.

Node {

keySelf;

key1;

key2;

...

keyN;

Node1;

Node2;

...

NodeN;

}

B-Tree的树深有限,一般为2层,除root节点外,分为Node page,和Leaf page两种结构,每个非叶子节点的节点为子节点,每个子节点维护者多个叶子节点,

具体每个Node维护多少个叶子节点,在不同的设计中有所不同.Node page中维护者子节点序列,正序排列,子节点所代表的值为最大叶子节点值.

B-tree中,需要保证,每个子节点的值都比其后续子节点的最小叶子节点值小,而且比前继子节点的最大叶子节点值大...针对任何值查询,可以使用二分法

找到其所属于的子节点,如果此值刚好为子节点值,则直接返回;否则从其leaf page中最左叶节点逐个遍历,直到获得值或者为空返回.

B-tree结构,避免了树深问题,但是如果设计不良,导致leaf page中叶子个数过多,同样产生类似树深的问题(顺序遍历).每个叶子节点到root节点的距离都一样.

 

                                   ROOT

                                       |

      NODE1                 NODE2             NODE3

           |                            |                        |

LEAF1->LEAF2    LEAF->LEAF     LEAF->LEAF

 

B+Tree,在B-tree的基础上做了一些微小的改造,在每个节点的最右叶子节点增加了指向下个子节点的指针.由此这种改动,对于"range scan"有很大的帮助.

 

在MYSQL中,对这两种数据结构使用,还有些特殊的区别.

 

5) 索引结构:

在mysql中,B+tree具有B-tree所有的特性,他们有个很大的区别就是,B+tree所引用的底层数据是按照被索引列排序的（聚簇索引，Primary key）,而B-tree不是.

hash表数据结构,key为"索引",value为实际数据的指针位置.对hash表的访问,时间开支为线性的.

fullText索引目前只能被myisam表支持,mysql5.6之后innodb表也将支持fullText.

 

一个很现实的事情是,B-tree结构在myisam和innodb中的实现也有些区别.在myisam引擎中,将使用B-tree结构存储主键/唯一索引和二级索引.对于二级索引,myisam中存储索引值和主键数据的指针.这和Innodb有些区别.

 在innodb引擎中,使用B+tree来存储主键,由于在每个leaf page间,有后继指针关系,允许我们能够更加简单的使用range scan操作.

在innodb引擎中,二级索引使用了B-tree结构,不过和myisam有些区别,在innodb中,二级索引存储的是主键的实际值,在myisam中则存储为实际数据的指针.因此在innodb中,如果主键的值长很大,例如40个字节,将可能导致二级索引文件尺寸也非常大;此外因为二级索引持有了主键的实际值,所以在表关联操作或者"covering indexes"时,可以提升性能.

 

对于memory table,使用hash索引时可以理解的,但是如果memory表尺寸很大的时候,同时sql查询中,有基于key的"模糊"查询,那么此时性能就会大幅度下降,因为这样的操作会导致

hash表的全扫描.:select * from memory_table where key like '%ok%'.. 针对这种情况,其实我们还可以对memory表,建立B-tree索引,对于此sql性能将得到大幅度提升.

alter table mem_table add index USING BTREE(key).此时针对key将会在内存中构建逻辑索引结构.在数据量较小的情况下,hash索引和btree索引性能差距不大,但是在数据量较大的情况下,

针对key='**'操作,hash性能几乎稳定,但是btree却有所下降.

 

第四章:建立索引

 

1) 当一个查询,有多个索引可被选择时(possible_keys),mysql将会尝试使用一个最高效的索引.如何评估一个索引对当前查询更加高效,那么索引信息中cardinality(计数)起到重要作用.

通过show indexes from table指令来查看.一个索引中,如果cardinality值越高,则表示其持有的"唯一值"越多,这也就以为可以在更少的数据遍历中找到需要的rows;不过这些值只是基于"统计"

估测出来的.最理想的情况,就是表中的行数和cardinality值很接近,即表达此表的某字段中几乎没有重复的索引值.

 

2) 当在一个索引列上使用表达式模糊查询时,会导致索引失效.不过有一种特殊情况不会,使用左前缀匹配,"key%"这种方式,仍然会使用索引的优势..但是对于"%key""%key%"都将会使索引失效.

3) 如果能够确定,一列中的数据都不会重复,那么可以考虑建立唯一索引,唯一索引将给我们打来"数据唯一性约束""数据快速查找(如果找到,则发挥,类似于limit 1)"等优点.

4) 如果sql语句中含有order by,那么建议为此后的列和where字句所含字段建立索引.

5) index_merge_intersection = on变量开启,意味着可以使用"索引联合",即一个sql查询,将有可能使用多个索引的查询结果然后"merge[/即union]"或者"intersect(交集)".

例如:select * from table where name = 'zhangsan' or age =25,如果在name和age上分别建立了索引,并且开启了上述变量,那么最终此查询将会在查询计划大概为:

possible_keys:name,age

key:name,age

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

 

如果语句为:select * from table where name = 'zhangsan' and age =25,那么其查询计划为:

possible_keys:name,age

key:name,age

Extra:Using intersect(name,age);Using where

"索引联合"性能不一定比单索引更加高效,当然单索引也不一定比"索引联合"更高效,如果实际环境中存在如上述例子的情况,还是需要多多测试.

默认"索引联合"变量是关闭的,即任何sql查询,最终只会使用一个索引.

6) "建立索引"的其他影响:

索引,会影响表中数据写入的性能,索引个数越多,那么导致insert/update等DML语句性能下降.原理很简单,任何导致数据修改的操作,都可能触发一个或者多个索引文件的调整,甚至重建,这

会同时发生在内存和索引文件中.

 

索引优化中,我们还需要移除"重复索引",mysql允许你对一个列建立重复索引且不提示异常.但是这对我们的查询是没有太大帮助的,例如:

create table..

key `name`,

key `user_type`,

key `m1` (`name`,`user_type`),

key `m2` (`user_type`,`name`);

其中m1和m2两个索引就是重复的.

 

索引多磁盘空间的影响,建立多个索引或者对字段建立不合适的索引,将会增减索引文件的尺寸,而且有可能最终索引文件比实际data文件还要大.需要申明的是,在innobd中,主键的值要尽量的小.

因为innodb中B-tree的索引将会把主键值直接添加到索引记录中,假如每个主键值有40个字节,那么当数据量很大时,索引文件也将膨胀而且既有可能远大于内存,很多时候,我们期望索引信息能够尽多

的被载入内存,尽少次数的索引page在内存中交换.

 

 

第五章: 建立高效索引

创建合适的索引,可以更好的改善性能.

 

1) Covering index(覆盖索引):

例如: add index (`user_name`,`user_type`),那么针对select id,user_name,user_type from user where user_name='xxx'语句将会使用"覆盖索引值".此查询将会使用索引,

因为建立索引时"user_name""user_type"的值已经在建立索引时作为索引值而持有,因此在select输出时不会导致对实际数据的磁盘检索,而是通过索引值直接可以获取,因此对于"uer_type"

的值是可以直接获得的,这就是"覆盖索引".同时在innodb中,任何select中包括主键时,也被covering.

注意"覆盖索引"仍然需要考虑"索引值最左原则",即只有组合索引的最左索引列参与查询才会使用到"覆盖索引";对于组合索引,参与查询的最左索引列(按照顺序顺序)越多,其索引效率越高.

比如组合索引("name","age","date"),如果查询语句中未包含"name"筛选条件,将会导致不会使用索引(全文检索),如果筛选条件为"name" + "date"将会导致只能使用"name"索引列值(通过explain可以看出index_len为name的值长,而date不参与索引值比较);如果筛选条件为"name" + "age"那么将会使用覆盖索引,且索引值为name + age.

2) partial index(局部索引):

例如:add index (name(20)),此指令将会对name字段的最左20个字符建立二级索引.因此对name的查询,有可能会使用此索引,而且在一定程度上,减少了索引文件的尺寸.

 

 

第六章:MYSQL配置选项

对于mysql server端将采取线程池的方式处理client的连接请求,可以确保server在多client情况下能够保持稳定可控.

1) innodb_buffer_pool_size:此变量用于定义innodb的数据和索引的缓冲池大小.此参数为全局内存buffer大小,使用与所有的innodb引擎表.

2) query_cache_size:定义select语句执行结果的缓存大小.此参数为全局内存buffer,用来缓存频繁查询的数据结果.不过在数据insert/update等write操作中,将会触发cache的结果和当前表

有关的所有数据失效,所以较大的query_cache_size在write操作频繁的环境中反而会影响性能.可以在读写比较大的环境中,设定合适的cache,可以提高性能.否则,建议关闭此参数.

3) key_buffer_size:针对myisam引擎,一个全局的内存buffer,它只缓存了myisam表索引信息,索引数据可以从磁盘中相应的文件中读取然后载入内存.如果内存不足以缓存不断更新的索引信息,将会

采用LRU策略,将不活跃的索引block交换出内存.key_cache_age_threshold/key_cache_block_size/key_cache_division_limit四个参数互相调整来决定内存中索引数据交换的方式.

缓存以block为单位维护(加载和移除),key_cache_block_size来决定其大小.如果查询是所涉及的索引不在内存,则触发到索引文件中读取响应的数据(一个block读入),并加入到cache的队列顶部;

如果insert/update/delete所涉及到的索引调整,则将内存中相应的block设置为dirty,并将新索引更新到文件;dirty状态的block在下一次被命中是重新载入或者被LRU移除.

key_cache_age_threshold是设计定block的"活跃性"基数的,由其单位时间命中的次数来决定(简单的这么认为吧),如果其"活跃性"达到阀值,会导致此block被已到"hot区",如果hot区中的block低于阀值,

会被移动到"warm区".阀值的计算有个公式,请参考其他资料.

4) join_buffer_size:线程参数,当sql中存在表关联且没有使用索引的情况下使用.这个参数被建议为保留默认值,否则此参数的设置有可能导致查询失败(过小),增大此参数值,对内存有很大影响,而且不能

有效的提升性能.

5) sort_buffer_size:线程参数,如果当前操作中有数据排序时使用.建议此参数为默认值.

6) max_allowed_packet:用来定义sql查询所允许返回的最大结果数,如果限定了此值,那么在遇到大结果集处理是,有可能是无法进行或者是低效.

 

 

第七章:SQL生命周期

 

1) 捕获慢查询sql是优化的第一步,可以通过如下几个手段获得:

-->普通查询日志(General query log):mysql的启动配置文件中可以通过

general_log = 1(//开启);

general_log_file = path/g_log.log

此日志记录下所有的被server指定的语句.通过select * from mysql.general_log可以简单的看出日志的记录.

此日志文件,不能提供每条sql所执行的时间,只是提供了SQL语句/执行的线程ID,我们可以用这些分析某些sql执行的次数和频率.

此日志功能,不建议在生产环境中开启.

2) 慢查询日志(slow query log):

通过在启动配置文件中配置:

slow_query_log = 1

slow_query_log_file = //

log_query_time = 2 (单位:微秒,千分之一毫秒)

log_output = FILE

对于所有执行时间超过log_query_time的查询,都会记录在慢查询日志中.此日志中展示出执行的sql/实际耗时/client信息等.

3) 二进制日志(binary log):

二进制日志,是mysql server必备的日志文件,对于所有的DDL以及非select类型的DML语句都将被记录.

//

log-bin=path/bin.log

此日志文件不能帮助我们分析select查询耗时问题,但是它提供了针对ALTER操作已经update/delete等语句造成的慢速问题.

4) processlist:

通过->show full processlist;质量可以帮助我们看到当前正在被执行的sql语句以及其处理状态,重要的是,可以告诉我们耗时情况.不过此指令也可以提供我们查看当前sql是否

处于"lock"状态(死锁问题).

 

顺便提示一下,mysql对于死锁有自己的检测和默认解决方案,在大部分情况下无需应用干预lock.针对mysql死锁的原因做如下简单解释:

mysql中锁的类型分为表锁(无死锁情况),行锁(最小粒度锁,极少几率死锁),叶锁(一定几率死锁).mysql中获取lock不是在实际data上,而是在索引上,

针对表(例如innodb)索引可以同时存在主键索引和二级索引.如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。

在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。

例如如下2个语句:

update table set name = ... where age > 20;

update table set name = ... where id = 145;

加入id = 145的数据,其age > 25,意味着这2个sql都会覆盖一个id = 145的数据,如果他们同时执行,而且第一个语句首先被接受的情况下,将会发生:

语句1首先对age > 20记录的二级索引上锁,然后获得记录的主键,再对主键索引加锁..

在语句1尚未对主键加锁时,语句2成功的获取主键索引值,此时它也希望获得name索引上的锁...此时死锁产生了.

//备注,此sql的前提是,name字段为二级索引,id为主键索引.

尽管mysql提供了死锁自动检测和解决方案,我们还是期望对索引值进行批量更新的sql,最好先转换成主键查询条件,例如:

select id from table where age > 20;

update table set name = ** where id in(....),通过程序达成对锁获取顺序的共识.

5) 工具采集:

这个可能不是生产环境中所允许的,不过我们可以使用一些开源的mysql server监控工具,来获得实时的慢查询语句信息.

 

 

第八章: 隐藏的性能

 

1) 组合你的DDL:

我们都知道DDL语句,将会对表加锁,且阻塞任何其他的数据查询.特别在生产环境中,如果数据量很大,那么DDL语句的执行将是很耗时的.

例如,我们向表添加多个索引:

ADD index ..

ADD index ..

如果我们将上述2个DDL,组合成一个:ADD INDEX..,ADD INDEX..那么实际的阻塞行为将只有一次,而且很大程度上减少了阻塞时间.Alter操作底层实际上是对整个表进行了一次"full copy";

尽管这个策略在不同的mysql版本中进行各种优化,但是alter仍然是耗时的以及无法避免的对表结构和数据的copy过程..组合DDL语句,就以为一次copy即可.

 

2) 移除重复索引

重复索引带来的2个直接影响就是:DML操作将会增加多余的工作量来维护索引和数据,再者增加没有必要的磁盘存储开支.

例如,对主键再次建立索引,组合索引的最左字段再次建立索引等,都属于重复索引.

index m1 (name),

index m2 (name,type)

事实上name字段已经存在m2索引的最左侧,没有必要对name列再次单独建立索引.

3) 移除无用索引

随着业务的发展,可能系统的sql语句已经调整,将导致某些索引已经不再会被使用到,因此可以移除这些无用的索引.

这个操作,可能需要整理出目前在用的所有sql语句,经过综合分析之后,才能决定某些索引是否可用.

4) 选择合适的索引列属性

-->bigInt vs int

bigint为8个字节,int为4个字节,对于"无/有符号"的情况下所支持的值域不同.

比如,对于自增的id,大家都习惯设置为bigint,其实想一想,一张表绝对不可能达到bigint极限,甚至int的极限都达不到,为何不声明为无符号int类型呢?可以大大减少存储空间.

 

-->Datetime vs timestamp

Datetime存储为8个字节,timestamp为4个字节.如果业务允许(timestamp存在时间界限,每个N年会被重新计算)可以考虑使用timestamp.

 

Enum:枚举类型是mysql中表达有限值域的最佳属性.比如:性别(男/女),比如,物品类型等等.枚举的特点是:提供了数据的值域约束功能,同时只需要一个字节即可表达最多255中值.

NULL VS NOT-NULL:除非你能确定此字段可以包含不确定值(NULL),那么你最好对此此字段设定为not null.这主要是从索引的角度考虑,如果一个字段为索引列,且大量存在NULL,

是不利于索引查找数据.

 

此外,在存在或者潜在存在表关联的字段,最好此字段的属性(类型,字符集,长度,NULL约束)都要保持一致,否则可能会导致在关联查询时索引失效.

5) 其他的优化:

->show profile for queury x;x表示上下文中指定的查询语句.show profile能够帮助我们查看一个sql被执行(以及被优化)的所有步骤和每个步骤的耗时情况.

比如:starting --> check permissions --> open table --> system lock -> create tmp table -->executing -->copy to tmp table-->sending data -->....

因为一条sql最终输出结果需要经过很多步骤,每条sql可能不尽相同.我们可以从每个步骤中获得其执行的时间,来了解究竟耗时的原因.

6) 表关联:

表关联操作,如果没有依赖索引,将可能造成麻烦.即使再有索引的情况下,表关联也是一个耗时/耗内存的操作.

左连接:对where字句的筛选结果中,参与关联的表,输出结果以左边为基准.对于右表缺失的列将以NULL补白.如果没有where字句,将全表数据输出.

右连接:原理同上

内连接:不以任何表为基准,在符合where字句的筛选结果中,只输出"on"关联条件的数据,不存在NULL补白问题.

7) 按需输出列:

根据实际需要输出列,输出多余的列,将会导致不必要的IO操作,以及对临时表内存的消耗,同时如果输出的列不被索引覆盖,也将会导致额外的磁盘IO检索.

 

第九章:EXPLAIN语法详解

1) EXPAIN tableName,此指令几乎等同于DESC tableName.

如果对update/delete语句使用EXPLAIN分析,最终将会转换成select方式.比如 EXPLAIN update table set.. where name = ..//等同于EXPLAIN select name from table where name = ..

 

如下介绍EXPLAIN对select语句的输出信息.

-->key: 表示当前select使用的索引

-->rows: sql为了得到指定结果,可能(预估)检索的行数,此值与表总行数的比值越低则表示索引的效果越好.

-->possible_keys:sql的执行,可被选择的索引列表,最终将会从此列表中评估出执行效率最高的一个索引,作为最终的结果执行.

-->key_len:索引值的长度,字节数,此值越小,表示索引所占磁盘/内存空间越小.也意味着索引值的比较更加高效.

-->select_type:当前sql的查询类型,比如:

simple(简单查询,无子查询,无符合查询语法等),

PRIMARY(在符合查询是,此表为主表),

DERIVED(派生,即此表不代表实际物理表,它可能是字句生成的临时表名),

UNION(关联类型)

-->Extra:表示sql的一些额外提示信息.信息中有可能出现如下几个字样:

Using where:使用where语句过滤了数据,且有效了使用索引.

Using temprary:表示使用了内存中临时表.比如在来自多个表的不同字段使用DISTINCT/ORDER BY/GROUP BY等,导致数据在内存中以临时表的方式多次计算.

Using filesort:在没有使用索引的情况下,使用了ORDER BY对结果进行排序.

Using index:在满足输出结果列的情况下,只使用了索引值,而没有进行实际磁盘检索.

-->type:表示sql在筛选数据时所采取的访问类型

const:此表最多只有一行匹配,唯一索引或者主键索引时发生.

System:const的一种特例,表示表有且只有一行匹配.主键索引时发生.

eq_ref:在出现表关联时,使用=符号作为2个关联表字段的比较,且使用了当前表的唯一索引或者主键;导致关联表从当前表中只读取到一条记录,类似于非关联操作中的const.

例如:explian select t1.name ,t2.name from t1,t2 where t1.id = t2.uid;如果此时t1.id为主键或者唯一索引,那么在执行计划中,很有可能t1条目的type为eq_ref.

ref:区别与eq_ref,即关联表时,使用=或者<=>符合比较,当前表没有使用到主键或者唯一索引,但是有效的使用了二级索引筛选出了有限结果集,对于另一个关联表而言,数据将依赖于此结果集.

此类型在表关联中,应该有较好性能.

ref_or_null:查询行中对索引列使用IS NULL语法,例如:select * from table where index_cloumn is null.

range:范围检索时,对索引列使用 >,< or,between等比较符使,导致索引进行范围检索.

index:使用到了索引,但是对索引树进行了全量扫描,性能仅高于all.

all:没有使用索引,对表实际数据进行了全表扫描.

 

性能由上往下逐渐变低,非关联查询,index级别是最低要求.对于关联查询,尽量保持为ref或者eq_ref.