精华内容
下载资源
问答
  • MySQL优化三:查询性能优化之查询优化器的局限性与提示
    千次阅读 多人点赞
    2019-01-17 19:22:50

    MySQL的万能嵌套循环并不是对每种查询都是最优的。不过MySQL查询优化器只对少部分查询不适用,而且我们往往可以通过改写查询让MySQL高效的完成工作。

    1 关联子查询

    MySQL的子查询实现的非常糟糕。最糟糕的一类查询时where条件中包含in()的子查询语句。因为MySQL对in()列表中的选项有专门的优化策略,一般会认为MySQL会先执行子查询返回所有in()子句中查询的值。一般来说,in()列表查询速度很快,所以我们会以为sql会这样执行

    select * from tast_user where id in (select id from user where name like '王%');
    我们以为这个sql会解析成下面的形式
    select * from tast_user where id in (1,2,3,4,5);
    实际上MySQL是这样解析的
    select * from tast_user where exists 
    (select id from user where name like '王%' and tast_user.id = user.id);

    MySQL会将相关的外层表压缩到子查询中,它认为这样可以更高效的查找到数据行。

    这时候由于子查询用到了外部表中的id字段所以子查询无法先执行。通过explin可以看到,MySQL先选择对tast_user表进行全表扫描,然后根据返回的id逐个执行子查询。如果外层是一个很大的表,那么这个查询的性能会非常糟糕。当然我们可以优化这个表的写法:

    select tast_user.* from tast_user inner join user using(tast_user.id) where user.name like '王%'

    另一个优化的办法就是使用group_concat()在in中构造一个由逗号分隔的列表。有时这比上面使用关联改写更快。因为使用in()加子查询,性能通常会非常糟糕。所以通常建议使用exists()等效的改写查询来获取更好的效率。

    如何书写更好的子查询就不在介绍了,因为现在基本都要求拆分成单表查询了,有兴趣的话可以自行去了解下。

    2 UNION的限制

    有时,MySQL无法将限制条件从外层下推导内层,这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

    如果希望union的各个子句能够根据limit只取部分结果集,或者希望能够先排好序在合并结果集的话,就需要在union的各个子句中分别使用这些子句。例如,想将两个子查询结果联合起来,然后在取前20条,那么MySQL会将两个表都存放到一个临时表中,然后在去除前20行。

    (select first_name,last_name from actor order by last_name) union all
    (select first_name,last_name from customer order by  last_name) limit 20;

    这条查询会将actor中的记录和customer表中的记录全部取出来放在一个临时表中,然后在取前20条,可以通过在两个子查询中分别加上一个limit 20来减少临时表中的数据。

    现在中间的临时表只会包含40条记录了,处于性能考虑之外,这里还需要注意一点:从临时表中取出数据的顺序并不是一定,所以如果想获得正确的顺序,还需要在加上一个全局的order by操作

    3 索引合并优化

    前面文章中已经提到过,MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

    4 等值传递

    某些时候,等值传递会带来一些意想不到的额外消耗。例如,有一个非常大的in()列表,而MySQL优化器发现存在where/on或using的子句,将这个列表的值和另一个表的某个列相关联。

    那么优化器会将in()列表都赋值应用到关联的各个表中。通常,因为各个表新增了过滤条件,优化器可以更高效的从存储引擎过滤记录。但是如果这个列表非常大,则会导致优化和执行都会变慢。

    5 并行执行

    MySQL无法利用多核特性来并行执行查询。很多其他的关系型数据库鞥能够提供这个特性,但MySQL做不到。这里特别指出是想提醒大家不要花时间去尝试寻找并行执行查询的方法。

    6 哈希关联

    在2013年MySQL并不执行哈希关联,MySQL的所有关联都是嵌套循环关联。不过可以通过建立一个哈希索引来曲线实现哈希关联如果使用的是Memory引擎,则索引都是哈希索引,所以关联的时候也类似于哈希关联。另外MariaDB已经实现了哈希关联。

    7 松散索引扫描

    由于历史原因,MySQL并不支持松散索引扫描,也就无法按照不连续的方式扫描一个索引。通常,MySQL的索引扫描需要先定义一个起点和重点,即使需要的数据只是这段索引中很少的几个,MySQL仍需要扫描这段索引中每个条目。

    例:现有索引(a,b)

    select * from table where b between 2 and 3;

    因为索引的前导字段是a,但是在查询中只指定了字段b,MySQL无法使用这个索引,从而只能通过全表扫描找到匹配的行。

    MySQL全表扫描:

    了解索引的物理结构的话,不难发现还可以有一个更快的办法执行上面的查询。索引的物理结构不是存储引擎的API使得可以先扫描a列第一个值对应的b列的范围,然后在跳到a列第二个不同值扫描对应的b列的范围

    这时就无需在使用where子句过滤,因为松散索引扫描已经跳过了所有不需要的记录。

    上面是一个简单的例子,处理松散索引扫描,新增一个合适的索引当然也可以优化上述查询。但对于某些场景,增加索引是没用的,例如,对于第一个索引列是范围条件,第二个索引列是等值提交建查询,靠增加索引就无法解决问题。

    MySQL5.6之后,关于松散索引扫描的一些限制将会通过索引条件吓退的分行是解决。

    8 最大值和最小值优化

    对于MIN()和MAX()查询,MySQL的优化做的并不好,例:

    select min(actor_id) from actor where first_name = 'wang'

    因为在first_name字段上并没有索引,因此MySQL将会进行一次全表扫描。如果MySQL能够进行主键扫描,那么理论上,当MySQL读到第一个太满足条件的记录的时候就是我们需要的最小值了,因为主键是严哥按照actor_id字段的大小排序的。但是MySSQL这时只会做全表扫描,我们可以通过show status的全表扫描计数器来验证这一点。一个区县优化办法就是移除min()函数,然后使用limit 1来查询。

    这个策略可以让MySQL扫描尽可能少的记录数。这个例子告诉我们有时候为了获得更高的性能,就得放弃一些原则。

    9 在同一个表上查询和更新

    MySQL不允许对同一张表同时进行查询和更新。这并不是优化器的限制,如果清楚MySQL是如何执行查询的,就可以避免这种情况。例:

    update table set cnt = (select count(*) from table as tb where tb.type = table.type);
    

    这个sql虽然符合标准单无法执行,我们可以通过使用生成表的形式绕过上面的限制,因为MySQL只会把这个表当做一个临时表来处理。

    update table inner join
    (select type,count(*) as cnt from table group by type) as tb using(type) 
    set table.cnt = tb.cnt;

    实际上这执行了两个查询:一个是子查询中的select语句,另一个是夺标关联update,只是关联的表时一个临时表。子查询会在update语句打开表之前就完成,所以会正常执行。

    10 查询优化器的提示(hint)

    如果对优化器选择的执行计划不满意,可以使用优化器提供的几个提示(hint)来控制最终的执行计划。下面将列举一些常见的提示,并简单的给出什么时候使用该提示。通过在查询中加入响应的提示,就可以控制该查询的执行计划。

    ① HIGH_PRIORITY 和 LOW_PRIORITY

    这个提示告诉MySQL,当多个语句同时访问某一表的时候,哪些语句的优先级相对高些,哪些语句优先级相对低些。

    HIGH_PRIORITY用于select语句的时候,MySQL会将此select语句重新调度到所有正在表锁以便修改数据的语句之前。实际上MySQL是将其放在表的队列的最前面,而不是按照常规顺序等待。HIGH_PRIORITY还可以用于insert语句,其效果只是简单的体校了全局LOW_PRIORITY设置对该语句的影响。

    LOW_PRIORITY则正好相反,它会让语句一直处于等待状态,只要在队列中有对同一表的访问,就会一直在队尾等待。在CRUD语句中都可以使用。

    这两个提示只对使用表锁的存储引擎有效,不能在InnoDB或其他有细粒度所机制和并发控制的引擎中使用。在MyISAM中也要慎用,因为这两个提示会导致并发插入被禁用,可能会严重降低性能。

    HIGH_PRIORITY和LOW_PRIORITY其实只是简单的控制了MySQL访问某个数据表的队列顺序。

    ② DELAYED

    这个提示对insert和replace有效。MySSQL会将使用该提示的语句立即返回给客户端,并将插入的行数据放入缓冲区,然后在表空闲时批量将数据写入。日志型系统使用这样的提示非常有效,或者是其他需要写入大量数据但是客户端却不需要等待单条语句完成I/O的应用。这个用法有一些限制。并不是所有的存储引擎都支持,并且该提示会导致函数last_insert_id()无法正常工作。

    ③ STRAIGHT_JOIN

    这个提示可以防止在select语句的select关键字之后,也可以防止在任何两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联。第二个用法则是固定其前后两个表的关联顺序。

    当MySQL没能选择正确的关联顺序的时候,或者由于可能的顺序太多导致MySQL无法评估所有的关联顺序的时候,STRAIGHT_JOIN都会很有用,在MySQL可能会发给大量时间在statistics状态时,加上这个提示则会大大减少优化器的搜索空间

    ④ SQL_SMALLRESULT和SQL_BIG_RESULT

    这个两个提示只对select语句有效。他们告诉优化器对group by或者distinct查询如何使用临时表及排序。SQL_SMALL_RESULT告诉优化器结果集会很小,可以将结果集放在内存中的索引临时表,以避免排序操作。如果是SQL_BIG_RESULT,则会告诉优化器结果集可能会非常大,建议使用磁盘临时表做排序操作。

    ⑤ SQL_BUFFER_RESULT

    这个提示告诉优化器将查询结果放入一个临时表,然后尽可能快速释放表锁。这和前面提到的由客户端缓存结果不同。当你无法使用客户端缓存的时候,使用服务器端的缓存通常很有效。好处是无需在客户端上消耗过多内存,还能尽快释放表锁。代价是服务器端将需要更多的内存。

    ⑥ SQL_CACHE和SQL_NO_CACHE

    这个提示告诉MySQL这个结果集是否应该放入查询缓存中。

    ⑦ SQL_CALC_FOUND_ROWS

    严哥来说,这并不是一个优化器提示。它不会告诉优化器任何关于执行计划的东西。它会让MySQL返回的结果集包含更多的信息。查询中加上该提示MySQL会计算limit子句之后这个查询要返回的结果集总数,而实际上值返回limit要求的结果集。可以通过函数found_row()获得这个值。慎用,后面会说明为什么。

    ⑧ FOR UPDATE和LOCK IN SHARE MODE

    这也不是真正的优化器提示。这两个提示主要控制select语句的锁机制,但只对实现了行级锁的存储引擎有效。使用该提示会对符合查询条件的数据行加锁。对于insert/select语句是不需要这两个提示的因为5.0以后会默认给这些记录加上读锁。

    唯一内置的支持这两个提示的引擎就是InnoDB,可以禁用该默认行为。另外需要记住的是,这两个提示会让某些优化无法正常使用,例如索引覆盖扫描。InnoDB不能在不访问主键的情况下排他的锁定行,因为行的版本信息保存在主键中。

    如果这两个提示被经常滥用,很容易早晨服务器的锁争用问题。

    ⑨ USE INDEX、IGNORE INDEX和FORCE INDEX

    这几个提示会告诉优化器使用或者不使用那些索引来查询记录。

    在5.0版本以后新增了一些参数来控制优化器的行为:

    ① optimizer_search_depth

    这个参数控制优化器在穷举执行计划时的限度。如果查询长时间处于statistics状态,那么可以考虑调低此参数。

    ② optimizer_prune_level

    该参数默认是打开的,这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划。

    ③optimizer_switch

    这个变量包含了一些开启/关闭优化器特性的标志位。

    前面两个参数时用来控制优化器可以走的一些捷径。这些捷径可以让优化器在处理非常复杂的SQL语句时,可以更高效,但也可能让优化器错过一些真正最优的执行计划,所以慎用。

    修改优化器提示可能在MySQL更新后让新版的优化策略失效,所以一定要谨慎。

    更多相关内容
  • 描述 MySQL 查询优化器的工作原理。 MySQL 查询优化器主要为执行的查询决断最有效的路线
  • MySQL查询优化器工作原理解析

    万次阅读 2016-05-28 21:06:31
    手册上MYSQL查询优化器概述;个人对MySQL优化器的理解;分析优化器优化过程中的信息;调节MySQL优化器的优化等

    手册上查询优化器概述

    查询优化器的任务是发现执行SQL查询的最佳方案。大多数查询优化器,包括MySQL的查询优化器,总或多或少地在所有可能的查询评估方案中搜索最佳方案。对于联接查询,MySQL优化器所调查的可能的方案数随查询中所引用的表的数目呈指数增长。对于小数量的表(典型小于7-10),这不是一个问题。然而,当提交的查询更大时,查询优化所花的时间会很容易地成为服务器性能的主要瓶颈。
    查询优化的一个更加灵活的方法是允许用户控制优化器详尽地搜索最佳查询评估方案。一般思想是优化器调查的方案越少,它编译一个查询所花费的时间越少。另一方面,因为优化器跳过了一些方案,它可能错过一个最佳方案。
    优化器关于方案数量评估的行为可以通过两个系统变量来控制:

    • optimizer_prune_level变量告诉优化器根据对每个表访问的行数的估计跳过某些方案。我们的试验显示该类“有根据的猜测”很少错过最佳方案,并且可以大大降低查询编辑次数。这就是为什么默认情况该选项为on(optimizer_prune_level=1)。然而,如果你认为优化器错过了一个更好的查询方案,则该选项可以关闭(optimizer_prune_level=0),风险是查询编辑花费的时间更长。请注意即使使用该启发,优化器仍然可以探测呈指数数目的方案。

    • ptimizer_search_depth变量告诉优化器对于每个未完成的“未来的”方案,应查看多深,以评估是否应对它进一步扩大。optimizer_search_depth值较小会使查询编辑次数大大减小。例如,如果optimizer_search_depth接近于查询中表的数量,对12、13或更多表的查询很可能需要几小时甚至几天的时间来编译。同时,如果用optimizer_search_depth等于3或4编辑,对于同一个查询,编译器编译时间可以少于1分钟。如果不能确定合理的optimizer_search_depth值,该变量可以设置为0,告诉优化器自动确定该值。
      我们可以通过show variables 来查看这些参数
      这里写图片描述
      备注(手册网址:http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter

    个人理解

    从官方手册上看,可以理解为,MySQL采用了基于开销的优化器,以确定处理查询的最解方式,也就是说执行查询之前,都会先选择一条自以为最优的方案,然后执行这个方案来获取结果。在很多情况下,MySQL能够计算最佳的可能查询计划,但在某些情况下,MySQL没有关于数据的足够信息,或者是提供太多的相关数据信息,估测就不那么友好了。
    但是感觉手册上,并没有说MySQL怎么去寻找最优方案呢?
    通过查询相应的资料,个人理解如下
    MySQL优化器中,一个主要的目标是只要可能就是用索引,而且使用条件最严格的索引来尽可能多、尽可能快地排除那些不符合索引条件的数据行,说白了就是选择怎样使用索引,当然优化器还受其他的影响。为了更直观,下面将通过例子来说明。
    创建一个表:

    CREATE TABLE t8(
    id1 INT NOT NULL ,
    id2 INT NOT NULL,
    KEY id1_key(`id1`),
    KEY id2_key(`id2`)
    ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
    

    插入几行数据如下:
    这里写图片描述
    当我执行如下查询语句时候,查询优化器会怎样进行优化呢?

    select * from t8 where id1=1 and id2=0;

    当然,MySQL不会傻到,从t8表中的一行开始,然后一行行的去比较,id1与id2。优化器会先分析数据表,得知有索引id1_key与id2_key,如果先判断id1_key的话,然后需要从4行数据中排除3行数据;如果先判断id2_key的话,然后需要从2行中排除1行。对人来说,这两种方式没有什么区别,但是对于程序而言,先判断id2_key需要较少的计算和磁盘输入输出。因此,查询优化器会规定程序,先去检验id2_key索引,然后在从中挑出id2为0的数据行。
    通过下图,我们可以看出,可以选择的索引有id1_key与id2_key,但是实际用到的索引只有id2_key
    这里写图片描述
    如果将SQL语句改为 select * from t8 where id1=1 and id2=0;执行情况也是一样的,不区分前后。如下图:
    这里写图片描述

    当然,如果将程序,修改为如下

    select * from t8 where id1=5 and id2=0;

    也可以分析得出,会使用id1_key索引
    这里写图片描述

    当然,如果在创建一个复合索引

    ALTER TABLE t8 ADD KEY id1_id2_key(`id1`,`id2`)

    此时,在此执行select * from t8 where id1=1 and id2=0; 当然会考虑使用id1_id2_key索引。
    这里写图片描述
    通过上面的例子,可以理解查询优化器在查询的时候,是选择哪一个索引作为最合适的索引。除此,也提示我们,要慎重选择创建索引。如,上面创建了三个索引(id1_key、id1_key、id1_id2_key),但是优化器优化程序时候,每次只能从中选择一个最合适的,如果创建过多,不仅仅是给数据的更新和插入带来了压力,同时也增加了优化器的压力。

    分析优化器优化过程中的信息

    其实,在上面已经查看过优化器优化过程中的信息,无非就是使用explain。在这里,在集中说说,里面的参数意义。如下图
    这里写图片描述
    id: MySQL Query Optimizer 选定的执行计划中查询的序列号。表示查询中执行 select 子句或操作表的顺序,id值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。
    select_type:查询类型,SIMPLE、PRIMARY、UNION、DEPENDENT UNION等。
    table:显示这一行的数据是关于哪张表的
    type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和all
    possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从where语句中选择一个合适的语句
    key: 实际使用的索引。如果为null,则没有使用索引。很少的情况下,mysql会选择优化不足的索引。这种情况下,可以在select语句中使用use index(indexname)来强制使用一个索引或者用ignore index(indexname)来强制mysql忽略索引
    key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好
    ref:显示索引的哪一列被使用了,如果可能的话,是一个常数
    rows:mysql认为必须检查的用来返回请求数据的行数
    extra:关于mysql如何解析查询的额外信息。

    调节MySQL优化器的优化

    影响索引的选择

    当我们在执行select * from t8 where id1=1 and id2=0; 语句的时候,优化器会id1_id2_key索引,但我们可以通过IGNORE INDEX、 IGNORE INDEX来影响索引的选择

    强制索引

    通过FORCE INDEX(索引1[,索引2])或者使用USE INDEX(索引1[,索引2]),来指定使用哪个索引,也可以指定多个索引,让优化器从中挑选。
    这里写图片描述

    这里写图片描述

    忽略索引

    可以使用IGNORE INDEX(索引1[,索引2])来忽略一些索引,这样优化器,就不会考虑使用这些所有,减少优化器优化时间。
    这里写图片描述

    影响优化器使用数据表的顺序

    一般情况下,MySQL优化器会自行决定按照哪种顺序扫描数据表才能最快地检索出数据,但是我们可以通过STRAGHT_JOIN强制优化器按特定的顺序使用数据表,毕竟优化器做的判断不一定都是最优的。使用原则是,让限制最强的选取操作最先执行。STRAIGHT_JOIN可以放在SELECT后面,也可以放在FROM子句中。
    如下图
    这里写图片描述

    这里写图片描述
    可以看出,无论from t8,t6还是from t6,t8,都是先检索t6中的表。但是使用STRAIGHT_JOIN的话,就会按照SQL中顺序。
    这里写图片描述
    为什么优化器要选择先判断t6中的数据呢?一个主要的原因,因为t6中数据更少。
    这里写图片描述
    如果将t8中数据删除几行后,很明显MySQL优化器选择顺序数据表的顺序就会发生变化。
    这里写图片描述

    控制SQL语句的优先权

    在高并发的网站中,因为MySQL默认的是写优先,有可能导致一些读操作有效时间内得不到执行机会,HIGH_PRIORITY可以使用在selectinsert操作中,让MYSQL知道,这个操作优先进行。
    这里写图片描述
    LOW_PRIORITY可以使用在insertupdate操作中,让mysql知道,这个操作将优先权将降低。
    这里写图片描述
    INSERT DELAYED告诉MySQL,这个操作将会延时插入。
    INSERT DELAYED INTO,是客户端提交数据给MySQL,MySQL返回OK状态给客户端。而这是并不是已经将数据插入表,而是存储在内存里面等待排队。当mysql有空余时,再插入。另一个重要的好处是,来自许多客户端的插入被集中在一起,并被编写入一个块。这比执行许多独立的插入要快很多,因为它较少了I/O操作。坏处是,不能返回自动递增的ID,以及系统崩溃时,MySQL还没有来得及插入数据的话,这些数据将会丢失。
    这里写图片描述

    控制查询缓冲

    在实际开发中,一些数据对实时性要求特别高,或者并不经常使用(可能几天就执行一次或两次),这样就需要把缓冲关了,不管这条SQL语句是否被执行过,服务器都不会在缓冲区中查找该数据,每次都会从磁盘中读取。因为如果实时性要求特别高,缓存中数据可能和磁盘中的就不同步,如果数据不经常使用,被缓存起来,就会占用内存。
    在my.ini中的query_cache_type,使用来控制表缓存的。这个变量有三个取值:0,1,2,分别代表了off、on、demand。
    0:表示query cache 是关闭。
    1:表示查询总是先到查询缓存中查找,即使使用了sql_no_cache仍然查询缓存,因为sql_no_cache只是不缓存查询结果,而不是不使用查询结果。
    2:表示只有在使用了SQL_CACHE后,才先从缓冲中查询数据,仍然将查询结果缓存起来。
    我本地缓存是关闭的,,如下图。
    这里写图片描述
    关于MySQL缓存可以参考这里
    http://blog.csdn.net/hsd2012/article/details/51526707

    展开全文
  • MySQL:常用的MySQL优化工具

    千次阅读 2020-05-21 10:37:07
    影响数据库性能的常见因素如下: (1)磁盘IO; (2)网卡流量; (3)服务器硬件; (4)SQL查询速度。...针对潜在的问题,给出改进的建议,帮助进行MySQL优化。MySQLTuner支持MySQL / MariaDB / Perc

    影响数据库性能的常见因素如下:
    (1)磁盘IO;
    (2)网卡流量;
    (3)服务器硬件;
    (4)SQL查询速度。

    下面介绍几个mysql 优化的工具,可以使用它们对MySQL进行检查,生成awr报告,从整体上把握数据库的性能情况。

    一、MySQLTuner.pl

    MySQLTuner是MySQL一个常用的数据库性能诊断工具,主要检查参数设置的合理性,包括日志文件、存储引擎、安全建议及性能分析。针对潜在的问题,给出改进的建议,帮助进行MySQL优化。MySQLTuner支持MySQL / MariaDB / Percona Server的约300个指标。

    项目地址:https://github.com/major/MySQLTuner-perl

    1.1 下载

    wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
    

    1.2 使用

    [root@localhost ~]# ./mysqltuner.pl --socket /var/lib/mysql/mysql.sock
     >> MySQLTuner 1.7.4 - Major Hayden <major@mhtx.net>
     >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >> Run with '--help' for additional options and output filtering
    [--] Skipped version check for MySQLTuner script
    Please enter your MySQL administrative login: root
    Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.23
    [OK] Operating on 64-bit architecture
    

    1.3、报告分析
    (1)重要关注[!!](中括号有叹号的项)例如[!!] Maximum possible memory usage: 4.8G (244.13% of installed RAM),表示内存已经严重用超了。
    在这里插入图片描述
    (2)关注最后给的建议“Recommendations ”。
    在这里插入图片描述

    二、tuning-primer

    tuning-primer针于mysql的整体进行一个体检,对潜在的问题,给出优化的建议。

    项目地址:https://github.com/BMDan/tuning-primer.sh

    目前,支持检测和优化建议的内容如下:
    (1)慢查询日志
    (2)最大连接数
    (3)工人线程
    (4)密钥缓冲区【仅限MyISAM】
    (5)查询缓存
    (6)排序缓冲区
    (7)加盟
    (8)临时表
    (9)表(开放和定义)缓存
    (10)表锁定
    (11)表扫描(read_buffer)【仅限MyISAM】
    (12)InnoDB状态

    2.1 下载

    wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh
    

    2.2 使用

    [root@localhost ~]# [root@localhost dba]# ./tuning-primer.sh
    
     -- MYSQL PERFORMANCE TUNING PRIMER --
     - By: Matthew Montgomery -
    

    2.3 报告分析
    重点查看有红色告警的选项,根据建议结合自己系统的实际情况进行修改,例如:
    在这里插入图片描述
    三、pt-variable-advisor
    pt-variable-advisor 可以分析MySQL变量,并就可能出现的问题提出建议。

    3.1 安装
    https://www.percona.com/downloads/percona-toolkit/LATEST/

    [root@localhost ~]#wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-re85ce15-el7-x86_64-bundle.tar
    
    [root@localhost ~]#yum install percona-toolkit-3.0.13-1.el7.x86_64.rpm
    

    3.2 使用
    pt-variable-advisor是pt工具集的一个子工具,主要用来诊断参数设置是否合理。

    [root@localhost ~]# pt-variable-advisor localhost --socket /var/lib/mysql/mysql.sock
    

    3.3 报告分析
    重点关注有WARN的信息的条目,例如:
    在这里插入图片描述

    四、pt-qurey-digest

    pt-query-digest 主要功能是从日志、进程列表和tcpdump分析MySQL查询。

    4.1安装
    https://www.percona.com/downloads/percona-toolkit/LATEST/

    [root@localhost ~]#wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-re85ce15-el7-x86_64-bundle.tar
    
    [root@localhost ~]#yum install percona-toolkit-3.0.13-1.el7.x86_64.rpm
    

    4.2使用
    pt-query-digest主要用来分析mysql的慢日志,与mysqldumpshow工具相比,py-query_digest 工具的分析结果更具体,更完善。

    [root@localhost ~]# pt-query-digest /var/lib/mysql/slowtest-slow.log
    

    4.3 常见用法分析
    (1)直接分析慢查询文件:

    pt-query-digest /var/lib/mysql/slowtest-slow.log > slow_report.log
    

    (2)分析最近12小时内的查询:

    pt-query-digest --since=12h /var/lib/mysql/slowtest-slow.log > slow_report2.log
    

    (3)分析指定时间范围内的查询:

    pt-query-digest /var/lib/mysql/slowtest-slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00'> > slow_report3.log
    

    (4)分析指含有select语句的慢查询

    pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/slowtest-slow.log> slow_report4.log
    

    (5)针对某个用户的慢查询

    pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /var/lib/mysql/slowtest-slow.log> slow_report5.log
    

    (6)查询所有所有的全表扫描或full join的慢查询

    pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' /var/lib/mysql/slowtest-slow.log> slow_report6.log
    

    4.4 报告分析
    第一部分:总体统计结果 Overall:总共有多少条查询 Time range:查询执行的时间范围 unique:唯一查询数量,即对查询条件进行参数化以后,总共有多少个不同的查询 total:总计 min:最小 max:最大 avg:平均 95%:把所有值从小到大排列,位置位于95%的那个数,这个数一般最具有参考价值 median:中位数,把所有值从小到大排列,位置位于中间那个数。

    第二部分:查询分组统计结果 Rank:所有语句的排名,默认按查询时间降序排列,通过–order-by指定 Query ID:语句的ID,(去掉多余空格和文本字符,计算hash值) Response:总的响应时间 time:该查询在本次分析中总的时间占比 calls:执行次数,即本次分析总共有多少条这种类型的查询语句 R/Call:平均每次执行的响应时间 V/M:响应时间Variance-to-mean的比率 Item:查询对象。

    第三部分:每一种查询的详细统计结果 ID:查询的ID号,和上图的Query ID对应 Databases:数据库名 Users:各个用户执行的次数(占比) Query_time distribution :查询时间分布, 长短体现区间占比。Tables:查询中涉及到的表 Explain:SQL语句。

    参考:https://mp.weixin.qq.com/s/dqxh1bIn5XlPQERAhQZ4xA

    展开全文
  • [玩转MySQL之六]MySQL查询优化器

    千次阅读 2018-12-21 14:55:27
    注:由于查询优化器涉及面很广也比较复杂,作者也没有完全领会,本文主要来自书籍<<数据库查询优化的艺术: 原理解析和SQL性能优化>>,如果涉及到版权,请告知作者,删除本文。 一、查询语句的执行过程...

    注:由于查询优化器涉及面很广也比较复杂,作者也没有完全领会,本文主要来自书籍<<数据库查询优化的艺术: 原理解析和SQL性能优化>>,如果涉及到版权,请告知作者,删除本文。

    一、查询语句的执行过程简介

    MySQL查询语句在数据库中的执行分为5个阶段,具体如下:

    1.1 SQL输入

    数据库接收用户输入的SQL语句,准备执行。

    1.2 语法分析

    对输入的SQL语句进行词法分析、语法分析,得到语法分析树。在这个阶段,输入的是一条SQL语句,输出的是一棵多叉树。

    1.3 语义检查

    根据语法树和系统的元信息进行语义检查,本阶段是对语法分析树进行逻辑判断,树的结构不发生变化。对语法分析树上的各个节点进行语义分析,判断对象是否存在、是否重名等,对不合语义的地方报告错误。

    1.4 SQL优化

    SQL优化通常包括两项工作:一是逻辑优化、二是物理优化。这两项工作都要对语法分析树的形态进行修改,把语法分析树变为查询树。其中,逻辑查询优化将生成逻辑查询执行计划。在生成逻辑查询执行计划过程中,根据关系代数的原理,把语法分析树变为关系代数语法树的样式,原先SQL语义中的一些谓词变化为逻辑代数的操作符等样式,这些样式是一个临时的中间状态,经过进一步的逻辑查询优化,如执行常量传递、选择下推等(如一些节点下移,一些节点上移),从而生成逻辑查询执行计划。
    在生成逻辑查询计划后,查询优化器会进一步对查询树进行物理查询优化。物理优化会对逻辑查询进行改造,改造的内容主要是对连接的顺序进行调整。SQL语句确定的连接顺序经过多表连接算法的处理,可能导致表之间的连接顺序发生变化,所以树的形态有可能调整。
    物理查询优化除了进行表的连接顺序调整外,还会使用代价估算模型对单个表的扫描方式、两表连接的连接算法进行评估,选择每一项操作中代价最小的操作为下一步优化的基础。
    物理查询优化的最终结果是生成最终物理查询执行计划。

    1.5 SQL执行

    在SQL执行阶段,依据物理查询计划执行查询,逐步调用相关算法进行执行。算法包括一趟算法、嵌套循环连接、基于排序的两趟算法、基于散列的两趟算法、基于索引的算法、使用超过两趟的算法等。

    二、 逻辑查询优化

    2.1 逻辑查询优化思路

    查询优化器在逻辑优化阶段主要解决的问题是: 如何找出SQL语句等价的变换形式,使得SQL执行更高效。
    一条SQL查询语句结构复杂,包含多种类型的字句,优化操作依赖于表的一些属性(如索引和约束等)。可用于优化的思路包括:

    • 字句局部优化: 每种类型字句都可能存在优化方式,如等价谓词重写、where和having条件化简中的大部分情况,都属于这种字句范围内的优化。
    • 字句间关联优化: 字句与字句之间关联的语义存在优化的可能,如外连接消除、连接消除、子查询优化、视图重写等都属于字句间的关联优化,因为他们的优化都需要借助其他字句、表定义或列属性等信息进行。
    • 局部与整体的优化: 需要协同考虑局部表达式和整体的关系,如OR重写并集规则需要考虑UNION操作(UNION师变换后的整体的形式)的花费和OR操作(OR是局部表达式)的花费。
    • 形式变化优化: 多个字句存在嵌套,可以通过形式的变化完成优化,如嵌套连接消除。
    • 语义优化:根据完整性约束、SQL表达的含义等信息对语句进行语义优化。

    2.2 查询重写规则

    传统的联机事务处理(OLTP)使用基于选择(SELECT)、投影(PROJECT)、连接(JOIN)3种基本操作相结合的查询,这种查询称为SPJ查询。
    数据库在查询优化的过程中,会对这3种基本操作进行优化。优化的方式如下:

    • 选择操作: 对应的是限制条件(格式类似field consant,field表示列对象,op是操作符,如=,>等),优化方式是选择操作下推,目的是尽量减少连接操作前的远组数,使得中间临时关系尽量少(元组数少,连接得到的远组数就少),这样可减少IO和CPU的消耗,节约内存空间。
    • 投影操作: 对应的SELECT查询的目的列对象,优化方式是投影操作下推,目的是尽量减少连接操作前的列数,使得中间临时关系尽量小(特别注意差别:选择操作是使元组的个数"尽量少",投影操作是使一条元组"尽量小"),这样虽然不能减少IO(多数数据库存储方式是行存储,元组是读取的最基本单位,所以要想操作列则必须读取一行数据),但可以减少连接后中间关系的元组大小,节约内存空间)。
    • 连接关系: 对应的是连接条件(格式类似field_1, field_2,field_1和field_2表示不同表上的列对象,op是操作符,如=,>等),表示两个表连接的条件。这里涉及以下两个子问题:

      • 多表连接中每个表被连接的顺序决定着效率。如果一个查询语句只有一个表,则这样的语句很简单;但如果有多个表,则会涉及表之间以什么样的顺序连接效率最高效(如A、B、C三表连接,如果ABC、ACB、BCA等连接后的结果集一样,则计算哪种连接次序的效率最高,是需要考虑的问题)。
      • 多表连接每个表被连接的顺序由用户语义决定。查询语句多表连接有着不同的语义(如笛卡尔积、内连接 、还是外连接中的左外连接等),这决定着表之间的额前后连接次序是不能随意更换的,否则结果集中数据是不同的。因此,表的前后连接次序是不能随意交换的。
    • 根据SQL语句的形式特点,可以针对SPJ的查询优化,如基于选择、投影、连接3种基本操作相结合的查询。

    2.3 启发式规则再逻辑优化阶段的应用

    逻辑优化阶段使用的启发式规则通常包括如下两类:

    2.3.1 一定能带来优化效果的,主要包括:

    • 优先做选择和投影(选择条件在查询树上下推)
    • 子查询的消除
    • 嵌套连接的消除
    • 外连接的消除
    • 连接的消除
    • 使用等价谓词重写,对条件化简
    • 语义优化
    • 剪掉冗余操作(一些剪枝优化技术)、最小化查询块。

    2.3.2 变换未必会带来性能的提高,需根据代价选择,主要包括:

    • 分组的合并
    • 借用索引优化分组、排序、DISTINCT等操作
    • 对视图的查询变为基于表的查询
    • 连接条件的下推
    • 分组的下推
    • 连接提取公共表达式
    • 谓词的上拉
    • 用连接取代集合操作
    • 用UNIONALL取代OR操作

    三、物理优化

    查询优化器在物理优化阶段,主要解决的问题如下:

    • 从可选的单表扫描方式中,挑选什么样的单表扫描方式是最优的?
    • 对于两个表连接时,如何选择是最优的?
    • 对多个表连接,连接顺序有多种组合,是否要对每种组合都探索?如果不全部探索,怎么找到最优的一种组合?
      在查询优化器实现的早期,使用的是逻辑优化技术,即使用关系代数规则和启发式规则对查询进行优化后,认为生成的执行计划就是最优的。

    在引入了基于代价的查询优化方式后,对查询执行计划做了定量的分析,对每一个可能的执行方式进行评估,挑出代价最小的作为最优的计划。
    目前数据库的查询优化器通常融合这两种方式。

    3.1 查询代价估算

    查询代价估算的重点是代价估算模型,这是物理查询优化的依据。除了代价模型外,选择率对代价求解也起着重要作用。

    3.2 单表扫描算法

    单表扫描需要从表上获取元组,直接关联到物理IO的读取,所以不同的单表扫描方式,有不同的代价。

    3.3 索引

    索引是 建立在表上的,本质上是通过索引直接定位表的物理元组,加快数据获取的方式,所以索引优化的手段应该归属到物理查询优化阶段。

    3.4 两表连接算法

    关系代数的一项重要操作是连接运算,多个表连接是建立在两表之间连接的基础上的。研究两表连接的方式,对连接效率的提高有着直接的影响。

    3.5 多表连接算法

    多表连接算法实现的是在查询路径生成的过程中,根据代价估算,从各种可能的候选路径中找出最优的路径(最优路径是代价最小的路径)。
    多表连接算法需要解决两个问题:

    • 多表连接的顺序: 表的不同连接顺序,会产生许多不同的连接路径;不同的连接路径有不同的效率。
    • 多表连接的搜索空间:因为多表连接的顺序不同,产生的连接组合会有多种,如果这个组合的数据巨大,连接次数会达到一个很高的数量级,最大可能的连接次数是N!(N的阶乘)。比如N=5,连接次数是120;N=10,连接次数是362880。所有的连接可能构成一个巨大的"搜索空间"。如何将搜索空间限制在一个可接受的时间范围内,并高效地生成查询执行计划将成为一个难点。

    四、查询优化器与其他模块的关系

    在数据库内部,根据功能不同,可以划分出多个模块,不同模块之间有的关系紧密,有的关系松散。查询优化器是其中的一个功能模块,是实现查询优化技术的模块。下面介绍数据库中与查询优化器相关的模块:

    4.1 查询优化器与语法分析器

    语法分析器是查询优化器的输入。理解查询优化器,从语法分析器开始,将是个好的开端。因为不同对象有着不同的数据结构,数据结构成员是对象属性的载体,而语法分析器把一个SQL分解为众多数据结构体并给数据结构赋值,这样才能被查询优化器逐项获取并用与计算,比如逻辑查询优化有一条"常量传递"规则,如果没有语法分析器分解条件,也不可能推知列值是常量,也不可能有此优化。

    4.2 优化器与执行器

    查询优化器是执行器的前端输入部分。查询优化器计划一条SQL的具体执行方式和步骤 ,执行器具体去完成计划中的每一步。
    在实践中,一条SQL最耗时的阶段多发生在执行阶段。如果查询计划做得不好,则执行起来非常耗时。

    4.3 优化器与缓冲区

    缓冲区有多种多样,比如与数据相关的缓冲区(如从存储设备加载数据到内存)、与实现过程相关的辅助缓冲区(如排序用到的临时表或内存块),与功能模块相关的缓冲区(如日志缓冲区)等。
    优化器主要是对SQL输入进行逻辑方式的变换,没有涉及数据部分,只涉及对数据量的估计。当估算排序空间的时候,会涉及排序缓冲区;当估算数据IO的时候,需要考虑数据是否在数据缓存中。所以,查询优化器与数据缓冲区有一定的关系。

    4.4 优化器与统计

    MySQL数据库的查询优化器使用了基于代价的查询执行计划估算,所以依赖于被查对象的各种数据,而数据是动态变化的,如表的元组数。如果实时获取这些数据,系统计算的开销会比较大。为了避免这样的问题,定期或者根据需要统计这些数据,则比较切合实际。
    优化器在物理优化阶段,需要对单表读取开销,两表连接开销,多表连接顺序开销等进行比较,比较基于的就是一些基础数据的值,这些数据通常不会被实时更新,所以优化器有时做出的计划未必是最合适的。

    4.5 优化器与索引

    优化器做物理查询优化需要利用索引提高单表扫描效率,进而减少了多表连接时的元组数,所以确定哪些索引可用、怎么有效利用索引等都在查询优化器中得到体现。

    五、 MySQL查询优化器概述

    MySQL 查询优化器的主要功能是完成SELECT语句的执行,在保证SELECT语句正确执行之外,还有一个重要的功能,就是使用关系代数、启发式规则、代价估值模型等不同种类的技术,提高SELECT语句的执行效率。

    MySQL 查询 优化 器 实现 第 2 章介绍 的 大多数查询优化技术,这些 技术, 用于 对 SPJ 和 非 SPJ 类型 的 查询 语句 进行 优化。

    下面将从整体上介绍MySQL查询优化器, 分别对MySQL 查询优化器的执行过程、架构、层次、设计 思想、主要概念、代码结构上宏观探讨MySQL查询优化器的实现。

    5.1 MySQL查询执行过程

    MySQL查询执行过程分为4个阶段,如下所示:

    • 语法分析阶段: 将SQL查询语句经词法和语法分析后变换成为一棵查询树st_select_lex传给优化器,并对SQL表达的语义进行检查。
    • 生成逻辑查询执行计划阶段: 优化器在查询树中遍历每个关系,确定关系是否是常量表、为每个关系查找可用的索引、运用关系代数原理和启发式规则进行逻辑上的查询优化(如消除子查询、消除外连接等)。
    • 生成物理查询执行计划阶段: 优化器对各个连接的表进行排序,然后求解多表连接最优路径,对于每个关系尽量利用索引计算其代价,找出代价最小的路径后保存到JOIN类的bets_positions
    • 执行查询执行计划阶段: 把查询执行计划传到执行器进行执行。

    mysql-exe.png

    MySQL查询优化器在逻辑查询执行计划阶段,机遇关系代数规则和启发式规则,把用户指定的SQL经过"等价"的代数转换,变为一种更节省IO的执行序列,执行起来更为高效。

    MySQL查询优化器在物理查询执行计划阶段,在解决多表连接的问题时,有两套算法:一是用户指定表连接次序的算法;二是混杂了贪婪和穷举思想的算法,解决的是较多表的连接和非用户指定连接次序的多表连接,但不能保证得到最优的查询执行计划。

    5.2 MySQL查询优化器的架构和设计思想

    MySQL查询优化器设计精巧,但层次不够清晰,V5.6之后的版本,混乱状态有所改善,但MySQL查询优化器实用而高效,在充分利用索引的基础上,实现了很多查询优化技术,有很多精巧之处值得学习探索。

    MySQL查询优化过程中,查询优化器通过JOIN对象的方法,如JOIN.prepare()、JOIN.optimize(),完成优化工作。JOIN.prepare()完成的查询优化主要包括:子查询的冗余子句消除、IN类型子查询优化、将ALL/ANY等类型的子查询转换为MIN/MAX等操作,这是对简单子查询进行的优化;JOIN.optimize()函数完成的查询优化主要包括:子查询上拉,把外连接优>化为内连接,把嵌套连接消除,WHRER子句、JOIN/ON子句、HAVING子句条件表达式的化简(尤其是对含有常量的表达式的化简、等式合并),优化没有GROUPBY子句情况下的COUNT(*)、MIN()、MAX(),裁剪分区partition(如果查询的表是分区表),确定多表的连接路径(单表是多表的特例,统计join的代价,两种多表连接算法选其一搜索最优的join顺序、生成执行计划)、优化等式谓词、优化DISTINCT、创建临时表存储临时结果优化分组排序等操作。在这样的过程中,MySQL没有把优化过程明显地分为逻辑查询优化阶段和物理查询优化阶段,而是互为混杂,在物理查询优化之后,继续进行了部分逻辑查询优化。这是MySQL查询优化器的一大特点。

    5.3 MySQL查询优化器架构

    MySQL查询优化器为SQL查询语句求解最优的执行方式。MySQL查询优化器架构和执行过程如下图所示。

    MySQL-opimize-structure.png

    MSQL查询语句的执行主要历经4个过程,分别如下:

    1. P1过程:SQL语句输入变为语法查询树。
    2. P2过程:查询预处理,优化相关的内容主要是子查询优化。
    3. P3过程:语法树变为逻辑关系查询树,进而变为物理查询执行计划,挑出最优计划。
    4. P4过程:依据最优查询执行计划得到查询结果。

    MySQL查询语句的执行,主要历经以下4个模块。

    1. M1模块:语法分析模块,执行过程P1的任务。
    2. M2模块:查询 预处理模块,执行过程P2的任务。
    3. M3模块:查询优化模块,执行过程P3的任务。
    4. M4模块:查询执行模块,执行过程P4的任务。

    实现MySQL查询优化器功能的主要是M3模块,其主要有以下两个子阶段的工作。

    • M3-S1逻辑查询优化阶段:把语法查询树通过关系代数原理,优化为关系代数查询树,关系代数的原理在这个阶段运用;
    • M3-S2物理查询优化阶段:把关系代数查询树用于贪婪算法,生成最优执行计划。

    5.4 MySQL查询优化器的层次

    MySQL整个查询优化器从代码层面看,逻辑结构不是很清晰,但是从技术层面看,还是能够分为两个阶段,一是逻辑查询优化阶段,二是物理查询优化阶段。

    • 逻辑查询优化阶段主要依据关系代数可以推知的规则和启发式规则,对SQL语句进行等价变换。MySQL淋漓尽致地使用了关系代数中可推定的各项规则,对投影、选择等操作进行句式的优化;对条件表达式进行了谓词的优化、条件化简;对连接语义进行了外连接、嵌套连接的优化;对集合、GROUPBY等尽量利用索引、排序算法进行优化。另外还利用子查询优化、视图重写、语义优化等技术对查询语句进行了优化。
    • 在物理查询优化阶段,通过贪婪算法,并依据代价估算模型,在求解多表连接顺序的过程中,对多个连接的表进行排序并探索连接方式,找出花费最小的路径,据此生成查询执行计划。在这个阶段,对于单表扫描和两表连接的操作,高效地使用了索引,提高了查询语句的执行速度。

    六 、 从功能角度看MySQL查询优化

    MySQL的查询优化技术的实现,基本也可以分为逻辑优化和物理优化两个阶段,只是和PostgreSQL相比,界线没有那么清晰。MySQL的查询优化过程概述如下:

    1. 优先处理集合操作,把集合操作分解为普通的SPJ和非SPJ操作。
    2. 应用子查询优化技术,去除子查询中冗余部分,转换为半连接、用物化操作优化子查询、执行In向EXISTS转换、优化ALL/ANY等类型的子查询向MIN/MAX转换等。
    3. 消除外连接、消除嵌套连接。
    4. 利用等价谓词重写优化技术,优化WHERE、JOIN/ON、 HAVING等条件中的表达式,尤其是常量表达式和多重等式。
    5. 利用索引优化count(*),MIN(),MAX()。
    6. 进行多表连接的顺序确定。

      • 找出常量表,求解多表连接的过程中不使用常量表作为连接的表(减少搜索空间)。
      • 尽量利用索引优化GROUP BY、DISTINCT结合的操作
      • 利用代价估算模型,评估连接的花费,找出最优连接。
      • 用物化优化半连接嵌套的形式。
      • 从两种多表连接的算法中任选其一: 一是用户指定连接顺序 ,二是使用贪婪和穷尽结合的方式。
      • "选择"、"投影"操作下推
      • 利用索引对ORDER BY进行优化
      • 对GROUP BY/DISTINCT的组合情况进行优化
      • 确定半连接优化策略,从5种备选策略选择其中之一。
      • 对没有GROUP BY和ORDER BY字句的IN子查询进行优化。

    七、 参考文献

    书籍: <<数据库查询优化的艺术: 原理解析和SQL性能优化>>

    展开全文
  • MySQL 优化器可选开关详解

    千次阅读 2014-03-13 16:17:09
    MySQL 优化器的可选开关进行了详细解释。
  • MySQL调优(6)- 优化器自动优化

    千次阅读 2020-04-19 18:46:57
    你真的懂Mysql吗?一条SQL的执行到底经历了些什么? 我之前花了三两天对Mysql进行了一段时间的学习,当时就觉得Mysql也挺好学的阿,事实上,现在回过头,发现只是学会了一些基本的增删改查而已。 其实想要写出高质量...
  • MySQL-查询优化

    万次阅读 多人点赞 2018-12-20 18:32:02
    对于刚入行的程序猿来说,如何优化MySQL查询,是必须跨过的坎。网上有很多关于SQL优化的博文,但大多是片段和结论。这里,我摘抄了《高性能MySQL》一书的内容,从全局的角度将MySQL查询优化的思路和要点进行串通,...
  • MySQL 8.0新增特性use_invisible_indexes 是否使用不可见索引,MySQL 8.0新增可以创建invisible索引,这一开关控制优化器是否使用invisible索引,on表示考虑使用。MySQL 5.7新增derived_merge 派生表合并,类似...
  • MySQL优化 高性能MySQL 高清完整中文第三版 含目录。适合DBA和开发人员阅读
  • 失效私聊补上,132张
  • 本篇博客主要优化 MySQL 中的插入操作,核心实现 insert 优化任务。 load data infile 导入数据 使用上述命令,可以大幅度提高批量插入数据。 如果希望使用该命令,可以用 show 命令进行测试 show variables like '%...
  • 面试官:你做过哪些Mysql优化

    千次阅读 2022-02-04 19:47:39
    书写优化 一、用PreparedStatement PreparedStatement 一般来说比Statement性能高:一个sql 发给服务器去执行,涉及步骤:语法检查、语义分析, 编译,缓存 二、避免外键约束 有外键约束会影响插入和删除性能,如果...
  • 超详细汇总21个mysql优化实践【收藏版MySQL优化

    万次阅读 多人点赞 2022-01-12 18:50:33
    21个mysql优化最佳实践
  • mysql优化方案

    千次阅读 2021-12-27 08:34:10
    一、优化方向二、硬件方面优化三、软件配置1、网络方面的配置,要修改/etc/sysctl.conf文件2、mysql本身的一些优化mysql配置文件 /etc/my.cnf3、innodb方案4、MyISAM 参数配置4.1,设置索引缓存区大小4.2,设置读...
  • Mysql优化

    千次阅读 2021-01-19 06:29:42
     策略8.Like查询要当心 like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作 EXPLAIN select * from staffs where name ='july' EXPLAIN select * from staffs where name like '%july%' EXPLAIN select...
  • mysql性能优化之order by limit

    千次阅读 2020-08-12 18:01:13
    mysql性能优化之order by limit 最近遇到一个慢查询sql,是用来做分页查询的,查询的表字段有一百多个,查询10万数据时间3S+。 通过查看表的设计,发现order by中的数据是建立了索引的,我用查询语句做测试的时候,...
  • MySql 配置优化

    千次阅读 2022-01-27 11:12:25
    针对MySQL优化,无非是涉及到内存、IO、CPU的优化,该文档将依次从这三个方面来介绍。 问题定位 如何定位是内存、IO、CPU当中的哪一个环节的问题。可以参考如下步骤: 使用top命令查看MySQL进程的CPU及内存使用...
  • MySQL基本介绍 MySQL优化方式 MySQL技巧分享 Q & A
  • MySQL 优化 —— IS NULL 优化

    千次阅读 2020-01-23 13:12:44
    MySQL 对 IS NULL 的优化 MySQL 可以对 IS NULL 执行和常量等值判断(列名= 常量表达式,如name = 'Tom')相同的优化MySQL 可以利用索引和范围来搜索空值。 例如: SELECT * FROM tbl_name WHERE key_co...
  • mysql查询优化器

    千次阅读 2019-03-15 00:55:44
    优化器分类 传统关系型数据库里面的优化器分为CBO和RBO两种。 RBO— Rule_Based Potimizer 基于规则的优化器: RBO :RBO所用的判断规则是一组内置的规则,这些规则是硬编码在数据库的编码中的,RBO会根据这些规则...
  • MySQL优化面试准备

    万次阅读 多人点赞 2018-10-07 20:18:31
    想了很久要不要发一篇关于MySql优化的文章最后还是决定把它写出来。以输出倒逼自己复习与输入。以下内容大都参考了《高性能MySQL》一书也好把一些的章节做一个总结。全文的聊到的三个主要能容是: MySql的特点与...
  • MySQL索引优化

    千次阅读 2021-02-19 09:42:44
    将会从MySQL索引基础、索引优化实战和数据库索引背后的数据结构三部分相关内容。 一、MySQL索引基础 首先,我们将从索引基础开始介绍一下什么是索引,分析索引的几种类型,并探讨一下如何创建索引以及索引设计的...
  • MySQL查询优化之查询优化器

    千次阅读 2009-08-20 16:45:00
    MySQL查询优化之查询优化器phpma.com  当你提交一个查询的时候,MySQL会分析它,看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL采用的优化手段,可以查看...
  • MySql基础知识总结(SQL优化篇)

    万次阅读 多人点赞 2021-07-10 12:26:26
    2、使用主键查询 3、使用联合索引查询 4、联合索引,但与索引顺序不一致 备注:因为mysql优化器的缘故,与索引顺序不一致,也会触发索引,但实际项目中尽量顺序一致。 5、联合索引,但其中一个条件是 > 6、联合索引...
  • mysql数据库优化方案

    2018-04-16 17:09:34
    简单描述数据库优化方案,以及数据库一些常用的操作,包括一些简单的查询语句,函数使用等等
  • mysql 性能优化

    2011-04-11 22:28:41
    mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化...
  • mysql like优化_超级优化 清木桥

    千次阅读 2021-02-05 11:10:36
    MySQL也无法进行优化了,只能mysql中like模糊查询优化 2016年01月28日 18:36:27 createment 阅读数:7691 这是我在一个百万级数据库遇到网上很多优化like的方法,无非下面几种,抄来抄去的。每条数据50个字段左右(用...
  • MySQL 性能优化

    千次阅读 2021-12-06 23:39:30
    对架构的优化mysql的性能优化收益最高。本文主要讨论架构优化。 架构优化  在系统设计时首先需要充分考虑业务的实际情况,例如排行榜的相关业务可以考虑迁移到redis中去做, 地理位置相关的需求放到mongodb中去...
  • 数据库优化——慢查询MySQL定位优化流程

    万次阅读 多人点赞 2021-05-19 11:07:25
    如何定位并优化慢查询SQL?如何使用慢查询日志?本文带来慢查询例子演示,新手都能看懂!那查询语句慢怎么办?explain带你分析sql...当主键索引、唯一索引、普通索引都存在,查询优化器如何选择?本文带你一探究竟!

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 472,670
精华内容 189,068
关键字:

mysql 优化器

mysql 订阅