>首页> IT >

超详细汇总mysql优化实践技巧

时间:2022-03-24 18:23:25       来源:转载
本篇文章给大家带来了关于mysql的相关知识,其中主要总结了二十一个mysql优化的实践技巧,下面就一起来看一下这二十一个优化技巧,希望对大家有帮助。

推荐学习:mysql视频教程

1.为查询缓存优化你的查询

2. EXPLAIN 你的 SELECT 查询

当我们为 group_id 字段加上索引后: 我们可以看到,前一个结果显示搜索了 7883 行,而后一个只是搜索了两个表的 9 和 16 行。查看 rows 列可以让我们找到潜在的性能问题。

3. 当只要一行数据时使用 LIMIT 1 1

4. 为搜索字段建索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你总要会经常用来做搜索,那么,请为其建立索引吧

5. 在 Join 表的时候使用相同类型的例

6. 千万不要 ORDER BY RAND()

下面的示例是随机挑一条记录:

7.避免 SELECT *

8. 永远为每张表设置一个 ID

9. 使用 ENUM 而不是 VARCHAR

10. 从 PROCEDURE ANALYSE() 取得建议

一定要注意,这些只是建议,只有当你的表里的数据越来越多时,这些建议才会变得准确。一定要记住,你才是最终做决定的人

11. 尽可能的使用 NOT NULL

12.Prepared Statements

13. 无缓冲的查询

14. 把 IP 地址存成 UNSIGNED INT

15. 固定长度的表会更快

使用“垂直分割”技术(见下一条),你可以分割你的表成为两个一个是定长的,一个则是不定长的。

16. 垂直分割

另外,你需要注意的是,这些被分出去的字段所形成的表,你不会经常性地去 Join 他们,不然的话,这样的性能会比不分割时还要差,而且,会是极数级的下降

17.拆分大的 DELETE 或 INSERT 语句

18.越小的列会越快

19. 选择正确的存储引擎

20. 使用一个对象关系映射器 (Object Relational Mapper)

21. 小心 “ 永久链接 ”

22.sql优化之索引优化

1.独立的列

在进行查询时,索引列不能是表达式的一部分,也不能是函数的参数,否则无法使用索引。 例如下面的查询不能使用 actor_id 列的索引:

#这是错误的SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

优化方式:可以将表达式、函数操作移动到等号右侧。如下:

SELECT actor_id FROM sakila.actor WHERE actor_id  = 5 - 1;

2.多列索引

在需要使用多个列作为条件进行查询时,使用多列索引比使用多个单列索引性能更好。 例如下面的语句中,最好把actor_idfilm_id设置为多列索引。猿辅导有道题,详见链接,可以让理解更深刻。

SELECT film_id, actor_ id FROM sakila.film_actorWHERE actor_id = 1 AND film_id = 1;

3.索引列的顺序

让选择性最强的索引列放在前面。 索引的选择性是指:不重复的索引值和记录总数的比值。最大值为 1,此时每个记录都有唯一的索引与其对应。选择性越高,每个记录的区分度越高,查询效率也越高。

例如下面显示的结果中 customer_id的选择性比 staff_id 更高,因此最好把 customer_id列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,COUNT(*)FROM payment;#结果如下staff_id_selectivity: 0.0001customer_id_selectivity: 0.0373COUNT(*): 16049

4.前缀索引

对于 BLOB、TEXT 和 VARCHAR类型的列,必须使用前缀索引,只索引开始的部分字符。

前缀长度的选取需要根据索引选择性来确定。

5.覆盖索引

索引包含所有需要查询的字段的值。具有以下优点:

6.优先使用索引,避免全表扫描

如:’%ptd_’ 和 ‘%ptd_%’ 都没有用到索引;而 ‘ptd_%’ 使用了索引。

#进行全表查询,没有用到索引EXPLAIN SELECT * FROM `user` WHERE username LIKE "%ptd_%";EXPLAIN SELECT * FROM `user` WHERE username LIKE "%ptd_";#有用到索引EXPLAIN SELECT * FROM `user` WHERE username LIKE "ptd_%";

再比如:经常用到的查询数据库中姓张的所有人:

SELECT * FROM `user` WHERE username LIKE "张%";

7.尽量避免使用in 和not in,会导致数据库引擎放弃索引进行全表扫描。

比如:

SELECT * FROM t WHERE id IN (2,3)SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)

优化方式:如果是连续数值,可以用between代替。如下:

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询,可以用exists代替。如下:

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)

8.尽量避免使用or,会导致数据库引擎放弃索引进行全表扫描

如:

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式:可以用union代替or。如下:

SELECT * FROM t WHERE id = 1UNIONSELECT * FROM t WHERE id = 3

9.尽量避免进行null值的判断,会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM t WHERE score IS NULL

优化方式:可以给字段添加默认值0,对0值进行判断。如下:

SELECT * FROM t WHERE score = 0

10.尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描

同第1个,单独的列;

SELECT * FROM t2 WHERE score/10 = 9SELECT * FROM t2 WHERE SUBSTR(username,1,2) = "li"

优化方式:可以将表达式、函数操作移动到等号右侧。如下:

SELECT * FROM t2 WHERE score = 10*9SELECT * FROM t2 WHERE username LIKE "li%"

11.当数据量大时,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描

SELECT * FROM t WHERE 1=1

索引规则:

23.SQL优化之查询优化

1.使用Explain进行分析

Explain 用来分析 SELECT查询语句,开发人员可以通过分析 Explain结果来优化查询语句。

比较重要的字段有:

2.优化数据访问

1.减少请求的数据量

2.减少服务器端扫描的行数

3.重构查询方式

1.切分大查询

一个大查询如果一次性执行的话,可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

2.分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询,然后在应用程序中进行关联,这样做的好处有:

SELECT * FROM tabJOIN tag_post ON tag_post.tag_id=tag.idJOIN post ON tag_post.post_id=post.idWHERE tag.tag="mysql";SELECT * FROM tag WHERE tag="mysql";SELECT * FROM tag_post WHERE tag_id=1234;SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

24.分析查询语句

推荐学习:mysql视频教程

以上就是超详细汇总mysql优化实践技巧的详细内容,更多请关注php中文网其它相关文章!

关键词: 查询语句 二十一个 视频教程