MySQL索引（基础）

MySQL

索引结构

Mysql的索引是在存储结构层实现的，不同的存储引擎有不同的结构，主要有

索引结构	描述
B+Tree索引	最常见的索引类型，大部分存储引擎都支持
Hash索引	底层数据结构是哈希表实现的，不支持范围查询
R-Tree空间索引	是MyISAM引擎的一个特殊索引类型，主要用于地理空间
Full-text全文索引	是通过建立倒排索引，快速匹配文档的方式。类似ES

二叉树（准备知识）

缺点：顺序插入时，会形成一个链表，查询效率还是很低。大数据下层级深，检索速度慢。

B-Tree（多路平衡查找数）（准备知识）

特点：每个节点（页）的数据都是有序排列的，数据会存储在每个节点（页）中。

缺点：每个页的大小时16K，数据和指针都存在一个页中，导致每个页能存储的数据有限，如果数据大的话层级还是会比较深。

B+Tree

特点：所有数据都存在叶子节点中，非叶子节点用来存key和指针，这样每个页就能最大限度的存储内容，层级在3层时大概能存2000多万数据。大于3000W可能深度就是4了，效率会下降，这时候就要考虑分库分表了。

Hash索引（查询效率高）

Hash索引采用的是hash算法，将键值换算成hash值，映射到对应的槽上，然后存储到hash表中。

如果出现两个相同的hash值（哈希碰撞），通过链表来解决（这里在HashMap中引入红黑树，因为链表长了也会影响效率）

为什么InnoDB存储引擎选择使用B+Tree索引结构

InnoDB中对B+Tree进行了优化，在叶子节点之间增加了双向链表

索引分类

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中列值重复	可以有多个	UNIQUE
常规索引	快速定位数据	可以有多个
全文索引	查找的文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

InnoDB存储引擎中，根据索引的存储形式，又分为以下两种

分类	含义	特点
聚集索引（clustered Index)	将数据存储和索引放到了一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引（Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存放多个

• 聚集索引选取规则：
  • 如果存在主键，那就主键就是聚集索引
  • 如果没有主键，那就选取唯一索引（UNIQUE）为聚集索引
  • 如果没有主键，也没有唯一索引，InnoDB会自动生成一个rwoid作为隐藏的聚集索引

语法

1. 创建索引

   CREATE [UNIQUE|FULLTEXT|不加就是常规索引] INDEX index_name ON table_name(index_col_name,...);
   

2. 查看索引

   SHOW INDEX FROM table_name
   

3. 删除索引

   DROP INDEX index_name ON table_name;
   

性能分析工具

SQL执行频率

通过show [session|global] status 命令可以提供服务器状态信息。可以通过以下指令查看当前数据库INSERT、DELETE、UPDATE、SELECT的访问频次：

SHOW GLOBAL STATUS LIKE 'COM_______'; # 七个下划线

慢查询日志

慢查询日志记录了所有执行时间超过了指定时间（long_quey_time，单位：秒，默认：10秒）的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启，需要在my.cnf中配置如下信息：

# 开启MySQL慢查询日志开关
slow_query_log = 1
# 设置慢日志的时间为2S，sql语句执行超过2秒就会被视为慢查询，从而记录到日志
long_query_time = 2;

配置完毕后，重启MySQL服务。慢日志信息路径localhost-slow.log

profile

show profile 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数可以看见当前MySQL是否支持profile操作

SELECT @@have_profiling;

默认情况下profile是关闭的，可以通过set语句在session/grobal级别开启profiling

SET profile = 1;

• profile常用指令

  # 查看每一条sql的耗时基本情况
  show profiles；
  # 查看指定query_id的sql语句各个阶段的耗时情况
  show profile for query query_id;
  # 查看指定query_di的sql语句CPU情况
  show profile cpu query query_id;
  

explain 执行计划

字段	含义
id	select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)。
select_type	表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY(主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询）等
type	表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all 。
possible_key	哪些可能是key
key	实际使用的索引，如果为null，则没有使用索引
key_len	表示索引中使用的字节数，该值为索引最大可能长度，越短越好
rows	MySQL认为必须要执行的行数，在innodb引擎中，是一个估计值，可能并不总是正确的
filtered	表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。(比如只查询一行数据，MySQL也只读了一行就是100%)
Extra	另外的

索引的使用

最左前缀法则

如果一个索引有多列（联合索引），要遵守最左前缀法则：

• 比如建立一个联合索引（a,b,c）

  • where后用and连接的话最左边的索引a必须要有，后面的bc才有可能走

    # 走索引（abc都有与位置无关）
    select * from table_name where a=1 and b=2 and c=3
    # 不走（a必须存在）
    select * from table_name where b=2 and c=3
    # 索引部分失效，只走a索引（中间那个没了，其它两个存在且与位置无关）
    select * from table_name where a=1 and c=3
    

  • 范围查询（联合索引下）

    联合索引中出现范围查询（>,<），范围查询右侧的列索引失效

    #索引失效（a不能范围查询，否则无效换>=也不行）
    select * from table_name where a > 1 and b =2 and c=3
    #索引部分失效（只走ab,c失效）
    select * from table_name where a = 1 and b>2 and c=3
    

索引失效情况

索引列运算

# 失效（phone为索引的前提）
select * from table_name where substring(phone,10,2)

字符串不加引号

# 失效（managerid为字符串类型的数字）
EXPLAIN SELECT * FROM table_name WHERE age = 20 AND managerid = 1 AND job = '项目经理'

模糊查询

# 失效（%不能放在前面）
EXPLAIN select * from table_name like where name like "%张"

or的连接

两侧必须都有索引才会生效

SQL提示

sql提示，是优化数据库的重要手段，简单来说，就是在sql语句中加入一些认为的提示来达到优化操作的目的

use Index（建议MySQL使用这个索引）

select * from table_name  use index(index_name)

ignore Index（不适用这个索引）

select * from table_name ingore index(index_name)

force（必须使用这个索引）

select * from table_name force index(index_name)

覆盖索引

select * 且条件为非主键索引时，很容易出现回表查询（在二级索引找不到的数据，只能通过二级索引叶子节点中的主键去聚集索引中找）

# 比如表user有字段a,b,c,d  建立的联合索引a,b,c
select * from user where a = 1,b=2 c=3
-- 那么此时只能通过联合索引去查找到abc，剩下的d需要回表到聚集索引中查找

面试题

• 建立username索引：虽然能提高效率，但是会有回表操作，得查两次
• 建立username,password联合索引：联合索引是二级索引的一种，叶子节点存的就是id所以只需要一次查询

前缀索引

当字段类型为字符串(varchar， text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘lO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

• 语法

  create index index_name on table_name(col_name(取前几个))
  

• 查询流程

  1. 先通过前缀去二级索引查找获取聚集索引id

  2. 然后拿着索引id去聚集索引里查找整行数据

  3. 最后再进行一个完整的对比

text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘lO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

• 语法

  create index index_name on table_name(col_name(取前几个))
  

• 查询流程

  1. 先通过前缀去二级索引查找获取聚集索引id

  2. 然后拿着索引id去聚集索引里查找整行数据

  3. 最后再进行一个完整的对比