SQL学习笔记系列(九)索引优化分析
SQL性能下降
数据过多
分库分表
关联过多表
SQL优化
没有充分利用索引
索引建立
服务器调优及各个参数设置
调整my.cnf
SQL预热:常见通用的Join查询
实际工作经验:通常用左外连接,很少用右外连接
建表SQL
CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);
七大join
1.所有有门派的人员信息
( A、B两表共有)
select *
from t_emp a
inner join t_dept b
on a.deptId = b.id;
2.列出所有用户,并显示其机构信息
(A的全集)
select *
from t_emp a
left join t_dept b
on a.deptId = b.id;
3.列出所有门派
(B的全集)
select *
from t_dept b
4.所有不入门派的人员
(A的独有)
select *
from t_emp a
left join t_dept b
on a.deptId = b.id
where b.id is null;
5.所有没人入的门派
(B的独有)
select *
from t_dept b
left join t_emp a
on a.deptId = b.id
where a.deptId is null;
6.列出所有人员和机构的对照关系
(AB全有)
#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
SELECT *
FROM t_emp A
LEFT JOIN t_dept B
ON A.deptId = B.id
UNION #联合两个表用UNION
SELECT *
FROM t_emp A
RIGHT JOIN t_dept B
ON A.deptId = B.id
7 列出所有没入派的人员和没人入的门派
(A的独有+B的独有)
SELECT *
FROM t_emp
A LEFT JOIN t_dept B
ON A.deptId = B.id
WHERE B.`id` IS NULL
UNION
SELECT *
FROM t_emp A
RIGHT JOIN t_dept B
ON A.deptId = B.id
WHERE A.`deptId` IS NULL;
索引简介
索引定义
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
可以得到索引的本质:索引是数据结构。
简单理解为“排好序的快速查找数据结构”。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上
-
优势:类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本;通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
-
劣势:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息;实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的
mysl索引结构
BTree索引
B+Tree索引
B树和B+树的区别
1)B-树的关键字和记录是放在一起的,叶子节点可以看作外部节点,不包含任何信息;B+树的非叶子节点中只有关键字和指向下一个节点的索引,记录只放在叶子节点中。
2)在B-树中,越靠近根节点的记录查找时间越快,只要找到关键字即可确定记录的存在;而B+树中每个记录的查找时间基本是一样的,都需要从根节点走到叶子节点,而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好,而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据,这样每个节点可容纳的元素个数比B-树多,树高比B-树小,这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多,但是一次磁盘访问的时间相当于成百上千次内存比较的时间,因此实际中B+树的性能可能还会好些,而且B+树的叶子节点使用指针连接在一起,方便顺序遍历(例如查看一个目录下的所有文件,一个表中的所有记录等),这也是很多数据库和文件系统使用B+树的缘故。
B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引
- B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
- B+树的查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。
聚簇索引与非聚簇索引
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。
术语‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。
如下图,左侧的索引就是聚簇索引,因为数据行在磁盘的排列和索引排序保持一致。
-
聚簇索引的好处:
按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不不用从多个数据块中提取数据,所以节省了大量的io操作。 -
聚簇索引的限制:
对于mysql数据库目前只有innodb数据引擎支持聚簇索引,而Myisam并不支持聚簇索引。
由于数据物理存储排序方式只能有一种,所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。
为了充分利用聚簇索引的聚簇的特性,所以innodb表的主键列尽量选用有序的顺序id,而不建议用无序的id,比如uuid这种。
mysql索引分类
基本语法
#创建
CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))
#建立索引的命名规则都是以index为命名开头
#删除
DROP INDEX [indexName] ON mytable;
#查看
SHOW INDEX FROM table_name\G
#使用ALTER命令(一般用上面的方法即可)
#有四种方式来添加数据表的索引:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
#该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
#这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTER TABLE tbl_name ADD INDEX index_name (column_list)
#添加普通索引,索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
#该语句指定了索引为 FULLTEXT ,用于全文索引。
单值索引
即一个索引只包含单个列,一个表可以有多个单列索引
#随表一起建索引:
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)
);
#单独建单值索引:
CREATE INDEX idx_customer_name
ON customer(customer_name);
#删除索引:
DROP INDEX idx_customer_name
ON customer;
唯一索引
索引列的值必须唯一,但允许有空值
#随表一起建索引:
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_no)
);
#单独建唯一索引:
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); #与单值索引不同的是多了个UNIQUE
#删除索引:
DROP INDEX idx_customer_no on customer ;
主键索引
设定为主键后数据库会自动建立索引,innodb为聚簇索引
#随表一起建索引:
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id)
);
CREATE TABLE customer2 (id INT(10) UNSIGNED ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id)
);
#单独建主键索引:
ALTER TABLE customer
add PRIMARY KEY customer(customer_no);
#删除建主键索引:
ALTER TABLE customer
drop PRIMARY KEY ;
#修改建主键索引:
必须先删除掉(drop)原索引,再新建(add)索引
存数据一般这个表的主键不会变
复合索引
即一个索引包含多个列
#随表一起建索引:
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_name),
KEY (customer_no,customer_name)
);
#单独建索引:
CREATE INDEX idx_no_name ON customer(customer_no,customer_name); #括号内增加多个
#删除索引:
DROP INDEX idx_no_name on customer ;
哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段,外键关系建立索引
- 单键/组合索引的选择问题, 组合索引性价比更高
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
哪些情况不需要创建索引
- 表记录太少
- 经常增删改的表或者字段
- Where条件里用不到的字段不创建索引
- 过滤性不好的不适合建索引
性能分析
Explain是什么(查看执行计划)
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
Explain作用
- 表的读取顺序
- 哪些索引可以使用
- 数据读取操作的操作类型
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被物理查询
Explain如何使用
- Explain + SQL语句
- 执行计划包含的信息
各字段解释
id
select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
- id相同,执行顺序由上至下
- id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
- id既有相同也有不同:先执行不同,再执行相同
关注点:id号每个号码,表示一趟独立的查询。一个sql 的查询趟数越少越好。
select_type
查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询
- SIMPLE:简单的 select查询,查询中不包含子查询或者UNION
- PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为Primary(主要)
- DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询, 把结果放在临时表里。
- SUBQUERY:在SELECT或WHERE列表中包含了子查询
- DEPENDENT SUBQUERY:在SELECT或WHERE列表中包含了子查询,子查询基于外层(依赖子查询,有in)
- UNCACHEABLE SUBQUREY(不可用缓存):有系统变量
- UNION:若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED
- UNION RESULT:从UNION表获取结果的SELECT
table
显示这一行的数据是关于哪张表的(表名)
partitions
代表分区表中的命中情况,非分区表,该项为null
type(对于优化非常重要)
访问类型排列:
type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system>const>eq_ref>ref>range>index>ALL
一般来说,得保证查询至少达到range级别,最好能达到ref。
- system:表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计
- const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快
如将主键置于where列表中,MySQL就能将该查询转换为一个常量 - eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
- ref:非唯一性索引扫描,返回匹配某个单独值的所有行.本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
- range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引.一般就是在你的where语句中出现了between、<、>、in等的查询.这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。(效果不是很好)
- index:出现index是sql使用了索引但是没用通过索引进行过滤,一般是使用了覆盖索引或者是利用索引进行了排序分组(优化效果低)
- all:Full Table Scan,将遍历全表以找到匹配的行,如果见到all,就要建立索引了
- index_merge:在查询过程中需要多个索引组合使用,通常出现在有 or 的关键字的sql中
- ref_or_null:对于某个字段既需要关联条件,也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。
- index_subquery:利用索引来关联子查询,不再全表扫描。
- unique_subquery :该联接类型类似于index_subquery。 子查询中的唯一索引
possible_keys
- 显示可能应用在这张表中的索引,一个或多个。
- 查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
key
- 实际使用的索引。如果为NULL,则没有使用索引
- 查询中若使用了覆盖索引,则该索引和查询的select字段重叠
key_len
- 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。
- key_len字段能够帮你检查是否充分的利用上了索引
- where 后面的筛选条件命中索引的长度
- 命中索引越长,效率越高
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值
rows
- rows列显示MySQL认为它执行查询时必须检查的行数。
- 越少越好
filtered
这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数
Extra
包含不适合在其他列中显示但十分重要的额外信息
- Using filesort :说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”,效率会慢。(order by 没使用索引)
- Using temporary :使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by,(group by 没使用索引),效率会慢。
- USING index:表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!
如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引只是用来读取数据而非利用索引执行查找。利用索引进行了排序或分组 - Using where:表明使用了where过滤
- using join buffer:使用了连接缓存(关联字段没用上索引)
- impossible where:where子句的值总是false,不能用来获取任何元组,(sql错误)
- select tables optimized away:在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
查询优化
批量创建数据
建表
CREATE TABLE `dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
ceo INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`empno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
往表里插入50W数据
- 设置参数log_bin_trust_function_creators(日志)
创建函数,假如报错:This function has none of DETERMINISTIC......
# 由于开启过慢查询日志,因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
show variables like 'log_bin_trust_function_creators';
#(mysql编程需要开启)
set global log_bin_trust_function_creators=1;
# 这样添加了参数以后,如果mysqld重启,上述参数又会消失,永久方法:
windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1
- 创建函数,保证每条数据都不同
- 随机产生字符串
- 随机产生部门编号
#随机生成字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
#假如要删除
#drop function rand_string;
#用于随机产生多少到多少的编号
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;
RETURN i;
END$$
#假如要删除
#drop function rand_num;
- 创建存储过程
- 创建往emp表中插入数据的存储过程
- 创建往dept表中插入数据的存储过程
#创建往emp表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE insert_emp( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit设置成0
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6) , rand_num(30,50),rand_num(1,10000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;
#创建往dept表中插入数据的存储过程
#执行存储过程,往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;
- 调用存储过程
- dept
- emp
#执行存储过程,往dept表添加1万条数据
DELIMITER ;
CALL insert_dept(10000);
#执行存储过程,往emp表添加50万条数据
DELIMITER ;
CALL insert_emp(100000,500000);
批量删除某个表上的所有索引
- 存储过程
DELIMITER $$
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY' ;
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index ",_index," on ",tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END$$
- 执行存储过程
CALL proc_drop_index("dbname","tablename");
单表使用索引及其常用索引失效
注意事项
- 最佳左前缀法则
- 如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。(“带头大哥不能死”)
- 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描.(能不用函数不要用,不然索引会失效)
- 存储引擎不能使用索引中范围条件右边的列,意思是“emp.deptId>20”这种范围索引右边的都会失效,所以遇到的话,建立索引时把它放在最右边。
- mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
- is not null 也无法使用索引,但是is null是可以使用索引的
- like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
- 字符串不加单引号索引失效
- 全值匹配我最爱:where后面有多少个字段就建立多少个索引
一般性建议
- 对于单键索引,尽量选择针对当前查询(query)过滤性更好的索引(如性别这种字段过滤性就不大好)
- 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
- 在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引(全值匹配我最爱)
- 在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面
- 书写sql语句时,尽量避免造成索引失效的情况
关联查询优化
- 保证被驱动表的join字段已经被索引
- left join 时,选择小表作为(第一个表)驱动表,大表作为被驱动表。(第一个表即驱动表是不可避免要全搜索的)
- inner join 时,mysql会自己帮你把小结果集的表选为驱动表。
- 子查询尽量不要放在被驱动表,有可能使用不到索引。
- 能够直接多表关联的尽量直接关联,不用子查询。
子查询优化
- 尽量不要使用not in 或者 not exists
- 用left join on xxx is null 替代
排序分组优化
ORDER BY
(无过滤不索引,加个limit也算过滤)
(顺序错,必排序,在order by中优化器不会调整顺序)
(方向反,必排序,一个升序一个降序用不了索引)
- ORDER BY子句,尽量使用Index方式排序,避免使用FileSort方式排序
- 如果不在索引列上,filesort有两种算法:mysql就要启动双路排序和单路排序。
-
双路排序
-
MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和orderby列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出
-
从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段。
-
取一批数据,要对磁盘进行了两次扫描,众所周知,I\O是很耗时的,所以在mysql4.1之后,出现了第二种改进的算法,就是单路排序。
-
单路排序
-
从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间,
因为它把每一行都保存在内存中了。 -
结论及引申出的问题
- 由于单路是后出的,总体而言好过双路
- 但是用单路有问题
- 在sort_buffer中,方法B比方法A要多占用很多空间,因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据,进行排序(创建tmp文件,多路合并),排完再取取sort_buffer容量大小,再排……从而多次I/O。本来想省一次I/O操作,反而导致了大量的I/O操作,反而得不偿失。
-
优化策略
- 增大sort_buffer_size参数的设置
- 增大max_length_for_sort_data参数的设置
- 减少select 后面的查询的字段。
-
提高Order By的速度
-
Order by时select * 是一个大忌只Query需要的字段, 这点非常重要。在这里的影响是:
1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时,会用改进后的算法——单路排序, 否则用老算法——多路排序。
1.2 两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。 -
尝试提高 sort_buffer_size
不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的 1M-8M之间调整 -
尝试提高 max_length_for_sort_data
提高这个参数, 会增加用改进算法的概率。但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率. 1024-8192之间调整
GROUP BY关键词优化
group by 使用索引的原则几乎跟order by一致 ,唯一区别是groupby即使没有过滤条件用到索引,也可以直接使用索引。
覆盖索引
减少使用select *