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

本篇文章给大家带来了关于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_id 和 film_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.0001
customer_id_selectivity: 0.0373
COUNT(*): 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 tab
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE 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优化实践技巧的详细内容，更多请关注悠悠之家其它相关文章！