sql不等于优化_不懂SQL优化？那你就OUT了索引(2)

1. 创建索引和维护索引要耗费时间&＃xff0c;这种时间随着数据量的增加而增加。 2. 索引需要占物理空间&＃xff0c;除了数据表占数据空间之外&＃xff0c;每一个索引还要占一定的物理空间&＃xff0c;3. 当对表中的数据进行增加、删除和修改的时候&＃xff0c;索引也要动态的维护&＃xff0c;这样就降低了数据的维护速度。

1.在经常需要搜索的列上,可以加快搜索的速度&＃xff1b; 2.在作为主键的列上.3.在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度&＃xff1b; 4.在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的&＃xff1b; 5.在经常需要order by,group by,distinct 列上创建索引,这样查询可以利用索引的排序,加快排序查询时间&＃xff1b; 6.在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

1.对于那些在查询中很少使用的列不应该创建索引。原因&＃xff1a;既然这些列很少使用到,因此有索引或无索引,并不能提高查询速度。相反&＃xff0c;由于增加了索引,反而降低了系统的维护速度和增大了空间需求。 2. 对于那些只有很少数据值的列也不应该增加索引原因&＃xff1a;由于这些列的取值很少, 例如:学生表的性别列&＃xff0c;在查询的结果中&＃xff0c;结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引&＃xff0c;并不能明显加快检索速度。 3.对于那些定义为text, blob数据类型的列不应该增加索引。原因&＃xff1a;这些列的数据量要么相当大,要么取值很少。 4.当修改性能远远大于检索性能时&＃xff0c;不应该创建索引。原因&＃xff1a;修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此当修改性能远远大于检索性能时不应该创建索引。5.单表数据太少&＃xff0c;不适合建索引

CREATE TABLE t_customer_one(customerId INT PRIMARY KEY AUTO_INCREMENT, -- 编号customerName VARCHAR(20), -- 姓名customerAge INT, -- 年龄customerGender CHAR(3), -- 性别customerPhone VARCHAR(29), -- 联系方式customerEmail VARCHAR(30), -- 电子邮件province VARCHAR(20), -- 所在省份city VARCHAR(30), -- 所在城市address VARCHAR(200) -- 详细地址
);

INSERT INTO t_customer_one(customerName,customerAge,customerGender,customerPhone,customerEmail,province,city,address) VALUES(&＃39;张三&＃39;,18,&＃39;女&＃39;,&＃39;15767678798&＃39;,&＃39;123456789&＃64;qq.com&＃39;,&＃39;四川&＃39;,&＃39;成都&＃39;,&＃39;武侯区科华北路88号&＃39;);
INSERT INTO t_customer_one(customerName,customerAge,customerGender,customerPhone,customerEmail,province,city,address) VALUES(&＃39;李四&＃39;,24,&＃39;男&＃39;,&＃39;18767689798&＃39;,&＃39;234567778&＃64;qq.com&＃39;,&＃39;广东&＃39;,&＃39;广州&＃39;,&＃39;白云区天明路188号&＃39;);
INSERT INTO t_customer_one(customerName,customerAge,customerGender,customerPhone,customerEmail,province,city,address) VALUES(&＃39;王五&＃39;,23,&＃39;女&＃39;,&＃39;17167675698&＃39;,&＃39;3456789&＃64;qq.com&＃39; , &＃39;四川&＃39;,&＃39;成都&＃39;,&＃39;武侯区科华北路85号&＃39;);
INSERT INTO t_customer_one(customerName,customerAge,customerGender,customerPhone,customerEmail,province,city,address) VALUES(&＃39;赵六&＃39;,26,&＃39;男&＃39;,&＃39;13767659697&＃39;,&＃39;45678123&＃64;qq.com&＃39;, &＃39;广东&＃39;,&＃39;广州&＃39;,&＃39;白云区天明路180号&＃39;);INSERT INTO t_customer_one(customerName,customerAge,customerGender,customerPhone,customerEmail,province,city,address) SELECT customerName,customerAge,customerGender,customerPhone,customerEmail,province,city,address FROM t_customer_one;

CREATE TABLE t_customer_two(customerId INT PRIMARY KEY AUTO_INCREMENT, -- 编号customerName VARCHAR(20), -- 姓名customerAge INT, -- 年龄customerGender CHAR(3), -- 性别customerPhone VARCHAR(29), -- 联系方式customerEmail VARCHAR(30), -- 电子邮件province VARCHAR(20), -- 所在省份city VARCHAR(30), -- 所在城市address VARCHAR(200) -- 详细地址
)

-- 单列索引ALTER TABLE t_customer_two ADD INDEX idx_name(customerName);ALTER TABLE t_customer_two ADD INDEX idx_age (customerAge);ALTER TABLE t_customer_two ADD INDEX idx_phone(customerPhone);-- 多列索引&＃xff08;联合索引&＃xff09;CREATE INDEX idx_province_city_address ON t_customer_two(province,city,address);

INSERT INTO t_customer_two(customerName,customerAge,customerGender,customerPhone,customerEmail,province,city,address) SELECT customerName,customerAge,customerGender,customerPhone,customerEmail,province,city,address FROM t_customer_one;

最左前缀&＃xff1a; 查询条件中的所有字段需要从最左边列起按顺序出现在多列索引中,查询条件的字段数要 小于,等于多列索引的字段数,中间字段不能存在范围查询的字段 (如<,like等),这样的sql语句可以使用该多列索引。

select * from t_customer_two Where province&＃61;‘xxx’ and city&＃61;‘xxxxx’ and address&＃61;‘XXX’ -- &＃xff08;多列索引有效&＃xff09;
select * from t_customer_two Where province&＃61;‘xxx’ and city&＃61;‘XXX’ -- &＃xff08;多列索引有效&＃xff09;
select * from t_customer_two Where province&＃61;‘xxx’ -- &＃xff08;多列索引有效&＃xff09;
select * from t_customer_two Where city&＃61;‘xxxxx’ and address&＃61;‘XXX’ -- &＃xff08;多列索引无效&＃xff0c;无最左边的province列&＃xff09;
select * from t_customer_two Where and address&＃61;‘XXX’ -- &＃xff08;多列索引无效&＃xff09;

1. 匹配全值,对索引中的所有列都指定具体的值&＃xff08;例如&＃xff1a; province&＃61;‘xxx’ and city&＃61;‘xxx’ and address&＃61;‘xxx’&＃xff09; 2. 匹配最左前缀&＃xff08;例如&＃xff1a; province&＃61;‘xxx’, 使用索引中的第一列&＃xff09; 3. 匹配部分最左前缀&＃xff08;例如&＃xff1a; province&＃61;‘xxx’ and city&＃61;‘xxx’&＃xff09; 4. 匹配第一列范围查询(可用like a%,但不能使用 like %b 或则 like %b%)

1. 在多列索引&＃xff0c;查询条件中用的不是最左边的列&＃xff0c;那么此时是不会使用索引。

2. like查询时 % 出现在第一位也不会使用索引。

3. 条件中有 or 也不会使用索引。

4. 如果 mysql 估计使用全表扫描比使用索引快&＃xff0c;它也不会使用索引。