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说明所使用的是独占表空间。
默认情况下,所使用的表空间为共享表空间。
对于之前使用过的共享表空间则不会影响,除非手动的去进行修改或者是
innodb_file_per_table=1 为使用独占表空间
innodb_file_per_table=0 为使用共享表空间
修改独占空表空间的数据存储位置
innodb_data_home_dir = “C:\mysql\data"
innodb_log_group_home_dir = “C:\mysql\data"
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位的网络序列地址
inet_aton()将一个32位的网络序列地址,转换为一个以 . 相隔的字符串IP地址
范式
一、原子性
字段必须独立
height | name | age |
---|---|---|
180 | sam | 18 |
196 | kobe | 18 |
二、消除主键部分依赖
- 主键:记录的标识,确定该标识,就可以定位对应的记录,primary key,主键可以由一个或多个字段构成。
- 部分依赖:表中的非主键字段,可以由主键中的部分字段来确定,非主键字段依赖于部分主键字段。存在对主键的部分依赖关系。
pk | height | name | age |
---|---|---|---|
1 | 180 | sam | 18 |
2 | 196 | kobe | 18 |
消除传递依赖
- 非主键字段,依赖其他的非主键字段,称为传递依赖。
- 将一类的实体,使用独立的表进行管理,存在逻辑关联的,使用主键进行关联即可。
- 减少数据冗余。
pk | height | name | age | city_id |
---|---|---|---|---|
1 | 180 | sam | 18 | 2 |
2 | 196 | kobe | 18 | 3 |
city_id | city_name |
---|---|
2 | 西安 |
3 | 洛杉矶 |
逆范式
- 增加数据冗余
- 方便查询
举例:获取所有商品分类下的商品数量
c_id | c_name |
---|---|
1 | 服装 |
2 | 电子产品 |
g_id | g_name | c_id |
---|---|---|
1 | Sam短袖 | 1 |
2 | Sam九分裤 | 1 |
3 | apple8手机 | 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_id | c_name | goods_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的执行计划。
- possible_keys: 可能用到的检索索引
- key:真正使用的索引
- rows:估计获取多少条记录才能获取查询结果
没用到索引
索引覆盖
当查询的数据,在索引中,就可以全部获取时,SQL选择使用索引来完成查询,而不使用数据记录。
OR原则
or 左右的条件都有索引可用时,才会使用索引。
左原则
- like,模糊查询时,保证匹配的字符串左侧不能是通配符。
- 复合索引中字段的地位是不同的,左边的索引才可以用索引,右边的字段不能使用索引,除非左边字段先确定。
查询缓存(QueryCache)
MySQL服务器内部,存在自己的缓存区,用于缓存select查询结果。
当缓存开启时,所有执行的SQL,就会被自动缓存,下次执行相同的SQL语句时,直接去缓存中获取即可。
查询缓存对于客户端是透明的,不用在服务器端是否开启了缓存。
如果希望使用MySQL的查询缓存,需要保证你的SQL语法的一致性,因为MySQL服务器依赖于SQL语法判断缓存是否存在,包括 大小写,空格等。
SQL存在一个提示,可以使当前的SQL不被缓存。
SELECT sql_no_cache field FROM table_name;
分区
将一张表的数据,分散在不同的数据索引存储文件中。
取余分区
- HASH:使用给定的一个整数,进行取余,一般对主键进行取余。
- KEY:使用任意字段,对分区数据取余分区。不要求是整数字段,但是必须是主键的一部分。
条件分区
- RANGE:利用给定的范围条件,进行分区
- LIST:利用给定的列表值,进行分区
分区操作
增加分区
# 取余分区
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
当没有特别明显的业务逻辑界限时,依据最常用的检索添加进行分区。
分表
通过引用程序,完成分表算法,将数据存储在结构相同的多个表中,称之为分表。
原因
- 不支持分区
- 分区已经不能满足已有的业务逻辑了
水平分表
分区和分表,都属于水平分表的一种,表中的记录减少,速度就会加快。
举例
创建表:设置相同的表名前缀,后面用数字表示。如:table_1,table_2,table_3,table_4
算法:通过ID取模表的个数,得到的余数就是所在的某个表,拼接上前缀即可。
现在有4张结构相同的表。
- 查询:
我们想取出ID=23的记录。
利用算法查询即可 - 插入:
因为ID是在插入之后自动生成的。- 得到每张表的最大ID进行对比
- 创建一个专门存储ID的表,在每次插入数据之前,在存储ID的表中先生成ID,然后得到该ID,使用上面的取余方法,获得表名,插入数据即可。
垂直分表
更改表的结构,减少字段,每条记录的长度减少后,所有的操作都会加快。
user
user_id | name | age |
---|---|---|
1 | sam | 18 |
2 | kobe | 18 |
wechat_user
user_id | nickname | openid |
---|---|---|
1 | sam | ** |
2 | kobe | ** |
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数量少。
劣势:表锁时间长,不利于并发。
- 单表查询:
。。。
架构
主从复制
原理
- 从库配置启动I/O进程监听主库的binlog二进制文件。
- 主库执行前将此次更新记录到binlog二进制文件中,记录完成后执行此次更新。
- 从库在监听到binlog二进制文件有更新时,获取其中数据记录到relay-log日志中。
- 从库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:推荐阅读文章。
- 本文链接:http://codersam.cn/2019/11/12/MYSQL%E4%BC%98%E5%8C%96%E7%AC%94%E8%AE%B0/
- 版权声明:本博客所有文章除特别声明外,均默认采用 许可协议。