针对数据分析来说,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 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引