【MySQL基础】08：索引

最新推荐文章于 2024-04-19 23:00:30 发布
最新推荐文章于 2024-04-19 23:00:30 发布
阅读量170 收藏
点赞数 1
分类专栏： MySQL 文章标签： mysql 全文检索 数据库 索引 后端
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_55712478/article/details/122312268
版权

🏷️当前篇为【MySQL基础】08：索引 🤖

🏷️更多内容：MySQL基础教程，带你零基础上手MySQL数据库！💪

🏷️欢迎点赞 👍 收藏 🌟 关注 ❤️ 留言 ✍️

索引

什么是索引

索引，就是将数据表中的某一列或多列的值取出来构造成更便于查找的结构进行存储，生成数据表的目录；当我们进行数据查询的时候，则先回在目录中进行查找得到对应的数据的地址，然后再到数据表中快速过去数据记录，避免全表扫描。

索引的作用：排序和查找

索引的分类

MySQL中的索引，更具创建索引的列的不同，可以分为：

  • 主键索引：在数据表的主键字段创建索引，这个字段必须被primary key修饰，每张表只能有一个主键。
  • 唯一索引：在数据表中的唯一列（unique）创建索引，此列的所有制只能出现一次，值允许为null。
  • 普通索引：在普通字段上建立的索引，没有特殊约束。
  • 组合索引：两个及两个以上字段联合主键的索引。

说明：

  1. 在创建数据表并同时将列声明为主键时，会自动在主键列创建主键索引；
  2. 在创建数据表并同时将列声明唯一约束，会自动在唯一键列创建唯一索引；

索引操作

创建索引

唯一索引

-- unique：声明为唯一索引， <index_name> : 索引名，<table_name>：数据表名，<column_name>：需要创建索引的列名
create unique index <index_name> on <table_name>(<column_name>);

普通索引

create index <index_name> on <table_name>(<column_name>);

组合索引

create index <index_name> on <table_name>(<column_name_1>,<column_name_2>,...,<column_name_n>);

全文索引

全文索引是MySQL5.6版本新增的索引，可以通过此索引进行全文检索，但是MySQL全文检索不支持中文，在应用开发中通常是通过搜索引擎<es…>实现全文检索。

create fulltext index <index_name> on <table_name>(<column_name>);

使用索引

索引生效的条件: where 字句中使用创建了索引的列作为条件。

-- sample sql
DROP TABLE IF EXISTS `worker`;
CREATE TABLE `worker` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(10) DEFAULT '0',
  `job` varchar(30) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `created_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age_job_phone` (`name`,`age`,`job`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into worker(name,age,job,phone,created_time) values('Bile',22,'Java Developer','13412345678',SYSDATE()),('Reox',26,'Go Developer','13401234567',SYSDATE()),('Wrood',33,'C++ Developer','15512345678',SYSDATE()),('Kiols',24,'Java Developer','13512345678',SYSDATE()),('Qrtus',31,'Python Developer','18812345678',SYSDATE());

通过执行过程查看索引使用详细

explain select_statement;

-- sample sql-1 索引生效
explain select * from worker where name = 'Bile' and age  = 22 and job = 'Java Developer' and phone = '13412345678';
-- result
mysql> explain select * from worker where name = 'Bile' and age  = 22 and job = 'Java Developer' and phone = '13412345678';
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys          | key                    | key_len | ref                     | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | worker | NULL       | ref  | idx_name_age_job_phone | idx_name_age_job_phone | 258     | const,const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------------------+------+----------+-------+
1 row in set (0.03 sec)

-- sample sql-2 索引生效
explain select * from worker where age  = 22 and job = 'Java Developer' and phone = '13412345678' and name = 'Bile';
-- result
mysql> explain select * from worker where age  = 22 and job = 'Java Developer' and phone = '13412345678' and name = 'Bile';
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys          | key                    | key_len | ref                     | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | worker | NULL       | ref  | idx_name_age_job_phone | idx_name_age_job_phone | 258     | const,const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------------------+------+----------+-------+
1 row in set (0.03 sec)

-- sample sql-3 索引生效
explain select * from worker where name = 'Bile' and age  = 22 and job = 'Java Developer';
-- result
mysql> explain select * from worker where name = 'Bile' and age  = 22 and job = 'Java Developer';
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys          | key                    | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | worker | NULL       | ref  | idx_name_age_job_phone | idx_name_age_job_phone | 211     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------+
1 row in set (0.04 sec)

-- sample sql-4 索引失效
explain select * from worker where age  = 22 and job = 'Java Developer' and phone = '13412345678';
-- result
mysql> explain select * from worker where age  = 22 and job = 'Java Developer' and phone = '13412345678';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | worker | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.04 sec)
  • possible_keys：可能用到的索引
  • key：实际使用的索引
  • 组合索引生效与顺序无关，MySQL底层的优化器会进行优化，调整索引的顺序

查看索引

show create table <table_name>\G;

show indexes from <table_name>;

show keys from <table_name>;

删除索引

drop index <index_name> on <table_name>;

索引失效

1. 违反最左前缀法则

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

-- sample sql
explain select * from worker where age  = 22 and job = 'Java Developer' and phone = '13412345678';
-- result
mysql> explain select * from worker where age  = 22 and job = 'Java Developer' and phone = '13412345678';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | worker | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.03 sec)

2. 在索引列上做任何操作

如计算、函数、（自动or手动）类型转换等操作，会导致索引失效从而全表扫描

-- sample sql
explain select * from worker where left(name,5) = 'zhangsan' and age  = 22 and job = 'Java Developer' and phone = '13412345678';
-- result
mysql> explain select * from worker where left(name,5) = 'zhangsan' and age  = 22 and job = 'Java Developer' and phone = '13412345678';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | worker | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.04 sec)

3. 索引范围条件右边的列

索引范围条件右边的索引列会失效，

drop index idx_name_age_job_phone on worker;
create index idx_name on worker(name); 
-- sample sql -1
explain select * from worker where age > 10 and id > 20 and name = 'Bile';
-- result
mysql> explain select * from worker where age > 20 and name = 'Bile' and job = 'Java Developer';
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | worker | NULL       | range | idx_name_age_job_phone | idx_name_age_job_phone | 88      | NULL |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+-----------------------+
1 row in set (0.06 sec)

4. 尽量使用覆盖索引

只访问索引查询（索引列和查询列一致），减少select *

-- sample sql 1 索引生效
explain select name,age,job,phone from worker where age = 20;
-- result
mysql> explain select name,age,job,phone from worker where age = 20;
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | worker | NULL       | index | idx_name_age_job_phone | idx_name_age_job_phone | 258     | NULL |    5 |    20.00 | Using where; Using index |
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+--------------------------+
1 row in set (0.06 sec)

-- sample sql 2 索引失效
explain select * from worker where age = 20;
-- result
mysql> explain select * from worker where age = 20;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | worker | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.06 sec)

5. 使用不等于（!=、<>）

mysql在使用不等于（!=、<>）的时候无法使用索引会导致全表扫描（除覆盖索引外）

-- sample sql 1 索引生效
explain select age from worker where age != 20;
explain select age from worker where age <> 20;
-- result
mysql> explain select age from worker where age != 20;
+----+-------------+--------+------------+-------+---------------+------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key                    | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | worker | NULL       | index | NULL          | idx_name_age_job_phone | 258     | NULL |    5 |    80.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+------------------------+---------+------+------+----------+--------------------------+
1 row in set (0.06 sec)

mysql> explain select age from worker where age <> 20;
+----+-------------+--------+------------+-------+---------------+------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key                    | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | worker | NULL       | index | NULL          | idx_name_age_job_phone | 258     | NULL |    5 |    80.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+------------------------+---------+------+------+----------+--------------------------+
1 row in set (0.06 sec)

-- sample sql 2 索引失效
explain select * from worker where age != 20;
explain select * from worker where age <> 20;
-- result
mysql> explain select * from worker where age != 20;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | worker | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    80.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.06 sec)

mysql> explain select * from worker where age <> 20;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | worker | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    80.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.06 sec)

6. like以通配符开头

-- sample sql 1 索引生效
explain select * from worker where name like 'Bile%' and age  = 22 and job = 'Java Developer' and phone = '13412345678';
-- result
mysql> explain select * from worker where name like 'Bile%' and age  = 22 and job = 'Java Developer' and phone = '13412345678';
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | worker | NULL       | range | idx_name_age_job_phone | idx_name_age_job_phone | 258     | NULL |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+-----------------------+
1 row in set (0.07 sec)

-- sample sql 2 索引失效
explain select * from worker where name like '%Bile' and age  = 22 and job = 'Java Developer' and phone = '13412345678';
-- result 
mysql> explain select * from worker where name like '%Bile' and age  = 22 and job = 'Java Developer' and phone = '13412345678';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | worker | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.06 sec)

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

-- sample sql 1 索引生效
explain select * from worker where name = 'Bile';
-- result
mysql> explain select * from worker where name = 'Bile';
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | worker | NULL       | ref  | idx_name_age_job_phone | idx_name_age_job_phone | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------+
1 row in set (0.06 sec)

-- sample sql 2 索引失效
explain select * from worker where name = 000;
-- result
mysql> explain select * from worker where name = 000;
+----+-------------+--------+------------+------+------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys          | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | worker | NULL       | ALL  | idx_name_age_job_phone | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+--------+------------+------+------------------------+------+---------+------+------+----------+-------------+
1 row in set (0.06 sec)

8. or连接

-- sample sql 1 索引生效
explain select * from worker where name = 'Bile' and age > 20;
-- result
mysql> explain select * from worker where name = 'Bile' and age > 20;
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | worker | NULL       | range | idx_name_age_job_phone | idx_name_age_job_phone | 88      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------------+------------------------+---------+------+------+----------+-----------------------+
1 row in set (0.07 sec)

-- sample sql 2 索引失效
explain select * from worker where name = 'Bile' or age > 20;
-- result
mysql> explain select * from worker where name = 'Bile' or age > 20;
+----+-------------+--------+------------+------+------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys          | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | worker | NULL       | ALL  | idx_name_age_job_phone | NULL | NULL    | NULL |    5 |    46.66 | Using where |
+----+-------------+--------+------------+------+------------------------+------+---------+------+------+----------+-------------+
1 row in set (0.07 sec)

9. order by

正常（索引参与了排序）
explain select * from user where name = ‘zhangsan’ and age = 20 order by age,pos;
备注：索引有两个作用：排序和查找

explain select name,age from user where name = 'zhangsan' order by pos;//
explain select name,age from user where name = 'zhangsan' order by pos,age;//违反最左前缀法则
explain select * from user where name = 'zhangsan' and age = 20 order by created_time,age;//含非索引字段

-- sample sql 1 索引参与了排序
explain select * from worker where name = 'Bile' and age  = 22 order by age;
-- result
mysql> explain select * from worker where name = 'Bile' and age  = 22 order by age;
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys          | key                    | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | worker | NULL       | ref  | idx_name_age_job_phone | idx_name_age_job_phone | 88      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------+------+----------+-------+
1 row in set (0.06 sec)

-- 导致额外的文件排序
-- sample sql 2 违反最左前缀法则
explain select name,age from worker where name = 'Bile' order by job,age;
-- result
mysql> explain select name,age from worker where name = 'Bile' order by job,age;
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra                       |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------------------+
|  1 | SIMPLE      | worker | NULL       | ref  | idx_name_age_job_phone | idx_name_age_job_phone | 83      | const |    1 |   100.00 | Using index; Using filesort |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------------------+
1 row in set (0.07 sec)

-- sample sql 3 含非索引字段
explain select * from worker where name = 'Bile' and age  = 22 order by created_time;
-- result
mysql> explain select * from worker where name = 'Bile' and age  = 22 order by created_time;
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys          | key                    | key_len | ref         | rows | filtered | Extra          |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------+------+----------+----------------+
|  1 | SIMPLE      | worker | NULL       | ref  | idx_name_age_job_phone | idx_name_age_job_phone | 88      | const,const |    1 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------------+------+----------+----------------+
1 row in set (0.07 sec)

10. group by

正常（索引参与了排序）
explain select name,age from worker where name = 'Bile' group by age;
分组之前必排序（排序同order by）

-- sample sql 1 索引正常
explain select name,age from worker where name = 'Bile' group by age;
-- result
mysql> explain select name,age from worker where name = 'Bile' group by age;
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | worker | NULL       | ref  | idx_name_age_job_phone | idx_name_age_job_phone | 83      | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
1 row in set (0.07 sec)

-- 导致产生临时表（会降低性能）
-- sample sql 2 违反最左前缀法则
explain select name,job from worker where name = 'Bile' group by job;
-- result
mysql> explain select name,job from worker where name = 'Bile' group by job;
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+------------------------------+
| id | select_type | table  | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+------------------------------+
|  1 | SIMPLE      | worker | NULL       | ref  | idx_name_age_job_phone | idx_name_age_job_phone | 83      | const |    1 |   100.00 | Using index; Using temporary |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+------------------------------+
1 row in set (0.06 sec)

-- sample sql 3 违反最左前缀法则
explain select name,age from worker where name = 'Bile' group by job,age;
-- result
mysql> explain select name,age from worker where name = 'Bile' group by job,age;
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+------------------------------+
| id | select_type | table  | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+------------------------------+
|  1 | SIMPLE      | worker | NULL       | ref  | idx_name_age_job_phone | idx_name_age_job_phone | 83      | const |    1 |   100.00 | Using index; Using temporary |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+------------------------------+
1 row in set (0.06 sec)

-- sample sql 4 含非索引字段
explain select name,age from worker where name = 'Bile' group by age,created_time;
-- result
mysql> explain select name,age from worker where name = 'Bile' group by age,created_time;
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------+
| id | select_type | table  | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra           |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------+
|  1 | SIMPLE      | worker | NULL       | ref  | idx_name_age_job_phone | idx_name_age_job_phone | 83      | const |    1 |   100.00 | Using temporary |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-----------------+
1 row in set (0.07 sec)

索引小结

索引优点

  • 提升检索效率，避免全表扫描
  • 索引可以避免数据库服务器进行排序，将随机IO转换为顺序IO

索引缺点

  • 索引是根据数据表中列的数据创建的，当数据表中创建索引的列发生DML操作时，索引也需要更新
  • 索引也是数据文件，同样会占用磁盘空间

使用注意

  • 数据表中数据量不大时，添加索引对检索的提升不明显
  • 数据表中数据量大但是DML操作频繁时，不建议使用索引
  • 避免在数据重复度高的列中建立索引，如性别等
  • 创建索引之后，要注意查询SQL语句的编写，避免索引失效或使用索引外的条件导致SQL性能下降
入秋的大橘
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
(1)MySql索引基础
12-14
本文是学习MySQL索引知识后进行得整理，部分图片来源马士兵老师的公开课，仅作笔记使用 一、MySql架构图 原创文章 1获赞 1访问量 50 关注 私信 展开阅读全文 作者：CatRecord
MySQL基础知识：索引、事务
mundo.wang的个人博客
11-11 1万+
not null、unique、主键、外键：每张表必须有一个主键，非空唯一。：解决了什么问题？表冗余的问题。（数据库三大范式）引入外键时，会有一个父表和子表的概念有外键字段的表为子表（或者可以叫主表和从表）
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 172万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MySQL性能优化：索引优化
weixin_55229531的博客
06-11 3285
对于一个查询而言，三星索引可能是其最好的索引。满足的条件如下： 索引将相关的记录放到一起则获得一星 如果索引中的数据顺序和查找中的排列顺序一致则获得二星（排序星） 如果索引中的列包含了查询中需要的全部列则获得三星（宽索引星）
mysql删除索引_SQL基础12：Mysql删除索引
weixin_39883129的博客
01-18 8168
图片发自简书App学习此文之前，一起先来回顾下Mysql创建索引简介：在本篇教程中，你将学习如何使用MYSQL DROP INDEX语法删除表中已经存在的索引。一、MYSQL删除索引的语法要从表中删除现有索引，可使用DROP INDEX*语句，如下所示：DROP INDEX index_name ON table_name语法说明:第一，你想删除的索引字段名在drop index 语法后第二，表名...
Mysql原理(二):索引的底层原理
12-14
索引的基础概念 1.数据库索引是什么? 数据库索引是数据库管理系统(DBMS)中一个排序的数据结构,以协助快速查询和更新数据库中表的结构. 2.索引的类型 普通索引：是最基本的索引，它没有任何限制, 唯一索引：列值唯一...
MySQL Day07 索引
01-19
MySQL 索引 MySQL官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构。 提取句子主干，就可以得到索引的本质：索引是数据结构。 1 索引的分类 一个表中主键索引只能有一个，唯一索引可能有多个 主键索引 ...
mysql 索引的基础操作汇总（四）
09-10
主要为大家详细介绍了mysql 索引的基础操作汇总，涵盖了创建和查看索引、删除索引等操作，感兴趣的小伙伴们可以参考一下
MySQL中利用索引对数据进行排序的基础教程
09-10
主要介绍了MySQL中利用索引对数据进行排序的基础教程,需要的朋友可以参考下
MySQL怎么排查慢sql语句，排查后一般怎么优化
hello_boyu的博客
04-18 277
记录了订单信息，其中包括订单号（order_id）、订单金额（amount）、订单创建时间（created_at）等字段。现在我们要分析并优化查询最近一周订单总金额超过100的订单数量。通过以上步骤，我们可以对慢查询进行优化，提高MySQL数据库的性能。通过执行计划可以看到是否使用了索引，以及是否存在全表扫描等情况。
【MySql】 MySQL中的MAX()和MIN()函数的高效使用
风是自由的，你也是。
04-19 266
我说过 我不闪躲我非要这么做讲不听 也偏要爱更努力爱 让你明白没有别条路能走你决定 要不要陪我讲不听偏爱靠我感觉爱等你的依赖对你偏爱痛也很愉快🎵 张芸京《偏爱》MySQL数据库是世界上最流行的关系型数据库管理系统之一，它不仅提供了存储数据的功能，还支持复杂的查询和数据操作。在数据分析中，我们经常需要找到一个数据集中的最大值和最小值。MySQL为此提供了两个非常有用的函数：MAX() 和 MIN()。接下来，我们将探讨这两个函数的使用方法以及一些高级技巧。
MySQL--数据的增删改
最新发布
pzn2506的博客
04-19 245
文中[ ]里面的内容可写可不写文中[ ]里。
Linux环境部署MySQL
阿楠的博客
04-19 111
MySQL是一种关系型数据库管理系统，所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点。本文采用社区版进行测试学习MySQL。
MySQL：执行一条查询语句期间发生了什么？
LZ的博客
04-13 394
利用一条语句的查询过程简述了mysql的架构
MySQL中如何随机获取一条记录
gjc592的博客
04-19 488
点击上方蓝字关注我随机获取一条记录是在数据库查询中常见的需求，特别在需要展示随机内容或者随机推荐的场景下。在 MySQL 中，有多种方法可以实现随机获取一条记录，每种方法都有其适用的情况和性能特点。在本文中，我们将探讨几种常用的方法，并推荐适合不同情况下的最佳方法。方法一：使用ORDER BY RAND()这是最常见的随机获取一条记录的方法之一：SELECT * FROM testdb....
mysql 如何查看一条SQL被回滚了
Programming Talk
04-15 552
直接查看一条SQL是否被回滚在MySQL中不是一个简单的任务。你需要结合错误日志、InnoDB状态、general log、binlog以及应用程序中的异常处理等多种手段来进行分析和判断。在实际应用中，根据具体的需求和场景选择合适的方法来追踪和监控SQL语句的执行和回滚情况是非常重要的。
基于SSM+Jsp+Mysql的多用户博客个人网站
老卓爱爪哇 的博客
04-19 779
个人网站，主要的模块包括查看；管理员；个人中心、博文类型管理、学生博客管理、学生管理、论坛信息、管理员管理、我的收藏管理、留言板管理、系统管理，学生；个人中心、学生博客管理、留言板管理、论坛信息、我的收藏管理、系统管理，前台首页；首页、博文类型、学生博客、论坛信息、新闻资讯、留言反馈、我的、跳转到后台、客服等功能。系统中管理员主要是为了安全有效地存储和管理各类信息，还可以对系统进行管理与更新维护等操作，并且对后台有相应的操作权限。
在mac上安装好python开发环境(pyenv、pyenv-virtualenv、docker-desktop、mysql5.7)，一文解决环境问题
zhiaidaidai的博客
04-19 422
python各种版本管理器、包管理器实在是太多了，感兴趣的可以看我的另一篇博客：http://t.csdnimg.cn/SRRyC但是说实话，建议不要看，直接入手本文如下的环境配置就好了。保证都是比较前沿，运用范围都比较广的（本人踩过太多坑啦，服辣！另外本文都是macOs Intel系统，建议win找另外的博客。而且至少保证环境安装了homebrew，如果没有，请看本人的另一篇博客：http://t.csdnimg.cn/FVuHa。
mysql技术内幕:sql编程pdf
09-05
读者可以了解到MySQL数据库的运行机制、数据存储结构以及索引优化等关键知识，帮助开发人员更好地调优和优化MySQL数据库，提高系统的性能和可靠性。 在SQL编程方面，该书从SQL语言的基础知识开始介绍，涵盖了SQL...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值