MySQL入门到精通—高级功能篇

三月渭城

已于 2024-06-11 17:40:44 修改

阅读量2k

点赞数 30

分类专栏： MySQL 文章标签： mysql 数据库全文检索

于 2024-06-09 23:37:40 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_57499387/article/details/139567612

版权

全文搜索（Full-Text Search）

特点

基本操作

搜索模式

索引创建与使用

索引是用于加速数据检索的数据结构，通常以B树或哈希表的形式实现,帮助MySQL高效获取数据的数据结构。

特点

提高查询速度
确保数据的唯一性
可以加速表和表之间的连接,实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时,可以显著减少分组和排序的时间
全文检索字段进行搜索优化

使用原则

索引不是越多越好，小数据量的表不需要加索引
不要对经常变动的数据增加索引
索引一般加在经常要查询的列上

索引优化

选择合适的列：选择查询频繁且选择性高的列。
避免过多索引：过多的索引会影响写性能，应该权衡查询和写入性能。
使用覆盖索引：尽量使查询可以仅通过索引获取所需数据，而无需访问表。
监控和调整：定期监控查询性能，根据需要添加或删除索引。
通过合理使用索引，可以显著提高数据库的查询性能，但也需要注意索引的维护成本和潜在的负面影响。

使用对象

查询优化

SELECT * FROM users WHERE name ='Alice';

如果 name 列上有索引，查询速度会更快。

排序优化

SELECT * FROM users ORDERBY name;

如果 name 列上有索引，排序速度会更快。

多列查询优化

SELECT * FROM users WHERE name ='Alice'AND email ='alice@example.com';

如果 name 和 email 列上有复合索引，查询速度会更快。

使用 EXPLAIN 分析查询

EXPLAIN SELECT * FROM users WHERE name ='Alice';

输出会显示查询是否使用了索引以及使用了哪个索引。

索引类型

单列索引：基于单列创建的索引。

多列索引：基于多列创建的复合索引。

空间索引：用于处理地理数据的索引。

单列索引 
CREATE INDEX index_name ON table_name (column_name); 
示例：
CREATE INDEX idx_user_name ON users (name); 

多列索引 
CREATE INDEX index_name ON table_name (column1, column2); 
示例： 
CREATE INDEX idx_user_name_email ON users (name, email); 

空间索引 
CREATE SPATIAL INDEX index_name ON table_name (spatial_column); 
示例： 
CREATE SPATIAL INDEX idx_spatial_location ON locations (coordinates); 


查看索引 
SHOW INDEX FROM table_name; 
示例： 
SHOW INDEX FROM users; 

删除索引 
DROP INDEX index_name ON table_name; 
示例： 
DROP INDEX idx_user_name ON users; 

重新构建索引 
ALTER TABLE table_name DROP INDEX index_name, ADD INDEX index_name (column_name); 
示例： 
ALTER TABLE users DROP INDEX idx_user_name, ADD INDEX idx_user_name (name);

主键索引（Primary Key Index）

每个表只能有一个主键索引
主键索引是唯一的，并且不允许为空值
自动创建在声明主键时

唯一索引（Unique Index）

确保索引列的值是唯一的。
可以有多个唯一索引。
允许一个 NULL 值。

普通索引（Normal Index）

仅用于加速数据访问，没有唯一性约束。
可以在表的任何列上创建。

复合索引（Composite Index）

基于多个列创建的索引。
加速多列组合查询。

全文索引（Full-text Index）

用于加速对文本数据的搜索。
常用于搜索引擎或需要全文检索的应用。

哈希索引（Hash Index）

基于哈希表实现的索引。
适用于等值查询，但不适合范围查询。

添加索引的优点

加速查询：索引可以显著提高 SELECT 语句的查询速度。
保证唯一性：唯一索引可以确保数据的唯一性。
加速排序和分组：ORDER BY 和 GROUP BY 操作可以通过索引加速。
提高联接性能：在联接操作中，可以快速定位关联表中的数据。

添加索引的缺点

占用空间：索引需要额外的存储空间，尤其是对于大型表。
降低写性能：INSERT、UPDATE和DELETE操作需要维护索引。
复杂性增加：管理和维护索引需要额外的工作。

索引的使用场景

查询频繁的列：对经常出现在 WHERE 子句中的列创建索引。
联接列：对经常用于表联接的列创建索引。
排序和分组列：对经常用于 ORDER BY 和 GROUP BY 的列创建索引。
唯一性约束：对需要唯一性的列创建唯一索引。

基本操作

-- 创建某个索引

-- 删除某个索引

-- 测试某个索引

视图创建与管理

定义

视图是基于 SQL 查询的虚拟表，可以简化复杂查询、增强数据安全性和提高数据管理的灵活性。

视图不存储数据，只存储查询逻辑。

特点

简化复杂查询：通过视图，可以将复杂的 SQL 查询封装成一个简单的查询。
增强安全性：通过视图，可以限制用户只能访问特定的数据。
数据抽象：视图提供了对底层表的抽象，使得数据库结构可以随时间变化而不会影响用户的查询。

基本操作

创建视图

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

示例：

假设有一个 users 表，包含以下字段：id、name、email、status。

创建一个视图，选择所有活跃用户的信息：

CREATE VIEW active_users AS SELECT id, name, email FROM users WHERE status = 'active';

通过视图查询活跃用户：

SELECT * FROM active_users;

视图可以使用 CREATE OR REPLACE VIEW 语句进行更新。

CREATE OR REPLACE VIEW view_name AS SELECT new_column1, new_column2, ... FROM new_table_name WHERE new_condition;

示例：

更新 active_users 视图，包含 status 字段：

CREATE OR REPLACE VIEW active_users AS SELECT id, name, email, status FROM users WHERE status = 'active';

删除视图

DROP VIEW view_name;

示例：

删除 active_users 视图：

DROP VIEW active_users;

复杂视图示例

假设有两个表 orders 和 order_details，创建一个视图显示每个订单的详细信息。

创建视图：

CREATE VIEW order_summary AS SELECT o.order_id, o.user_id, o.order_date, d.product_id, d.quantity, d.price, (d.quantity * d.price) AS total_price FROM orders o JOIN order_details d ON o.order_id = d.order_id;

查询视图，获取订单摘要信息：

SELECT * FROM order_summary;

视图权限

控制用户对视图的访问权限，可以限制用户只能访问视图而不能直接访问底层表。

授予权限：

GRANT SELECT ON view_name TO 'username'@'hostname';

示例：

GRANT SELECT ON active_users TO 'user'@'localhost';

视图更新

最低0.47元/天解锁文章