一、索引

1.1、概述：

索引是一种数据结构(有序)，帮助MySQL高效获取数据

1.2、优缺点：

优点	缺点
提高数据的查找效率	索引也要占用空间
通过索引列对数据进行排序，降低数据的排序成本，降低CPU的消耗	索引会降低插入、删除和修改的性能

1.3、索引结构：

索引是在存储引擎层实现的，不同的存储引擎支持的索引类型也不一样。主要包含以下几种：

索引结构	描述
B+树索引	最常见的索引类型，大部分引擎都支持B+树索引
hash索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-Tree(空间索引)	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，可以支持存储经纬度信息，使用较少
Full-text(全文索引)	是一种通过建立倒排索引，快速匹配文档的方式，类似于ElasticSearch,主要用于搜索引擎

索引	InnoDB	MyISAM	Memory
B+树索引	支持	支持	支持
hash索引	不支持	不支持	支持
R-Tree索引	不支持	支持	不支持
Full-text索引	支持	支持	不支持

B+树和B树的区别：

• 1. B+树中，只有叶子节点存放数据信息，非叶子节点只存放键值信息
• 2. B+树中，所有叶子节点之间，形成了一个单链表

MySQL中,B+树在叶子节点这，还存储了指向下一节点的指针，形成一个双向链表


hash索引：
- 1. hash索引只能用于对等比较(=,in)，不支持范围查询(>, <, between)
- 2. 无法利用索引完成排序
- 3. 检索效率非常高，通常一次检索就可以找到数据，通常高于B+树索引

1.4、索引分类:

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	针对表中某个字段创建的唯一索引，可以重复多个	可以有多个	UNIQUE
常规索引	最基本的索引，没有任何限制	可以有多个
全文索引	针对文本的内容创建的索引	可以有多个	FULLTEXT
根据索引的存储形式，可以分为：
分类	含义	特点
–	–	–
聚集索引	将数据存储与索引放在一块，索引结构的叶子节点保存了行数量	必须有，而且只有一个
二级索引	将数据与索引分开，索引结构的叶子节点关联的是对应的主键	可以有多个

聚焦索引选取原则：

• 1. 如果存在主键，则主键索引就是聚集索引
• 2. 如果不存在主键，则第一个唯一索引就是聚集索引
• 3. 如果1、2都不存在，则InnoDB内部会生成一个隐藏的主键(rowid)作为聚集索引
     
     

1.5、索引的语法

• 1. 创建索引

  CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名[长度],...);    -- 创建索引,是否时聚焦索引，就看括号里面是一个值还是多个值
  -- 比如：
  CREATE UNIQUE INDEX index_name ON tb_sanguo(name);
  

• 2. 查看索引

  SHOW INDEX FROM 表名;    -- 查看表的索引信息
  

• 3. 删除索引

  DROP INDEX 索引名 ON 表名;    -- 删除表的索引
  

  

1.6、SQL性能分析：

• 1. 查看SQL执行频次

  SHOW [session | global] STATUS LIKE 'Com_select';    -- 查看SQL执行频次，Com_xxx表示每种类型的命令的次数,session表示当前会话，global表示全局
  SHOW STATUS LIKE 'Com_select';    -- 查看SQL执行频次，Com_xxx表示每种类型的命令的次数
  

• 2. 慢查询日志:
     慢查询日志记录了所有执行时间超过long_query_time值的SQL，默认的阈值是10秒。
     
     慢查询日志默认是关闭的，可以通过配置文件/etc/my.cnf开启，或者在MySQL中执行以下命令：

  SHOW VARIABLES LIKE 'slow_query_log';    -- 查看慢查询日志是否开启
  SET [GLOBAL|SESSION] slow_query_log = 1;    -- 设置慢查询日志开启
  

  配置文件中，修改参数

  show_query_log = 1;    -- 开启慢查询日志
  long_query_time = 2;    -- 设置慢查询阈值为2秒
  

  查看慢查询日志,路径在/var/lib/mysql/下，文件名通常是localhost-slow.log
  
  

• 3. profile分析SQL执行详情
     之前是通过慢查询日志来查看哪些SQL语句超过预设阈值，但对于未超过阈值的某些SQL语句，比如某条查询语句执行1.5S,但需要对其进行优化，慢启动日志就看不了
     
     profile能够在做SQL优化时，帮助了解时间都耗哪里去了，通过have_profiling参数，能够看到当前MySQL是否支持profile功能
     

  SELECT @@have_profiling;    -- 查看是否支持profile
  SET [GLOBAL|SESSION] profiling = 1;    -- 设置开启profile, 默认是关闭的
  
  SHOW PROFILES;    -- 查看所有SQL的执行时间
  SHOW PROFILE FOR QUERY 查询编号;    -- 查看指定query_id的SQL的执行详情
  SHOW PROFILE FOR QUERY 查询编号, TYPE;    -- 查看指定query_id的SQL的详细执行信息，TYPE可以是ALL、BLOCK IO、CONTEXT SWITCHES、CPU、IPC、MEMORY、PAGE FAULTS、SOURCE、SWAPS
  

• 4. explain分析SQL执行计划
     前面都是粗略的估算，通过EXPLAN或者DESC命令获取MySQL如何执行SQL语句，包括是否走索引、扫描行数、时间等
     

  -- 在任意的SQL语句前加EXPLAIN或者DESC命令，就可以获取MySQL如何执行该语句的信息
  EXPLAIN SELECT * FROM 表名 WHERE 条件;    -- 分析SQL执行计划
  

  

  EXPLAIN个字段解释：
  

  字段	含义
  id	查询的序列号，表示查询中执行顺序的标识，ID越大越先执行,id相同，执行顺序从上至下
  select_type	查询的类型，比如SIMPLE表示简单查询，PRIMARY表示主查询，SUBQUERY表示子查询等
  type	表示连接类型，性能由好到差依次是：NULL、const、eq_ref、ref、range、index、all
  possible_keys	显示可能应用在这张表上的索引，一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用
  key	实际使用的索引，如果没有使用索引则为NULL。如果想强制MySQL使用或忽视不使用某个索引，可以在查询中使用FORCE INDEX、IGNORE INDEX
  key_len	表示索引中使用的字节数，为索引字段最大可能长度，在不损失精确性的情况下，长度越短越好
  rows	MySQL认为必须要执行查询的行数，是个估算值
  filtered	表示返回结果的行数占需要读取行数的百分比，值越大越好

  当使用主键或者唯一索引查询时，type是const。

1.7、使用规则：

• 最左前缀原则：
  如果索引了多列(联合索引)时，查询条件必须包含索引最左侧的列，才能触发索引匹配.
  
• 范围查询：
  在联合索引中，出现范围查询(>,<)时，范围查询右侧的列表索引失效
  
  反而>=和<=不会失效
  
• 索引列运算
  不要再索引列上进行运算操作，否则索引将失效
  
• 字符串不加引号
  字符串类型字段使用时，不加引号，会导致索引失效
  
• 模糊查询
  如果仅仅时尾部模糊查询，索引不会失效，如果是头部模糊查询，索引会失效
  
• or连接的条件：
  使用or连接的条件，如果一侧有索引，另一侧没有索引，那么涉及的索引都会失效.
  
• 数据分布影响：
  如果MySQL评估使用索引比全表扫描还慢，则不会使用索引；比如id >= 10，全表大部分数据都满足这个条件
  
• SQL提示：
  在SQL语句中加入一些人为的提示来达到优化操作的目的
  

  -- use index:告诉MySQL使用哪个索引
  explain select * from user use index(idx_user_pro) where id = 1;    -- 使用id索引
  
  -- ignore index:告诉MySQL不使用哪个索引
  explain select * from user ignore index(idx_user_pro) where id = 1;    -- 不使用id索引
  
  -- force index:强制使用哪个索引
  explain select * from user force index(idx_user_pro) where id = 1;    -- 强制使用id索引
  

• 覆盖索引：
  查询使用了索引，并且返回的列正是索引是包含的全部列(即覆盖索引)，减少select *。
  
  Tips:
  
  using index condition表示使用了索引，但是需要回表查询数据
  
  using where; using index表示使用了索引，需要的数据在索引列中能够找到，不需要回表查询数据
  
• 前缀索引:
  当字段类型为字符串(varchar, text)时，有时候需要前缀索引，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。
  
  解决方案:将字符串的一部分前缀，建立索引，减少索引大小，提高查询效率
  

  CREATE INDEX idx_xxx on table_name(字段名(n));    -- 前缀索引，n表示前缀长度
  
  -- 以email字段为例，可以通过以下两条语句来判断，值越大越好，1是最好
  SELECT COUNT(DISTINCT(email)) / COUNT(*) FROM tb_user;    -- 计算唯一邮箱占比
  SELECT COUNT(DISTINCT(SUBSTRING(email, 1, 5))) / COUNT(*) FROM tb_user;    -- 计算唯一邮箱前缀占比
  

• 单列索引和联合索引
  • 单列索引：即一个索引只包含单列
  • 联合索引：即一个索引包含多个列，根据前面最左前缀原则，在创建联合索引时，应该考虑列的顺序。

1.8、索引设计原则：

• 1. 对于数据量较大(百万条记录)，并且查询操作比较频繁的表建立索引
• 2. 针对于常作为查询条件(where)，排序(order by)和分组(group by)操作的字段建立索引
• 3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
• 4. 如果时字符串类型的字段，字段的长度较长，可以建立前缀索引。
• 5. 尽量使用联合索引，减少单列索引；查询时，联合索引可以覆盖索引，减少回表查询。
• 6. 要控制索引的数量，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
• 7. 如果索引列不能存储NULL值，那么在创建表时使用NOT NULL约束，便于MySQL优化器优化。

二、小结：

• 索引概述：索引是高效获取数据的数据结构。
• 索引结构：B+Tree和hash
• 索引分类：主键索引、唯一索引、普通索引、全文索引；聚集索引、二级索引
• SQL性能分析：执行频次，慢查询日志、profile、explain

三、问题：

3.1、一张表，有四个字段(id, username, password, status)，由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案？

select id, username, password from tb_user where username = 'zhangsan';    -- 优化这条SQL语句

方案一：在username上建立索引，但是会触发回表查询，性能较差

方案二：在username和password上建立联合索引, 成立二级索引，二级索引下面就是id，不需要回表查询

3.2、 为什么InnoDB默认使用B+树索引结构？

• 1. 相对于二叉树，层级更少，查询效率更高
• 2. 对于B树，无论是叶子节点还是非叶子节点，都会保存数据(即key-value)，这样导致一页中存储的键值对会相对更少，树的高度会增加，因此检索效率不如B+树高
• 3. 相对于hash索引，B+树支持范围匹配及排序操作(双向链表)