mysql的索引分类和索引优化

索引介绍

• 索引是帮助MySQL高效获取数据的排好序的数据结构；存储的内容是指向表中数据的指针

• mysql有多种索引，B+tree索引，全文索引，哈希索引、空间索引。其中以B+Tree索引应用最为广泛

假设从0到1000中，猜一个数字。结果只有对、错2种，猜错时会提示猜大了还是小了。猜对的次数会随着数字基数的增大而增加，这就类似于一颗二叉树。

B-tree索引

**数据存储：**节点（内部节点和叶子节点）都可以存储数据，非叶子节点也存储数据和指向子节点的指针

**链式结构：**叶子节点之间没有链式连接，数据查找必须在树上进行遍历，无法顺序读取所有数据。因此范围查询时效率差

**树的层数：**非叶子节点也存储数据，B树的每个节点可以容纳的数据较少，因此树的高度相对较高

B+Tree索引

B-Tree和B+Tree的对比表

比较维度	B树	B+树
数据存储位置	数据存储在所有节点（包括内部节点和叶子节点）	数据仅存储在叶子节点，内部节点只存储索引
叶子节点链表	叶子节点之间无链表	叶子节点通过链表相连，方便顺序遍历
树的高度	相对较高	相对较低，内部节点可容纳更多索引
数据访问效率	可能提前在非叶子节点找到数据	查找时需到叶子节点，效率稳定
范围查询效率	不适合范围查询	通过叶子节点的链表结构，范围查询效率高
磁盘I/O性能	相对较差，节点存储数据和索引	占用磁盘空间更少，I/O 性能较好

可视化B+Tree操作

• 数据写入时，把数据均匀的写入到磁盘中。根据页节点的存储信息，取出边界值（最大值或最小值）。并将每个叶节点最大数据信息进行汇总整合
• B+Tree增加了同级相邻叶子节点之间的双向指针，从而实现相邻节点相互跳转。往右跳转SELECT * FROM table WHERE id > 5
• 树越往上，索引范围越大，存储更多的索引
• 将需要存储的数据信息，均匀分配保存到对应页当中，最终数据信息的均匀存储
• 在进行等值数据查询时，每次查询消耗的IO数量相等

BTree索引的优势介绍

• 擅于等值和范围查询数据
• 树的结构层次是（根 支 页），跟和枝存储的是指针信息，叶子节点存储的是数据信息
• 在 inodb中，通常设置 主键索引和 辅助索引

索引分类

主键索引

注：主键索引也叫聚簇索引

聚簇索引主要是：将多个簇（区-64个数据页-1M）聚集在一起就构成了所谓聚簇索引，也可以称之为主键索引；

聚簇索引作用是：用来组织存储表的数据行信息的，也可以理解为数据行信息都是按照聚簇索引结构进行存储的，即按区分配空间的；

聚簇索引的存储：聚簇是多个簇，簇是多个连续数据页(64个)，页是多个连续数据块(4个)，块是多个连续扇区(8个)；

如果没有定义主键，InnoDB会尝试选择第一个唯一且非空的索引作为聚簇索引

• 特点是存储数据的顺序和索引顺序一致
• 叶子节点包含一条完整的记录

# innodb world库目录下，只有3个文件(对应3张表)，目录和索引全在一个文件内。而myisam引擎索引和数据是分散的
[root@db01world]# pwd
/data/3306/data/world
[root@db01world]# ls
city.ibd  country.ibd  countrylanguage.ibd

构建过程：

• 数据表创建时，显示的构建了主键信息（pk），主键（pk）就是聚簇索引；
• 数据表创建时，没有显示的构建主键信息时，会将第一个不为空的UK的列做为聚簇索引；
• 数据表创建时，以上条件都不符合时，生成一个6字节的隐藏列，row ID作为聚簇索引；

为什么建议使用自增主键建立索引？

• 如果使用字符作为索引，mysql会比对编码转成2进制数字，数字会比较长，然后在使用数字建立索引
• 直接使用一个int主键作为索引，减少了转换，所以快速

辅助索引

辅助索引介绍

• 创建表时，显示的一般索引信息就是辅助索引

• 没有辅助索引就会进行全表扫描

• 非主键索引结构叶子节点存储的是主键值(一致性和节省存储空间)

• 有唯一性设置的辅助索引优先级比其他没有设置唯一性索引高

==场景：==如果不知道信息的id，只知道name=wzy，怎么找到这一行数据

假设普通查询：select * from table where name='x';

范围查询：**`select * from table where name like '%x%';`**

1️⃣以name为x进行聚簇查询，得到name为x的记录信息，并查出id=y

2️⃣然后用 select * from table whre id=y; 回表查询，然后得出整个信息

3️⃣辅助索引存储的值是主键索引

辅助索引的构建

int(数字)类型可以排序，char(字符串)也可以排序，

查看主键|辅助索引

1.查看表结构

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       |                |
+-------------+----------+------+-----+---------+----------------+

2.查看建表语句，包含了索引设置

show CREATE TABLE city;

CREATE TABLE `city` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

3.查看索引的详细信息

mysql> show index from city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

索引中的字段说明

字段说明：

• Table：表的名称，这里是 city
• Non_unique：是否允许重复值（即该索引是否唯一）；0 表示索引是唯一的（如 PRIMARY索引），1 表示索引可以包含重复的值
• Key_name：索引的名称。PRIMARY 是主键索引的名称，CountryCode 是非唯一索引的名称
• Seq_in_index：索引中列的顺序。对于复合索引（包含多个列），指示该列在索引中的位置，1 表示这是索引中的第一个列
• Column_name：该索引所包含的列名。对于 PRIMARY 索引，列名是 ID。对于 CountryCode 索引，列名是 CountryCode
• Collation：索引中的列的排序规则。通常为 A 表示按升序排序
• Cardinality：索引的基数，表示该列中不同值的数量
• Sub_part：如果索引只使用列的部分字符长度，该列的长度。NULL 表示索引使用整个列的值
• Packed：索引是否已压缩。NULL 表示没有压缩
• Null：指示该列是否允许 NULL 值。若允许 NULL，则显示 YES，否则为空（表示不允许 NULL 值）
• Index_type：索引的类型，常见的有 BTREE、HASH 等。这里使用的是 BTREE，表示该索引使用的是 B+Tree 结构
• Comment：索引的备注信息。为空表示没有备注
• Visible：索引是否有效。YES 表示索引有效，可以被查询使用。否则就是处于被禁用状态
• Expression：如果该索引是基于表达式生成的（例如在索引中包含计算结果），则显示表达式的内容。如果是普通列索引，则为 NULL

前缀索引

MySQL 的 前缀索引 在存储**长文本（如 VARCHAR 或 TEXT）**时，比较有用。前缀索引是指创建索引时，只对列的前几个字符进行索引，而不是对整列数据进行索引。能够显著减少索引的存储空间，并在查询时提高效率

1.假设有一个包含用户信息的表 users，其中包含一个 email 字段，存储每个用户的电子邮件地址。如果有数百万条记录，email 列包含的是用户的电子邮件地址，而这些地址通常有很多相似的部分（如 @gmail.com、@mail.com 等）。如果我们为 email 列创建一个普通的 全文索引，这会导致非常大的索引文件，并且查询效率也不一定很高。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

2.使用前缀索引。可以只对邮箱的前缀部分进行索引，

CREATE INDEX email_prefix_idx ON users(email(10));

3.查看索引信息

mysql> desc users;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
| email | varchar(255) | YES  | MUL | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

mysql> show index from users;
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          0 | PRIMARY          |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | email_prefix_idx |            1 | email       | A         |           0 |       10 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

4.注意：使用 where email LIKE '%@gmail.com' 将无法利用前缀索引，

正确的为：SELECT * FROM users WHERE email LIKE 'john%';

哈希索引

• 哈希索引 主要用于 Memory 引擎，适用于 等值查询，可以提供 O(1) 的查找效率
• 哈希对于范围查询、排序 或其他非等值查询无效
• 如果数据量过大，哈希表可能会变得拥挤，导致 哈希冲突 增加，性能反而下降

1.假设有一个 users 表，包含以下数据：

CREATE TABLE users (
    user_id INT,
    username VARCHAR(50),
    email VARCHAR(100),
    PRIMARY KEY (user_id)
) ENGINE=InnoDB;

2.有一个查询需求：根据电子邮件查询用户信息

SELECT * FROM users WHERE email = 'wenzhiyong@qq.com';

如果针对 email 字段没有任何索引，那么mysql会执行全表扫描查找

3.可以对 email 设置一个哈希索引

哈希索引会将 email 的值通过哈希函数计算出一个哈希值，并直接定位到哈希表中相应的位置。这意味着查询的时间复杂度是 O(1)，查询时间和数据量无关

CREATE TABLE users_hash (
    user_id INT,
    username VARCHAR(50),
    email VARCHAR(100),
    PRIMARY KEY (user_id),
    KEY (email) USING HASH    # 使用哈希索引
) ENGINE=MEMORY;

4.注意哈希索引不能用于 排序 或 范围查询，例如：

SELECT * FROM users_hash WHERE email > 'wzy@qq.com';
SELECT * FROM users_hash WHERE email > '%wzy@mail.com%';

联合索引

• 联合索引的列顺序需要与查询条件的列顺序匹配，否则索引失效，即最左匹配原则

1.假设有一个这样的订单表

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,  # 订单id
    customer_id INT,                          # 客户 ID
    order_date DATE,                          # 订单日期
    total_amount DECIMAL(10, 2),              # 订单总金额 
    status VARCHAR(20)                        # 订单状态（例如：已发货、待付款等）
);

有2条独立的索引：基于 客户id 的和 基于订单日期的

CREATE INDEX idx_customer_id ON orders (customer_id);
CREATE INDEX idx_order_date ON orders (order_date);

2.有如下查询需求

查询某个客户在特定日期范围内的所有订单：

SELECT * FROM orders 
WHERE customer_id = 123 
AND order_date BETWEEN '2024-01-01' AND '2024-01-31';

查询某个客户所有已发货订单的总金额：

SELECT SUM(total_amount) FROM orders 
WHERE customer_id = 123 
AND status = 'Shipped';

3.先分析一下未使用联合索引时的查询情况

SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2024-01-01';

MySQL 首先会使用 idx_customer_id 来查找所有 customer_id = 123 的记录，然后，MySQL 会使用 idx_order_date 来查找所有 order_date = '2024-01-01' 的记录。最后MySQL 会 合并 这两个结果集，找出既符合 customer_id = 123 又符合 order_date = '2024-01-01' 的记录

这里就涉及到索引合并：

• 索引合并需要先分别扫描两个索引，消耗额外的计算资源
• 然后还要合并两个结果集，导致更多的计算和内存开销

4.以 客户id 和 订单日期创建一条联合索引

CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

5.当有如下查询时：

SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2024-01-01';

MySQL 会直接使用 idx_customer_date 联合索引，这样只需要 一次扫描，而不需要扫描两个索引再进行合并。由于只有一次扫描，查询效率更高，性能也更好

6.如果查询的列并没有出现在联合索引的最前面，索引将无法被有效利用。例如：

SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

因为 order_date 并不是联合索引的第一个列，所以联合索引失效了

7.查看 orders 的索引情况

mysql> show index from orders;
+--------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| orders |          0 | PRIMARY           |            1 | order_id    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | idx_customer_date |            1 | customer_id | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| orders |          1 | idx_customer_date |            2 | order_date  | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

PRI聚簇、主键索引

MUL辅助索引

mysql> show index from world.city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4046 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

1️⃣Non_unique=0表示唯一索引，1 表示可以重复

2️⃣Collation： 显示创建索引排序方式 A：升序 D：降序

3️⃣Cardinality：显示索引列的索引选择度，数据库会自动计算估值，索引列数据重复情况
4188/4079 约等于 1 重复值越少

选择度 = (唯一索引值的数量 / 总记录数)

• 选择度接近1：表示索引的选择性很好，索引列中的值几乎不重复。
• 选择度接近0：表示索引的选择性很差，索引列中的值大部分或全部相同。

索引的创建/删除

创建索引

1.创建表时就创建索引

create table t1 (id int, name varchar(255), primary key(id));

2.创建表后再创建索引

mysql> create table t1 (id int, name varchar(255));
mysql> desc t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int          | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

# 添加主键索引
mysql> alter table t1 add primary key (id);

辅助索引创建

 create table t1 (id int, name char(10), index index_name(name));
 mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  | MUL | NULL    |       |
+-------+----------+------+-----+---------+-------+

-- 索引信息：辅助索引 
alter table test01 add index idx_name(name);
alter table test01 add index idx_name(name(length));  -- 设置前缀，length可选项
alter table test04 add index xiaoA(name desc) ;       -- 调整排序方

• 只创建一个辅助索引

create table t1 (id int, name char(10), unique index index_name(name));

尽量遵循最左最高原则

• 多列索引，（联合索引）

create table t01 (id int,name char(10),age char(5),index index_name(name,age));

select * from t01 where name='xx' and age=20;

当出现重名时，设置2个条件以减少回表数量，联合索引减少IO的更多

索引覆盖

覆盖索引（Covering Index）是指在查询执行过程中，索引本身包含了查询所需要的所有列，因此 MySQL 可以直接通过索引获取查询结果，而无需访问表中的实际数据行。也就是说，查询所需的所有数据都在索引中，不需要回表（即不需要访问表的数据页）

1.假设有1个员工表

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,   # 员工id
    first_name VARCHAR(50),   # 姓氏
    last_name VARCHAR(50),    # 名字
    department VARCHAR(50),   # 任职部门
    salary DECIMAL(10, 2)     # 薪资
);

2.基于姓名创建一个联合索引，首先通过主键索引（PRIMARY KEY）定位到符合 emp_id = 1001 的数据行；然后，MySQL 会回表，访问数据行，取出 first_name 和 last_name 字段的值

CREATE INDEX idx_name ON employees(first_name, last_name);

3.没有覆盖索引方式查询员工姓名

SELECT first_name, last_name FROM employees WHERE emp_id = 1001;

4.使用覆盖索引方式查询员工姓名

SELECT first_name, last_name FROM employees WHERE first_name = 'John' AND last_name = 'Doe';

创建索引时的注意事项

• 创建辅助索引时，考虑回表次数，尽可能降低回表次数

• 使用覆盖索引，即索引本身包含了查询所需要的所有列

  • select name from user whrere name='wzy' and password='wzy666'
    

• 树的高度越低越好

• 数据量过大可以分库分表

删除索引

3）删除索引
聚簇索引删除 
alter table t100x drop primary key;
辅助索引删除：
alter table t100w drop index idx_name;  

mysql> show index from t01;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t01   |          1 | index_name |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t01   |          1 | index_name |            2 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
mysql> alter table t01 drop index index_name; 
Query OK, 0 rows affected (0.01 sec)

临时禁用索引

alter table city alter index CountryCode invisible;
alter table city alter index CountryCode visible;

查询压测数据库

1.准备测试的数据，插入100万条数据

mysql> CREATE TABLE `t100w` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

mysql> desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type      | Null | Key | Default           | Extra                                         |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id    | int       | YES  |     | NULL              |                                               |
| num   | int       | YES  |     | NULL              |                                               |
| k1    | char(2)   | YES  |     | NULL              |                                               |
| k2    | char(4)   | YES  |     | NULL              |                                               |
| dt    | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+

mysql> select * from t100w limit 10;
+------+--------+------+------+---------------------+
| id   | num    | k1   | k2   | dt                  |
+------+--------+------+------+---------------------+
|    1 |  25503 | 0M   | IJ56 | 2019-08-12 11:41:16 |
|    2 | 756660 | rx   | bc67 | 2019-08-12 11:41:16 |
|    3 | 876710 | 2m   | tu67 | 2019-08-12 11:41:16 |
|    4 | 279106 | E0   | VWtu | 2019-08-12 11:41:16 |
|    5 | 641631 | At   | rsEF | 2019-08-12 11:41:16 |
|    6 | 584039 | QJ   | VWlm | 2019-08-12 11:41:16 |
|    7 | 541486 | vc   | ijKL | 2019-08-12 11:41:16 |
|    8 | 771751 | 47   | ghLM | 2019-08-12 11:41:16 |
|    9 | 752847 | aQ   | CDno | 2019-08-12 11:41:16 |
|   10 | 913759 | ej   | EFfg | 2019-08-12 11:41:16 |
+------+--------+------+------+---------------------+

2.执行压测命令

mysqlslap \
--defaults-file=/data/3306/my.cnf \
--concurrency=100 \
--iterations=1 \
--create-schema='zhiyong18' \
--query="select * from zhiyong18.t100w where k2='VWlm'" \
engine=innodb \
--number-of-queries=2000 \
-uroot -paa -h10.0.0.51 \
-verbose

--concurrency=100：模拟的并发用户数为 100，即每个并发用户都会执行相同的查询

--iterations=1：执行一次测试

--create-schema='zhiyong18'：在运行查询之前创建一个名为 zhiyong18 的数据库架构（如果该数据库不存在）。注意，mysqlslap 会尝试创建此数据库，但不会删除已存在的数据库

--number-of-queries=2000：每个并发用户会执行 2000 次查询操作

3.可以发现主机负载异常的高，查询耗时也比较长

[root@db51~]# uptime
 23:23:59 up  9:06,  3 users,  load average: 84.14, 40.37, 17.14
[root@db51~]# top -bn1 | grep -A 1 '%CPU'
   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
  1319 mysql     20   0 7063820 573312  17844 S  1194 28.3  39:03.62 mysqld

4.对 k2 列增加辅助索引

mysql> alter table t100w add index idx_k2(k2);
Query OK, 0 rows affected (2.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type      | Null | Key | Default           | Extra                                         |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id    | int       | YES  |     | NULL              |                                               |
| num   | int       | YES  |     | NULL              |                                               |
| k1    | char(2)   | YES  |     | NULL              |                                               |
| k2    | char(4)   | YES  | MUL | NULL              |                                               |
| dt    | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+

5.再次压测，不到4秒就压测完毕，效果明显

[root@db51~]# mysqlslap \
> --defaults-file=/data/3306/my.cnf \
> --concurrency=100 \
> --iterations=1 \
> --create-schema='zhiyong18' \
> --query="select * from zhiyong18.t100w where k2='VWlm'" \
> engine=innodb \
> --number-of-queries=2000 \
> -uroot -paa -h10.0.0.51 \
> -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 3.737 seconds
Minimum number of seconds to run all queries: 3.737 seconds
Maximum number of seconds to run all queries: 3.737 seconds
Number of clients running queries: 100
Average number of queries per client: 20

索引优化

常规索引优化

总结：

• 最好有主键索引

• 选择索引选择度高的列作为索引

• 配置了联合索引，要按最左匹配原则

• 创建辅助索引，前缀索引

• 少用 >，like 等筛选条件

• 注意观察执行计划的type

• 关注执行计划Extra 是否为 using where，全表扫描

• 因为是范围查找，所以name字段失效。

select * from table where age>100 and name=wzy;

正确写法：

select * from table where name=wzy and age>100;

• 不低于

select name =! 'wzy';

• 以通配符开头

select name like '%wzy';

• or或者

AHI功能

AHI全称（中文名称）为自适应的hash索引/散列索引，用于在内存中建立索引，快速锁定内存中的热点数据索引页位置；

正常情况下，所有数据都是存储在磁盘中的，如果想访问读取相应磁盘的数据信息，都是会将磁盘数据调取存放在内存中，即消耗IO；

对于数据库服务而言，想要读取数据信息，也是会从磁盘中读取存储页，在放入内存中被数据库服务进行访问，索引访问也是一样的；

但是当数据页大量的被存放在内存中后，从大量内存中的数据页找到想要的，也是比较困难的事情；

因此，可以对内存中经常被访问数据索引页建立一个hash索引，从而可以帮助数据库服务快速定位内存中想要找的索引数据页；

mysql> show variables like 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+

change_buffer调整

change_buffer 与 InnoDB 存储引擎中的写入缓冲区有关。它主要用于加速写入操作，尤其是对非主键索引的插入、更新和删除操作

1.change_buffer 的大小，说明默认为内存的25%

mysql> show variables like '%change_buffer%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| innodb_change_buffer_max_size           | 25    |
| innodb_change_buffering                 | all   |
+-----------------------------------------+-------+

预读功能

当使用这样的查询时，k2 结果集超过总数据量的25%时，优化器就不走索引了。而是走预读功能

select * from t100w where k2='a00' or k2='a01' k2='a02' k2='a03'

索引元信息

select * from mysql.innodb_index_stats;


select * from mysql.innodb_table_stats;

• 在数据表中插入 修改 删除数据时，聚簇索引树会进行同步实时更新，辅助索引树会进行异步延时更新

• 数据频繁写入的时候会触发索引元数据锁，导致数据不能写入

  • 解决方法：先临时放在change buffer区域，积攒一定数据量再修改索引结构

mysql> select * from mysql.innodb_index_stats where database_name='world' and table_name='city';
+---------------+------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name   | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+
| world         | city       | CountryCode  | 2024-12-01 18:46:24 | n_diff_pfx01 |        232 |           7 | CountryCode                       |
| world         | city       | CountryCode  | 2024-12-01 18:46:24 | n_diff_pfx02 |       4079 |           7 | CountryCode,ID                    |
| world         | city       | CountryCode  | 2024-12-01 18:46:24 | n_leaf_pages |          7 |        NULL | Number of leaf pages in the index |
| world         | city       | CountryCode  | 2024-12-01 18:46:24 | size         |          8 |        NULL | Number of pages in the index      |
| world         | city       | PRIMARY      | 2024-12-01 18:46:24 | n_diff_pfx01 |       4188 |          20 | ID                                |
| world         | city       | PRIMARY      | 2024-12-01 18:46:24 | n_leaf_pages |         24 |        NULL | Number of leaf pages in the index |
| world         | city       | PRIMARY      | 2024-12-01 18:46:24 | size         |         25 |        NULL | Number of pages in the index      |
| world         | city       | idx_code_pop | 2024-12-01 18:46:24 | n_diff_pfx01 |        232 |           5 | CountryCode                       |
| world         | city       | idx_code_pop | 2024-12-01 18:46:24 | n_diff_pfx02 |       4052 |           5 | CountryCode,Population            |
| world         | city       | idx_code_pop | 2024-12-01 18:46:24 | n_diff_pfx03 |       4079 |           5 | CountryCode,Population,ID         |
| world         | city       | idx_code_pop | 2024-12-01 18:46:24 | n_leaf_pages |          5 |        NULL | Number of leaf pages in the index |
| world         | city       | idx_code_pop | 2024-12-01 18:46:24 | size         |          6 |        NULL | Number of pages in the index      |
+---------------+------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+
iff_pfx01 |        232 |           5 | CountryCode                       |
| world         | city       | idx_code_pop | 2024-12-01 18:46:24 | n_diff_pfx02 |       4052 |           5 | CountryCode,Population            |
| world         | city       | idx_code_pop | 2024-12-01 18:46:24 | n_diff_pfx03 |       4079 |           5 | CountryCode,Population,ID         |
| world         | city       | idx_code_pop | 2024-12-01 18:46:24 | n_leaf_pages |          5 |        NULL | Number of leaf pages in the index |
| world         | city       | idx_code_pop | 2024-12-01 18:46:24 | size         |          6 |        NULL | Number of pages in the index      |
+---------------+------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+

原文地址：https://blog.csdn.net/qq_73797346/article/details/144319844

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！