2018 年 06 月 13 日

MySQL 表与索引设计攻略

欢迎关注富途web开发团队，php , 前端都缺。缺人从众

首先，跟大家说句抱歉。很久没有更新了。

上个月去了趟上海，参加了FDC2018前端千人峰会之后。就一直没有来得及更新。

参会体验分享点击这里

感触挺多的。有机会以后会多参加。

最近想着自己也学习一点好玩的东西吧。

原文链接

第一部分 MySQL概述

MySQL 是什么

1970 年，Edgar Frank "Ted" Codd（关系数据库之父）发表了题为"A Relational Model of Data for Large Shared Data Banks"（大型共享数据库的关系数据模型）的论文，文中首次提出并证明了，可以使用关系模型来描述数据。

关系模型是指使用二维表的形式来表示实体和实体间的联系。MySQL 是基于这个理论而实现的许多关系型数据库之一。

关系模型图

MySQL 的历史与现状

1990 Michael Widenius 写了 MySQL 的第一个版本。
1995 Michael Widenius 成立了 MySQL AB 公司。
2000 Michael Widenius 公布了 MySQL 源码，采用 GPL 许可协议。MySQL 进入开源时代。
2008 Sun 收购了 MySQL AB 公司。MySQL 数据库进入 Sun 时代。
2009 Oracle 收购 Sun 公司。MySQL 数据库进入 Oracle 时代。Oracle 同时维护社区版的 MySQL，以及一个企业版本的 MySQL。
2009 Michael Widenius 从开源的 MySQL 分支上重新拉分支，创立 MariaDB。

MySQL 版本

MySQL 的社区版是开源免费的，企业版是闭源收费的。

下面是主要版本特性的对比图。

腾讯云 CDB 5.6 的版本是：5.6.28-cdb20160902-log。online ddl的时候，需要注意：给某个表增加列的时候还是会造成堵塞。

5.7版本的mysql 在性能和并发连接数上都有很大幅度的提升。

MySQL 与 MariaDB 的兼容性和差异

随着Oracle买下Sun，MySQL也落入了关系型数据库王者之手。而早在2009年，考虑到Oracle的名声以及其入手之后闭源的可能性，MySQL之父的Michael便先行一步，以他女儿Maria的名字开始了MySQL的另外一个衍生版本：MariaDB。

As MariaDB is a full replacement of MySQL, the MySQL manual at http://dev.mysql.com/doc is generally applicable.--来源

我们每月都会将社区版的 MySQL 基本代码编译入 MariaDB，从而保证 MariaDB 与 Oracle 添加的任何补丁和更新的 MySQL 相兼容。

MariaDB版本与Mysql版本相匹配——比如MariaDB 5.1，与MySQL 5.1使用相同的代码。由于更新和修复是针对MySQL源码树的，这样的话MariaDB可以采纳这些补丁,指的是原有代码的补丁，不是各自的新特性（理论上，MariaDB每月都与MySQL源码合并）。--来源

Upgrading from MySQL to MariaDB

兼容性和差异

MySQL 的逻辑架构

Mysql是由SQL接口，解析器，优化器，缓存，存储引擎组成的。

Connectors 调用方
Management Serveices & Utilities 系统管理和控制工具
Connection Pool 连接池
SQL Interface
Parser 解析器
Optimizer 优化器
Cache & Buffer 各种缓存
Engine 存储引擎

线程池是Mysql5.6的一个核心功能，对于服务器应用而言，无论是web应用服务还是DB服务，高并发请求始终是一个绕不开的话题。当有大量请求并发访问时，一定伴随着资源的不断创建和释放，导致资源利用率低，降低了服务质量。线程池是一种通用的技术，通过预先创建一定数量的线程，当有请求达到时，线程池分配一个线程提供服务，请求结束后，该线程又去服务其他请求。通过这种方式，避免了线程和内存对象的频繁创建和释放，降低了服务端的并发度，减少了上下文切换和资源的竞争，提高资源利用效率。所有服务的线程池本质都是位了提高资源利用效率，并且实现方式也大体相同。本文主要说明Mysql线程池的实现原理。

MySQL 的物理文件

日志文件会记录mysql运行期间发生的变化。当mysql遭到意外的损坏时，可以通过日志文件进行数据恢复。

日志记录的信息很多。比如：mysql连接状况、SQL语句的执行情况和错误信息等都会被记录下来。

mysql的日志文件主要包含以下的几种：

错误日志
查询日志
慢查询日志
事务日志
二进制日志

错误日志主要记录mysql服务器相关的数据；

慢查询日志记录一些执行时间较长的查询query；

事务日志是InnoDB存储引擎特有的日志；

二进制日志主要记录修改数据或有可能引起数据改变的mysql语句；

mysql存储数据文件会根据各个存储引擎不同而使用不同的存储文件。

SQL 执行过程

所有的我们想查询或者修改，删除数据，都是通过执行sql语句来完成的。mysql通过分析我们传入的sql语句来进行相关操作。

sql语句传入mysql后，会首选查看缓存中有木有匹配的数据，有的话直接返回数据，结束sql。如果没有，则需要将当前sql语句传入解析器进行语法解析。然后进行预处理检查语法是否符合语义，最后优化器将其转化为执行计划，得到mysql最合适的查询语句，最后交给查询执行引擎。获取我们最后想要的数据。

缓存池、顺序读取与随机读取

缓存池是存储引擎实现的（与查询缓存是两个不同层次的缓存）。在 MySQL InnoDB 中，可以通过innodb_buffer_pool_size参数来定义缓存池的大小。

缓存池通过 LRU 策略进行维护。若数据库中的数据可以完全存放于缓存池中，则可以认为，此时数据库的性能是最佳的了。除了同步或异步的写磁盘操作外，所有其他操作都可以在内存中完成。

下面是 18G 的数据，随着缓存池的变大，TPS 的变化情况。18G 数据，存到内存要比 18G 大一点，因为还有其他的开销。

因为有了缓存池，一些热点的数据，就可以自动躺在缓存池中了。

磁盘与硬盘的随机读写和顺序读写：顺序读取是指顺序地读取磁盘上的页。随机读取是指访问的页不是连续的，需要磁盘的磁头不停地移动。

======================第一部分完=====================

第二部分数据库设计

范式定义

第一范式：属性不可分割。数据表中的每一列（每个字段）必须是不可拆分的最小单元，也就是确保每一列的原子性；
第二范式：要有主键，要求其他字段都依赖于主键。满足1NF后，要求表中的所有列，都必须依赖于主键，而不能有任何一列与主键没有关系，也就是说一个表只描述一件事情；
第三范式：消除传递依赖（消除冗余）。必须先满足第二范式（2NF），要求：表中的每一列只与主键直接相关而不是间接相关，（表中的每一列只能依赖于主键）
巴斯-科德范式（BCNF）: 每个表中只有一个候选键
第四范式: 消除表中的多值依赖。（当一个表中的非主属性互相独立时（3NF），这些非主属性不应该有多值）

注意：通常我们到第三范式就够了，后面的都太严格了，不符合实际使用。按照领域模型方式来建数据库，一般都能很好地满足到第三范式。

先按照范式的规范来设计表。然后根据实际的查询查询需求，使用反范式加速查询。

字段类型选择

基本准则：

更小的，简单地，够用的，通常更好
一般情况下，应该尽量使用可以正确存储数据的最小数据类型。
更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并在处理的时候需药的CPU周期也更少。
当然，后期更换字段类型是很耗时和痛苦的事情。所以，在一开始设计的时候，最好根据业务规模，在“更小原则”与“后期维护”间进行权衡。在够用的情况下，选择最小的。
根据字段的属性，选择简单地数据类型。如：年龄就应该用整型存，不要用字符串存。时间就应该用内置的时间类型来存。IP就应该用int来存。（inet_aton('127.0.0.1') inet_ntoa(4294967295) MySQL都已经内置了转化函数了）
尽量避免 NULL
尽量避免使用 set 和 enum 类型

整型

TINYINT: 8位
SMALLINT: 16位
MEDIUMINT: 24位
INT: 32位
BIGINT: 64位

范围：-2^(n-1) ~ (2^(n-1))-1

使用 UNSIGNED,可以使正数的范围提高正式的一倍+1，如-128~127 -> 0~255

UNSIGNED INT 40亿
UNSIGNED BIGINT 18446744万亿

INT(11)，其中的11并没有什么作用。只是规定了一些MYSQL客户端在显示数据时的显示格式而已。

题外话：上面的5种整数类型，只是规定了 MYSQL 怎么在内存和磁盘中保存数据。而，在整数计算时，MYSQL 一般将其全部转成64位的 BIGINT 进行运算。

实数类型

小数字段推荐使用 decimal 类型，float 和 double 精度不够，特别是涉及金钱的业务，必须使用 decimal。--腾讯云 CDB for MySQL 使用规范指南

我们的实际业务中，更喜欢用整型来存（扩大 1000、10000等）

字符串类型

5.7 后默认使用utf8mb4

CHAR 定长。括号中写多少就固定开好了指定个数的存储，只能存多少个字符，不够则系统会默认补上些东西。
VARCHAR 变长。括号中填的是最大的字符个数。实际存数据的时候，不是开固定长度的空间。而是根据写入的数据来开，但是不能超过最大的字符数。

注意，括号中定义的是字符数不是字节数

CHAR 会去掉最右边的空格（如果有的话）。而 VARCHAR 则会保留。

VARCHAR 和 CHAR 的括号中存的都是最大字符数。

存 'hello' 时，使用 VARCHAR(5) 比使用 VARCHAR(200) 要好。虽然二者占用的空间是一样的，但是还是 VARCHAR(5) 会好些，在后续的某些操作者更有效率。（更少原则）

存储很大的数据:

BLOB系列（二进制）：TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB
TEXT系列（字符串）：TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT

附一个通用的实践：整数字段的查找效率是最好的。所以，如果某些字段要查找、排序、关联等，使用整型的效率最好。

日期和时间类型

DATETIME

使用64位来存储，时间跨度为1001年~9999年。这个类型没有时区的概念，比如我在东八区存了个"2018-01-01 00:00:00"进去，然后在西八区取出来，取到的还是"2018-01-01 00:00:00"。那这就不正确了。

所以比较好的做法是，数据库中使用DATETIME，然后存时间的时候一律用程序生成UTC时间（而不是local时区的时间）存进去，取出来的时候不管想显示服务器时间还是显示用户的时间都可以处理。

TIMESTAMP 使用32位来存储，实际上存的是时间戳（所以范围是1970~2038）

MYSQL提供了FROM_UNIXTIME在输出时格式化显示时间（同时也帮你把时区加上去了）。同时，MySQL也提供了CURRENT_TIMESTAMP来自动维护created_at

具体用 int 还是 TIMESTAMP 还是 DATETIME ，看下面文章，自己考虑下。

https://www.jianshu.com/p/edfdaacc3b7c

https://blog.csdn.net/ppvqq/article/details/47424163

标识列的数据类型选择

标识列就是类似自增ID这种，用于标识某一行数据的一个关键值。因为这些标识列经常会用于关联操作，或者和其他数据进行比较等，所以标识列的数据类型在选择时一定要特别注意，以达到最佳的查询效率。

下面是一些小技巧

一旦选定了类型，与其比较或关联的相关列的数据类型最好和标识列完全一样，包括unsigned这些属性也最好一样。
选用int等整形永远是最好的选择。
一定避免使用enum和set类型。
尽量不要使用字符串类型。
尽量避免 NULL

一些技巧

使用缓存表和汇总表

为了更快地读，只能更慢地写。有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表（特别是为满足检索的需求时）

缓存表：一般用于“冗余数据”，可以从某个表中，花费一定的时间来生成。
汇总表：一般是 group 操作的结果（如：收藏数量表就是一个汇总表）。

汇总表、计数表，可能会遇到写瓶颈。此时，可以使用“槽（slot）”，把每次新增的“+1”随机地分配到某一行中，这样就可以将每次写都锁一行，变成每次写，会从 N 个槽中选一个来写。尽量地避免了写锁等待。

其他的一些技巧

约束性与并发控制

唯一索引

利用数据库的机制，来帮我们实现唯一性。可以通过组合字段的唯一性，来达到唯 N 性等。

乐观锁

更新数据时，更新条件中带上之前读取的记录的版本号（或重要字段的值）。

YII 的乐观锁支持

increase 与 decrease

当前取到count=4，要加 1 后存会数据库。count = count + 1 与 count = 5 的区别

使用槽（slot）来减少锁资源等待

如果要对一行数据进行频繁的修改，可能会出现对这行数据的写锁等待。此时，考虑下能否把改一行，从业务逻辑上变成改多行。把写操作分配到多行，减少单行的锁等待。

存储原始数据而非结果

比如，要获取用户的剩余抽奖次数。此时，数据库中，存已经用了的次数和总共有多少次抽奖次数 要优于只存一个 剩余抽奖次数。

===================第二部分完=======================

第三部分索引设计

这里讨论的是：MySQL 5.6 InnoDB BTREE 索引。只要把 MySQL InnoDB 中 BTREE 的树结构理清了，就能自己推导出许多索引的设计准则。

索引简介

索引其实就是一种数据结构。（哈希表、树等等）不同类型的索引有着不同的数据结构和功能。

例如：语文课本，每篇文章都对应一个起始页码，所有的文章按照页码顺序进行整理排版。此时，“文章按页码顺序进行整理排版”就是索引的数据结构，书本的目录就是索引文件。

索引类型

Mysql 5.6 InnoDB 提供了两个类型的索引（Index_type）

BTREE
FULLTEXT

（Mysql 5.6 InnoDB 不支持手动使用 hash index（InnoDB 内部支持自适应哈希索引），也不支持 Geospatial index（5.7 后支持））

Clustered indexes 指聚集索引

可以使用 BTREE 索引，来实现

主键索引PRIMARY KEY
唯一索引UNIQUE KEY
普通索引KEY

（外键FOREIGN KEY的实现，也有部分依赖于 BTREE索引，建外键的时候，必须要求当前指定的列最少有个普通索引，不然的话，系统会自动帮你建一个。）

可以使用 FULLTEXT 索引，来实现

全文索引FULLTEXT KEY

单列索引与联合索引

单列索引，只使用一列来建索引
联合索引，使用多列来建索引




    

CREATE DATABASE `test_db` DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

use test_db;

CREATE TABLE  `table_b`(
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `name` VARCHAR(100) NOT NULL,
   `f_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY uk_name (`name`),
    KEY title (`title`),
    KEY title_name_fid (`title`,`name`,`f_id`),
    FULLTEXT KEY (`name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



SHOW INDEX FROM table_b;

+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table_b |          0 | PRIMARY        |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          0 | uk_name        |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | title          |            1 | title       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | title_name_fid |            1 | title       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | title_name_fid |            2 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | title_name_fid |            3 | f_id        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| table_b |          1 | name           |            1 | name        | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

索引的作用

加速查询速度
维护数据的约束性（完整性、一致性）

对于加速查询，使用索引不一定是最好的选择。小表就直接全表扫描，中到大表就建索引，超大表就分区分表。其实主要就要索引带来的好处和维护索引的成本之间的权衡。

BTREE 索引

在 MySQL 5.6 InnoDB 中，我们平时建索引，只有 BTREE 这个选择了。全文索引，一般我们的业务场景不会用到。

B+ 树简介

B+树和二叉树、平衡二叉树一样，都是经典的数据结构。B+树由B树和索引顺序访问方法（ISAM，是不是很熟悉？对，这也是MyISAM引擎最初参考的数据结构）演化而来，但是在实际使用过程中几乎已经没有使用B树的情况了。

B+树的定义十分复杂，因此只简要地介绍B+树：B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶节点中，各叶节点指针进行连接。

特点：

树
查找树
平衡
数据都在叶子节点
节点可以包含多个数据
叶子节点间有指针相连
查找、插入、删除近似于 O(lgn)

Mysql InnoDB 的逻辑存储结构

划分逻辑结构的目的是便于管理，就像学校要划分年级、班级、小组一样。

表空间（tablespace）
段（segment）
区（extent）
页（page）
行（row）

页是 B+树中一个一个节点（叶子节点或非叶子节点）。
页有分索引页和数据页。索引页，其中存放的就是非叶子节点的数据，数据页存放的就是叶子节点的数据。每个页中，包含 1 个以上的行，行间通过指针按顺序相连。我们在搜索数据时，先定位到某个页，再在页内寻找想要的行。每页默认 16KB。
一个区包括 64 个页，区在申请磁盘时，是整块整块申请的，所以，一个区中的数据，在物理上是连续的。（64 个页 × 一个页 16kb = 一个区 1M）。一般内存都有能力把一个 B+树索引中的所以非叶子节点全部 load 进内存中进行管理。所以，在内存中维护这个 B+树的非叶子节点时，一般开销都相对较小。但是，维护叶子节点的话，一般都要磁盘 io 了，因此整个叶子数据一般不能全部 load 进内存。
表空间就像是一个文件夹，段就是文件夹中的一个一个文件。新建一个索引时，就会新建两个段数据，一个只存索引（索引段），一个只存数据（数据段）。
表空间就是一个表的数据了。

Mysql InnoDB 的逻辑存储结构有 3 个有意思的地方：

段，是为了区分非叶子数据和叶子数据。便于把整个非叶子数据 load 进内存。
区，是一个连续的磁盘空间。
页，页内也是一个连续的空间。

类比：

中心，划分中心，是为了更好地管理有相同职能技能的员工。
业务线，业务线中包含好多小组，各个小组坐在附近，能便于交流。
小组，小组中包含员工，同一小组的员工坐在一起，能便于交流。

从逻辑结构，我们可以知道：

连续性
叶子节点与非叶子节点的分开管理

MySQL InnoDB BTREE 总览

通过上面图片，我们能推导出下面这些结论

全值匹配
匹配最左前缀
匹配列前缀
匹配范围值
精确匹配某一列并范围匹配另外一列
第一个范围查询字段后面的段索引字都只能用于过滤。

索引中的字段可以有两个作用：

确定索引片起始和终止位置
过滤，比较筛选

聚集索引与辅助索引

聚集索引

在 MySQL InnoDB 中，每行记录，必有一个主键。官方推荐使用业务无关的整形无符号自增非空类型作为主键。如果没有自定义主键，系统会根据以下规则来选取主键

看有没有单个的非空的唯一索引，有的话，就用这个作为主键。有多个的话，就选择建表语句中，符合条件的第一个字段。
若无的话，就自己维护一个6个字节的空间作为主键。详情请见

扩展：为啥要自己定义自增非空int

6字节。自己可能用不到那么大
顺序。减少页分裂。

辅助索引

其他的索引就是辅助索引（唯一索引、普通索引等）。辅助索引中，叶子节点存的是记录的主键值。通过主键值再去聚集索引查实际的值。

建议在业务要求的辅助索引字段后面补上主键字段？

这里加和不加，从实验上看不出区别。应该是优化器帮我们处理好了（优化器是很强大的，优化器的代码更新地很快，特性很多，要相信优化器）。

为什么辅助索引的叶子节点不直接存表行记录实际的物理指针（页号等），而是要去聚集索引那边再查一次？

如果这样的话，如果聚集索引中，页的分裂，将会导致数据的物理结构发生变化。如果辅助索引存的还是物理信息，那么就还要去更新辅助索引中的数据，那就会产生许多的额外操作。而目前这种结构，我们要查两次，但是这个负担不是很严重，因为非叶子节点都在内存中，查起来很快的。

三星索引设计规范

如何为一条 SQL 语句设计索引。

覆盖索引

无需访问聚集索引，通过辅助索引就能完成需求。

要select的字段都在索引中。
select count(*) 因为我们只要一个数字就好了，所以也是只需要访问辅助索引就好了，所以也算是覆盖索引。

因为通常辅助索引都比聚集索引要小（辅助索引的叶子节点页中，一个页能包含更多的列记录）。

如何优化count(*)

特别是带条件的计数时，肯定要扫描的，没有系统的统计信息可以直接拿。所以，要不就使用覆盖索引，如果还是慢的话，就用之前设计表时说的汇总表，设置多个槽防止锁表。

我们说的全表扫描，就是指遍历聚集索引的叶子节点

三星索引的定义:

第一颗星：尽量缩短将要被扫描的叶子节点范围（起始位和终止位之间的间隔尽量小）。
第二颗星：避免把叶子节点的数据 load 进内存中的排序操作。使用 B+树索引帮我们提前排好。
第三颗星：避免减少回聚集索引查询，通过辅助索引就解决战斗。

设计步骤

通常，第三颗星我们都能达到，即，使用覆盖索引来避免回聚集索引查找的过程，可以减少很多回表的 I/O。

如果，查询的 where 条件中都是等值查询（或没有排序的话），那么我们能完成满足 3 颗星的要求。

如果，查询的 where 条件中存在范围查询，且有排序的需要，那么我们就只要在（第一颗星 + 第三颗星）和（第二颗星 + 第三颗星）这两者间选择了。

候选A

（第一颗星 + 第三颗星）追求窄，扫描最少的索引片

取出对于等值条件的列，将这些列作为索引的前导列，任意顺序皆可。（选择性高的靠前会好点，便于其他查询复用这个是索引）
将选择性最好的范围条件作为索引的下一列。
以正确的顺序添加 order by列。忽略上面两步已经添加过的列。
以任意顺序将select语句中的其他列添加到索引中，已不易变列开始。

候选B

（第二颗星 + 第三颗星）追求不用排序

取出对于等值条件的列，将这些列作为索引的前导列，任意顺序皆可。（选择性高的靠前会好点，便于其他查询复用这个是索引）
以正确的顺序添加 order by列。忽略上面两步已经添加过的列。
以任意顺序将 select 语句中的其他列添加到索引中，以不易变列开始。

有排序且有范围查询时，才考虑选择候选A 或候选B，其他情况，都能满足三星索引。

排序指：order by

选择候选A 还是候选B，就是判断：load 进内存排序的成本大，还是一个一个从头遍历的筛选的成本大。这种没有定性的答案，需要根据数据的特性以及要去取怎样的数据决定。

select A,B from user where A > a order by B;

假设总共有 n 条记录，满足条件 A > a 的有 m 条，每条数据一次i/o

候选A ：n->m，然后 i/o m 条数据进入内存进行排序，耗时O(mlgm)，即，耗时为：m次i/o+ mlgm次比较
候选B ：i/o n 条数据，进入内存中比较，比较次数为1~n，即，耗时为：1~n次[比较+i/o时间]。（因为如果只需取 1 条数据，可以提前退出，所以 1~n）

假如 m 很大，大到接近 n 的话，那么候选B 好。假如 m 很小，小到接近 1 的话，那么候选A 好。

==================第三部分完====================

第四部分实践测试

硬件配置

腾讯云 CDB
5.6.28-cdb2016-log 20180122
高IO版，内存1000MB，硬盘25GB，1000次/秒

MySQL 主要配置信息

关闭查询缓存




    
show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.01 sec)

每页大小 16KB

show variables like '%innodb_page%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

InnoDB 缓存区大小 893M

show variables like '%innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 936378368      |
+-------------------------------------+----------------+

测试数据库初始状态

数量：500W
主键：int 型自增主键

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sex` tinyint(3) unsigned NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  `email` varchar(255) NOT NULL,
  `address` varchar(350) NOT NULL,
  `company` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


select count(*) from user;
+----------+
| count(*) |
+----------+
|  5037343 |
+----------+
1 row in set (1.81 sec)


select * from user limit 1 \G
*************************** 1. row ***************************
     id: 1
   name: Prof. Osborne Waelchi I
    sex: 0
    age: 60
  email: dach.angela@yahoo.com
address: 35712 Quigley Mountains North Alysonville, CO 53682-2718
company: McGlynn Ltd
   city: Port Maziebury
1 row in set (0.01 sec)

实验案例

索引相关

运算后的列，不能使用索引

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> explain select * from user where id+1=2;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.04 sec)

MySQL [test_db_for_index]> select * from user where id+1=2;
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (3.04 sec)

MySQL [test_db_for_index]> select * from user where id=1;
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (0.01 sec)

列字段的前缀性与 IN 绕过技巧


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex_name |            1 | sex         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex_name |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.02 sec)

MySQL [test_db_for_index]> desc select * from user where name='Prof. Osborne Waelchi I';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I'


    
;
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (3.11 sec)

MySQL [test_db_for_index]> explain select * from user where name='Prof. Osborne Waelchi I' and sex in (0,1);
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | sex_name      | sex_name | 768     | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I' and sex in (0,1);
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (0.01 sec)

多列索引的部分使用

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)


MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I' and age=60;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.03 sec)

MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I' and age=60;;
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (3.03 sec)

ERROR: No query specified

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I';
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (3.03 sec)


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY        |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            2 | age         | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.02 sec)


MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I';
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys  | key            | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | name_age_email | name_age_email | 767     | const |    1 | Using index condition |
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I'


    
;
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I' and age=60;;
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
| id | name                    | sex | age | email                 | address                                                   | company     | city           |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury |
+----+-------------------------+-----+-----+-----------------------+-----------------------------------------------------------+-------------+----------------+
1 row in set (0.01 sec)

ERROR: No query specified

MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I' and age=60;
+----+-------------+-------+------+----------------+----------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys  | key            | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+----------------+----------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | name_age_email | name_age_email | 768     | const,const |    1 | Using index condition |
+----+-------------+-------+------+----------------+----------------+---------+-------------+------+-----------------------+
1 row in set (0.04 sec)

单索引的部分使用

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.02 sec)


MySQL [test_db_for_index]> desc select *  from user where name like 'Prof. Osborne W%';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.02 sec)


MySQL [test_db_for_index]> select *  from user where name like 'Prof. Osborne W%';
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
| id      | name                     | sex | age | email                    | address                                                   | company        | city             |
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
|       1 | Prof. Osborne Waelchi I  |   0 |  60 | dach.angela@yahoo.com    | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd    | Port Maziebury   |
|  798465 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 1167101 | Prof. Osborne Weissnat V |   0 |  74 | monserrat36@shanahan.com | 77818 Rohan Throughway
Koelpinmouth, VA 66568-0775        | Stark-Anderson | Oberbrunnershire |
| 1660173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 2160173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 2660173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 3160173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 3528809 | Prof. Osborne Weissnat V |   0 |  74 | monserrat36@shanahan.com | 77818 Rohan Throughway
Koelpinmouth, VA 66568-0775        | Stark-Anderson | Oberbrunnershire |
| 4021968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 4521968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 5021968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
11 rows in set (3.30 sec)



MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name     |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.02 sec)

MySQL [test_db_for_index]> desc select *  from user where name like 'Prof. Osborne W%';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | name          | name | 767     | NULL |   11 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> select *  from user where name like 'Prof. Osborne W%'


    
;
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
| id      | name                     | sex | age | email                    | address                                                   | company        | city             |
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
|       1 | Prof. Osborne Waelchi I  |   0 |  60 | dach.angela@yahoo.com    | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd    | Port Maziebury   |
|  798465 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 1660173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 2160173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 2660173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 3160173 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 4021968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 4521968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 5021968 | Prof. Osborne Weimann I  |   0 |  79 | santino92@spinka.com     | 64416 Lia Mills Apt. 784
Kleinburgh, MI 09030-1298        | Green PLC      | Hesselhaven      |
| 1167101 | Prof. Osborne Weissnat V |   0 |  74 | monserrat36@shanahan.com | 77818 Rohan Throughway
Koelpinmouth, VA 66568-0775        | Stark-Anderson | Oberbrunnershire |
| 3528809 | Prof. Osborne Weissnat V |   0 |  74 | monserrat36@shanahan.com | 77818 Rohan Throughway
Koelpinmouth, VA 66568-0775        | Stark-Anderson | Oberbrunnershire |
+---------+--------------------------+-----+-----+--------------------------+-----------------------------------------------------------+----------------+------------------+
11 rows in set (0.04 sec)

同字段的等值 or，新版优化器会优化成 in，可以使用索引

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name     |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)


MySQL [test_db_for_index]> desc select * from user where name='Prof. Osborne Waelchi I' or name='Zaria Quigley';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | name          | name | 767     | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> desc select * from user where name in('Prof. Osborne Waelchi I','Zaria Quigley');
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | name          | name | 767     | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.01 sec)


MySQL [test_db_for_index]> select * from user where name in('Prof. Osborne Waelchi I','Zaria Quigley');
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
| id | name                    | sex | age | email                  | address                                                   | company          | city           |
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com  | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd      | Port Maziebury |
|  4 | Zaria Quigley           |   0 |  41 | ryan.anissa@cronin.com | 799 Barney Cove
Princessland, VA 34382                    | Farrell-Hartmann | DuBuqueport    |
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
2 rows in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I' or name='Zaria Quigley';
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
| id | name                    | sex | age | email                  | address                                                   | company          | city           |
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
|  1 | Prof. Osborne Waelchi I |   0 |  60 | dach.angela@yahoo.com  | 35712 Quigley Mountains
North Alysonville, CO 53682-2718 | McGlynn Ltd      | Port Maziebury |
|  4 | Zaria Quigley           |   0 |  41 | ryan.anissa@cronin.com | 799 Barney Cove
Princessland, VA 34382                    | Farrell-Hartmann | DuBuqueport    |
+----+-------------------------+-----+-----+------------------------+-----------------------------------------------------------+------------------+----------------+
2 rows in set (0.01 sec)

不同字段的or的优化，使用两个单列索引组合，使用 union 也是同样效果，并不会优化。


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name     |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | email    |            1 | email       | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where name='1' or email='d';
+----+-------------+-------+-------------+---------------+------------+---------+------+------+--------------------------------------+
| id | select_type | table | type        | possible_keys | key        | key_len | ref  | rows | Extra                                |
+----+-------------+-------+-------------+---------------+------------+---------+------+------+--------------------------------------+
|  1 | SIMPLE      | user  | index_merge | name,email    | name,email | 767,767 | NULL |    2 | Using union(name,email); Using where |
+----+-------------+-------+-------------+---------------+------------+---------+------+------+--------------------------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='1'


    
 or email='d';
Empty set (0.02 sec)


MySQL [test_db_for_index]> select * from user where name='1' union select * from user where email='d';
Empty set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where name='1' union select * from user where email='d';
+----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------------+
| id | select_type  | table      | type | possible_keys | key   | key_len | ref   | rows | Extra                 |
+----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------------+
|  1 | PRIMARY      | user       | ref  | name          | name  | 767     | const |    1 | Using index condition |
|  2 | UNION        | user       | ref  | email         | email | 767     | const |    1 | Using index condition |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL  | NULL    | NULL  | NULL | Using temporary       |
+----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------------+
3 rows in set (0.01 sec)

尽量减少范围条件。（>、<、!=、not in、between、not between），能转成已知的，都转已知的

## sex字段只有 0 1 两个取值
MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex_name |            1 | sex         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex_name |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where sex!=1 and name='payton';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | sex_name      | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where sex!=1 and name='payton';
Empty set (3.18 sec)

MySQL [test_db_for_index]> desc select * from user where sex=0 and name='payton';
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | sex_name      | sex_name | 768     | const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> select * from user where sex=0 and name='payton';
Empty set (0.02 sec)

联合索引中，范围搜索字段后面的字段不能再决定索引片宽度，只能用于筛选。使用 IN 优化

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name |            1 | age         | A         |         136 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where age >= 10 and age <= 15 and name='payton';
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows   | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+
|  1 | SIMPLE      | user  | range | age_name      | age_name | 768     | NULL | 626654 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where age >= 10 and age <= 15 and name='payton';
Empty set (0.09 sec)

MySQL [test_db_for_index]> desc select * from user where age in (10,11,12,13,14,15) and name='payton';
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | age_name      | age_name | 768     | NULL |    6 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where age in (10,11,12,13,14,15) and name='payton';
Empty set (0.02 sec)

覆盖索引更优

因为这里的 InnoDB 的缓存池太大了，有接近 900M 的内存。完全有能力把辅助索引的叶子和非叶子节点全部 load 进内存。同理，聚集索引也是。因为这里没有 io 的差距，所以差别就是一页的记录行数据多少了。所以这里的差别不是很大。但是，如果不能完全把聚集索引非叶子节点全部进内存的话，这里的差距会更大。

MySQL [test_db_for_index]> desc select count(*) from user;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | PRIMARY | 4       | NULL | 4870574 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> desc select count(*) from user;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type


    
  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | PRIMARY | 4       | NULL | 4870574 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select count(*) from user;
+----------+
| count(*) |
+----------+
|  5037343 |
+----------+
1 row in set (1.69 sec)


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex      |            1 | sex         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

MySQL [test_db_for_index]> desc select count(*) from user;
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | sex  | 1       | NULL | 4870574 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select count(*) from user;
+----------+
| count(*) |
+----------+
|  5037343 |
+----------+
1 row in set (0.67 sec)

InnoDB 缓存池的大小，对查询的影响

这个实验的环境和其他的不一样。

缓存池是存储引擎实现的。在 MySQL InnoDB 中，可以通过innodb_buffer_pool_size参数来定义缓存池的大小。

缓存池通过LRU策略进行维护。若数据库中的数据可以完全存放于缓存池中，则可以认为，此时数据库的性能是最佳的了。除了同步或异步的写磁盘操作外，所有其他操作都可以在内存中完成。

下面是18G的数据，随着缓存池的变大，TPS的变化情况。18G数据，存到内存要比18G大一点，因为还有其他的开销。

因为有了缓存池，一些热点的数据，就可以自动躺在缓存池中了，这样，就快了。

磁盘与硬盘的随机读写和顺序读写

顺序读取是指顺序地读取磁盘上的页。随机读取是指访问的页不是连续的，需要磁盘的磁头不停地移动。

注意，这里指的顺序，指的是大块内部是顺序地，大块与大块间可以是不连续的。因为很难保证能申请到一块几十G的连续空间。

在 MySQL InnoDB 中，页是通过区来进行管理的，每次申请存储时，会申请一块连续的区，其中包括64个页。所以，可以保证这64个页是连续的，但是区与区间就不保证连续了。

固态硬盘虽然物理结构和磁盘不一样，但是也是准守上面的原则的。顺序读还是会比随机读快。

缓存的作用，系统把random_digit索引的所有叶子节点都缓存到内存中了

MariaDB [big_tables]> show index from custom;
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| custom |          0 | PRIMARY      |            1 | id           | A         |     1240315 |     NULL | NULL   |      | BTREE      |         |               |
| custom |          1 | email        |            1 | email        | A         |     1240315 |      255 | NULL   | YES  | BTREE      |         |               |
| custom |          1 | name         |            1 | name         | A         |     1240315 |      255 | NULL   | YES  | BTREE      |         |               |
| custom |          1 | random_digit |            1 | random_digit | A         |          20 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (1.39 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (0.23 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (0.23 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (0.25 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (0.22 sec)

MariaDB [big_tables]> desc select count(*) from custom;
+------+-------------+--------+-------+---------------+--------------+---------+------+---------+-------------+
| id   | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows    | Extra       |
+------+-------------+--------+-------+---------------+--------------+---------+------+---------+-------------+
|    1 | SIMPLE      | custom | index | NULL          | random_digit | 7       | NULL | 1240315 | Using index |
+------+-------------+--------+-------+---------------+--------------+---------+------+---------+-------------+
1 row in set (0.00 sec)

优化器是很厉害的


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY        |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            1 | age         | A         |       29698 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            2 | age         | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>1 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    2 | Using where


    
; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> desc select age,name,email from user where age>50 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>70 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+


MySQL [test_db_for_index]> desc select age,name,email from user where age>80 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | user  | range | age_name_email | age_name_email | 1       | NULL |    1 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.02 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>580 order by name limit 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.02 sec)

Cardinality 与字段选择性


MySQL [test_db_for_index]> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | sex      |            1 | sex         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)


MySQL [test_db_for_index]> select count(distinct sex)/count(*) from user;
+------------------------------+
| count(distinct sex)/count(*) |
+------------------------------+
|                       0.0000 |  # 其实不是0，不够很接近
+------------------------------+
1 row in set (1.88 sec)


MySQL [test_db_for_index]> select count(distinct name)/count(*) from user;
+-------------------------------+
| count(distinct name)/count(*) |
+-------------------------------+
|                        0.1592 |
+-------------------------------+
1 row in set (6.13 sec)

MySQL [test_db_for_index]> select count(distinct left(email,5))/count(*) from user;
+----------------------------------------+
| count(distinct left(email,5))/count(*) |
+----------------------------------------+
|                                 0.0049 |
+----------------------------------------+
1 row in set (4.12 sec)


MySQL [test_db_for_index]> select count(distinct left(email,15))/count(*) from user;
+-----------------------------------------+
| count(distinct left(email,15))/count(*) |
+-----------------------------------------+
|                                  0.1545 | # 这个最省空间。不过要注意，截断后，就不能使用覆盖索引了，必须要回聚集索引才能拿到当前列完整的内容
+-----------------------------------------+
1 row in set (5.74 sec)


MySQL [test_db_for_index]> select count(distinct email)/count(*) from user;
+--------------------------------+
| count(distinct email)/count(*) |
+--------------------------------+
|                         0.1586 |
+--------------------------------+
1 row in set (5.66 sec)

候选A 还是候选B

MySQL [test_db_for_index]> show index from user;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY        |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            1 | age         | A         |       29698 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            2 | age         | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)

MySQL [test_db_for_index]> select age,name,email from user where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 18 order by name limit


    
 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (3.11 sec)

MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (0.00 sec)


MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 18 order by name limit 100000,10;
+-----+--------------------+-------------------------------+
| age | name               | email                         |
+-----+--------------------+-------------------------------+
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  47 | Alexandrea Denesik | loy.larkin@durgan.com         |
|  47 | Alexandrea Denesik | loy.larkin@durgan.com         |
|  28 | Alexandrea Dibbert | rae61@gerhold.info            |
|  28 | Alexandrea Dibbert | rae61@gerhold.info            |
+-----+--------------------+-------------------------------+
10 rows in set (0.06 sec)


MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 18 order by name limit 100000,10;
+-----+--------------------+-------------------------------+
| age | name               | email                         |
+-----+--------------------+-------------------------------+
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  20 | Alexandrea Deckow  | lueilwitz.barry@hermiston.com |
|  47 | Alexandrea Denesik | loy.larkin@durgan.com         |
|  47 | Alexandrea Denesik | loy.larkin@durgan.com         |
|  28 | Alexandrea Dibbert | rae61@gerhold.info            |
|  28 | Alexandrea Dibbert | rae61@gerhold.info            |
+-----+--------------------+-------------------------------+
10 rows in set (18.65 sec)


--------------------------


MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 89 order by name limit 1;
Empty set (1.61 sec)

MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 89 order by name limit 1;
Empty set (0.01 sec)


MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (0.00 sec)

MySQL [test_db_for_index]> select age,name,email from user force index(age_name_email) where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | grice@yahoo.com |
+-----+--------------------+-----------------+
1 row in set (3.11 sec)

## 选择候选A 还是 候选B，我们自己想的过程，其次优化器已经帮我们都想好了。（如果同时存在候选A 和 候选B 的话）



MySQL [test_db_for_index]> show index from user;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY        |            1 | id          | A         |     4870574 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            1 | age         | A         |       29698 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            2 | name        | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | age_name_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            1 | name        | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            2 | age         | A         |     1623524 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | name_age_email |            3 | email       | A         |     2435287 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>1 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> desc select age,name,email from user where age>50 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>70 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | age_name_email | name_age_email | 1535    | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+


MySQL [test_db_for_index]> desc select age,name,email from user where age>80 order by name limit 1;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type


    
  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | user  | range | age_name_email | age_name_email | 1       | NULL |    1 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.02 sec)

参考资料

书

博客

测试数据集

今天看啥 - 高品质阅读平台
本文地址：http://www.jintiankansha.me/t/6ICMAHQk96

MySQL 表与索引设计攻略

MySQL 表与索引设计攻略

第一部分 MySQL概述

MySQL 是什么

MySQL 的历史与现状

MySQL 版本

MySQL 与 MariaDB 的兼容性和差异

MySQL 的逻辑架构

MySQL 的物理文件

SQL 执行过程

缓存池、顺序读取与随机读取

第二部分 数据库设计

范式定义

字段类型选择

整型

实数类型

字符串类型

日期和时间类型

标识列的数据类型选择

一些技巧

使用缓存表和汇总表

约束性与并发控制

唯一索引

乐观锁

increase 与 decrease

使用槽（slot）来减少锁资源等待

存储原始数据而非结果

第三部分 索引设计

索引简介

索引类型

索引的作用

BTREE 索引

B+ 树简介

Mysql InnoDB 的逻辑存储结构

MySQL InnoDB BTREE 总览

聚集索引与辅助索引

聚集索引

辅助索引

三星索引设计规范

覆盖索引

设计步骤

第四部分 实践测试

硬件配置

MySQL 主要配置信息

测试数据库初始状态

实验案例

索引相关

运算后的列，不能使用索引

列字段的前缀性与 IN 绕过技巧

多列索引的部分使用

单索引的部分使用

同字段的等值 or，新版优化器会优化成 in，可以使用索引

不同字段的or的优化，使用两个单列索引组合，使用 union 也是同样效果，并不会优化。

尽量减少范围条件。（>、<、!=、not in、between、not between），能转成已知的，都转已知的

联合索引中，范围搜索字段后面的字段不能再决定索引片宽度，只能用于筛选。使用 IN 优化

覆盖索引更优

InnoDB 缓存池的大小，对查询的影响

优化器是很厉害的

Cardinality 与字段选择性

候选A 还是 候选B

参考资料

书

博客

测试数据集

第二部分数据库设计

第三部分索引设计

第四部分实践测试

候选A 还是候选B