MySQL索引（二）

最新推荐文章于 2024-09-02 07:02:10 发布
最新推荐文章于 2024-09-02 07:02:10 发布
阅读量296 收藏 1
点赞数
分类专栏： MySQL高级
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46073538/article/details/125728882
版权

mysql 数据库 java

4、索引语法

创建索引：
create [unique|fulltext] index index_name on table_name (index_col_name,....);
查看索引：
show index from table_name;
删除索引：
drop index index_name on table_name;
练习：
  1. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
  2. phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
  3. 为profession、age、status创建联合索引。
  4. 为email建立合适的索引来提升查询效率。
mysql> create index idx_user_name on tb_user(name);
mysql> create unique index idx_user_phone on tb_user(phone);
mysql> create index idx_user_pro_age_sta on tb_user(profession,age,status);
mysql> create index idx_user_email on tb_user(email);

# 结果
mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone       |            1 | phone       | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_name        |            1 | name        | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            1 | profession  | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            2 | age         | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            3 | status      | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_email       |            1 | email       | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.03 sec)

5、SQL性能分析

查看执行频次：
mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 8     |
| Com_delete    | 2     |
| Com_import    | 0     |
| Com_insert    | 30    |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 1017  |
| Com_signal    | 0     |
| Com_update    | 22    |
| Com_xa_end    | 0     |
+---------------+-------+
11 rows in set (0.05 sec)
慢查询日志：
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.01 sec)

开启慢查询：

# 慢查询日志开启
slow_query_log = 1

long_query_time = 2

重新启动mysqld服务

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.01 sec)
profile详情：

show profiles 能够在做SQL优化时帮助我们了解事件都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：

set profiling = 1;

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.01 sec)

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

# 查看每一条SQL的耗时基本情况
show profiles;

# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

# 查看指定query_id 的SQL语句CPU的使用情况
show profile cpu for query query_id;

mysql> show profiles;
+----------+------------+-------------------------------------------------+
| Query_ID | Duration   | Query                                           |
+----------+------------+-------------------------------------------------+
|        1 | 0.00021200 | select @@profiling                              |
|        2 | 0.00012275 | select * from tb_user                           |
|        3 | 0.00023375 | SELECT DATABASE()                               |
|        4 | 0.00392925 | show databases                                  |
|        5 | 0.00291425 | show tables                                     |
|        6 | 0.00038200 | select * from tb_user                           |
|        9 | 0.00059875 | select * from tb_user where name = '吕布'       |
|       10 | 0.00048100 | select * from tb_user where id = 1              |
|       11 | 0.04580075 | select count(*) from tb_user                    |
+----------+------------+-------------------------------------------------+
10 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 11;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000105 |
| Executing hook on transaction  | 0.000005 |
| starting                       | 0.000011 |
| checking permissions           | 0.000009 |
| Opening tables                 | 0.000055 |
| init                           | 0.000007 |
| System lock                    | 0.000011 |
| optimizing                     | 0.000009 |
| statistics                     | 0.000023 |
| preparing                      | 0.000025 |
| executing                      | 0.045451 |
| end                            | 0.000018 |
| query end                      | 0.000006 |
| waiting for handler commit     | 0.000013 |
| closing tables                 | 0.000012 |
| freeing items                  | 0.000027 |
| cleaning up                    | 0.000016 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

mysql> show profile cpu for query 11;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000105 | 0.000055 |   0.000043 |
| Executing hook on transaction  | 0.000005 | 0.000003 |   0.000002 |
| starting                       | 0.000011 | 0.000006 |   0.000005 |
| checking permissions           | 0.000009 | 0.000005 |   0.000003 |
| Opening tables                 | 0.000055 | 0.000031 |   0.000024 |
| init                           | 0.000007 | 0.000003 |   0.000003 |
| System lock                    | 0.000011 | 0.000007 |   0.000005 |
| optimizing                     | 0.000009 | 0.000005 |   0.000004 |
| statistics                     | 0.000023 | 0.000013 |   0.000010 |
| preparing                      | 0.000025 | 0.000014 |   0.000011 |
| executing                      | 0.045451 | 0.044543 |   0.000000 |
| end                            | 0.000018 | 0.000011 |   0.000000 |
| query end                      | 0.000006 | 0.000006 |   0.000000 |
| waiting for handler commit     | 0.000013 | 0.000012 |   0.000000 |
| closing tables                 | 0.000012 | 0.000013 |   0.000000 |
| freeing items                  | 0.000027 | 0.000026 |   0.000000 |
| cleaning up                    | 0.000016 | 0.000017 |   0.000000 |
+--------------------------------+----------+----------+------------+
17 rows in set, 1 warning (0.00 sec)
explain执行计划：

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接顺序。

语法：

# 直接在select语句之前加上关键字explain/desc
EXPLAIN select 字段列表 from 表名 where 条件；

测试：

mysql> explain select * from tb_user;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

测试一个多表查询：

mysql> explain select * from student s,sc,course where s.sno = sc.sno and course.cno = sc.cno;
+----+-------------+--------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+
| id | select_type | table  | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra                                              |
+----+-------------+--------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | course | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL          |    4 |   100.00 | NULL                                               |
|  1 | SIMPLE      | sc     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |    5 |    20.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | s      | NULL       | eq_ref | PRIMARY       | PRIMARY | 40      | gradem.sc.sno |    1 |   100.00 | NULL                                               |
+----+-------------+--------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> explain select * from student where student.sno in (select sc.sno from sc where sc.cno = (select cno from course where cname = "数据库"));
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                                   |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------+
|  1 | PRIMARY     | student | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    4 |   100.00 | NULL                                                                    |
|  1 | PRIMARY     | sc      | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where; FirstMatch(student); Using join buffer (Block Nested Loop) |
|  3 | SUBQUERY    | course  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where                                                             |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

explain执行计划各个字段含义：

字段含义
idselect 查询的序列号，表示查询中执行select子句或操作标的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)
select_type表示select的类型，常见的取值有simple（简单表，即不使用表连接或子查询）、primary（主查询，即外层的查询）、union（union中的第二个或者后面的查询语句）、subquery（select/where之后包含子查询）
type表示连接的类型，性能由好到差的连接类型为NUll、system、const、eq_ref、ref、range、index、all
possible_keys显示可能应用在这张表上的索引，一个或多个
key实际使用的索引，如果为null，则没有使用索引
key_len表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rowsMySQL认为必须要执行查询的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的
filtered返回结果的行数占需读取行数的百分比，filtered的值越大越好
ref
Extra
毕竟尹稳健
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
数据库索引-简介
zhizhuodewo6的博客
08-06 223
索引是对数据库表中一个或多个列（例如，employee 表的姓名 (name) 列）的值进行排序的结构。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。 例如这样一个查询：select * from table where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引...
mysql添加普通索引_mysql在建表语句中添加索引
weixin_42429109的博客
01-18 2682
普通索引创建创建普通索引，即不添加 UNIQUE、FULLTEXT 等任何参数。【例】创建表名为 score 的数据表，并在该表的 id 字段上建立索引，SQL 语句如下:CREATE table score(id int(11) AUTO_INCREMENT primary key not null,name varchar(50) not null,math int(5) ...
【MySQL 面试系列】索引原理
csdn_CZQ的博客
09-21 910
Mysql进阶、索引原理、面试准备
性别字段为什么不适合加索引？从 B+树原理解释。
qq_43193797的博客
03-16 5533
尽量选择区分度高的字段作为索引,区分度的公式是 count(distinct col)/count(*)，表示 字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、 性别字段可能在大数据面前区分度就是 0。在性别字段上增加索引，并不能明显加快检索 速度 ...
[InnoDB]性别字段为什么不适合加索引
Java之上
11-01 9357
表结构与数据 id为主键，id为5,15,25,35…时sex=1 sex=0,90000条数据;sex=1,10000条数据 CREATE TABLE `people` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `sex` tinyint(1) unsi...
分享几道关于MySQL索引的重点面试题
01-19
但MySQL 索引你真的懂吗？这几道题带你了解索引的几个重要知识点 1. 什么是最左前缀原则？ 以下回答全部是基于MySQL的InnoDB引擎 例如对于下面这一张表 如果我们按照 name 字段来建立索引的话，采用B+树的结构，...
MySQL InnoDB 二级索引的排序示例详解
09-09
MySQL InnoDB 存储引擎的二级索引是其特性之一，用于提高数据查询效率。在InnoDB表中，除了主键索引（Primary Key），还可以创建一个或多个辅助索引（Secondary Indexes）。这些索引的叶子节点并不直接存储行数据，...
MySql索引详解，索引可以大大提高MySql的检索速度
04-10
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车拿汉语字典的目录页(索引)打比方，我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到...
mysql 索引详细介绍
12-16
MySQL中的索引是一种为了加速数据检索的数据库结构，它可以显著提升查询速度，降低数据库的负担。索引主要有两种类型：Hash索引和BTree索引。 1. BTree索引： - 全值匹配索引：当查询条件完全匹配索引的所有列时，...
哪些字段可以加索引？
qq_41582642的博客
03-27 1946
1.数据量超过300的表应该有索引； 2.经常与其他表进行连接的表，在连接字段上应该建立索引； 3.复合索引的几个字段是否经常同时以AND方式出现在Where子句中？如果是，则可以建立复合索引； 4.一个表如果是复合索引,尽量不要超过3个. 5.频繁进行数据操作(增,删,改会增加数据库性能的开销)的表，不要建立太多的索引； ...
Mysql索引详解
lianglong_1的博客
08-05 1681
Mysql索引相关总结（一）： 1. 什么是索引 2. 索引有什么用 3. 如何创建索引 4. 如何查看索引 5. 索引的分类 6. 索引的数据结构浅析 7. 什么情况下需要索引 8. 什么情况下不会走索引 9. 索引相关补充 10. 索引常见使用情形 11. 慢查询日志.........
Mysql索引常用操作
weixin_45118229的博客
04-10 1173
文章目录前言一、创建索引二、查看索引三、删除索引四、练习 前言 在数据库中如何创建索引。本文中练习中，使用的表数据DDL如下： create table tb_user ( id int auto_increment comment '主键' primary key, name varchar(50) not null comment '用户名', phone varchar(11) not null com
mysql email能索引吗_10、MySQL索引
weixin_35110266的博客
02-26 166
索引索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。3010405153566161119213955100MySQL中常见索...
mysql email能索引吗_mysql索引
weixin_39518678的博客
02-07 325
现实操作里，读写操作中读的比例往往远高于写。我们需要更快地查到所想查看的数据，所以要用索引来优化(加速查询)。首先要明确，索引在mysql里是键，查询会有key。索引如同字典里的目录、音序表。其次，明确磁盘里的IO和预读。一次IO约为9ms，包括寻道时间(磁臂移动到指定磁道所花的时间，一般5ms以下)、旋转延迟(60/7200/2=4.17ms)、传输时间(磁盘读出或写入的时间，零点几秒，相比较可...
MySQL数据库增删查改（基础）CRUD
最新发布
chendemingxxx的博客
09-02 799
比如说：创建一张学生表，有姓名，学号。插入两个学生。可能有点草率看看例子： 顺序不是问题，后面参数和前面括号参数对等就行。比如说上面的例子：把id和name参数倒过来了，后面参数一一对应也可以。不推荐使用全列查询原因：比如说上面例子我只要id或者说只要name；当然也可以查询多个列： 比如说我们创建一个这样的表命令：第一个 效果：第三个总分效果：拓展一个现象： 拓展：语法：比较运算符：逻辑运算符：解释：%在mysql这里代表的是多个任意字符，_代表的是 一个任意字符。揭晓来才是修改硬盘里面的内容。案例：
mysql-day02
m0_64370841的博客
08-29 2234
【代码】mysql-day02。
MySQL 函数、约束、多表查询与事务详解
A_cot的博客
08-30 1664
在 MySQL 数据库中，函数、约束、多表查询和事务是非常重要的概念，它们可以帮助我们更好地管理和操作数据。本文将详细介绍这些概念，并通过代码演示来帮助你更好地理解。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

毕竟尹稳健

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值