存储引擎系列（四）：不同类型的查询语句如何设置索引（上）—— 数据表初始化

B+ 索引树回顾

上篇教程学院君给大家介绍了不同类型的数据库索引对应的 B+ 树是如何维护的，这其实是对数据库表记录进行更新时底层所做的（插入、修改、删除）事情，我们来简单回顾下 B+ 索引树：

每个索引都对应一棵 B+ 树，这棵 B+ 树最下面一层叶子节点存放的是存储用户记录的数据页，其他层存放的是存储数据页目录项（这里的数据页可能是叶子节点、也可能是非叶子节点）的数据页；
对于 InnoDB 存储引擎而言，主键索引也叫簇拥索引，叶子节点存储的用户记录包含了对应表记录的完整数据集，如果一张表没有指定主键，则系统会自动为其创建一个隐式主键；
对于二级索引（唯一索引、普通索引、联合索引），叶子节点存储的用户记录由索引列和主键值组成（如果对应数据表没有指定主键，则使用系统自动生成的隐式主键），因此想通过二级索引获取完整数据记录，需要经历两次查询：先通过二级索引获取对应记录主键值，再通过主键值到簇拥索引获取完整数据记录（这一步操作叫做回表）；
B+ 树的每一层节点以及节点内的记录都是按照索引值从小到大排列的，这样一来，当我们进行 SQL 查询时，就可以从 B+ 树的根节点开始，先通过二分查找在数据页目录中快速定位到记录所在的数据页，再在存储用户记录的数据页中通过二分查找找到对应的数据记录，由于二分查找效率非常高，所以命中索引的 SQL 查询效率也非常高。

注：上篇教程是在数据表有主键的基础上介绍 B+ 索引树的维护，如果一张数据表没有指定主键，则 MySQL 会自动为其创建隐式主键，这样一来，就依然会有完整的簇拥索引和二级索引，只是这个隐式索引字段是虚拟的，不可能通过显式的 SQL 查询条件命中，但是如果命中了二级索引，回表的时候依然不会出现全表扫描，而是通过隐式主键去簇拥索引中拿到完整数据记录。

SQL 查询语句分为多种类型，包括等值查询、范围查询、模糊匹配、连接查询，以及排序、分组、限定等更复杂的过滤条件，在各种不同的查询场景下，又是如何命中索引对指定 B+ 树进行搜索的呢？这将是我们今天所要探讨的问题。

初始化数据库

通过存储过程

对于一些非常简单的数据表示例数据填充，可以通过 MySQL 自带的存储过程来实现，比如我们创建一个名为 demo 的数据表：

 
 
CREATE TABLE `demo` (
  `a` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  `c` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

为了方便对比测试，我们先不设置任何索引字段，然后我们通过存储过程对这张数据表进行填充：

 
 
delimiter ;;
create procedure insertdata()
begin
  declare i int;  
  set i=1;  
  while(i<=1000000)do
      insert into demo values(i, i, i);    
      set i=i+1;  
  end while;
end;;
delimiter ;
call insertdata();

-w571

这里我们向 demo 表插入了 1000000 条记录，由于没有设置任何索引，所以查询耗时很长：

-w592

如果使用 explain 查看执行计划的话，通过 type 字段为 ALL 表明使用了全表扫描：

-w1258

而如果我们为字段设置索引的话：

 
 
CREATE TABLE `demo` (
  `a` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  `c` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a`),
  KEY `b_c` (`b`, `c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

则查询效率会明显改善：

-w1200

通过执行计划可以看到第二次查询命中了主键索引，使得查询效率提升了 30 倍。

通过编写代码

如果表结构比较复杂，要填充数据表，可以通过编码的方式实现，只不过使用这种方式性能远不及存储过程高效。

这里我们借助一个 PHP 命令行应用微框架 Laravel Zero 演示数据表结构的初始化和测试数据的填充。

数据库和项目初始化

开始之前，我们先来创建一个测试数据库 test_db：

-w833

然后运行如下命令通过 Composer 初始化一个命令行应用 db-test：

 
 
composer create-project --prefer-dist laravel-zero/laravel-zero db-test -vvv

进入 db-test 项目目录，安装数据库依赖：

 
 
php application app:install database

以及支持通过 .env 配置环境变量：

 
 
php application app:install dotenv

在 .env 中完成数据库配置：

 
 
DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=test_db
DB_USERNAME=root
DB_PASSWORD=root

创建演示数据表

创建一个数据库模型类和对应的迁移文件：

-w639

在数据表迁移文件 2020_09_07_094403_create_users_table.php 中，编写创建数据表的 up 方法如下：

 
 
public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name', 50);
        $table->string('id_number', 60);
        $table->boolean('gender');
        $table->string('address', 100);
        $table->date('birthday');
    });
}

然后运行 php application migrate 创建 users 表：

-w674

这样就可以看到数据库 test_db 中已经存在这个数据表了：

-w510

或者你也可以执行如下 SQL 语句去创建：

 
 
CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `id_number` varchar(60) NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `address` varchar(100) NOT NULL,
  `birthday` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

为了对比索引存在与否对查询性能的影响，除了主键 ID 之外，并没有添加其他的索引字段。

通过模型工厂模拟数据插入

接下来，我们通过模型工厂来编写填充示例数据的代码：

-w687

打开刚刚生成的模型工厂文件 UserFactory.php（位于 database/factories 目录下），编写模型工厂代码如下：

 
 
<?php
​
/** @var \Illuminate\Database\Eloquent\Factory $factory */
​
use App\User;
use Faker\Generator as Faker;
​
$factory->define(User::class, function (Faker $faker) {
    return [
        'name' => $faker->name,
        'id_number' => $faker->uuid,
        'gender' => $faker->boolean,
        'address' => $faker->address,
        'birthday' => $faker->date(),
    ];
});

修改 User 模型类代码如下：

 
 
<?php
​
namespace App;
​
use Illuminate\Database\Eloquent\Model;
​
class User extends Model
{
    const FEMALE = 0;
    const MALE = 1;
​
    public $timestamps = false;
}

编写填充用户数据命令

最后我们编写一个命令调用模型工厂填充 users 数据表：

 
 
php application make:command SeedUsersTable

生成的命令类位于 app/Commands 目录下，编写命令类代码如下：

 
 
<?php
​
namespace App\Commands;
​
use App\User;
use Illuminate\Console\Scheduling\Schedule;
use LaravelZero\Framework\Commands\Command;
​
class SeedUsersTable extends Command
{
    /**
     * The signature of the command.
     *
     * @var string
     */
    protected $signature = 'seed:users';
​
    /**
     * The description of the command.
     *
     * @var string
     */
    protected $description = 'Seed Users Table';
​
    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $this->info('Start seeding users table...');
        $startTime = time();
        // 插入 100000 条记录
        $amount = 100000;
        // 通过进度条显式进度
        $this->output->progressStart($amount);
        // 调用模型工厂插入用户记录，每次插入 1000 条
        for ($i = 0; $i < $amount; $i += 1000) {
            factory(User::class, 1000)->create();
            $this->output->progressAdvance(1000);
        }
        $this->output->progressFinish();
        $endTime = time();
        $execTime = $endTime - $startTime;
        $this->info('Finished.(Time spent: ' . $execTime . 's)');
    }
​
    /**
     * Define the command's schedule.
     *
     * @param  \Illuminate\Console\Scheduling\Schedule $schedule
     * @return void
     */
    public function schedule(Schedule $schedule): void
    {
        // $schedule->command(static::class)->everyMinute();
    }
}