数据库调之索引和语句优化

https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html

B-Tree和B+Tree的差异

• B+Tree有n个子节点的节点中有n个关键字
• B-Tree是n个子节点的节点中有n-1个关键字
• B+Tree中，所有的叶子节点中包含了全部关键字的信息，且叶子节点按照关键字的大小顺序连接
• B-Tree的叶子节点不包含全部关键字
• B+Tree中，非叶子节点仅用于索引，不保存数据记录，记录存放在叶子节点中
• B-Tree中，非叶子节点既保存索引也保存数据记录

InnoDB存储方式

B+Tree索引

主键索引：叶子节点存储主键及数据

非主键索引（二级索引，辅助索引）:叶子节点存储主键以及索引

MyISAM存储方式

B+Tree索引

主键/非主键索引：叶子节点都是存储指向数据块的指针，索引和数据时分开的

 

Hash索引

B树索引和哈希索引的比较

B树索引可以在使用表达式中使用的对列的比较 =， >， >=， <， <=，或BETWEEN。LIKE 如果to的参数LIKE是一个不以通配符开头的常量字符串，则该索引也可以用于比较 。

例如，以下SELECT语句使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

以下SELECT语句不使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

在第一个语句中，该LIKE 值以通配符开头。在第二条语句中，该LIKE值不是常数

 

没有覆盖子句中所有AND级别的 任何索引都 WHERE不会用于优化查询。换句话说，为了能够使用索引，必须在每个AND组中使用索引的前缀 。

以下WHERE子句使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

    /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2

    /* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5

    /* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

这些WHERE子句 不使用索引：

 

    /* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2

    /*  Index is not used in both parts of the WHERE clause  */
... WHERE index=1 OR A=10

    /* No index spans all rows  */
... WHERE index_part1=1 OR index_part2=10

Hash索引并不是按照索引值排序，无法使用排序

不支持部分索引列查找

只支持等值查询例如=，in,不支持范围查询和模糊查询

索引失效常见的场景

• 索引不是单独的列，举例where emp+1 = 1000;或者where  SUBSTRING(name,1,2) ='zhansan' 
• 使用了左模糊，举例where name like '%zhansan' 
• or查询字段没有索引
• 字符串条件未使用''   举例 where emp_no = 1000
• 不符合最左匹配原则
• 索引字段尽量not null
• 隐式转换导致失效

索引调优技巧

• 长字段的调优

1. 可以另建一个索引字段用于存储长字段值的hash值，CRC32()或者FNV64()

举例 where name_hash=CRC32('zhansasn') and name='zhansasn'

    2. 针对模糊查询长字段，建立前缀索引

索引选择性 = 不重复的索引值/数据表的总记录数，数值越大，表示选择性越高，性能越好

• 多个单列索引可以优化成组合索引
• 覆盖索引：查询字段的值直接从索引字段中获取
• 去除重复索引，冗余索引，未使用的索引

SQL语句优化

https://dev.mysql.com/doc/refman/8.0/en/statement-optimization.html

熟练掌握NLJ和BNLJ的原理

join调优技巧

• 用小表驱动大表（一般会自动优化）
• join字段创建索引并且类型要保持相同

limit分页调优技巧

• 使用覆盖索引

select emp_no from employee limit 30000,10

• 覆盖索引+join

select *  from employee e innor join
( select emp_no from employee limit 30000,10 ) t
on e.emp_no = t.emp_no

• 覆盖索引+子查询

select *  from employee where emp_no >=
( select emp_no from employee limit 30000,1 ) t
limit 10;

• 传入起始索引和结束索引

select *  from employee where emp_no between 30000 and 30010

count语句优化

https://dev.mysql.com/doc/refman/5.7/en/functions.html 

• count(*)

1. 会选择最小的非主键索引，如果不存在任何非主键索引就会使用主键索引
2. 不会排除为null的行

• count(字段)

1. 只会针对该字段统计，使用这个字段上的索引如果有的话
2. 会排除掉该字段值为null

• count(*)和count(1) 一样

order by语句优化

https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

group by语句优化

https://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html

调优工具percona tooklit

https://www.percona.com/doc/percona-toolkit/LATEST/index.html