MYSQL语句和表的优化

梦里梦外; 2022-08-02 05:41 187阅读 0赞

-- 优化SQL的一般步骤
    -- 一、通过show status和应用特点了解各种SQL的执行频率
    /*
    通过SHOW STATUS可以提供服务器状态信息，也可以使用mysqladmin extended-status命令获得。SHOW STATUS可以根据需要显示session级别的统计结果和global级别的统计结果。
    以下几个参数对Myisam和Innodb存储引擎都计数：
    1.Com_select  执行select操作的次数，一次查询只累加1；
    2.Com_insert 执行insert操作的次数，对于批量插入的insert操作，只累加一次；
    3.Com_update 执行update操作的次数；
    4.Com_delete　执行delete操作的次数；
    */
    SHOW STATUS WHERE Variable_name = 'Com_select';
    
    /*
    以下几个参数是针对Innodb存储引擎计数的，累加的算法也略有不同：
    1.Innodb_rows_read select查询返回的行数；
    2.Innodb_rows_inserted 执行Insert操作插入的行数；
    3.Innodb_rows_updated 执行update操作更新的行数；
    4.Innodb_rows_deleted 执行delete操作删除的行数；
    通过以上几个参数，可以很容易的了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的SQL大致的执行比例是多少。
    对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会累加。
    
    对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。
    此外，以下几个参数便于我们了解数据库的基本情况：
    1.Connections 试图连接Mysql服务器的次数
    2.Uptime 服务器工作时间
    3.Slow_queries 慢查询的次数
    
    二、定位执行效率较低的SQL语句
    可以通过以下两种方式定位执行效率较低的SQL语句：
    1.可以通过慢查询日志定位那些执行效率较低的sql语句，用--log-slow-queries[=file_name]选项启动时，
    mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。可以链接到管理维护中的相关章节。
    
    2.慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，
    可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等等，可以实时的查看SQL执行情况，同时对一些锁表操作进行优化。
    */
    SHOW PROCESSLIST;
    
    /*
    三、通过EXPLAIN分析低效SQL的执行计划
    通过以上步骤查询到效率低的SQL后，我们可以通过explain或者desc 获取MySQL如何执行SELECT语句的信息，包括select语句执行过程表如何连接和连接的次序。
    explain 可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。
    */
    EXPLAIN SELECT * FROM message a LEFT JOIN mytable b ON a.id = b.id WHERE a.id=1;
    /*
    返回结果
    +--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
    |   id   | select_type   | table | type  | possible_keys| key            | key_len   | ref   |  rows  |  Extra       |
    +--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
    |   1    | SIMPLE        | a     | const | PRIMARY      | PRIMARY        | 4         | const | 1      |              |
    |   1    | SIMPLE        | b     | ALL   | NULL         | NULL           | NULL      |       | 9999   |              |
    +--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
    
    select_type：select 类型
    table：      输出结果集的表
    type：       表示表的连接类型
    
    ①当表中仅有一行是type的值为system是最佳的连接类型；
    
    ②当select操作中使用索引进行表连接时type的值为ref；
    
    ③当select的表连接没有使用索引时，经常会看到type的值为ALL，表示对该表进行了全表扫描，这时需要考虑通过创建索引来提高表连接的效率。
    
    possible_keys：表示查询时,可以使用的索引列.
    key：          表示使用的索引
    key_len：      索引长度
    rows：         扫描范围
    Extra：执行情况的说明和描述
    
    
    四、确定问题，并采取相应的优化措施
    经过以上步骤，基本可以确认问题出现的原因，可以根据情况采取相应的措施，进行优化提高执行的效率。
    例如上面的例子，我们确认是对b表的全表扫描导致效率的不理想，我们对b表的 id 字段创建了索引，查询需要扫描的行数明显较少。
    返回结果
    +--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
    |   id   | select_type   | table | type  | possible_keys| key            | key_len   | ref   |  rows  |  Extra       |
    +--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
    |   1    | SIMPLE        | a     | const | PRIMARY      | PRIMARY        | 4         | const | 1      |              |
    |   1    | SIMPLE        | b     | const | PRIMARY      | PRIMARY        | 4         | const | 1      |              |
    +--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
    
    
    
    
    
    
    
    
    大批量插入数据时优化SQL语句==============================================
    一、对于Myisam类型的表，可以通过以下步骤快速的导入大量的数据。 
        ALTER TABLE tablename DISABLE KEYS;
        批量插入数据
        ALTER TABLE tablename ENABLE KEYS;
    前后两个命令用来打开或者关闭Myisam表非唯一索引的更新。在导入大量的数据到一个非空的Myisam表时，通过设置这两个命令，可以提高导入的效率。
    对于导入大量数据到一个空的Myisam表，默认就是先导入数据然后才创建索引的，所以不用进行设置。
    */
    ALTER TABLE mytable DISABLE KEYS;
    INSERT INTO mytable(id, username, city, age) VALUES(1, 'name1', 'city1', 10),(2, 'name2', 'city2', 20),(3, 'name3', 'city3', 30);
    ALTER TABLE mytable ENABLE KEYS;
    
    /*
    二、而对于Innodb类型的表，这种方式并不能提高导入数据的效率。对于Innodb类型的表，我们有以下几种方式可以提高导入的效率：
    ①因为Innodb类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。
    如果Innodb表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。
    
    ②在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
    
    ③如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
    */
    SET UNIQUE_CHECKS=0;
    SET UNIQUE_CHECKS=1;
    SET AUTOCOMMIT=0;
    SET AUTOCOMMIT=1;
    /*
    优化insert语句==============================================
    1、如果同时插入很多行，请使用多个值的INSERT语句。这比使用分开INSERT语句快(在一些情况中几倍)。
    Insert into test values(1,2),(1,3),(1,4)…
    
    2、如果从不同客户插入很多行，能通过使用INSERT DELAYED 语句得到更高的速度。 
    Delayed 的含义是让insert 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘；这比每条语句分别插入要快的多；
    LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完后才进行插入；
    
    3、将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）；
    
    4、如果批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对myisam表使用；
    
    5、当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍；
    
    6、根据应用情况使用 replace 语句代替 insert；
    
    7、根据应用情况使用 ignore 关键字忽略重复记录。
    */
    INSERT DELAYED INTO mytable(id, username, city, age) VALUES(4, 'name4', 'city4', 40);
    INSERT LOW_PRIORITY INTO mytable(id, username, city, age) VALUES(5, 'name5', 'city5', 50);
    REPLACE INTO mytable(id, username, city, age) VALUES(5, 'name5', 'city5', 50);
    INSERT IGNORE INTO mytable(id, username, city, age) VALUES(5, 'name5', 'city5', 50);
    /*
    优化group by语句==============================================
    
    默认情况下，MySQL排序所有GROUP BY col1，col2，....。查询的方法如同在查询中指定ORDER BY  col1，col2，...。
    如果显式包括一个包含相同的列的ORDER BY子句，MySQL可以毫不减速地对它进行优化，尽管仍然进行排序。
    
    如果查询包括GROUP BY但你想要避免排序结果的消耗，你可以指定 ORDER BY NULL禁止排序。例如：
    */
    SELECT * FROM mytable GROUP BY username ORDER BY NULL;
    /*
    优化order by语句==============================================
    
    在某些情况中，MySQL可以使用一个索引在 ORDER BY 子句中，而不需要额外的排序。
    where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。
    
    例如：下列sql可以使用索引。
        SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; -- 同一组合索引的某一部分
        SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
        SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
     
    但是以下情况不使用索引：
    SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
    -- order by的字段混合ASC和DESC
    
    SELECT * FROM t1 WHERE key2=constant ORDER BY key1；
    -- 用于查询行的关键字与ORDER BY中所使用的不相同
    
    SELECT * FROM t1 ORDER BY key1, key2；
    -- 对不同的索引使用ORDER BY：
    
    
    
    
    
    
    优化join语句==============================================
    
    Mysql4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。
    使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。
    但是，有些情况下，子查询可以被更有效率的连接(JOIN).. 替代。
    
    假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：
        SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
     
    如果使用连接(JOIN).. 来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：
        SELECT * FROM customerinfo
        LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID
        WHERE salesinfo.CustomerID IS NULL 
     
    连接(JOIN).. 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
    
    
    
    
    insert、update、delete的使用顺序==============================================
    
    MySQL还允许改变语句调度的优先级，它可以使来自多个客户端的查询更好地协作，这样单个客户端就不会由于锁定而等待很长时间。
    改变优先级还可以确保特定类型的查询被处理得更快。
    我们首先应该确定应用的类型，判断应用是以查询为主还是以更新为主的，是确保查询效率还是确保更新的效率，决定是查询优先还是更新优先。
    
    下面我们提到的改变调度策略的方法主要是针对Myisam存储引擎的，对于Innodb存储引擎，语句的执行是由获得行锁的顺序决定的。
     
    MySQL的默认的调度策略可用总结如下：
    1.写入操作优先于读取操作。
    2.对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。
    3.对某张数据表的多个读取操作可以同时地进行。
    
    MySQL提供了几个语句调节符，允许你修改它的调度策略：
    1.LOW_PRIORITY 关键字应用于 DELETE 、 INSERT 、 LOAD DATA 、 REPLACE和UPDATE 。
    2.HIGH_PRIORITY关键字应用于SELECT和INSERT语句。
    3.DELAYED关键字应用于INSERT和REPLACE语句。
     
    如果写入操作是一个LOW_PRIORITY（低优先级）请求，那么系统就不会认为它的优先级高于读取操作。
    在这种情况下，如果写入者在等待的时候，第二个读取者到达了，那么就允许第二个读取者插到写入者之前。
    只有在没有其它的读取者的时候，才允许写入者开始操作。这种调度修改可能存在LOW_PRIORITY写入操作永远被阻塞的情况。
    
    SELECT查询的HIGH_PRIORITY（高优先级）关键字也类似。它允许SELECT插入正在等待的写入操作之前，即使在正常情况下写入操作的优先级更高。
    另外一种影响是，高优先级的SELECT在正常的SELECT语句之前执行，因为这些语句会被写入操作阻塞。
    
    如果你希望所有支持LOW_PRIORITY选项的语句都默认地按照低优先级来处理，那么请使用--low-priority-updates选项来启动服务器。
    通过使用INSERT HIGH_PRIORITY来把INSERT语句提高到正常的写入优先级，可以消除该选项对单个INSERT语句的影响。
    */
    INSERT LOW_PRIORITY INTO mytable(id, username, city, age) VALUES(7, 'name7', 'city7', 70);
    /*
    优化数据表==============================================
    
    一、优化表的数据类型
    表需要使用何种数据类型，是需要根据应用来判断的。
    虽然应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样即浪费存储也浪费内存。
    我们可以使用PROCEDURE ANALYSE()对当前已有应用的表类型的判断，该函数可以对数据表中的列的数据类型提出优化建议，可以根据应用的实际情况酌情考虑是否实施优化。
    
    语法：
       SELECT * FROM tbl_name PROCEDURE ANALYSE();
       SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);
    输出的每一列信息都会对数据表中的列的数据类型提出优化建议。第二个例子告诉PROCEDURE ANALYSE()不要为那些包含的值多于16个或者256字节的ENUM类型提出建议。
    如果没有这样的限制，输出信息可能很长；ENUM定义通常很难阅读。
    
    在对字段类型进行优化时，可以根据统计信息并结合应用的实际情况对其进行优化。
    二、通过拆分，提高表的访问效率
    这里我们所说的拆分，主要是针对Myisam类型的表，拆分的方法可以分成两种情况：
    
    1、纵向拆分：
    纵向拆分是只按照应用访问的频度，将表中经常访问的字段和不经常访问的字段拆分成两个表，经常访问的字段尽量是定长的，这样可以有效的提高表的查询和更新的效率。
    
    2、横向拆分：
    横向拆分是指按照应用的情况，有目的的将数据横向拆分成几个表或者通过分区分到多个分区中，这样可以有效的避免Myisam表的读取和更新导致的锁问题。
    
    三、逆规范化
    数据库的规范化设计强调数据的独立性，数据应该尽可能少地冗余，因为存在过多的冗余数据，这就意味着要占用了更多的物理空间，同时也对数据的维护和一致性检查带来了问题。
    
    对于查询操作很多的应用，一次查询可能需要访问多表进行，如果通过冗余相同数据纪录在一个表中，更新的代价增加不多，
    但是查询操作效率可以有明显提高，这种情况就可以考虑通过冗余数据来提高效率。
    
    四、使用冗余统计表
    使用create temporary table语法，它是基于session的表，表的数据保存在内存里面，当session断掉后，表自然消除。
    
    对于大表的统计分析，如果统计的数据量不大，利用insert。。。select将数据移到临时表中比直接在大表上做统计要效率更高。
    
    五、选择更合适的表类型
    1、如果应用出现比较严重的锁冲突，请考虑是否更改存储引擎到innodb，行锁机制可以有效的减少锁冲突的出现。
    2、如果应用查询操作很多，且对事务完整性要求不严格，则可以考虑使用Myisam存储引擎。
    */
    SELECT * FROM mytable PROCEDURE ANALYSE(16,256);
    
    /*
    其他优化措施
    
    使用连接池==============================================
    对于访问数据库来说，建立连接的代价比较昂贵，因此，我们有必要建立"连接池"以提高访问的性能。
    我们可以把连接当作对象或者设备，池中又有许多已经建立的连接，访问本来需要与数据库的连接的地方，都改为和池相连，池临时分配连接供访问使用，结果返回后，访问将连接交还。
    
    
    减少对Mysql的访问==============================================
    一、避免对同一数据做重复检索：
    应用中需要理清楚对数据库的访问逻辑，需要对相同表的访问，尽量集中在相同sql访问，一次提取结果，减少对数据库的重复访问。
    二、使用mysql query cache：
    作用：查询缓存存储SELECT查询的文本以及发送给客户端的相应结果。如果随后收到一个相同的查询，服务器从查询缓存中重新得到查询结果，而不再需要解析和执行查询。
    
    适用范围：不发生数据更新的表。当表更改（包括表结构和表数据）后，查询缓存值的相关条目被清空。
    
    查询缓存的主要参数设置：
    */
    SHOW VARIABLES LIKE '%query_cache%'; -- 也可以写成 SHOW VARIABLES WHERE Variable_name LIKE '%query_cache%';
    /*
    have_query_cache  表明服务器在安装使已经配置了高速缓存
    query_cache_size  表明缓存区大小，单位为字节(1024字节为1KB)
    query_cache_type  值从0到2，含义分别为
                      0或者off（缓存关闭）
                      1或者on（缓存打开，使用sql_no_cache的select除外）
                      2或者demand（只有带sql_cache的select语句提供高速缓存）
    */
    SET GLOBAL query_cache_size=1024*50;
    -- 设置查询缓存大小，单位字节，1024字节为 1KB，query_cache_size大小的设置必须大于40KB
    
    
    -- 在 SHOW STATUS 中，你可以监视查询缓存的性能
    SHOW STATUS LIKE '%Qcache%';
    /*
    Qcache_queries_in_cache  在缓存中已注册的查询数目
    Qcache_inserts           被加入到缓存中的查询数目
    Qcache_hits              缓存采样数数目
    Qcache_lowmem_prunes     因为缺少内存而被从缓存中删除的查询数目
    Qcache_not_cached        没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)
    Qcache_free_memory       查询缓存的空闲内存总数
    Qcache_free_blocks       查询缓存中的空闲内存块的数目
    Qcache_total_blocks      查询缓存中的块的总数目
    
    
    三、加cache层：
    Cache（高速缓存）、Memory（内存）、Hard disk（硬盘）都是数据存取单元，但存取速度却有很大差异，呈依次递减的顺序。
    对于CPU来说，它可以从距离自己最近的Cache高速地存取数据，而不是从内存和硬盘以低几个数量级的速度来存取数据。
    而Cache中所存储的数据，往往是CPU要反复存取的数据，有特定的机制（或程序）来保证Cache内数据的命中率（Hit Rate）。
    因此，CPU存取数据的速度在应用高速缓存后得到了巨大的提高。
    
    因为将数据写入高速缓存的任务由Cache Manager负责，所以对用户来说高速缓存的内容肯定是只读的。
    需要你做的工作很少，程序中的SQL语句和直接访问DBMS时没有分别，返回的结果也看不出有什么差别。
    而数据库厂商往往会在DB Server的配置文件中提供与Cache相关的参数，通过修改它们，可针对我们的应用优化Cache的管理。
    
    
    
    均衡负载==============================================
    一、利用mysql 复制分流查询操作：
    利用mysql的主从复制可以有效的分流更新操作和查询操作，具体的实现是一个主服务器，承担更新操作，多台从服务器，承担查询操作，主从之间通过复制实现数据的同步。
    多台从服务器一方面用来确保可用性，一方面可以创建不同的索引满足不同查询的需要。
    
    对于主从之间不需要复制全部表的情况，可以通过在主的服务器上搭建一个虚拟的从服务器，
    将需要复制到从服务器的表设置成blackhole引擎，然后定义replicate-do-table参数只复制这些表，这样就过滤出需要复制的binlog，
    减少了传输binlog的带宽。因为搭建的虚拟的从服务器只起到过滤binlog的作用，并没有实际纪录任何数据，所以对主数据库服务器的性能影响也非常的有限。
    
    通过复制分流查询的存在的问题是主数据库上更新频繁或者网络出现问题的时候，主从之间的数据可能存在差异，造成查询结果的异议，应用在设计的时候需要有所考虑。
    
    二、采用分布式数据库架构：
    mysql从5.0.3开始支持分布式事务，当前分布式事务只对Innodb存储引擎支持。
    分布式的数据库架构适合大数据量，负载高的情况，有良好的扩展性和高可用性。
    通过在多台服务器之间分布数据实现在多台服务器之间的负载平均，提高了访问的执行效率。
    具体实现的时候，可以使用mysql的Cluster功能（NDB引擎）或者自己编写程序来实现全局事务。
    */

原文地址：http://www.cnblogs.com/dreamhome/archive/2013/05/22/3093720.html