AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL - 践行索引优化

2024-01-02 51
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： MySQL - 践行索引优化

生猛干货

带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试

Pre

MySQL - Explain深度剖析

Table Demo

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

DB Version

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.29-log |
+------------+
1 row in set (0.00 sec)
mysql>

Case

KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE

联合索引

全值匹配

mysql> explain select * from employees where name = 'LiLei';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>

算算这个ke_len

key_len ： 显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

【字符串】

  • char(n)：n字节长度
  • varchar(n)：如果是utf-8，则长度 3n + 2 字节，加的2字节用来存储字符串长度

【数值类型】

  • tinyint：1字节
  • smallint：2字节
  • int：4字节
  • bigint：8字节

【时间类型】

  • date：3字节
  • timestamp：4字节
  • datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

name varchar(24) —> 3 * 24 + 2 = 74 , 用了联合索引中的name .

mysql> explain select * from employees where name = 'LiLei' and age= 22;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 78      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

key_len 变成了 78 ？

第二个是int , int 占 4个字节 ， 74 + 4 = 78 ，这个SQL用了联合索引中的 name + age

mysql> explain select * from employees where name = 'LiLei' and age= 22 and position = 'manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>

key_len = 74 + 4 + 72 = 140

那我们跳过age 呢 ？

mysql> explain select * from employees where name = 'LiLei'  and position = 'manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |    33.33 | Using index condition |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql>

用了联合所以中的 name

最左前缀

如果索引了多列，要遵守最左前缀法则 , 指的是查询从索引的最左前列开始并且不跳过索引中的列。

mysql> explain select * from employees where name = 'LiLei' and age= 22;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 78      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>

符合最左前缀。

不符合 最左前缀

user where : 使用 where 语句来处理结果，并且查询的列未被索引覆盖

不符合 最左前缀

user where : 使用 where 语句来处理结果，并且查询的列未被索引覆盖

禁止索引列上做任何操作（计算、函数、（自动or手动）类型转换）

会导致索引失效而转向全表扫描

mysql> explain select * from employees where name = 'LiLei';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> 
mysql> explain select * from employees where left(name,2) = 'LiLei';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>

结合索引那个B+Tree , 特征 排好序

left 函数，MYSQL并没有做优化 ，left(name,2) 在那棵B+Tree上并没有，肯定不会走索引。

看看函数的操作

加个索引

alter table employees add index idx_hire_time(hire_time) using btree;

查看目前的索引

mysql> show index from employees ;
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees |          0 | PRIMARY               |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | idx_name_age_position |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | idx_name_age_position |            2 | age         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | idx_name_age_position |            3 | position    | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | idx_hire_time         |            1 | hire_time   | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
mysql>

在索引列上使用函数

mysql> explain select * from employees where date(hire_time)='2018-09-30';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>

变幻一下

mysql> explain select * from employees where  hire_time>='2018-09-30 00:00:00' and  hire_time<='2018-09-30 23:59:59';
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_hire_time | idx_hire_time | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql>

好了 ，实验完毕

移除索引

alter table employees drop  index idx_hire_time;

存储引擎不能使用索引中范围条件右边的列

比对一下

mysql> explain select * from employees where name = 'LiLei' and age= 22 and position = 'manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>

key_len = 140 （74 + 4 + 78） 全部走了 idx_name_age_position (name,age,position)

mysql> explain select * from employees where name = 'LiLei' and age> 22 and position = 'manager';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_name_age_position | idx_name_age_position | 78      | NULL |    1 |    33.33 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql>

key_len = 78 （74 + 4 ） 走了 idx_name_age_position (name,age,position) 中的 name 和 age

为什么呢？

脑海中找到那个B+Tree

name 是相同的， 所以第二列 age 肯定是有序的， 而age这里取的是大于， age是大于， 第三列没办法保证有序。 如果age是等于，那可以，第三列有序。 上面这个图不是很合适，不要被误导了，放上去仅仅是为了让读者对B+树有个轮廓。

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

mysql> explain select * from employees where name = 'LiLei' and age= 22 and position = 'manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select name , age  from employees where name = 'LiLei' and age= 22 and position = 'manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 140     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>

看到第二个的 Extra ： Using Index 使用了覆盖索引

mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

mysql> 
mysql> explain select * from employees where name != 'LiLei' ;
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

is null,is not null 一般情况下也无法使用索引

mysql> explain select * from employees where name is null ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)
mysql>

null 值在树中会放到一起和其他节点搞个双向指针

like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作

mysql> explain select * from employees where name like '%Lei';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

%在前

还是要回想那个索引B+Tree , % 在前面 意味着前面可能还有其他的字符串， 那在树中的有序性没法保证啊

mysql> explain select * from employees where name like 'Lei%';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_name_age_position | idx_name_age_position | 74      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql>

继续回想那个索引B+Tree , % 不在前面 意味着%前面的字符串固定， 那在树中的就是有序的，当然可以走索引

key_len = 74 ,可以推导出 走了 联合索引中的name

like 的优化

【问题：解决like’%字符串%'索引不被使用的方法？】

A： 使用覆盖索引，查询字段必须是建立覆盖索引字段

mysql> explain select * from employees where name like '%Lei';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> 
mysql> 
mysql> explain select name ,age position  from employees where name like '%Lei';
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | idx_name_age_position | 140     | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql>

不敢说好太多， index 总比 all 好吧 。

B： 如果不能使用覆盖索引则可能需要借助搜索引擎 ，Es等

字符串不加单引号索引失效

少用or或in

用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引，详见范围查询优化

范围查询优化

增加索引

alter table employees add index idx_age(age) using BTREE;
mysql> explain select * from employees where age>=1 and age<=2000;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_age       | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>

没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引

优化方法： 可以将大的范围拆分成多个小范围

mysql> explain select * from employees where age>=1 and age<=10;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_age       | idx_age | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from employees where age>=11 and age<=20;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_age       | idx_age | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql>

还原索引

alter table employees drop  index idx_age ;

索引总结

like KK%相当于=常量，%KK和%KK% 相当于范围

搞定MySQL


文章标签：
云数据库 RDS MySQL 版
索引
关系型数据库
MySQL
存储
机器学习/深度学习
关键词：
云数据库 RDS MySQL 版索引优化
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
小小工匠
目录
相关文章
mikechen的互联网架构
|
1月前
|
SQL 关系型数据库 MySQL
大厂面试官：聊下 MySQL 慢查询优化、索引优化？
MySQL慢查询优化、索引优化，是必知必备，大厂面试高频，本文深入详解，建议收藏。关注【mikechen的互联网架构】，10年+BAT架构经验分享。
mikechen的互联网架构
137 0
大厂面试官：聊下 MySQL 慢查询优化、索引优化？
醉鱼Java
|
7月前
|
SQL 存储 关系型数据库
MySQL索引（二）索引优化方案有哪些
MySQL索引（二）索引优化方案有哪些
醉鱼Java
109 0
蓝易云
|
9天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化与慢查询优化：原理与实践
通过本文的介绍，希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法，并在实际项目中灵活运用这些技术，提升数据库的整体性能。
蓝易云
38 5
mikechen的互联网架构
|
1月前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案，包括索引优化、SQL慢查询优化和数据库表优化，帮助提升数据库性能。关注【mikechen的互联网架构】，10年+BAT架构经验倾囊相授。
mikechen的互联网架构
196 9
MySQL慢查询优化、索引优化、以及表等优化详解
喜欢猪猪
|
29天前
|
关系型数据库 MySQL Java
MySQL索引优化与Java应用实践
【11月更文挑战第25天】在大数据量和高并发的业务场景下，MySQL数据库的索引优化是提升查询性能的关键。本文将深入探讨MySQL索引的多种类型、优化策略及其在Java应用中的实践，通过历史背景、业务场景、底层原理的介绍，并结合Java示例代码，帮助Java架构师更好地理解并应用这些技术。
喜欢猪猪
30 2
sunrr
|
6月前
|
监控 关系型数据库 MySQL
MySQL 8.0如何进行索引优化？
【6月更文挑战第14天】MySQL 8.0如何进行索引优化？
sunrr
106 5
香吧香
|
5月前
|
SQL 缓存 关系型数据库
MySQL 查询索引失效及如何进行索引优化
MySQL 查询索引失效及如何进行索引优化
香吧香
217 1
蓝易云
|
4月前
|
关系型数据库 MySQL 数据库
如何利用MySQL建立覆盖原表的索引优化查询性能
通过合理使用覆盖索引，可以显著提高MySQL数据库的查询性能。然而，创建索引时需要仔细分析查询需求，合理设计索引结构，以确保索引能够发挥最大的效益。
蓝易云
185 0
公众号:码到三十五
|
6月前
|
关系型数据库 MySQL 数据库
MySQL索引优化：深入理解索引合并
MySQL索引优化：深入理解索引合并
公众号:码到三十五
81 1
sunrr
|
6月前
|
关系型数据库 MySQL 数据库
mysql索引优化
【6月更文挑战第16天】mysql索引优化
sunrr
36 2

热门文章

最新文章

  • 1
    RDS入门——Excel文件转存到RDS数据库实践
  • 2
    PostgreSQL SQL HINT的使用
  • 3
    MySql进阶使用之视图、索引（详解）
  • 4
    MYSQL 逗号分割字段行转列
  • 5
    mysql中的时间函数---运维常用
  • 6
    mysql索引优化
  • 7
    Mysql5.7使用DTS增量同步数据到MaxCompute
  • 8
    Linux下MySQL的基础（一）
  • 9
    MySQL学习之流程函数
  • 10
    mysql_init调用卡住原因分析
  • 1
    java云MES 系统源码Java+ springboot+ mysql 一款基于云计算技术的企业级生产管理系统
    116
  • 2
    mysql造数据占用临时表空间
    450
  • 3
    查询服务器中有没有mysql - 蓝易云
    167
  • 4
    查询mysql版本sql - 蓝易云
    80
  • 5
    实时计算 Flink版操作报错合集之用CTAS从mysql同步数据到hologres，改了字段长度，报错提示需要全部重新同步如何解决
    169
  • 6
    实时计算 Flink版操作报错合集之程序初始化mysql没有完成就报错如何解决
    299
  • 7
    实时计算 Flink版操作报错合集之sqlserver mysql都用的胖包，sqlserver的成功了，mysql报这个错如何解决
    320
  • 8
    实时计算 Flink版操作报错合集之连接器换成2.4.2之后，mysql作业一直报错如何解决
    203
  • 9
    解决centos7.0安装mysql后出现access defind for user@'localhost'的错误
    116
  • 10
    实时计算 Flink版产品使用合集之在抓取 MySQL binlog 数据时，datetime 字段会被自动转换为时间戳形式如何解决
    83

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • 使用DAS实现数据库SQL优化
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（CentOS 7）

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    【案例实战】SpringBoot整合阿里云文件上传OSS