digoal
2022-09-10
PostgreSQL , duckdb , sqlite3
介绍SQLite3 的索引推荐功能. (duckdb也可以通过这个功能来支持索引推荐.)
sqlite> create table tbl (id int, info text, crt_time timestamp);
sqlite> create table tbl1 (id int, info text,crt_time timestamp);
sqlite> .expert
sqlite> select tbl.* from tbl join tbl1 on (tbl.id=tbl1.id) where tbl1.info='abc' and tbl.crt_time >='2022-01-01';
CREATE INDEX tbl_idx_c7458fc8 ON tbl(id, crt_time);
CREATE INDEX tbl1_idx_21d2b5d5 ON tbl1(info, id);
SEARCH tbl1 USING COVERING INDEX tbl1_idx_21d2b5d5 (info=?)
SEARCH tbl USING INDEX tbl_idx_c7458fc8 (id=? AND crt_time>?)
expert支持两个扩展选项, verbose, sample, 分别代表输出详情, analyze采样百分比.
sqlite> .expert --verbose --sample 100
sqlite> select tbl.* from tbl join tbl1 on (tbl.id=tbl1.id) where tbl1.info='abc' and tbl.crt_time >='2022-01-01';
-- Candidates -----------------------------
CREATE INDEX tbl_idx_c7458fc8 ON tbl(id, crt_time);
CREATE INDEX tbl_idx_00000415 ON tbl(id);
CREATE INDEX tbl_idx_20492837 ON tbl(crt_time);
CREATE INDEX tbl1_idx_21d2b5d5 ON tbl1(info, id);
CREATE INDEX tbl1_idx_000151d4 ON tbl1(info);
-- Query 1 --------------------------------
select tbl.* from tbl join tbl1 on (tbl.id=tbl1.id) where tbl1.info='abc' and tbl.crt_time >='2022-01-01';
CREATE INDEX tbl_idx_c7458fc8 ON tbl(id, crt_time);
CREATE INDEX tbl1_idx_21d2b5d5 ON tbl1(info, id);
SEARCH tbl1 USING COVERING INDEX tbl1_idx_21d2b5d5 (info=?)
SEARCH tbl USING INDEX tbl_idx_c7458fc8 (id=? AND crt_time>?)
其他例子, 发现一个小缺陷, 即使修改了默认的like识别大小写, 推荐的索引还是nocase的.
sqlite> create table tbl (id int, info text, crt_time timestamp);
sqlite> .expert
sqlite> select * from tbl where info like '%abc%' limit 1;
(no new indexes)
SCAN tbl
sqlite> .expert
sqlite> select * from tbl where info like 'abc%' limit 1;
CREATE INDEX tbl_idx_f71e9789 ON tbl(info COLLATE NOCASE);
SEARCH tbl USING INDEX tbl_idx_f71e9789 (info>? AND info<?)
https://www.sqlite.org/pragma.html
PRAGMA case_sensitive_like = boolean;
The default behavior of the LIKE operator is to ignore case for ASCII characters. Hence, by default 'a' LIKE 'A' is true. The case_sensitive_like pragma installs a new application-defined LIKE function that is either case sensitive or insensitive depending on the value of the case_sensitive_like pragma. When case_sensitive_like is disabled, the default LIKE behavior is expressed. When case_sensitive_like is enabled, case becomes significant. So, for example, 'a' LIKE 'A' is false but 'a' LIKE 'a' is still true.
This pragma uses sqlite3_create_function() to overload the LIKE and GLOB functions, which may override previous implementations of LIKE and GLOB registered by the application. This pragma only changes the behavior of the SQL LIKE operator. It does not change the behavior of the sqlite3_strlike() C-language interface, which is always case insensitive.
https://www.sqlite.org/cli.html
| Option | Purpose |
|---|---|
‑‑verbose ---If present, output a more verbose report for each query analyzed. |
|
‑‑sample PERCENT ---This parameter defaults to 0, causing the ".expert" command to recommend indexes based on the query and database schema alone. This is similar to the way the SQLite query planner selects indexes for queries if the user has not run the ANALYZE command on the database to generate data distribution statistics. If this option is passed a non-zero argument, the ".expert" command generates similar data distribution statistics for all indexes considered based on PERCENT percent of the rows currently stored in each database table. For databases with unusual data distributions, this may lead to better index recommendations, particularly if the application intends to run ANALYZE. For small databases and modern CPUs, there is usually no reason not to pass "--sample 100". However, gathering data distribution statistics can be expensive for large database tables. If the operation is too slow, try passing a smaller value for the --sample option. |
