网站建设定制单,电商网站前端模板,品牌定位策略,网站服务器租用你的知识宝库文章目录 第一节、索引失效案例1.1 数据准备1.2 全值匹配我最爱1.3 最佳左前缀法则 第一节、索引失效案例
可以从以下维度对数据库进行优化#xff1a;
索引失效、没有充分利用到索引–索引建立关联查询太多JOIN (设计缺陷或不得已的需求)–SQL优化服务器调优及各个参数设置… 文章目录 第一节、索引失效案例1.1 数据准备1.2 全值匹配我最爱1.3 最佳左前缀法则 第一节、索引失效案例
可以从以下维度对数据库进行优化
索引失效、没有充分利用到索引–索引建立关联查询太多JOIN (设计缺陷或不得已的需求)–SQL优化服务器调优及各个参数设置(缓冲、线程数等–调整my.cnf数据过多–分库分表
虽然SQL查询优化的技术有很多但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。
物理查询优化是通过索引和表连接方式等技术来进行优化这里重点需要掌握索引的使用。逻辑查询优化就是通过SQL等价变换提升查询效率直白一点就是说换一种查询写法执行效率可能更高。
其实用不用索引最终都是优化器说了算。优化器是基于cost开销通过JSON格式可以看到开销数据的它不是基于规则也不是基于语义。怎么样开销小就怎么来。另外SQL语句是否使用索引跟数据库版本、数据量、数据选择度都有关系。 EXPLAIN可以输出四种格式 传统格式 JSON格式 TREE格式 以及可视化输出 。用户可以根据需要选择适用于自己的格式。 1.1 数据准备
创建两个表通过函数和存储过程填充数据。
CREATE TABLE class (
id INT(11) NOT NULL AUTO_INCREMENT,
className VARCHAR(30) DEFAULT NULL,
address VARCHAR(40) DEFAULT NULL,
monitor INT NULL ,
PRIMARY KEY (id)
) ENGINEINNODB AUTO_INCREMENT1 DEFAULT CHARSETutf8;CREATE TABLE student (
id INT(11) NOT NULL AUTO_INCREMENT,
stuno INT NOT NULL ,
name VARCHAR(20) DEFAULT NULL,
age INT(3) DEFAULT NULL,
classId INT(11) DEFAULT NULL,
PRIMARY KEY (id)
#CONSTRAINT fk_class_id FOREIGN KEY (classId) REFERENCES t_class (id)
) ENGINEINNODB AUTO_INCREMENT1 DEFAULT CHARSETutf8;1.2 全值匹配我最爱
// 查询语句
SELECT SQL_NO_CACHE * FROM student WHERE age30 AND classid4 AND NAME abcd;// 分别建立不同的索引
CREATE INDEX idx_age ON student (age) ; // 索引1
CREATE INDEX idx_age_classid oN student (age,classid); // 索引2
CREATE INDEX idx_age_classid_name ON student (age,classid,NAME); // 索引3当数据量非常大时发现使用索引3的执行时间最短。也就是说尽量将WHERE后的字段都建立索引如果有多个建立联合索引。 1.3 最佳左前缀法则
如果索引了多列要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
SELECT SQL_NO_CACHE * FROM student WHERE student.classid1 AND student.name abcd;针对于上面的查询语句虽然已经创建了索引idx_age_classid_name但是WHERE后没有使用age所以无法使用此索引。这一点可以从索引数据结构的角度来解释创建索引idx_age_classid_name时B树首先根据age来排序如果age相同再根据classid如果classid相同再根据name。而WHERE后没有使用age所以无法从此B树获取结果从而无法使用索引。
