SQL查询优化（MySQL入门）

已于 2024-01-29 12:27:09 修改
阅读量2.5k 收藏 21
点赞数 16
分类专栏： MySQL 文章标签： mysql
于 2022-01-23 17:03:03 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40695970/article/details/122529432
版权

SQL查询优化（MySQL入门）

SQL查询的优化要从这个伟大的故事说起。有一天，小猿🙊🙊在公司办公，钱老板交代了大量的数据查询任务💔💔，要查询完之后才能下班🏃🏃。忽然吟诗一首，目极魂断望不见，三声猿啼泪滴衣🙉🙉。我滴亲娘呢，这么多数据💀💀，什么时候才能查得完⌚️⌚️，什么时候才能吃上老婆煮的爱心晚餐🍜🍜。为了爱心晚餐，就是卷，就是干。接下来，我们就来看看小猿查询数据的妙招。👏👏👏

程序猿

文章目录

1. 索引

👉👉在数据库中，索引（Index）使数据库程序无需对整个表进行扫描，就可以在其中找到所需数据。而数据库中的索引是一个表中所包含的值的列表，其中注明了表中包含各个值的行所在的存储位置。

1.1 定义

👉👉索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引

1.2 分类

👉👉MySQL中支持的索引主要是B-tree（B-树）和Hash（哈希）两种。MyISAM和InnoDB存储引擎只支持B-tree索引，B-tree索引也是MySQL中最常用的索引结构。Memory（Heap）存储引擎可以支持Hash和B-tree索引。

索引基本都为B-tree索引：

  • 普通索引🍀🍀🍀
    在创建普通索引时，不需加任何限制条件，建立索引以后，查询时可以通过索引进行查询。(无任何限制条件)

  • 唯一索引🌺🌺🌺
    使用unique参数可以设置索引为唯一性索引。在创建唯一索引时，限制该索引的值必须唯一，主键就是一种特殊的唯一性索引。（索引值唯一）

  • 单列索引🌸🌸🌸
    在表中的单个字段上创建索引，单列索引只能根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。（一个字段）

  • 多列索引🌷🌷🌷
    在表中的多个字段上创建索引，多列索引指向创建时对应的多个字段，可以通过这几个字段进行查询。（多个字段）

  • 全文索引💐💐💐
    使用fulltext参数可以设置索引为全文索引。全文索引只能创建在char、varchar或text类型的字段上。使用全文索引可以提高查询速度。（字符串类型字段）

  • 空间索引🌻🌻🌻
    使用spatial参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括geometry（存储任何类型）和point（简单点）、linstring（简单线）和polygon（简单面）等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。（空间数据类型字段）🌝🌝🌝

1.3 创建索引

👉👉创建索引是在某个表的一列或多列上建立一个索引，提高对表数据的访问速度。创建索引有两种方式：使用Navicat图形工具和使用SQL语句进行创建。SQL语句创建又分为三种：创建新表时创建索引，在已经存在的表上创建索引和使用alter table语句来创建索引。

1.3.1 使用Navicat创建

🌞🌞使用Navicat图形工具为uid字段创建名为index_uid的普通索引。其中，索引设计中的索引类型可以选择Normal、Unique、Full Text三种选项，索引方法可选择B-tree或Hash选项。索引设计图如下：
使用Navicat创建

1.3.2 创建新表时创建

1️⃣ 创建普通索引
🌛🌛创建一个user2表，在uid字段上建立普通索引。SQL代码如下：

mysql> create table user2
    -> (uid int(20) not null,
    -> name varchar(30) not null,
    -> sex enum('男','女'),
    -> index(uid)
    -> );
Query OK, 0 rows affected
mysql> show create table user2;
| user2 | CREATE TABLE `user2` (
  `uid` int(20) NOT NULL,
  `name` varchar(30) NOT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set
mysql> explain select * from user2 where uid=1;
+----+-------------+-------+------+---------------+-----+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+-----+---------+-------+------+-------+
|  1 | SIMPLE      | user2 | ref  | uid           | uid | 4       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+-----+---------+-------+------+-------+
1 row in set

注：explain+sql语句，通过执行explain可以获得sql语句执行的相关信息。如上面结果显示，possible_keys和key处的值都为uid，说明uid索引已经存在，而且开始起作用。

2️⃣ 创建唯一性索引
🌏🌏创建一个user2表，在uid字段上建立名为index_uid的唯一性索引。SQL代码如下：

mysql> create table user2
    -> (uid int(20) unique not null,
    -> name varchar(30) not null,
    -> sex enum('男','女') default '男',
    -> unique key index_uid(uid asc)
    -> );
Query OK, 0 rows affected
mysql> show create table user2;
| user2 | CREATE TABLE `user2` (
  `uid` int(20) NOT NULL,
  `name` varchar(30) NOT NULL,
  `sex` enum('男','女') DEFAULT '男',
  UNIQUE KEY `uid` (`uid`),
  UNIQUE KEY `index_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set

注：index和key都可以表示索引关键字，选一即可；asc表示按照字段uid的升序进行排列。

3️⃣ 创建单列索引
🍄🍄创建一个user2表，在name字段上建立名为index_name的单列索引。SQL代码如下：

mysql> create table user2
    -> (uid int(20) not null,
    -> name varchar(30) not null,
    -> index index_name(name(20))
    -> );
Query OK, 0 rows affected
mysql> show create table user2;
| user2 | CREATE TABLE `user2` (
  `uid` int(20) NOT NULL,
  `name` varchar(30) NOT NULL,
  KEY `index_name` (`name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set

注：字段name的长度为30，索引index_name的长度为20，这样能提高查询速度，字符型数据只查询前面若干字符信息方可。

4️⃣ 创建多列索引
🌵🌵创建一个user2表，在name和sex字段上建立名为index_ns的多列索引。SQL代码如下：

mysql> create table user2
    -> (uid int(20) not null,
    -> name varchar(30) not null,
    -> sex enum('男','女') default '男',
    -> index index_ns(name,sex)
    -> );
Query OK, 0 rows affected
mysql> show create table user2;
| user2 | CREATE TABLE `user2` (
  `uid` int(20) NOT NULL,
  `name` varchar(30) NOT NULL,
  `sex` enum('男','女') DEFAULT '男',
  KEY `index_ns` (`name`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set
mysql> explain select * from user2 where name='mary';  #possible_keys和key的值都是index_ns。
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user2 | ref  | index_ns      | index_ns | 92      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set
mysql> explain select * from user2 where sex='女';  #额外信息（Extra）显示正在使用索引，possible_keys和key的值都为null。
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user2 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set

注：用explain语句查看索引的使用情况。多列索引中，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。如果没有使用索引的第一个字段，那么多列索引将不会起作用，所以在优化代码时需要考虑优化多列索引。

5️⃣ 创建全文索引
🌴🌴创建一个user2表，在info字段上建立名为index_info的全文索引。SQL代码如下：

mysql> create table user2
    -> (uid int(20) not null,
    -> info varchar(30) not null,
    -> fulltext index index_info(info)
    -> );
Query OK, 0 rows affected
mysql> show create table user2;
| user2 | CREATE TABLE `user2` (
  `uid` int(20) NOT NULL,
  `info` varchar(30) NOT NULL,
  FULLTEXT KEY `index_info` (`info`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

注：InnoDB在MySQL5.6版本以后开始支持全文检索，自MySQL5.7.6版本起，内置了ngram全文解析器，可以支持中文的全文索引。

6️⃣ 创建空间索引
🐈🐈创建一个user2表，在space字段上建立名为index_space的空间索引。SQL代码如下：

mysql> create table user2
    -> (uid int(20) not null,
    -> space geometry not null,
    -> spatial index index_space(space)
    -> )engine=MyISAM;  #表的存储引擎必须是MyISAM。
Query OK, 0 rows affected
mysql> show create table user2;
| user2 | CREATE TABLE `user2` (
  `uid` int(20) NOT NULL,
  `space` geometry NOT NULL,
  SPATIAL KEY `index_space` (`space`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
1 row in set

注：
a. 创建空间索引时，必须使用spatial参数来设置，而且表的存储引擎必须是MyISAM；
b. 空间数据类型的字段默认为非空，空间数据类型包括geometry，point，linstring和polygon类型等；
c. 如果表的存储引擎是InnoDB的话会报错 1464 - The used table type doesn’t support SPATIAL indexes 。

1.3.3 在已存在的表上创建

1️⃣ 创建普通索引
🐕🐕在user3表的uid字段上建立名为index_uid的普通索引。SQL代码如下：

mysql> create index index_uid on user3(uid);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table user3;
| user3 | CREATE TABLE `user3` (
  `uid` int(20) NOT NULL,
  `name` varchar(30) NOT NULL,
  `sex` enum('男','女') NOT NULL,
  `age` int(20) NOT NULL,
  KEY `index_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set

2️⃣ 创建唯一性索引
🐅🐅在user3表的uid字段上建立名为index_uid的唯一性索引。SQL代码如下：

mysql> create unique index index_uid on user3(uid);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table user3;
| user3 | CREATE TABLE `user3` (
  `uid` int(20) NOT NULL,
  `name` varchar(30) NOT NULL,
  `sex` enum('男','女') NOT NULL,
  `age` int(20) NOT NULL,
  UNIQUE KEY `index_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set

注：unique用来设置索引为唯一索引。

3️⃣ 创建单列索引
🐬🐬在user3表的name字段上建立名为index_name的单列索引。SQL代码如下：

mysql> create index index_name on user3(name(6));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table user3;
| user3 | CREATE TABLE `user3` (
  `uid` int(20) NOT NULL,
  `name` varchar(30) NOT NULL,
  `sex` enum('男','女') NOT NULL,
  `age` int(20) NOT NULL,
  KEY `index_name` (`name`(6))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set

注：name字段的数据类型varcha(30)，索引的数据类型为char(6)，查询时只查询name字段的前6个字符，不需要全部查询。

4️⃣ 创建多列索引
🐳🐳在user3表的name和sex字段上建立名为index_ns的多列索引。SQL代码如下：

mysql> create index index_ns on user3(name,sex);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table user3;
| user3 | CREATE TABLE `user3` (
  `uid` int(20) NOT NULL,
  `name` varchar(30) NOT NULL,
  `sex` enum('男','女') NOT NULL,
  `age` int(20) NOT NULL,
  KEY `index_ns` (`name`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set

注：查询条件中必须有name字段才能使用该索引。

5️⃣ 创建全文索引
🐠🐠在user3表的name字段上建立名为index_name的全文索引。SQL代码如下：

mysql> create fulltext index index_name on user3(name);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

注：fulltext用来设置索引为全文索引，name字段为varchar类型。

6️⃣ 创建空间索引
🐱🐱在user3表的space字段上建立名为index_space的空间索引。SQL代码如下：

mysql> create spatial index_space on user3(space);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

注：表的存储引擎必须是MyISAM，space字段必须为空间数据类型，而且为非空。

1.3.4 使用alter table语句创建

1️⃣ 创建普通索引
🐶🐶在user4表的uid字段上建立名为index_uid的普通索引。SQL代码如下：

mysql> alter table user4 add index index_uid(uid);

2️⃣ 创建唯一性索引
🐹🐹在user4表的uid字段上建立名为index_uid的唯一性索引。SQL代码如下：

mysql> alter table user4 add unique index index_uid(uid);

3️⃣ 创建单列索引
😎😎在user4表的name字段上建立名为index_name的单列索引。SQL代码如下：

mysql> alter table user4 add index index_name(name(6));

4️⃣ 创建多列索引
😋😋在user4表的name和sex字段上建立名为index_ns的多列索引。SQL代码如下：

mysql> alter table user4 add index index_ns(name,sex);

5️⃣ 创建全文索引
😉😉在user4表的name字段上建立名为index_name的全文索引。SQL代码如下：

mysql> alter table user4 add fulltext index index_name(name);

6️⃣ 创建空间索引
😆😆在user4表的name字段上建立名为index_space的空间索引。SQL代码如下：

mysql> alter table user4 add spatial index index_space(space);

1.4 删除索引

👉👉由于一些不再使用的索引会降低表的更新速度，会影响数据库的性能，所以将有些索引删除掉。

❄️❄️在user4中删除索引名为index_ns的多列索引。SQL代码如下：

mysql> drop index index_ns on user4;
mysql> show create table user4;
| user4 | CREATE TABLE `user4` (
  `uid` int(20) NOT NULL,
  `name` varchar(30) NOT NULL,
  `sex` enum('男','女') NOT NULL,
  `age` int(20) NOT NULL,
  UNIQUE KEY `index_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set

2. 视图

👉👉视图（View）是预定义的查询作为对象存储在数据库中，提高了性能也使得整体架构更加清晰。数据库管理员可以利用视图作为高性能接口向应用程序和用户提供数据。

2.1 定义

  • 💝 视图可以看作虚拟表或存储查询，其内容由查询定义，同真实的表一样，视图的作用类似于筛选 💝

  • 💝 定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图 💝

  • 💝 视图本身并不存储实际数据，不会大量占用磁盘空间 💝

  • 💝 它只包含映射到基本表的一个select语句，select语句的结果集构成视图所返回的虚拟表 💝

  • 💝 当基本表数据发生变化时，这种变化会自动反映到视图中 💝

2.2 创建视图

👉👉在已存在的数据库表上创建视图，视图可以建立在一个表中，也可以建立在多张表中。

2.2.1 使用Navicat创建

☁️☁️使用Navicat图形工具，创建名为view_users的视图，视图用于查询user4表中用户姓名为Liming的用户信息。
创建视图

2.2.2 使用create view语句创建

☔️☔️使用SQL语句创建视图View，语法格式如下：

CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW  #ALGORITHM可选参数，表示视图选择的算法，UNDEFINED是创建视图时未指定显式算法的默认算法。
视图名[列名]
AS 
select_statement  #表示视图定义的select语句。
[WITH [CASCADED|LOCAL] CHECK OPTION]  #可选参数，表示更新视图时要保证在该视图的权限范围之内。

☀️☀️首先，创建名为view_uob的视图，用来显示所有用户购买的商品信息，包括商品名称name，商品价格price，商品数量quantity。SQL代码如下：

mysql> create view view_uob(用户姓名,商品名称,商品价格,商品数量)
    -> as select u.name,b.name,price,quantity
    -> from orders o join user u on o.uid=u.uid
    -> join goods b on o.gid=b.gid;
Query OK, 0 rows affected

⛄️⛄️生成的视图界面如下：
创建视图2
🌀🌀可以使用select语句查看该视图关联的数据结果，SQL代码如下：

mysql> select * from view_uob;

🌼🌼执行结果如下：
创建视图3
注：
a. 当使用with check option子句创建视图时，MySQL会通过视图检查正在更改的每个行，以使其符合视图的where子句设置的条件；
b. 如果不符合视图的where子句设置的条件，数据库服务器将会拒绝该操作，并显示错误；
c. MySQL允许基于另一个视图创建视图，它会检查依赖视图中的规则以保持一致性；
d. 为了确定检查的范围，MySQL提供了local、cascaded两个参数，默认为cascaded；
e. 这种创建方式比较严格，可以保证数据库的安全性。

2.3 查看视图

👉👉查看数据库中已存在视图的定义。查看视图必须要有show view的权限，查看视图包括show table status语句、desc语句、show create view语句和查询information_schema数据库下的views表等。

🎃🎃查看数据库已存在视图的信息，SQL代码如下：

mysql> show table status like 'view_uob';
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| view_uob | NULL   | NULL    | NULL       | NULL | NULL           | NULL        | NULL            | NULL         | NULL      | NULL           | NULL        | NULL        | NULL       | NULL      | NULL     | NULL           | VIEW    |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
1 row in set

🎅🎅查看视图的结构，SQL代码如下：

mysql> desc view_uob;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| 用户姓名 | varchar(30) | NO   |     | NULL    |       |
| 商品名称 | varchar(32) | NO   |     | NULL    |       |
| 商品价格 | int(20)     | YES  |     | NULL    |       |
| 商品数量 | int(30)     | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

👻👻查看视图的详细定义，SQL代码如下：

mysql> show create view view_uob;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View     | Create View                                                                                                                                                                                                                                                                                                                  | character_set_client | collation_connection |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_uob | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_uob` AS select `u`.`name` AS `用户姓名`,`b`.`name` AS `商品名称`,`b`.`price` AS `商品价格`,`o`.`quantity` AS `商品数量` from ((`orders` `o` join `user` `u` on((`o`.`uid` = `u`.`uid`))) join `goods` `b` on((`o`.`gid` = `b`.`gid`))) | utf8                 | utf8_general_ci      |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set

🎄🎄查看所有视图的详细信息，SQL代码如下：

mysql> select * from information_schema.views;

注：
a. 从视图的信息中显示，存储引擎、数据长度等信息都为NULL，说明视图是虚拟表；
b. 从视图的结构中显示了视图的字段定义，字段的数据类型，值是否为空，是否为主/外键，默认值和其他信息；
c. 从视图的详细定义中显示了视图的各个属性和字符编码等信息，如果使用了with check option子句它也会显示；
d. information_schema.views表示information_schema数据库下面的views表。

2.4 修改视图

👉👉修改数据库中已存在视图的定义，修改视图包括create or replace语句和alter view语句。

2.4.1 使用create or replace view语句修改

🎁🎁修改名为view_users的视图，用于查询user表中所有用户的用户姓名name和电话telephone。SQL代码如下：

mysql> create or replace view view_users(用户姓名,电话)
    -> as
    -> select name,telephone
    -> from user;
Query OK, 0 rows affected
mysql> select * from view_users;
+----------+-------------+
| 用户姓名 | 电话        |
+----------+-------------+
| 小文     | 18866899966 |
| 张三     | 15286947945 |
| 李四     | 19856245232 |
| 王麻子   | 17932036662 |
| 大胖     | 18782659898 |
+----------+-------------+
5 rows in set
2.4.2 使用alter view语句修改

🔔🔔修改名为view_users的视图，用于查询user表中所有用户的用户姓名name和年龄age。SQL代码如下：

mysql> alter view view_users(用户姓名,年龄)
    -> as
    -> select name,year(now())-year(birthday) as age
    -> from user;
Query OK, 0 rows affected
mysql> select * from view_users;
+----------+------+
| 用户姓名 | 年龄 |
+----------+------+
| 小文     |   23 |
| 张三     |   32 |
| 李四     |   33 |
| 王麻子   |   27 |
| 大胖     |   24 |
+----------+------+
5 rows in set

2.5 更新视图

👉👉通过视图来insert、update、delete表中的数据，视图是一个虚拟表，其中没有数据。通过视图更新时，都是转换到基本表来更新。更新视图时，只能更新权限范围内的数据，超出范围就不能更新。

🎊🎊首先，创建名为view_user4的视图，查询用户uid=10001的用户姓名name，性别sex，年龄age。SQL代码如下：

mysql> create view view_user4(用户姓名,性别,年龄)
    -> as
    -> select name,sex,age from user4 where uid=10001;
Query OK, 0 rows affected
mysql> select * from view_user4;
+----------+------+------+
| 用户姓名 | 性别 | 年龄 |
+----------+------+------+
| Liming   | 男   |   23 |
+----------+------+------+
1 row in set

🍏🍏向view_user4视图中更新一条记录，新记录的用户姓名为Michael，性别为男，年龄为27。SQL语句如下：

mysql> update view_user4 set 用户姓名='Michael',性别='男',年龄=27;  #等价于“update user4 set name='Michael',sex='男',age=27 where uid=10001;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from view_user4;
+----------+------+------+
| 用户姓名 | 性别 | 年龄 |
+----------+------+------+
| Michael  | 男   |   27 |
+----------+------+------+
1 row in set

🍎🍎查询一下user4表中的所有记录，结果显示用户uid=10001的记录已经更新成Michael。SQL代码如下：

mysql> select * from user4;
+-------+----------+-----+-----+
| uid   | name     | sex | age |
+-------+----------+-----+-----+
| 10001 | Michael  | 男  |  27 |
| 10002 | Kangkang | 男  |  30 |
| 10003 | Mary     | 女  |  25 |
+-------+----------+-----+-----+
3 rows in set

注：有以下8种情况视图是不能更新的。
a. 视图中包含sum()、count()、max()和min()等聚合函数；
b. 视图中包含union、union all、distinct、group by和having等关键字；
c. 视图为常量视图，如使用 create view view_string as select ‘Kangkang’ as name; 语句创建的视图；
d. 视图中的select语句包含子查询；
e. 由不可更新的视图导出的视图，如使用 create view view_cope as select * from view_string; 语句创建的视图；
f. 视图选择的algotithm算法为temptable类型，即临时表类型；
g. 视图对应的表上存在没有默认值的列，而且该列没有包含在视图里，如表中包含的name字段没有默认值，视图中也不包括该字段这种情况；
h. 使用了with check option子句的视图，当指明参数为local时不满足该视图本身定义的条件的时候，以及参数为cascaded时不满足所有相关视图和表的条件的时候。

2.6 删除视图

👉👉删除数据库中已存在的视图。删除视图时，只会删除视图的定义，不会删除数据。

🍒🍒删除视图view_uob，SQL代码如下：

mysql> drop view view_uob;
Query OK, 0 rows affected

🍑🍑查看用户root是否具有drop权限，SQL语句如下：

mysql> select drop_priv from mysql.user where user='root';  #drop_priv属性表示用户是否具有drop权限，结果Y表示拥有drop权限，N表示没有。
+-----------+
| drop_priv |
+-----------+
| Y         |
| Y         |
| Y         |
+-----------+
3 rows in set

注：删除并重新创建视图和修改视图的效果不一样，修改视图语句不影响分配的权限，而删除再重新创建视图的话则需要重新分配权限。

3. 查询优化

👉👉数据查询是应用系统中最频繁的操作，当要访问的数据量很大时，查询不可避免地需要筛选大量的数据，造成查询性能低下。为了提高查询的速度，需要对查询语句进行必要的优化。

3.1 子查询优化

👉👉子查询时，MySQL需要为内查询语句的查询结果建立一个临时表，外层查询语句在临时表中查询记录，查询完后还需撤销这些临时表，造成查询性能低下。通常情况下，MySQL将连接查询替代子查询以及结合索引来优化子查询。

🍉🍉查询已购物的用户信息，包括用户姓名name、生日birthday、电话telephone。采用子查询的方式，SQL代码如下：

mysql> select name,birthday,telephone
    -> from user
    -> where uid in (select uid from orders);
+--------+---------------------+-------------+
| name   | birthday            | telephone   |
+--------+---------------------+-------------+
| 小文   | 1999-06-01 00:00:00 | 18866899966 |
| 张三   | 1990-11-15 00:00:00 | 15286947945 |
| 李四   | 1989-08-24 00:00:00 | 19856245232 |
| 王麻子 | 1995-09-15 00:00:00 | 17932036662 |
| 大胖   | 1998-08-10 00:00:00 | 18782659898 |
+--------+---------------------+-------------+
5 rows in set

🍭🍭使用explain语句分析子查询的执行计划，执行结果如下：
子查询
🍓🍓使用连接查询替代子查询，SQL代码如下：

select name,birthday,telephone 
from user u join orders o 
on u.uid=o.oid;

🌽🌽使用explain语句分析连接查询的执行计划，执行结果如下：
连接查询
注：使用连接查询比子查询在索引使用情况、执行时间上都有了明显提升。在数据量很大的情况下，这种性能的提升更为有效。

3.2 limit查询优化

👉👉limit子句主要用来强制select语句返回指定的记录数。由于使用limit子句查询数据时存在缺陷，并且采用的全表扫描导致效率低，所以查询可以采取查询优化法或索引覆盖法来优化limit查询语句。

3.2.1 查询优化法

🍌🍌查询goods表中7条记录中的后5条记录。采用limit子句查询的方式，SQL代码如下：

mysql> select * from goods;
+-------+------+--------+-------+------+
| gid   | code | name   | price | city |
+-------+------+--------+-------+------+
| 20000 |  101 | 坚果   |   100 | 重庆 |
| 20001 |  102 | 汉服   |   200 | 上海 |
| 20002 |  103 | 空调   |  2000 | 深圳 |
| 20003 |  101 | 薯片   |   150 | 重庆 |
| 20004 |  102 | 羽绒服 |   300 | 浙江 |
| 20005 |  101 | 辣条   |    50 | 重庆 |
| 20006 |  103 | 冰箱   | NULL  | 深圳 |
+-------+------+--------+-------+------+
7 rows in set
mysql> select * from goods
    -> limit 2,5;
+-------+------+--------+-------+------+
| gid   | code | name   | price | city |
+-------+------+--------+-------+------+
| 20002 |  103 | 空调   |  2000 | 深圳 |
| 20003 |  101 | 薯片   |   150 | 重庆 |
| 20004 |  102 | 羽绒服 |   300 | 浙江 |
| 20005 |  101 | 辣条   |    50 | 重庆 |
| 20006 |  103 | 冰箱   | NULL  | 深圳 |
+-------+------+--------+-------+------+
5 rows in set

🍐🍐使用explain语句分析limit子句查询的执行计划，执行结果如下：
limit子句
🍰🍰采用查询优化法，先根据主键聚集索引列查询到目标记录的第1条，再选取其后的5条记录即可。SQL代码如下：

select * from goods
where gid>=(select gid from goods limit 2,1)
limit 5;

🍦🍦使用explain语句分析查询优化法的执行计划，执行结果如下：
查询优化法
注：从优化后的执行计划中看出，查询优化器在子查询中采用索引查找，主查询采用有限制的索引查询range，扫描数据行为2行。

3.2.2 索引覆盖法

🍬🍬查询user4表中的用户信息，包括用户uid，用户姓名name，按用户姓名升序排序后，返回6条记录中的后5条记录。SQL代码如下：

mysql> select * from user4;
+-------+----------+-----+-----+
| uid   | name     | sex | age |
+-------+----------+-----+-----+
| 10001 | Michael  | 男  |  27 |
| 10002 | Kangkang | 男  |  30 |
| 10003 | Mary     | 女  |  25 |
| 10004 | Liming   | 男  |  23 |
| 10005 | Tom      | 女  |  24 |
| 10006 | Sally    | 女  |  26 |
+-------+----------+-----+-----+
6 rows in set
mysql> select uid,name from user4
    -> order by name
    -> limit 1,5;
+-------+---------+
| uid   | name    |
+-------+---------+
| 10004 | Liming  |
| 10003 | Mary    |
| 10001 | Michael |
| 10006 | Sally   |
| 10005 | Tom     |
+-------+---------+
5 rows in set

🍟🍟使用explain语句分析limit子句查询的执行计划，执行结果如下：
limit子句2
🍔🍔该查询需要对name排序，因此在name列上建立索引可以有效的提高查询性能。SQL代码如下：

alter table user4
add index index_name using btree(name asc);

🍹🍹使用explain语句分析索引覆盖法的执行计划，执行结果如下：
索引覆盖法
注：覆盖索引法是查询列要被所建立的索引覆盖，索引的字段不仅包含查询的列，还包含查询条件和排序等。建立了索引后的查询语句的执行效率要高于未建立索引的语句。

3.3 group by优化

👉👉group by子句用于对查询结果按照指定的字段进行分类统计。group by子句会尽可能的读取满足条件的索引键完成分类统计操作，如果没有合适的索引可用，先会进行全表扫描并创建一个新的临时表，然后按照group by指定的字段进行排序，在创建的临时表中每个组的所有记录应为连续的，最后使用该临时表来找到组并执行聚合函数。由于group by子句使用临时表实现分组统计操作，导致查询性能低下，所以通常采用只查索引的方法来提高查询性能。

⭐️⭐️查询所有用户购买的商品总数量sumtotal，SQL代码如下：

mysql> select name,sum(quantity) as sumtotal
    -> from orders join user using(uid)
    -> group by uid;
+--------+----------+
| name   | sumtotal |
+--------+----------+
| 小文   | 1        |
| 张三   | 21       |
| 李四   | 142      |
| 王麻子 | 8        |
| 大胖   | 12       |
+--------+----------+

🌟🌟使用explain语句分析group by子句的执行计划，执行结果如下：
group by子句
✨✨将group by子句采用只查索引的方法对该语句进行优化，SQL代码如下：

select name,sumtotal
from user join
(select uid,sum(quantity) as sumtotal from orders group by uid) as b using(uid);

💫💫使用explain语句分析group by子句采用只查询索引方法的执行计划，执行结果如下：
group by子句2
注：
a. group by子句使用只查询索引的方法有效的消除了临时表的使用；
b. 尽量不要对大结果集进行group by操作，因为要考虑到数据量和临时表的大小，会严重影响查询性能；
c. 如果在做多表查询时报错 1248 - Every derived table must have its own alias ，是由于派生表没有加上自己的别名。

✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨

——>以上内容是关于SQL语句查询优化的基础知识，希望对初学者或再次学习者有所帮助，基础打扎实，不怕风吹雨打！ 如果以上内容有错误或者内容不全，望大家提出！我也会继续写好每一篇博文！
👍👍👍

待续未完
——文优
🙊🙊🙊

欢迎观看和提问！！！
👏👏👏

文优
关注
专栏目录
mysql如何进行优化入门篇
05-11
这是MySQL性能优化的关键，因为优化器会基于表统计信息和索引来决定查询的执行路径。 6. 执行计划传递给查询执行引擎，由存储引擎实际执行。 影响查询性能的主要因素是磁盘I/O，因为数据的读取速度取决于硬盘的机械...
MySQL常用30种SQL查询语句优化方法
竹林幽深
10-24 1922
本文介绍了常用的30种MySQL SQL查询优化方法，并通过实际案例演示了它们的应用。优化SQL查询是提高数据库性能和响应速度的关键，通过合理优化SQL查询可以减少数据库的负载，提高查询效率。读者可以根据自己的实际情况和业务需求，选择合适的优化方法，提高数据库的性能和可用性。同时，我们也要不断学习和实践，在数据库领域不断提高自己的知识水平和技术能力，成为一名优秀的数据库工程师。
sql 优化，提高查询速度
最新发布
我哎GIS博客
08-14 1万+
在系统开发中，SQL查询的优化是提高应用性能和响应速度的关键。SQL查询的优化都是相对的，要根据具体业务和库表数据量的大小选择合适的优化方案。
Mysql sql优化
weixin_42189467的博客
10-26 3904
Mysql sql优化
MySQL数据库优化：SQL优化
weixin_41645232的博客
12-15 2211
SQL优化步骤： 在应用开发过程中，由于初期数据量较小，或者赶进度等原因，开发人员对于SQL更偏向于功能的实现。随着生产数据量的不断增加，SQL开始出现性能问题，对生产的影响也越来越大，这时候SQL性能问题就会成为系统的瓶颈。因此必须对SQL进行优化。 1. 查看SQL执行频率 连接MySQL后，可以通过下面语句来查看数据库中全局或当前连接的增删改查SQL的执行频率： ...
【mysql】SQL优化15种方法
A-Itfuture的博客
03-09 8904
其实，开发项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断SQL对程序的运行效率有多大，故很少针对SQL进行专门的优化， 但是，随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。在实际业务场景中，可能我们真正需要使用的只有其中一两列。查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者cpu。此外，多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。还
MySQL入门与提高实践
05-22
《MySQL入门与提高实践》是一本专为初学者和有一定基础的数据库管理员设计的书籍，旨在帮助读者深入理解和掌握MySQL数据库系统。这本书由资深Linux运维专家"老男孩"撰写，结合丰富的实战经验，以epub电子书的形式...
MySQL入门笔记.docx
08-23
MySQL入门笔记涵盖了MySQL的基本概念、核心结构以及存储引擎的详细解释。MySQL是一个广泛使用的开源关系型数据库管理系统，现由Oracle公司维护。由于其开源、免费和高性能的特点，它已成为许多企业的首选数据库解决...
MySQL入门教程合集以及sql代码应用案例合集.zip
08-11
本合集包含的“MySQL入门教程合集”和“SQL代码应用案例合集”是一套全面的学习资源，旨在帮助初学者快速掌握MySQL的基础知识，并通过实际案例提升SQL编程技能。 首先，让我们来看看MySQL入门教程。这部分教程通常...
mysql入门学习笔记，优化
10-12
这篇入门学习笔记将带你深入了解MySQL的基础知识，以及如何进行性能优化。让我们逐一探讨这些主题。 首先，数据库概述：MySQL是一个开源、免费的数据库系统，基于SQL（结构化查询语言）标准，由Oracle公司维护。它...
【MYSQL优化之道摘抄】mysql常见的SQL优化方法
低音钢琴的专栏
05-17 2437
无论对于开发人员、运维人员或者测试人员，数据库的优化都是绕不开的话题。而在数据库的优化中SQL的优化又是最为关键的一项。再牛逼的服务器硬件也扛不住百万次的oracle硬解析，再快的SSD硬盘和raid，碰到千万级数据的全表扫描也一样会趴窝。这就是SQL优化的重要性，但是也恰恰是最容易被忽视的。SQL优化虽然深邃，但是入门不难，这里整理了以前的一些MYSQL的优化笔记，遵循这些优化原则的话，至少能保证数据库不会被慢SQL跑死
Mysql的sql优化
m0_37742400的博客
02-03 1129
主要讲述了查询优化，针对where 、group by 、order by基本原理，以及根据后面的字段如何建立合适的索引
Mysql调优-sql查询
田田田的博客
04-23 174
sql查询调优
SQL优化15个技巧
weixin_42293660的博客
05-16 5352
因为ids太多，即使能快速查出数据，但如果返回的数据量太大了，网络传输也是非常消耗性能的，接口性能始终好不到哪里去。然后在代码中，获取第一个元素的数据，即首单的数据，就能获取首单时间。程序先运行在嵌套在最内层的语句，再运行外层的语句。如果实现业务场景中需要查询出另外几张表中的数据，可以在a、b、c表中冗余专门的字段，比如：在表a中冗余d_name字段，保存需要查询出的数据。除非是有些特殊的场景，比如union all之后，结果集中出现了重复数据，而业务场景中是不允许产生重复数据的，这时可以使用union。
聊聊sql优化的15个小技巧
lisu061714112的专栏
11-10 8791
前言 sql优化是一个大家都比较关注的热门话题，无论你在面试，还是工作中，都很有可能会遇到。 如果某天你负责的某个线上接口，出现了性能问题，需要做优化。那么你首先想到的很有可能是优化sql语句，因为它的改造成本相对于代码来说也要小得多。 那么，如何优化sql语句呢？ ​ 我的个人技术博客：https://susan.net.cn​​​​​​​ ​ 这篇文章从15个方面，分享了sql优化的一些小技巧，希望对你有所帮助。 最近无意间获得一份BAT大厂大佬写的刷题笔记，一下子打通了我的任督二脉，越来越觉得算法没
Sql查询优化
技术分享博客
06-30 4725
查询优化 1.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 2.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id f
SQL优化的15个小技巧，纯干货分享！
热门推荐
黄团团的个人博客
07-10 1万+
普通索引最基本的索引，没有任何限制唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值主索引即主键索引，关键字PRIMARY复合索引为了更多的提高MySQL效率可建立组合索引，遵循“最左前缀”原则全文索引可用于MyISAM表，MySQL5.6之后也可用于innodb表，用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时和空间以上就是目前我对SQL优化的一些个人理解和总结，如果遗漏，欢迎评论区补充说明！
评论 10
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

文优

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

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

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

打赏作者

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

抵扣说明：

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

余额充值