MYSQL数据库优化《详细版》

MYSQL数据库优化方向

1.  sql及索引优化，存储优化（程序）
2. 数据库表结构优化
3. 系统配置
4. 硬件

一、SQL语句优化

   1、Mysql慢查日志的开启和日志存储格式

         查看慢查询日志是否开启：show variables like 'slow_query_log'

         查看没索引日志是否记录：show variables like '%log%'

         列表项‘log_queries_not_using_indexes’,如果是OFF

         开启非索引日志记录：set global log_queries_not_using_indexes=on

         查询时间设置（超过多长时间的sql记录慢查询日志）：show variables like 'long_query_time';

         这里测试设置为空0：set global long_query_time=0.0  (需要重新链接下mysql才回看到设置的值)

         测试：随便查询几个表

         找到慢查询日志存放目录：show varibels like '%log%';

         | slow_query_log_file                     | /var/lib/mysql/ubuntu-slow.log 

         退出mysql,

         sudo cat /var/lib/mysql/ubuntu-slow.log ，查看慢查日记记录内容

            
 

         2、慢查询日志管理工具

               mysqldumpslow

              退出mysql,系统中:mysqldumpslow -h查看工具使用方法，比如：

              sudo mysqldumpslow -t 5 /var/lib/mysql/ubuntu-slow.log

                  

                  pt-query-digest功能比mysqldumpslow强的多

                  查看帮助命令：pt-query-digest --help

              使用实例：sudo pt-query-digest /var/lib/mysql/ubuntu-slow.log 

 

        3、慢查询日志如何发现有问题的sql

        

                     rows examine扫描行数。

 

       4、explain查询和分析sql执行计划，先计划在执行

        
         
              
        using filesort 和using temporary一般是order by 和group by导致的。

 

    5、max()优化

          max()直接使用会扫描所有行，创建索引后不需要扫描：

         创建索引：explain select max(payment_date) from payment \G;

          命：:explain select max(payment_date) from payment \G;

 

    6、子查询优化

          优化成 join..on(链接方式，若果一对多，注意重复数据处理ditinct)

 

    7、group by优化

     sql:EXPLAIN SELECT actor.first_name,actor.last_name,COUNT(*) FROM film_actor INNER JOIN actor USING(actor_id) GROUP BY film_actor.actor_id;

      操作了大量的io(filesort)

      优化成子查询：

       EXPLAIN SELECT actor.first_name,actor.last_name,c.cnt FROM actor INNER JOIN (SELECT film_actor.actor_id,COUNT(*) AS cnt FROM film_actor GROUP BY actor_id) AS c USING(actor_id);

      group 多表链接尽量使用子查询。

 

    8、limit优化

     普通sql:EXPLAIN SELECT f.film_id,f.description,f.title FROM film f LIMIT 0,5;扫描全表，type是all

     第一步优化：EXPLAIN SELECT f.film_id,f.description,f.title FROM film f ORDER BY f.film_id LIMIT 0,5;

     通过一个ID排序，也是扫描全表但是，type是index(索引类型)

     第二步优化：EXPLAIN SELECT f.film_id,f.description,f.title FROM film f WHERE film_id<=56 AND       film_id>50 ORDER BY f.film_id LIMIT 0,5;

     通过记录上次id位置，避免扫描全表，注意适用于ID递增，没有空缺的表。

二、索引优化

    1、选择合适的列建立索引

     

        列的离散度，是指列的唯一性。离散度越大，唯一性越大。比如：SELECT COUNT(DISTINCT p.customer_id), COUNT(DISTINCT p.staff_id) FROM payment AS p，统计的customer_id,比staff_id大，说明，离散密度就大。

     2、SQL优化索引

         
          
       

     优化重复和冗余的索引。此工具可以显示重复和冗余的索引，以及解决办法。

     3、索引维护（删除不用的索引）

         
 

三、数据化结构优化

    1、选择合适的数据类型

         
         

         

    2、范式化优化
       

       

       比如删除表里面所有饮料的商品，结果饮料分类和描述也删除了。

    3、反范式化优化

         
       
        
          
       
 
     4、表垂直拆分
     
 

       
     
        
 
    5、表的水平拆分
       
 
        
 

 

四、系统配置优化

    1、数据库操作系统配置的优化

 
 
    

   2、mysql常用配置参数

   
 
         
 
         
 
      
 
        
 
      
 
       
 
 

五、服务器硬件优化

    1、CPU的选择

     

     通常选择单核更快的CPU   

 

    2、磁盘的选择
   

         RAID0,读写最好，RAIA1，数据不会丢失，安全性高。通常选择RAID1+0