高效查询方法

最新推荐文章于 2024-09-12 01:15:37 发布

金鸡湖最后的张万森

最新推荐文章于 2024-09-12 01:15:37 发布

阅读量1k

点赞数 18

分类专栏：菜菜菊花酱数据分析课程 MySQL 文章标签： mysql 数据分析

本文链接：https://blog.csdn.net/Alex_sdhjkb/article/details/136158965

版权

菜菜菊花酱数据分析课程同时被 2 个专栏收录

13 篇文章 0 订阅

订阅专栏

MySQL

12 篇文章 0 订阅

订阅专栏

为了提高数据的查询速度，最常用的解决方案就是给表中变量创建索引。我们可以将索引理解成书的目录，如果一本书没有目录，那检索起来可能就比较麻烦，一旦有了目录，我们就可以根据目录进行索引，很快地找到我们需要的内容。同样的道理，如果数据表中有了索引，就可以大大提高MySQL的执行效率。

常见索引类型

普通索引

普通索引是一种没有任何约束的索引，它对表中变量的值不做任何的限制，不管变量的值是否存在重复值或缺失值（也就是NULL值），所以普通索引是使用最频繁的一种索引。

可以通过两种方式来建立普通索引：

创建新表时设定某个字段为普通索引

CREATE TABLE <table_name_>( field1 data_type1,
	field2 data_type2,
	field3 data_type3,
	……
	INDEX <index_name>(field1));

基于已有的表添加普通索引

CREATE INDEX <index_name> ON <table_name_>(field_list); -- 基于已有的表创建索引
-- 或者
ALTER TABLE <table_name_> ADD INDEX <index_name> ON(field_list); -- 基于已有表修改索引

例子

以校园一卡通消费数据stu_card为例，查询出交易时间在2013年9月1日的所有记录 (数据来源于菊花酱数据分析)

-- 没有创建索引之前的条件查询
SELECT * FROM stu_card
WHERE custom_date BETWEEN '2013-09-01 00:00:00'
AND '2013-09-01 23:59:59';

# 创建索引
CREATE INDEX date_index ON stu_card(custom_date);

# 执行查询
SELECT * FROM stu_card
WHERE custom_date BETWEEN '2013-09-01 00:00:00'
AND '2013-09-01 23:59:59';

在返回的表格中是没有date_index索引字段的

唯一索引

相对于普通索引来说，唯一索引对字段或者字段组合是有约束的，也就是确保字段或者字段组合的每一个观测值都是唯一的，不能存在重复值。如果字段中还有多个空白字符串，也算是有重复值，因为空字符串代表一种值。

一个表中可以有多个唯一索引，创建唯一索引的方法也有两种，与创建普通索引类似：

在创建新表的时候设置唯一索引；

-- 建表时创建索引
CREATE TABLE <table_name_>( field1 data_type1,
	field2 data_type2,
	field3 data_type3,
	……
	UNIQUE <index_name>(field1,field2));

对已有表添加唯一索引

-- 对已有表添加索引，可以通过创建法或修改法
CREATE UNIQUE INDEX <index_name> ON <table_name_>(field_list); #基于已有的表创建索引
-- 或者
ALTER TABLE <table_name_> ADD UNIQUE <index_name> ON(field_list); #基于已有表修改索引

例子

以某平台的旅游交易数据为例 (数据来源于菊花酱数据分析)

新建数据表tourism_orders

CREATE TABLE tourism_orders(
    userid VARCHAR(20),
    orderid VARCHAR(12),
    orderTime VARCHAR(15),
    orderType VARCHAR(2),
    city VARCHAR(20),
    country VARCHAR(20),
    continent VARCHAR(10));

往表中插入数据

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server8.0/Uploads/tourism_orders.csv'
INTO TABLE tourism_orders
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- 查询数据前几行
SELECT * FROM tourism_orders LIMIT 10;

无索引下的条件查询

SELECT *
FROM tourism_orders
WHERE userid = '100000001445'; -- 约0.016秒

有索引下的条件查询

# 创建两个组合变量的唯一索引
CREATE UNIQUE INDEX id_idx ON tourism_orders (userid, orderid);

# 再次执行查询语句
SELECT *
FROM tourism_orders
WHERE userid = '100000001445'; -- 几乎为0秒

主键索引

主键索引对字段的要求最为严格，必须确保字段中的值既不存在重复值也不存在缺失值。

与普通索引和唯一索引不同的是，一张表中只能有一个主键索引

建表时创建索引

CREATE TABLE <table_name_>( field1 data_type1,
    field2 data_type2,
    field3 data_type3,
    ……
    PRIMARY KEY <index_name>(field1,field2));

对已有表添加索引

ALTER TABLE <table_name_> ADD PRIMARY KEY <index_name>(field_list); #基于已有表修改索引

例子

使用用户注册数据和用户交易数据 ( 数据来源于菊花酱数据分析)

创建用户注册表和RFM表

CREATE TABLE regit_info(
    uid VARCHAR(10),
    gender TINYINT,
    age TINYINT,
    regit_date DATE);
    
CREATE TABLE RFM(
    uid VARCHAR(10),
    R INT,
    F TINYINT,
    M DECIMAL(10,2));

批量导入数据

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server8.0/Uploads/user_regit_RFM/regit_info.csv'
INTO TABLE regit_info
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server8.0/Uploads/user_regit_RFM/RFM.csv'
INTO TABLE RFM
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

内连接完成两表字段的合并

SELECT t1.*,t2.R,t2.F,t2.M
FROM regit_info AS t1
INNER JOIN RFM AS t2 ON t1.uid=t2.uid
LIMIT 10000;

添加主键索引,再次执行查询

-- 添加主键索引
ALTER TABLE regit_info ADD PRIMARY key (uid);
ALTER TABLE RFM ADD PRIMARY key (uid);

-- 再次执行查询
SELECT t1.*,t2.R,t2.F,t2.M
FROM regit_info AS t1
INNER JOIN RFM AS t2 ON t1.uid=t2.uid
LIMIT 10000;

索引的查询

如果想要对表进行操作，通常需要知道:

表是否已经存在索引
- 如果存在这些索引
  - 是什么类型的
  - 名称是什么
  - 设置在哪些字段上
  - 等等

只有了解表中的索引信息，我们才能进一步管理索引

-- 查询索引信息语法
SHOW INDEX FROM <table_name_>;

例子

查询用户注册表regit_info和旅游交易表tourism_orders的索引信息 ( 数据来源于菊花酱数据分析)

SHOW INDEX FROM regit_info;
SHOW INDEX FROM tourism_orders;

删除索引

删除普通索引和唯一索引

DROP INDEX <index_name> ON <table_name_>;
-- 或者
ALTER TABLE <table_name_> DROP INDEX <index_name>;

用于删除主键索引

ALTER TABLE <table_name_> DROP PRIMARY KEY;

例子

删除用户注册表regit_info中的主键索引

ALTER TABLE regit_info DROP PRIMARY KEY; -- 删除索引
SHOW INDEX FROM regit_info; -- 查看索引

删除旅游交易表tourism_orders中的唯一索引

DROP INDEX id_idx ON tourism_orders; -- 删除索引
SHOW INDEX FROM tourism_orders; -- 查看索引

关于索引的注意事项

尽管索引有提速的功能（可以提高响应的select的效率），但是也不能滥用，因为它会降低数据表的写操作速度（insert 和 update的效率会变低），也会占用一定的磁盘空间。

何时创建索引

WHERE 关键词后面的字段创建索引，可以加快条件判断速度
ORDER BY关键词后面的字段创建索引，可以加快排序速度
表连接关键词 ON 后面的字段创建索引，可以加快表连接速度
包含大量NULL的字段不适合创建索引，因为索引不可以包含NULL值
包含大量重复值的字段不适合创建索引，因为基于索引的查询规则，在进行条件筛选的时候可能会产生大量的数据行，此时索引并不能加快数据库搜索过程中的扫描速度

索引无效的情况

WHERE 关键词后面的条件表达式中如果使用IN、OR、!=或者<>，均会导致索引无效
- 解决方法:将“!=”或者“<>”替换为">AND<“，将"IS NOT NULL"替换为”>=CHR(0)"。
  - CHR(0):代表一个空字符（null character）
筛选或排序过程中，如果对索引列使用函数，则索引失效。
筛选过程中，如果字符型字段写成了数值型的数字，则索引失效（比如，用户id字段是字符型，那筛选的时候需要写where uid = “1”）
使用模糊查询的时候，如果将通配符放在开头，则索引失效（like “%aaa%” 不会使用索引而like“aaa%”可以使用索引）
对于多列的组合索引，遵循左原则，例如对字段A,B,C设置索引 INDEX(A,B,C) ，则"A>0" 、“A=1AND B>10”、“A=10 AND B<6 AND C>100"都可以使组合索引有效，但是"B>10”、"B<6 ANDC>100"都会导致组合索引失效。
在JOIN操作中，关键词ON后面的字段类型要保持一致（也就是左表中这个字段的数据类型和右表中同一字段的数据类型要保持一致），否则索引无效。