商城网站建设开发,内蒙古网站seo优化,蚂蜂窝网站分析,青园网站建设索引介绍 索引是帮助MySQL高效获取数据的排好序的数据结构#xff1b;存储的内容是指向表中数据的指针 mysql有多种索引#xff0c;Btree索引#xff0c;全文索引#xff0c;哈希索引、空间索引。其中以BTree索引应用最为广泛
假设从0到1000中#xff0c;猜一个数字。结…索引介绍 索引是帮助MySQL高效获取数据的排好序的数据结构存储的内容是指向表中数据的指针 mysql有多种索引Btree索引全文索引哈希索引、空间索引。其中以BTree索引应用最为广泛
假设从0到1000中猜一个数字。结果只有对、错2种猜错时会提示猜大了还是小了。猜对的次数会随着数字基数的增大而增加这就类似于一颗二叉树。 B-tree索引
**数据存储**节点内部节点和叶子节点都可以存储数据非叶子节点也存储数据和指向子节点的指针
**链式结构**叶子节点之间没有链式连接数据查找必须在树上进行遍历无法顺序读取所有数据。因此范围查询时效率差
**树的层数**非叶子节点也存储数据B树的每个节点可以容纳的数据较少因此树的高度相对较高 BTree索引
B-Tree和BTree的对比表
比较维度B树B树数据存储位置数据存储在所有节点包括内部节点和叶子节点数据仅存储在叶子节点内部节点只存储索引叶子节点链表叶子节点之间无链表叶子节点通过链表相连方便顺序遍历树的高度相对较高相对较低内部节点可容纳更多索引数据访问效率可能提前在非叶子节点找到数据查找时需到叶子节点效率稳定范围查询效率不适合范围查询通过叶子节点的链表结构范围查询效率高磁盘I/O性能相对较差节点存储数据和索引占用磁盘空间更少I/O 性能较好
可视化BTree操作
数据写入时把数据均匀的写入到磁盘中。根据页节点的存储信息取出边界值最大值或最小值。并将每个叶节点最大数据信息进行汇总整合BTree增加了同级相邻叶子节点之间的双向指针从而实现相邻节点相互跳转。往右跳转SELECT * FROM table WHERE id 5树越往上索引范围越大存储更多的索引将需要存储的数据信息均匀分配保存到对应页当中最终数据信息的均匀存储在进行等值数据查询时每次查询消耗的IO数量相等
BTree索引的优势介绍
擅于等值和范围查询数据树的结构层次是根 支 页跟和枝存储的是指针信息叶子节点存储的是数据信息在 inodb中通常设置 主键索引和 辅助索引
索引分类
主键索引 注主键索引也叫聚簇索引
聚簇索引主要是将多个簇区-64个数据页-1M聚集在一起就构成了所谓聚簇索引也可以称之为主键索引
聚簇索引作用是用来组织存储表的数据行信息的也可以理解为数据行信息都是按照聚簇索引结构进行存储的即按区分配空间的
聚簇索引的存储聚簇是多个簇簇是多个连续数据页(64个)页是多个连续数据块(4个)块是多个连续扇区(8个)
如果没有定义主键InnoDB会尝试选择第一个唯一且非空的索引作为聚簇索引
特点是存储数据的顺序和索引顺序一致叶子节点包含一条完整的记录
# innodb world库目录下只有3个文件(对应3张表)目录和索引全在一个文件内。而myisam引擎索引和数据是分散的
[rootdb01world]# pwd
/data/3306/data/world
[rootdb01world]# ls
city.ibd country.ibd countrylanguage.ibd构建过程
数据表创建时显示的构建了主键信息pk主键pk就是聚簇索引数据表创建时没有显示的构建主键信息时会将第一个不为空的UK的列做为聚簇索引数据表创建时以上条件都不符合时生成一个6字节的隐藏列row ID作为聚簇索引
为什么建议使用自增主键建立索引
如果使用字符作为索引mysql会比对编码转成2进制数字数字会比较长然后在使用数字建立索引直接使用一个int主键作为索引减少了转换所以快速
辅助索引
辅助索引介绍 创建表时显示的一般索引信息就是辅助索引 没有辅助索引就会进行全表扫描 非主键索引结构叶子节点存储的是主键值(一致性和节省存储空间) 有唯一性设置的辅助索引优先级比其他没有设置唯一性索引高
场景如果不知道信息的id只知道namewzy怎么找到这一行数据
假设普通查询select * from table where namex;
范围查询**select * from table where name like %x%;**1️⃣以name为x进行聚簇查询得到name为x的记录信息并查出idy
2️⃣然后用 select * from table whre idy; 回表查询然后得出整个信息
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)
) ENGINEInnoDB AUTO_INCREMENT4080 DEFAULT CHARSETlatin1;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表的名称这里是 cityNon_unique是否允许重复值即该索引是否唯一0 表示索引是唯一的如 PRIMARY索引1 表示索引可以包含重复的值Key_name索引的名称。PRIMARY 是主键索引的名称CountryCode 是非唯一索引的名称Seq_in_index索引中列的顺序。对于复合索引包含多个列指示该列在索引中的位置1 表示这是索引中的第一个列Column_name该索引所包含的列名。对于 PRIMARY 索引列名是 ID。对于 CountryCode 索引列名是 CountryCodeCollation索引中的列的排序规则。通常为 A 表示按升序排序Cardinality索引的基数表示该列中不同值的数量Sub_part如果索引只使用列的部分字符长度该列的长度。NULL 表示索引使用整个列的值Packed索引是否已压缩。NULL 表示没有压缩Null指示该列是否允许 NULL 值。若允许 NULL则显示 YES否则为空表示不允许 NULL 值Index_type索引的类型常见的有 BTREE、HASH 等。这里使用的是 BTREE表示该索引使用的是 BTree 结构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)
) ENGINEInnoDB;2.有一个查询需求根据电子邮件查询用户信息
SELECT * FROM users WHERE email wenzhiyongqq.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 # 使用哈希索引
) ENGINEMEMORY;4.注意哈希索引不能用于 排序 或 范围查询例如
SELECT * FROM users_hash WHERE email wzyqq.com;
SELECT * FROM users_hash WHERE email %wzymail.com%;联合索引
联合索引的列顺序需要与查询条件的列顺序匹配否则索引失效即最左匹配原则
1.假设有一个这样的订单表
CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY, # 订单idcustomer_id INT, # 客户 IDorder_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_unique0表示唯一索引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 namexx and age20;
当出现重名时设置2个条件以减少回表数量联合索引减少IO的更多
索引覆盖
覆盖索引Covering Index是指在查询执行过程中索引本身包含了查询所需要的所有列因此 MySQL 可以直接通过索引获取查询结果而无需访问表中的实际数据行。也就是说查询所需的所有数据都在索引中不需要回表即不需要访问表的数据页
1.假设有1个员工表
CREATE TABLE employees (emp_id INT PRIMARY KEY, # 员工idfirst_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 namewzy and passwordwzy666树的高度越低越好 数据量过大可以分库分表
删除索引
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
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_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 \
--concurrency100 \
--iterations1 \
--create-schemazhiyong18 \
--queryselect * from zhiyong18.t100w where k2VWlm \
engineinnodb \
--number-of-queries2000 \
-uroot -paa -h10.0.0.51 \
-verbose--concurrency100模拟的并发用户数为 100即每个并发用户都会执行相同的查询 --iterations1执行一次测试 --create-schemazhiyong18在运行查询之前创建一个名为 zhiyong18 的数据库架构如果该数据库不存在。注意mysqlslap 会尝试创建此数据库但不会删除已存在的数据库 --number-of-queries2000每个并发用户会执行 2000 次查询操作 3.可以发现主机负载异常的高查询耗时也比较长
[rootdb51~]# uptime23:23:59 up 9:06, 3 users, load average: 84.14, 40.37, 17.14
[rootdb51~]# top -bn1 | grep -A 1 %CPUPID USER PR NI VIRT RES SHR S %CPU %MEM TIME COMMAND1319 mysql 20 0 7063820 573312 17844 S 1194 28.3 39:03.62 mysqld4.对 k2 列增加辅助索引
mysql alter table t100w add index idx_k2(k2);
Query OK, 0 rows affected (2.28 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql 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秒就压测完毕效果明显
[rootdb51~]# mysqlslap \--defaults-file/data/3306/my.cnf \--concurrency100 \--iterations1 \--create-schemazhiyong18 \--queryselect * from zhiyong18.t100w where k2VWlm \engineinnodb \--number-of-queries2000 \-uroot -paa -h10.0.0.51 \-verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
BenchmarkRunning for engine rboseAverage number of seconds to run all queries: 3.737 secondsMinimum number of seconds to run all queries: 3.737 secondsMaximum number of seconds to run all queries: 3.737 secondsNumber of clients running queries: 100Average number of queries per client: 20索引优化
常规索引优化
总结 最好有主键索引 选择索引选择度高的列作为索引 配置了联合索引要按最左匹配原则 创建辅助索引前缀索引 少用 like 等筛选条件 注意观察执行计划的type 关注执行计划Extra 是否为 using where全表扫描 因为是范围查找所以name字段失效。
select * from table where age100 and namewzy;正确写法
select * from table where namewzy and age100;不低于
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 k2a00 or k2a01 k2a02 k2a03索引元信息
select * from mysql.innodb_index_stats;select * from mysql.innodb_table_stats; 在数据表中插入 修改 删除数据时聚簇索引树会进行同步实时更新辅助索引树会进行异步延时更新 数据频繁写入的时候会触发索引元数据锁导致数据不能写入 解决方法先临时放在change buffer区域积攒一定数据量再修改索引结构
mysql select * from mysql.innodb_index_stats where database_nameworld and table_namecity;
----------------------------------------------------------------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------------------------------------------------------------
