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 |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
View Code

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 |系统启动后到现在总共等待的次数 +-------------------------------+-------+

行锁分析
  尽可能让所有数据检索都通过索引完成,避免无索引行锁升级为表锁
  合理设计索引,尽量缩小锁的范围
  尽可能减少检索条件,避免间隙锁
  尽量控制事务大小,减少锁定资源量和时间长度
  尽可能低级别事务隔离

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 




 

 

 

posted @ 2017-03-23 01:40  黑色月牙  阅读(939)  评论(0)    收藏  举报