在进行索引操作之前，可以进行一个压力测试，将一个100W数据量的数据库备份数据进行备份恢复：

# 进行测试数据恢复操作：
mysql> source ~/t100w.sql

# 导入数据后进行验证
mysql> show tables from zq;
+--------------+
| Tables_in_zq |
+--------------+
| t100w        |
+--------------+
1 row in set (0.00 sec)

# 查看表的总行数为1000000
mysql> select count(*) from zq.t100w;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.01 sec)

# 进行数据库程序服务压测,观察到用时的最大值、最小值、平均值为85.745秒
[root@master ~]# mysqlslap --defaults-file=/data/3306/data/my.cnf --concurrency=100 --iterations=1 --create-schema='zq' --query="select * from zq.t100w where k2='VWlm'" engine=innodb --number-of-queries=2000  -verbose
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 85.745 seconds
    Minimum number of seconds to run all queries: 85.745 seconds
    Maximum number of seconds to run all queries: 85.745 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

# 补充说明：如果涉及mysql用户和密码，可以参考下面格式
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='zq' --query="select * from zq.t100w where k2='VWlm'" engine=innodb --number-of-queries=2000 -uroot -p123456 -h10.0.0.51 -verbose

相关参数说明：

  • concurrency=100 模拟同时100会话连接,这个需要注意数据库max_connections的参数值(默认151)，可以通过select @@max_connections;这个sql语句进行查询；
  • iterations=1 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次
  • create-schema='test' 指定操作的数据库信息；
  • query="select * from test.100w where k2='780P'" 指定压测过程具体执行了什么语句操作
  • number-of-queries=2000 指定一共做了多少次查询，总的测试查询次数(并发客户数×每客户查询次数)

数据库压力测试结果情况：

Day006-数据库服务索引执行计划-图4

进行索引建立优化：

mysql> alter table zq.t100w add index idx_k2(k2);

重新进行压力测试，观察到时间已将为0.883秒

[root@master ~]# mysqlslap --defaults-file=/data/3306/data/my.cnf --concurrency=100 --iterations=1 --create-schema='zq' --query="select * from zq.t100w where k2='VWlm'" engine=innodb --number-of-queries=2000  -verbose
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 0.883 seconds
    Minimum number of seconds to run all queries: 0.883 seconds
    Maximum number of seconds to run all queries: 0.883 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

在进行压测检查确认：

Day006-数据库服务索引执行计划-图5

一、索引基本操作说明

环境准备：

-- 将world.sql数据库文件上传到数据库服务器中，根据存储路径进行加载恢复数据库数据
[root@master ~]# unzip world.zip

-- 导入sql语句方式一：进入数据库中执行source语句
mysql> source /root/world.sql

-- 导入sql语句方式二：数据库外使用<符号进行导入
[root@master ~]# mysql < /root/world.sql

-- 查看表
mysql> use world;
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

二、查询索引信息

-- 查询表结构信息，获取索引配置
mysql> use world;
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Day006-数据库服务索引执行计划-图6

索引信息的展示形式：

序号 索引标识 解释说明
01 PK（PRI） 表示为聚簇索引，也可以理解为主键索引
02 K（MUL） 表示为辅助索引，也可以理解为一般索引
03 UK 表示唯一键索引 
-- 查询表索引信息，获取索引详细信息
mysql> use world;
mysql> show index from city\G
*************************** 1. row ***************************
        Table: city
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID
    Collation: A
  Cardinality: 4188
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: city
   Non_unique: 1
     Key_name: CountryCode
 Seq_in_index: 1
  Column_name: CountryCode
    Collation: A
  Cardinality: 232
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

-- 算下city表行数
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

Day006-数据库服务索引执行计划-图77

回显重要参数解读：

  • Non_unique：索引信息是否是唯一键值索引，其中0表示唯一，1 表示可以重复
  • Collation：显示创建索引排序方式，其中A代表升序，D代表降序
  • Cardinality：显示索引列的索引选择度，数据库会自动计算估值，索引列数据重复情况。先通过select count(*) from city;计算city表总行数4079，这里4188/4079约等于1，表示重复值越少；这里232/4079约等于0，表示重复值越多。
  • Sub_part：当设置前缀索引时，会显示截取的前缀字符数量
  • Visible：是否临时禁用索引功能，可以影响日常批量操作效率

三、创建索引信息

环境准备

-- 创建数据库db18
mysql> create database db18;

3.1 创建单列索引

创建单列索引语法格式

-- 创建主键索引，主键索引是一种特殊的唯一索引，一个表中只能有一个主键索引，不允许有空值
mysql> CREATE TABLE `table_name` ( ...,PRIMARY KEY ( `column` ))
mysql> ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

-- 创建辅助索引，辅助索引又称为普通索引,是MySOL中的基本索引类型,允许在定义索引的列中插入重复值和空值
mysql> CREATE TABLE `table_name` ( ...,index index_name(column(length))
mysql> CREATE index `index_name` on table(column(length))
mysql> alter table `table_name` add index `index_name` (column(length));
mysql> alter table `table_name` add index `index_name` on (column(length));

-- 创建唯一键索引，唯一索引指索引列的值必须唯一,但允许有空值;如果是组合素引,则列值的组合必须唯一;
mysql> CREATE TABLE `table_name` ( ...,unique index index_name(column(length))
mysql> CREATE unique index `index_name` on table(column(length))
mysql> ALTER TABLE `table_name` ADD UNIQUE `index_name` on (column(length));

Day006-数据库服务索引执行计划-图8

3.1.1 创建主键索引

情况一：创建表的时候将索引创建

-- 创建表的时候顺带创建主键索引
mysql> create table db18.test01(id int,name char(10),primary key(id));
-- 结果验证
mysql> desc db18.test01;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

情况二：创建表之后再创建索引

-- 先创建表test02
mysql> create table db18.test02(id int,name char(10));

-- 在已有表的基础创建主键索引
mysql> alter table db18.test02 ADD primary key(id);

-- 结果验证，查看索引
mysql> desc db18.test02;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show index from db18.test02\G;
*************************** 1. row ***************************
        Table: test02
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

ERROR:
No query specified

3.1.2 创建辅助索引

情况一：创建表的时候将索引创建

-- 创建表的时候顺带创建辅助索引,不带前缀
mysql> create table db18.test03(id int,name char(10),index index_name(name));
-- 结果验证
mysql> desc db18.test03;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  | MUL | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- 创建表的时候顺带创建辅助索引,设置排序方式为降序
mysql> create table db18.test05(id int,name char(10),age char(5),index zq(name desc));
-- 结果验证，观察Collation的值为D，代表为降序
mysql> show index from db18.test05\G;
*************************** 1. row ***************************
        Table: test05
   Non_unique: 1
     Key_name: zq
 Seq_in_index: 1
  Column_name: name
    Collation: D
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

情况二：创建表之后再创建索引

-- 先创建表test08
mysql> create table db18.test08(id int,name char(10));

-- 在已有表的基础添加辅助索引
mysql> alter table db18.test08 ADD index index_name(name);

-- 结果验证，查看索引
mysql> desc db18.test08;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  | MUL | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3.1.3 创建唯一键索引

情况一：创建表的时候将索引创建

-- 创建表的时候顺带创建唯一索引
mysql> create table db18.test06(id int,name char(10),unique index index_name(name));
-- 结果验证，观察到Key字段为UNI
mysql> desc db18.test06;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

情况二：创建表之后再创建索引

-- 先创建表test09
mysql> create table db18.test09(id int,name char(10));

-- 在已有表的基础添加唯一索引
mysql> alter table db18.test09 ADD unique index index_name(name);

-- 结果验证，查看索引
mysql> desc db18.test09;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3.2 创建联合索引(多列索引)

作用：减少查询数据信息时回表次数

情况一：创建表的时候将索引创建

-- 创建表的时候顺带创建联合索引
mysql> create table db18.test07(id int,name char(10),age char(5),index index_name(name,age));
-- 结果验证，观察到Seq_in_index字段为1和2
mysql> SHOW INDEX FROM db18.test07\G;
*************************** 1. row ***************************
        Table: test07
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: test07
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 2
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

情况二：创建表之后再创建索引

-- 先创建表test10
mysql> create table db18.test10(id int,name char(10),age char(5));

-- 在已有表的基础添加唯一索引
mysql> alter table db18.test10 ADD index index_name(name,age);

-- 结果验证，查看索引
-- 结果验证，观察到Seq_in_index字段为1和2
mysql> SHOW INDEX FROM db18.test10\G;
*************************** 1. row ***************************
        Table: test10
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: test10
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 2
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

3.3 创建前缀索引

mysql> alter table city add index ix_n(name(10));

Day006-数据库服务索引执行计划-图9

情况一：创建表的时候将索引创建

-- 创建表的时候顺带创建辅助索引,带前缀
mysql> create table db18.test04(id int,name char(10),index index_name(name(5)));
-- 结果验证,观察到Sub_part这个字段显示值为5
mysql> show index from db18.test04\G;
*************************** 1. row ***************************
        Table: test04
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: 5
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

情况二：创建表之后再创建索引

-- 先创建表test11
mysql> create table db18.test11(id int,name char(10));

-- 在已有表的基础添加辅助索引
mysql> alter table db18.test11 ADD index index_name(name(5));

-- 结果验证,观察到Sub_part这个字段显示值为5
mysql> show index from db18.test11\G;
*************************** 1. row ***************************
        Table: test11
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: 5
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

四、删除索引信息

# 删除索引信息（一般索引）
alter table 表名 drop index 索引名;
mysql> alter table t100w drop index idx_name;
-- 删除辅助索引
mysql> alter table t100w drop index oldboy;
-- 删除联合索引
mysql> alter table t100w drop index id;
-- 删除唯一索引

# 删除索引信息（聚簇索引）
alter table 表名 drop primary key;
mysql> alter table t100w drop primary key;
-- 删除聚簇索引

Day006-数据库服务索引执行计划-图10

4.1 删除聚簇索引

环境准备

-- 创建表的时候顺带创建主键索引
mysql> create table db18.test01(id int,name char(10),primary key(id));
-- 结果验证
mysql> desc db18.test01;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除聚簇索引

mysql> alter table db18.test01 drop primary key;

结果验证

mysql> desc db18.test01;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

4.2 删除辅助索引

环境准备

-- 创建表的时候顺带创建辅助索引,不带前缀
mysql> create table db18.test03(id int,name char(10),index index_name(name));
-- 结果验证
mysql> desc db18.test03;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  | MUL | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- 创建表的时候顺带创建辅助索引,设置排序方式为降序
mysql> create table db18.test05(id int,name char(10),age char(5),index zq(name desc));
-- 结果验证，观察Collation的值为D，代表为降序
mysql> show index from db18.test05\G;
*************************** 1. row ***************************
        Table: test05
   Non_unique: 1
     Key_name: zq
 Seq_in_index: 1
  Column_name: name
    Collation: D
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

删除辅助索引

mysql> alter table db18.test03 drop index index_name;

结果验证

-- 结果验证
mysql> desc db18.test03;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)