电脑技术网——专业手机电脑知识平台,关注科技、手机、电脑、智能硬件
MySQLMSSQLAccessOracle

MySQL的20条基本优化 赞参考资料

2021-01-22 11:23:25 出处:[ 菜菜电脑网 ] 人气:次阅读

mysql的性能优化包罗颇多:
索引优化,浏览优化,检索堆栈,服务器设置优化,操作系统和硬件优化,运用于层面优化(web服务器,线程)等等。这里的记录的优化技巧更等同于于开发人员,都值得注意网络上搜罗和自己整理的,主要是搜索语句上面的优化,其它层面的优化技巧在此不来作记录。
浏览的负担指标:
制订时间
检查的行数
前往的行数
建起索引的几个准则:
1、合理的创设索引必需较慢数据复制到效率,不合理的设立索引反而时会辗慢速数据库的响应速度。
2、索引越多,更新数据的速度越慢。
3、尽量在采行MyIsam作为引擎的时候用于索引(因为MySQL以BTree存储索引),而不是InnoDB。但MyISAM不反对Transcation。
4、当你的程序和数据库结构/SQL语句已经优化到无法优化的程度,而程序瓶颈并只能可惜解决问题,那就是必要顾虑用到诸如memcached这样的分布式寄存器系统的时候了。
5、习惯和强行自己用EXPLAIN来分析你SQL语句的性能。
1. count的优化
比如:算出id大于5的城市

1 a. select count(*) from world.city where id > 5;2 b. select (select count(*) from world.city) – count(*) from world.city where id <= 5;


a语句当行数有约11自为的时候只能追踪的行数比b语句要多, b语句显影了6行,此种情况下,b语句比a语句更有效率。当从未where语句的时候必要

select count(*) from world.city

这样亦会更快,因为mysql总是其实表的行数。
2. 可能会常用不适配的数据类型。
例如float和int、char和varchar、binary和varbinary是不向下兼容的。数据类型的不兼容性不太可能使优化器无法制订一些本来可以顺利完成的优化操控。
在程序中,情况下在借助于功能的基础上,尽量减少对数据库的访问期间次数;通过跟踪参数,尽量减少对表的回访行数,最小化结果集,从而加重网络开销;必须单独的可用尽量这样一来处置,降低每次的响应速度;在数据窗口可用SQL时,尽量把应用于的索引放到自由选择的首列;算法的结构尽量单纯;在查找时,不要过多地用到通配符如 SELECT * FROM T1语句,要加进几列就自由选择几列如:SELECT COL1,COL2 FROM T1;在显然的情况下尽量放宽尽量结果集行数如:SELECT TOP 300 COL1,COL2,COL3 FROM T1,因为某些情况下用户是不无需那么多的数据的。不要在系统设计中常用数据库游标,游标是非常适合于的工具,但比用于常规的、面向集的SQL语句只能更大的工作量;按照特定顺序分离出来数据的匹配。
3. 索引字段上同步进行运算亦会使索引过载。
尽量避免在WHERE子句中对字段开展函数或表达式配置,这将避免引擎退出采用索引而进行时全表扫瞄。如:

SELECT * FROM T1 WHERE F1/2=100 应当更名: SELECT * FROM T1 WHERE F1=100*2


4. 不必要用作!=或<>、IS NULL或IS NOT NULL、IN ,NOT IN等这样的操作符.
因为这都会使系统无法用于索引,而不能必要追踪表中的数据。例如: SELECT id FROM employee WHERE id != “B%” 优化器将无法通过索引来已确定将要打中的行数,因此能够关键字该表的所有行。在in语句中能用exists语句取而代之的就用exists.
5. 尽量可用数字型字段.
一部分开发人员和数据库管理人员迷恋把构成数值信息的字段
设计为字符型,这时会减少转发和相互连接的性能,并但会增大存储所需。这是因为引擎在妥善处理核对和连结回老家逐个比较字符串中每一个字符,而对于数字型而言只必需比较一次就不够了。
6. 合理采用EXISTS,NOT EXISTS子句。如下标明:

1.SELECT SUM(T1.C1) FROM T1 WHERE (SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)2.SELECT SUM(T1.C1) FROM T1WHERE EXISTS(SELECT * FROM T2 WHERE T2.C2=T1.C2)


两者产生相同的结果,但是后者的效率看来要略低于前者。因为后者一定会产生大量拉出的列于读取或是索引显影。如果你想要校验表里否普遍存在某条纪录,不要用count(*)那样效率很低,而且浪费服务器资源。可以用EXISTS。如:

IF (SELECT COUNT(*) FROM table_name WHERE column_name = ‘xxx’)

可以写就:

IF EXISTS (SELECT * FROM table_name WHERE column_name = ‘xxx’)


7. 并不需要用BETWEEN的就不要用IN
8. 很难用DISTINCT的就可不GROUP BY
9. 尽量不要用SELECT INTO语句。SELECT INTO 语句可能会引致注记锁死,解救其他用户会面时该同上。
10. 必要时一律键入优化器适用某个索引

SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45) 改用:SELECT * FROM T1 (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45)


则浏览优化器将就会强迫利用索引IX_ProcessID 拒绝执行转发。
11. 除去对大型表行数据的顺序存储
尽管在所有的检查上都有索引,但某些形式的WHERE子句任由优化器适用顺序存储。如:

SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008


解决办法可以运用于并集来可避免顺序加载:

SELECT * FROM orders WHERE customer_num=104 AND order_num>1001 UNION SELECT * FROM orders WHERE order_num=1008


这样就能利用索引路径检视查看。【jacking 数据结果集很多,但检索条件特典后结果集有所的情况下,后面的语句快速】
12. 尽量避免在索引过的字符数据中,运用于非打头字母抓取。这也使得引擎无法利用索引。
闻如下例子:

SELECT * FROM T1 WHERE NAME LIKE%L%SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’SELECT * FROM T1 WHERE NAME LIKE ‘L%

 


即使NAME字段建起索引,前两个查阅依然无法利用索引启动放缓加载,引擎不得不对全表所有数据逐条操控来顺利进行任务。而第三个检索需要采用索引来放缓操作方法,不要习惯性的可用 ‘%L%’这种方式(但会造成了全表成像),如果可以用到`L%’相对来说更好;
13. 虽然UPDATE、DELETE语句的拼法基本互换,但是还是对UPDATE语句给点建议:

a) 尽量不要简化主键字段。b) 当修订VARCHAR型字段时,尽量适用相同长度内容的值。c) 尽量最小化对于包含UPDATE触发器的表的UPDATE加载。d) 防止UPDATE将要加到其他数据库的列。e) 防止UPDATE建起很多索引的列。f) 防止UPDATE在WHERE子句条件中的列。

14. 要用UNION ALL就不要用UNION
UNION ALL不继续执行SELECT DISTINCT函数,这样就不会减小很多不必要的资源
在多个各不相同的数据库时用到UNION是一个引人入胜的优化方法,UNION从两个互不关联的表中离开数据,这就反之亦然不必显现出来重复的行,同时也必须对数据来进行排序,我们真的排序是非常耗用资源的,特别是对大表的排序。
UNION ALL可以大大加快速度,如果你已经并不知道你的数据不能最主要重复行,或者你不在乎确实可能会注意到重复的行,在这两种情况下用作UNION ALL更较难。此外,还可以在分析方法程序逻辑中选用某些方法常因重复的行,这样UNION ALL和UNION赶回的结果都是一样的,但UNION ALL才会顺利完成排序。
15. 字段数据类型优化:
a. 可能会可用NULL类型:NULL对于大多数数据库都必须多种不同妥善处理,MySQL也不或多或少,它必需更多的代码,更多的检查和类似的索引逻辑,有些开发人员显然没意识到,始创表时NULL是默认值,但大多数时候某种程度采用NOT NULL,或者用到一个多种不同的值,如0,-1作为默认值。
b. 尽可能常用更小的字段,MySQL从磁盘读取数据后是存储到内存中的,然后用到cpu周期和磁盘I/O复制到它,这并不一定越小的数据类型迁走的空间越小,从磁盘中学毕业或拆开到内存的效率都更好,但也不要太过深信增高数据类型,要是以后应用程序愈演愈烈什么变化就未空间了。重写表将能够重构,间接地有可能导致代码的忽略,这是很奇怪的是的问题,因此能够寻找一个平衡点。
c. 优先用于定长型
16. 关于大数据量limit原产的优化不知下面链接(当偏移量特别大时,limit效率时会非常低):
http://ariyue.iteye.com/blog/553541
附上一个降低limit效率的有用技巧,在其余部分索引(延展索引用通俗的话说就是在select的时候能用去复制到索引而拿到数据,不用透过二次select无关同上)上顺利完成偏差,而不是对全行数据进行时对齐。可以将从遮盖索引上抽取出来的数据和全行数据展开串联,然后夺得只能的列,都会更有效率,进去下面的转发:
mysql> select film_id, description from sakila.film order by title limit 50, 5;
如果备注非常大,这个检索众所周知写出下面的样子:

mysql> select film.film_id, film.description from sakila.filminner join(select film_id from sakila.film order by title liimit 50,5) as film usinig(film_id);

 


17. 程序中如果一次性对同一个所列抽出多条数据,比如以下语句:

insert into person(name,age) values(‘xboy’, 14);insert into person(name,age) values(‘xgirl’, 15);insert into person(name,age) values(‘nia’, 19);

 


把它组合而成一条语句执行者效率时会更高.

insert into person(name,age) values(‘xboy’, 14), (‘xgirl’, 15),(‘nia’, 19);

 


18. 不要在选取的栏位上放有索引,这是无意义的。一定会在条件必需的语句上合理的放有索引,比如where,order by。

SELECT id,title,content,cat_id FROM article WHERE cat_id = 1;

 


上面这个语句,你在id/title/content上置放索引是毫无意义的,对这个语句从未任何优化主导作用。但是如果你独自键cat_id上放于一个索引,那发挥作用就相当大了。
19. ORDER BY语句的MySQL优化:
a. ORDER BY + LIMITPop的索引优化。如果一个SQL语句形如:

SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];

 


这个SQL语句优化比较简单,在[sort]这个栏位上创建索引即可。
b. WHERE + ORDER BY + LIMIT一组的索引优化,形如:

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [VALUE] ORDER BY [sort] LIMIT [offset],[LIMIT];

 


这个语句,如果你仍然换用第一个例子中设立索引的方法,虽然可以比如说索引,但是效率不较低。更高效的方法是创设一个合组索引(columnX,sort)
c. WHERE + IN + ORDER BY + LIMITPop的索引优化,形如:

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY [sort] LIMIT [offset],[LIMIT];

 


这个语句如果你有别于第二个例子中构建索引的方法,不会未获预期的效果(仅在[sort]上是using index,WHERE那里是using where;using filesort),理由是这里对应columnX的值对应多个。
目前哥还木有发现比较出色的办法,赶紧高手指教。
d.WHERE+ORDER BY多个栏位+LIMIT,比如:
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
对于这个语句,大家似乎是沙一个这样的索引:(x,y,uid)。但实际上更好的效果是(uid,x,y)。这是由MySQL处理过程排序的机制致使的。
20. 其它技巧:
http://www.cnblogs.com/nokiaguy/archive/2008/05/24/1206469.html
http://www.cnblogs.com/suchshow/archive/2011/12/15/2289182.html
http://www.cnblogs.com/cy163/archive/2009/05/28/1491473.html
http://www.cnblogs.com/younggun/articles/1719943.html
http://wenku.baidu.com/view/f57c7041be1e650e52ea9985.html
最后,你可以用于explain关键字去判别和评测一个sql语句到底还有优化的可能性,关于它的详尽适用叮嘱参照mysql手册

关于我们 - 广告合作 - 联系我们 - 免责声明 - 网站地图 - 投诉建议 - 在线投稿

©CopyRight 2008-2020 caicaipc.com Inc All Rights Reserved.
菜菜电脑网 版权所有