数据库优化综合
- 预发跑SQL,explain
- 排除 缓存 sql nocache
- 看那一下行数对不对,不对可以用annalyze table t 矫正
- 添加索引 索引不一定是最优的 force index 强制走索引不建议用
- 是否存在回表情况
- 覆盖索引避免回表,不要写 *
- 联合索引 不能无限建 高频场景
- 最左前缀原则 按照索引定义的字段顺序写sql
- 合理安排联合索引的顺序
- 在5.6版之后,索引下推 减少回表次数
- 给字符串加索引(前缀索引,倒序存储,Hash)
- 数据库的flush的时机(redo log满了 修改checkpoint flush到磁盘、 系统内存不足淘汰数据页、系统空闲的时候找间隙刷脏页、MySQL正常关闭,会把内存脏页flush到磁盘)
- innodb刷盘速度 (脏页比例、redolog写盘速度、innodb_flush_neighbors 0) 在机械磁盘的随机IO不太行,减少随机IO,设置为1最好。而ssd设置0就够了,8.0之后默认就是0
- 索引自字段不要做函数操作,会破坏索引值的有序性,优化器或放弃走树结构(如果触发隐式转换,那也会cast函数,会放弃走索引)
- 字符集不同可能走不上索引, convert也是函数所以走不上
拓展:
mysql操作中我们经常需要对数据进行类型转换。此时我们应该使用的是cast()或convert()。
二、两者的对比
相同点:都是进行数据类型转换,实现的功能基本等同
不同点:两者的语法不同,cast(value as type) 、 convert(value,type)
三、所支持的类型如下:
二进制,同带binary前缀的效果 : BINARY
字符型,可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
16.不常使用的数据迁移备份,避免每次都在海量数据中去检索。
17.提升数据库服务器硬件配置,或者搭建数据库集群。
18.编程手段防止SQL注入:使用JDBC PreparedStatement按位插入或查询;正则表达式过滤(非法字符串过滤)
SQL语句中九大尽量进行优化
查询语句的优化是SQL效率优化的一个方式,可以通过优化sql语句来尽量使用已有的索引,避免全表扫描,从而提高查询效率。最近在对项目中的一些sql进行优化,总结整理了一些方法。
1、在表中建立索引,优先考虑where、group by使用到的字段。
2、尽量避免使用select 星号,返回无用的字段会降低查询效率
3、尽量避免使用in 和not in,会导致数据库引擎放弃索引进行全表扫描。
4、尽量避免使用or,会导致数据库引擎放弃索引进行全表扫描。
SELECT * FROM t WHERE id = 1 OR id = 3
优化方式:可以用union代替or。如下:
SELECT * FROM t WHERE id = 1 UNION SELECT * FROM t WHERE id = 3
(PS:如果or两边的字段是同一个,如例子中这样。貌似两种方式效率差不多,即使union扫描的是索引,or扫描的是全表)
5、尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描。如下:
SELECT * FROM t WHERE username LIKE '%li%'
优化方式:尽量在字段后面使用模糊查询。如下:
SELECT * FROM t WHERE username LIKE 'li%'
6、尽量避免进行null值的判断,会导致数据库引擎放弃索引进行全表扫描。如下:
SELECT * FROM t WHERE score IS NULL
优化方式:可以给字段添加默认值0,对0值进行判断。如下:
SELECT * FROM t WHERE score = 0
7、尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描。 例如:
SELECT * FROM t2 WHERE score/10 = 9
SELECT * FROM t2 WHERE SUBSTR(username,1,2) = 'li'
优化方式:可以将表达式、函数操作移动到等号右侧。如下:
SELECT * FROM t2 WHERE score = 10*9
SELECT * FROM t2 WHERE username LIKE 'li%'
**8、当数据量大时,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描。**如下:
SELECT * FROM t WHERE 1=1
优化方式:用代码拼装sql时进行判断,没where加where,有where加and。
9.Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤