mysql性能优化

Sql优化顺序

  1,数据库是否设置合理

•             基本满足三范式

1. 第一范式(确保每列保持原子性)
2. 第二范式(确保表中的每列都和主键相关)
3. 第三范式(确保每列都和主键列直接相关,而不是间接相关)

•             性能比规范更重要

   2，业务sql的优化

   3，数据的配置参数优化

   4，DB服务器硬件优化

   5，操作系统的优化

 

业务的sql优化

    sql优化通用方法：

   1>通过show status 了解sql执行的频率

            a,显示mysql数据库当前操作的历史状态，主要查看"Com"开头的指令

             

           b，查看数据库的连接次数

             

             c,查看数据库服务器工作时间(s)

             

             d，存储过程和函数构建海量数据

                 创建两张关联表

                      Emp: eid,ename,identify,age,sex,did  (200w)

                       Dept:did ,dname        (5w) 

                  分析：1，构建两个存储过程：Insert emp  ;  Insert dept

                              2,eid处理：直接循环1-200w

                              3，ename处理:定义一个函数，随机返回三个汉字

                                       select getRandName(3);

                               4，identify身份证处理：1-50w：513022开头，后12位随机数字

                                                                      50w01-200w：513011开头，后12位随机

                                       SELECT CONCAT('513022',getRandNum(12)) ;

                                       SELECT CONCAT('513011',getRandNum(12)) ;

                               5，age处理：234   0-9

                                    SELECT CAST(getRand234(1) AS signed)*10 + getRandNum(1);

                               6，sex处理：随机返回0或1

                                     select ROUND(RAND());

                       函数定义：   

 ##函数
 ## getRand234 在234中获取一定个数随机字符串
CREATE FUNCTION getRand234(n INT)
       RETURNS VARCHAR(255)
begin 
      declare chars_str varchar(100) DEFAULT '234';
      declare return_str varchar(255) DEFAULT '';
      declare i int DEFAULT 0;
 
      WHILE i<n DO
            set return_str = concat(return_str,SUBSTRING(chars_str,FLOOR(1+rand()*3),1));
            set i=i+1;
      END WHILE;    
  RETURN return_str;    
end;

## getRandName在字符串中获取一定个数的随机字符串
CREATE FUNCTION getRandName(n INT)
       RETURNS VARCHAR(255)
begin 
      declare chars_str varchar(100) DEFAULT '张三李斯王麻子和卢布库斯贸易有限责任公司肖锦曦';
      declare return_str varchar(255) DEFAULT '';
      declare i int DEFAULT 0;
 
      WHILE i<n DO
            set return_str = concat(return_str,SUBSTRING(chars_str,FLOOR(1+rand()*23),1));
            set i=i+1;
      END WHILE;    
  RETURN return_str;    
end;



## getRandNum 在0-9直接获取一定个数的随机字符串
CREATE FUNCTION getRandNum(n INT)
       RETURNS VARCHAR(255)
begin 
      declare chars_str varchar(100) DEFAULT '0123456789';
      declare return_str varchar(255) DEFAULT '';
      declare i int DEFAULT 0;
 
      WHILE i<n DO
            set return_str = concat(return_str,SUBSTRING(chars_str,FLOOR(1+rand()*10),1));
            set i=i+1;
      END WHILE;    
  RETURN return_str;    
end;

            存储过程的定义:

##存储过程 插入部门数据 did dname
create PROCEDURE insert_dept(IN max_num INT(10))
BEGIN
   declare i int default 0;
   set autocommit=0;

  REPEAT
		 set i = i+1;
		 insert into dept(did,dname) VALUES (i,getRandName(5));
		 UNTIL i = max_num
	end REPEAT;
 COMMIT;
END;

##存储过程 插入员工数据eid,ename,identify,age,sex,did
create PROCEDURE insert_empt(IN max_num INT(10))
BEGIN
   declare i int default 0;
   set autocommit=0;

  REPEAT
		 set i = i+1;
		 insert into empt(eid,ename,identify,age,sex,did) VALUES (i,getRandName(3),CONCAT('513022',getRandNum(12)),(CAST(getRand234(1) AS signed)*10 + getRandNum(1)),ROUND(RAND()),ROUND(1+RAND()*50000));
		 UNTIL i = max_num
	end REPEAT;
 COMMIT;
END;

              f,查看慢查询次数

               

                1,查询慢查询个数

                show status like '%slow_queries%';

                2，查询慢查询参数

                show variables like '%slow%';

                   

               3,设置是否开启慢查询日志记录

                 set global slow_query_log=ON; --全局参数需要加global

  

   2> 通过工具定位效率较低sql执行语句

            1，工具：mysqldumpslow.pl 或者pt-query-digest(第三方，需要自己安装),都需要需要安装perl环境

            2，日志查询参数 

              mysqldumpslow

             

             pt-query-digest

             

            3,读取慢查询日志:   

           mysqldumpslow                   

         pt-query-digest

 

 

3> 通过explain定位执行语句效率较低的原因

    explain:mysql性能分析常用命令。

  

参数解析：

  

 

4>构建索引

1，概念：在数据库中通过特殊的算法对sql进行编排处理，使查询的速度达到最快。

2，分类

• 普通索引/组合索引：INDEX
• 唯一索引：UNIQUE INDEX
• 全文索引：FULLTEXT，只在MYISAM引擎中有效
• 主键索引：PRIMARY INDEX

3，创建索引：ALTER table empt add INDEX userId_key(userId);

                       CREATE INDEX userId_key ON empt(userId);

                       Create Table empt (id,useId,name,age,INDEX userId_key(userId));

4,删除索引：ALTER table empt drop INDEX userId_key(userId);

                     ALTER table empt drop INDEX primary INDEX;  --主键索引

5,索引的优化

不加索引性能分析：

加上索引之后：

 

查询索引使用情况：

参数说明：

刷新状态：FLUSH status;

5>水平拆分

把原来一张海量数据的表水平拆分成若干表，小表的结构与原表一致。

插入时：可根据一定规则随机获取表名插入。如可根据表id对分表的数量n取模的方式来确定插入到哪张表。id%-->表名。

查询时：获取表名的方式和插入数据时一样。

 

6>垂直拆分

把原来一张海量数据的表垂直拆分成若干表，小表的列是原表的部分列。

插入时：可根据一定规则随机获取表名插入。如可根据表id对分表的数量n取模的方式来确定插入到哪张表。id%-->表名。

查询时：获取表名的方式和插入数据时一样。

 

7>读写分离(集群)

把原来在一个库里面进行读写操作，转变成读、写分开在多个库中操作。

一般写在主库中进行，读在从库中进行。

 

  8>常用的sql优化方法

a,查看当前mysql的存储引擎        

b,配置优化

c，合适的数据类型

     如果对精度要求高，float（10，2）会丢失精度，建议选择decima(10,2)

     对日期的存储：字符串代替时间："2018-12-24 12:23:30"

                               整形代替时间戳：123132125（ms）

     ip地址通过bigint存储：

d,limit优化

   将limit语句尽量转换成where条件：需要满足字段连续增长且不为空

  

f，order by null 手动去掉排序

g,能用连接查询就不用子查询

h,sql的执行顺序

i：文件和图片需要单独的服务器存储，my.ini系统参数配置优化

sql优化汇总

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

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：  select id from t where num is null   可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：    select id from t where num=0      

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

4.应尽量避免在 where 子句中使用 or 来连接条件(用union all )，否则将导致引擎放弃使用索引而进行全表扫描，如：    

select id from t where num=10 or num=20    

可以这样查询：    

select id from t where num=10    

union all    

select id from t where num=20    

5.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    

6.下面的查询也将导致全表扫描：    

select id from t where name like '%abc%'      

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

select id from t where num/2=100    

应改为:    

select id from t where num=100*2     

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

select id from t where substring(name,1,3)='abc'--name以abc开头的id    

应改为:    

select id from t where name like 'abc%'        

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

10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，    

否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。     

11.不要写一些没有意义的查询，如需要生成一个空表结构：    

select col1,col2 into #t from t where 1=0    

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：    

create table #t(...)       

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.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，    

如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。        

14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，    

因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。    

一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。        

15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。    

这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。        

16.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，    

其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。        

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

18.避免频繁创建和删除临时表，以减少系统表资源的消耗。

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

20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，    

以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

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

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

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

24.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。

在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

25.尽量避免大事务操作，提高系统并发能力。

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