关于查询优化的一些思路和总结
一、程序优化
热点数据使用缓存
数据库读写分离
二、数据库方面的优化
1、数据库设计优化
如果单表数据量过大,可以根据业务来做分表
数据库表可以做一些字段冗余,可以减少连表查询,提升查询效率
2、Sql语句优化
2.1.首先定位慢查询
开启慢查询日志 mysql
slow_query_log:是否开启慢查询
slow_query_log_file:慢查询日志存储路径
long-query-time:慢查询阈值,当查询时间大于阈值时会记录到日志
第三方监测工具
2.2.Sql优化
1、避免使用select *,只查需要的列
2、如果不需要去重尽量使用union all(union会去重,遍历耗时)
3、子查询改用连表查询(子查询会生成临时表)
4、深分页查询可以将上一页最大Id作为where条件
5、尽可能小表驱动大表
6、尽量不要太多表关联查询,可先查出每张表的数据在程序中处理
7、开启执行计划查看查询是否走索引,根据执行计划设置合理的索引
索引如何设置: ①where条件中较为频繁的字段、order by、group by可以作为索引
②更新频繁的字段不适合作为索引(更新数据时也会更新索引信息)
③唯一性太差的字段不适合作为索引(比如:状态,查询扫描数据太多,查询优化器就不会用到索引)
④不会出现在where条件中的字段不应该作为索引
⑤where条件+group by条件+order by条件来设置组合索引,组合索引字段顺序根据查询字段热度来
避免索引失效:
1)遵循最佳左前缀法则:如果使用组合索引,查询条件必须按组合索引顺序来(比如组合索引abc,查询条件a|a,b|a,b,c都会走索引)
2)不在索引列上做任何计算操作
3)使用模糊查询like时不要以通配符开头('%xx'),尽量不要使用全模糊查询
4)避免使用is not null或者!=(数据表字段null可以改用0代替)
5)存储引擎不能使用索引中范围条件右边的列
6)不要使用OR(可以改用union all)
关于索引覆盖:走二级索引(非主键索引),如果查询字段索引没有覆盖,会有回表操作(通过主键索引B+树查找其他字段)。
B+树:只在叶子节点存储数据,数据之间是一个双向链表
Hash索引:Hash索引在Innodb中只存在于缓存中、适合单条数据查询、不适合范围查询
索引分类:
按数据结构:B+树索引、Hash索引、Full-text索引
按物理存储:聚集索引(索引与物理顺序一致、一般为主键)、非聚集索引(索引为逻辑顺序)
按字段特性:主键索引、唯一索引、普通索引
按字段个数:单个索引、组合索引
Mysql中锁的分类
按锁粒度:
0、全局锁:Flush TABLES with READ LOCK/UNLOCK TABLES
1、行锁:锁定某行数据,锁粒度最小,并发度高,锁开销大
2、表锁:锁整张表,锁粒度最大,并发度低,锁开销小 lock TABLES tableName read/write
3、临建锁:左开右闭、非唯一索引、防止幻读
4、间隙锁:锁定一个区间(范围查询)、防止幻读
按功能:
1、共享锁:也称为读锁,一个事务给某行数据加了读锁,其他事务也可以读,但是不能写
Lock in Share mode
2、排他锁:也称为写锁,一个事务给某行数据加了写锁,其他事务在锁释放之前,不能对这行数据读和写
For Update
按程序员角度:
1、乐观锁:并不会真正去锁定数据,而是通过版本号去实现(读多写少、冲突少(成功率高)、短事务操作)
2、悲观锁:真正的锁,如上(行锁、表锁等)(并发高、写操作比较多、数据强一致性)
InnoDB和MyISAM的区别
1、InnoDB每个表只有一个文件,MyISAM每个表数据文件和索引文件
2、InnoDB支持事务、外键、行锁
B+树(平衡树,非叶子节点存放索引,叶子节点存放数据,叶子节点之间是双向链表)
Mysql InnoDB引擎下,每张表都有一个主键索引B+树,非叶子节点存放主键索引,叶子节点存放完整数据。
当创建普通索引时会创建一个新的B+树,按索引字段排序,叶子节点存放索引字段、主键ID,当要查询索引列以外的数据时,需要回表(通过ID查询主键索引B+树)
喜欢 C#、SQL、Web