SQL优化大全心得
时间:2022-03-10 17:08
SQL
能否共享
原因
select max(sal_cap) from sal_limit;
不能
每个用户都有一个private synonym - sal_limit , 它们是不同的对象
select count(*) from work_city where sdesc like ‘NEW%‘;
能
两个用户访问相同的对象public synonym - work_city
select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id
不能
用户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象不同.
Use short primary keys. Use numbers, not strings, when joining tables.
When using multi-part keys, the first part should be the most-used key.
When in doubt, use columns with more duplicates first to get better key compression.
If you run the client and MySQL server on the same machine, use sockets instead of TCP/IP when connecting to MySQL (this can give you up to a 7.5 % improvement). You can do this by specifying no hostname or localhost
when connecting to the MySQL server.
Use --skip-locking
(default on some OSes) if possible. This will turn off external locking and will give better performance.
Use application-level hashed values instead of using long keys:
Store BLOB‘s that you need to access as files in files. Store only the file name in the database.
It is faster to remove all rows than to remove a large part of the rows.
If SQL is not fast enough, take a look at the lower level interfaces to access the data.
Updates to a table or
INSERT
on a table with deleted rows, combined withSELECTS
that take a long time.JOINS
without using keys or keys which are not unique enough.JOINS
on columns that have different column types.Using HEAP tables when not using a full key match with
=
Forgetting a
WHERE
clause withUPDATE
orDELETE
in the MySQL monitor. If you tend to do this, use the--i-am-a-dummy
option to themysq
client.
表锁定是低水平的事务处理。简而言之,MYSQL允许你锁定一组表,使得只有一个客户可以使用。与事物不同,它不限制表的类型。但是你不能回滚对已锁定表锁进行的操作。
锁定有两个基本功能:
A、操作一组表的多条语句,使它们作为一个工作单元来执行
B、在某些情况下使多个更新执行得更快
MYSQL支持三种锁定:
读、本地读和写
如:
lock tablesaccount write;
select@bal:=balance from account where account_id = 1;
update accountset where account_id =1;
unlock tables;
在某些必须使用游标的场合,可考虑将符合条件的数据行转入临时表中,再对临时表定义游标进行操作,这样可使性能得到明显提高。
经验显示,性能的最大改进得益于逻辑的数据库设计、索引设计和查询设计方面。反过来说,最大的性能问题常常是由其中这些相同方面中的不足引起的。其实SQL优化的实质就是在结果正确的前提下,用优化器可以识别的语句,充份利用索引,减少表扫描的I/O次数,尽量避免表搜索的发生。
其实SQL的性能优化是一个复杂的过程,上述这些只是在应用层次的一种体现,深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。
SQL优化大全心得,布布扣,bubuko.com