索引建立
索引建立
- 单表
-
create table if not exists `artcle`( id INT(10) unsigned not null primary key auto_increment, author_id INT(10) unsigned not null , category_id INT(10) unsigned not null, views INT(10) unsigned not null , comments INT(10) unsigned not null , title varbinary(255) not null , content TEXT not null ); desc artcle; insert into artcle(author_id, category_id, views, comments, title, content) values
(1,1,1,1,'1','1');(2,2,2,2,'2','2')(1,1,3,3,'3','3'）; select * from artcle; explain select id ,author_id from artcle where category_id = 1 and comments>1 order by views desc limit 1;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | artcle | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)type是all 而且extra 为Using filesort，要优化 select id ,author_id from artcle where category_id = 1 and comments>1 order by views desc limit 1; show index from artcle;
开始优化
1，新建索引2.删除索引
建立 ccv 联合主键create index idx_artcle_ccv on artcle(category_id,comments,views);
show index from artcle;explain select id ,author_id from artcle where category_id = 1 and comments>1 order by views desc limit 1;
explain select id ,author_id from artcle where category_id = 1 and comments>1 order by views desc limit 1; 索引尽量用等于 =
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | artcle | NULL | range | idx_artcle_ccv | idx_artcle_ccv | 8 | NULL | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)从结果看，联合索引运用到了，但是extra还是Using filesort
1.建立联合索引ccv之后因为按照Btree索引工作原理，先排序category_id，如果遇到相同的category_id则再排序comments，如果遇到相同comments
再排序views
2.当comments字段在联合索引里处于中间位置时，因为comments>1条件是一个范围值，range ，mysql无法利用索引对后面的views部分进行检索
，即range类型查询字段后面的索引无效。
建立 cv联合索引create index idx_artcle_cv on artcle(category_id,views);
再次explainexplain select id ,author_id from artcle where category_id = 1 and comments>1 order by views desc limit 1;
mysql> explain select id ,author_id from artcle where category_id = 1 and comments>1 order by views desc limit 1;
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+----------------------------------+
| 1 | SIMPLE | artcle | NULL | ref | idx_artcle_cv | idx_artcle_cv | 4 | const | 2 | 33.33 | Using where; Backward index scan |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
发现type变成了ref 实际运用索引被使用，extra里面Using filesort 消失 出现了Backward index scan 问题解决
-
- 两表
建表语句为
create table if not exists `class`(
id int(10) unsigned not null auto_increment,
card int(10) unsigned not null,
primary key(id)
);
create table if not exists `book`(
bookid int(10) unsigned not null auto_increment,
card int(10) unsigned not null ,
primary key (bookid)
);
show tables;
insert into class(card) values(FLOOR(1+(RAND()*20)));
delimiter $
create procedure insertt(in count int(10))
begin
declare i int default 1;
a:while i<count do
insert into class(card) values(FLOOR(1+(RAND()*20)));
set i = i+1;
end while a;
end $
delimiter $
create procedure insert_book(in count int(10))
begin
declare i int default 1;
a:while i<count do
insert into book(card) values(FLOOR(1+(RAND()*20)));
set i = i+1;
end while a;
end $
call insertt(12);
call insert_book(12);
select * from class;
select * from book;mysql> explain select * from class left join book b on class.card = b.card; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 18 | 100.00 | NULL | | 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 18 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)
type 等于 allalter table book add index Y(card);
建立book表索引
mysql> show index from book; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | book | 0 | PRIMARY | 1 | bookid | A | 18 | NULL | NULL | | BTREE | | | YES | NULL | | book | 1 | Y | 1 | card | A | 11 | NULL | NULL | | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 2 rows in set (0.01 sec) mysql> explain select * from class left join book b on class.card = b.card; +----+-------------+-------+------------+------+---------------+------+---------+--------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+--------------------+------+----------+-------------+ | 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 18 | 100.00 | NULL | | 1 | SIMPLE | b | NULL | ref | Y | Y | 4 | mysqljj.class.card | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+--------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
type 变为 ref 情况变好点建立class表card索引
alter table class add index Y(card);
mysql> explain select * from class left join book b on class.card = b.card; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | class | NULL | index | NULL | Y | 4 | NULL | 18 | 100.00 | Using index | | 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 18 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)
个人理解：左连接一般加在右表，因为左面为主表，左表引用右表，我们引用右表，当然是拿左表的数据取检索右表的数据，我们将索引添加到右表，大大提高了性能，type变为了ref
换一种理解思路，因为我们是用主表中数据取比对附表，那么我们主表数据肯定都有，那么我们检索相对应的表的时候，可以添加所以，提高性能
同样，right join ，右连接，那么就是在相反的，左表中添加索引mysql> alter table `book` add index Y(`card`); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from class left join book b on class.card = b.card; +----+-------------+-------+------------+------+---------------+------+---------+--------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+--------------------+------+----------+-------------+ | 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 18 | 100.00 | NULL | | 1 | SIMPLE | b | NULL | ref | Y | Y | 4 | mysqljj.class.card | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+--------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
右连接示范 右连接是查找右面的所有数据，所以，右主表都有，坐标加索引 type 变为 ref 相反建 mysql> explain select * from book b RIGHT JOIN class c on b.card = c.card; +----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+-------------+ | 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | 18 | 100.00 | NULL | | 1 | SIMPLE | b | NULL | ref | Y | Y | 4 | mysqljj.c.card | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) - 三表
添加phone 表
create table if not exists `phone`( phoneid int(10) unsigned not null auto_increment, card int(10) unsigned not null , primary key (phoneid) )ENGINE=INNODB; delimiter $ create procedure insert_phone(in count int(10)) begin declare i int default 1; a:while i<count do insert into phone(card) values(FLOOR(1+(RAND()*20))); set i = i+1; end while a; end $ call insert_phone(21);内连接三表连接
mysql> explain select * from class inner join book b on class.card = b.card inner join phone p on b.card = p.card; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 29 | 100.00 | NULL | | 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 29 | 10.00 | Using where; Using join buffer (hash join) | | 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 50 | 10.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ 3 rows in set, 1 warning (0.00 sec)
type 全为 all
使用Using join buffer 缓冲流
添加phone和book的索引mysql> alter table `phone` add index Z(`card`);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> alter table `book` add index Y(`card`);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0再次explain 发现b表和p表的type变为了ref，rows优化了，总行数变少，因此索引最好设置再需要经常查询的字段中
mysql> explain select * from class inner join book b on class.card = b.card inner join phone p on b.card = p.card; +----+-------------+-------+------------+------+---------------+------+---------+--------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+--------------------+------+----------+-------------+ | 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 3028 | 100.00 | NULL | | 1 | SIMPLE | b | NULL | ref | Y | Y | 4 | mysqljj.class.card | 151 | 100.00 | Using index | | 1 | SIMPLE | p | NULL | ref | Z | Z | 4 | mysqljj.class.card | 152 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+--------------------+------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec)join语句的优化
尽可能减少join语句中的NestedLoop的循环次数，永远用小结果集驱动大的结果集
个人理解：因为大的结果集运用join的时候他的不能全表扫描all ，这样大大浪费时间和cpu内存，所以要用在大的结果集上加索引，用小的去join结果集大的。
浙公网安备 33010602011771号