基本

• 索引是帮助数据高效查询的有序数据结构，
• 没有索引进行查询就会进行全表扫描
• myisam中的.MYI和innodb中的.idb都是存放索引的文件。
• 索引提高查询效率的同时，也降低了更新表的数据，因为数据库中删改查会维护索引的结构。
• 一般提到的索引就是B+树索引

数据结构

Hash索引

Hash索引的形成原理，以人员表中的name列索引为例：

1. 计算表中每行数据的hash值。
2. 对name列进行hash取模，以为键值对，存放至HashMap结构中，其中hash(name)表示name列的hash值，hash(row)表示步骤一中计算的对应行数据的hash值。
3. 如果产生hash冲突，也是使用链表的形式解决。

Hash索引特点：

• 只能进行对等比较，比如=或in，不支持范围查询，比如大于小于，between等（因为hashmap里是无序的）。
• 无法进行排序操作
• 查询效率比较高，通常一次检索就可查到数据，效率通常高于B+索引。

存储引擎的应用

• mysql中，支持hash索引的是memory引擎
• innodb中有hash自适应，hash索引是存储引擎根据B+树索引在指定的条件下自动构建的。

二叉树

• 左中右型树形结构，左子节点永远比父节点小，右子节点永远比父节点大，只有两个子节点。
• 缺点：
  • 当按照大小顺序插入时，会形成一个链表，查询性能大大降低。
  • 而且只有两个子节点，最大度数为2，当数据量庞大，会造成层数较深，不利于检索。
  • （树的度数是指一个节点的子节点个数）
• 相比于上述缺点，使用红黑树会解决顺序插入的链表问题，因为红黑树是个自平衡二叉树。 但是红黑树本身也是二叉树，所以解决不了数据量庞大时的层数较深问题。

B树（多路平衡查找树）

• 树形存储结构，特征如下：
  • 一个节点最多有5个子节点。
  • 每个节点最多存储4个key，5个指针，5个指针是穿插在4个key的中间，所以理论上，如果节点有N个key，那么节点上都会有N+1个指针。
  • 每个key都对应的是一条数据。
• 如下图，根节点4个key分别是20、30、62、89，和5个指针（图中5条黑线），如果数据位于20-30之间，那么就根据第二个指针找到第二个子节点，然后进行检索。

• 中间元素向上分裂原则（B树构建过程），使用数据可视化网站：
  • 数据1980在插入的时候，先和根节点的1200比对，比1200大，所以在最右子节点
  • 和最右子节点比对，发现比1800大，所以放在最右，那么这时候中间元素是1567。
  • 根据中间元素向上分裂原则，1567要向上提，放在根节点最右侧。

B+ 树

• 带有双向链表的树形存储结构，和Btree相比：
  • 只有叶子节点每个key对应数据
  • 叶子节点形成了一个单向链表
• mysql索引数据结构比经典B+树的基础上，增加指向临近叶子节点的链表指针，形成双向链表（注意下图展示的是经典B+树是单向链表，不是MYsql中的B+索引结构）

为什么innodb使用B+树索引，B+树索引的优点：

• 相比于红黑树/二叉树，层级更少，搜索的效率高。
• 相比于B树，非叶子节点（对应数据库中的一个页，一般一个页默认16K）不存储数据，可以留出更多的空间用来存放更多的key和指针，从而降低树的度和层数，更利于检索数据。
• 相比于Hash索引，B+树索引支持范围匹配和排序。

索引分类

• 主键索引：PRIMARY，默认自动创建，只能有一个
• 唯一索引：UNIQUE，可以有多个
• 常规索引：快速定位特定数据
• 全文索引：查找的是文本中的关键词，而不是比较索引中的值。

聚集索引和非聚集索引（二级索引）

• 聚集索引：将数据存储和索引放到了一起，索引结构的叶子节点保存了整条行数据，聚集索引在表中必须有，而且只有一个，否则表数据没办法存放。
• 二级索引（非聚集索引）：数据和索引分开存储，索引结构的叶子节点关联的是对应的数据主键（根据索引查到主键然后回表），一个表中可以存在多个非聚集索引。
• 聚集索引选取依据（无论如何表中的聚集索引是一定存在的）：
  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一索引作为聚集索引。
  • 如果没主键也没唯一索引，则Innodb会自动生成一个rowid作为隐藏的聚集索引。
• 回表查询：先根据二级索引检索到主键值，再根据聚集索引中拿到数据。

一个小小的计算题

假如innodb设置的页大小是16k，一行数据平均有1k，一个指针占用6个字节，主键key使用bigInt占用8个字节，请问：高度为2和3时分别大约可以存储多少条数据？

• 高度为2：
  1. 设置一个非叶子节点可以存储n个key，那么就会有n+1个指针指向第二层节点，又因为1k=1024字节，所以得出公式：n*8+(n+1)*6 = 16*1024
  2. 得出n为1170，1170即为根节点可以存放1170个key和1171个指针。
  3. 1171*16即得出可高度为2的树可存放数据量为18736条。
• 高度为3：
  • 前面的计算过程和高度为2一样，就是多乘了个1171:。
  • 1171117116得出高度为3的树可存放数据量为21939856条。

SQL性能分析

• SQL执行频次：通过show [session | global] status查看服务器状态信息，可以查看当前数据的增删改查次数：show global status like 'Com_______'。根据这个命令，可以查到Com_insert/Com_select/Com_update等执行的次数，session代表当前会话，global代表所有会话。
• SQL慢查询日志：慢查询记录了执行时间超过预设时间的所有SQL（默认10秒，默认不开启，需要在/etc/my.cnf中配置，配置后慢sql日志会被默认记录在/var/lib/mysql/localhost-slow.log文件中）
  • 查看是否开启慢sql：show variables like 'slow_query_log'
  • 配置/etc/my.cnf文件，打开慢sql记录，重启mysql后生效：

  # 开启慢sql日志记录
  slow_query_log=1
  # 设置慢日志的时间为2s
  long_query_time=2
  

• profile详情：相当于对SQL慢查询日志的补充，对于没有超过预设时间的sql语句，profile会进行记录。
  • 先查看当前MYSQL是否支持profile：select @@have_profiling，再查看profiling是否开启：select @@profiling
  • 如果没开启，使用set profiling = 1进行开启，，无需重启数据库。
  • 使用profile工具进行分析：

  # 查看每一条SQL耗时
  show profiles;
  # 根据query_id查看sql各阶段耗时
  show profile for query query_id;
  # 查看指定query_id的sql语句CPU使用情况
  show profile cpu for query query_id;
  

• explain执行计划，explain/desc + sql语句，各个字段的含义（最常用到的几个字段分别是type possible_keys key key_len rows Extra）：
  • id：查询的序列号，表示查询中select子句或操作表的顺序，如果有多个id相同，则代表从上到下依次执行，如果ID不同，值越大的约先执行。
  • select_type：select的类型，常见有simple（简单表，不用连接或子查询）、primary（主查询，外层查询）、union、subquery（子查询）
  • type：连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。
    • （null的性能最高，但是真实中不太可能，以为null多数为不查询任何表）
    • （根据主键或者唯一索引进行检索，会出现const）
    • （非唯一性索引列的查询会出现ref）
    • （index表示虽然用到了索引，但是也是全表扫描）
    • （all就指进行了全表扫描）
  • possible_key：表示可能用到的索引
  • key：实际用到的索引，null为没有使用任何索引。
  • key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精度前提下，肯定越短越好。
  • rows：表示必须要执行查询的行数，innodb表中，是一个估计值，可能不总是精确的。
  • filtered：表示返回的行数占读取行数的百分比，filtered是越接近100越好。
  • Extra：额外信息提示
    • 如果出现using index condition：查询使用到了索引，但是需要回表进行查询数据，如果查询结果列中存在没有索引的列，会出现这类提示，例如select *
    • 如果出现using where using index：查找使用了索引，但是结果列在索引列中都可以找到，不用回表查询操作。这种效率肯定比上面的高，推荐尽量使用这种。
    • 如果出现null，也表示要进行回表查询。

索引使用原则

• 最左前缀法则：
  • 在联合索引中，查询条件如果包含（where中不分顺序）联合索引中最左列，并且不跳过联合索引中任何列，索引即可生效
  • 如果跳跃了联合索引中的某一列，后序列会在索引中部分失效。
• 索引覆盖原则：查询结果列尽量使用索引列而不是select *，这样会大大减少回表操作。
• 尽量走聚集索引：where id = 1肯定会比where A = 'a'效率更高，即使A也是索引列，因为id作为聚集索引列，行数据挂在B+树叶子节点下，检索到1之后直接可以取出。而根据二级索引列A查询到后，还需要回表操作。
• 优先使用联合索引：当多个条件联合查询时候，MYSQL优化器会先评估哪个索引列的索引效率更高，然后会选择该索引进行查询，而不走其他索引，还会产生回表。所以考虑走联合索引，而非单列索引。
• 前缀索引原则：如果字段值很长，或者大文本字段要加索引的话，不建议对值的全长建立索引，因为在构建索引结构时候，会把这些值都存一份，会耗费磁盘IO和性能，也比较占据空间，那么我们选择截取字段值的前面一部分形成索引。
  • 和建立普通索引一样，只是列后边加个括号，说明前缀的长度create index idx_name_6 on tb_name(A(6))，这种索引名称一般加个_n，用来直观区分前缀索引及截取长度。
  • 那怎么确定用多少长度的索引合适呢？可以套用这个规则：select count(distinct substring(column_name, 1, n)) / count(*) from tb_name ，在公式计算结果接近于1的前提下，可以从大到小逐渐调整n的值，并得到最后合适的结果。公式的意思就是截取最少的前缀，以保证可以最大程度地覆盖所有的行数据。
  • 前缀索引的检索流程，以select * from user where address = '山东省济南市历城区科创路1000号'为例（已经建立了address长度为6的前缀索引）：
    1. 先把sql中address截取6位，得到“山东省济南市”。
    2. 在二级索引中进行查询，检索到key对应的id比如是5。
    3. 根据id为5进行聚集索引回表，得到row行数据。
    4. 关键一步：把row行数据中的address和条件语句中的山东省济南市历城区科创路1000号进行全长匹配，如果全长匹配上了，就认为这一条是想要的数据；如果没匹配上，回到步骤2中根据B+树的双向链表结构，对叶子节点进行下一条的检索，直到本步骤匹配上为止。
• 最大程度避免索引失效

索引失效情况

• 范围查询失效问题，以联合索引A B C三列为例：
  • 联合索引中，出现范围查询>或<号，范围查询右侧的列索引失效。
  • 例如where A='a' and B > 0 and C='c'中，C='c'就会失效，因为在B列范围查询的右侧。
  • 但是如果是B>=0，右侧的就不会失效，所以在真实只用中，尽量用>=而不是>
• 索引列进行运算会导致索引失效，例如加减、字符串截取等。
• 字符串类型字段如果不加’'单引号，数据类型隐式转换，索引会失效。
• 模糊查询导致索引失效：如果对字符串尾部进行模糊匹配，索引不会失效；如果是字符串尾部模糊匹配，索引会失效。
• or可能会索引失效：被or连接的所有条件中，只要有一列没有索引，其他列也不会走索引，A= 0 or B = 1，如果A有索引而B没有，则这个语句不会走索引。
• 数据分布分析：如果一些情况下，数据库评估使用索引还不如与走全表扫描快，那么就不会使用索引。

索引如何设计？

• 数据量较大（>100w条），且查询较频繁的表。
• 常作为查询条件、排序、分组的字段建立索引，建议建立联合索引。
• 尽量使用区分度高的列作为索引，例如手机号、身份证号等；性别列不建议作为索引列。尽量建立唯一索引，区分度越高，使用索引的效率也就越高。
• 字符串或长文本字段，尽量建立前缀索引。
• 索引不宜太多，只建立有必要的索引，索引越多，维护索引结构的代价也就越大，增删改查的效率就会越低。
• 尽量使用联合索引，减少单列索引，查询时候联合索引可以覆盖索引，节省存储空间避免回表。
• 如果索引列不能存储null，在创建表的时候使用非空约束。当优化器知道每列是否包含null时，可以更好确定哪个索引最有效地用于查询。

SQL提示

用来在sql语句中提示mysql用哪个索引

• 建议用哪个索引use index()，select * from tb_name use index(index_name) where A =''。
• 忽略哪个索引ignore index()
• 强制使用哪个索引force index()

问题

• B+树和redis里的跳表好像，有什么区别和联系？
• 当数据发生变化时候，索引需要手工维护吗？怎么维护？
• 什么叫索引长度？explain中的key_len？
• myisam中非聚集索引和innodb中的聚集索引
• myisam和innodb的使用场景。？
• mysql数据类型