提高操作性能的最佳方法 SELECT是在查询中测试的一个或多个列上创建索引。
索引条目就像指向表行的指针，允许查询快速确定哪些行与子句中的条件匹配WHERE，并检索这些行的其他列值。可以索引所有 MySQL 数据类型。
尽管为查询中可能使用的每个列创建索引可能很诱人，但不必要的索引会浪费空间和时间，让 MySQL 决定使用哪些索引。
索引还增加了插入、更新和删除的成本，因为每个索引都必须更新。您必须找到合适的平衡点以使用最佳索引集实现快速查询。

索引优点

  1. 大大减少了服务器需要扫描的数据量
  2. 帮助服务器避免排序和临时表
  3. 将随机io变成顺序io

索引分类

  • 主键
  • 唯一
  • 普通
  • 全文
  • 组合

索引数据结构

  • 哈希表 MEMORY表
  • B+Tree
    二叉树–红黑树–B树–B+树

技术名词

回表

普通索引列存储的是主键，叶子节点 存储的是主键 然后通过主键索引树查询到所要的记录
select * from table where name=?

覆盖索引

叶子节点 存储的是主键 查询到主键直接返回
select id from table where name=?

最左匹配

指的是联合（组合）索引中，优先走最左边列的索引。对于多个字段的联合索引，也同理
select * from table where name=? and age=?
select * from table where age=?
select * from table where name=?
优化调整
调换name,age顺序
单独age创建索引

索引占用磁盘空间，优先考虑相对占用少的字段建立索引

索引下推

组合索引（name,age）
where name=? and age=?
在存储引擎提取数据的时候直接过滤name,age,减少在server读取数据

谓词下推

select t1.name,t2.name from t1 join t2 on t1.id=t2.id

  1. t1 t2按照id 关联 再取出t1和t2的name
  2. 先取出 t1 t2的 id和name 再进行关联

组合索引 > 索引合并（MySQL优化器）

索引匹配方式

  1. 全值匹配
  2. 匹配最左前缀
  3. 匹配列前缀
  4. 匹配范围值
  5. 精确匹配某一列并范围匹配另外一列
  6. 只访问索引的查询
    Using index索引覆盖

哈希索引

哈希冲突
CRC32（循环冗余校验算法）

组合索引

当包含多个列作为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要
顺序问题
存储空间问题

聚簇索引与非聚簇索引

  1. 聚簇索引(InnoDB)
    不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起
    优点
    1、可以把相关数据保存在一起
    2、数据访问更快，因为索引和数据保存在同一个树中
    3、使用覆盖索引扫描的查询可以直接使用页节点中的主键值
    缺点
    1、聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势
    2、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式
    3、更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
    4、基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
    5、聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
  2. 非聚簇索引(MyISAM)
    数据文件跟索引文件分开存放

页分裂
页合并

在大批量导入数据时，先禁用索引, 在完全导入后, 再启用索引。比一次性完成效率高

1
2
3
4
# 关闭索引
ALTER TABLE table DISABLE KEYS;
# 开启索引
ALTER TABLE table ENABLE KEYS;

覆盖索引

如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引
不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值
不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory不支持覆盖索引

优势
1、索引条目通常远小于数据行大小，如果只需要读取索引，那么mysql就会极大的较少数据访问量
2、因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多
3、一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题
4、由于INNODB的聚簇索引，覆盖索引对INNODB表特别有用

消耗资源
IO密集型
CPU密集型

1
2
3
4
5
6
7
8
show index from city;

+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY           |            1 | city_id     | A         |         600 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_fk_country_id |            1 | country_id  | A         |         109 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

基数 Cardinality
数据去重之后的唯一值
联机分析处理OLAP是一种软件技术，它使分析人员能够迅速、一致、交互地从各个方面观察信息，以达到深入理解数据的目的
HyperLogLog

实践

1

优化

  1. 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层
    select actor_id from actor where actor_id=4;
    select actor_id from actor where actor_id+1=5;

  2. 尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询
    使用前缀索引
    前缀索引实例说明.md

  3. 使用索引扫描来排序
    使用索引扫描来做排序.md

  4. union all,in,or都能够使用索引，但是推荐使用in
    explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
    explain select * from actor where actor_id in (1,2);
    explain select * from actor where actor_id = 1 or actor_id =2;

  5. 范围列可以用到索引
    范围条件是：<、<=、>、>=、between
    范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列
    强制类型转换会全表扫描
    explain select * from user where phone=13800001234;
    不会触发索引
    explain select * from user where phone=’13800001234’;
    触发索引
    更新十分频繁，数据区分度不高的字段上不宜建立索引
    更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能
    类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据，
    一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算
    创建索引的列，不允许为null，可能会得到不符合预期的结果

  6. 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致
    join 内部实现（mapjoin）

嵌套循环连接算法

r为驱动表，s为匹配表，从r中分解取出每一行记录去配比s的列，然后再合并数据，对s表进行r表的行数次访问，对数据库的开销比较大
这个要求非驱动表(匹配表s) 上有索引，可以通过索引来减少比较，加速查询。在查询时，驱动表 (r) 会根据关联字段的索引进行查找当在索引上找到符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表查询。如果非驱动表 (s) 的关联健是主键的话，性能会非常高如果不是主键，要进行多次回表查询，先关联索引，然后根据二级索引的主键ID进行回表操作，性能上比索引是主键要慢
如果有索引，会选取第二种方式进行join，但如果join列没有索引，就会采用Block Nested-Loop Join。可以看到中间有个join buffer缓冲区，是将驱动表的所有join相关的列都先缓存到join buffer中，然后批量与匹配表进行匹配，将第一种多次比较合并为一次，降低了非驱动表 (s) 的访问频率。默认情况下join buffer size=256K，在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。
块嵌套循环连接算法

https://dev.mysql.com/doc/refman/8.0/en/nested-loop-joins.html

能使用limit的时候尽量使用limit
单表索引建议控制在5个以内
单索引字段数不允许超过5个（组合索引）
创建索引的时候应该避免以下错误概念
索引越多越好
过早优化，在不了解系统的情况下进行优化

union all 优先级高于 union
union会自动压缩多个结果集合中的重复结果,而union all则将所有的结果全部显示出来,不管是不是重复

exists() 子查询

参考

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

工具

数据结构可视化