MySQL索引及优化

一、MySQL索引

1、什么是索引？

• 索引是帮助MySQL高效获取 有序 数据的数据结构，主要是用于提高数据的检索效率，降低数据库的IO成本（不需要全表扫描）。通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
  

2、了解过索引的数据结构吗？B树和B+树的区别？（底层原理）

• MySQL的InnoDB存储引擎采用的是B+树的数据结构来存储索引，因其相对于B-树等数据结构来说，阶数更多，路径更短，并且磁盘读写代价B+树更低；非叶子节点只存储指针，叶子节点存储数据；B+树便于扫库和区间查询，叶子节点是一个双向链表。

• B树是一种多叉路平衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多支。B+树是在B树基础上的一种优化，在B树中，非叶子节点和叶子节点都会存储数据，而B+树的所有数据都存储在叶子节点，B+树查找效率更加稳定。在将进行范围查询时，B+树效率更高，因为B+树的数据都存储在叶子节点上，并且叶子节点是一个双向链表。

• B树‌：B树是一种自平衡的‌多叉树‌，适用于外存储器的高效检索。B树的每个节点可以存储多个关键字，根节点至少有两个子节点，除根节点和叶子节点外，每个节点至少有m个子节点（m是B树的阶），所有叶子节点在同一层‌。

• B+树‌：B+树是B树的优化版本，也是一种自平衡的‌多叉树‌。B+树的非叶子节点不存储数据，只存储关键字和指向子树中最小关键字的指针；所有关键字都存储在叶子节点，并且叶子节点之间通过指针连接成一个有序链表，便于范围查询和遍历‌

• 红黑树‌：红黑树是一种自平衡的‌二叉树‌，适用于内存存储。每个节点不是红色就是黑色，根节点是黑色的，每个叶子节点是黑色的空节点。红黑树通过旋转和重新着色来保持平衡，确保查找、插入和删除操作的时间复杂度为O(log n)‌。

• B+树是高度平衡的，它可以保持所有的路径长度都是相对平衡的，这样可以保证查找、插入和删除操作的性能接近于对数平均时间复杂度。相比之下，B树可能因为节点过大而导致深度增加，从而影响性能。

• B+树的非叶子节点只存储键值，而不存储数据，这样每个节点可以存储更多的键值，需要读取的磁盘页更少，减少了I/O操作。

• B+树的所有叶子节点构成了一个有序链表，这使得范围查询和顺序扫描效率高。B+树的叶子节点存储了指向实际数据的指针，这样简化了存储引擎的设计，也使得MySQL可以更灵活地与不同的存储引擎进行交互。

• 红黑树是自平衡二叉查找树的一种，但是它主要用于内存中，而MySQL的索引通常存储在磁盘上，并且需要多次I/O操作。另外，红黑树的插入和删除操作可能需要左旋和右旋操作来维持平衡，这在磁盘I/O操作较多的情况下会导致性能问题。

3、什么是聚簇索引（聚集索引）？什么是非聚簇索引（二级索引）？什么是回表？

• 聚簇索引（聚集索引）：聚簇索引主要是指数据和索引放到一块，B+树的叶子节点保存了整行数据，聚簇索引有且只有一个。
• 非聚簇索引（二级索引）：非聚簇索引是指将数据和索引分开存储，B+树的叶子节点保存对应的主键，非聚簇索引可以有多个，一般自己定义的索引都是非聚簇索引。
• 回表查询：通过二级索引找到对应的主键值，然后再通过主键值到聚簇索引中查找整行数据，这个过程就是回表。

4、什么是覆盖索引？MySQL超大分页处理？

• 覆盖索引：覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。
  使用id查询，直接走聚簇索引查询，一次索引扫描，直接返回数据，性能高
  如果返回的列中没有创建索引，有可能会触发回表查询，所以尽量避免使用select*

// id为主键，默认是主键索引，name为普通索引
select * from user where id = 1;  // 覆盖索引
select id, name from user where name = 'Arm';  // 覆盖索引
select id, name, sex from user where name = 'Arm';  // 非覆盖索引，需回表查询

• MySQL超大分页处理：优化思路:一般分页查询时，通过创建覆盖索引能够比较好的提高性能，可以通过覆盖索引加子查询形式进行优化。
  超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低。我们可以采用覆盖索引和子查询来解决，先分页查询数据的ID字段，确定了ID之后再用子查询来过滤，只查询这个ID列表中的数据就可以了，因为查询ID的时候走的覆盖索引，所以效率可以提升很多。

select * from user limit 100000，10;  

在进行分页查询时，如果执行如上 limit 100000，10 ；此时需要MySQL排序前100010记录，然后再仅返回后10条记录，查询排序的代价非常大。

select * from user u, (select id from user order by id limit 100000，10)a where u.id = a.id;

子查询select id可以在聚簇索引中查到，无需回表查询，进行关联查询时减少了回表次数。

5、MySQL有哪些索引？

• 普通索引：即不应用任何限制条件的索引，该索引可以在任何数据类型中创建。
• 唯一索引：索引列的值必须唯一，但允许有空值。
• 主键索引：是一种特殊的唯一索引，不允许为空值。
• 联合索引：索引可以覆盖多个数据列，如INDEX(columnA，columnB）索引。
• 全文索引（fulltext）：通过建立倒排索引，可以极大的提升检索效率，解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术。（ALTER TABLE table_name ADD FULLTEXT（column）;创建全文索引）

6、索引设计的原则？

• 针对数据量较大，且查询比较频繁的表建立索引。
• 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的子段建立索引。
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度高，使用索引的效率高。
• 如果是字符串类型的字段，尽量选择长度较短的，可建立前缀索引。
• 尽量使用联合索引，减少单列索引，查询时联合索引很多适合可以覆盖索引，节省存储空间，避免回表，提高查询效率。
• 要控制索引的数量，索引越多，维护代价越大，会影响增删效率。
• 如果索引列不能存储NULL值，在创表时使用NOT NULL约束它，有效

7、什么情况下索引会失效？

• 违反了最左前缀原则：
• 范围查询右边的列，不能使用索引。MySQL会从索引的第一个简直一直向右匹配，直到范围查询时（ >，< ，< >，or，like）停止匹配。
• 在索引列上进行了运算操作，索引失效。
• 字符串不加单引号，造成索引失效。
• 以%开头的like模糊查询，索引失效，如果仅仅是尾部模糊查询匹配，索引不会失效，如果是头部模糊匹配，索引失效。

假设给user表的name、age、address按此顺序创建了联合索引。
如果索引了很多列，需要遵循最左前缀法则，指的是查询从索引的最左前列开始，并且不跳过索引中的列。如下所示SQL语句命中索引情况。

select * from user where name = 'tom';  // 命中一个索引
select * from user where name = 'tom' and age = '24';  // 命中2个索引
select * from user where name = 'tom' and age = '24' and address = '北京';  // 命中3个索引

但如果跳过中间sex，后面的address索引失效

select * from user where name = 'tom' and address = '北京';  // 命中1个索引name, address索引失效。

使用范围查询，导致后续address索引失效

select * from user where name = 'tom' and age > '24' and address = '北京';  // 命中2个索引, address索引失效。

在索引列上进行运算操作，索引失效

select * from user where substring(name, 3, 2) = 'to';  // 索引失效

如果age是字符串类型，未加单引号，MySQL的查询优化器，会自动进行类型转换，造成索引失效

select * from user where name = 'tom' and age > 24;  // 命中1个索引name, age索引失效。可使用>=或<=代替来命中索引

模糊查询，以%开头，索引失效，仅仅尾部使用，索引不失效

select * from user where name like 'tom%';  // name索引不失效
select * from user where name like '%tom';  // name索引失效
select * from user where name like '%tom%';  // name索引失效

二、MySQL优化

1、如何定位慢查询？

• 慢查询的主要原因是：聚合查询、多表查询、表数据量过大查询、分表查询
• MySQL慢日志查询：可修改MySQL的配置文件my.cnf，配置如下信息来开启慢日志查询日志功能。一般只会在调试阶段开启慢查询。

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

• 使用开源工具Arthas、运维工具Prometheus、Skywalking

2、SQL语句执行很慢如何优化？

• 如果一条sql执行很慢的话，可以采用MySQL自带的执行计划EXPLAIN或者DESC命令查看这条sql的执行情况。如：可以通过key和key_len检查是否命中了索引（索引本身存在是否有失效发情况），然后通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描。其次可以通过extra建议判断是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复。
  

3、MySQL优化经验有哪些？

• SQL语句优化：
  1）尽量避免使用select * ，查询不必要的字段，较少回表查询的可能（通过二级索引找到对应的主键值，然后再通过主键值到聚簇索引中查找整行数据，这个过程就是回表。）
  2）在where从句，group by从句， order by从句， on从句中出现的列 尽量使用索引的列，尽量避免不必要的order by
  3）全模糊查询中尽量不要使用子查询，
  4）group by从句， order by从句中尽量避免出现不确定的结果函数，如now(),rand()，否则系统将可能无法正确使用索引。
  5）用exists替代IN、用not exists替代NOT IN，in 和 not in 也要慎用，否则会导致全表扫描（全表扫描是指在查询数据时，需要扫描整个表中的每一行数据来查找符合条件的数据）
  6）应尽量避免在 where 子句中对字段进行 null 值判断、进行表达式操作，否则将导致引擎放弃使用索引而进行全表扫描
  7）尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
  8）索引字段越小越好
  9）join优化：尽量使用inner join，如必须使用left join 、right join 一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放外面，把打表放里面。left join 、right join不会重新调整顺序。
• 主从复制、读写分离：
  如果数据库的使用场景读的操作比较多的时候，为了避免写的操作操作性能影响，可以采用读写分离的架构
• 表结构设计的优化
  1）选择合适的数据类型，如char定长效率高，varchar可变长度，效率稍低
  2）把一个很多列的表进行拆分，拆分成多个表：把不常用的字段单独放到一个表中；把大字段独立存放到一个表中；把经常使用的字段放到一起。

4、MySQL的主从同步原理

• MySQL主从复制的核心就是二进制日志binlog（记录了所有的DDL【数据定义语言】语句和DML【数据操纵语言】语句）
  1）主库在事务提交时，会把数据变更记录在二进制日志文件中。
  2）从库读取主库的二进制文件binlog，写入到从库的中继日志Relay Log中。
  3）从库重做中继日志中的事件，将改变反映它自己的数据。

5、分库分表

• 1）垂直分库:以表为依据，根据业务将不同的表参分到不同的库中。
  特点:按业务对数据分级管理、维护、监控、扩展，在高并发下提高磁盘io和数据量连接数。
• 2）垂直分表:以字段为依据，根据字段属性将不同字段拆分到不同表中。
  拆分规矩:把不常用的字段单独放在一张表里，把text，blob等大字段拆分出来放到附表中。
  特点:冷热数据分离，减少io过度争抢，两表互不影响。
• 03）水平分库:将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题。
  路由规则:根据ID节点取模；按ID也就是范围路由。
  特点:解决了单裤大量数据高并发的瓶颈问题；提高了系统的稳定性和可用性。
• 4）水平分表:将一个表的数据拆分到多个表中（可以在同一个库内），解决单表存储和性能的问题。
  特点，优化单表数据量过大而产生的性能问题，避免io争抢，并减少锁表的几率。

分库之后的问题:
1）分布式事物一致性的问题，跨节点关联查询，跨节点分页、排序函数，主键避重。
2）分库分表中间件:sharding-sphere，mycat