1. 使用查询缓存优化查询
我们知道,一条SQL语句的执行需要经过:解析、优化和截断过程。当有相同的查询SQL被执行了多次时,这些查询结果就会放到一个缓存中。后续相同的查询结果就不需要再次执行SQL语句,而直接从缓存中获取结果。加快了查询性能。
目前大多数MySQL数据库自动开启了查询缓存。但是如果我们使用一些数据不固定的查询语句(例如使用rand()函数),这样就导致每次查询的结果都不固定,数据库就不会使用查询缓存。
2. EXPLAIN 你的select 查询
使用 explain 命令可以让你知道MySQL是如何执行你的SQL语句的。可以知道是否使用了索引,使用了哪个索引,数据表是如何被搜索和排序的等等。
3. Limit 1
当你只需要一行数据时:比如你想要知道某个User实例在数据库中存不存在。那么这时候我们不需要 select 出所有的结果然后去判断。我们加一个 limit 1 的限制条件,当SQL查找到指定数据中就会直接返回结果,不用再继续向下执行。极大地提高了效率
select * from user where id=1
×
select 1 from user where id=1 limit 1
√
4. 为搜索的关键字加索引
索引不一定只加在主键上。有的时候我们需要对其它某个非主键字段进行多次查询,我们也可以对这个字段建立索引。但是要注意不要让索引失效哦!
5. Join 表的时候使用数据类型一致的列,并索引
要确保两个表用来Join的字段是建过索引的,这样MySQL内部会自动优化Join的SQL语句。
要确保用来Join的字段类型时相同的,否则将无法使用索引。
就比如将 DECIMAL类型字段和 INT 类型字段进行Join的话,就会导致索引失效。如果是字符串类型的字段进行 Join,还要保证字符串的编码相同。
6. 不要使用 order by rand()
这条语句的作用就是:从数据表中取出随机的几条数据。通过慢查询日志我们可以知道,这样做是会导致全表扫描的,效率非常低。使用这条语句的目的是为了做随机测试,但是要慎重使用!
7. 避免select *
查的字段越多,查的效率越低。 如果web服务器和SQL服务器是两台独立的服务器,那么还会增加网络传输的负担。 只要取有用的即可。
8. 永远为每张表设置一个ID
应该为每张数据表设置一个 INT类型(最好是 UNSIGNED)的主键ID,并设置自增标志。即使表中有其他字段可以唯一标识数据(比如手机号),那也别用来当主键,因为数据库的查询性能是和字段的长度成反比的。而且在关联多表时, ID 更加得心应手。
9. 使用ENUM而不是VARCHAR
枚举类型 ENUM是非常轻量的。 它保存的其实是 TINYINT,外表显示为字符串,这样一来 ENUM用来做一些选项列表是非常合适的。(比如 sex:男/女, 登录状态:已登录/未登录)。 如果使用 varchar, 不免有种大材小用的感觉。
10. 从 procedure analyse() 取得建议
procedure analyse() 过程分析,MySQL会去分析你的数据表中的字段和实际数据,提出一些有用的建议来提高性能。
就比如: 数据表中有某个 varchar字段, 由于数据量不大,MySQL会建议你将其改为 enum 类型。
这种建议是基于数据表字段和数据量来分析的,数据量小的时候会缺乏准确性。
11. 尽可能使用 NOT NULL
不到万不得已,不要去使用 NULL 值。
原因:1、 NULL值也是需要额外的存储空间的。 2、进行 NULL值比较时,程序会更复杂,也会影响性能。
12. Prepared Statements
Prepared Statements 有太多好处:一个是性能,一个是安全。
性能方面:我们可以通过设置Prepared Statements的一些参数,来使得相同的SQL语句只会被解析一次,大大提升性能。
安全方面:Prepared Statements会检查你将要执行的SQL语句中所绑定的变量,防止SQL注入问题。
13. 无缓冲查询
我的理解是:这相当于 Java的 NIO模型。 无缓冲查询就是,当一条SQL查询语句还未结束时,我们就可以着手处理已经查询到的结果,无需等到所有结果返回。这里就会出现一问题: 已经返回的结果如果没有使用,就会占据缓存区域,可能会与下次的查询结果混淆。
因此使用无缓冲查询,要么将所有查询结果都处理了,要么就在下次查询前清除查询结果。
14. 把IP地址存成UNSIGNED INT
IP地址我们一般是使用varchar类型来保存。 但是如果我们用 整形(unsigned int) 来存放,只需要4个字节,并且可以有定长的字段。 而且如果要查找范围 IP,我们甚至可以直接:
select * from IP between ip1 and ip2
15. 使用固定长度的表
如果表中所有的字段都是定长的,那么这张表就会被认为是 “static” 或 “fixed_length”。
这样就有一个好处:查询的时候,我们可以通过当前位置的数据,很容易计算出下一个数据的偏移量,查询自然就会增快。 如果不定长的话,我们就需要重新找到主键,再通过主键进行索引,这样效率会比较低。
16. 垂直分表
将一张表,按列分成多张表的方法。可以降低表的复杂度和字段的数目,从而达到优化的目的。
比如:
一张User表,我们只需要 ID,Name,Password 三个字段, 那何不将其他的不是经常使用的如 地址address,性别sex等分到其他的表中。小表的性能自然会更好。
再比如:
还是一张User表,里面有个上次登录时间 last_login字段,这个字段值并不是固定的。按照我们上文所说,查询缓存将会失效。因此我们可以将这个字段单独分离出去。
我们还可以把一些不定长的字段列分离出去,只留下定长字段,这样也可以提升性能。
!!需要注意:分表后,要确保不会经常 Join,否则将会导致比不分表更差的性能。而且分表要注意不要违反了三范式。
17. 拆分大的 delete 或 insert语句
意思就是,不要一次性执行过多的 插入删除操作。如果是在服务器上执行,会导致服务器阻塞一段时间,在高并发的场景下这是非常致命的。
我们可以 通过 limit 来限制一次性执行的数据量,然后通过 sleep()方法来控制空闲时间。
18. 越小的列会越快
数据量不大的时候,我们就没必要使用 INT来做主键,可以使用更小的 MEDIUMINT,SMALLINT,或者是 TINYINT。如果不需要记录具体时间,可以用DATE来替换
DATETIME。
注意你先需要留下数据扩展的空间。
19. 选择合适的存储引擎
目前 MySQL使用最多的引擎就是 MyISAM和 InnoDB。
具体的选择问题还是要先去了解二者的区别,这边就不仔细阐述了。
20. 使用 关系映射器(ORM)
Java 推 MyBatis,其他不了解 T^T
21. 小心数据库永久链接
这个问题还不太了解,先记录一哈。
以上内容是基于网上博客+《高性能MySQL》+自己的理解整合的,大家看看就好,
有错误帮忙指出一下,谢谢搭嘎。