推荐学习: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.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中文网其它相关文章!