数据库及SQL优化

SQL的生命周期

        1. 应用服务器与数据库服务器建立一个连接
        2. 数据库进程拿到请求 sql
        3. 解析并生成执行计划,执行
        4. 读取数据到内存并进行逻辑处理
        5. 通过步骤一的连接,发送结果到客户端
        6. 关掉连接,释放资源

优化方式

        1、优化SQL

        2、优化表结构和索引

        3、加缓存(如:redis)

        4、分库分表

        5、主从复制、读写分离

        6、使用集群

优化SQL
1、定位SQL语句的性能问题
MySQL 提供了 explain命令来查看语句的执行计划,使用方式:在执行的SQL前加上 explain 即可。
执行带有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 关键字根据某个字段进行分区;拆分表字段

主从复制、读写分离
        将主数据库中的DDL DML 操作通过二进制日志( BINLOG )传输到从数据库上,然后将这
些日志重新执行(重做);从而使得从数据库的数据与主数据库保持一致。

MySQL记录binlog的三种模式

1、基于SQL语句的复制

        每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一 条sql语句和每一行的数据变化,减少了binlog日志量,节约IO,提高性能。缺点是在某些情况下会导致 master-slave 中的数据不一致( sleep()函数)

2、基于行 的复制

        不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了,修改成什么样;缺点是会产生大量的日志

3、混合模式复制

        以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于 STATEMENT 模式无法复制的操作使用ROW模式保存binlogMySQL会根据执行的SQL语句选择日志保存方式

Mysql的锁机制

1、乐观锁:加个version字段,每次更新

2、悲观锁:类似于java中的synchronized,执行完释放锁,其他链接才可进行操作(FOR UPDATE)

3、共享锁、排它锁(悲观锁的实现):共享锁(读锁):只能读,不能写(LOCK IN SHARE MODE);排它锁(写锁):当前事务读写完成之前其他事务只能读,不能写(FOR UPDATE)

4、表级锁:没有索引的表进行查询时会锁定整个表(LOCK TABLES、UNLOCK TABLES)

5、行级锁:通过索引给某一行加锁

  • 4
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值