Mysql架构设计及索引原理

MySQL架构设计

引言

查询语句：

select * from user_contacts where id = 1125491;

返回结果：

问题：

• 思考：一条SQL查询语句是如何执行的？

MySQL架构设计

Server层

MySQL 架构可以分为 Server层 和 Engine层两部分：

连接器、查询缓存、分析器、优化器、执行器这些概念性的解释我并不想多写，大概了解这么几个概念就行，多说无益。

Engine层

mysql支持哪些存储引擎？我们使用命令行查看：

SHOW ENGINES;

不同的数据处理选择适合的存储引擎是使用MySQL的一大优势

存储引擎知道这两个图的内容就可以了，其他概念性叙述不多写。

MySQL索引原理&优化

索引原理

索引的存储原理可以概括为一句话：以空间换时间

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

数据库在未添加索引进行查询的时候默认是进行全文搜索，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕。

索引分类

主键索引

• 设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）。

# (1) 创建表添加主键索引
CREATE TABLE `table_name` (
    [...] ,
    PRIMARY KEY (`col_name`),
)
# (2) 添加主键索引
ALTER TABLE `table_name` ADD PRIMARY KEY (`col_name`);

普通索引（单列索引）

• 普通索引（单列索引）：单列索引是最基本的索引，它没有任何限制。

#  (1) 直接创建索引
CREATE INDEX index_name ON table_name(`col_name`);
#  (2) 修改表结构的方式添加索引
ALTER TABLE `table_name` ADD INDEX index_name(`col_name`);
#  (3) 创建表的时候同时创建索引
CREATE TABLE `table_name` (
    [...] ,
    PRIMARY KEY (`id`),
    INDEX index_name (`col_name`)
)
#  (4) 删除索引
DROP INDEX index_name ON table_name;   
alter table `表名` drop index 索引名;

复合索引（组合索引）

• 复合索引：复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。

# （1）创建一个复合索引
create index index_name on table_name(`col_name1`,`col_name2`,...);
# （2）修改表结构的方式添加索引
alter table table_name add index index_name(`col_name1`,`col_name2`,...);

唯一索引

唯一索引：唯一索引和普通索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许存在空值（只允许存在一条空值）。

如果在已经有数据的表上添加唯一性索引的话：

• 如果添加索引的列的值存在两个或者两个以上的空值，则不能创建唯一性索引会失败。（一般在创建表的时候，要对自动设置唯一性索引，需要在字段上加上 not null）
• 如果添加索引的列的值存在两个或者两个以上的null值，还是可以创建唯一性索引，只是后面创建的数据不能再插入null值 ，并且严格意义上此列并不是唯一的，因为存在多个null值。
• 对于多个字段创建唯一索引规定列值的组合必须唯一

“空值” 和”NULL”的概念： 
1：空值是不占用空间的 .
2: MySQL中的NULL其实是占用空间的.

长度验证：注意空值的之间是没有空格的。

> select length(''),length(null),length(' ');
+------------+--------------+-------------+
| length('') | length(null) | length(' ') |
+------------+--------------+-------------+
|          0 |         NULL |           1 |
+------------+--------------+-------------+
-- （1）创建唯一索引
# 创建单个索引
CREATE UNIQUE INDEX index_name ON table_name(`col_name`);

# 创建多个索引
CREATE UNIQUE INDEX index_name on table_name(`col_name`,...);

-- （2）修改表结构
# 单个
ALTER TABLE table_name ADD UNIQUE index index_name(`col_name`);
# 多个
ALTER TABLE table_name ADD UNIQUE index index_name(`col_name`,...);

-- （3）创建表的时候直接指定索引
CREATE TABLE `table_name` (
    [...] ,
    PRIMARY KEY (`id`),
    UNIQUE index_name_unique(`col_name`)
)

全文索引

• Full Text类型索引（FULLTEXT 索引在 MySQL 5.6 版本之后支持 InnoDB，而之前的版本只支持 MyISAM 表）。
• 全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，目前只有char、varchar，text 列上可以创建全文索引。

-- （1）创建表的适合添加全文索引
CREATE TABLE `table_name` (
    [...] ,
    PRIMARY KEY (`id`),
    FULLTEXT (`col_name`)
)

-- （2）修改表结构添加全文索引
ALTER TABLE table_name ADD FULLTEXT index_fulltext_content(`col_name`)

-- （3）直接创建索引
CREATE FULLTEXT INDEX index_fulltext_content ON table_name(`col_name`)

注意：

• 默认 MySQL 不支持中文全文检索！
• MySQL 全文搜索只是一个临时方案，对于全文搜索场景，更专业的做法是使用全文搜索引擎，例如 ElasticSearch 或 Solr。

索引的优缺点

优点：

• 大大提高数据查询速度。
• 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
• 通过索引列对数据进行排序，降低数据的排序成本降低了CPU的消耗。
• 被索引的列会自动进行排序，包括【单例索引】和【组合索引】，只是组合索引的排序需要复杂一些。
• 如果按照索引列的顺序进行排序，对order 不用语句来说，效率就会提高很多。

缺点：

• 索引会占据磁盘空间。
• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改查操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。
• 维护索引需要消耗数据库资源。

综合索引的优缺点：

数据库表中不是索引越多越好，而是仅为那些常用的搜索字段建立索引效果最佳!

MySQL的索引实现（针对InnoDB索引）

主键索引（聚簇索引）

每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。

InnoDB创建索引的具体规则如下：

• 在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
• 如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
• 如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

辅助索引

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址也非行记录。

以表user_innodb的age列为例，age索引的索引结果如下图。

• 辅助索引的底层叶子节点是按照（age，id）的顺序排序，先按照age列从小到大排序，age相同时按照id列从小到大排序。
• 使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后根据主键到主键索引中检索获得数据记录。

组合索引

• 以表abc_innodb为例，id列为主键索引，创建一个联合索引idx_abc(a，b，c)。

CREATE TABLE `abc_innodb`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a`  int(11)     DEFAULT NULL,
  `b`  int(11)     DEFAULT NULL,
  `c`  varchar(10) DEFAULT NULL,
  `d`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE = InnoDB;

show index from abc_innodb

-- 是否会匹配索引
-- （1）
explain select * from abc_innodb where  a = 1 and b = 2
explain select * from abc_innodb where  b = 2 and a = 1

--  (2)
explain select * from abc_innodb where  b = 2

--  (3)
explain select * from abc_innodb where a = 1 and b>2 and c=3

组合索引的数据结构：

组合索引的查询过程：

select * from abc_innodb where a = 13 and b = 16 and c = 4;

最左匹配原则

最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。

在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排序，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内有序递增；而c列只能在a和b两列值相等的情况下小范围内有序递增。

就像上面的查询，B+ 树会先比较a列来确定下一步应该检索的方向，往左还是往右。如果a列相同再比较b列，但是如果查询条件中没有a列，B+树就不知道第一步应该从那个节点开始查起。

可以说创建的idx_(a，b，c)索引，相当于创建了(a)、(a，b)、(a，b，c)三个索引。

组合索引的最左前缀匹配原则：

使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)等就会停止匹配。

覆盖索引

覆盖索引并不是一种索引结构，覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到相应的主键值，想要获取最终的数据记录，还需要根据主键通过主键索引再去检索，最终获取到符合条件的数据记录。

在上面的abc_innodb表中的组合索引查询时，如果我们查询的结果只需要a、b、c这三个字段，那我们使用这个idx_index(a，b，c)组合索引查询到叶子节点时就可以直接返回了，而不需要再次回表查询，这种情况就是覆盖索引。

未使用索引覆盖的情况：

索引覆盖的情况

回表和联合索引的应用

回表查询

在InnoDB的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前数据记录，而是当前数据记录的主键索引。如果需要获取当前记录完整的数据，就必须要再次根据主键从主键索引中继续检索查询，这个过程我们称之为回表查询。

由此可见，在数据量比较大的时候，回表必然会消耗很多的时间影响性能，所以我们要尽量避免回表的发生。

如何避免回表

• 使用索引覆盖

举例：

CREATE TABLE `user`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name`  int(11)     DEFAULT NULL,
  `sex`  char(3)     DEFAULT NULL,
  `address`  varchar(10) DEFAULT NULL,
  `hobby`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `i_name` (`name`)
) ENGINE = InnoDB;

如果有一个场景:

select id,name,sex from user where name = 'zhangsan';

这个语句在业务上频繁使用到，而user表中的其他字段使用频率远低于这几个字段，在这个情况下，如果我们在建立name字段的索引时，不是使用单一索引，而是使用联合索引（name，sex），这样的话再执行这个查询语句，根据这个辅助索引（name，sex）查询到的结果就包括了我们所需要的查询结果的所有字段的完整数据，这样就不需要再次回表查询去检索sex字段的数据了。

• 以上就是一个典型的使用覆盖索引的优化策略减少了回表查询的情况。

联合索引的使用

联合索引：

在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间，还可以更容易的使用到索引覆盖。

节省空间：

试想一下，索引的字段越多，是不是更容易满足查询需要返回的数据呢。比如联合索引（a_b_c），是不是等于有了索引：a，a_b，a_b_c三个索引，这样是不是节省了空间，当然节省的空间并不是三倍于（a，a_b，a_b_c）三个索引，因为索引树的数据没变，但是索引data字段的数据确实真实的节省了。

联合索引的创建原则：

在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大，这些都是在索引创建的需要考虑到的优化场景，也可以在常需要作为查询返回的字段上增加到联合索引中。
如果在联合索引上增加一个字段而使用到了覆盖索引，那建议这种情况下使用联合索引。

联合索引的使用:

• 考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引。
• 当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。

下一节：Mysql调优