Mysql之索引及优化
性能分析
MySql Query Optimizer
自身select语句优化模块(未必是DBA认为最优的)
mysql常见瓶颈
CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
IO:磁盘IO瓶颈发生在装入数据远大于内存容量的时候
服务器硬件性能瓶颈
Explain
模拟优化器执行sql语句,从而知道mysql是如何处理sql语句的,从而分析出性能瓶颈
MyISAM 不支持外键 不支持事务 表锁 只缓存索引,不缓存数据 表空间小 关注性能
InnoDB 支持外键 支持事务 行锁 缓存索引及数据 表空间大 关注事务
Percona的xtradb引擎是一款可以替代InnoDB的引擎,在性能和并发上做的更好
多表查询
select <select_list> from tableA A left join tableB B on A.key=B.key 右补NULL select <select_list> from tableA A left join tableB B on A.key=B.key where B.key=NULL A独有 select <select_list> from tableA A right join tableB B on A.key=B.key 左补NULL select <select_list> from tableA A right join tableB B on A.key=B.key where A.key=NULL B独有 select <select_list> from tableA A inner join tableB B on A.key=B.key 共有 select <select_list> from tableA A full outer join tableB B on A.key=B.key 全有(mysql不支持,变通为左联unin右联) select <select_list> from tableA A full outer join tableB B on A.key=B.key where A.key=NULL or where B.key=NULL A独加B独(mysql不支持,变通为左独联unin右独联)
什么是索引
是帮助Mysql高效获取数据的数据结构,简单理解为“排好序的快速查找数据结构”,即索引两大功能,排序和查找
数据本身之外,数据库还维护这一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引
通常使用BTREE索引(多路搜索树) ,其他索引结构有hash。full-text,r-tree
优点
提高数据检索效率,降低数据库的IO成本
通过索引列对数据进行排序,降低数据排序成本,降低CPU消耗
缺点
索引也是一张表,保存主键与索引字段,并指向实体记录,所以所以列也是要占用空间
提高了查询速度,但是会降低更新,即增删改的速度
索引只是提高效率的一个因素,如果有大数据量的表,需要花时间建立最优索引
delete从逻辑删除,将记录标记为inactive,大量的inactive记录影响数据查找,稳定后有必要重建索引
创建索引基本知识
1、索引类似书的目录,会加快查询速度
2、在表的列(字段)上创建索引
3、索引会加快查询速度,但是也会影响更新速度,因为更新会维护索引数据。update修改数据时,会自动更新索引,所以有时会慢
4、索引不是越多越好,一般选择查询频繁的where条件的字段上创建
5、小表或重复值很多的列上一般不建索引,要在大表及重复值少的条件列上创建索引
6、多个列联合索引有前缀生效特性
7、当字段内容前n个字符已经接近唯一时,可以对字段前n个字符创建索引
8、索引从工作方式分,有主键,唯一,普通索引
9、索引类型有BTREE(默认)和hash(适合缓存,内存数据库)等
索引列的创建及生效条件
问题1?既然索引可以加快查询速度,那么就给所有列建索引吧?
解答:索引不但占用系统空间,更新数据库时还需要维护索引数据,因此索引是一把双刃剑,不是越多越好。几十到几百行的小表无需建立索引,写多读少的业务少建索引。
问题2?到底在哪些列上建立索引呢
secect user,host from mysql.user where host=...索引一定要创建在查询频繁条件列,而不是selcect后的选择数据的列,另外我们要尽量选择在唯一值多的大表上的列建立索引。select count(distinct列) from table
适合创建索引的场景
主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段,外键关系建立索引
频繁更新的字段不适合建索引
where条件里用不到的字段不建索引
单键/组合索引的选择:在高并发下倾向创建组合索引
查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度。即建立了inx_col1_col2_col3,order by col,col3优于order by col1
查询中统计或者分组字段
不适合创建索引的场景
表记录较少 (300w内)
经常增删改的表
数据分布平均的表字段或者重复多。例如有1000条记录,有999个唯一,索引选择性是999/1000.越接近于1,效率就越高
索引的建立与删除
=>增加索引在访问低谷时进行,几百万条需要几分钟
=>uniq index唯一索引,(用来约束字段值唯一,比如网站注册时用的email唯一,查询速度会快些)
mysql> show index from student; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | | | student | 1 | idx_name | 1 | names | A | 4 | NULL | NULL | YES | BTREE | | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ mysql> drop index idx_name on student; mysql> create index idx_name on student(names); mysql> alter table student drop index idx_name; mysql> alter table student add index idx_name(names);
索引分类
单值
唯一
复合
alter table tb_name add primary key(col_list) 主键索引,索引值必须唯一,不能为NUll
alter table tb_name add unique index_name(col_list) 创建的索引值必须唯一(除了NULL,NULL可能会出现多次)
alter table tb_name add index_name(col_list) 普通索引,索引值可出现多次
alter table tb_name add fulltext index_name(col_list) 全文索引
对字段的前n个字符创建索引
mysql> select * from student; +----+--------+-------------+-----+--------+----------+ | id | names | phonenum | age | dept | qq | +----+--------+-------------+-----+--------+----------+ | 1 | 张三 | 13701800003 | 23 | 经理 | 9485754 | | 2 | 李四 | 13701800004 | 24 | 助理 | 12344754 | | 3 | 王五 | 13701800005 | 25 | 员工 | 5414754 | | 4 | 赵六 | 1370180006 | 26 | 员工 | 1209756 | +----+--------+-------------+-----+--------+----------+
mysql> create index idx_qq4 on student(qq(4));
mysql> show index from student\G
*************************** 3. row ***************************
Table: student
Non_unique: 1
Key_name: idx_qq4
Seq_in_index: 1
Column_name: qq
Collation: A
Cardinality: 4
Sub_part: 4
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
创建联合索引
/*按照列查询数据时,联合索引是有前缀生效特性的
/*index(a,b,c)仅仅a,ab,abc三个查询条件列可以走索引,其他不走索引,所以把经常用于条件查询的放前面
mysql> create index idx_names_qq on student(names,qq(4)); mysql> show index from student\G *************************** 4. row *************************** Table: student Non_unique: 1 Key_name: idx_names_qq Seq_in_index: 1 Column_name: names Collation: A Cardinality: 4 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 5. row *************************** Table: student Non_unique: 1 Key_name: idx_names_qq Seq_in_index: 2 Column_name: qq Collation: A Cardinality: 4 Sub_part: 4 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment:
MYSQL查询优化 explain作用(sql语句执行计划)
- 表的读取顺序 id
- 数据读取操作的操作类型
- 哪些索引可以使用 possible_keys
- 哪些索引被实际使用 key
- 表之间的引用 ref
- 每张表有多少行被优化器查询 rows
mysql> explain select * from student where qq="5414754"; +----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | student | ref | idx_qq4 | idx_qq4 | 13 | const | 1 | Using where | +----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+
id:select
查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序 1、id相同,执行顺序由上至下 2、id不同,如果是子查询,id的序号会递增,越大优先级越高,越先被执行 3、id有相同有不同,大的先执行,相同的顺序执行 select_type:
主要是用于区别普通查询、联合查询、子查询或是 SIMPLE 简单的select查询,查询中部包含子查询或是UNION PRIMARY 查询中若包含任何复杂查询的子部分,最外层查询被标记为PRIMARY SUBQUERY 在SELECT或WHERE列表中包含了子查询 DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生),MYsql会递归执行这些子查询,把结果放在临时表 UNION 若第二个SELECT出现在UNION后,则标记为UNION,若UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED UNION RESULT 从UNION表获取结果的SELECT type:
访问类型排列,显示了查询使用了何种类型,常用的好到差依次为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 Full Index scan,(全索引扫描)index和ALL的区别为index类型只遍历索引树,通常比ALL快,因为索引文件通常比数据文件小。也就是说虽然all和index都是读全表,但是index是从索引中读取,而all则是从硬盘中读取 all Full Table scan,将遍历全表以找到匹配的行,全表扫描 possible_keys
显示可能应用在这张表中的索引,一个或多个,查询涉及到的字段上若存在索引,则该索引被列出,但是不一定被使用
key
实际使用的索引,如果为NULL,则没有使用索引。查询中若使用到了覆盖索引(联合索引个数与select项顺序及个数吻合),则该索引仅出现在key列表中
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引长度,在不损失精度的情况下,长度越短越好,key_len显示的值为索引字段的最大可能长度,并非实际使用长度。即key_len是根据表定义计算而得,不是通过表内检索出的
ref
显示索引的哪一列被使用了,如果可能的话,最好是一个常量,哪些列或常量被用于查找索引列上的值
rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数 extra
包含在不适合在其他列中显示但十分重要的额外信息 Using filesort(差) 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql中无法利用索引完成的排序操作称为“文件排序” Using temporary(更差) 使用了临时表保存中间结果,Mysql在对查询结果排序时使用了临时表,常见于排序order by和分组查询group by Using index(好) 表示相应的select操作中使用到了覆盖索引(Covering Index),避免访问了表的数据行,效率不错 如果同时出现using where,表明索引被用来执行索引键值的查找 如果没有同时出现using where,表明索引被用来读取数据而非执行查找动作 Covering Index (覆盖索引):就是select的数据列只用从索引中就能取得,不必读取数据行,mysql可以利用索引返回select表中的字段,而不必根据索引再次读取数据文件,换句话说,查询列要被所建立的索引覆盖。
如果要使用覆盖索引,一定要注意select取出需要的列,不可select * Using where Using join buffer(不重要,少见) 使用了连接缓存 impossible where (不重要,少见) where子句的值总是false,不能用来获取任何元组(where name=“a” and name=“b”) select tables optimized away(不重要,少见) 在没有group by子句的情况下,基于索引优化min/max操作或者对于myisam存储引擎优化count(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化 distinct(不重要,少见) 优化distinct操作,在找到第一匹配的元组后即停止找同样值得动作
mysql> drop index idx_qq4 on student; mysql> explain select * from student where qq="5414754"; +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
索引分析
单表
两表
三表
单表
#创建测试数据
create table article ( id int(10) unsigned not null primary key auto_increment, auther_id int(10) unsigned not null, category_id int(10) unsigned not null, view int(10) unsigned not null, comments int(10) unsigned not null, title varbinary(255) not null, content text not null ); insert into article values (1,1,1,1,1,"1","1"); insert into article values (2,2,2,2,2,"2","1"); insert into article values (3,3,3,3,3,"3","3");
#查询category_id=1,且comments>1,view最多的auther_id
mysql> explain select id,auther_id from article where category_id=1 and comments>1 order by view desc limit 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | article | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
mysql> create index idx_article_ccv on article(category_id,comments,view);
mysql> explain select id,auther_id from article where category_id=1 and comments>1 order by view desc limit 1; #comments>1范围以后索引导致失效
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | article | range | idx_article_ccv | idx_article_ccv | 8 | NULL | 1 | Using where; Using filesort |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
==>BTREE索引工作原理,A,B,C联合索引,先排序A,如果有相同的,再排序B,B有相同的,排序C。上述案例因为comments>1条件是个范围,因此,无法再利用索引对其后字段进行检索
mysql> explain select id,auther_id from article where category_id=1 and comments=1 order by view desc limit 1;
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+
| 1 | SIMPLE | article | ref | idx_article_ccv | idx_article_ccv | 8 | const,const | 1 | Using where |
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+
mysql> drop index idx_article_ccv on article;
#第一次优化不理想,尝试第二次
mysql> create index idx_article_cv on article(category_id,view);
mysql> explain select id,auther_id from article where category_id=1 and comments>1 order by view desc limit 1;
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| 1 | SIMPLE | article | ref | idx_article_cv | idx_article_cv | 4 | const | 1 | Using where |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
两表

create table class ( id int(10) unsigned not null auto_increment, card int(10) unsigned not null, primary key (id) ); create table book ( bookid int(10) unsigned not null auto_increment, card int(10) unsigned not null, primary key (bookid) ); insert into class(card) values (FLOOR(1+(RAND()*20))); insert into class(card) values (FLOOR(1+(RAND()*20))); insert into class(card) values (FLOOR(1+(RAND()*20))); insert into class(card) values (FLOOR(1+(RAND()*20))); insert into class(card) values (FLOOR(1+(RAND()*20))); insert into class(card) values (FLOOR(1+(RAND()*20))); insert into class(card) values (FLOOR(1+(RAND()*20))); insert into class(card) values (FLOOR(1+(RAND()*20))); insert into class(card) values (FLOOR(1+(RAND()*20))); insert into book(card) values (FLOOR(1+(RAND()*20))); insert into book(card) values (FLOOR(1+(RAND()*20))); insert into book(card) values (FLOOR(1+(RAND()*20))); insert into book(card) values (FLOOR(1+(RAND()*20))); insert into book(card) values (FLOOR(1+(RAND()*20))); insert into book(card) values (FLOOR(1+(RAND()*20))); insert into book(card) values (FLOOR(1+(RAND()*20)));
mysql> explain select * from class left join book on class.card=book.card;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 10 | |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 7 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
mysql> alter table book add index Y(card);
mysql> explain select * from class left join book on class.card=book.card; #左连接,右索引的情况
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 10 | |
| 1 | SIMPLE | book | ref | Y | Y | 4 | gtms.class.card | 1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
mysql> drop index Y on book
mysql> alter table class add index Y(card);
mysql> explain select * from class left join book on class.card=book.card; #左连接,左索引的情况
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | class | index | NULL | Y | 4 | NULL | 10 | Using index |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 7 | |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
==>结论,leftjoin条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引。
==>即左连接,使用右索引。class left join book=book right join class,交换表位置,所以索引不变,但是可以改变这个
==>右连接同理
三表
创建测试数据 create table phone ( phoneid int(10) unsigned not null auto_increment, card int(10) unsigned not null, primary key (phoneid) ) engine=innodb; insert into phone(card) values (FLOOR(1+(RAND()*20))); insert into phone(card) values (FLOOR(1+(RAND()*20))); insert into phone(card) values (FLOOR(1+(RAND()*20))); insert into phone(card) values (FLOOR(1+(RAND()*20))); insert into phone(card) values (FLOOR(1+(RAND()*20))); insert into phone(card) values (FLOOR(1+(RAND()*20))); insert into phone(card) values (FLOOR(1+(RAND()*20))); insert into phone(card) values (FLOOR(1+(RAND()*20))); insert into phone(card) values (FLOOR(1+(RAND()*20))); insert into phone(card) values (FLOOR(1+(RAND()*20))); insert into phone(card) values (FLOOR(1+(RAND()*20))); insert into phone(card) values (FLOOR(1+(RAND()*20)));
mysql> explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 10 | |
| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 7 | |
| 1 | SIMPLE | phone | ALL | NULL | NULL | NULL | NULL | 12 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
在右2表加索引
mysql> alter table phone add index Z(card);
mysql> alter table book add index Y(card);
mysql> explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 10 | |
| 1 | SIMPLE | book | ref | Y | Y | 4 | gtms.class.card | 1 | Using index |
| 1 | SIMPLE | phone | ref | Z | Z | 4 | gtms.book.card | 1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
==>后两行type都是ref且总row优化效果很好,因此索引最好设置在需要经常查询的字段上
结论jion语句优化
尽可能减少join语句中的nestedloop的循环总次数,“永远用小结果集驱动大的结果集” 即,左连接,左边全部,右边部分,右边小,因此索引建在右
优先优化nestedloop的内层循环
保证jion语句中被驱动表上的join条件字段已经被索引
当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下,不要太吝啬JoinBuffer的设置
避免索引失效注意点
全值匹配我最爱 最佳左前缀法则 不在索引列上做任何操作(计算、函数、类型转换(自动或手动)),会导致索引失效而转向全表扫描 存储引擎不能使用索引中范围条件右边的列 尽量使用覆盖索引,减少select * mysql在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描 is null ,is not null也无法使用索引 like以通配符开头 ("%abc") mysql索引失效会变成全表扫描 字符串补加引号索引失效 少用or,用它来连接时会索引失效
#创建测试数据
CREATE TABLE `staffs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', `pos` varchar(20) NOT NULL DEFAULT '' COMMENT '职位', `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT 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 idx_staffs_nap(name,age,pos);
mysql> explain select * from staffs where name="July"; +----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nap | idx_staffs_nap | 74 | const | 1 | Using where | +----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+ mysql> explain select * from staffs where name="July" and age="25"; +----+-------------+--------+------+----------------+----------------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+----------------+----------------+---------+-------------+------+-------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nap | idx_staffs_nap | 78 | const,const | 1 | Using where | +----+-------------+--------+------+----------------+----------------+---------+-------------+------+-------------+
mysql> explain select * from staffs where name="July" and age="25" and pos="dev"; +----+-------------+--------+------+----------------+----------------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+----------------+----------------+---------+-------------------+------+-------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nap | idx_staffs_nap | 140 | const,const,const | 1 | Using where | +----+-------------+--------+------+----------------+----------------+---------+-------------------+------+-------------+
mysql> explain select * from staffs where age="25" and pos="dev";
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
mysql> explain select * from staffs where pos="dev";
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
==>前缀生效原则,即最佳左前缀法则
如果索引了多列,查询需要从索引的最左前列开始并且不跳过索引中的列
mysql> explain select * from staffs where name="July" and pos="dev";
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+
| 1 | SIMPLE | staffs | ref | idx_staffs_nap | idx_staffs_nap | 74 | const | 1 | Using where |
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+
==>中间跳过了一个,索引部分生效,单独where name时key_len是74,现在依然是74。建议也别跳过
mysql> explain select * from staffs where left(name,4)="July";
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
==>不在索引列上做任何操作(计算、函数、类型转换(自动或手动)),会导致索引失效而转向全表扫描
mysql> explain select * from staffs where name="July" and age>25 and pos="dev";
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | range | idx_staffs_nap | idx_staffs_nap | 78 | NULL | 1 | Using where |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
==>存储引擎不能使用索引中范围条件右边的列,范围之后全失效(age被用到了,但是用在了排序,而不是查找)
mysql> explain select name,age,pos from staffs where name="July" and age=25 and pos="manager";
+----+-------------+--------+------+----------------+----------------+---------+-------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------+----------------+---------+-------------------+------+--------------------------+
| 1 | SIMPLE | staffs | ref | idx_staffs_nap | idx_staffs_nap | 140 | const,const,const | 1 | Using where; Using index |
+----+-------------+--------+------+----------------+----------------+---------+-------------------+------+--------------------------+
mysql> explain select * from staffs where name="July" and age=25 and pos="manager";
+----+-------------+--------+------+----------------+----------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------+----------------+---------+-------------------+------+-------------+
| 1 | SIMPLE | staffs | ref | idx_staffs_nap | idx_staffs_nap | 140 | const,const,const | 1 | Using where |
+----+-------------+--------+------+----------------+----------------+---------+-------------------+------+-------------+
==>尽量使用覆盖索引,减少select *
mysql> explain select * from staffs where name !="July";
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | idx_staffs_nap | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
mysql> explain select * from staffs where name <>"July";
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | idx_staffs_nap | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
==>mysql在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描
mysql> explain select * from staffs where name is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
mysql> explain select * from staffs where name is not null;
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | idx_staffs_nap | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
==>is null ,is not null也无法使用索引
mysql> explain select * from staffs where name like '%July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
mysql> explain select * from staffs where name like 'July%';
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | range | idx_staffs_nap | idx_staffs_nap | 74 | NULL | 1 | Using where |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
==>like以通配符开头 ("%abc") mysql索引失效会变成全表扫描
如何解决这种需要使用的情况呢
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','b@163.com');
insert into tbl_user(name,age,email)values('2aa2','222','a@163.com');
insert into tbl_user(name,age,email)values('3aa3','265','c@163.com');
insert into tbl_user(name,age,email)values('4aa4','21','d@163.com');
进行如下代码测试==>通过建立联合索引,即覆盖索引来解决

mysql> explain select name,age from tbl_user where name like "%aa%"; +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ mysql> explain select * from tbl_user where name like "%aa%"; +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ mysql> explain select name from tbl_user where name like "%aa%"; +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ mysql> explain select id from tbl_user where name like "%aa%"; +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ mysql> explain select age from tbl_user where name like "%aa%"; +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ mysql> explain select id,name,age from tbl_user where name like "%aa%"; +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ mysql> explain select * from tbl_user where name like "%aa%"; +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ mysql> create index idx_user_nameAge on tbl_user(name,age); Query OK, 0 rows affected (0.14 sec) ************************************* mysql> explain select name,age from tbl_user where name like "%aa%"; +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ | 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index | +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ mysql> explain select id from tbl_user where name like "%aa%"; +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ | 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index | +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ mysql> explain select name from tbl_user where name like "%aa%"; +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ | 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index | +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ mysql> explain select age from tbl_user where name like "%aa%"; +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ | 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index | +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ mysql> explain select id,name from tbl_user where name like "%aa%"; +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ | 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index | +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ mysql> explain select id,name,age from tbl_user where name like "%aa%"; +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ | 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index | +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ mysql> explain select name,age from tbl_user where name like "%aa%"; +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ | 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index | +----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+ mysql> explain select * from tbl_user where name like "%aa%"; +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+
mysql> explain select * from staffs where name=2000;(隐形的数字转换)
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | idx_staffs_nap | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
mysql> explain select * from staffs where name="2000";
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+
| 1 | SIMPLE | staffs | ref | idx_staffs_nap | idx_staffs_nap | 74 | const | 1 | Using where |
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-------------+
==>字符串不加引号索引失效
mysql> explain select * from staffs where name='July' or name='z3';
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | idx_staffs_nap | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
==>少用or,用它来连接时会索引失效
测试数据,创建联合索引,观察相关执行情况 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 inx_c1234 on test03(c1,c2,c3,c4);
mysql> explain select * from test03 where c1='a1';
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
| 1 | SIMPLE | test03 | ref | inx_c1234 | inx_c1234 | 31 | const | 1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
mysql> explain select * from test03 where c1='a1' and c2='a2';
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+
| 1 | SIMPLE | test03 | ref | inx_c1234 | inx_c1234 | 62 | const,const | 1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+
mysql> explain select * from test03 where c1='a1' and c2='a2' and c3='a3';
+----+-------------+--------+------+---------------+-----------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------------------+------+-------------+
| 1 | SIMPLE | test03 | ref | inx_c1234 | inx_c1234 | 93 | const,const,const | 1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------------------+------+-------------+
mysql> explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
+----+-------------+--------+------+---------------+-----------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | test03 | ref | inx_c1234 | inx_c1234 | 124 | const,const,const,const | 1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------------------------+------+-------------+
mysql> explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';
+----+-------------+--------+------+---------------+-----------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | test03 | ref | inx_c1234 | inx_c1234 | 124 | const,const,const,const | 1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------------------------+------+-------------+
mysql> explain select * from test03 where c4='a4' and c3='a3' and c2='a2' and c1='a1';
+----+-------------+--------+------+---------------+-----------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | test03 | ref | inx_c1234 | inx_c1234 | 124 | const,const,const,const | 1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------------------------+------+-------------+
==>MYSQL OPTIMIZER查询优化器起的作用
mysql> explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
==>范围后索引失效,c3用在了排序
mysql> explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | test03 | range | inx_c1234 | inx_c1234 | 93 | NULL | 1 | Using where |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
mysql> explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | test03 | range | inx_c1234 | inx_c1234 | 124 | NULL | 1 | Using where |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
==>用到了4个,c4>'a4'范围之后全失效,但是c4是最后一个,所以。
mysql> explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+
| 1 | SIMPLE | test03 | ref | inx_c1234 | inx_c1234 | 62 | const,const | 1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+
==>c3用到了,但是用在了排序,而不是查找
mysql> explain select * from test03 where c1='a1' and c2='a2' order by c3;
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+
| 1 | SIMPLE | test03 | ref | inx_c1234 | inx_c1234 | 62 | const,const | 1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+
==>效果同上
mysql> explain select * from test03 where c1='a1' and c2='a2' order by c4;
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-----------------------------+
| 1 | SIMPLE | test03 | ref | inx_c1234 | inx_c1234 | 62 | const,const | 1 | Using where; Using filesort |
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-----------------------------+
==>c3断了,Using filesort
mysql> explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
| 1 | SIMPLE | test03 | ref | inx_c1234 | inx_c1234 | 31 | const | 1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
==>只用c1字段排序,但是c2,c3用于排序,因此无Using filesort,如果后面order by c3,c4会出现
mysql> explain select * from test03 where c1='a1' and c2='a2' order by c3,c2;
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+
| 1 | SIMPLE | test03 | ref | inx_c1234 | inx_c1234 | 62 | const,const | 1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------------+------+-------------+
==>因为排序字段c2已经是个常量,即c2是一个唯一值。
mysql> explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
| 1 | SIMPLE | test03 | ref | inx_c1234 | inx_c1234 | 31 | const | 1 | Using where |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-------------+
mysql> explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;
+----+-------------+--------+------+---------------+-----------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+----------------------------------------------+
| 1 | SIMPLE | test03 | ref | inx_c1234 | inx_c1234 | 31 | const | 1 | Using where; Using temporary; Using filesort |
+----+-------------+--------+------+---------------+-----------+---------+-------+------+-----------------------------------------
==>group by基本上都需要排序,会有临时表产生(分组之前必排序)
索引优化一般性建议
1、对于单键索引,尽量选择对当前query过滤性更好的索引
2、在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好
3、在选择组合索引的时候,尽量选择可能能够包含当前query中的where字句中更多字段的索引
4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
SQL语句查询优化原则:小表驱动大表
#################原理######### select * from A where id in (select id from B) 等价于 for select id from B for select * from A where A.id=B.id 当B表的数据集必须小于A表的数据集时,用in优于exist select * from A where exists (select 1 from B where B.id=A.id) 等价于 for select * from A for select * from B where B.id=A.id 当A表的数据集小于B表的数据集时,用exist优于in(A与B表的ID字段应该建立索引) EXISTS select ... from table where exists(subquery) 该语法可以理解为,将主查询的数据,放到子查询中做条件验证,根据验证结果(TRUE或FALSE),来决定主查询的数据结果是否得以保留 EXISTS(subquery)只返回TRUE或FALSE EXISTS(subquery)实际过程中可能经过了优化而不是我们理解上的逐条对比 EXISTS(subquery)往往也可以使用条件表达式,其他子查询或者join来替代,何种最优需要具体分析
Order by优化
reate table tblA ( age int, birth timestamp not null ); insert into tblA(age,birth)values(22,now()); insert into tblA(age,birth)values(23,now()); insert into tblA(age,birth)values(24,now()); create index idx_ab on tblA(age,birth);
mysql> explain select * from tblA where age >20 order by age;
+----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+
| 1 | SIMPLE | tblA | index | idx_ab | idx_ab | 9 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+
mysql> explain select * from tblA where age >20 order by birth;
+----+-------------+-------+-------+---------------+--------+---------+------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+------+------------------------------------------+
| 1 | SIMPLE | tblA | index | idx_ab | idx_ab | 9 | NULL | 3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+---------------+--------+---------+------+------+------------------------------------------+
mysql> explain select * from tblA where age >20 order by age,birth;
+----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+
| 1 | SIMPLE | tblA | index | idx_ab | idx_ab | 9 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+
mysql> explain select * from tblA order by age asc,birth desc;
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------+
| 1 | SIMPLE | tblA | index | NULL | idx_ab | 9 | NULL | 3 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-----------------------------+
==>order by默认升序
order by排序结论
Mysql支持FileSort和Index方式排序,Index方式效率高(扫描索引),并尽可能的在索引列上完成排序操作,遵循索引建立的最佳左前缀原则
如果不在索引列上,filesort有两种算法:双路排序和单路排序 双路排序
双路排序:mysql4.1前使用双路排序,扫描两次磁盘,得到数据。先读取行指针和order by的列,进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出
单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对他们进行排序,然后扫描排序后的列表进行输出,效率更快些,避免二次读取数据,并且把随机IO变成顺序IO,但是需要更多的空间,因为它把每行都保存在内存中了
在sort_buffer中,方法B要比A暂用更多空间,所以有可能取出的数据总大小超出其容量,导致每次只能读取其相应容量的数据,进行排序(创建tmp文件,多路合并),排完再取,再排,从而多次IO,反而导致大量IO
提高order by速度
1、order by使用select *是大忌
当query字段大小总和小于max_lenth_for_sort_data而且字段不是text或blob类型时,会使用单路,否则使用多路
两种算法都有可能超出sort_buffer容量,超出的话使用单路风险更大,所以可以提高sort_buffer_size
2、尝试提高sort_buffer_size
对两种算法都有效率提升,要注意此参数针对每个进程
3、尝试提高max_lenth_for_sort_data
会增加使用改进算法的概率,但是入股哦太高,数据总量超过sort_buffer_size的概率就增大,明显症状是高的磁盘IO和低的cpu使用效率
order by满足两种情况会使用Index方式排序
1、order by语句使用索引最左前列
2、使用where子句与order by子句条件列组合满足索引最左前列
为排序使用索引
Mysql两种排序方式:文件排序和扫描有序索引排序
Mysql能为排序和查询使用相同的索引
key a_b_c(a,b,c)
order by使用索引前缀生效
order by a order by a,b order by a,b,c
order by a desc,b desc,c desc 要么同升,要么同降
如果where使用最左前缀定义为常量,则order by能使用索引
where a = const order by b,c
where a = const and b =const order by c
where a = const and b>const order by b,c
不能使用索引进行排序
order by a asc,b desc,c desc 排序不一致
where g=const order by b,c 丢失a索引
where a=const order by c 丢失b索引
where a=const order by a,d d不是索引的一部分
where a in (...) order by b,c 对于排序来说,多个相等条件也是范围查询
group by关键字优化
实质是先排序后分组,遵照索引建的最佳左前缀
当无法使用索引列,增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where高于having,能写在where限定的条件就不要去having限定了
使用explain优化SQL语句的基本流程(一般让开发去优化)
1、抓慢查询,然后使用explain语句检查索引执行情况
a、mysql> show full processlist\G (现场抓)或#mysql -uroot -prootabcd -e "show processlist;" | grep -i select(效果更好) ,之后可以用explain或profile分析
b、分析慢查询日志(平时运维)使用mysqlsla工具分析(自带的mysqldumpslow比较简陋)
long_query_time=2 超过2秒以上的查询记录下来
log-slow-queries=/data/slow.log
log_queries_not_using_indexes 没走索引的也记录
mysql> show variables like "%slow_query%";
+---------------------+-----------------------+
| Variable_name | Value |
+---------------------+-----------------------+
| slow_query_log | OFF |
| slow_query_log_file | /data/node80-slow.log |
+---------------------+-----------------------+
mysql> show variables like "%long_query%";
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
mysql> show global status like "%Slow_que%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 0 |
+---------------+-------+
mysqldump分析工具
返回记录集最多的10个sql mysqldumpslow -s r -t 10 slowfile | more
返回访问次数最多的10个sql mysqldumpslow -s c -t 10 slowfile | more
按时间排序的前10条里面含有做链接的sql mysqldumpslow -s t -t 10 -g "left join" slowfile | more
对slow.log,进行切割脚本分析 切割慢查询(写成脚本,使用mysqlsla分析),自带mysqldumpslow工具
cd /data/ &&
/bin/mv slow.log /back/$(date +%F)_slow.log
mysqladmin -uroot -prootabcd flush-logs (也会刷新binlog)
mysqlsla /back/$(date +%F)_slow.log > new_/back/$(date +%F)_slow.log
mail -s "logname" mailadress </bakup/ new_/back/$(date +%F)_slow.log
2、explain语句检查索引执行情况
3、对需要建索引的列建立索引或者参数调优(最后才考虑调优)
使用explain优化sql语句场景案例
优化起因
1、网站慢,浏览慢,假定查出是数据库问题
2、数据库服务器查看uptime,负载很高(load average:8.01 6.30,5.58 超过核数数属于高的)
3、登陆数据库show full processlist===>再用explain 检查语句是否走索引,查看建表语句或show index等,
4、慢查询语句或日志
5、select count(distinct列) from table 查看字段唯一数 根据这个和开发沟通创建相关索引,如果都不多考虑联合索引,如果大表,创建需要几分钟,建议在业务低谷执行
mysql> show global variables like "profiling";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
注意如下问题
converting HEAP to MyISAM查询结果太大,内存都不够用了往磁盘上搬了
Creating tmp table 创建临时表
Copying to tmp table on disk 把内存中的临时表复制到磁盘,危险
locked
锁
锁是计算机协调多个进程或线程并发访问某一资源的机制
根据操作类型分
读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响
写锁(排它锁):当前写操作没有完成前,它会阻断其他写锁和读锁
根据数据操作的力度
行锁
表锁
myisam表锁分析
create table mylock( id int not null primary key auto_increment, name varchar(20) ) engine myisam;
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
mysql> show open tables; 查看哪些表被加锁了 +--------------------+----------------------------------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +--------------------+----------------------------------------------+--------+-------------+ | mysql | time_zone_transition_type | 0 | 0 | | performance_schema | events_waits_summary_global_by_event_name | 0 | 0 | | performance_schema | setup_timers | 0 | 0 | | performance_schema | events_waits_history_long | 0 | 0 | | gtms | tblA | 0 | 0 | | performance_schema | mutex_instances | 0 | 0 | | performance_schema | events_waits_summary_by_instance | 0 | 0 | | mysql | tables_priv | 0 | 0 | | gtms | mylock | 0 | 0 |
mysql> show status like "table%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 54 |产生表锁的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1
| Table_locks_waited | 0 |出现表锁争用而发生的等待次数,(不能立即获取的次数,每等待一次加1),此值高说明存在较严重的表锁争用
+-----------------------+-------+
加解锁的方法
mysql> lock table mylock read;
mysql> lock table mylock write;
mysql> unlock tables;
进行测试
mysql> lock table mylock read; 增加读锁
mysql> update mylock set name='a2' where id=1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated 不能进行更新
mysql> select * from article;
ERROR 1100 (HY000): Table 'article' was not locked with LOCK TABLES 也不能读其他表
==>只能读自己,不能改自己,也不能读其他表
==>再开一个sesson,尝试update,会阻塞,等待解锁后被执行。可想而知,效率呢?
mysql> unlock tables;
mysql> lock table mylock write;
mysql> update mylock set name='a2' where id=1;
mysql> select * from article; 不能读其他表
ERROR 1100 (HY000): Table 'article' was not locked with LOCK TABLES
==>再开个session,对mylock进行读操作,阻塞(备注:测试时换成不同ID,因为第二次条件会从缓存获得,影响锁效果演示)
mysql表锁结论
1、读锁,不会阻塞其他进程对同一表的读请求,但是会阻塞对同一表的写请求,只有释放后,才会执行其他进程的操作
2、写锁,会阻塞其他进程对同一表的读写操作,释放后,才会执行其他进程的读写操作
==>简而言之,读会阻塞写,但是不会阻塞读,写锁则会把读和写都阻塞
此外,myisam的读写锁调度室写优先,这也是不适合作为主表的引擎,因为写锁后,其他线程不能做任何操作,大量的更新使查询很难得锁,从而造成永远阻塞
InnoDB与MyISAM最大不同点:支持事务及行级锁(行锁会变成表锁,因为char类型没加"")
1、原子性(Atomicity):事务中的全部操作在数据库中是不可分割的,要么全部完成,要么均不执行。 2、一致性(Consistency):几个并行执行的事务,其执行结果必须与按某一顺序串行执行的结果相一致。 3、隔离性(Isolation):事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明的。(未提交读,已提交读,可重复读,可序列化4个级别) 比如--single-transaction 4、持久性(Durability):对于任意已提交事务,系统必须保证该事务对数据库的改变不被丢失,即使数据库出现故障。一个事务一旦被提交,它对数据库中的数据改变就是永久性的,如果出了错误,事务也不允许撤销,只能通过“补偿性事务”
间隙锁
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁; 对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。 例如 update table set b="444" where a>1 and a<6 (其中记录4不存在) 当其他session需要插入记录4时,会被阻塞,直到之前的update语句commit后才能执行 InnoDB使用间隙锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求, 对于上面的例子,要是不使用间隙锁,如果其他事务插入了a>1 and a<6 任何记录,那么本事务如果再次执行上述语句,就会发生幻读; 很显然,在使用范围条件检索并锁定记录时,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待 因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。 InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁
如何锁定一行
mysql>begin mysql>select * from tableA where a=8 for update;
xxxxxxxxxxxxxxxxxxxx msyql>commit;
分析行锁争夺情况
mysql> show status like "innodb_row_lock%"; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 |当前正在等待锁定的数量 | Innodb_row_lock_time | 0 |从启动到现在锁定总时间长度 | Innodb_row_lock_time_avg | 0 |每次等待所花平均时间 | Innodb_row_lock_time_max | 0 |从系统启动到现在等待最长一次所花时间 | Innodb_row_lock_waits | 0 |系统启动后到现在总共等待的次数 +-------------------------------+-------+
行锁分析
尽可能让所有数据检索都通过索引完成,避免无索引行锁升级为表锁
合理设计索引,尽量缩小锁的范围
尽可能减少检索条件,避免间隙锁
尽量控制事务大小,减少锁定资源量和时间长度
尽可能低级别事务隔离