MySQL索引初探

索引数据结构

二叉树

• 每个结点最多有两颗子树，所以二叉树中不存在度大于2的结点。
• 左子树和右子树是有顺序的，次序不能任意颠倒。
• 即使树中某结点只有一棵子树，也要区分它是左子树还是右子树。

问题：某些情况会变成链表

红黑树

一个平衡二叉树

• 结点是红色或黑色。
• 根结点是黑色。
• 所有叶子都是黑色。叶子是NIL结点
• 每个红色结点的两个子结点都是黑色。（从每个叶子到根的所有路径上不能有两个连续的红色结点）
• 从任一节结点其每个叶子的所有路径都包含相同数目的黑色结点
  

问题：深度太高

Hash表

• 对索引的key进行一次hash计算就可以定位出数据存储的位置
• 很多时候Hash索引要比B+ 树索引更高效
• 仅能满足 “=”，“IN”，不支持范围查询
• hash冲突问题
  
  问题：范围无法查询

B-Tree

多路树

B+Tree

• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能
  

聚集索引和非聚集索引

• ibd: InnoDB Index+Data
• MYI ： MyISAM Index
• MYD: MyISAM Data
• frm：表结构
  

聚集索引-叶节点包含了完整的数据记录

非聚集索引-索引文件和数据文件是分离的

最左前缀原则

执行计划

MySQL官网优化索引文档
MySQL官网explain文档
MySQL官网explain-output文档

执行计划EXPLAIN

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 4
          ref: const
         rows: 5
        Extra: Using where; Using index

执行计划的每个参数：

Table 8.1 EXPLAIN Output Columns

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

• id: 这个执行计划的唯一编号
• select_type: 查询类型

select_type Value	JSON Name	Meaning
SIMPLE	None	Simple SELECT (not using UNION or subqueries)
PRIMARY	None	Outermost SELECT
UNION	None	Second or later SELECT statement in a UNION
DEPENDENT UNION	dependent (true)	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	union_result	Result of a UNION.
SUBQUERY	None	First SELECT in subquery
DEPENDENT SUBQUERY	dependent (true)	First SELECT in subquery, dependent on outer query
DERIVED	None	Derived table
MATERIALIZED	materialized_from_subquery	Materialized subquery
UNCACHEABLE SUBQUERY	cacheable (false)	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	cacheable (false)	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

• table: 涉及到的表
• type: 查询类型
• possible_keys: PRIMARY,k_d
• key: 所走的索引
• key_len: 索引的字节长度和
• ref:
• rows: 扫描多少行
• Extra: 一些关键描述
  比如是否走索引，排序方式，group by方式 ，联表方式等

优化思路

1、表设计优化
2、表结构优化
3、sql优化
4、配置优化

innodb配置参数

例如：

# 查看innodb_buffer_pool_size大小
show VARIABLES like '%innodb_buffer_pool_size%';

#临时的，持久的需要放到配置文件mysql.cnf
SET GLOBAL innodb_buffer_pool_size=1326531840;
SET GLOBAL innodb_buffer_pool_size=134217728;

#测试sql
select e.error_msg from qc_first_page_analysis a left join qc_error_item e on a.mrm_first_page_id = e.mrm_first_page_id group by e.error_msg order by null;

5、分库分表