sql优化问题

墨蓝 2022-04-17 04:23 180阅读 0赞

针对数据分析来说，sql语句是必不可少的，但是怎么做到sql的优化问题呢，这里就简单的总结一下。

首先，sql优化的几种途径（这里分为两大块表述）：

第一部分：数据表格创建时：

1、选择合适的数据库表格存储引擎

通过show engines;查看SQL下的引擎，存储引擎的概念是MySQL的特点，Oracle中没有专门的存储引擎的概念，Oracle有OLTP和OLAP模式的区分。不同的存储引擎决定了MySQL数据库中的表可以用不同的方式来存储。我们可以根据数据的特点来选择不同的存储引擎。

引擎包括三种，innodb、myisam、memory，各有优缺点。

其中，innoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制，支持回滚。缺点是读写效率较差，占用的数据空间相对较大(连表查询)，

MyISAM的表存储成3个文件。文件的名字与表名相同。拓展名为*frm*、*MYD*、*MYI*。其实，frm文件存储表的结构；MYD文件存储数据，是MYData的缩写；MYI文件存储索引，是MYIndex的缩写。

基于MyISAM存储引擎的表支持3种不同的存储格式。包括静态型、动态型和压缩型。

其中，静态型是MyISAM的默认存储格式，它的字段是固定长度的,该表具有极高的访问速度,即使在操作相当的数据表也是如此,但其最大的缺点就是占用空间较大;

动态型包含变长字段，记录的长度不是固定的,其优点是它存数据时,采用的是数据的实际长度,能节省不少空间,但也是由于这点,当数据要做更新时,长度发生变更时,  
就不会存在原来的位置,而存在别的位置,造成原来的位置形成一空洞,并且关联的数据并不是存放在相邻的块中,而且产生大量的碎片,要定期进行碎片整理;

压缩型需要用到myisampack工具，空间占用相当小,只有原大小的一半,而且读取数据时,还会对数据进行加压缩,并且注意该类型的表是只读表,不能进行修改.

MEMORY数据全部放在内存中,

每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为*frm*类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。注意，MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的

2、设置表格字段时，使用varchar代替char，因为变长字段记录的长度不是固定的，存储时采用的是数据的实际长度，节省空间，另外，尽量采用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。 这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了

3、创建表格时，添加索引，可以增加查询的速度，但是索引也不是越多越好，太多的索引会降低insert和update的效率，因为插入和更新时有可能会重建索引，一个表的索引最好不要超出6个，具体要根据需要确定是否有必要添加索引

第二部分sql语句部分：

1、尽量避免全表查询，不要出现select \* from table的情况，首先考虑where和order by

2、避免使用太过复杂的sql语句查询，尤其是出现嵌套的时候，最好不要出现三层以上的嵌套，否则会影响查询效率。

3、考虑使用临时表暂存，简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表。

4.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：      
select id from t where num is null      
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：      
select id from t where num=0      
      
5.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。      
      
6.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：      
select id from t where num=10 or num=20      
可以这样查询：      
select id from t where num=10      
union all      
select id from t where num=20      
      
7.in 和 not in 也要慎用，否则会导致全表扫描，如：      
select id from t where num in(1,2,3)      
对于连续的数值，能用 between 就不要用 in 了：      
select id from t where num between 1 and 3      
      
8.下面的查询也将导致全表扫描：      
select id from t where name like '%abc%'      
      
9.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：      
select id from t where num/2=100      
应改为:      
select id from t where num=100\*2      
      
10.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：      
select id from t where substring(name,1,3)='abc'--name以abc开头的id      
应改为:      
select id from t where name like 'abc%'      
      
11.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引