乐趣区

关于java:MySql索引分析及查询优化

jiezi

B-Tree

  1. 外围特点:

    1. 多路，非二叉树
    2. 每个节点既保留索引，又保留数据
    3. 搜寻时相当于二分查找

B+Tree

  1. 外围特点

    1. 多路非二叉
    2. 只有叶子节点保留数据
    3. 搜寻时相当于二分查找
    4. 减少了相邻接点的指向指针。

B-Tree VS B+Tree

  1. B+ 树查问工夫复杂度固定是 log n，B- 树查问复杂度最好是 O(1)。
  2. B+ 树相邻接点的指针能够大大增加区间拜访性，可应用在范畴查问等，而 B - 树每个节点 key 和 data 在一起，则无奈区间查找。
  3. B+ 树更适宜内部存储，也就是磁盘存储。因为两头节点无 data 域，每个节点能索引的范畴更大更准确。

MySQL InnoDB

数据存储:

  1. 数据结构依照 B + 树存储，自身就是索引，又叫聚簇索引
  2. key 为主键
  3. InnoDB 要求表必须要有主键
  4. 若未显示指定，则主动抉择惟一标识记录列为主键，若不存在则主动创立

一般索引:

  1. 叶子节点寄存的主键，而不是行的物理地址
  2. 须要两次检索，（1）检索主键（2）依据主键检索数据
  3. 存主键益处：页决裂或数据挪动时不须要变动
  4. 主键要设计的尽可能小，起因：每个索引都会存，过大节约空间
  5. 主键最好有序，缩小索引保护开销

MySQL InnoDB 页大小:

SHOW VARIABLES LIKE 'innodb_page_size';

查找关键字 = 8 的数据:

  1. 根页在表空间中地位固定。
  2. 根页装入内存，查找到指针 P6
  3. P6 指向的页装入内存
  4. 二分查找找到 8

InnoDB 中高度为 3 的 B + 树寄存:

  1. 假如数据表中一行数据为 1K。
  2. 假如主键 ID 为 bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 1 占用 14 字节。
  3. 一个页中寄存指针数目（索引个数）：
  4. 16KB（16*1024=16384 byte）16384/14=1170
  5. 高度为 2 的 B + 树，能寄存 1170*16=18720 条数据记录。
  6. 高度为 3 的 B + 树，能寄存：
  7. 1170（索引个数）1170（索引个数）16（每页行数）=21902400（2 千万）条这样的记录

高性能索引策略:

  1. 独立的列：

    1. 索引列不能是表达式的一部分，也不能是函数的参数。
    2. 如：select x ,y,z from table where x+1 = 2;
    3. 如果 x 是索引，那上述 sql 无奈利用索引，应写成：select x ,y,z from table where x = 1;
  2. 索引选择性 ：不反复的索引个数(X，X<=T) 和数据表的记录总数（T）的比值，范畴在 X/T 到 1 之间。索引的选择性越高则查问效率越高。惟一索引的 X =T，其选择性为 1，所以惟一索引的性能最好。

  3. 前缀索引：

    1. 对于 TEXT 或是 VARCHAR 类型的列，当这个列中的值长度很大又必须利用其进行查问时，就必须应用这个列的前几位值以作索引，即前缀索引，因为整个列的值当做索引时 B +tree 会占用十分大的空间，查找也不不便。
    2. 前缀索引的制订准则：前缀索引的选择性须要和整个列的选择性靠近，这样性能不会影响太多，同时还不能太长而占用太多空间。

    3. 如何寻找最佳前缀索引？

      1. 假如：有一个表中的某一列，名为 testcol，类型为 varchar(100)
      2. 计算残缺列的选择性: SELECT COUNT(DISTINCT testcol) / COUNT(*) FROM table;
      3. 计算前缀长度为 x 的选择性:SELECT COUNT(DISTINCT LEFT( testcol, x)) / COUNT(*) FROM table;
      4. 扭转 x 的值来计算不同前缀的选择性，最初在多个值中，综合思考选择性靠近性和前缀长度的两个方面，能够选出一个较为适合的前缀索引。

  4. 多列索引

    1. select x,y,z from table where x=1 and y=1;
    2. 在 Mysql 执行查问时，如果是应用多列索引 key(x,y)，则会先查问合乎第一列索引的数据集，而后再在这一部分数据集中查问出合乎第二列的数据，以此类推，这样在不必扫描数据的状况下就能选出数据；
    3. 而如果一个多列索引拆分成多个单列索引 (key(x),key(y)) 的话，Mysql 在执行查问时，只会从中选出一个限度最严格的索引以供应用，其余的索引就节约了，所以在上述情况中多列索引性能要好。

  5. 索引程序

    1. select x,y,z from table where x=1 and y=1;
    2. x=1 and y = 1 还是 y=1 and x=1?
    3. 将选择性高的索引列放在后面；索引列依照选择性从高到低搁置

  6. 笼罩索引

    1. 如果一个索引蕴含了所有须要查问的字段的值，就称之为“笼罩索引”
    2. InnoDB 存储引擎应用聚簇索引，笼罩索引可防止回表查问。因为 InnoDB 的二级索引的 B -Tree 的叶结点存储的是对应的一级索引，所以如果二级索引笼罩了所要查问的值则会少一次利用一级索引查问，晋升效率。
    3. 当发动一个索引笼罩查问时，在执行打算的 Extra 列中可见“Using index”的信息。

  7. 索引冗余

    1. 当存在 key (a, b)索引时，如果再创立一个 key (a)就是多余的，因为它只是多列索引的前缀而已。
    2. 然而当创立 key (b)时，就不属于冗余索引了，因为上述的多列索引是无奈独自应用 b 作索引查问

  8. 索引相干问题

    1. 索引越多越好? 数据更新时须要保护索引，带来开销，按需创立。
    2. 哪些列适宜建索引？选择性较高的列，一些常量和枚举的字段（如：性别）不适宜建索引，选择性较低还减少保护老本，得失相当。
    3. 索引字段类型？自增字段，表比拟大时，uuid 等没有法则的字段不太适宜作为主键

MySQL 执行打算

  1. select_type

    id select_type description
    1 SIMPLE 不蕴含任何子查问或 union 等查问
    2 PRIMARY 蕴含子查问最外层查问就显示为 PRIMARY
    3 SUBQUERY 在 select 或 where 字句中蕴含的查问
    4 DERIVED from 字句中蕴含的查问
    5 UNION 呈现在 union 后的查问语句中
    6 UNION RESULT 从 UNION 中获取后果集

  2. type

    type description
    ALL 扫描全表数据，不走索引
    index 遍历索引，需扫描索引的全副数据，如：select count(*) from tableA
    range 索引范畴查找
    index_merge 索引合并，多个单列索引，如 and 条件，or 条件
    index_subquery 在子查问中应用 ref
    unique_subquery 在子查问中应用 eq_ref
    ref_or_null 对 Null 进行索引的优化的 ref
    fulltext 应用全文索引，分词搜寻
    ref 应用非惟一索引查找数据，相似 eq_ref，区别是索引非惟一
    eq_ref 在 join 查问中应用 PRIMARY KEY or UNIQUE NOT NULL 索引关联。
    const 应用主键或者惟一索引，且匹配的后果只有一条记录。
    system const 连贯类型的特例，查问的表为零碎表，往往不须要进行磁盘 IO。
  3. possible_keys: 可能应用的索引，留神不肯定会应用。查问波及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL 时就要思考以后的 SQL 是否须要优化了。

  4. key

    1. 显示 MySQL 在查问中理论应用的索引，若没有应用索引，显示为 NULL。
    2. TIPS: 查问中若应用了笼罩索引(笼罩索引：索引的数据笼罩了须要查问的所有数据)，则该索引仅呈现在 key 列表中。
    3. select_type 为 index_merge 时，这里可能呈现两个以上的索引，其余的 select_type 这里只会呈现一个

  5. key_length：索引字段长度

    1. char()、varchar()索引长度的计算公式：

      (Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(容许 null) + 2(变长列)

    2. int 索引长度的计算公式：4+ 1(容许 null)

  6. extra: extra 的信息十分丰盛，常见的有：

    1. Using index 应用笼罩索引
    2. Using where 应用了用 where 子句来过滤后果集
    3. Using filesort 应用文件排序，应用非索引列进行排序时呈现，十分耗费性能，尽量优化。
    4. Using temporary 应用了长期表。

sql 优化倡议

  1. SQL 语句不要写的太简单：一个 SQL 语句要尽量简略，不要嵌套太多层。
  2. 应用『长期表』缓存两头后果：简化 SQL 语句的重要办法就是采纳长期表暂存两头后果，这样能够防止程序中屡次扫描主表，也大大减少了阻塞，进步了并发性能。
  3. 应用 like 的时候要留神是否会导致全表扫描：有的时候会须要进行一些含糊查问比方 select id from table where username like‘%abc%’。关键词 %abc%，因为 abc 后面用到了“%”，因而该查问会应用全表扫描，除非必要，否则不要在关键词前加 %，
  4. 尽量避免应用 not in,!= 或 <> 操作符：在 where 语句中应用 not in,!= 或 <>，引擎将放弃应用索引而进行全表扫描。

  5. 尽量避免应用 or 来连贯条件：

    1. 用 or 宰割开的条件，如果 or 前的条件中的列有索引，而前面的列中没有索
      引，那么波及到的索引都不会被用到。
    2. 应尽量避免在 where 子句中应用 or 来连贯条件，否则将导致引擎放弃应用索引而进行全表扫描，如：假如 num1 有索引，num2 没有索引，查问语句 select id from t where num1=10 or num2=20 会放弃应用索引，能够改为这样查问：select id from t where num1=10 union all select id from t where num2=20，这样尽管 num2 没有应用索引，但至多 num1 会应用索引，提高效率
  6. 尽量应用数字型字段：若只含数值信息的字段尽量不要设计为字符型，这会升高查问和连贯的性能，并会减少存储开销。

  7. 尽量不要让字段的默认值为 NULL：

    1. 在 MySQL 中，含有空值的列很难进行查问优化，因为它们使得索引、索引的统计信息以及比拟运算更加简单。
    2. 索引不会蕴含有 NULL 值的列，只有列中蕴含有 NULL 值都将不会被蕴含在索引中，复合索引中只
      要有一列含有 NULL 值，那么这一列对于此复合索引就是有效的。
    3. 所以咱们在数据库设计时尽量不要让字段的默认值为 NULL，应该指定列为 NOT NULL，除非你想存储 NULL。你应该用 0、一个非凡的值或者一个空串代替空值。
  8. 如果列类型是字符串，那么肯定记得在 where 条件中把字符常量值用引号引起来，否则的话即使这个列上有索引，MySQL 也不会用到的，因为 MySQL 默认把输出的常量值进行转换当前才进行检索。如：select * from t_student where std_name = 3;

  9. 慎用 insert into select。

    1. 语句：insert into tableA select * from tableB where date_time > ‘2020- 07-31’
    2. 问题剖析：该语句会导致 tableB 逐渐被锁定，其余操作无奈进行的问题。
    3. 解决方案：data_time 字段增加索引

  10. 索引 null 值问题

    1. 惟一索引 null 值

      1. 惟一索引中容许存在多行值为 NULL 的数据存在
      2. 联结惟一索引中存在 null 值，将失落唯一性，如 unique key(email,phone)，若 phone 为空将导致 email 雷同的多条记录存在
      3. 对 NULL 值的检索只能应用 is null / is not null / <=>，不能应用 =,<,> 这样的运算符
    2. 一般索引 null 值：null 值存在依然能够走索引
退出移动版