您的当前位置：首页正文

MySQL7-性能优化

2023-11-11 来源：东饰资讯网

网上说使用强制索引可以改善查询速度，经过试验并不能。SELECT COUNT(*) FROM ta_info_cad_andr FORCE INDEX(ctime) WHERE ctime > @startTime AND ctime < @endTime;#15s 9、拆分大的delete/update语句：因为这两个操作会锁表，可能导致其他线程无法访问，甚至是MySQL服务器崩溃。 10、查询分区数据时，如果数据都在一个分区，可以显示的把分区语句加上。如下所示，语句1耗时7s，语句2耗时25s。SELECT SQL_NO_CACHE COUNT(DISTINCT deviceid) FROM ta_info_cad_andr_test2 PARTITION(p201610);SELECT SQL_NO_CACHE COUNT(DISTINCT deviceid) FROM ta_info_cad_andr_test2 WHERE ctime > ‘2016-10-01 00:00:00‘ AND ctime < ‘2016-11-01 00:00:00‘; 三、工具与技巧1、explain 2、从procedure analyse()获取建议：当表中有实际数据(最好多一些)时，这些建议才更可能有效；建议是针对数据类型的；建议只是建议，决定还得自己根据实际情况做。SELECT COUNT(DISTINCT deviceid) FROM ta_info_cad_ios WHERE ctime > ‘2016-11-01‘ PROCEDURE analyse();SELECT DISTINCT deviceid FROM ta_info_cad_ios WHERE ctime > ‘2016-11-01‘ PROCEDURE analyse();给出建议分别如下：可见这些建议也不是很靠谱。 3、慢查询日志 4、定期进行analyze table 与 optimize table：由于二者都会对表加锁，谨慎使用。 5、其他：Prepared Statements、无缓冲查询 6、为了防止服务器缓存对查询效果评估的影响，查询语句中可以加入SQL_NO_QUERY。SELECT SQL_NO_CACHE COUNT(DISTINCT deviceid) FROM ta_info_cad_andr_test2 WHERE ctime > ‘2016-10-01 00:00:00‘ AND ctime < ‘2016-11-01 00:00:00‘; PARTITION(p201610); 参考：http://www.cnblogs.com/daxian2012/articles/2767989.html*************************************************************************************************************************** 《文章-运维角度浅谈MySQL数据库优化》里面讲的很好如果问数据库运行速度的怎么样？我想最直观最直接的方式，是查看慢查询日志，可以使用mysqldumpslow、pt-query-digest或者直接查看。 IO、网络、CPU、内存都可能是制约数据库速度的瓶颈，其中IO是瓶颈的时候较多。

查询优化、索引优化、库表结构优化，应该齐头并进，一个不落。【这些都是在数据库使用层面；硬件配置、数据库资源使用配置方面也是优化方向】

QPS、TPS：questions per second与transactions per second***************************************************************************************************************************查看慢(查询)日志的相关状态/参数show variables like ‘slow%‘ slow_query_log：on/offslow_query_log_file：慢日志文件名slow_launch_time：注意，这个时间阈值是新建线程消耗时间的阈值，并不是慢查询时间阈值show variables like ‘%long_query_time%‘long_query_time：慢日志记录的时间阈值，单位秒；注意，这个时间阈值是查询过程消耗时间的阈值；默认是10s，一般2s居多，但是对于web应用，2s也太长，可以考虑1s。5.6的版本是可以将阈值精度设置到ms级别的。注意，如果日志中只有以下行，则没有慢查询【以下行是慢查询的表头，每次重启服务器会增加一次(已验证)】C:Program FilesMySQLMySQL Server 5.6inmysqld.exe, Version: 5.6.26-log (MySQL Community Server (GPL)). started with:TCP Port: 3306, Named Pipe: (null)Time Id Command Argument【慢速日志的确提供了很多有用的信息，但是不代表出现的查询一定一直都是慢的。如果同样的查询在慢速日志里出现了多次，那么它的确需要优化，但是如果只是出现了偶尔一两次，则有可能是其他客观原因造成的，比如某些锁，I/O磁盘物理性问题，网络问题等等】【查看慢日志主要有两种方式：1、直接打开文件查看2、使用mysqldumpslow，这是MySQL的一个脚本，后缀是.pl，需要安装pl才能调用】 ***************************************************************************************************************************当向MySQL发送一个请求的时候，MySQL到底做了什么？1、客户端发送一条查询给服务器2、服务器先检查查询缓存(Query Cache)，如果命中缓存，则立即返回存储在缓存中的结果；否则进入下一个阶段3、服务器生成执行计划，这个过程主要包括解析SQL、预处理和优化SQL执行计划4、MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询5、将结果返回给客户端 show (full) processlist：查看连接/线程的状态可以显示数据库服务器的域名、数据库名称、用户名等基本信息，以及以下几个最重要的信息time：从开始执行到现在执行了多少时间，单位是sinfo：执行信息，一般代表执行了什么命令command：当前状态sleep：线程正等待客户端发送新的请求query：线程正在执行查询或者正在将结果发送给客户端locked：在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，如InnoDB的行锁，并不会体现在线程状态中。analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。coping to tmp table [on disk]：线程正在执行查询操作，并且将结果集都复制到一个临时表中，这种状态一般要么是在做group by操作，要么是文件排序操作，或者是union操作。如果有on disk，则正在将一个内存临时表放到磁盘上。sorting result：线程正在对结果集进行排序sending data：表示多种情况：线程可能在多个状态之间传送数据(?)，或者在生成结果集，或者在向客户端返回数据 ***************************************************************************************************************************explain select的详解id：sql语句的执行顺序，不重要table：表名possible_keys：可能用到哪些索引key：用到了哪些索引key_len：使用的索引长度ref：显示使用哪个列或常数与key一起从表中选择行rows：执行查询的行数，是查看性能的主要指标extra：查询的详细信息，比较多而且可以出现不止一个，如using index：通过索引便可以找到，不需要扫描数据行 select_type：simple：简单查询，没有union和子查询primary：子查询的外层查询，或union的第一个查询union：union查询中第二个或后面查询dependent union：union查询中第二个或后面查询union result：union查询的结果…… type从最优到最差：前5个是比较优的方式system：仅有一行，是const的特例const：（1）primary key或unique key（2）全查询，即不能只是最左边（3）=，不能使<>（4）只能是一个表eq_ref：const的（4）不满足，即两个表primary key/unique key相等ref：const的（1）或（2）不能满足时，使用ref；因为无论是对于普通key，还是只检索最左边，都不能保证查询结果的唯一ref_or_null：可以搜索包含null值的行index_mergeunique_subqueryindex_subqueryrange：当有索引(B-tree可以，hash索引不行)时，使用<>between、in(in也要特别注意)等条件，range。如果没有索引，是all。index：扫描全表-索引表，如使用了索引中非最左边的数据。all：扫描全表-原表，如没有使用索引，或使用hash索引而条件不是=时(未验证)。需要注意的两点：（1）在查询时应该注意类型，如有一张表id为普通索引，类型为varchar，如果使用id=‘1000‘，则type为ref；如果使用id=1000，则type为all；切记切记！（2）对于查询较为频繁的列，能用primary/unique就不用普通索引；能设置为not null，就不要允许null(因为允许null对于索引的性能影响较大)。 ***************************************************************************************************************************长连接和短连接：什么是长连接？其实长连接是相对于通常的短连接而说的，也就是长时间保持客户端与服务端的连接状态。通常的短连接操作步骤是：连接-》数据传输-》关闭连接；而长连接通常就是：连接-》数据传输-》保持连接-》数据传输-》保持连接-》…………-》关闭连接；这就要求长连接在没有数据通信时，定时发送数据包，以维持连接状态，短连接在没有数据传输时直接关闭就行了什么时候用长连接，短连接？长连接主要用于在少数客户端与服务端的频繁通信，因为这时候如果用短连接频繁通信常会发生Socket出错，并且频繁创建Socket连接也是对资源的浪费。但是对于服务端来说，长连接也会耗费一定的资源，需要专门的线程（unix下可以用进程管理）来负责维护连接状态。总之，长连接和短连接的选择要视情况而定。如何设置长连接和短连接？不太清楚 Hibernate的连接池中的连接都是长连接。**************************************************************************************************************************《MySQL必知必会》对于改善性能的一些建议硬件方面1、注意对硬件的要求；一般来说，关键的生产DBMS应该运行在专用服务器上。2、MySQL是用一系列的默认设置预先设置的；过一段时间以后，可能需要调整内存分配、缓冲区大小等。使用show variables 和 show status 可以查看。 3、MySQL是多用户多线程的，经常执行多个任务；如果一个任务执行缓慢，会导致所有任务执行缓慢。如果遇到显著的性能不良，可以用show processlist显示所有活动进程，用kill命令终结某个进程。 4、总有不止一种方法编写同一条select语句，应该试验联结、并、子查询等，找出最佳方法。5、使用explain语句查看如何执行一条select语句。6、一般来说，存储过程比一条条执行MySQL语句快。7、应该总是使用正确的数据类型。8、绝不要检索比需求还要多的数据，慎用select *。9、有的操作(包括insert)支持delayed关键字，如果使用它，就把控制立即返回给调用程序，并且一旦有可能就实际执行该操作。10、在导入数据时，应该关闭自动提交。你可能还想删除索引(包括fulltext索引)，然后在导入完成后再重建它们。11、使用索引可以提升检索的性能。同时，会损害插入、删除和更新的性能，如果有一些表收集数据且不经常搜索，建议不使用索引。12、如果select语句中有一系列复杂的or条件，建议使用多条select语句和连接它们的union语句，性能会大大改善。13、like很慢，一般来说最好使用fulltext而不是like。 14、数据库是不断变化的实体；由于表的使用和内容的更改，理想的优化和配置也会改变。15、最重要的规则是，每条规则在某些条件下都会被打败。 **********************************************************************************************************************************实践经验1、联合主键的弊端有时候在一张表A中，两个字段c1和c2，可以唯一确定一条记录；原理上来说，可以使用c1和c2做A的联合主键，虽然比使用单独的主键减少冗余，但是可能会有下面的问题：如果表A在其他表中被引用，那么需要同时使用c1和c2作为外键，不但冗余大，操作麻烦，而且如果做索引，对效率的影响就更大了。

MySQL7-性能优化

标签：需要表头定时服务端 curd let ogr 大量 lan

小编还为您整理了以下内容，可能对您也有帮助：

怎样优化mysql数据库来提高mysql性能(mysql数据库的优化)

优化“mysql数据库”来提高“mysql性能”的方法有：

1、选取最适用的字段属性。

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

2、使用连接（JOIN）来代替子查询(Sub-Queries)。

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。

3、使用联合(UNION)来代替手动创建的临时表。

MySQL从4.0的版本开始支持UNION查询，它可以把需要使用临时表的两条或更多的SELECT查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。

4、事务。

要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。

5、锁定表。

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。

6、使用外键。

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

7、使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

8、优化的查询语句

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

MySQL数据库优化(七)

为了能最小化磁盘I/O MyISAM 存储引擎采用了很多数据库系统使用的一种策略它采用一种机制将最经常访问的表保存在内存区块中

　　对索引区块来说它维护着一个叫索引缓存(索引缓冲)的结构体这个结构体中放著许多那些最常使用的索引区块的缓冲区块　　对数据区块来说 MySQL没有使用特定的缓存它依靠操作系统的本地文件系统缓存

本章首先描述了 MyISAM 索引缓存的基本操作然后讨论在MySQL 中所做的改进它提高了索引缓存性能同时能更好地控制缓存操作

　　线程之间不再是串行地访问索引缓存多个线程可以并行地访问索引缓存　　可以设置多个索引缓存同时也能指定数据表索引到特定的缓存中

索引缓存机制对 ISAM 表同样适用不过这种有效性正在减弱自从MySQL 开始 MyISAM 表类型引进之后 ISAM 就不再建议使用了 MySQL 更是延续了这个趋势 ISAM 类型默认被禁用了

可以通过系统变量 key_buffer_size 来控制索引缓存区块的大小如果这个值大小为那么就不使用缓存当这个值小得于不足以分配区块缓冲的最小数量( )时也不会使用缓存

当索引缓存无法操作时索引文件就只通过操作系统提供的本地文件系统缓冲来访问(换言之表索引区块采用的访问策略和数据区块的一致)

一个索引区块在 MyISAM 索引文件中是一个连续访问的单元通常这个索引区块的大小和B树索引节点大小一样(索引在磁盘中是以B树结构来表示的这个树的底部时叶子节点叶子节点之上则是非叶子节点)

在索引缓存结构中所有的区块大小都是一样的这个值可能等于大于或小于表的索引区块大小通常这两个值是不一样的

当必须访问来自任何表的索引区块时服务器首先检查在索引缓存中是否有可用的缓冲区块如果有服务器就访问缓存中的数据而非磁盘就是说它直接存取缓存而不是存取磁盘否则服务器选择一个(多个)包含其它不同表索引区块的缓存缓冲区块将它的内容替换成请求表的索引区块的拷贝一旦新的索引区块在缓存中了索引数据就可以存取了

当发生被选中要替换的区块内容修改了的情况时这个区块就被认为脏了那么在替换之前它的内容就必须先刷新到它指向的标索引

通常服务器遵循LRU(最近最少使用)策略当要选择替换的区块时它选择最近最少使用的索引区块为了想要让选择变得更容易索引缓存模块会维护一个包含所有使用区块特别的队列(LRU链) 当一个区块被访问了就把它放到队列的最后位置当区块要被替换时在队列开始位置的区块就是最近最少使用的它就是第一候选删除对象

共享访问索引缓存

在MySQL 以前访问索引缓存是串行的两个线程不能并行地访问索引缓存缓冲服务器处理一个访问索引区块的请求只能等它之前的请求处理完结果新的请求所需的索引区块就不在任何索引缓存环冲区块中因为其他线程把包含这个索引区块的缓冲给更新了

从MySQL 开始服务器支持共享方式访问索引缓存

没有正在被更新的缓冲可以被多个线程访问

缓冲正被更新时需要使用这个缓冲的线程只能等到更新完成之后

多个线程可以初始化需要替换缓存区块的请求只要它们不干扰别的线程(也就是它们请求不同的索引区块因此不同的缓存区块被替换)

共享方式访问索引缓存令服务器明显改善了吞吐量

多重索引缓存

共享访问索引缓存改善了性能却不能完全消除线程间的冲突它们仍然争抢控制管理存取索引缓存缓冲的结构为了更进一步减少索引缓存存取冲突 MySQL 提供了多重索引缓存特性这能将不同的表索引指定到不同的索引缓存

当有多个索引缓存服务器在处理指定的 MyISAM 表查询时必须知道该使用哪个默认地所有的 MyISAM 表索引都缓存在默认的索引缓存中想要指定到特定的缓存中可以使用 CACHE INDEX 语句

如下语句所示指定表的索 t t 和 t 引缓存到名为 hot_cache 的缓存中

注意如果服务器编译支持存 ISAM 储引擎了那么 ISAM 表也使用索引缓存机制不过 ISAM 表索引只能使用默认的索引缓存而不能自定义

CACHE INDEX 语句中用到的索引缓存是根据用 SET GLOBAL 语句的参数设定的值或者服务器启动参数指定的值创建的如下　　mysql> SET GLOBAL keycache key_buffer_size= * ;　　想要删除索引缓存只需设置它的大小为　　mysql> SET GLOBAL keycache key_buffer_size= ;　　索引缓存变量是一个结构体变量由名字和组件构成例如 keycache key_buffer_size keycache 就是缓存名 key_buffer_size 是缓存组件　　默认地表索引在服务器启动时指定到主(默认的)索引缓存中当一个索引缓存被删掉后指定到这个缓存的所有索引都被重新指向到了默认索引缓存中去　　对一个繁忙的系统来说我们建议以下三条策略来使用索引缓存　　热缓存占用 %的总缓存空间用于繁重搜索但很少更新的表　　冷缓存占用 %的总缓存空间用于中等强度更新的表如临时表　　冷缓存占用 %的总缓存空间作为默认的缓存用于所有其他表　　使用三个缓存的一个原因是好处在于存取一个缓存结构时不会阻止对其他缓存的访问访问一个表索引的查询不会跟指定到其他缓存的查询竞争性能提高还表现在以下几点原因　　热缓存只用于检索记录因此它的内容总是不需要变化所以无论什么时候一个索引区块需要从磁盘中引入被选中要替换的缓存区块的内容总是要先被刷新　　索引被指向热缓存中后如果没有需要扫描全部索引的查询那么对应到B树中非叶子节点的索引区块极可能还保留在缓存中　　在临时表里必须频繁执行一个更新操作是相当快的如果要被更新的节点已经在缓存中了它无需先从磁盘中读取出来当临时表的索引大小和冷缓存大小一样时那么在需要更新一个节点时它已经在缓存中存在的几率是相当高的

中点插入策略

默认地 MySQL 的索引缓存管理系统采用LRU策略来选择要被清除的缓存区块不过它也支持更完善的方法叫做中点插入策略

使用中点插入策略时 LRU链就被分割成两半一个热子链一个温子链两半分割的点不是固定的不过缓存管理系统会注意不让温子链部分太短总是至少包括全部缓存区块的 key_cache_division_limit 比率 key_cache_division_limit 是缓存结构体变量的组件部分因此它是每个缓存都可以设置这个参数值

当一个索引区块从表中读入缓存时它首先放在温子链的末尾当达到一定的点击率(访问这个区块)后它就提升到热子链中去目前要提升一个区块的点击率( )对每个区块来说都是一样的将来我们会让点击率依靠B树中对应的索引区块节点的级别包含非叶子节点的索引区块所要求的提升点击率就低一点包含叶子节点的B索引树的区块的值就高点

提升起来的区块首先放在热子链的末尾这个区块在热子链内一直循环如果这个区块在该子链开头位置停留时间足够长了它就会被降级回温子链这个时间是由索引缓存结构体变量的组件 key_cache_age_threshold 值来决定的

这个阀值是这么描述的一个索引缓存包含了 N 个区块热子链开头的区块在低于 N*key_cache_age_threshold/ 次访问后就被移动到温子链的开头位置它又首先成为被删除的候选对象因为要被替换的区块还是从温子链的开头位置开始的

中点插入策略就能在缓存中总能保持更有价值的区块如果更喜欢采用LRU策略只需让 key_cache_division_limit 的值低于默认值

中点插入策略能帮助改善在执行需要有效扫描索引它会将所有对应到B树中高级别的有价值的节点推出的查询时的性能为了避免这样就必须设定 key_cache_division_limit 远远低于以采用中点插入策略则在扫描索引操作时那些有价值的频繁点击的节点就会保留在热子链中了

索引预载入

如果索引缓存中有足够的区块用来保存全部索引或者至少足够保存全部非叶子节点那么在使用前就载入索引缓存就很有意义了将索引区块以十分有效的方法预载入索引缓存缓冲从磁盘中顺序地读取索引区块

没有预载入查询所需的索引区块仍然需要被放到缓存中去虽然索引区块要保留在缓存中因为有足够的缓冲它们可以从磁盘中随机读取到而非顺序地

想要预载入缓存可以使用 LOAD INDEX INTO CACHE 语句如下语句预载入了表 t 和 t 的索引节点(区块)

增加修饰语 IGNORE LEAVES 就只预载入非叶子节点的索引区块因此上述语句加载了 t 的全部索引区块但是只加载 t 的非叶子节点区块

如果使用 CACHE INDEX 语句将索引指向一个索引缓存将索引区块预先放到那个缓存中去否则索引区块只会加载到默认的缓存中去

索引缓存大小

MySQL 引进了对每个索引缓存的新变量 key_cache_block_size 这个变量可以指定每个索引缓存的区块大小用它就可以来调整索引文件I/O操作的性能

当读缓冲的大小和本地操作系统的I/O缓冲大小一样时就达到了I/O操作的最高性能了但是设置索引节点的大小和I/O缓冲大小一样未必能达到最好的总体性能读比较大的叶子节点时服务器会读进来很多不必要的数据这大大阻碍了读其他叶子节点

目前还不能控制数据表的索引区块大小这个大小在服务器创建索引文件 ` MYI 时已经设定好了它根据数据表的索引大小的定义而定在很多时候它设置成和I/O缓冲大小一样在将来可以改变它的值并且会全面采用变量 key_cache_block_size

重建索引缓存

索引缓存可以通过修改其参数值在任何时候重建它例如

　　mysql> SET GLOBAL cold_cache key_buffer_size= * * ;

如果设定索引缓存的结构体变量组件变量 key_buffer_size 或 key_cache_block_size 任何一个的值和它当前的值不一样服务器就会清空原来的缓存在新的变量值基础上重建缓存如果缓存中有任何的脏索引块服务器会先把它们保存起来然后才重建缓存重新设定其他的索引缓存变量并不会重建缓存

lishixin/Article/program/Oracle/201311/16615

MySQL数据库优化(七)

为了能最小化磁盘I/O MyISAM 存储引擎采用了很多数据库系统使用的一种策略它采用一种机制将最经常访问的表保存在内存区块中

本章首先描述了 MyISAM 索引缓存的基本操作然后讨论在MySQL 中所做的改进它提高了索引缓存性能同时能更好地控制缓存操作

　　线程之间不再是串行地访问索引缓存多个线程可以并行地访问索引缓存　　可以设置多个索引缓存同时也能指定数据表索引到特定的缓存中

当索引缓存无法操作时索引文件就只通过操作系统提供的本地文件系统缓冲来访问(换言之表索引区块采用的访问策略和数据区块的一致)

在索引缓存结构中所有的区块大小都是一样的这个值可能等于大于或小于表的索引区块大小通常这两个值是不一样的

当发生被选中要替换的区块内容修改了的情况时这个区块就被认为脏了那么在替换之前它的内容就必须先刷新到它指向的标索引

共享访问索引缓存

从MySQL 开始服务器支持共享方式访问索引缓存

没有正在被更新的缓冲可以被多个线程访问

缓冲正被更新时需要使用这个缓冲的线程只能等到更新完成之后

多个线程可以初始化需要替换缓存区块的请求只要它们不干扰别的线程(也就是它们请求不同的索引区块因此不同的缓存区块被替换)

共享方式访问索引缓存令服务器明显改善了吞吐量

多重索引缓存

如下语句所示指定表的索 t t 和 t 引缓存到名为 hot_cache 的缓存中

注意如果服务器编译支持存 ISAM 储引擎了那么 ISAM 表也使用索引缓存机制不过 ISAM 表索引只能使用默认的索引缓存而不能自定义

中点插入策略

默认地 MySQL 的索引缓存管理系统采用LRU策略来选择要被清除的缓存区块不过它也支持更完善的方法叫做中点插入策略

中点插入策略就能在缓存中总能保持更有价值的区块如果更喜欢采用LRU策略只需让 key_cache_division_limit 的值低于默认值

索引预载入

没有预载入查询所需的索引区块仍然需要被放到缓存中去虽然索引区块要保留在缓存中因为有足够的缓冲它们可以从磁盘中随机读取到而非顺序地

想要预载入缓存可以使用 LOAD INDEX INTO CACHE 语句如下语句预载入了表 t 和 t 的索引节点(区块)

增加修饰语 IGNORE LEAVES 就只预载入非叶子节点的索引区块因此上述语句加载了 t 的全部索引区块但是只加载 t 的非叶子节点区块

如果使用 CACHE INDEX 语句将索引指向一个索引缓存将索引区块预先放到那个缓存中去否则索引区块只会加载到默认的缓存中去

索引缓存大小

MySQL 引进了对每个索引缓存的新变量 key_cache_block_size 这个变量可以指定每个索引缓存的区块大小用它就可以来调整索引文件I/O操作的性能

重建索引缓存

索引缓存可以通过修改其参数值在任何时候重建它例如

　　mysql> SET GLOBAL cold_cache key_buffer_size= * * ;

lishixin/Article/program/Oracle/201311/16615

mysql数据库如何优化,优化了哪些功能

mysql的优化大的有两方面：

1、配置优化
配置的优化其实包含两个方面的：操作系统内核的优化和mysql配置文件的优化
1)系统内核的优化对专用的mysql服务器来说，无非是内存实用、连接数、超时处理、TCP处理等方面的优化，根据自己的硬件配置来进行优化，这里不多讲；
2)mysql配置的优化，一般来说包含：IO处理的常用参数、最大连接数设置、缓存使用参数的设置、慢日志的参数的设置、innodb相关参数的设置等，如果有主从关系在设置主从同步的相关参数即可，网上的相关配置文件很多，大同小异，常用的设置大多修改这些差不多就够用了。
2、sql语句的优化
1、尽量稍作计算
Mysql的作用是用来存取数据的，不是做计算的，做计算的话可以用其他方法去实现，mysql做计算是很耗资源的。

2.尽量少 join

MySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join，一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈。

3.尽量少排序

排序操作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL的响应时间。
对于MySQL来说，减少排序有多种办法，比如：
通过利用索引来排序的方式进行优化
减少参与排序的记录条数
非必要不对数据进行排序

mysql数据库如何优化,优化了哪些功能

超详细MySQL数据库优化

数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.

1. 优化一览图

2. 优化

笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.

2.1 软优化

2.1.1 查询语句优化

1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.

2.例:

显示:

其中会显示索引和查询数据读取数据条数等信息.

2.1.2 优化子查询

在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

2.1.3 使用索引

索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者<MySQL数据库索引>一文,介绍比较详细,此处记录使用索引的三大注意事项:

2.1.4 分解表

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

2.1.5 中间表

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

2.1.6 增加冗余字段

类似于创建中间表,增加冗余也是为了减少连接查询.

2.1.7 分析表,,检查表,优化表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

1. 分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;

2. 检查表: 使用 CHECK关键字,如CHECK TABLE user [option]

option 只对MyISAM有效,共五个参数值:

3. 优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

2.2 硬优化

2.2.1 硬件三件套

1.配置多核心和频率高的cpu,多核心可以执行多个线程.

2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.

3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

2.2.2 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

2.2.3 分库分表

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

2.2.4 缓存集群

如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。

一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.

mysql对于大量数据,怎么进行优化

1）调整服务器的性能参数：key_buffer_size、Innodb_buffer_pool_size进行合理的配置
2）建立合适的索引
3）写查询语句用explain分析一下执行过程，核实一下执行计划，是否按照自己的意愿执行。
索引使要注意的地方：
1)索引不会包含有NULL值的列(使用索引的列设需要置默认值)2)使用短索引 3)不要在列上进行运算，即操作符号左端（使用函数）4) like语句操作5)不使用NOT IN和<>操作6)复合索引的建立7)选择自己使用的索引： USE INDEX ， IGNORE INDEX ， FORCE INDEX 8) where子句中已经使用了索引的话，那么order by中的列是不会使用索引的（使用复合索引解决）
表扫描要注意的地方：
1)数据表很小，全表扫描比做索引键的查找来得快。当表的记录总数小于10且比较短时通常这么做。
2)没有合适用于 ON 或 WHERE 分句的索引字段。
3)让索引字段和常量值比较，MySQL已经计算（基于索引树）到常量覆盖了数据表的很大部分。
4)通过其他字段使用了一个基数很小（很多记录匹配索引键值）的索引键。这种情况下，MySQL认为使用索引键需要大量查找，还不如全表扫描来得更快。
5)使用合适的索引可以解决表扫描
6) 使用Limit有时候也可以解决表扫描
优化的地方太多了，一一列举不完，你可以去这里看一下，这里面关于优化的知识有很多
http://www.quzixi.com/forum-2-2.html，如果觉得说的有用就给个好评，写这么多怪不容易的，用了我一刻钟的时间呀

mysql对于大量数据,怎么进行优化

mysql 存储过程执行太慢怎么优化

1.当我们请求mysql服务器的时候,MySQL前端会有一个监听,请求到了之后,服务器得到相关的SQL语句,执行之前(虚线部分为执行),还会做权限的判断

2.通过权限之后,SQL就到MySQL内部,他会在查询缓存中,看该SQL有没有执行过,如果有查询过,则把缓存结果返回,说明在MySQL内部,也有一个查询缓存.但是这个查询缓存,默认是不开启的,这个查询缓存,和我们的Hibernate，Mybatis的查询缓存是一样的,因为查询缓存要求SQL和参数都要一样,所以这个命中率是非常低的（没什么卵用的意思）。

3.如果我们没有开启查询缓存,或者缓存中没有找到对应的结果,那么就到了解析器,解析器主要对SQL语法进行解析

4.解析结束后就变成一颗解析树,这个解析树其实在Hibernate里面也是有的,大家回忆一下,在以前做过Hibernate项目的时候,是不是有个一个antlr.jar。这个就是专门做语法解析的工具.因为在Hibernate里面有HQL，它就是通过这个工具转换成SQL的,我们编程语言之所以有很多规范、语法,其实就是为了便于这个解析器解析,这个学过编译原理的应该知道.

5.得到解析树之后,不能马上执行,这还需要对这棵树进行预处理,也就是说,这棵树,我没有经过任何优化的树,预处理器会这这棵树进行一些预处理,比如常量放在什么地方,如果有计算的东西,把计算的结果算出来等等...

6.预处理完毕之后,此时得到一棵比较规范的树,这棵树就是要拿去马上做执行的树,比起之前的那棵树,这棵得到了一些优化

7.查询优化器，是MySQL里面最关键的东西,我们写任何一条SQL,比如SELECT * FROM USER WHERE USERNAME = toby AND PASSWORD = 1,它会怎么去执行?它是先执行username = toby还是password = 1?每一条SQL的执行顺序查询优化器就是根据MySQL对数据统计表的一些信息,比如索引,比如表一共有多少数据,MySQL都是有缓存起来的,在真正执行SQL之前,他会根据自己的这些数据,进行一个综合的判定,判断这一次在多种执行方式里面,到底选哪一种执行方式,可能运行的最快.这一步是MySQL性能中,最关键的核心点,也是我们的优化原则.我们平时所讲的优化SQL,其实说白了,就是想让查询优化器,按照我们的想法,帮我们选择最优的执行方案,因为我们比MySQL更懂我们的数据.MySQL看数据,仅仅只是自己收集到的信息,这些信息可能是不准确的,MySQL根据这些信息选了一个它自认为最优的方案,但是这个方案可能和我们想象的不一样.

8.这里的查询执行计划,也就是MySQL查询中的执行计划,比如要先执行username = toby还是password = 1

9.这个执行计划会传给查询执行引擎,执行引擎选择存储引擎来执行这一份传过来的计划,到磁盘中的文件中去查询,这个时候重点来了,影响这个查询性能最根本的原因是什么?就是硬盘的机械运动,也就是我们平时熟悉的IO,所以一条查询语句是快还是慢,就是根据这个时间的IO来确定的.那怎么执行IO又是什么来确定的?就是传过来的这一份执行计划.(优化就是制定一个我们认为最快的执行方案,最节省IO,和执行最快)

10.如果开了查询缓存,则返回结果给客户端,并且查询缓存也放一份。

mysql如何优化以下语句，查询耗时太久了？

一般进行性能分析，分如下三步：

首先需要使用慢查询日志功能，去获取所有查询时间比较长的SQL语句

其次查看执行计划查看有问题的SQL的执行计划 explain

最后可以使用show profile查看有问题的SQL的性能使用情况

慢查询日志分析

首先我们要使用慢查询日志，因为它收集了查询时间比较长的SQL语句，但使用之前必须开启慢查询日志，在配置文件my.cnf（一般为/etc/my.cnf）中的[mysqld] 增加如下参数：

slow_query_log=ONlong_query_time=3slow_query_log_file=/var/lib/mysql/slow-log.log复制代码

增加这些参数之后，重启MySQL，可以进行查询慢查询日志是否开启。

1. 任何地方都不要使用 select * from t，用具体的字段列表代替“*“，不要返回用不到的任何字段。

2. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

3. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

4. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

5. 尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

6. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

7. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where和order by相关的列上建立索引。

8. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。

例如： select * from t where num is null

我们可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select * from t where num=0。