MYSQL优化方案（不定时更新补充，建议保存）

• 系统的数据都从数据库上来，数据库的吞吐量和速度一定程度决定系统的并发和响应速度
• 系统运行与数据量成正比，数据读处理尤其是查询自然就慢
• Mysql数据库的数据最终在磁盘上持久化存储，读写不如Redis等这些内存数据库

数据库优化一般从以下几个方面来：

• 数据库设计：数据表设计遵循三范式，使用合适的数据类型，使用合适的存储引擎、索引
• 数据库扩展：数据库的分表分库，读写分离等
• 数据库使用：SQL语句优化等
• 数据库服务器配置：尽可能磁盘读写速度快的配置

 一、数据库设计

        三范式：字段原子性、消除对主键的部分依赖、在2NF的基础上增加外键。

三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库！需求才是粑粑

         数据类型：

1.尽量使用可以正确存储数据的最小数据类型，更小的数据类型意味着更快，占用更少的磁盘，内存、缓存和处理时间。

2.尽可能使用not null，这个值是很烦人的，建字段时请尽量指定是否非空，NULL使得索引，统计，比较都变得更复杂，而且索引尽量不要创建到可以为null的字段上

3.字符串类型，VARCHAR是可变长字符串，比定长字符串（CHAR）更节省空间

4.日期和时间类型，datetime使用8字节存储空间，与时区无关，timestamp只使用4字节存储，依赖系统时区

5.整数类型，tinyint一般用它存储状态值而不要用int，如果是Boolean类型，那么tinyint(1)当值为1和0时，查询结果自动转为true和false；INT(11)不会限制值的范围，只是规定了一些客户端工具用来显示的字符的个数，所以对于存储和计算来说INT(11)和INT(1)相同；IP地址实际上是32位无符号整数，用INT存储，Mysql提供转换函数为INET_ATON()和INET_NTOA()；

        存储引擎：数据库存储引擎是数据库底层组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。SHOW ENGINES;

1、InnoDB存储引擎

MySQL的默认内置存储引擎已经是InnoDB；

支持事务，默认事务隔离界别为可重复读；

使用的锁粒度为行锁，可以支持更高的并发；

支持外键；

配合一些热备工具可以支持在线热备份；

在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度
对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。根据主键进行排序，数据和索引放在一块，都位于B+数的叶子节点上；

其他：select for update本身是行锁，若查询时没有使用到索引或主键，索引失效的情况下是表锁，比如like使用索引无效，for update就是表锁。

2、MyISAM存储引擎

不支持事务；

不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用；

对数据的查询缓存只会缓存索引，不会像InnoDB一样缓存数据，而且是利用操作系统本身的缓存；

默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁；

支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如ElasticSearch，Solr，Sphinx等；

数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难恢复；

3、其他索引很少用MEMORY、ARCHIVE、CSV等

总结：

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键保证数据完整性，比如常规使用系统。

二、数据库扩展

        针对大型数据操作，进行分库分表，读写分离优化

        使用参考往期文章：Mysql 实现读写分离（使用MyCat1.6.6）_一朵纯洁的小白花的博客-CSDN博客上篇讲到Mysql实现主从复制mysql 部署主从数据库_一朵纯洁的小白花的博客-CSDN博客本篇来实现mysql在程序中的读写分离使用到数据库中间件myCat，实现读写库的自动路由服务器环境：linux Centos 7mysql8.0.21服务器三台（一主一从） 192.168.121.10 (主库)、192.168.121.20(从库)192.168.121.30（mycat中间件）三台机器均需安装同一版本mysql数据库安装步骤参考linux安装mysql详细...https://blog.csdn.net/TurboAnho/article/details/121607924?spm=1001.2014.3001.5501mysql 部署主从数据库_一朵纯洁的小白花的博客-CSDN博客部署环境：linux Centos 7mysql8.0.21服务器两台（一主一从） 192.168.121.10 (主库)、192.168.121.20(从库)数据库安装步骤参考上篇链接linux安装mysql详细教程（远程连接）_一朵纯洁的小白花的博客-CSDN博客两台服务器最好安装版本一致开始部署1、配置master主服务器的mysql[root@localhost bin]# vim /etc/my.cnf1.在[mysqlId]下新添加server-...https://blog.csdn.net/TurboAnho/article/details/121488941?spm=1001.2014.3001.5501       解决读写分离延迟、更多其他方案后期补充.........

三、sql优化

        涉及工程师具体的代码规范，最直接的优化方案，注意点如下（欢迎留言补充）：

1、查询尽量避免全表扫描，首先考虑在where、order by字段上添加索引

2、避免在where字段上使用NULL值，所以在设计表时尽量使用NOT NULL约束，有些数据会默认为NULL，可以设置默认值为0或者-1

3、避免在where子句中使用!=或<>操作符，Mysql只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE使用索引

4、避免在where中使用OR来连接条件，否则可能导致引擎放弃索引来执行全表扫描，可以使用UNION进行合并查询

      select id from t where num = 30 union select id from t where num = 40;

5、尽量避免在where子句中进行函数或者表达式操作

6、最好不要使用select * from t，用具体的字段列表代替"*"，不要返回用不到的任何字段

7、in 和 not in 也要慎用，否则会导致全表扫描，如

select id from t where num IN(1,2,3)如果是连续的值建议使用between and，select id from t where between 1 and 3；

8、select id from t where col like %a%;模糊查询左侧有%会导致全表检索，如果需要全文检索可以使用全文搜索引擎比如es，slor；

9、limit offset rows关于分页查询，尽量保证不要出现大的offset，比如limit 10000,10相当于对已查询出来的行数弃掉前10000行后再取10行，完全可以加一些条件过滤一下（完成筛选），而不应该使用limit跳过已查询到的数据。这是一个==offset做无用功==的问题。对应实际工程中，要避免出现大页码的情况，尽量引导用户做条件过滤；

10、mybatis动态sql虽然利好工程师，也要合理利用，避免多层嵌套查询。

(欢迎留言补充。。。。。。。)

索引失效的几种情况：

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效 

2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

3、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描

4、在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引

5、在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描

6、对索引字段进行计算操作、字段上使用函数

7、联合索引ABC三列，单查某列时，只有第一列A索引有效；A and B索引有效，A or B索引无效，顺序无关；B and C索引无效

(欢迎留言补充。。。。。。。)

关于mysql语句优化更多的可通过Explain关键字进行分析从而进行调整，可参考另一篇专题博客： mysql优化—分析Explain执行计划_一朵纯洁的小白花的博客-CSDN博客

四、数据库服务器配置

直接上资本，选择配置核数更多，磁盘读写能力更强的服务器，一台好的服务器也许其他查询慢的优化工作直接不用做了。

linux服务器资源信息查看及常规命令操作可参考往期文章：Linux操作（不定时更新补充，建议保存）_一朵纯洁的小白花的博客-CSDN博客1、跨服务器文件迁移在目标服务器上输入命令： scp -r root@源服务器ip:文件地址 目标服务器文件地址如：scp -r root@192.168.101.1:/software/2021-12-01-19-38-17.sql /data/mysqldata2、根据创建日期批量删除文件...https://blog.csdn.net/TurboAnho/article/details/121675575?spm=1001.2014.3001.5501