索引面试题分析

最新推荐文章于 2022-05-09 15:02:44 发布
最新推荐文章于 2022-05-09 15:02:44 发布
阅读量138 收藏
点赞数
分类专栏： Mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chengqiuming/article/details/120382422
版权

一 口诀

全值匹配我最爱，最左前缀要遵守； 

带头大哥不能死，中间兄弟不能断； 

索引列上少计算，范围之后全失效； 

KE 百分写最右，覆盖索引不写*； 

不等空值还有 OR，索引影响要注意； 

VAR 引号不可丢，SQL 优化有诀窍。

二 脚本

create table test03(
  id int primary key not null auto_increment,
  c1 char(10),
  c2 char(10),
  c3 char(10),
  c4 char(10),
  c5 char(10)
);

insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

三 分析

我们创建了 idx_test03_c1234 ，分析各种 SQL 的索引使用情况。

# 最左前缀要遵守
mysql> explain select * from test03 where c1='a1';
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# 最左前缀要遵守
mysql> explain select * from test03 where c1='a1' and c2='a2';
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# 最左前缀要遵守
mysql> explain select * from test03 where c1='a1' and c2='a2' and c3='a3';
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 93      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# 全值匹配我最爱
mysql> explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref                     | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 124     | const,const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# 搜索引擎的优化器对 c1，c2，c4，c3 的顺序进行了优化，优化后，还是符合全值匹配我最爱，但最好还是不要这样用，因为会用到优化器，也会消耗性能
mysql> explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref                     | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 124     | const,const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# 范围之后全失效，c1 和 c2 用于索引，c3 用于排序，c4 没用到
mysql> explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 93      | NULL |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

# 范围之后全失效，c1 c2 和 c3 用于索引，c4 用于排序
mysql> explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4>'a4';
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 124     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)


# where 之后的语句先优化成 where c1='a1' and c2='a2' and c3='a3' and c4>'a4'; 然后按照上面这句进行查找
mysql> explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 124     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

# c1 和 c2 用于查找，c3 用于排序
mysql> mysql> explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

# 和上面这句功能一样，说明上句的 c4='a4' 是干扰项
mysql> explain select * from test03 where c1='a1' and c2='a2' order by c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

# c1 和 c2 用于查找，c4 无法用于排序，使用到了 Using filesort（九死一生），中间兄弟不能断
mysql> explain select * from test03 where c1='a1' and c2='a2' order by c4;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

# c1 用于查找，c5 是干扰项，c2 和 c3 用于排序
mysql> explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |    20.00 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

# c1 用于查找，c5 是干扰项，c2 和 c3 无法用于排序，使用到了 Using filesort（九死一生），因为 c3 和 c2 的顺序反了
mysql> explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |    20.00 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

# c1，c2 用于查找，c2，c3 用于排序
mysql> explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

# 这里 c5='a5' 是干扰项，和上面这句一样
mysql> explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |    20.00 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)

# c1 和 c2 用于查找，c5 是干扰项，因为 c2='a2'，排序字段已经是一个常量了，所以 c3,c2 即使顺序反了，也不会影响 c3 排序，不会产生 filesort
mysql> explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |    20.00 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

# c1 用于查找，c2 和 c3 用于排序，不会产生 filesort
mysql> explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

# c1 用于查找，c2 和 c3 不能用于排序，产生了 temporary 和 filesort
mysql> explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                                                  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |    20.00 | Using index condition; Using temporary; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

# c1,c2，c3 索引都用到————like 后常量开头
mysql> explain select * from test03 where c1='a1' and c2 like 'kk%' and c3='a3';
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 93      | NULL |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

# c1 索引用到
mysql> explain select * from test03 where c1='a1' and c2 like '%kk' and c3='a3';
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

# c1 索引用到
mysql> explain select * from test03 where c1='a1' and c2 like '%kk%' and c3='a3';
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

# c1,c2，c3 索引都用到————like 后常量开头
mysql> explain select * from test03 where c1='a1' and c2 like 'k%kk%' and c3='a3';
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 93      | NULL |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

四 说明

group by 表面叫分组，但分组前必排序。所以 group by 的排序法则和 sort by 基本一致。但 group by 还会有临时表的产生。

chengqiuming
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL 高级(3): 索引优化（关联，排序，分组，案例）
Mr_tianyanxiaobai的博客
08-25 424
准备 【优化总结口诀】 全值匹配要记牢，最左前缀不能忘； 带头大哥不能死，中间兄弟不能断； 索引列上少计算，范围之后全失效； Like百分写最右，覆盖索引不写星； 不等空值还有or，索引失效要少用； 引号不可丢，丢了就失效。 在讲解之前，先创建两个表： CREATE TABLE IF NOT EXISTS `class` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PR
使用explan排查分析对慢sql进行调优的口诀和案例3
健康平安的活着的专栏
03-27 2017
一 概述 1.1 优化索引口诀 优化的口诀如下： 全值匹配我最爱， 最佳左前缀法则； 带头大哥不能死， 中间兄弟不能断； 索引列上少计算， 范围之后全失效； LIKE 百分写最右， 覆盖索引不写*； 不等空值还有 OR， 索引影响要注意； VAR 引号不可丢， SQL 优化有诀窍。 1.1.1 全值匹配我最爱 CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME); 结论：全职匹配我最爱指的是，查询的字段按照顺序在索引..
mysql高级----索引优化（二）：索引失效案例（应该避免）
阿官
03-05 2392
建表SQL索引情况一、全值匹配我最爱二、最佳左前缀法则1、where条件列的，从索引的最左前列开始，且不跳过索引中的列2、违背原则：未以最左前列开始，索引失效3、违背原则：跳过索引中间列（age），只引用了部分索引结论：where条件要满足最佳左前缀法则。口诀：带头大哥不能死，中间兄弟不能断三、不在索引列上做任何操作（计算、函数、自动or手动类型转换），会导致索引失效而转向全表扫描即使满足最左前缀...
MySQL高级知识（六）——索引优化
thedarkclouds的博客
09-09 1600
  前言：索引优化的目的主要是让索引不失效，本篇通过相关案例对索引优化进行讲解。 0.准备 创建经典的tb_emp表。 DROP TABLE IF EXISTS `tb_emp`; CREATE TABLE `tb_emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `age`...
MySQL学习--------索引失效试题复习和解析
qq_43719634的博客
11-16 108
表结构 create table test03( id int primary key not null auto_increment, c1 char(10), c2 char(10), c3 char(10), c4 char(10), c5 char(10) ); insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5'); insert into test03(c...
mysql相关面试题（锁、事务、索引等）
02-18
笔记-Mysql索引底层原理分析.pdf 一、InnoDb行格式、数据页结构以及索引底层原理分析.pdf 二、B+树索引实战.pdf 三、Mysql事务以及锁原理讲解.pdf 锁的原理一10.25.pdf 适用于mysql相关面试
mysql关于索引的面试题
04-07
现在，我们来分析索引可能失效的情况： 1. **计算、函数、类型转换**：当索引字段被用于计算或函数中，或者在比较时发生了隐式或显式的类型转换，数据库可能无法使用索引。 2. **联合索引**：如果在联合索引的条件...
SQL面试题汇总.pdf
最新发布
01-26
在SQL面试题（1）的答案中，使用了`RIGHT JOIN`将`testtable1`和`testtable2`进行连接。右连接返回所有右表（在这里是`testtable2`）的记录，即使在左表（这里是`testtable1`）中没有匹配的记录。对于左表中没有...
JavaSE基础面试题.docx
06-11
JavaSE基础面试题涵盖了许多核心概念，以下是这些知识点的详细说明： 1. **面向对象的特征**：包括封装、继承和多态。封装是隐藏对象的属性和实现细节，仅对外提供公共访问方式；继承是子类继承父类的属性和方法，...
MySQL面试题精选35题
08-07
MySQL 是一款广泛使用的开源关系型数据库管理系统，其面试题涵盖了多个方面，包括查询语句的执行过程、查询缓存、存储引擎的选择与区别、索引类型、事务处理、数据库空间管理以及查询优化等内容。以下是对这些知识点...
mysql出现which is not functionally dependent on columns in GROUP BY clause报错解决方案
热门推荐
实践求真知
05-10 1万+
一数据表结构 CREATE TABLE `sys_user_role` ( `id` varchar(30) NOT NULL, `uid` varchar(30) NOT NULL, `rid` varchar(30) NOT NULL, PRIMARY KEY (`id`,`uid`,`rid`), KEY `user_role_ibfk_2` (`uid`), KEY `user_role_ibfk_1` (`rid`), CONSTRAINT `sys_user_r.
python的mysql数据库的删除操作
实践求真知
11-22 1万+
一 代码 import pymysql # 打开数据库连接 db = pymysql.connect("localhost","root","root","db_test01" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 删除语句 sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" %
E-R图转化为关系表
实践求真知
03-22 6956
一E-R图 二关系表 这里只列出4个关系表。 t_project(project_id,project_name) t_order(project_id,order_id,data_type,creater,creat_time,end_time,state) t_workorder(order_id,workorder_id,operator,start_time,end_ti...
慢查询日志
实践求真知
09-21 5539
一什么是慢查询日志 1 MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。 2 具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。long_query_time 的默认值为 10，意思是运行 10秒 以上的语句。 3 由它来查看哪些 SQL 超出了我们的最大忍耐时间值，比如一条 sql 执行超过5秒钟，我们...
我的招聘网——数据库设计实战
实践求真知
08-10 3279
一分析 我的招聘网包括三个实体：应聘人员、企业、岗位。 应聘人员和岗位之间的申请关系是多对多的关系：一个应聘人员可以申请多个岗位，同样一个岗位也能被许多应聘人员申请。 企业与岗位需求应该是一对多的关系，一个企业可以发布多个岗位需求，但是每个岗位需求只对应一个企业。 应聘人员、企业与岗位之间存在一个上岗关系，这个上岗关系是1:1:1。 二基本表设计 1共设计8张表 2w...
SQL各种连接图解
实践求真知
03-11 2267
一内连接——InnerJoin 内连接Inner join基于连接谓词将两张表（如A和B）的列组合在一起，产生新的结果表。 二左外连接——LeftOuterJoin 三右外连接——RightOuterJoin 四全连接——FullJoin 五交叉连接——CrossJoin 交叉连接，又称卡迪尔连接或叉乘，如果A和B是两个集合，它们的交叉连接就记为...
mysql 5.7 ERROR 1786 (HY000) 错误处理
实践求真知
05-09 2177
一问题描述 执行下面语句。 create table zz.ii_app_version select * fromii.app_version; 报类似下面错误。 ERROR 1786 (HY000): CREATE TABLE ... SELECTis forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1 二根由分析 Mysql 5.7 版本，如果配置如下。 gtid_mode = ON enforce_gtid_con...
MySQL ALTER命令
实践求真知
04-12 2078
当需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。 我们先创建一张表，表名为：testalter_tbl。 MariaDB [RUNOOB]> create table testalter_tbl -> ( -> i INT, -> c CHAR(1) -> ); Query
数据库的主键和外键设计方法
实践求真知
04-27 2064
关于数据库主键和外键 https://blog.csdn.net/bingqingsuimeng/article/details/51595560 外键设计 https://blog.csdn.net/xianyu_0418/article/details/4469072
Oracle DBA面试题解析：SQL优化与索引策略
"Oracle面试题集合，涵盖SQL优化、索引管理、性能监控等多个方面，适合DBA面试准备。" 在Oracle数据库领域，面试时经常涉及的关键知识点包括SQL优化、索引原理、性能分析和调整策略。以下是对这些知识点的详细说明...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值