MYSQL优化主要从四个方面入手。
设计:数据类型、存储引擎、范式、逆范式
功能:索引、分表、分区
架构:主从复制、读写分离、负载均衡
合理SQL:测试,经验

设计层

存储引擎

存储引擎:storageEngine,不同的数据库对象的存储机制方式。

MySQL支持很多的存储引擎,以支持不同的操作。

命令:show engines 即可查看MySQL所支持的所有存储引擎

MYISAM

存储方式
  • .frm : 数据表的结构文件
  • .MYD : 数据表的数据(记录)
  • .MYI :数据表的索引
特定功能
  • 支持全文索引(鸡肋)
  • 占用的存储空间小
  • 文件级别快速备份,可以将 数据库文件 拷贝到任意位置,需要还原时,拷贝回来即可。
并发处理能力

锁定力度:表级

并发插入,在表中执行其他操作时,可以同时处理插入工作,大多数情况是不需要考虑锁的问题,因为,插入不影响其他记录的操作,在最后追加插入。

空间空洞

在数据特别多的情况下,我们的文件也特别大,如果出现了大批量的删除工作,在MyISAM中执行,表中就会出现空洞。

修复方法:repair table table-name

INNODB

innodb这种引擎,与MYISAM引擎的区别很大。特别是它的数据存储格式等.
对于innodb的数据结构,首先要解决两个概念性的问题: 共享表空间以及独占表空间。

什么是共享表空间和独占表空间
共享表空间以及独占表空间都是针对数据的存储方式而言的。

共享表空间

某一个数据库的所有的表数据,索引文件全部放在一个文件中,默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1 初始化为10M。

  • 共享表空间:

    • 优点:
      可以放表空间分成多个文件存放到各个磁盘上(表空间文件大小不受表大小的限制,如一个表可以分布在不同步的文件上)。数据和文件放在一起方便管理。
    • 缺点:
      所有的数据和索引存放到一个文件中以为着将有一个很常大的文件,虽然可以把一个大文件分成多个小文件,但是多个表及索引在表空间中混合存储,这样对于一个表做了大量删除操作后表空间中将会有大量的空隙,特别是对于统计分析,日值系统这类应用最不适合用共享表空间。
独占表空间

每一个表都将会生成以独立的文件方式来进行存储,每一个表都有一个.frm表描述文件,还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容,默认情况下它的存储位置也是在表的位置之中。

独立表空间:在配置文件(my.cnf)中设置: innodb_file_per_table

  • 优点:

    • 每个表都有自已独立的表空间。
    • 每个表的数据和索引都会存在自已的表空间中。
    • 可以实现单表在不同的数据库中移动。
    • 空间可以回收(除drop table操作处,表空不能自已回收)
    • Drop table操作自动回收表空间,如果对于统计分析或是日值表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
    • 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
    • 对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。
  • 缺点:

    • 单表增加过大,如超过100个G。

相比较之下,使用独占表空间的效率以及性能会更高一点。

共享表空间以及独占表空间之间的转化

innodb_file_per_table 通过这个参数来实现的转化,如果为ON说明所使用的是独占表空间。

默认情况下,所使用的表空间为共享表空间。
图片1.png

对于之前使用过的共享表空间则不会影响,除非手动的去进行修改或者是
innodb_file_per_table=1 为使用独占表空间
innodb_file_per_table=0 为使用共享表空间

修改独占空表空间的数据存储位置
innodb_data_home_dir = "C:mysqldata"
innodb_log_group_home_dir = "C:mysqldata"
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table=1

参数说明:
**这个设置配置一个可扩展大小的尺寸为10MB的单独文件,名为ibdata1。没有给出文件的位置,所以默认的是在MySQL的数据目录内。【对数据来进行初始化的设置】
innodb_data_home_dir 代表为数据库文件所存放的目录
innodb_log_group_home_dir 为日志存放目录
innodb_file_per_table 是否使用共享以及独占表空间来

以上的几个参数必须在一起加入。
对于参数一些注意的地方
InnoDB不创建目录,所以在启动服务器之前请确认”所配置的路径目录”的确存在。这对你配置的任何日志文件目录来说也是真实的。使用Unix或DOS的mkdir命令来创建任何必需的目录。

存储方式
  • .frm:表结构
  • .ibd:数据
特定功能

支持外键、支持事物、支持行级锁定

并发处理能力

锁定力度:同时支持 表级和行级

多版本并发控制,MVCC,实现无阻塞读操作

总结

业务逻辑以大量的读和插入为主,少量的更新和删除,建议使用MyISAM

业务逻辑以大量的更新和删除为主,建议使用innoDB,此时需要增加写锁(独占锁,阻塞其他的所有操作)

字段类型

*尽可能使用占用存储空间较少的类型

举个例子:

如果我们要存储一个名字,一般varchar(16)可能就够了。

但是确定以了一个varchar(32)的就没有必要了。

整型的类型 一般使用int 和 tinyint,因为它们以其他语言基本是一致的。

在MySQL中没有布尔型的概念,一般使用0,1表示。

尽可能使用定长数据,占用固定的存储空间

定长:整型,永远占4个字节,无论存储0,-1,2135435占用的空间都是一样的。

变长:varchar,占用的空间会随着内容的增长而增大。

char与varchar

char 速度快,浪费空间

varchar 速度慢,节约空间

text,文本,text中的数据,不被记录到总的记录长度内,MySQL中,对一条记录的长度有限制,默认65535

decimal(定点数)与double(双精度浮点数)

decimal:数据精确,但是是变长数据类型,随着存储的数据小数位的增加,而占用更多的空间。

double:精度丢失,但是是定长数据类型,无论小数位有多少,永远占8个字节。(float:4个字节)

尽可能使用整型,而少用字符串

整型数据的运算有很明显的优势。

枚举:enum,使用一个数表示一个枚举值,占用2个字节,一共2^16个枚举值

集合:set,使用一个位表示一个元素值,占用8个字节,一共8*8个元素值

进行IP的存储,选择无符号的整数存储 : int unsigned

inet_aton()将一个字符串的IP地址,转换为一个32位的网络序列地址
图片2.png

inet_aton()将一个32位的网络序列地址,转换为一个以 . 相隔的字符串IP地址
图片3.png

范式

一、原子性

字段必须独立

heightnameage
180sam18
196kobe18

二、消除主键部分依赖

  • 主键:记录的标识,确定该标识,就可以定位对应的记录,primary key,主键可以由一个或多个字段构成。
  • 部分依赖:表中的非主键字段,可以由主键中的部分字段来确定,非主键字段依赖于部分主键字段。存在对主键的部分依赖关系。
pkheightnameage
1180sam18
2196kobe18

消除传递依赖

  • 非主键字段,依赖其他的非主键字段,称为传递依赖。
  • 将一类的实体,使用独立的表进行管理,存在逻辑关联的,使用主键进行关联即可。
  • 减少数据冗余。
pkheightnameagecity_id
1180sam182
2196kobe183

| city_id | city_name |
| --- | --- |
| 2 | 西安 |
| 3 | 洛杉矶 |

逆范式

  • 增加数据冗余
  • 方便查询

举例:获取所有商品分类下的商品数量

| c_id | c_name |
| --- | --- |
| 1 | 服装 |
| 2 | 电子产品 |

g_idg_namec_id
1Sam短袖1
2Sam九分裤1
3apple8手机2
SELECT c.c_name, COUNT(g_id) AS num FROM categroy AS c LEFT JOIN goods AS g ON c.c_id = g.c_id GROUP BY c.c_id;

通常情况都会使用连接查询来进行统计数据集,但是,假如商品数量较大,那么就比较耗性能了。
可以在商品分类表冗余

c_idc_namegoods_count
1服装2
2电子产品1

功能方面

索引

关键字与记录位置的映射关系,称之为索引。

  • 优点:

    • 加快查询
  • 缺点:

    • 增加维护成本,占用磁盘空间

索引类型

  • 普通索引(index):对关键字没有要求
  • 唯一索引(unique key):要求关键字不能重复,可以为NULL。
  • 主键索引(primary key):要求关键字不能重复,不能为NULL。
  • 全文索引(fulltext key):全文索引, 获取关键字的方式, 不是全部的字段内容, 而是字段中的部分关键词。
    由于MySQL, 不支持中文的分词系统, MySQL的全文索引几乎无用。
  • 复合索引:提取多个字段建立索引。

索引的数据类型

  • B-Tree索引
    磁盘上,存储索引的通用结构,所有的数据库软件都用。
  • hash索引
    就是hash表,就是关联数组,就是键值对列表。

数据在内存中常用的存储结构。
有时MySQL会缓存索引,将常用的索引,存储在内存中。
缓存在内存中的索引,存储成hash结构。

  • B+Tree 聚簇索引
    B+Tree,是B-Tree的变种。

就是指记录数据和索引关键字存储在一起的形式。
在innodb中主键索引,就是聚簇的。只有这一个索引是聚簇的。
非主键索引是关键字与记录位置的映射关系。

如何建立索引

最基本的就是在查询字段,排序字段,关联字段上建立索引。
建立更好的索引需要在:

表的结构,表的关联关系,统计表中执行的SQL、SQL的执行频率、SQL的权重。

更多的还是依据业务逻辑。

通过测试才能够衡量索引是否优秀。

SQL执行计划(explain)

explain通过这个关键字可以看到一条SQL的执行计划。

图片4.png

  • possible_keys: 可能用到的检索索引
  • key:真正使用的索引
  • rows:估计获取多少条记录才能获取查询结果

没用到索引
图片6.png

索引覆盖

当查询的数据,在索引中,就可以全部获取时,SQL选择使用索引来完成查询,而不使用数据记录。
图片7.png

OR原则

or 左右的条件都有索引可用时,才会使用索引。

左原则

  • like,模糊查询时,保证匹配的字符串左侧不能是通配符。
    图片8.png
  • 复合索引中字段的地位是不同的,左边的索引才可以用索引,右边的字段不能使用索引,除非左边字段先确定。
    图片9.png

图片10.png
图片11.png

查询缓存(QueryCache)

MySQL服务器内部,存在自己的缓存区,用于缓存select查询结果。

当缓存开启时,所有执行的SQL,就会被自动缓存,下次执行相同的SQL语句时,直接去缓存中获取即可。
图片12.png

图片13.png

图片14.png

WX20200912-164002@2x.png

查询缓存对于客户端是透明的,不用在服务器端是否开启了缓存。

如果希望使用MySQL的查询缓存,需要保证你的SQL语法的一致性,因为MySQL服务器依赖于SQL语法判断缓存是否存在,包括 大小写,空格等。

SQL存在一个提示,可以使当前的SQL不被缓存。

SELECT sql_no_cache field FROM table_name;

分区

将一张表的数据,分散在不同的数据索引存储文件中。
图片15.png

取余分区

  • HASH:使用给定的一个整数,进行取余,一般对主键进行取余。
    图片16.png

图片17.png

  • KEY:使用任意字段,对分区数据取余分区。<span style="color:#FF0000">不要求是整数字段,但是必须是主键的一部分。</span>
    图片18.png

条件分区

  • RANGE:利用给定的范围条件,进行分区
    图片19.png
  • LIST:利用给定的列表值,进行分区
    图片20.png

分区操作

增加分区

# 取余分区
alter table table_name add partition partitions number;
# 条件分区
alter table table_name add partition (condition);

减少分区

# 取余分区
alter table table_name coalesce partition number;
# 条件分区
alter table table_name drop partition 分区名
增加、删除分区对数据的影响

取余分区:在增加、删除某分区后,会重新分配数据到不同的分区,不会对数据产生影响。

条件分区:在增加分区时,数据不会受到影响,在删除时,会导致分区内的数据一同丢失,等价于删除了一张表。

分区总结

依据业务逻辑分区:list,range

如果有明显的业务逻辑界限,必须先做业务逻辑分区。

平均分配:hash key

当没有特别明显的业务逻辑界限时,依据最常用的检索添加进行分区。

分表

通过引用程序,完成分表算法,将数据存储在结构相同的多个表中,称之为分表。

原因

  1. 不支持分区
  2. 分区已经不能满足已有的业务逻辑了

水平分表

分区和分表,都属于水平分表的一种,表中的记录减少,速度就会加快。
举例

创建表:设置相同的表名前缀,后面用数字表示。如:table_1,table_2,table_3,table_4

算法:通过ID取模表的个数,得到的余数就是所在的某个表,拼接上前缀即可。

现在有4张结构相同的表。

  • 查询:
    我们想取出ID=23的记录。

利用算法查询即可

  • 插入:
    因为ID是在插入之后自动生成的。

    1. 得到每张表的最大ID进行对比
    2. 创建一个专门存储ID的表,在每次插入数据之前,在存储ID的表中先生成ID,然后得到该ID,使用上面的取余方法,获得表名,插入数据即可。

垂直分表

更改表的结构,减少字段,每条记录的长度减少后,所有的操作都会加快。

user

user_idnameage
1sam18
2kobe18

wechat_user

user_idnicknameopenid
1sam
2kobe

SQL语句优化

下面是个人日常工作中碰到或者其他大佬博主学习到的优化方法。

  • 索引不要建立在大量的重复数据字段上,如性别。
  • 索引不是越多越好,一张表的索引建议不要超过5个,如果太多请考虑索引是否合理。
  • 字段默认值尽量不要为NULL,where子句可以使用默认值作为条件不要用NULL
  • 大量删除、更新数据时,建议分批次处理,否则会导致CPU占用过高,影响其他业务读取的性能
  • 查询一条数据或者聚合查询时,建议加上LIMIT 1
  • 尽量避免在索引上使用MYSQL内置函数
  • 如果索引列是字符串的话,建议用引号引起来,这样才能使用到索引
  • 最左原则不是指查询条件顺序,而是查询条件中是否用到索引的最左列
  • 使用连接查询(join)代替子查询
  • 对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
  • 应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描
  • in 和 not in 也要慎用,否则会导致全表扫描,对于连续的数值,能用 between 就不要用 in 了,很多时候用 exists 代替 in 是一个好的选择。

    select num from a where exists(select 1 from b where num=a.num);
  • 应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。
  • 避免使用*进行查询
  • limit offset 需要注意在select的字句中,where group having order by limit 是顺序执行,而limit是最后才执行的,但是这时数据其实已经取出来了,如果添加了offset 就说明要放弃其中的部分数据。在数据量特别大的时候就会非常慢。

    # 使用where条件进行优化
    SELECT field1, field2 FROM table_name WHERE id > 100000000 limit 10;
    # 在无法实现条件过滤的情况下,可以使用缓存。
  • 默认分组(group by)查询时,会进行自动排序,如果不需要排序拼接子句order by null即可
  • 获取不同数据时,使用单表查询还是连表查询

    • 单表查询:
      优势:单表锁时间短。利于并发

    劣势:SQL数量多。

    • 连表查询:
      优势:SQL数量少。

    劣势:表锁时间长,不利于并发。

  • 。。。

架构

主从复制

推荐阅读文章。

原理

  1. 从库配置启动I/O进程监听主库的binlog二进制文件。
  2. 主库执行前将此次更新记录到binlog二进制文件中,记录完成后执行此次更新。
  3. 从库在监听到binlog二进制文件有更新时,获取其中数据记录到relay-log日志中。
  4. 从库SQL进程读取到relay-log日志中的信息,在本地执行该信息内容。

配置

# 主库
[mysqld]
server-id 107 # 服务器设置唯一ID,默认为1,推荐取IP最后部分;
log-bin=mysql-bin # 设置二进制日志文件的基本名,默认不开启,配置后表示开启日志;
binlog-ignore-db = mysql,information_schema  #忽略写入binlog的库


# 从库
server-id 108 # 服务器设置唯一ID,默认为1,推荐取IP最后部分;
replicate-do-db=test # 需要同步的数据库

命令

从库配置主库信息

mysql> CHANGE MASTER TO
->   MASTER_HOST='192.168.0.107',
->   MASTER_PORT=3306,
->   MASTER_USER='slave',
->   MASTER_PASSWORD='slave';

主库配置从库账号

mysql> mysql -u root -p password
mysql> GRANT REPLICATION SLAVE ON *.* to ‘slave’@‘192.168.1.2’ identified by ‘slave’; #replication:复制

读写分离

在主从复制的基础上,进行读写分离的配置。目前常见的读写分离中间件有:Amoeba,MySQL-Proxy。

MySQL-Proxy:推荐阅读文章。

Amoeba:推荐阅读文章。

最后修改:2020 年 10 月 14 日 04 : 09 PM
如果觉得我的文章对你有用,请随意赞赏