SQL的生命周期
优化方式
1、优化SQL
2、优化表结构和索引
3、加缓存(如:redis)
4、分库分表
5、主从复制、读写分离
6、使用集群
优化SQL
1、定位SQL语句的性能问题
执行带有EXPLAIN的查询后,你将得到一个结果集,其中包含了关于查询执行计划的详细信息。
结果集中的每一行都代表查询中的一个步骤或操作,并包含以下列(某些列可能因MySQL版本和配置而异):
id: 查询的标识符。
select_type: 查询的类型(例如SIMPLE, SUBQUERY, DERIVED等)。
table: 查询涉及的表。
type: 访问类型,这是性能调优的关键指标之一。常见的访问类型包括ALL(全表扫描)、index(索引全扫描)、range(索引范围扫描)、ref(非唯一索引查找)、eq_ref(唯一索引查找)、const(常量引用)等。
possible_keys: 可能被使用的索引。
key: 实际使用的索引。
key_len: 使用的索引的长度。在不使用索引的情况下,值为NULL。
ref: 显示哪些列或常量被用作索引查找的条件。
rows: 估计要检查的行数。
Extra: 额外的信息,例如“Using where”表示使用了WHERE子句来过滤结果,“Using index”表示只使用了索引,而没有读取实际的表数据等。
2、优化SQL
1)尽量减少查询字段,使用索引,使用limit
2)UNION ALL不去重,效率高于UNION
3)避免全表扫描:不对字段进行null值判断,如is not null,避免使用 != 或 <>、or、in、Like
表结构优化
1)加索引、主键,注意:索引不是越多越好
2)分表分区,使用partition 关键字根据某个字段进行分区;拆分表字段
主从复制、读写分离
MySQL记录binlog的三种模式
1、基于SQL语句的复制
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一 条sql语句和每一行的数据变化,减少了binlog日志量,节约IO,提高性能。缺点是在某些情况下会导致 master-slave 中的数据不一致( 如sleep()函数)
2、基于行 的复制
不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了,修改成什么样;缺点是会产生大量的日志
3、混合模式复制
以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于 STATEMENT 模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式
Mysql的锁机制
1、乐观锁:加个version字段,每次更新
2、悲观锁:类似于java中的synchronized,执行完释放锁,其他链接才可进行操作(FOR UPDATE)
3、共享锁、排它锁(悲观锁的实现):共享锁(读锁):只能读,不能写(LOCK IN SHARE MODE);排它锁(写锁):当前事务读写完成之前其他事务只能读,不能写(FOR UPDATE)
4、表级锁:没有索引的表进行查询时会锁定整个表(LOCK TABLES、UNLOCK TABLES)
5、行级锁:通过索引给某一行加锁