mysql查询优化_mysql 索引优化 查询 优化 储存优化 - CSDN
  • MySQL——SQL查询优化

    2019-03-10 11:35:21
    对于后台开发而言,涉及的知识面广,且如果要想进大厂的话还要求钻的深,而对于数据库,事务、存储引擎、索引、SQL优化等都是必备的技能,所以本篇博客就开启后序述说MySQL数据库的这些知识点的篇章,本篇将主要说...

    SQL优化总说

    对于后台开发而言,涉及的知识面广,且如果要想进大厂的话还要求钻的深,而对于数据库,事务、存储引擎、索引、SQL优化等都是必备的技能,所以本篇博客就开启后序述说MySQL数据库的这些知识点的篇章,本篇将主要说SQL语句的优化、和解释explain、以及涉及的fileSort的两种算法的解析等。
    对于SQL的优化总体思路如下:
    1、观察,通常是让SQL跑一段时间
    2、开启慢查询日志,设置慢查询标准,抓取慢SQL
    3、对慢SQL采用explain+SQL分析其在数据库内部的执行计划
    4、show profile查看更详细的SQL在服务器内部的生命周期以及执行细节
    5、调整MySQL服务器的参数

    所以接下来就来说下explain

    explain细说

    1)用法:就是explain+SQL语句即可
    在这里插入图片描述
    2)字段解析(重点)

    id:表示加载表或者说对表操作的顺序,有三种情况
    	1、id都一致,那么各表的加载顺序就按执行计划给出的表顺序从上到小依次加载
    	2、id都不一致,那么id大的先被加载
    	3、id有相同的也有不同的,那么相同的就按从上到下的顺序依次执行,而id不同的id大的优先被加载
    select_type:表示这次查询操作在这张表上的类型
    	1、SIMPLE:简单的select查询,查询中不包含子查询或者UNION
      	2、PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY(最后加载的那一个 )
    	3、SUBQUERY:在SELECT或WHERE列表中包含了子查询
    	4、DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生)Mysql会递归执行这些子查询,把结果放在临时表里。
     	5、UNION:若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM字句的查询中,外层				   SELECT将被标记为:DERIVED
    	6、UNION  RESULT:从UNION表获取结果的SELECT
    table:即表示MySQL给出的执行计划的每一行的信息所属的表
    type:表示查询的类型
    	1、System:表只有一行记录,这是const类型的特例,平时不会出现(忽略不计)
    	2、const:表示通过索引一次就找到了,const用于比较primary key或者unique索引,因为只匹配一行数据,所以很快。如将主键置于where列表中,MySQL就能将该查询转换为一个常量。
    	3、eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
    	4、ref:非唯一索引扫描,返回匹配某个单独值的行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而它可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体
    	5、range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引,一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。
    	6、index:FULL INDEX SCAN,index与all区别为index类型只遍历索引树。这通常比all快,因为索引文件通常比数据文件小。
    possible keys:即表示在这张表上的查询可能用到的索引
    key:即实际使用到的索引
    key_len:表示索引中使用的字节数,可通过该列计算查询中索引的长度,在不损失精确性的情况下,长度越短越好,key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。
    ref:显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引上的值。
    rows:根据表统计信息及索引选用情况,大致估算出找到所需记录所需要读取的行数
    extra(重要)
    	1、Using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,MYSQL中无法利用索引完成的排序操作称为“文件排序”
    	2、Using temporary:使用了临时表保存中间结果,MYSQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by 
    	3、Using index:表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率不错。如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找操作。
    	4、Using Where:表示索引用于查找数据而非读取数据
    

    Where查询字段优化(索引失效)问题

    知道了explain分析的各个字段的含义那么我们接下来就要看下索引在where的优化及怎么避免索引失效:

    Where语句后的索引字段
    1、最左前缀原则
    2、不要在索引列上计算、用函数、类型转换
    3、索引列是字符的话要用单引号
    4、in、>、 < 、between、等范围查询那么范围后的索引不会命中,且用于范围查询的那个字段也只是用到了索引排序的功能
    5、!=、<>用在索引字段上索引不起作用
    6、like kk% k%kk%这种那么该字段可以用到索引并且它后面的索引字段也都能用到
    7、like %kk、%kk%这种那么该字段只会用到索引的排序并且它后面的索引字段也不会命中
    

    OrderBy字段优化

    orderby语句后的索引字段
    1、orderby的排序字段没有在where语句中出现过,那么如果此时orderby之后的字段不匹配左前缀原则就会出现filesort
    2、orderby的排序字段出现在了where语句中,但是是作为A>B这种类型的范围,那么此时orderby后的排序字段如果不匹配最左前缀原则也会出现filesort
    3、orderby的排序的字段出现在了where语句中,且是作为常量A=B这种类型,那么此时orderby后的排序可以不单独严格遵守最左前缀原则,而是和前面的字段一起遵守最左前缀原则(此时有两种情况:where A=''
    orderby B,C;where A=''orderby B,A因为这里A是常量所以等效于orderby B,2)
    4、如果排序字段显示采用了DESC、ASC并且不一致的话那么orderby后的字段索引也会失效
    总结:
    1、orderby排序的字段没在where中出现过那么orderby和where前的字段完全无关系
    2、orderby排序的字段在where中出现过但是不是常量形式,那么此时orderby后的字段也和where后的字段无关
    3、orderby排序的字段在where中出现过是常量形式,那么此时orderby后的字段也和where后的字段有关系(可以配合满足最左前缀)
    

    总结:可能你在看到这里了还对上面where和orderby的优化规则很懵,那么建议你就去实践下看。。

    fileSort两种排序算法

    说下当orderby索引失效后出现filesort的排序算法底层的两种算法:双路算法、单路算法
    1、双路算法
    核心思想:第一次IO,读取行指针和排序的字段在内存中排好序后,第二次IO利用排好序的行指针去读取真的数据,所以会发生两次IO,故效率可能会比较低。

    2、单路算法
    就是对双路的改进,核心思想:一次IO解决,一次就把需要的字段数据读入内存排好序后再输出,不用再去读表了;但它有一个瓶颈,就是如果服务器端的参数sort_buffer_size的值没设置好或者说要读取的数据大于了sort_buffer_size的值那么,单路会在第一次只能读取sort_buffer_size这么大的数据,产生临时表,之后还会去磁盘IO,直到能成功解决,所以这里可以看出,单路算法可能会产生多次IO

    3、说下sort_buffer_size、max_length_for_sort_data这两个参数
    sort_buffer_size:设置太小,可能会造成单路和双路产生原本设定的IO次数;设置太多,可能造成查询的数据超过了max_length_for_sort_data的值,就会采用双路
    max_length_for_sort_data:即当select后查询的字段容量大于这个值设定的容量时,这时会采用双路算法而不是单路,该值设定的太大,可能会造成单路IO产生多次IO,即要读取的数据量超过了sort_buffer_size;设定太小,就会增加采用双路算法的概率

    慢查询日志

    慢查询日志是记录在会话中SQL查询消耗时间超过设定的阙值的SQL语句日志,可以利用它来捕捉慢查询的SQL语句,精准的优化SQL
    1)如何查看是否开启和开启该功能

    1、show variables like '%slow_query_log'; 默认是关闭的
    2、set global slow_query_log = 1;临时开启,如果永久生效就配置在配置文件中(一般不允许)
    

    2)查看设定的阙值
    在这里插入图片描述

    1、show variables like 'long_query_time';默认为10秒
    2、set global long_query_time=3;设定阙值
    

    3)到指定的慢查询日志去查看慢查询里的慢查询SQL语句
    在这里插入图片描述
    在这里select sleep(4)就是慢查询的SQL语句,之后我们可以利用explain+SQL分析来分析它慢的原因以此来优化它

    4)生产中慢查询日志中的记录会很多,这时人工捕捉很费时,就利用MySQL的mysqldumpslow工具来导出优化人员着重关注的慢SQL语句

    show profiles

    show profiles是一种更细粒度的SQL语句优化,利用它可以记录最近一段时间内SQL语句在服务端执行的整个生命周期内消耗的资源和SQL语句每个生命期间所消耗的时间,这对DBA优化SQL很有帮助
    1)查看show profiles功能是否开启

    show variables like 'profiling';默认没有开启
    

    2)

    set  profiling = on开启
    

    3)执行SQL

    4)查看show profiles;记录最近一段时间内的SQL语句执行情况
    在这里插入图片描述
    在这里显示了SQL语句所消耗的时间,如果要看某条SQL的消耗资源的具体情况如下:

    show profile  cpu,block io for query id(id是某条SQL的id)
    

    在这里插入图片描述
    这里这张图就显示了SQL语句的整个生命周期和所消耗的资源情况,其中我们可以查看SQL的cpu、block io、memory、context switches(上下文切换)等资源的消耗

    对于表中出现的如下几个字段要特别注意:

    Converting heap to disk查询的数据太多直接存到磁盘上
    Creat temp table创建临时表
    Copy to temp table on disk复制临时表内容到磁盘上
    locked锁
    
    展开全文
  • MySQL数据库优化的八种方式 1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更好的性能,我...

    查询速度慢的原因

    从程序员的角度

    1. 查询语句写的不好
    2. 没建索引,索引建的不合理或索引失效
    3. 关联查询有太多的join

    从服务器的角度

    • 服务器磁盘空间不足
    • 服务器调优配置参数设置不合理

    MySQL数据库优化的八种方式

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

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

    例如,在定义邮政编码这个字段时,如果将其设置为CHAR(255),显然给数据库增加了不必要的空间,甚至使用VARCHAR这种类型也是多余的,因为CHAR(6)就可以很好的完成任务了。同样的,如果可以的话,我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

    另外一个提高效率的方法是在可能的情况下,应该尽量把字段设置为NOTNULL,这样在将来执行查询的时候,数据库不用去比较NULL值。
    对于某些文本字段,例如“省份”或者“性别”,我们可以将它们定义为ENUM类型。因为在MySQL中,ENUM类型被当作数值型数据来处理,而数值型数据被处理起来的速度要比文本类型快得多。这样,我们又可以提高数据库的性能。

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

    MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。例如,我们要将客户基本信息表中没有任何订单的客户删除掉,就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来,然后将结果传递给主查询,如下所示:

    DELETE FROM customerinfo WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo)

    使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)..替代。例如,假设我们要将所有没有订单记录的用户取出来,可以用下面这个查询完成:

    SELECT * FROM customerinfo WHERE CustomerID NOT IN (SELECTC ustomerID FROM salesinfo)

    如果使用连接(JOIN)..来完成这个查询工作,速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话,性能将会更好,查询如下:

    SELECT * FROM customerinfo LEFT JOIN salesinfo 
    ON customerinfo.CustomerID=salesinfo.CustomerID 
    WHERE salesinfo.CustomerID ISNULL

    连接(JOIN)..之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

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

    MySQL从4.0的版本开始支持union查询,它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效。使用union来创建查询的时候,我们只需要用UNION作为关键字把多个select语句连接起来就可以了,要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。

    SELECT Name,Phone FROM client UNION
    
    SELECT Name,BirthDate FROM author UNION
    
    SELECT Name,Supplier FROM product

    4、事务

    尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询,但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下,当这个语句块中的某一条语句运行出错的时候,整个语句块的操作就会变得不确定起来。设想一下,要把某个数据同时插入两个相关联的表中,可能会出现这样的情况:第一个表中成功更新后,数据库突然出现意外状况,造成第二个表中的操作没有完成,这样,就会造成数据的不完整,甚至会破坏数据库中的数据。要避免这种情况,就应该使用事务,它的作用是:要么语句块中每条语句都操作成功,要么都失败。换句话说,就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始,COMMIT关键字结束。在这之间的一条SQL操作失败,那么,ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

    BEGIN; INSERT INTO salesinfo SET CustomerID=14; 
    UPDATE inventory SET Quantity=11 WHERE item='book'; COMMIT;

    事务的另一个重要作用是当多个用户同时使用相同的数据源时,它可以利用锁定数据库的方法来为用户提供一种安全的访问方式,这样可以保证用户的操作不被其它的用户所干扰。

    5、锁定表

    尽管事务是维护数据库完整性的一个非常好的方法,但却因为它的独占性,有时会影响数据库的性能,尤其是在很大的应用系统中。由于在事务执行的过程中,数据库将会被锁定,因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用,事务造成的影响不会成为一个太大的问题;但假设有成千上万的用户同时访问一个数据库系统,例如访问一个电子商务网站,就会产生比较严重的响应延迟。

    其实,有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

    LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';
    
    ...
    
    UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES

    这里,我们用一个select语句取出初始数据,通过一些计算,用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前,不会有其它的访问来对inventory进行插入、更新或者删除的操作。

    6、使用外键

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

    例如,外键可以保证每一条销售记录都指向某一个存在的客户。在这里,外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID,任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。

    CREATE  TABLE  customerinfo(CustomerIDINT  NOT    NULL,PRIMARYKEY(CustomerID))TYPE=INNODB;
    
    CREATE  TABLE  salesinfo(SalesIDNT  NOT  NULL,CustomerIDINT  NOT  NULL,
    
    PRIMARYKEY(CustomerID,SalesID),
    
    FOREIGNKEY(CustomerID)  REFERENCES  customerinfo(CustomerID)  ON  DELETE    CASCADE)TYPE=INNODB;

    注意例子中的参数“ON DELETE CASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候,salesinfo表中所有与该客户相关的记录也会被自动删除。

    7、使用索引

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

    那该对哪些字段建立索引呢?

    一般说来,索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说,出现大量重复值是很有可能的情况

    例如customerinfo中的“province”..字段,在这样的字段上建立索引将不会有什么帮助;相反,还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引,也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外,MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引,但仅能用于MyISAM类型的表。对于一个大的数据库,将数据装载到一个没有FULLTEXT索引的表中,然后再使用ALTERTABLE或CREATEINDEX创建索引,将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中,执行过程将会非常慢。

    8、优化的查询语句

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

     

    28种 SQL 查询语句的优化方法:

    1、应尽量避免在 where 子句中使用 != 或者 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。

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

    select id from t where num is null;

    可以在 num 上设置默认值 0 ,确保表中 num 列没有 null 值,然后这样查询:

    
    select id from t where num = 0;

    3、查询语句的查询条件中只有OR关键字,并且OR前后的两个条件中的列都是索引时,查询中才使用索引。否则,查询将不使用索引。

    4、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

    5、前导模糊查询不能利用索引(like '%XX'或者like '%XX%'),可以使用索引覆盖避免。

    6、in 和 not in 也要慎用,否则会导致全表扫描。如:

    select id from t where num in(1,2,3) 

    对于连续的数值,能用 between 就不要用 in 了: 

    select id from t where num between 1 and 3 

    7、如果在 where 子句中使用参数,也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择到运行时;它必须在编译时进行选择。然而,如果在编译时简历访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描: 

    select id from t where num=@num

    可以改为强制查询使用索引: 

    select id from t with(index(索引名)) where num=@num 

    8、应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

    select id from t where num/2 = 100;

    应改为:

    select id from t where num = 100 * 2;

    9、应尽量避免在 where 子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

    select id from t where substring(name, 1, 3) = ’abc’–name;  //以abc开头的id
     
    select id from t where datediff(day,createdate,’2005-11-30′) = 0–’2005-11-30′;  //生成的id

    应改为:

    select id from t where name like ‘abc%’
     
    select id from t where createdate >= ’2005-11-30′ and createdate < ’2005-12-1′;

    10、不要在 where 子句中的 “=” 左边进行函数,算术运算或者其他表达式运算,否则系统将可能无法正确使用索引。 

    11、在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

    12、很多时候用 exists 代替 in 是一个好的选择: 

    select num from a where num in(select num from b);

    用下面的语句替换: 

    select num from a where exists(select 1 from b where num=a.num);

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

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

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

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

    17、任何地方都不要使用 select * from t ,用具体的字段列表代替 *,不要返回用不到的任何字段。

    18、尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

    19、避免频繁创建和删除临时表,以减少系统表资源的消耗。

    20、临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

    21、在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先 create table,然后 insert。

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

    23、尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

    24、使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

    25、与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

    26、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

    27、尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

    28、尽量避免大事务操作,提高系统并发能力。

    展开全文
  • 常用查询优化 1: max()优化: 在相应列上添加索引 2: count()优化:count(*) 会算出包含null记录的数量, count(field_name)只包含不含 null的数量(这也是很多时候两种count方式结果不一致的原因), count()的...

    常用查询优化

    1: max()优化: 在相应列上添加索引
    2: count()优化:count(*) 会算出包含null记录的数量, count(field_name)只包含不含 null的数量(这也是很多时候两种count方式结果不一致的原因), count()的时候尽量用后一种, count(null)返回0,即不会记录null记录数量
    3: 子查询优化=====》(改为)联接查询(如果1对多的关系,注意重复记录)
    4: group by优化 如果包含子查询,在子查询里面使用where条件和group by过滤, 避免在复杂查询的最外层使用group by(如果最外层使用会用到临时表)
    5: order by , limit 优化:

    方式1:尽量使用主键或有索引的列order by;
    方式2: 使用自增型的字段: 记录上一次返回的主键或者自增列(此种方式该字段不能有空值,否则会出现有的页面数量不足的问题, 解决的方式是添加附加的index_id, 自增且索引), 过滤时先用大于上一次主键值且小于上一次的主键值+每页的数量, 过滤该字段,然后order by 和limit
    PS: 复合索引有效条件:

    1: where 条件中依次过滤(最左前缀)
    2:排序时: 索引字段有正有反的时候不能使用
    3:排序时: 某列有范围查询的时候该列右侧的字段不能使用索引
    优化的思路就是尽量避免扫描过多的记录。

    创建索引的原则:

    1: where, order by ,group by, on从句中的字段
    2:索引字段越小越好
    3:联合索引时把离散程度高的字段放前面

    表级优化

    1: 表的范式优化
    2: 适当增减一些冗余, 做反范式优化(以空间换取时间)
    3: 表的列非常多的时候使用垂直拆分
    原则:
    1: 把不常用的单独字段放到一个表中
    2: 把大字段独立存放到一个表中
    3: 把经常一起用的字段放在一起
    4: 表的数据量非常大的时候使用水平拆分
    方法:
    1: 根据某个字段进行hash预算, 如果要拆分成5个表, 用取余的方式取到0-4,分表保到相应的表中
    2: 针对不同的hashID把数据存到不同的表中
    问题:
    1: 跨分区查询的问题
    2: 统计及后台报表操作

    (前台使用分表查询, 后台使用汇总表查询做汇总报表操作).


    一、 通过查询缓冲提高查询速度

      一般我们使用SQL语句进行查询时,数据库服务器每次在收到客户端发来SQL后,都会执行这条SQL语句。但当在一定间隔内(如1分钟内),接到完全一样的SQL语句,也同样执行它。虽然这样可以保证数据的实时性,但在大多数时候,数据并不要求完全的实时,也就是说可以有一定的延时。如果是这样的话,在短时间内执行完全一样的SQL就有些得不偿失。
    幸好MySQL为我们提供了查询缓冲的功能(只能在MySQL 4.0.1及以上版本使用查询缓冲)。我们可以通过查询缓冲在一定程度上提高查询性能。

    1、我们可以通过在MySQL安装目录中的my.ini文件设置查询缓冲:

      设置也非常简单,只需要将query_cache_type设为1即可。在设置了这个属性后,MySQL在执行任何SELECT语句之前,都会在它的缓冲区中查询是否在相同的SELECT语句被执行过,如果有,并且执行结果没有过期,那么就直接取查询结果返回给客户端。但在写SQL语句时注意,MySQL的查询缓冲是区分大小写的。如下列的两条SELECT语句:

    SELECT * FROM TABLE1
    SELECT * FROM TABLE1 
    上面的两条SQL语句对于查询缓冲是完全不同的SELECT。而且查询缓冲并不自动处理空格,因此,在写SQL语句时,应尽量减少空格的使用,尤其是在SQL首和尾的空格(因为,查询缓冲并不自动截取首尾空格)。

    2、临时关闭查询缓冲方法:

      虽然不设置查询缓冲,有时可能带来性能上的损失,但有一些SQL语句需要实时地查询数据,或者并不经常使用(可能一天就执行一两次)。这样就需要把缓冲关了。当然,这可以通过设置query_cache_type的值来关闭查询缓冲,但这就将查询缓冲永久地关闭了。
    在MySQL 5.0中提供了一种可以临时关闭查询缓冲的方法:SQL_NO_CACHE。

    SELECT SQL_NO_CACHE field1, field2 FROM TABLE1 
    以上的SQL语句由于使用了SQL_NO_CACHE,因此,不管这条SQL语句是否被执行过,服务器都不会在缓冲区中查找,每次都会执行它。

    3、临时开启查询缓冲方法:

      我们还可以将my.ini中的query_cache_type设成2,这样只有在使用了SQL_CACHE后,才使用查询缓冲。

    SELECT SQL_CALHE * FROM TABLE1 
     

    二、MySQL对查询的自动优化

      索引对于数据库是非常重要的。在查询时可以通过索引来提高性能。但有时使用索引反而会降低性能。我们可以看如下的SALES表:

    CREATETABLE SALES
    (
       ID INT(10) UNSIGNED NOTNULL AUTO_INCREMENT,
       NAME VARCHAR(100) NOTNULL,
       PRICE FLOATNOTNULL,
       SALE_COUNT INTNOTNULL,
       SALE_DATE DATE NOTNULL,
    PRIMARYKEY(ID),
    INDEX (NAME),
      INDEX (SALE_DATE)
    )
    假设这个表中保存了数百万条数据,而我们要查询商品号为1000的商品在2004年和2005年的平均价格。我们可以写如下的SQL语句:

    SELECT AVG(PRICE) FROM SALES
    WHERE ID=1000 AND SALE_DATE BETWEEN '2004-01-01' AND '2005-12-31';
    如果这种商品的数量非常多,差不多占了SALES表的记录的50%或更多。那么使用SALE_DATE字段上索引来计算平均数就有些慢。因为如果使用索引,就得对索引进行排序操作。当满足条件的记录非常多时(如占整个表的记录的50%或更多的比例),速度会变慢,这样还不如对整个表进行扫描。因此,MySQL会自动根据满足条件的数据占整个表的数据的比例自动决定是否使用索引进行查询。

        对于MySQL来说,上述的查询结果占整个表的记录的比例是30%左右时就不使用索引了,这个比例是MySQL的开发人员根据他们的经验得出的。然而,实际的比例值会根据所使用的数据库引擎不同而不同。

     

    三、 基于索引的排序

      MySQL的弱点之一是它的排序。虽然MySQL可以在1秒中查询大约15,000条记录,但由于MySQL在查询时最多只能使用一个索引。因此,如果WHERE条件已经占用了索引,那么在排序中就不使用索引了,这将大大降低查询的速度。我们可以看看如下的SQL语句:

    SELECT*FROM SALES WHERE NAME = 'name' ORDERBY SALE_DATE DESC;
        在以上的SQL的WHERE子句中已经使用了NAME字段上的索引,因此,在对SALE_DATE进行排序时将不再使用索引。为了解决这个问题,我们可以对SALES表建立复合索引:

    ALTERTABLE SALES DROPINDEX NAME, ADDINDEX (NAME, SALE_DATE) 
        这样再使用上述的SELECT语句进行查询时速度就会大副提升。但要注意,在使用这个方法时,要确保WHERE子句中没有排序字段,在上例中就是不能用SALE_DATE进行查询,否则虽然排序快了,但是SALE_DATE字段上没有单独的索引,因此查询又会慢下来。

    SELECT*FROM SALES WHERE NAME = 'name1' AND NAME = 'name2'
        以上的查询语句要查找NAME既等于name1又等于name2的记录。很明显,这是一个不可达的查询,WHERE条件一定是假。MySQL在执行SQL 语句之前,会先分析WHERE条件是否是不可达的查询,如果是,就不再执行这条SQL语句了。为了验证这一点。我们首先对如下的SQL使用EXPLAIN 进行测试:

    EXPLAIN SELECT*FROM SALES WHERE NAME = ’name1'
        上面的查询是一个正常的查询,我们可以看到使用EXPLAIN返回的执行信息数据中table项是SALES。这说明MySQL对SALES进行操作了。再看看下面的语句:

    EXPLAIN SELECT*FROM SALES WHERE NAME = ’name1' AND NAME = 'name2'
       我们可以看到,table项是空,这说明MySQL并没有对SALES表进行操作。

     

    四、 使用各种查询选择来提高性能

      SELECT语句除了正常的使用外,MySQL还为我们提供了很多可以增强查询性能的选项。如上面介绍的用于控制查询缓冲的SQL_NO_CACHE和SQL_CACHE就是其中两个选项。在这一部分,我将介绍几个常用的查询选项。

    1、STRAIGHT_JOIN:强制连接顺序

    当我们将两个或多个表连接起来进行查询时,我们并不用关心MySQL先连哪个表,后连哪个表。而这一切都是由MySQL内部通过一系列的计算、评估,最后得出的一个连接顺序决定的。如下列的SQL语句中,TABLE1和TABLE2并不一定是谁连接谁:

    SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 ,TABLE2 WHERE … 
        如果开发人员需要人为地干预连接的顺序,就得使用STRAIGHT_JOIN关键字,如下列的SQL语句:

    SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
    由上面的SQL语句可知,通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话,就可以通过STRAIGHT_JOIN来确定连接顺序。

    2、干预索引使用,提高性能

      在上面已经提到了索引的使用。一般情况下,在查询时MySQL将自己决定是否使用索引,使用哪一个索引。
    但在一些特殊情况下,我们希望MySQL只使用一个或几个索引,或者不希望使用某个索引。这就需要使用MySQL的控制索引的一些查询选项。

    (1)限制使用索引的范围:

      有时我们在数据表里建立了很多索引,当MySQL对索引进行选择时,这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引,而不是全部的索引,这就需要用到USE INDEX对查询语句进行设置。

    SELECT*FROM TABLE1 USEINDEX (FIELD1, FIELD2) …
    从以上SQL语句可以看出,无论在TABLE1中已经建立了多少个索引,MySQL在选择索引时,只考虑在FIELD1和FIELD2上建立的索引。

    (2)限制不使用索引的范围:

      如果我们要考虑的索引很多,而不被使用的索引又很少时,可以使用IGNORE INDEX进行反向选取。在上面的例子中是选择被考虑的索引,而使用IGNORE INDEX是选择不被考虑的索引。

    SELECT*FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …
        在上面的SQL语句中,TABLE1表中只有FIELD1和FIELD2上的索引不被使用。

    (3)强迫使用某一个索引:

      上面的两个例子都是给MySQL提供一个选择,也就是说MySQL并不一定要使用这些索引。而有时我们希望MySQL必须要使用某一个索引(由于MySQL在查询时只能使用一个索引,因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。

    SELECT*FROM TABLE1 FORCE INDEX (FIELD1) …
    以上的SQL语句只使用建立在FIELD1上的索引,而不使用其它字段上的索引。


    3. 使用临时表提供查询性能

      当我们查询的结果集中的数据比较多时,可以通过SQL_BUFFER_RESULT选项强制将结果集放到临时表中,这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了),并且可以长时间地为客户端提供大记录集。

      SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …
        和SQL_BUFFER_RESULT选项类似的还有SQL_BIG_RESULT,这个选项一般用于分组或DISTINCT关键字,这个选项通知MySQL,如果有必要,就将查询结果放到临时表中,甚至在临时表中进行排序。

    SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUPBY FIELD1


    五、MYSQL查询优化:使用索引

    MySQL有几种使用索引的方式:

      · 如上所述,索引被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。

      · 对于使用了MIN()或MAX()函数的查询,索引数据列中最小或最大值可以很快地找到,不用检查每个数据行。

      · MySQL利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。

       · 有时候MySQL会利用索引来读取查询得到的所有信息。假设你选择了MyISAM表中的被索引的数值列,那么就不需要从该数据表中选择其它的数据列。在这种情况下,MySQL从索引文件中读取索引值,它所得到的值与读取数据文件得到的值是相同的。没有必要两次读取相同的值,因此没有必要考虑数据文件。

     

    索引创建规则:
    1、表的主键、外键必须有索引;
    2、数据量超过300的表应该有索引;
    3、经常与其他表进行连接的表,在连接字段上应该建立索引;
    4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
    5、索引应该建在选择性高的字段上;
    6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
    7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
    A、正确选择复合索引中的主列字段,一般是选择性较好的字段;
    B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
    C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
    D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
    E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
    8、频繁进行数据操作的表,不要建立太多的索引;
    9、删除无用的索引,避免对执行计划造成负面影响;

        以上是一些普遍的建立索引时的判断依据。一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。
        因为太多的索引与不充分、不正确的索引对性能都毫无益处:在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销。
    另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的;相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大。
     

    查询优化之explain的深入解析

    下面来举一个例子来说明下 explain 的用法。 
    先来一张表:

    CREATE TABLE IF NOT EXISTS `article` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `author_id` int(10) unsigned NOT NULL,
    `category_id` int(10) unsigned NOT NULL,
    `views` int(10) unsigned NOT NULL,
    `comments` int(10) unsigned NOT NULL,
    `title` varbinary(255) NOT NULL,
    `content` text NOT NULL,
    PRIMARY KEY (`id`)
    );

    再插几条数据:

    INSERT INTO `article`
    (`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
    (1, 1, 1, 1, '1', '1'),
    (2, 2, 2, 2, '2', '2'),
    (1, 1, 3, 3, '3', '3');

    需求:
    查询 category_id 为 1 且 comments 大于 1 的情况下,views 最多的 article_id。 
    先查查试试看:

    EXPLAIN
    SELECT author_id
    FROM `article`
    WHERE category_id = 1 AND comments > 1
    ORDER BY views DESC
    LIMIT 1\G

    看看部分输出结果:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: article
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 3
            Extra: Using where; Using filesort
    1 row in set (0.00 sec)

    很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
    嗯,那么最简单的解决方案就是加索引了。好,我们来试一试。查询的条件里即 where 之后共使用了 category_id,comments,views 三个字段。那么来一个联合索引是最简单的了。

    ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `views` );

    结果有了一定好转,但仍然很糟糕:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: article
             type: range
    possible_keys: x
              key: x
          key_len: 8
              ref: NULL
             rows: 1
            Extra: Using where; Using filesort
    1 row in set (0.00 sec)

    type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。但是我们已经建立了索引,为啥没用呢?这是因为按照 BTree 索引的工作原理,先排序 category_id,如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。当 comments 字段在联合索引里处于中间位置时,因comments > 1 条件是一个范围值(所谓 range),MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。
    那么我们需要抛弃 comments,删除旧索引:

     DROP INDEX x ON article;

    然后建立新索引:

    ALTER TABLE `article` ADD INDEX y ( `category_id` , `views` ) ;

    接着再运行查询:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: article
             type: ref
    possible_keys: y
              key: y
          key_len: 4
              ref: const
             rows: 1
            Extra: Using where
    1 row in set (0.00 sec)

    可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。
    再来看一个多表查询的例子。
    首先定义 3个表 class 和 room。

    CREATE TABLE IF NOT EXISTS `class` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `card` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`)
    );
    CREATE TABLE IF NOT EXISTS `book` (
    `bookid` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `card` int(10) unsigned NOT NULL,
    PRIMARY KEY (`bookid`)
    );
    CREATE TABLE IF NOT EXISTS `phone` (
    `phoneid` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `card` int(10) unsigned NOT NULL,
    PRIMARY KEY (`phoneid`)
    ) engine = innodb;

    然后再分别插入大量数据。插入数据的php脚本:

    <?php
    $link = mysql_connect("localhost","root","870516");
    mysql_select_db("test",$link);
    for($i=0;$i<10000;$i++)
    {
        $j   = rand(1,20);
        $sql = " insert into class(card) values({$j})";
        mysql_query($sql);
    }
    for($i=0;$i<10000;$i++)
    {
        $j   = rand(1,20);
        $sql = " insert into book(card) values({$j})";
        mysql_query($sql);
    }
    for($i=0;$i<10000;$i++)
    {
        $j   = rand(1,20);
        $sql = " insert into phone(card) values({$j})";
        mysql_query($sql);
    }
    mysql_query("COMMIT");
    ?>

    然后来看一个左连接查询:

    explain select * from class left join book on class.card = book.card\G

    分析结果是:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra: 
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra: 
    2 rows in set (0.00 sec)

    显然第二个 ALL 是需要我们进行优化的。
    建立个索引试试看:

    ALTER TABLE `book` ADD INDEX y ( `card`);

     

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra: 
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ref
    possible_keys: y
              key: y
          key_len: 4
              ref: test.class.card
             rows: 1000
            Extra: 
    2 rows in set (0.00 sec)

    可以看到第二行的 type 变为了 ref,rows 也变成了 1741*18,优化比较明显。这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引。
    删除旧索引:

    DROP INDEX y ON book;

    建立新索引。

    ALTER TABLE `class` ADD INDEX x ( `card`);

    结果

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra: 
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra: 
    2 rows in set (0.00 sec)

    基本无变化。
           然后来看一个右连接查询:

    explain select * from class right join book on class.card = book.card;

    分析结果是:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra: 
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ref
    possible_keys: x
              key: x
          key_len: 4
              ref: test.book.card
             rows: 1000
            Extra: 
    2 rows in set (0.00 sec)

    优化较明显。这是因为 RIGHT JOIN 条件用于确定如何从左表搜索行,右边一定都有,所以左边是我们的关键点,一定需要建立索引。
    删除旧索引:

    DROP INDEX x ON class;

    建立新索引。

    ALTER TABLE `book` ADD INDEX y ( `card`);

    结果

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra: 
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra: 
    2 rows in set (0.00 sec)

    基本无变化。
    最后来看看 inner join 的情况:

    explain select * from class inner join book on class.card = book.card;

    结果:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra: 
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ref
    possible_keys: x
              key: x
          key_len: 4
              ref: test.book.card
             rows: 1000
            Extra: 
    2 rows in set (0.00 sec)

    删除旧索引:

    DROP INDEX y ON book;

    结果

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra: 
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra: 
    2 rows in set (0.00 sec)

    建立新索引。

    ALTER TABLE `class` ADD INDEX x ( `card`);

    结果

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra: 
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra: 
    2 rows in set (0.00 sec)

    综上所述,inner join 和 left join 差不多,都需要优化右表。而 right join 需要优化左表。
    我们再来看看三表查询的例子
    添加一个新索引:

    ALTER TABLE `phone` ADD INDEX z ( `card`);
    ALTER TABLE `book` ADD INDEX y ( `card`);

     

    explain select * from class left join book on class.card=book.card left join phone on book.card = phone.card;

     

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra: 
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ref
    possible_keys: y
              key: y
          key_len: 4
              ref: test.class.card
             rows: 1000
            Extra: 
    *************************** 3. row ***************************
               id: 1
      select_type: SIMPLE
            table: phone
             type: ref
    possible_keys: z
              key: z
          key_len: 4
              ref: test.book.card
             rows: 260
            Extra: Using index
    3 rows in set (0.00 sec)

    后 2 行的 type 都是 ref 且总 rows 优化很好,效果不错。

    转载: https://www.cnblogs.com/wanghuaijun/p/6693432.html

    展开全文
  • MySQL查询优化

    2020-03-21 10:19:27
    2.MySQL大多数核心服务均在中间这一层,包括查询解析、分析、优化、缓存、内置函数(比如:时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现:存储过程、触发器、视图等。 3.最下层为存储引擎,其负责...

    MySQL的逻辑架构图

    在这里插入图片描述
    1.客户端层:连接处理、授权认证、安全等功能均在这一层处理

    2.MySQL大多数核心服务均在中间这一层,包括查询解析、分析、优化、缓存、内置函数(比如:时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现:存储过程、触发器、视图等。

    3.最下层为存储引擎,其负责MySQL中的数据存储和提取。和Linux下的文件系统类似,每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信,这些API接口屏蔽了不同存储引擎间的差异。

    MySQL查询过程

    在这里插入图片描述

    1.客户端/服务端通信协议

    MySQL客户端/服务端通信协议是“半双工”的:在任一时刻,要么是服务器向客户端发送数据,要么是客户端向服务器发送数据,这两个动作不能同时发生。一旦一端开始发送消息,另一端要接收完整个消息才能响应它。

    2.查询缓存

    在解析一个查询语句前,如果查询缓存是打开的,那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存,在检查一次用户权限后直接返回缓存中的结果。这种情况下,查询不会被解析,也不会生成执行计划,更不会执行。

    工作流程:

    1. 服务器接收SQL,以SQL和一些其他条件为key查找缓存表(额外性能消耗)

    2. 如果找到了缓存,则直接返回缓存(性能提升)

    3. 如果没有找到缓存,则执行SQL查询,包括原来的SQL解析,优化等.

    4. 执行完SQL查询结果以后,将SQL查询结果存入缓存表(额外性能消耗)

    表数据修改不频繁、数据较静态。
    查询(Select)重复度高。
    查询结果集小于 1 MB。

    查询缓存参考:https://blog.csdn.net/Lei_Da_Gou/article/details/90030371

    3.语法解析和预处理

    MySQL通过关键字将SQL语句进行解析,并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

    4.查询优化

    多数情况下,一条查询可以有很多种执行方式,最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

    5.查询执行引擎

    在完成解析和优化阶段以后,MySQL会生成对应的执行计划,查询执行引擎根据执行计划给出的指令逐步执行得出结果,整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成。

    6.返回结果给客户端

    查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据,MySQL仍然会返回这个查询的相关信息,比如该查询影响到的行数以及执行时间等等。
    如果查询缓存被打开且这个查询可以被缓存,MySQL也会将结果存放到缓存中。

    总结:

    1. 客户端向MySQL服务器发送一条查询请求
    2. 服务器首先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段
    3. 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
    4. MySQL根据执行计划,调用存储引擎的API来执行查询
    5. 将结果返回给客户端,同时缓存查询结果

    性能优化建议

    1.Scheme设计与数据类型优化

    1. 选择数据类型只要遵循小而简单的原则就好,越小的数据类型通常会更快,占用更少的磁盘、内存,处理时需要的CPU周期也更少。
    2. 对整数类型指定宽度,比如INT(11),没有任何卵用。INT使用32位(4个字节)存储空间,那么它的表示范围已经确定,所以INT(1)和INT(20)对于存储和计算是相同的。
    3. 尽量避免NULL。如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理。
    4. schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列,这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话,有可能会导致CPU占用过高。
    5. 大表ALTER TABLE非常耗时,MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表,从旧表中查出所有的数据插入新表,然后再删除旧表。尤其当内存不足而表又很大,而且还有很大索引的情况下,耗时更久。

    2.创建高性能索引

    索引是提高MySQL查询性能的一个重要途径,但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用,从而影响应用程序的整体性能。

    3.特定类型查询优化,优化COUNT()查询

    COUNT()函数,它有两种不同的作用,其一是统计某个列值的数量,其二是统计行数。统计列值时,要求列值是非空的,它不会统计NULL。如果确认括号中的表达式不可能为空时,实际上就是在统计行数。最简单的就是当使用COUNT(*)时,并不是我们所想象的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计行数。

    4.优化关联查询

    MySQL是如何执行关联查询的。当前MySQL关联执行的策略非常简单,它对任何的关联都执行嵌套循环关联操作,即先在一个表中循环取出单条数据,然后在嵌套循环到下一个表中寻找匹配的行,依次下去,直到找到所有表中匹配的行为为止。然后根据各个表匹配的行,返回查询中需要的各个列。

    5.优化UNION

    MySQL处理UNION的策略是先创建临时表,然后再把各个查询结果插入到临时表中,最后再来做查询。

    除非确实需要服务器去重,否则就一定要使用UNION ALL,如果没有ALL关键字,MySQL会给临时表加上DISTINCT选项,这会导致整个临时表的数据做唯一性检查,这样做的代价非常高。当然即使使用ALL关键字,MySQL总是将结果放入临时表,然后再读出,再返回给客户端。虽然很多时候没有这个必要,比如有时候可以直接把每个子查询的结果返回给客户端。

    Django ORM 查询优化

    1.select_related(基于连表查询)

    对于一对一字段(OneToOneField)和外键字段(ForeignKey),可以使用select_related 来对QuerySet进行优化。
    在对QuerySet使用select_related()函数后,Django会获取相应外键对应的对象,从而在之后需要的时候不必再查询数据库了。

    例子

    # Hits the database.
    article = models.Article.objects.get(nid=2)
    # Hits the database again to get the related Blog object.
    print(article.category.title)
    

    对应SQL:

    SELECT
        "blog_article"."nid",
        "blog_article"."title",
        "blog_article"."desc",
        "blog_article"."read_count",
        "blog_article"."comment_count",
        "blog_article"."up_count",
        "blog_article"."down_count",
        "blog_article"."category_id",
        "blog_article"."create_time",
         "blog_article"."blog_id",
         "blog_article"."article_type_id"
                 FROM "blog_article"
                 WHERE "blog_article"."nid" = 2; args=(2,)
     
    SELECT
         "blog_category"."nid",
         "blog_category"."title",
         "blog_category"."blog_id"
                  FROM "blog_category"
                  WHERE "blog_category"."nid" = 4; args=(4,)
    

    使用select_related()函数:

    articleList=models.Article.objects.select_related("category").all()
        for article_obj in articleList:
            #  Doesn't hit the database, because article_obj.category
            #  has been prepopulated in the previous query.
            print(article_obj.category.title)
    

    对应SQL:

    SELECT
         "blog_article"."nid",
         "blog_article"."title",
         "blog_article"."desc",
         "blog_article"."read_count",
         "blog_article"."comment_count",
         "blog_article"."up_count",
         "blog_article"."down_count",
         "blog_article"."category_id",
         "blog_article"."create_time",
         "blog_article"."blog_id",
         "blog_article"."article_type_id",
     
         "blog_category"."nid",
         "blog_category"."title",
         "blog_category"."blog_id"
     
    FROM "blog_article"
    LEFT OUTER JOIN "blog_category" ON ("blog_article"."category_id" = "blog_category"."nid");
    

    总结:

    1. select_related主要针一对一和多对一关系进行优化。
    2. select_related使用SQL的JOIN语句进行优化,通过减少SQL查询的次数来进行优化、提高性能。
    3. 可以通过可变长参数指定需要select_related的字段名。也可以通过使用双下划线“__”连接字段名来实现指定的递归查询。
    4. 没有指定的字段不会缓存,没有指定的深度不会缓存,如果要访问的话Django会再次进行SQL查询。
    5. 也可以通过depth参数指定递归的深度,Django会自动缓存指定深度内所有的字段。如果要访问指定深度外的字段,Django会再次进行SQL查询。
    6. 也接受无参数的调用,Django会尽可能深的递归查询所有的字段。但注意有Django递归的限制和性能的浪费。

    2.prefetch_related(基于子查询)

    对于多对多字段(ManyToManyField)和一对多字段,可以使用prefetch_related()来进行优化。

    prefetch_related()和select_related()的设计目的很相似,都是为了减少SQL查询的数量,但是实现的方式不一样。后者是通过JOIN语句,在SQL查询内解决问题。但是对于多对多关系,使用SQL语句解决就显得有些不太明智,因为JOIN得到的表将会很长,会导致SQL语句运行时间的增加和内存占用的增加。若有n个对象,每个对象的多对多字段对应Mi条,就会生成Σ(n)Mi 行的结果表。

    prefetch_related()的解决方法是,分别查询每个表,然后用Python处理他们之间的关系。

    3.extra

    有些情况下,Django的查询语法难以简单的表达复杂的 WHERE 子句,对于这种情况, Django 提供了 extra() QuerySet修改机制 — 它能在 QuerySet生成的SQL从句中注入新子句

    extra(select=None, where=None, params=None, 
          tables=None, order_by=None, select_params=None)
    

    extra可以指定一个或多个 参数,例如 select, where or tables. 这些参数都不是必须的,但是你至少要使用一个!要注意这些额外的方式对不同的数据库引擎可能存在移植性问题.(因为你在显式的书写SQL语句),除非万不得已,尽量避免这样做

    select 参数可以让你在 SELECT 从句中添加其他字段信息,它应该是一个字典,存放着属性名到 SQL 从句的映射。

    queryResult=models.Article.objects.extra(select={'is_recent': "create_time > '2017-09-05'"})
    

    where / tables参数
    您可以使用where定义显式SQL WHERE子句 - 也许执行非显式连接。您可以使用tables手动将表添加到SQL FROM子句。
    where和tables都接受字符串列表。所有where参数均为“与”任何其他搜索条件。
    举例来讲:

    queryResult=models.Article.objects.extra(where=['nid in (1,3) OR title like "py%" ','nid>2'])
    

    4.整体插入数据

    在Django中需要向数据库中插入多条数据(list)。使用如下方法,每次save()的时候都会访问一次数据库。导致性能问题:
    创建对象时,尽可能使用bulk_create()来减少SQL查询的数量。
    例子:

    device_obj_list = []
        for i in range(int(nums)):
            device_obj_list.append(
                Device(
                    category=category,
                    seat=seat_obj,
                    asset_code='---',
                    asset_num='{}-xxxx'.format(category.name),  # 类型-xxxx
                    use_info='---',
                    operator=operator,
                    op_type=1
                )
            )
         # 使用django.db.models.query.QuerySet.bulk_create()批量创建对象,减少SQL查询次数
        Device.objects.bulk_create(device_obj_list) 
    

    5.创建索引

    索引可以极大的提高数据的查询速度,但是会降低插入、删除、更新表的速度,因为在执行这些写操作时,还要操作索引文件

    通过db_index和Meta index选项给数据表字段建立索引

    使用索引可快速访问数据库表中的特定信息。数据库索引好比是一本书前面的目录,没有索引目录的话,你访问书中某个页面需要从第1页遍历到最后一页,如果有目录,你可以快速地根据目录查找到所需要的页面。Django项目中如果你需要频繁地对数据表中的某些字段(如title)使用filter(), exclude()和order_by()方法进行查询,我们强烈建议你对这些字段建议索引(index), 提升查询效率。

    要对模型中的某个字段建立数据库索引,你可以使用db_index选项,也可以使用Meta选项建立索引。使用Meta选项的好处是你可以一次性对多个字段建立索引,还可以对多个字段建立组合索引。

    方法一: 使用db_index选项

    class Article(models.Model):    
    	"""文章模型"""    
    	# 使用db_index=True对title建立索引    
    	title = models.CharField('标题', max_length=200, db_index=True)
    

    方法二: 使用Meta选项

    class Article(models.Model):    
    	"""文章模型"""   
    	title = models.CharField('标题', max_length=200,)    
    
    	class Meta:        
    		indexes = [models.Index(fields=['title']),]
    

    unique_together 联合主键,包含 index_together
    index_together 组合索引

    class Meta:
            unique_together = [["store", "barcode"], ["store", "mac_addr_no"]]
    
        class Meta:
            index_together = ["store", "sensor"]
    

    Django索引原则:

    主键必定是索引
    Django默认会为每个Foreginkey创建一个索引

    展开全文
  • MySQL-查询优化

    2019-01-01 10:53:33
    对于刚入行的程序猿来说,如何优化MySQL查询,是必须跨过的坎。网上有很多关于SQL优化的博文,但大多是片段和结论。这里,我摘抄了《高性能MySQL》一书的内容,从全局的角度将MySQL查询优化的思路和要点进行串通,...

    对于刚入行的程序猿来说,如何优化MySQL查询,是必须跨过的坎。网上有很多关于SQL优化的博文,但大多是片段和结论。这里,我摘抄了《高性能MySQL》一书的内容,从全局的角度将MySQL查询优化的思路和要点进行串通,希望能帮助大家有一个系统性的认知。如果希望深入学习请阅读此书籍,并在实际开发中反复思考佐证。

    一、MySQL基本架构

    1、基本架构组成

    Server层:包括连接器、查询缓存、分析器、优化器、执行器等。涵盖了mysql大多数核心服务功能,以及所有内置函数,所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。

    存储引擎:负责数据的存储和提取,插件式架构模式。

    2、连接器

    连接器负责跟客户建立连接、获取权限、维持和管理连接。用户连接里的权限,都依赖于登陆时用户名、密码通过后,在权限表里查出的用户权限,也就意味着,即使管理员账号对用户权限做了修改,也不会影响已经存在连接的权限。

    客户如果长时间没有动静,连接器就会自动将它断开,这个时间由参数wait_timeout控制,默认8小时。当用户全部使用长连接后,你可能会发现,由些时候MySQL占用内存涨得特别快,这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的,这些资源在连接断开时才释放。

    解决方案:1.定期断开长连接。或者程序里判断执行一个占用内存的大查询后,断开连接,之后查询再重连。2.MySQL5.7以上版本,可以在每次执行一个比较大的操作后,通过执行mysql_reset_connection来重新初始化连接资源,这个过程不需要重连和重做权限认证。

    3、查询缓存

    连接建立后,执行逻辑就会来到第二步:查询缓存。之前执行过的语句及其结果会以key-value的方式缓存在内存中,key是查询语句,value是查询结果。

    查询缓存的失效非常频繁,只要对一个表的更新,这个表上所有的查询缓存都会被清空,所以静态表(比如系统配置表)适合查询缓存(二级缓存?),对于更新压力大的数据库来说,缓存命中率会非常低。

    可以将参数query_cache_type设置成demand,这样对默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句,可以使用SQL_CACHE显示指定。Select SQL_CACHE * from ......

    需要注意的是Mysql8.0版本直接将查询缓存整块功能删除掉了。

    4、分析器

    分析器会先做词法分析,MySQL需要识别出你输入的SQL字符串代表什么。Selcet识别出来是查询语句,字符串T识别成表名T...,判断表是否存在,字段是否存在。然后做语法分析,根据语法规则,判断你输入的语法是否满足MySQL语法,相当于java的编译器。

    5、优化器

    优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在语句有多表关联的时候,决定各表的连接顺序。也就是MySQL通过分析器知道了你要做什么,通过优化器知道了该怎么做,于是就可以进入执行器阶段。

    6、执行器

    开始执行的时候,要判断一下你对这个表T有没有执行查询的权限。打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口。

    二、查询性能优化

    1、为什么查询速度会慢

    通常来说,查询的生命周期大致可以分为以下顺序:从客户端,到服务器,然后在服务器上进行解析,优化后生成执行计划,执行,并返回结果给客户端。其中执行可以认为是整个生命周期最重要的阶段,这其中包含了大量为了检索数据到存储引擎的调用,以及调用后的数据处理,包括排序和分组。

    在完成这些任务的时候,查询需要在不同的地方花费时间,包括网络、CPU计算,生成统计信息和执行计划、锁等待等操作,尤其是向底层存储引擎检索数据的调用操作。优化和查询的目的就是减少和消除这些操作所花费的时间。

    2、慢查询的基础:优化数据访问

    查询性能低下的最基本原因是访问的数据太多,大部分性能低下的查询可以通过减少访问的数据量的方式进行优化:

    1. 确认程序是否在检索大量超过需要的数据,这通常意味着访问太多行,但有时也可能是访问太多列。
    2. 确认MySQL服务器层是否在分析大量超过需求的数据。

    2.1 是否向数据库请求了不需要的数据

    (1)查询不需要的记录

    例如查询select查询大量的结果,然后获取前面N条结果后关闭结果集。分页查询使用逻辑分页也是一样,查询所有数据,只返给页面所需N条记录。最简单有效的方法就是在这样的查询中使用Limit。

    (2)多表关联时返回全部列

    “select * from..”总是取出全部列,会让优化器无法完成索引覆盖扫描这列优化,还会为服务器带来额外的I\O,内存和CPU消耗。因此一些DBA严格禁止SELECT * 的写法。但是在许多实际开发中,查询返回超过需要的数据也不总是坏事,因为这种有点浪费数据库资源的方式可以简化开发,提高相同代码片段的复用性(一组数据结果可供多个接口使用)。获取并缓存所有列的查询,相比多个独立的只获取部分列的查询可能更有好处。

    (3)重复查询相同的数据。

    在程序中很容易出现这样的逻辑错误——不断执行相同的查询,并返回相同数据。例如,博客中用户评论功能中,需要查询用户头像,那么用户多次评论的时候,可能会反复查询头像数据。比较好的方案是,在初次查询的时候就将头像数据缓存起来。

    2.2 MySQL是否在扫描额外记录

    在确定查询只返回需要的数据后,接下来应该看看为了需要的结果是是否扫描了过多的数据。对于MySQL,最简单的衡量查询开销三个指标如下:

    • 响应时间
    • 扫描行数
    • 返回行数

    这三个指标都会记录在MySQL慢日志中。

    (1)响应时间

    响应时间是两部分之和:服务时间和排队时间(一般常见的是等待I/0操作、行锁等等)。响应时间可能是单个查询的问题,也可能是服务器问题等等。

    (2)扫描的行数和访问类型

    MySQL有好几种访问方式可以查找并返回一行结果。有些方式可能需要扫描很多行才会返回一行结果,也有些访问方式可能无须扫描就返回结果。

    在EXPLAIN语句中的type列反应了访问类型。访问类型有很多种,从全表扫描,索引扫描,范围扫描、唯一索引查询、常数引用等。

    一般MySQL可以用如下三种方式应用where条件,从好到坏依次是:

    • 在索引中使用where条件来过滤不匹配的记录,这是在存储引擎完成的。
    • 使用索引覆盖扫描(在extra列中出现了Using Index)来返回记录,直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成,但无需再回表查询记录。
    • 从数据表中返回数据,然后过滤不满足条件的记录(在extra列中出现了Using Where),这在MySQL服务器层完成,MySQL需要先从数据表中读出记录然后过滤。

    所以好的索引可以让查询使用适合的访问类型,尽可能地只扫描需要的数据行。如果发现查询需要扫描大量的数据但只返回少数的行,那么通常可以尝试以下的方式去优化它:

    • 使用索引覆盖扫描,把所有需要用的列都放在索引中(比如多字段的聚合索引),这样存储引擎无须回表获取对应的行就能返回结果。
    • 改变库表结构,例如使用单独的汇总表。
    • 重写这个复杂的查询,让MySQL优化器能够以更优化的方式执行这个查询。

    例子:

    Collection_info 表有100万条数据,id为主键,collection_id,java中查询语句collection_id中和collect_time由参数传递

    Select id,device_id,device_name,collection_id,collection_type,collection_name,collection_value,collect_time 
    from collection_info 
    where collection_id=352 and (collect_time between CONCAT('2018-12-01',' 00:00:00') and CONCAT ('2018-12-01',' 23:59:59')) 

    这么一条sql语句执行时间:3.992s

    EXPLAIN查看执行计划

    虽然type达到ref级别,但扫描行rows16350,这是由于单索引collecion_id选择性(基数)太差,也就是重复数多。extra使用Useing where。

    优化:

    对collection_id,collect_time两字段使用组合索引(where 条件求交,即and,使用组合索引往往优于单索引,但要注意索引顺序)。另一方面因为B-TREE索引,collect_time的范围查询只需要找出首点数据,就能连续性导出范围数据。

    Alter table collection_info add index idx_collection_id_time (collection_id,collect_time)

    EXPLAIN查看执行计划

    很明显扫描行数得到了很大优化。

    执行时间:0.061s

    后期,我又将collection_id,collect_time调整为primary key 即主键,查询速度为0.020s,这是因为innodb引擎默认使用主键来建立聚簇索引(聚集单页数据),原来以id为主键时,相同collecion_id的数据存放在不同的块中,最糟糕的情况是每一条查询可能都会导致一次I/O。后期优化后,也就是同collecion_id的相关的数据都紧邻存放在同一个块中,减少I/O次数,提高了I/O效率。另一方面,聚簇索引页节点直接存放数据,普通索引页节点存放指针,还得再走一次聚簇索引。

    3、重构查询方式

    3.1 一个复杂的查询还是多个简单的查询

    在传统实现中,总是强调需要数据库层完成尽可能多的工作,这样做的原因在于以前总是认为,网络通信、查询解析和优化是一件代价很高的事情。

    但是这对MySQL并不适用。MySQL连接和断开都很轻量级,在返回一个小的查询结果集方面很高效。现在的网络速度比以前快很多,无论是带宽还是延迟。

    在一个通用的服务器上,MySQL能运行每秒超过10万条查询,即使1000兆网卡,也能轻松满足每秒超过2000次查询。MySQL内部每秒能够扫描内存中上百万行数据,相比之下,MySQL响应数据给客户端就慢得多。在其他条件相同的情况下,使用尽可能少的查询当然更好,但有时将一个大查询分解成多个小查询也是有必要的。

    3.2 切分查询

    将大查询切分成小查询,每个查询功能完全一样,只完成一小部分,每次只返回一小部分查询结果。

    删除旧数据就是一个很好的例子,如果一个大的语句一次性完成的话,则可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻碍很多小的但重要的查询。将一个大的delete语句切分成多个较小的查询语句可以尽可能小的影响MySQL性能,同时还可以减少复制的延迟,例如每个月需要运行一次下面的查询。

    DELET FROM message where create < DATE_SUB(NOW(),INTERVAL 3 MONTH)

    那么我们可以用类似下面的办法来完成工作:

    rows_affected=0
    do{
        rows_affected=do_query(
            DELET FROM message where create < DATE_SUB(NOW(),INTERVAL 3 MONTH) limit 10000
        )
    } while rows_affected > 0

    3.3 分解关联查询

    可以对每个表进行一次单表查询,然后将结果在应用程序中关联。

    好处:

    • 让缓存效率更高。许多应用程序可以方便的缓存单表查询的结果集,另外,关联中的某个表发生了变化,那么就无法使用查询缓存了,拆分后,单表之间不受影响。
    • 执行单个查询可以减少锁的竞争。
    • 在应用层做关联,可以更容易对数据库拆分,更容易做到高性能和扩展性。
    • 查询效率本身可能会有所提升。
    • 可减少冗余记录的查询。在应用层做关联查询,意味着对某条记录应用只需要查询一次,而在数据库中做关联查询可能需要重复地访问一部分数据。
    • 更进一步,这样做相当于在应用层实现了哈希关联,而不是MySQL的嵌套循环关联。

    4、查询执行的基础

    当希望MySQL能以更的性能运行查询时,最好的办法就是弄清楚MySQL是如何优化和执行查询的。下图为一条查询语句的执行路径:

    4.1 MySQL客户端/通信协议

    MySQL客户端和服务器之间的通信协议是半双工的,在任何一个时刻,要么由客户端向服务器发送数据,要么由服务器向客服端发送数据,这两个动作不能同时发生。这种协议让MySQL通信简单快速,但也带来诸如没法进行流量控制等限制。

    客户端用一个单独的数据包将查询传递给服务器,这也是为什么当一个查询语句很长时,参数max_allowed_packet就特别重要了,比如批量查询时一次性传递N条的SQL组合语句。

    相反地一般服务器返回给客户端的数据很多,由多个数包组成,客户端必须完整地接收返回结果,而不能简单地只取前面几条结果,然后让服务器终止数据发送,这也是在必要的时候一定要在查询中加上limit的原因(limit可终止查询)。

    4.2 查询缓存

    一个查询执行过程中,MySQL会优先检查这个查询是否命中查询缓存中的数据,这个检查是通过一个对大小写敏感的哈希查找来实现的。如果命中缓存,再检查用户权限。

    4.3 查询优化处理

    Mysql能够处理的优化类型:

    • 重定义关联表的顺序
    • 将外连接转换成内连接
    • 使用等价变换规则
    • 优化count()、max()和min()
    • 预估并转化为常数表达式
    • 覆盖索引扫描
    • 子查询优化
    • 提前终止查询
    • 等值传播
    • 列表in()的比较

    (1)MySQL如何执行关联查询

    MySQL关联执行的策略很简单:对任何关联查询都执行循环嵌套关联操作,即MySQL先从一张表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配行,依次下去,直到找到所有表中的匹配行(类似菜单迭代的过程),按照这样的方式查找第一表记录,再嵌套查询下一个关联表,然后再回溯到上一个表。

    请看下面的例子:

    用伪代码表示:

    MySQL再from子句中遇到子查询时,先执行子查询并将结果集放到一个临时表中,然后进行循环嵌套关联查询。注意临时表是没有任何索引的,所以能用表关联提升查询效率时,就代替子查询。

    对于Union查询,MySQL会将一系列单个查询放入一张临时表,然后再重新读出临时表的数据来完成查询操作。

    (2)关联顺序优化

    MySQL会选择适合的关联顺序(通常小表驱动),来让查询执行成本尽可能低,不过如果有超过n多个表,优化器不可能逐一评估每一中关联的成本,则会使用贪婪搜索方式。

    (3)排序优化

    无论如何排序都是一个成本很高的操作,所以从性能角度考虑,应尽可能避免排序或者尽可能避免大数据排序(索引排序另当别论)。当不能使用索引生成排序结果时,MySQL需要自己进行排序,如果数据量小于排序缓冲区,使用内存快速排序,如果内存不够排序,MySQL会先将数据分块,对每个独立的块使用快速排序,并将各个块排序结果存放在磁盘上,最后将各个块合并返回排序结果。

    MySQL在进行文件排序的时候,对没一个排序记录都会分配一个足够长的定长空间来存放,那排序时所需要的临时存储空间往往比磁盘上的表大很多。

    MySQL5.6对limit的order by 做了改进。当只需要返回部分结果时,例如使用了limit子句,MySQL不再对所有结果进行排序,而是根据实际情况,抛弃不满足条件的结果,然后再进行排序。

    5、查询执行引擎

    执行阶段,MySQL只是简单地根据执行计划(一种数据结构)给出的指令逐步执行。在执行过程中有大量的操作需要通过调用存储引擎实现的接口来完成,这些接口称为“handler API”,查询中的每一个表由一个handler实例表示。

    实际上MySQL在优化阶段就为每个表创建了一个handler实例,优化器根据这些实例的接口获取表的相关信息,包括表的所有列,索引统计信息(不同的促存储引擎统计方式不同),等等。

    是在表里面有多个索引的时候,决定使用哪个索引;或者在语句有多表关联的时候,决定各表的连接顺序。也就是MySQL通过分析器知道了你要做什么,通过优化器知道了该怎么做,于是就可以进入执行器阶段。但底层TCP可能对MySQL的封包缓存一部分后批量传送。

    6、返回结果给客户端

    MySQL将结果返回给客户端是一个增量,逐步返回的过程。例如前面的关联操作,一旦服务器处理完最后一个关联表,开始生成第一条结果,MySQL就开始向客户端逐步返回结果集了。这样服务器就无需为存储太过结果而消耗大量内存,客户端也能在第一时间获得返回结果。

    开始执行的时候,要判断一下你对这个表T有没有执行查询的权限。打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口。

     

    展开全文
  • MySQL查询优化方法

    2017-11-28 15:05:58
    MySQL查询优化方法
  • 1.两种查询引擎查询速度(myIsam 引擎 )InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行。MyISAM只要简单的读出保存好的行数即可。注意的是,当...
  • 4、LIKE前缀%号、双百分号、_下划线查询非索引列或*无法使用到索引,如果查询的是索引列则可以 5、读取适当的记录LIMIT M,N,而不要读多余的记录 6、避免数据类型不一致 7、分组统计可以禁止排序sort,总和查询...
  • 交代一下背景,这算是一次项目经验吧,属于公司一个已上线平台的功能,这算是离职人员挖下的坑,随着数据越来越多,原本的SQL查询变得越来越慢,用户体验特别差,因此SQL优化任务交到了我手上。 这个SQL查询关联两个...
  •  在公司实习的时候,导师分配了SQL慢查询优化的任务,任务是这样的:每周从平台中导出生产数据库的慢查询文件进行分析。进行SQL优化的手段也主要是修改SQL写法,或者新增索引。  现在从记录项目中的一点点做起。 ...
  • 一、mysql查询原理: MYSQL从4.1版体开始支持子查询,使用子查询可以进行 SELECT语句的嵌套查询,即一个 SELECT查询的结果作为另一个 SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多 个步骤才能完成的...
  • MySQL查询优化查询优化器phpma.com  当你提交一个查询的时候,MySQL会分析它,看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL采用的优化手段,可以查看...
  • MySQL 查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行。最终目标是提交 SELECT 语句查找数据行,而不是排除数据行。优化器试图排除数据行的原因在于它...
  • Mysql查询优化器浅析(上)译者:杨万富 1 定义 Mysql查询优化器的工作是为查询语句选择合适的执行路径。查询优化器的代码一般是经常变动的,这和存储引擎不太一样。因此,需要理解最新版本的查询优化器是如何组织...
  • Mysql查询优化器浅析(下)译者:杨万富 7 存取类型 当我们评估一个条件表达式,MySQL判断该表达式的存取类型。下面是一些存取类型,按照从最优到最差的顺序进行排列:system … 系统表,并且是常量表const …...
  • 注:由于查询优化器涉及面很广也比较复杂,作者也没有完全领会,本文主要来自书籍<<数据库查询优化的艺术: 原理解析和SQL性能优化>>,如果涉及到版权,请告知作者,删除本文。 一、查询语句的执行过程...
  • 手册上MYSQL查询优化器概述;个人对MySQL优化器的理解;分析优化器优化过程中的信息;调节MySQL优化器的优化等
  • 不过MySQL查询优化器只对少部分查询不适用,而且我们往往可以通过改写查询MySQL高效的完成工作。 1 关联子查询 MySQL的子查询实现的非常糟糕。最糟糕的一类查询时where条件中包含in()的子查询语句。因为MySQL对...
  • mysql查询优化

    2019-03-31 00:36:36
    优化器分类 传统关系型数据库里面的优化器分为CBO和RBO两种。 RBO— Rule_Based Potimizer 基于规则的优化器: RBO :RBO所用的判断规则是一组内置的规则,这些规则是硬编码在数据库的编码中的,RBO会根据这些规则...
  • MySQL查询优化之二-范围优化(Range Optimization) 如需转载请标明出处:http://blog.csdn.net/itas109 QQ技术交流群:12951803 环境: MySQL版本:5.5.15 操作系统:windows 本文讨论范围的优化...
1 2 3 4 5 ... 20
收藏数 284,086
精华内容 113,634
关键字:

mysql查询优化