不要一次更新太多数据
尽量多条更新一起提交,减少undo log 和redo log的刷盘次数
让事务尽快提交,不然mvcc日志没法清理
只读事务没有TRX_ID 的, autocommit =1
的select语句, START TRANSACTION READ ONLY
启动的事务。
innodb_buffer_pool_size
推荐配置为占系统可用内存的50%-75%
表join的时候,VARCHAR(10)
和 CHAR(10)
当相同字段对待,能使用索引。VARCHAR(10)
和 CHAR(15)
就不行。
数据类型不一致也不能使用索引
如果没有BLOB 列,那么 GROUP BY 和 ORDER BY可以使用 MEMORY storage engine,否则使用文件排序。
碩长无比的列最好单独成一个关联表,这样主表的一个数据块可以存放更多的记录从而减少磁盘io。
SELECT COUNT(*), COUNT(age) FROM person;
COUNT(age) 不计算null行
varchar 保留尾部的空格,char 获取的时候删除尾部的空格
pullout:转换成inner join
Duplicate Weedout:转换成inner join然后删除多余的行
FirstMatch:每个外层值只匹配subquery一行
LooseScan:使用索引选择subquery的一行
subquery table first and using its index to select one record from multiple duplicates
format:expr IN (SELECT tbl.keypart1 FROM tbl ...)
Materialize:subquery产生带索引的临时表
1 2 3 4 5 6 |
select null in (select dept_no from departments) as col1, null in (select dept_no from departments where dept_no='') as col2, 'd009' in (select dept_no from departments where dept_no='') as col3, 'd009' in (null ) as col4, null= null as col5, null=1 as col6; |
+------+------+------+------+------+------+
| col1 | col2 | col3 | col4 | col5 | col6 |
+------+------+------+------+------+------+
| NULL | 0 | 0 | NULL | NULL | NULL |
+------+------+------+------+------+------+
接下来我们看看这个语句的执行计划
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
Is converted into:
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND trigcond(outer_expr=inner_expr))
trigcond(X) := X when the "linked" outer expression oe_i is not NULL
trigcond(X) := TRUE when the "linked" outer expression oe_i is NULL
1 2 3 4 5 6 7 |
explain format=tree select null in (select dept_no from departments) as col1, null in (select dept_no from departments where dept_no='') as col2, '008' in (select dept_no from departments where dept_no='') as col3, 'd009' in (null ) as col4, null= null as col5, null=1 as col6\G |
输出结果
1 2 3 4 5 6 7 8 9 10 11 12 13 |
*************************** 1. row *************************** EXPLAIN: -> Rows fetched before execution (cost=0.00..0.00 rows=1) -> Select #2 (subquery in projection; run only once) -> Limit: 1 row(s) -> Filter: <if>(outer_field_is_not_null, (<cache>(NULL) = departments.dept_no), true) -> Alternative plans for IN subquery: Index lookup unless dept_no IS NULL -> Single-row covering index lookup on departments using PRIMARY (dept_no=<cache>(NULL)) -> Table scan on departments -> Select #3 (subquery in projection; run only once) -> Zero rows (no matching row in const table) (cost=0.00..0.00 rows=0) -> Select #4 (subquery in projection; run only once) -> Zero rows (Impossible WHERE) (cost=0.00..0.00 rows=0) |
由于Select #3和Select #4子查询结果集可以直接判定为空,所以输出很简单,
Select #2就解释了trigcond(X)函数的逻辑
Posted in: database
Comments are closed.