通过实例来理解MySQL索引-tutorial mysql-php.cn

索引的使用首先建立库之后，并创建表，表结构如下： mysql createdatabase test1; Query OK, 0 rowsaffected (0.01 sec) mysql use test1; Database changed mysql createtable yw ( - id int unsigned not nullauto_increment, - c1 int not null default

索引的使用

首先建立库之后，并创建表，表结构如下：

mysql> createdatabase test1;

Query OK, 0 rowsaffected (0.01 sec)

mysql> use test1;

Database changed

mysql> createtable yw (

-> id int unsigned not nullauto_increment,

-> c1 int not null default '0',

-> c2 int not null default '0',

-> c3 int not null default '0',

-> c4 int not null default '0',

-> c5 timestamp not null,

-> c6 varchar(200) not null default '',

-> primary key(id)

-> );

Query OK, 0 rowsaffected (0.01 sec)

导入sql文件

内容如下

[root@mysql_node1test]# cat suoyin_test.sql

drop table yw; #已将刚才创建的库删除了，然后又重新创建了一个库

create table yw (

id int unsigned notnull primary key auto_increment,

c1 int not nulldefault '0',

c2 int not nulldefault '0',

c3 int not nulldefault '0',

c4 int not nulldefault '0',

c5 timestamp notnull,

c6 varchar(200) notnull default ''

);

delimiter $$

drop procedure ifexists `insert_yw` $$

create procedure`insert_yw`(in row_num int )

begin

declare i int default 0;

while i

insert into yw(c1, c2, c3,c4, c5,c6) values(floor(rand()*row_num),floor(rand()*row_num),floor(rand()*row_num),floor(rand()*row_num),now(),repeat('wubx', floor(rand()*20)));

set i = i+1;

END while;

end$$

delimiter ;

#插入300W条数据

callinsert_yw(3000000);

delimiter $$

drop procedure ifexists `update_yw` $$

create procedure`update_yw`(in row_num int )

begin

declare i int default 0;

while i

update yw set c3= floor(rand()*row_num) whereid=i;

set i = i+1;

END while;

end$$

delimiter ;

更改参数

mysql> set globalinnodb_flush_log_at_trx_commit=2

导入数据表

mysql> source/root/test/suoyin.sql

Query OK, 0 rowsaffected (0.11 sec)

Query OK, 0 rowsaffected (0.01 sec)

Query OK, 0 rowsaffected (0.00 sec)

Query OK, 1 row affected(4 min 20.75 sec)

Query OK, 0 rowsaffected (0.00 sec)

我们会发现导入很慢，当然300W条数据也不小，所以我们的问题来了：

为什么这个查询这么慢？

mysql> select *from yw a, (select c2 from yw where id=10) b where a.c2 =b.c2;

+---------+---------+--------+---------+---------+---------------------+------------------------------------------------------------------+--------+

| id | c1 | c2 | c3 | c4 | c5 | c6 | c2 |

+---------+---------+--------+---------+---------+---------------------+------------------------------------------------------------------+--------+

| 10 | 2833881 | 185188 | 1424297 | 565924 | 2014-09-24 14:30:31 |wubxwubxwubxwubxwubxwubxwubxwubxwubxwubx | 185188 |

| 1530223 | 1345871 |185188 | 2888330 | 1886085 | 2014-09-24 14:32:44 | wubxwubxwubxwubxwubx |185188 |

| 1623964 | 1289414 |185188 | 57699 | 2732932 | 2014-09-2414:32:52 | wubxwubxwubxwubxwubxwubxwubxwubxwubx | 185188 |

| 2825263 | 729557 | 185188 | 1737273 | 2130798 |2014-09-24 14:34:37 | wubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubx| 185188 |

+---------+---------+--------+---------+---------+---------------------+------------------------------------------------------------------+--------+

4 rows in set (7.28 sec)

经过最后查看，显示的是7.28秒执行完成，一个很简单的查询但是执行完后会很慢，

那么这里我们看到一个2825263, 那么我们将sql改为一个简单的sql并查看

这是一个非常简单的sql，如果在有索引的300w的数据，应该是非常快的，但实际上的表结构跑这样的sql还是很慢的，如下所示，总共用了7.96秒，如下所示：

mysql> select * from yw where c1 = 2825263 ;

+---------+---------+---------+---------+---------+---------------------+------------------------------------------------------+

| id | c1 | c2 | c3 | c4 | c5 | c6 |

+---------+---------+---------+---------+---------+---------------------+------------------------------------------------------+

| 1421241 | 2825263 |2015825 | 1603339 | 1969218 | 2014-09-24 14:32:35 |wubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubx |

+---------+---------+---------+---------+---------+---------------------+------------------------------------------------------+

1 row in set (7.96 sec)

之所以慢，是因为在全表扫描而造成的

这种情况下可以对其进行添加索引进行优化

再次追加300万条数据

使用call insert_表名进行添加

mysql> call insert_yw(3000000);

Query OK, 1 rowaffected (4 min 21.74 sec)

大概在7分钟将索引添加将300万条数据加载完毕

PS：在生产环境中都要模拟百万条的数据去进行测试

创建完后查看索引大小，大概476M左右

[root@mysql_node1test1]# ll -th

总用量 477M

-rw-rw----. 1 mysqlmysql 476M 9月 24 14:49 yw.ibd

-rw-rw----. 1 mysqlmysql 8.6K 9月 24 14:30 yw.frm

-rw-rw----. 1 mysqlmysql 61 9月 24 14:00 db.opt

mysql> desc select* from yw a, (select c2 from yw where id=10) b where a.c2 =b.c2;

+----+-------------+------------+--------+---------------+---------+---------+------+---------+-------------+

+----+-------------+------------+--------+---------------+---------+---------+------+---------+-------------+

| 2 | DERIVED | yw | const | PRIMARY | PRIMARY | 4 | | 1 | |

+----+-------------+------------+--------+---------------+---------+---------+------+---------+-------------+

3 rows in set (0.00sec)

索引的使用

索引的简介

索引实际上是Btree结构

有些生产环境上尤其是在主从环境下用不到索引的，从而使得主从延迟，当发现从库延迟，要先去定位是否是从库上有sql写入的时间是否没有用到索引，如果是的话则加索引即可

这类情况在排查主从结构的时候特别多

而delete也是支持索引的，如果不进行索引，那么也会进行全表扫描

比如在某场景下我们要批量删除大量数据，通常建议使用工具或存储过程去分段（批量）删除数据，比如：

deletefrom tb where addtime&get;xxxx and addtime

使用这样的语句去按段删除

通过索引可以让update selecet delete 都可以实现到加速，但添加索引的话对写入影响较重

主建是不能去执行update的，生产中是不应该对update做索引的因为update会将表重新组织一遍并进行btree重排序，所以会非常慢

创建索引，并将其进行对比

将之前创建的表改名并添加新所索引

mysql> renametable yw to yw_1;

ERROR 2006 (HY000):MySQL server has gone away

No connection. Tryingto reconnect...

Connection id: 12

Current database:test1

Query OK, 0 rowsaffected (0.11 sec)

mysql> showtables;

+-----------------+

| Tables_in_test1 |

+-----------------+

| yw_1 |

+-----------------+

1 row in set (0.00sec)

新建表

create table yw (

id int unsigned not null auto_increment,

c1 int not null default '0',

c2 int not null default '0',

c3 int not null default '0',

c4 int not null default '0',

c5 timestamp not null,

c6 varchar(200) not null default '',

primary key(`id`),

KEY `idx_c2`(`c2`),

key `idx_c3`(`c3`)

);

Query OK, 0 rowsaffected (0.03 sec)

查看表结构

mysql> desc yw;

+-------+------------------+------+-----+-------------------+-----------------------------+

+-------+------------------+------+-----+-------------------+-----------------------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| c1 | int(11) | NO | | 0 | |

| c2 | int(11) | NO | MUL | 0 | |

| c3 | int(11) | NO | MUL | 0 | |

| c4 | int(11) | NO | | 0 | |

| c6 | varchar(200) | NO | | | |

+-------+------------------+------+-----+-------------------+-----------------------------+

7 rows in set (0.07sec)

[root@mysql_node1test]# cat 2.sql

delimiter $$

drop procedure ifexists `update_yw` $$

create procedure`update_yw`(in row_num int )

begin

declare i int default 0;

while i

update yw set c3=floor(rand()*row_num) where id=i;

set i = i+1;

END while;

end$$

delimiter ;

导入

mysql> source/root/test/2.sql

Query OK, 0 rowsaffected (0.00 sec)

再次插入300W条记录，查看用时时间

mysql> callinsert_yw(3000000);

Query OK, 1 rowaffected (8 min 11.57 sec)

将之前备份的表还原并再次执行，这里插入一百万条数据

mysql> renametable yw to yw_idx;

Query OK, 0 rowsaffected (0.06 sec)

mysql> renametable yw_1 to yw;

Query OK, 0 rowsaffected (0.01 sec)

mysql> showtables;

+-----------------+

| Tables_in_test1 |

+-----------------+

| yw |

| yw_idx |

+-----------------+

2 rows in set (0.00sec)

这样一个是带索引，一个是不带索引的

再次调用包含索引的结构

mysql> call update_yw(3000000);

Query OK, 1 rowaffected (4 min 32.31 sec)

与之对比如下：

表名	是否所用索引	执行过程所耗时间
yw	否	11.57 sec
yw_idx	是	32.31 sec

两者间速度相差3倍左右

如果存在c3的索引的话，那么执行以下sql语句：

select c3 from yw where id=1;

发现同样是很慢的，因为在发生更新第三列的时候同时需要做索引的维护

索引同样是Btree结构，如果发生任何变更的时候，会将Btree更新，重新排序，这样就会重新开销所以会慢

MySQL支持什么样的索引？

一般来讲都5类

普通索引，唯一索引，主建，组合索引，全文索引（mysql5.6的特性）

全文搜索第三方工具：sphinx

创建索引：

create index idx_xxx ontb(xxx);

更新索引：

alter table tb add indexidx_xxx(xxx);

删除索引：

DROP [ONLINE|OFFLINE]INDEX index_name ON tbl_name

普通索引一般包含前缀索引，如果前端部分很长可以建立前缀索引（前字符区分开来，减少一下索引的长度，让扫起来更省点IO），如下所示：

alter table yw add indxidx_c6_6(c6(6));

普通索引是的列是可以NULL的

唯一索引：

在设计中属于一种约束，在使用中，设置字段唯一的，或者是联合索引

例：

select * from table_nameidx_xxx=xxx;

如果在普通索引中，在记录中探测到下下条再判断是否是需要的记录，如果是则返回，所以普通索引是要往下多度几次，这是普通索引的开销

但唯一索引，只做等于匹配，不会再往下进行，其好处是比较节省IO，

唯一索引列可以允许有NULL,但只能有一个

主建

Innodb里聚集class index key以为所有的数据以主建排序存储

主建是不允许有null列的

组合索引（联合索引）：

也被称为

select * from yw where c4=XXXX order by c3;

使用explain查看执行性能

mysql> explainselect * from yw_idx where c3=251609 order by c4;

+----+-------------+--------+------+---------------+--------+---------+-------+------+-----------------------------+

+----+-------------+--------+------+---------------+--------+---------+-------+------+-----------------------------+

+----+-------------+--------+------+---------------+--------+---------+-------+------+-----------------------------+

1 row in set (0.03sec)

实际执行sql还是很慢，key_len为4,但是还会很慢，这种sql是忽悠人的，但是实际上possible_keys里面是没有东西的，这属于一种欺骗性的所以需要注意

使用where条件判断

如果是前缀索引如果用到了c3 是否还可以继续调用c4字段