mysql优化

发表于 | 分类于 | | 本文总阅读数 次

mysql体系结构

存储引擎

简介：

常用引擎对比

功能 MylSAM MEMORY InnoDB Archive
存储限制 256TB RAM 64TB None
支持事务 No No Yes No
支持全文索引 Yes No No No
支持树索引 Yes Yes Yes No
支持哈希索引 No Yes No No
支持数据缓存 No N/A Yes No
支持外键 No No Yes No

可以根据以下的原则来选择 MySQL 存储引擎：

  • 如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
  • 如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
  • 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
  • 如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

索引

简介：索引是帮助mysql高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护这满足特定查找的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优点：

  • 提高数据检索的效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点：

  • 索引列也是要占用空间的
  • 索引大大提高了查询效率，同时却也降低更新表的速度

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

索引结构 描述
B+Tree索引 最常见的索引类型，大部分引擎都支持B+树索引
Hash索引 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree（空间索引） 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text（全文索引） 是一种通过建立倒排索引，快速匹配文档的方式。类似于Licene，Solr，ES
索引 InnoDB MyISAM Memory
B+tree索引 支持 支持 支持
Hash索引 不支持 不支持 支持
R-tree索引 不支持 支持 不支持
Full-text 5.6版本之后支持 支持 不支持

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的连表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

思考题：

为什么InnoDB存储引擎选择使用B+Tree索引结构？

  • 相对于二叉树，层级更少，搜索效率高；
  • 对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
  • 相对Hash索引，B+Tree支持范围匹配以及排序操作；

索引分类

分类 含义 特点 关键字
主键索引 针对于表中主键创建的索引 默认自动创建，只能有一个 PRIMARY
唯一索引 避免同一个表中某数据列中的值重复 可以有多个 UNIQUE
常规索引 快速定位特定数据 可以有多个
全文索引 全文索引查找的是本文中的关键字，而不是比较索引中的值 可以有多个 FULLTEXT

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类 含义 特点
聚集索引 将数据存储与索引放在一块，索引结构的叶子节点保存了行数据 必须有，而且只有一个
二级索引 将数据与索引分开存储，索引结构叶子节点关联的是对应的主键 可以存在多个

聚集索引选取规则：

  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

索引失效

  • 索引列运算

    不要在索引列上进行运算操作，否则索引将失效

  • 字符串不加引号

    字符串类型字段使用时，不加引号，索引将失效。

  • 模糊查询

    如果仅仅是在尾部模糊匹配，索引不会失效。如果头部模糊匹配，索引失效。

  • or连接的条件

    用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

索引语法

  • 创建索引

  • 查看索引

  • 删除索引

索引设计原则

  1. 针对于数据量较大，且查询比较频繁的表建立索引。

  2. 针对于常作为查询条件（where)、排序（order by)、分组(group by)操作的字段建立索引。

  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

  7. 如果索引列不能存储NULL值，请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个
    索引最有效地用于查询。

sql性能分析

  • sql执行频率

    mysql客户端连接成功后，通过show[session|global]status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的insert、update、delete、select的访问频次：

    1
    show global status like 'Com_______'   # 七个下划线

  • 慢查询日志

    慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有sql语句的日志。

    mysql的慢查询日志默认没有开启，需要在mysql的配置文件（/etc/my.cnf）中配置如下信息：

    1
    2
    3
    4
    # 开启mysql慢日志查询开关
    slow_query_log=1
    #设置慢日志的时间为2秒，sql语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
    long_query_time=2

    配置完毕之后，通过以下指令重新启动mysql服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log

  • profile详情

    show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作:

    1
    select @@have_profiling;

    默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：

    1
    set profiling=1;
    1
    2
    3
    4
    5
    6
    # 查看每一条sql的耗时基本情况
    show profiles;
    # 查看指定query_id的sql语句每个阶段的耗时情况
    show profile for query query_id;
    # 查看指定query_id的申请了语句CPU的使用情况
    show profile cpu for query query_id;

  • explain执行计划

    EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

    语法：

    1
    2
    # 直接在select语句之前加上关键字explain/desc
    explain select 字段列表 from 表名 where 条件;

sql优化

插入数据

  • insert优化

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    insert into tb_test values(1,'tom');
    insert into tb_test values(2,'cat');
    insert into tb_test values(3,'jerry');
    ......

    # 优化方法
    # 批量
    insert into tb_test values(1,'tom'),(2,'cat'),(3,'jerry');

    # 手动提交事务
    start transaction;
    insert into tb_test values(1,'tom'),(2,'cat'),(3,'jerry');
    insert into tb_test values(4,'tom'),(5,'cat'),(6,'jerry');
    insert into tb_test values(7,'tom'),(8,'cat'),(9,'jerry');
    commit;

    # 主键顺序插入
    主键乱序插入：8 1 9 21 88 2 4 15 89 5 7 3
    主键顺序插入：1 2 3 4 5 6 7 8 9 15 21 88 89

  • 大批量插入数据

    如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

    1
    2
    3
    4
    5
    6
    7
    # 客户端连接服务端时，加上参数--local-infile
    mysql --local-infile -u root -p
    # 设置全局参数local——infile为1，开启从本地加载文件导入数据的开关
    # 查看select @@local_infile
    set global local_infile=1;
    # 执行load指令将准备好的数据，加载到表结构中
    load data local infile 'root/sql.log' into table 'tb_user' fields terminated by ',' lines teminated by '/n';

主键优化

  • 数据组织方式

    在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）。

  • 页分裂

    也可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据多大，会行溢出），根据主键排列。

  • 页合并

    当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

    当页中删除的记录达到MERGE THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

  • 主键设计原则
    • 满足业务需求的情况下，尽量降低主键的长度。
    • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
    • 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
    • 业务操作时，避免对主键的修改。

order by优化

  1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
  2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
1
2
3
4
5
6
7
8
# 没有创建索引时，根据age, phone进行排序
explain select id,age,phone from tb_user order by age, phone;
# 创建索引
create index idx_user_age_phone_aa on tb_user(age,phsne);
# 创建索引后，根据age, phone进行升序排序
explain select id,age,phone from tb_user order by age, phone;
# 创建索引后，根据age, phone进行降序排序
explain select id,age,phone from tb_user order by age desc, phone desc;
1
2
3
4
5
6
# 根据age, phone进行降序一个升序，一个降序
explain select id,age,phone from tb_user order by age asc , phone desc;
# 创建索引
create index idx_user_age _phone_ad on tb_user(age asc ,phone desc);
# 根据age, phone进行降序一个升序，一个降序公
explain select id,age,phone from tb_user order by age asc , phone desc;

  • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

  • 尽量使用覆盖索引。

  • 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。

  • 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

group by优化

1
2
3
4
5
6
7
8
9
10
#删除掉目前的联合索引 idx_user_pro_age_sta
drop index idx_user_pro_age_sta on tb_user;
#执行分组操作，根据profession字段分组
explain select profession , count(*) from tb_user group by profession ;
#创建索引
Create index idx_user_pro_age_sta on tb_user(profession , age , status);
#执行分组操作，根据profession字段分组
explain select profession , count(*) from tb_user group by profession;
#执行分组操作，根据profession字段分组
explain select profession , count(*) from tb_user group by profession, age;
  • 在分组操作时，可以通过索引来提高效率
  • 分组操作时，索引的使用也是满足最左前缀法则的

limit优化

一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

1
explain select *from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where tid = a.id;

count优化

  • MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；
  • InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

优化思路：自己计数。

  • count的几种用法
    • count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。
    • 用法:count (*) 、count(主键)、count(字段)、count ( 1)
      • count （主键)
        InnoDB引擎会遍历整张表，把每一行的主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)。
      • count（字段)
        没有not null约束: InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null约束: InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
      • count ( 1)
        InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
      • count (*)
        InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累

按照效率排序的话，count(字段)<count(主键id)<count(1) s count(*)。

所以尽量使用count(*)

update优化

1
2
update student set no='2000100100'where id = 1;
update student set no ='2000100105'where name='韦一笑";

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁,并且该索引不能失效，否则会从行锁升级为表锁。

小结

  1. 插入数据
    insert:批量插入、手动控制事务、主键顺序插入

    大批量插入: load data local infile

  2. 主键优化
    主键长度尽量短、顺序插入
    AUTO_INCREMENT UUID

  3. order by优化
    using index:直接通过索引返回数据，性能高

    using filesort:需要将返回的结果在排序缓冲区排序

  4. group by优化

    索引，多字段分组满足最左前缀法则

  5. limit优化
    覆盖索引＋子查询

  6. count优化
    性能: count(字段)<count(主键id)<count(1) s count(*)

  7. update优化
    尽量根据主键/索引字段进行数据更新

日志

错误日志

错误日志是MySQL中最重要的日志之一，它记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

该日志是默认开启的，默认存放目录/var/log/，默认的日志文件名为mysqld.log。查看日志位置:

1
show variables like '%log_error%'

二进制日志

二进制日志（BINLOG)记录了所有的DDL(数据定义语言）语句和DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW)语句。

作用：①.灾难时的数据恢复;

​ ②.MySQL的主从复制。在MySQL8版本中，默认二进制日志是开启着的，涉及到的参数如下:

1
show variables like '%log_bin%'

  • 日志格式

    MySQL服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：

    日志格式 含义
    STATEMENT 基于SQL语句的日志记录，记录的是sQL语句，对数据进行修改的sQL都会记录在日志文件中。
    ROW 基于行的日志记录，记录的是每一行的数据变更。（默认
    MIXED 混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录。 
    1
    show variables like '%binlog_format%'

  • 日志查看

    由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具mysqlbinlog 来查看，具体语法:

    1
    2
    3
    4
    5
    6
    mysqlbinlog[参数选项] logfilename
    参数选项:
    -d   指定数据库名称，只列出指定的数据库相关操作。
    -o   忽略掉日志中的前n行命令。
    -v   将行事件(数据变更)重构为SQL语句
    -w   将行事件(数据变更)重构为5QL语句，并输出注释信息

  • 日志删除

    对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：

    指令 含义
    reset master 删除全部binlog日志，删除之后,日志编号，将从binlog.000001重新开始
    purge master loas to ‘binlnn.********* 删除*****编号之前的所有日志
    purge master logs before ‘yyyy-mm-dd hh24:mi:ss’ 删除日志为”yyyy-mm-dd hh24:mi:ss”之前产生的所有日志

查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置以下配置︰

1
show variables like '%general%';

修改mysql的配置文件/etc/my.cnf文件，添加如下内容：

1
2
3
4
#该选项用来开启查询日志,可选值:0或者1;0代表关闭，1代表开启
general_log=1
#f设置日志的文件名,如果没有指定，默认的文件名为host_name.log
general_log_file=mysql_query.log

慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time 设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志，默认未开启。long_query_time默认为10秒，最小为0，精度可以到微秒。

1
2
3
4
#慢查询日志
slow query log=1
#执行时间参数
long query time=2

主从复制

主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

MySQL复制的有点主要包含以下三个方面:

  • 主库出现问题，可以快速切换到从库提供服务。

  • 实现读写分离，降低主库的访问压力。

  • 可以在从库中执行备份，以避免备份期间影响主库服务。

原理

mysql主从复制原理如下。

从上图来看，复制分成三步:

  1. Master主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
  2. 从库读取主库的二进制日志文件Binlog，写入到从库的中继日志Relay Log 。
  3. slave重做中继日志中的事件，将改变反映它自己的数据。

分库分表

  • 问题分析

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈:

  1. IO瓶颈:热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求数据太多带宽不够，网络IO瓶颈。
  2. CPU瓶颈:排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多CPU出现瓶颈。

分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

拆分策略

  • 垂直拆分

    垂直分库:

    以表为依据，根据业务将不同表拆分到不同库中。特点:

    1.每个库的表结构都不一样。

    2.每个库的数据也不一样。

    3.所有库的并集是全量数据。

    垂直分表:

    以字段为依据，根据字段属性将不同字段拆分到不同表中。特点:

    1.每个表的结构都不一样。

    2.每个表的数据也不一样，一般通过一列（主键/外键）关联。3．所有表的并集是全量数据。

  • 水平拆分

    水平分库:

    以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。
    特点:
    1.每个库的表结构都一样。

    2.每个库的数据都不一样。

    3.所有库的并集是全量数据。

    水平分表:

    以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。
    特点:
    1.每个表的表结构都一样。

    2.每个表的数据都不一样。

    3．所有表的并集是全量数据。

  • 实现技术

    MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及shardingJDBC。

mycat分片

  • 垂直分片

    场景：

    准备：

    分别再三台mysql中创建数据库shopping

    配置：

读写分离

读写分离,简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。

通过MyCat即可轻易实现上述功能，不仅可以支持MySQL，也可以支持Oracle和SQL Server。

分享到： 收藏夹 复制网址 邮件 微信 QQ空间 腾讯微博 豆瓣 一键分享 更多