SQL索引优化

https://use-the-index-luke.com/3-minute-test/mysql indexed top-N query B-tree traversal (log(n)) B+tree simulator Index-Only Scan

1 索引使用函数的影响 Unnecessarily using functions on indexed columns in the where clause

CREATE INDEX tbl_idx ON tbl (date_column)


SELECT COUNT(*)
  FROM tbl
 WHERE EXTRACT(YEAR FROM date_column) = 2017


# 不会读取全表,  但是会读取所有的索引

修改 避免使用函数

SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= DATE'2017-01-01'
   AND date_column <  DATE'2018-01-01'

2 索引支持 WHERE 和 ORDER BY

CREATE INDEX tbl_idx ON tbl (a, date_column)

SELECT *
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 LIMIT 1


#  The database uses the index to find the last entry that matches the where clause and takes it as result. Even though there is an order by clause, there is no need to sort any rows.

3 索引顺序

CREATE INDEX tbl_idx ON tbl (a, b)


SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1
   
# The index covers 
   
SELECT *
  FROM tbl
 WHERE b = 1

#  cannot use the index efficiently
#  still read the full index end to end.
#  Indexes can only be used from left to right side. If the first index column is not in the where clause, the index is of little help.

4 通配符号 %, _ 放在开头索引无效, 放在后面索引有效

CREATE INDEX tbl_idx ON tbl (text)


SELECT *
  FROM tbl
 WHERE text LIKE 'TJ%'


#  LIKE 'TJ%'  有效 
#  LIKE 'T%J'  有效 

#  LIKE '%TJ%' 无效 

5 使用 index-only scan

Avoid select * to increase chances for an index-only scan.

CREATE INDEX tbl_idx ON tbl (a, date_column)


SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
 GROUP BY date_column



SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
   AND b = 1
 GROUP BY date_column
 
# 增加过滤条件 

# 访问不属于索引的任何列均会阻止此优化， 数据库必须针对符合原始where子句的每一行查看实际表，以查看其是否也满足新过滤条件