近段时间学习SQL将遇到的问题总结
连接与合并的效率问题
使用SQL语句连接和合并时候发现效率差距真的非常巨大,特别是在数据量超过百万条的项目上,看到他人总结的是
在有索引的情况下,连接效率更高,在没有索引的情况下,合并效率更高
但个人觉得还有一个因素
在连接的两张表都是WITH创建的临时表的情况下,合并效率更高,因为使用连接的情况下,两张临时表也需要占用资源和时间来进行查询操作,再进行连接,而合并只用进行查询拼接就可以了,这样就节省了效率
关于全连接FULL JOIN问题
全连接是Oracle的连接方式,在MySQL中只有左右连接和内连接,但在MySQL中我们也可以通过左右连接和合并来实现全连接的操作方式,具体代码如下
SELECT a.id AS aid,a.name AS a_name,b.name AS b_name FROM a_table_name RIGHT JOIN b_table_name
UNION ALL
SELECT a.id AS aid,a.name AS a_name,b.name AS b_name FROM a_table_name LEFT JOIN b_table_name
连接效率问题
内连接 > 左右连接 > 全连接
所以在使用全连接时要慎重考虑,特别是在数据量较大时,特别影响效率
创建索引问题
在已经被创建好的表上面添加索引需要注意不要 CREATE INDEX 语句,可能会出现索引无法使用的问题,需要使用 ALTER TABLE table_name ADD INDEX index_name(column_name)
查看查询语句效率如何函数 Explain(sql)
索引失效情况
sql语句条件中带OR,索引失效
对于多列索引,不适用第一部分作为查询字段,索引失效(顺序不变)
like模糊查询以%开头,索引失效
生成新字段递增id问题
ROW_NUMBER() OVER(PARTITION BY column_name ORDER BY column_name)
该函数可以生成一个新的字段,如果没有参数,该字段随着数据递增,其中PARTITION BY为分组,OREDER BY 为排序,该函数适用与大多数计算场景,但注意低版本MySQL不能使用该函数
低版本MySQL虽然不能使用该函数,但还有其他办法同样可以有新生成一个递增字段的作用
set @rown = 0;
SELECT @rown = @rown+1 AS id,* FROM table_name
我们生成的rown变量可以随着数据加一