1:硬件,2:网络I/O,3查询时间
查询的时间相关:数据表结构的建立,索引的应用,SQL语句的使用
我们后端工程师能够把握的主要是表结构,索引和SQL语句方面,可以通过工程师的智慧来减小公司的成本,优化用户的体验
目前的计算机系统使用的都是分时系统,可能是一个进程给一个用户提供服务。计算机在不同的进程间快速的切换(针对的是单核的原理),如果一个进程占用了太长的时间,不近会影响进程的排序时间,也会因为保存上下文占用空间的资源。所以优化查询就变的很重要了
2更好的查询:时间和空间使用的更少(内存和磁盘消耗,响应时间)
扫描的行数和返回的行数的比例较小
3.索引的优点
索引能减少服务器扫描数据量
可以帮助服务器避免排序和临时表
可以将随机I/O变为顺序I/O
创建索引的一个基本的判断
count(distinct field) / count(*)
4. B-tree的使用场景
更适合有比较的范围的查询:>,<,between
因为只用叶子节点存储数据,其余的节点存储的是数据的范围值(
比保存在左子树的任何键值都要大比保存在右子树的任何键值都要小
)
使用索引的经验之谈:where条件中,group by中的,表连接中的on判断语句中的都可以根据实际的情况来创建索引
使用的是多列的索引还是单列的索引也要根据实际的情况来判定
5.SQL的执行顺序呢
清楚SQL执行顺序以后就能够更加深刻的理解SQL的查询过程和优化的
第一步,执行FROM
语句。我们首先需要知道最开始从哪个表开始的,这就是FROM
告诉我们的。现在有了<left_table>
和<right_table>
两个表,我们到底从哪个表开始,还是从两个表进行某种联系以后再开始呢?它们之间如何产生联系呢?——笛卡尔积
关于什么是笛卡尔积,请自行Google补脑。经过FROM语句对两个表执行笛卡尔积,会得到一个虚拟表,暂且叫VT1(vitual table 1),内容如下:
总共有28(table1的记录条数 * table2的记录条数)条记录。这就是VT1的结果,接下来的操作就在VT1的基础上进行。
执行ON过滤
执行完笛卡尔积以后,接着就进行ON a.customer_id = b.customer_id
条件过滤,根据ON
中指定的条件,去掉那些不符合条件的数据,得到VT2表,内容如下:
VT2就是经过ON
条件筛选以后得到的有用数据,而接下来的操作将在VT2的基础上继续进行。
添加外部行
这一步只有在连接类型为OUTER JOIN
时才发生,如LEFT OUTER JOIN
、RIGHT OUTER JOIN
和FULL OUTER JOIN
。在大多数的时候,我们都是会省略掉OUTER
关键字的,但OUTER
表示的就是外部行的概念。
LEFT OUTER JOIN
把左表记为保留表,得到的结果为:
RIGHT OUTER JOIN
把右表记为保留表,得到的结果为:
FULL OUTER JOIN
把左右表都作为保留表,得到的结果为:
添加外部行的工作就是在VT2表的基础上添加保留表中被过滤条件过滤掉的数据,非保留表中的数据被赋予NULL值,最后生成虚拟表VT3。
由于我在准备的测试SQL查询逻辑语句中使用的是LEFT JOIN
,过滤掉了以下这条数据:
现在就把这条数据添加到VT2表中,得到的VT3表如下:
接下来的操作都会在该VT3表上进行。
执行WHERE过滤
对添加外部行得到的VT3进行WHERE过滤,只有符合<where_condition>的记录才会输出到虚拟表VT4中。当我们执行WHERE a.city = 'hangzhou'
的时候,就会得到以下内容,并存在虚拟表VT4中:
但是在使用WHERE子句时,需要注意以下两点:
- 由于数据还没有分组,因此现在还不能在WHERE过滤器中使用
where_condition=MIN(col)
这类对分组统计的过滤; - 由于还没有进行列的选取操作,因此在SELECT中使用列的别名也是不被允许的,如:
SELECT city as c FROM t WHERE c='shanghai';
是不允许出现的。
执行GROUP BY分组
GROU BY
子句主要是对使用WHERE
子句得到的虚拟表进行分组操作。我们执行测试语句中的GROUP BY a.customer_id
,就会得到以下内容:
得到的内容会存入虚拟表VT5中,此时,我们就得到了一个VT5虚拟表,接下来的操作都会在该表上完成。
执行HAVING过滤
HAVING
子句主要和GROUP BY
子句配合使用,对分组得到的VT5虚拟表进行条件过滤。当我执行测试语句中的HAVING count(b.order_id) < 2
时,将得到以下内容:
这就是虚拟表VT6。
SELECT列表
现在才会执行到SELECT
子句,不要以为SELECT
子句被写在第一行,就是第一个被执行的。
我们执行测试语句中的SELECT a.customer_id, COUNT(b.order_id) as total_orders
,从虚拟表VT6中选择出我们需要的内容。我们将得到以下内容:
不,还没有完,这只是虚拟表VT7。
执行DISTINCT子句
如果在查询中指定了DISTINCT
子句,则会创建一张内存临时表(如果内存放不下,就需要存放在硬盘了)。这张临时表的表结构和上一步产生的虚拟表VT7是一样的,不同的是对进行DISTINCT操作的列增加了一个唯一索引,以此来除重复数据。
由于我的测试SQL语句中并没有使用DISTINCT,所以,在该查询中,这一步不会生成一个虚拟表。
执行ORDER BY子句
对虚拟表中的内容按照指定的列进行排序,然后返回一个新的虚拟表,我们执行测试SQL语句中的ORDER BY total_orders DESC
,就会得到以下内容:
可以看到这是对total_orders列进行降序排列的。上述结果会存储在VT8中。
执行LIMIT子句
LIMIT
子句从上一步得到的VT8虚拟表中选出从指定位置开始的指定行数据。对于没有应用ORDER BY的LIMIT子句,得到的结果同样是无序的,所以,很多时候,我们都会看到LIMIT子句会和ORDER BY子句一起使用。
MySQL数据库的LIMIT支持如下形式的选择:
LIMIT n, m
是非常低效的。因为LIMIT的机制是每次都是从头开始扫描,如果需要从第60万行开始,读取3条数据,就需要先扫描定位到60万行,然后再进行读取,而扫描的过程是一个非常低效的过程。所以,对于大数据处理时,是非常有必要在应用层建立一定的缓存机制(貌似现在的大数据处理,都有缓存哦)。各位,请期待我的缓存方面的文章哦。
多列索引中要遵循最左前缀的规则name_sex_age组成的多列索引,在使用name,name_sex,name_sex_age这三种情况的时候才会被调用
单列索引,如果分别对name,sex,age建成索引的话,在查询的时候回调用一个最严格的索引
所以如果有多列索引的时候,最左端的字段可以不用再建立一个单列索引
where条件的顺序和查找的时间无关,MySQL的内部优化器会按照最合理的索引顺序来进行查找
8.导入大的SQL文件
max_allowed_packet限制了传递的包的大小
客户端向服务端发送sql的时候是一个单独的数据包
Mysql在数据小于排序缓冲区的时候使用的是快速排序,内存不够的时候使用的是分块排序,最后聚合
磁盘访问太大,要记录每一次的排序结果
Mysql不是先查询内层,然后使用IN语句,他是把外层压到内层
select * from A where id IN (select a_id from B where status = 1)
mysql 会转换成select * from A where EXISTS(select * from B where status =1 and id = a_id)
子查询要用到id,id是未知的,所以先执行外层
(摘选自《高性能Mysql》)
11.优化limit
limit的数据较大的时候读取的行数更多,舍弃的行数就多了
使用延迟关联可以提高查询的性能
select id from A INNER JOIN (select id from A order by title limit 50,5) AS b ON a.id = b.id
使用其余的方法缩小查询的范围
如果有主键的where条件
使用where id&gt; 50 limit 5
12.强大的内部函数
参考:
http://www.jellythink.com/archives/924