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/
- 版权声明：本博客所有文章除特别声明外，均默认采用 许可协议。