Mysql 日常优化小技巧汇总

475人浏览 2023-10-07

mysql 优化

一.SQL优化
》定位执行效率低的SQL语句
    1. 通过慢查询日志定位慢SQL,用--log-slow-queries[=file_name]选项启动时,mysqld会写一个所有执行时间超过long_query_time秒的SQL语句的日志文件。
    2. 使用SHOW FULL PROCESSLIST; 查看当前MySQL在进行的线程,同时对一些锁表操作进行优化。
    3. 通过EXPLAIN分析慢SQL
       语法:EXPLAIN SQL语句

》SQL查询优化
  常用举例:

    1.Like查询时不要以%开头,否则数据库引擎会放弃使用索引而进行全表扫描。
        select * from tbl_name where field_name like '%value%';
        不会使用索引
        select * from tbl_name where field_name like 'value%';
        会使用索引
    2.在查询时,如果查询的字段值为空,则不要使用空值查询,而应该使用is null查询。

    3.where条件不符合最左原则
    假设有这样一个索引——(a,b,c),必须用到第一个字段a,才会走索引。否则如select * from table where b='b' and c='c',就不会使用到索引。
    有时也可以在给b和c分别添加索引。


    4.索引列使用 != 或 <> 操作符时,数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效。

    5.在where子句中对索引列进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。
        如:select * from table where a=b+c;
    6.在where子句中对索引列进行null值判断,引擎会放弃使用索引而进行全表扫描。
        如:select * from table where a is null;
        可以在note 上设置默认值0,确保表中note 列没有null值,然后这样查询:
        select * from table where a=0;

    7.在where子句中使用or来连接条件,导致引擎放弃使用索引而进行全表扫描
        如:select * from table where a=1 or a=2;
      替代方案:
        select * from table where a in (1,2);
        注意:SQL语句中IN包含的值不应过多 --

    8.能用DISTINCT的就不用GROUP BY
        如:select name from table group by name;
        可改为:
        select distinct name from table;
    9.只取需要的列,避免SELECT *
        如:select * from table;
        可改为:
        select name,age from table;
    10.当只需要一条数据的时候,使用limit 1  --
        如:select * from table limit 1;

    11 尽量避免负向查询 (NOT、!=、<>、!<、!>、NOT IN、NOT LIKE)

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

    13.Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。

    14.select count(*) from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义。

二.数据表设计优化
    设计表时应该注意:
    2.使用 varchar/nvarchar 代替 char/nchar
    1.表字段避免null值出现,null值很难查询优化且占用额外的索引空间,推荐默认数字0代替null。
    3.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
    4.尽量使用INT而非BIGINT,如果非负则加上UNSIGNED(这样数值容量会扩大一倍),当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
    5.使用枚举或整数代替字符串类型
    6.尽量使用TIMESTAMP而非DATETIME
    7.单表不要有太多字段,建议在20以内
    8.用整型来存IP
    9.索引并不是越多越好,要根据查询有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全表扫描


分表

  数据量的日剧增加,数据库中某个表有几百万条数据,导致查询和插入耗时太长,怎么能解决单表压力呢?你就该考虑是否把这个表拆分成多个小表,来减轻单个表的压力,提高处理效率,此方式称为分表。

  分表技术比较麻烦,要修改程序代码里的SQL语句,还要手动去创建其他表,也可以用merge存储引擎实现分表,相对简单许多。分表后,程序是对一个总表进行操作,这个总表不存放数据,只有一些分表的关系,以及更新数据的方式,总表会根据不同的查询,将压力分到不同的小表上,因此提高并发能力和磁盘I/O性能。

  分表分为垂直拆分和水平拆分:

  垂直拆分:把原来的一个很多字段的表拆分多个表,解决表的宽度问题。你可以把不常用的字段单独放到一个表中,也可以把大字段独立放一个表中,或者把关联密切的字段放一个表中。

  水平拆分:把原来一个表拆分成多个表,每个表的结构都一样,解决单表数据量大的问题。

分库

  分库是根据业务不同把相关的表切分到不同的数据库中,比如web、bbs、blog等库。如果业务量很大,还可将切分后的库做主从架构,进一步避免单个库压力过大。
  略过

三.其他优化

使用缓存

使用连接池
    》连接池的好处
    1.资源重用
    由于数据库连接得到重用,避免了频繁创建、释放连接引起的大量性能开销。在减少系统消耗的基础上,另一方面也增进了系统运行环境的平稳性(减少内存碎片以及数据库临时进程/线程的数量)。
    2.更快的系统响应速度一.SQL优化
               》定位执行效率低的SQL语句
                   1. 通过慢查询日志定位慢SQL,用--log-slow-queries[=file_name]选项启动时,mysqld会写一个所有执行时间超过long_query_time秒的SQL语句的日志文件。
                   2. 使用SHOW FULL PROCESSLIST; 查看当前MySQL在进行的线程,同时对一些锁表操作进行优化。
                   3. 通过EXPLAIN分析慢SQL
                      语法:EXPLAIN SQL语句
               
               》SQL查询优化
                 常用举例:
               
                   1.Like查询时不要以%开头,否则数据库引擎会放弃使用索引而进行全表扫描。
                       select * from tbl_name where field_name like '%value%';
                       不会使用索引
                       select * from tbl_name where field_name like 'value%';
                       会使用索引
                   2.在查询时,如果查询的字段值为空,则不要使用空值查询,而应该使用is null查询。
               
                   3.where条件不符合最左原则
                   假设有这样一个索引——(a,b,c),必须用到第一个字段a,才会走索引。否则如select * from table where b='b' and c='c',就不会使用到索引。
                   有时也可以在给b和c分别添加索引。
               
               
                   4.索引列使用 != 或 <> 操作符时,数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效。
               
                   5.在where子句中对索引列进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。
                       如:select * from table where a=b+c;
                   6.在where子句中对索引列进行null值判断,引擎会放弃使用索引而进行全表扫描。
                       如:select * from table where a is null;
                       可以在note 上设置默认值0,确保表中note 列没有null值,然后这样查询:
                       select * from table where a=0;
               
                   7.在where子句中使用or来连接条件,导致引擎放弃使用索引而进行全表扫描
                       如:select * from table where a=1 or a=2;
                     替代方案:
                       select * from table where a in (1,2);
                       注意:SQL语句中IN包含的值不应过多 --
               
                   8.能用DISTINCT的就不用GROUP BY
                       如:select name from table group by name;
                       可改为:
                       select distinct name from table;
                   9.只取需要的列,避免SELECT *
                       如:select * from table;
                       可改为:
                       select name,age from table;
                   10.当只需要一条数据的时候,使用limit 1  --
                       如:select * from table limit 1;
               
                   11 尽量避免负向查询 (NOT、!=、<>、!<、!>、NOT IN、NOT LIKE)
               
                   12.使用连接(JOIN)来代替子查询(Sub-Queries)
               
                   13.Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。
               
                   14.select count(*) from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义。
               
               二.数据表设计优化
                   设计表时应该注意:
                   2.使用 varchar/nvarchar 代替 char/nchar
                   1.表字段避免null值出现,null值很难查询优化且占用额外的索引空间,推荐默认数字0代替null。
                   3.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
                   4.尽量使用INT而非BIGINT,如果非负则加上UNSIGNED(这样数值容量会扩大一倍),当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
                   5.使用枚举或整数代替字符串类型
                   6.尽量使用TIMESTAMP而非DATETIME
                   7.单表不要有太多字段,建议在20以内
                   8.用整型来存IP
                   9.索引并不是越多越好,要根据查询有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全表扫描
               
               
               分表
               
                 数据量的日剧增加,数据库中某个表有几百万条数据,导致查询和插入耗时太长,怎么能解决单表压力呢?你就该考虑是否把这个表拆分成多个小表,来减轻单个表的压力,提高处理效率,此方式称为分表。
               
                 分表技术比较麻烦,要修改程序代码里的SQL语句,还要手动去创建其他表,也可以用merge存储引擎实现分表,相对简单许多。分表后,程序是对一个总表进行操作,这个总表不存放数据,只有一些分表的关系,以及更新数据的方式,总表会根据不同的查询,将压力分到不同的小表上,因此提高并发能力和磁盘I/O性能。
               
                 分表分为垂直拆分和水平拆分:
               
                 垂直拆分:把原来的一个很多字段的表拆分多个表,解决表的宽度问题。你可以把不常用的字段单独放到一个表中,也可以把大字段独立放一个表中,或者把关联密切的字段放一个表中。
               
                 水平拆分:把原来一个表拆分成多个表,每个表的结构都一样,解决单表数据量大的问题。
               
               分库
               
                 分库是根据业务不同把相关的表切分到不同的数据库中,比如web、bbs、blog等库。如果业务量很大,还可将切分后的库做主从架构,进一步避免单个库压力过大。
                 略过
               
               
               
               三.其他优化
               
               使用缓存
               
               使用连接池
                   》连接池的好处
                   1.资源重用
                   由于数据库连接得到重用,避免了频繁创建、释放连接引起的大量性能开销。在减少系统消耗的基础上,另一方面也增进了系统运行环境的平稳性(减少内存碎片以及数据库临时进程/线程的数量)。
                   2.更快的系统响应速度
                   数据库连接池在初始化过程中,往往已经创建了若干数据库连接置于池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言,直接利用现有可用连接,避免了数据库连接初始化和释放过程的时间开销,从而缩减了系统整体响应时间。
                   3.统一的连接管理,避免数据库连接泄漏
                   在较为完备的数据库连接池实现中,可根据预先的连接占用超时设定,强制收回被占用连接。从而避免了常规数据库连接操作中可能出现的资源泄漏。
               
                   》连接池的缺点
                   1.连接池的初始化时间
                   初始化连接池的过程非常耗时,因此在应用启动时,应该把连接池初始化工作放在最早的时间点,以避免应用启动时的连接池初始化时间影响应用的响应速度。
                   2.连接池的管理
                   在连接池的管理上,连接池的管理是一个非常耗时的操作,因此在应用中,应该把连接池的管理放在最早的时间点,以避免应用中的连接池管理时间影响应用的响应速度。
    数据库连接池在初始化过程中,往往已经创建了若干数据库连接置于池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言,直接利用现有可用连接,避免了数据库连接初始化和释放过程的时间开销,从而缩减了系统整体响应时间。
    3.统一的连接管理,避免数据库连接泄漏
    在较为完备的数据库连接池实现中,可根据预先的连接占用超时设定,强制收回被占用连接。从而避免了常规数据库连接操作中可能出现的资源泄漏。

    》连接池的缺点
    1.连接池的初始化时间
    初始化连接池的过程非常耗时,因此在应用启动时,应该把连接池初始化工作放在最早的时间点,以避免应用启动时的连接池初始化时间影响应用的响应速度。
    2.连接池的管理
    在连接池的管理上,连接池的管理是一个非常耗时的操作,因此在应用中,应该把连接池的管理放在最早的时间点,以避免应用中的连接池管理时间影响应用的响应速度。

推荐文章

Mysql8.0: SELECT list is not in GROUP BY clause and contains nonaggregated column
2020-09-10
MySQL千万级数据数据库设计优化解决方案
2021-03-11
Mysql Group by 后取最新插入的一条数据。
2021-11-08
搜索文章