#建库
CREATE DATABASE IF NOT EXISTS day0321 DEFAULT CHARSET utf8;
USE day0321;
#建表;
CREATE TABLE IF NOT EXISTS staffs
(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年龄',
pos VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
)ENGINE=INNODB CHARSET utf8 COMMENT '员工表';
SELECT * FROM staffs;
#修改表结构创建索引
ALTER TABLE staffs ADD INDEX idx_nap(NAME,age,pos);
#优化1:全值匹配
SELECT * FROM staffs WHERE NAME = 'July' ADD age = '23' AND pos = 'dev';
#优化2:匹配最左列
SELECT * FROM staffs WHERE NAME ='July' AND age='23';
#优化3:模糊查询,匹配前缀
SELECT * FROM staffs WHERE NAME LIKE 'J%';
#优化4:匹配范围
SELECT * FROM stadds WHERE NAME > 'marry';
#优化5:
SELECT * FROM staffs WHERE NAME = 'marry' AND age > 18;
#优化6: 只使用索引字段
SELECT NAME,age,pos FROM staffs WHERE NAME = 'July' AND age = 25 AND pos='dev';
SELECT NAME,age FROM staffs WHERE NAME = 'July' AND age>25;
mySQL索引优化
最新推荐文章于 2024-10-01 18:39:54 发布
本文详细介绍了如何在MySQL中创建、修改表结构并利用索引来提升查询效率,包括全值匹配、最左匹配、模糊查询、范围查询等优化策略。重点讲解了针对员工表staffs的实例,以帮助读者理解数据库管理和SQL优化技巧。
摘要由CSDN通过智能技术生成