Mysql学习day3 UNION 和索引

这篇博客主要介绍了MySQL中的子查询、UNION操作及其区别,重点讲解了全文索引的创建、使用和优化,包括在创建表时、通过ALTER TABLE以及CREATE INDEX的方式添加索引,并探讨了索引查询的不同方法,如全文本查询、查询扩展和布尔文本搜索。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1-子查询

所有的子查询都是从内向外处理

2-UNION


  1. 注意这两个是不一样的,UNION自动过滤相同结果,但是UNION ALL不是
select id ,notification_name from notification where id <5
UNION 
select id ,notification_name from notification where id >2 AND id <10

select id ,notification_name from notification where id <5
UNION ALL
select id ,notification_name from notification where id >2 AND id <10

而且如果查询的列完全相同的情况下,可以UNION 不同的表


SELECT id , user_id
from admin_advertiser WHERE   id <10
UNION ALL
SELECT id, user_id  
from admin_agency h where  h.id<15 and h.id >7

3-索引学习


看一个表里面的索引

 show index from table_name;

3.1创建表的时候建立一个全文索引

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
) ENGINE=MyISAM

3.2创建数据

INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');


3.3全文索引测试
—————注意 MATCH (title,body) 里面的值必须是前面建立全文索引的两个字段不能少。
against的意思是反对,靠着,相反,在这里是靠着的意思

SELECT * FROM articles   WHERE MATCH (title,body) AGAINST ('database'); 


3.4创建全文索引


在看完上面的例子之后,难免会问索引是什么,干嘛的,怎么创建的,下面将详细说明
3.4.1创建表的同时创建索引这个就是上面示例,不重复介绍
3.4.2通过alter table的方式来添加
注意括号里面的包括表名都是用 ESC下面的键打出来的,不是英文的单引号,但是表名可以不加

//ft_stu_name是索引名,可以随便起

ALTER TABLE `student` ADD FULLTEXT INDEX ft_stu_name  (`name`)  
或者:
ALTER TABLE `student` ADD FULLTEXT ft_stu_name  (`name`)

3.4.3直接通过create index的方式创建索引

CREATE FULLTEXT INDEX myindex3 ON `te` (`char`)

3.5删除索引
删除索引有两种方法,一个时直接删,另一个时用alter table

DROP INDEX 索引名on 表名
ALTER table 表名 drop index 索引名

3.6索引优化
特别注意:50%的门坎限制(当查询结果很多,几乎所有记录都有,或者极少的数据,都有可能会返回非所期望的结果) 。可用IN BOOLEAN MODE即可以避开50%的限制。于是上面的查询就变成了

SELECT * FROM articles   WHERE MATCH (title,body) AGAINST
 ('database' IN BOOLEAN MODE);

在AGAINST的两个单引号中间可以有多个词,但是里面有一些语法规则

  • +用在词的前面,表示一定要包含该词,而且一定要放在开始位置
 AGAINST  ('  +Apple ')
  • : -不包含该词,所以不能只用「-yoursql」这样是查不到任何row的,必须搭配其他语法使用。
    AGAINST ('-林志玲 +张筱雨')匹配到: 所有不包含林志玲,但包含张筱雨的记录

  • 空(也就是默认情况),表示可选的,包含该词的顺序较高。
    AGAINST('APPLE BANANA')找至少包含上面词中的一个的记录行apple banana


  +apple +juice     两个词均在被包含

  +apple macintosh     包含词 “apple”,但是如果同时包含 “macintosh”,它的排列将更高一些

 +apple -macintosh   包含 “apple” 但不包含 “macintosh”
  • 提高该字的相关性,查询的结果会排在比较靠前的位置。

  • < :降低相关性,查询的结果会排在比较靠后的位置。

  • ( ):可以通过括号来使用字条件。

  • ~ :将其相关性由正转负,表示拥有该字会降低相关性,但不像「-」将之排除,只是排在较后面。

  • eg: +apple ~macintosh 先匹配apple,但如果同时包含macintosh,就排名会靠后。
  • : * 通配符*,这个只能接在字符串后面。
MATCH (girl_name) AGAINST ('+*ABC*')   #错误,不能放前面
MATCH (girl_name) AGAINST ('+张筱雨*')  #正确
  • ” ” :整体匹配,用双引号将一段句子包起来表示要完全相符,不可拆字。
eg:  "tommy huang" 可以匹配  tommy huang xxxxx   但是不能匹配  tommy is huang。

3.7索引查询方法

  • 3.7.1 全文本查询

SELECT * FROM articles   WHERE MATCH (title,body) AGAINST ( 'database'); 
  • 3.7.2使用查询扩展
//查询扩展的结果让返回的行数增加,但是也同样增加了不想要的数据
SELECT * FROM articles   WHERE MATCH (title,body) AGAINST ( 'database' with query expansion); 
  • 3.7.3布尔文本搜索
//布尔搜索不同于其它的全文搜索语法在于,即使没有定义FULLTEXT索引,也可以使用,但是这时一种非常慢的操作,上面的那些规则都是布尔搜索里面的
SELECT * FROM articles   WHERE MATCH (title,body) AGAINST
 ('database  ' in boolean mode ); 
<think>嗯,用户问的是MySQL中“大于等于”查询不走索引的情况以及优化方法。首先,我需要回想一下索引的工作原理。索引通常使用B+树结构,适合范围查询,但有时候即使写了索引,查询还是可能没用到。这可能涉及到索引选择性、数据分布、查询条件的具体情况等因素。 用户提到的是“大于等于”操作符,比如WHERE column >= value。这时候应该考虑索引是否被正确使用。首先,要确认是否有合适的索引在相关列上。如果没有索引,那当然不会走,但如果有的话,可能还有其他原因。 比如,索引选择性低的情况。如果column的值大部分都满足>=某个值,那MySQL可能会认为全表扫描比索引更快。比如状态列,90%的数据都是status >=1,这时候优化器可能跳过索引。这时候可能需要强制使用索引或者调整查询。 还有联合索引的情况,如果查询条件不符合最左前缀原则,比如索引是(a,b,c),但查询只用到了bc,这时候索引可能不会被使用。另外,如果查询中用了函数或者类型转换,比如对列进行运算或者隐式类型转换,也可能导致索引失效。 数据量也是一个因素,小表的时候全表扫描更快,优化器可能不选择索引。另外统计信息不准确的话,优化器可能做出错误的判断,需要重新分析表。 优化方法的话,可以考虑强制使用索引,用FORCE INDEX提示。或者优化查询条件,拆分成多个范围,比如>=改成BETWEEN=的组合。调整索引,比如使用覆盖索引,避免回表,或者创建更适合的索引。定期更新统计信息,避免优化器误判。如果数据分布特殊,可能需要调整查询策略,比如分页查询或者分区表。 不过要注意,强制索引可能有副作用,数据变化后可能不适用。所以需要根据具体情况测试,用EXPLAIN分析执行计划,确认优化效果。总之,要结合表结构、数据分布查询需求来综合优化。</think>### MySQL "大于等于" 不走索引的常见场景及优化思路 #### 一、索引失效的核心原因 $$ \text{索引选择率} = \frac{\text{满足条件的记录数}}{\text{总记录数}} $$ 当选择率超过 **30%** 时,MySQL 优化器可能放弃使用索引。对于 `WHERE column >= value` 这类查询,具体失效场景包括: #### 二、典型场景分析 1. **低选择性场景** ```sql -- 假设 status=1 占全表 90% 数据 EXPLAIN SELECT * FROM orders WHERE status >= 1; ``` - 📉 优化器预判扫描大部分数据 - 💡 优化方案:强制索引 `SELECT * FROM orders FORCE INDEX(idx_status) WHERE status >= 1` 2. **联合索引未命中最左前缀** ```sql -- 索引为 (create_time, amount) SELECT * FROM payments WHERE amount >= 100; ``` - ❌ 无法使用索引的第二列进行范围查询 - 💡 优化方案:调整索引顺序或创建单独索引 3. **隐式类型转换** ```sql -- 假设 phone 是 VARCHAR 类型 SELECT * FROM users WHERE phone >= 13800000000; ``` - ⚠️ 字符串与数字比较时发生隐式转换 - 💡 优化方案:显式转换 `WHERE phone >= &#39;13800000000&#39;` #### 三、优化解决方案 1. **索引覆盖优化** ```sql -- 原索引:idx_age -- 新建覆盖索引:idx_age_name ALTER TABLE employees ADD INDEX idx_age_name(age, name); SELECT name FROM employees WHERE age >= 30; -- 使用覆盖索引 ``` 2. **分段查询策略** ```sql (SELECT * FROM log WHERE create_time >= &#39;2023-01-01&#39; LIMIT 1000) UNION ALL (SELECT * FROM log WHERE create_time >= &#39;2023-02-01&#39; LIMIT 1000) ``` 3. **统计信息维护** ```sql ANALYZE TABLE sales; -- 更新统计信息 ``` 4. **函数索引MySQL 8.0+)** ```sql -- 处理带函数的查询 ALTER TABLE products ADD INDEX idx_price_floor((FLOOR(price))); SELECT * FROM products WHERE FLOOR(price) >= 100; ``` #### 四、诊断工具 使用 `EXPLAIN` 分析执行计划: ```sql EXPLAIN FORMAT=JSON SELECT * FROM inventory WHERE stock >= 50; ``` 重点关注: - `key`: 实际使用的索引 - `rows`: 预估扫描行数 - `Extra`: `Using where; Using index` 最佳 #### 五、特殊场景处理 **时间范围查询优化** ```sql -- 原查询 SELECT * FROM sensor_data WHERE collect_time >= NOW() - INTERVAL 7 DAY; -- 优化方案 CREATE INDEX idx_collect_time ON sensor_data(collect_time); ALTER TABLE sensor_data PARTITION BY RANGE (TO_DAYS(collect_time)) (...); ``` #### 六、注意事项 1. `FORCE INDEX` 需定期评估有效性 2. 索引维护成本:写操作时会增加索引更新开销 3. 冷热数据分离:对历史归档数据使用单独存储策略 通过 `SHOW INDEX FROM table_name` 查看索引基数(Cardinality),结合业务数据分布特征选择最优方案。实际优化时应通过基准测试验证效果。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值