性能优化篇

性能优化篇

mysql优化

先看一下mysql的执行过程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e8eDKyF3-1722578541141)(https://i-blog.csdnimg.cn/blog_migrate/fb38a198bed9c35914925473a6fa08a6.png#pic_center)]

1、sql语句调优

长话短说，直接上干货

1、符合场景下，尽量使用between代替in，使用in代替or
2、适当采用limit，减少row的读取
3、默认情况下，Mysql会对所有的GROUP BT col1,col2…的字段进行排序，如果想要避免排序结果的消耗，可以指定ORDER BY NULL禁止排序：
尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Using filesort；
4、两个结果集没有重复数据时，用union all 代替union ，减少默认的去重计算操作
5、如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法（直接用磁盘临时表）得到group by的结果。【SELECT SQL_BIG_RESULT * FROM users;】
6、如果连接时长过长且暂时无法优化，可以手动书写连接时长【select /*+ MAX_EXECUTION_TIME(30000) */ t.id from t】表示此条sql连接时长为30秒
7、数字尽量设计为TINYINT等数字类型，而不要设计为字符类型，会稍微影响性能
8、永远用小结果集驱动大的结果集，例如left join 前面是驱动表，后面是被驱动表，尽量保证驱动表为小结果集

2、sql索引优化

1、存储引擎不能使用索引中范围条件右边的列，可使用explain查看是否走了你想要的索引【如这样的sql: select * from user where username='123' and age>20 and phone='1390012345',其中username, age, phone都有索引，只有username和age会生效，phone的索引没有用到。】
2、mysql在使用负向查询条件(!=、<>、not in、not exists、not like)的时候无法使用索引会导致全表扫描
3、like 以通配符开头(%abc..)时，mysql索引失效会变成全表扫描的操作。
4、少用or，在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
5、在组合/联合索引中，将有区分度的索引放在前面
如果没有区分度，例如用性别，相当于把整个大表分成两部分，查找数据还是需要遍历半个表才能找到，使得索引失去了意义。
6、SQL 性能优化 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
ref：使用普通的索引
range：对索引进行范围检索
当 type=index 时，索引物理文件全扫，速度非常慢。

3、存储引擎优化

这里我们简单说一下InnoDB，MyISAM的区别

MySQL的两种主要存储引擎 MyISAM 和 InnoDB 在性能、事务支持、锁定方式等方面有很多不同之处：

1. 事务支持：
   • InnoDB 支持事务，具有ACID（原子性、一致性、隔离性、持久性）特性，这意味着它可以确保数据的完整性和可靠性。MyISAM 不支持事务，因此在并发写入和数据一致性方面有一定的限制。
2. 锁定方式：
   • MyISAM 使用表级锁定（table-level locking），这意味着当一个写操作在进行时，其他写操作和读操作都会被阻塞。
   • InnoDB 使用行级锁定（row-level locking），可以最大程度地减少锁定冲突，提高并发性能。
3. 崩溃恢复和数据完整性：
   • InnoDB 对于崩溃恢复和数据完整性方面的支持更好。它有一个类似于日志（log）的机制，可以在崩溃后自动进行恢复，保证数据的一致性。
   • MyISAM 在崩溃后可能需要手动修复，可能会导致数据丢失或不一致。
4. 表空间：
   • InnoDB 表的大小受限于表空间的大小，可以支持非常大的表。
   • MyISAM 表的大小受限于操作系统文件系统的限制，通常不如 InnoDB 大。
5. 性能：
   • 在读密集型场景下，MyISAM 可能会比 InnoDB 快，因为它不需要维护事务日志和行级锁定。但在写密集型场景下，尤其是并发写入场景下，InnoDB 通常表现更好。
6. 全文搜索：
   • MyISAM 支持全文搜索（Full-Text Search），而 InnoDB 直到 MySQL 5.6 之后才开始支持全文搜索。
7. 外键约束：
   • InnoDB 支持外键约束（foreign key constraints），而 MyISAM 不支持。这意味着在 InnoDB 中可以通过外键确保数据的完整性和一致性。

综上所述，一般来说，如果需要事务支持、高并发、数据完整性和崩溃恢复能力，则应该选择 InnoDB。如果是只读操作较多的情况，可以考虑使用 MyISAM，但需要注意可能出现的数据一致性和崩溃恢复的问题。

引擎的sql操作

// 修改引擎InnoDB，MyISAM
alter table table_name type = InnoDB;
// 查看引擎
show table status from table_name; 

4、分表

这一步主要就是针对业务来说，做横向和纵向分表，将数据量分解出来