1. 性能下降
1.1 索引失效
- 单值
- 复合
1.2 关联查询太多join
1.3 服务器调优及各个参数设置
- 缓冲
- 线程数
2. 常见Join查询
2.1 SQL执行顺序
SELECT 7
DISTINCT <select_list> 8
FROM <left_table> 1
<join_type>JOIN <right_table> 3
ON <join_condition> 2
WHERE <where_condition> 4
GROUP BY <group_by_list> 5
HAVING <having_condition> 6
ORDER BY <order_by_condition> 9
LIMIT <limit_number> 10
- FROM 笛卡尔积
- ON 主表保留
- JOIN 不符合ON也添加
- WHERE 非聚合,非SELECT别名
- GROUP BY 改变对表引用
- HAVING 只作用分组后
- SELECT DISTINCT
- ORDER BY 可使用SELECT别名
- LIMIT offset rows
3. 索引简介
- 索引(Index)是帮助Msql高效获取数据的数据结构,提高查询效率,可类比字典
- 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样据可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引
- 索引本身很大,不可能全部存在内存中,往往以索引文件的形式存储在磁盘上
优势
- 提高数据检索的效率,降低数据库IO成本
- 通过索引列对数据进行排序,降低了CPU消耗
劣势
- 实际上索引也是一张表,索引列也是占空间的
- 提高了查询速度,降低了更新表的速度,insert,update和delete
3.1 mysql索引分类
- 单值索引:一个索引只包含单个列,一个表可以有多个单列索引
- 唯一索引:索引列的值必须唯一,但允许有空值
- 复合索引:一个索引包含多个列
3.2 语法
#创建
create [unique] index indexName on mytable(columnname(length));
alter mytable add [unique] index [indexName] on (colmnname(length));
#删除
drop index [indexName] on mytable;
#查看
show index from table_name;
#有四种方式来添加数表的索引:
alter table tbl_name add primary key (column_list);#该语句添加一个主键,这意味着索引值必须是唯一的,且不能为null
alter table tbl_name add unique index_name(column_list);#这条语句创建索引的值必须是唯一额,除null外
alter table tbl_name add index index_name(column_list);#添加普通索引,索引值可出现多次
alter table tbl_name add fulltext index_name(column_list);#指定了索引为fulltext,用于全文索引
3.3 索引结构
- BTree索引
- Hash索引
- full-text全文索引
- R-Tree索引
哪些情况要建索引
1.主键自动建立唯一索引
2.频繁作为查询条件的字段
3.查询与其他表关联的字段,外键关系建立索引
4.频繁更新的字段不适合创建索引
5.where条件用不到的字段不创建
6.高并发下倾向创建组合索引
7.查询中排序的字段,若通过索引去访问将大大提高排序速度的
8.查询中统计或分组字段
哪些情况不要创建索引
1.表记录太少
2.经常增删改的表
3.数据重复且分布平均的表字段,如果某个数据列包含许多重复的内容,建索引没有太大的实际效果
4. 性能分析
4.1 Mysql Query Optimizer
- Mysql有专门负责优化select语句额优化器模块,通过计算分析系统中收集得到的统计信息,为客户端请求的Query提供他认为最优的执行计划
4.2 Mysql常见瓶颈
- CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时
- IO:磁盘I/O瓶颈发生在装入数据远大于内存容量时
- 服务器硬件的性能瓶颈:top,free,iostat和vmstat查看系统的性能状态
4.3 Explain(执行计划)
- 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道Mysql是如何处理SQL语句的,分析查询语句或是表结构的性能瓶颈
- 官网描述
4.3.1 id
- select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
1.id相同,执行顺序由上至下
2.id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
3.id相同不同同时存在,大id先执行,衍生=derived
4.3.2 select_type
- 查询的类型,主要用于区别普通查询、联合查询、子查询等复杂查询
1.SIMPLE,简单的select查询,查询中不包含子查询或union
2. PRIMARY,查询中包含任何复杂的子查询,最外层查询被标记为
3.SUBQUERY,在select或where列表中包含子查询
4.DERIVED,在FROM列表中包含的子查询被标记为derived(衍生),mysql会递归执行这些子查询,把结果放在临时表中
5.UNION,若第二个select出现在union后,则被标记为union;若union包含在from子句的子查询中,外层select将被标记为derived
6.UNION RESULT,从union表获取结果的select
4.3.3 table
- 显示这一行的数据是关于哪张表的
4.3.4 type
- 访问类型排列
- 显示查询使用了何种类型,从最好到最差依次是system>const>eq_ref>ref>range>index>ALL
- 一般来说,保证查询至少达到raneg级别,最好达到ref
system,表只有一行记录,等于系统表,这是const类型的特例,平时不会出现,可以忽略不计
const,表示通过索引一次就能找到,const用于比较primary key或unique索引,因为只匹配一行数据,所以很快如将主键置于where列表,mysql就能将该查询转换为一个常量
eq_ref,唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描
ref,非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引扫描,返回所有匹配某个单独值的行
range,只检索给定范围的行,使用一个索引来选择行,key列显示使用了哪些索引,一般是where语句中出现了between、<、>、in等查询
index,Full Index Scan,与ALL区别为index只遍历索引树,比ALL快,因为索引文件通常比数据文件小,index与ALL都是读全表,index是从索引中读取,all是从硬盘中读取
all,Full Table Scan,遍历全表找到匹配行
4.3.5 possible_keys
- 显示可能应用在这张表中的索引,一个或多个
- 查询涉及都的字段上若存在索引,则索引将被列出,但不一定被查询实际使用
4.3.6 key
- 实际使用的索引,如果为null,则没有使用索引
- 查询中若使用了覆盖索引,则该索引仅出现在key列表中
- 覆盖索引:select的数据列只从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖
4.3.7 key_len
- 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况西,长度越短越好
- 显示的值为索引字段的最大可能长度,并非实际使用长度,根据表定义计算而得,不是通过表内检索出的
4.3.8 ref
- 显示索引的哪一列被使用了,如果可能的话,是一个常数,哪些列或常量被用于查找索引列上的值
- 查询中与其他表关联的字段,外键关系建立索引
4.3.9 rows
- 根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
4.3.10 Extra
- 包含不适合在其他列显示但十分重要的额外信息
- Using filesort,mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”
- Using temporyary,使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
- Using index,表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错。如果同时出现using where,表明索引被用来职系那个索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作
- Using where,表明使用了where过滤
- using join buffer,使用了连接缓存
- impossible where,where子句的值总是false,不能用来获取任何元组
- select tables optimized away,在没有group by子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化
- distinct,优化distinct操作,在找到第一匹配的元组后即停止找同样值的操作
5. 索引优化
5.1 索引分析
5.1.1 单表
CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL ,
`views` INT(10) UNSIGNED NOT NULL ,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3');
SELECT * FROM article;
#普通查询,type是ALL,还Using filesort
explain select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;
#创建索引
create index idx_article_ccv on article(category_id,comments,views);
#再次explain,type是range,依旧Using filesort
drop index idx_article_ccv on article;
#再次创建索引
create index idx_article_cv on article(category_id,views);
#再次explain,type是ref,Using filesort消失
5.1.2 两表
CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
#基本左连接,type全是ALL
explain select * from class left join book on class.card=book.card;
#创建右表索引优化
alter table book add index y (card);
#再次explain,右表type变为ref
drop index y on book;
#创建左表索引优化
alter table class add index y (card);
#再次explin,左表type变为index
#LEFT JOIN用于确定如何从右表搜索行,左边一定有,所以在右表建立索引
5.1.3 三表
CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
#普通连接,type全是ALL
explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
#创建索引
alter table phone add index x (card);
alter table book add index y (card);
#再次explin,type变为ref,总rows优化很好。因此索引最好设置在需要经常查询的字段中
Join语句的优化
1.尽可能减少Join语句中的NestedLoop的循环总次数,永远用小结果集驱动大结果集
2.优先优化NestedLoop的内层循环
3。保证Joinu语句中被驱动表上Join条件字段已被索引
4.当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下,不要太吝啬JoinBuffer的设置
5.2 索引失效(避免)
CREATE TABLE 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'入职时间'
)CHARSET utf8 COMMENT'员工记录表';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
- 全值匹配
#type是ref
explain select * from staffs where name ='July';
explain select * from staffs where name ='July' and age =25;
explain select * from staffs where name ='July' and age =25 and pos ='dev';
#type是ALL
explain select * from staffs where age =25 and pos ='dev';
explain select * from staffs where pos ='dev';
- 最佳左前缀法则
如果索引了多列,要遵守最左前缀法则。查询从索引的最左前列开始并且不跳过索引中的列
- 不在索引上做任何操作(计算、函数、类型转换),会导致索引失效而转向全表扫描
#type是ALL
explain select * from staffs where left(name,4)='July';
- 存储引擎不能使用索引中范围条件右边的列
explain select * from staffs where name='z4' and age=22 and pos='manager';
#type是range
explain select * from staffs where name='z4' and age>11 and pos='manager';
- 尽量使用覆盖索引,减少select *
explain select * from staffs where name='z4' and age=22 and pos='manager';
explain select * from staffs where name='z4' and age>11 and pos='manager';
#type是ref,Using index
explain select name,age,pos from staffs where name='z4' and age=22 and pos='manager';
explain select name,age,pos from staffs where name='z4' and age>11 and pos='manager';
- MySQL在不使用不等于(!=,<>)时无法使用索引会导致全表扫描
#type是ALL
explain select * from staffs where name != 'July';
- is null,is not null 也无法使用索引
#type是ALL
explain select * from staffs where name is not null;
#type是null
explain select * from staffs where name is null;
- like以通配符开头(%xxx)MySQL索引失效会变成全表扫描的操作
#type是ALL,key是NULL
explain select * from staffs where name like '%July%';
explain select * from staffs where name like '%July';
#type是range
explain select * from staffs where name like 'July%';
/*必须使用'%xxx%'时,如何解决like'%xxx%'时索引不被使用
使用覆盖索引
*/
CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');
explain select id,name,age,email from tbl_user where name like '%aa%';
#创建索引
create index idx_user_nameAge on tbl_user(name,age);
- 字符串不加单引号索引失效
#type是ref
explain select * from staffs where name ='2000';
#type是ALL
explain select * from staffs where name =2000;
10.少用or,用or连接时会索引失效
#type是ALL
explain select * from staffs where name ='July' or name='z3';
面试题
create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10));
insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
explain select * from test03 where c4='a1' and c3='a2' and c2='a3' and c1='a4';
explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;#c3的作用用于排序而不是查找
explain select * from test03 where c1='a1' and c2='a2' order by c3;
explain select * from test03 where c1='a1' and c2='a2' order by c4;#Using filesort
explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;#只用了c1一个字段索引,c2、c3用于排序,无filesort
explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;#Using filesort
explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;#用c1、c2两个索引
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;#无filesort
explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;#Using temporary; Using filesort
- 定值、范围还是排序,一般order by是给个范围
- group by 基本上都需要进行排序,会有临时表产生
5.3 一般建议
- 对于单键索引,尽量选择针对当前查询过滤性更好的索引
- 在选择组合索引的时候,当前查询中过滤性最好的字段在索引字段顺序中,位置越靠前越好
- 选择组合索引时,尽量选择可以能够包含当前查询中的where字句中更多字段的索引
- 尽可能通过统计信息和调整查询的写法来达到选择合适索引的目的
全值匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
索引列上少计算,范围之后全失效
Like百分写最右,覆盖索引不写星