mysql 索引例子_MySQL索引简单例子

建表

# index_a表只有主键，无其他索引

mysql> create table index_a (

-> id int unsigned not null auto_increment,

-> title varchar(64) not null,

-> name varchar(16) not null,

-> stock int not nul default 0,

-> create_time int not null default 0,

-> primary key (id)

-> );

Query OK, 0 rows affected (0.36 sec)

# index_b表有主键和单列索引

mysql> create table index_b (

-> id int unsigned not null auto_increment,

-> title varchar(64) not null,

-> name varchar(16) not null,

-> sn varchar(12) not null default '',

-> stock int not null default 0,

-> create_time int not null default 0,

-> primary key (id),

-> unique key name(name) using btree,

-> unique key sn(sn) using btree

-> );

Query OK, 0 rows affected (0.58 sec)

# index_c表有主键和组合索引

mysql> create table index_c (

-> id int unsigned not null auto_increment,

-> title varchar(64) not null,

-> name varchar(16) not null,

-> sn varchar(12) not null default '',

-> stock int not null default 0,

-> create_time int not null default 0,

-> primary key (id),

-> KEY name_sn_index(name, sn) using btree

-> );

Query OK, 0 rows affected (0.48 sec)

PHP脚本插入数据

// 三个表分别插入100w条数据

set_time_limit(0);

// 断掉连接 后台挂起

// fastcgi_finish_request();

$host = 'localhost';

$db = 'samp_db';

$user = 'samp';

$pass = 'samp';

$conn = new PDO('mysql:host='.$host.';dbname='.$db, $user, $pass);

$sth = $conn->beginTransaction();

$sql = 'insert into index_a

(title, name, sn, stock, create_time)

values (?,?,?,?,?)'; # index_a另外换成index_b index_c

$sth = $conn->prepare($sql);

$time = strtotime('2010-01-01');

echo date('H:i:s') . '
';

for ($i = 1; $i < 1000000; $i++) {

// 设置当前第几个

$num = substr($i + 1000000, 1, 6);

// 插入数据

$sth->execute([

'title_' . $num,

'name_' . $num,

'sn_' . $num,

rand(1000, 2000),

$time,

]);

$time += 50;

// 每1w条提交一次

if ($i % 10000 == 0) {

$conn->commit();

$conn->beginTransaction();

}

}

$conn->commit();

echo date('H:i:s');

索引简单测试

# 通过指定name分别查询

# a表name无索引

# b表name有单列索引

# c表name有组合索引

mysql> set profiling=1;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from index_a where name='name_876543';

......

mysql> select * from index_b where name='name_876543';

......

mysql> select * from index_c where name='name_876543';

......

mysql> show profiles;

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

| Query_ID | Duration | Query |

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

| 1 | 1.70158355 | select * from index_a where name='name_876543' |

| 2 | 0.00110591 | select * from index_b where name='name_876543' |

| 3 | 0.02820820 | select * from index_c where name='name_876543' |

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

3 rows in set (0.00 sec)

# 通过title查询

# a b c三表title均无索引

mysql> select * from index_a where title = 'title_456789';

......

mysql> select * from index_b where title = 'title_456789';

......

mysql> select * from index_c where title = 'title_456789';

......

mysql> show profiles; # 只显示相关

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

| Query_ID | Duration | Query |

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

| 4 | 0.51155937 | select * from index_a where title = 'title_456789' |

| 5 | 0.63892961 | select * from index_b where title = 'title_456789' |

| 6 | 0.54366214 | select * from index_c where title = 'title_456789' |

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

6 rows in set (0.00 sec)

# 测试like

# a表name无索引

# b表name有单列索引

# c表name有组合索引

mysql> select * from index_a where name like '%www';

......

mysql> select * from index_b where name like '%www';

......

mysql> select * from index_c where name like '%www';

......

mysql> select * from index_a where name like 'www%';

......

mysql> select * from index_b where name like 'www%';

......

mysql> select * from index_c where name like 'www%';

......

mysql> show profiles;

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

| Query_ID | Duration | Query |

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

| 1 | 3.31670231 | select * from index_a where name like '%www' |

| 2 | 3.24503318 | select * from index_b where name like '%www' |

| 3 | 3.53191539 | select * from index_c where name like '%www' |

| 4 | 3.58034354 | select * from index_a where name like 'www%' |

| 5 | 0.00045643 | select * from index_b where name like 'www%' |

| 6 | 0.00040764 | select * from index_c where name like 'www%' |

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

6 rows in set (0.00 sec)

# 综上: MySQL对于like 'www%'索引有效，对于'%www'索引无效。

# 测试or

# a 无索引

# b name sn都有索引

# c (name, sn) 组合索引

mysql> select * from index_a where name='name_556677' or sn = 'sn_667788';

......

mysql> select * from index_b where name='name_556677' or sn = 'sn_667788';

......

mysql> select * from index_c where name='name_556677' or sn = 'sn_667788';

......

mysql> show profiles;

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

| Query_ID | Duration | Query |

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

| 1 | 0.59950080 | select * from index_a where name='name_556677' or sn = 'sn_667788' |

| 2 | 0.00161993 | select * from index_b where name='name_556677' or sn = 'sn_667788' |

| 3 | 0.60054383 | select * from index_c where name='name_556677' or sn = 'sn_667788' |

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

3 rows in set (0.00 sec)

# 综上: or需要条件都加索引才命中[bc表对比，ac表对比]

mysql> select * from index_a where name in ('name_111111', 'name_333333');

......

mysql> select * from index_b where name in ('name_111111', 'name_333333');

......

mysql> select * from index_c where name in ('name_111111', 'name_333333');

......

2 rows in set (0.00 sec)

mysql> show profiles;

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

| Query_ID | Duration | Query |

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

| 1 | 0.63480784 | select * from index_a where name in ('name_111111', 'name_333333') |

| 2 | 0.00107901 | select * from index_b where name in ('name_111111', 'name_333333') |

| 3 | 0.00122203 | select * from index_c where name in ('name_111111', 'name_333333') |

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

3 rows in set (0.00 sec)

# 综上: in可以命中索引

# 测试范围符号(>,>=,

# 先为index_b的stock加上索引

mysql> alter table index_b add index stock(stock);

Query OK, 0 rows affected (5.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [samp_db]> select count(1) from index_a where stock > 1900;

......

MariaDB [samp_db]> select count(1) from index_b where stock > 1900;

......

MariaDB [samp_db]> select count(1) from index_c where stock > 1900;

......

MariaDB [samp_db]> select count(1) from index_a where stock between 1000 and 1050;

......

MariaDB [samp_db]> select count(1) from index_b where stock between 1000 and 1050;

......

MariaDB [samp_db]> select count(1) from index_c where stock between 1000 and 1050;

......

MariaDB [samp_db]> show profiles;

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

| Query_ID | Duration | Query |

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

| 4 | 1.09335718 | select count(1) from index_a where stock > 1900 |

| 5 | 0.05711385 | select count(1) from index_b where stock > 1900 |

| 6 | 0.31060459 | select count(1) from index_c where stock > 1900 |

| 7 | 0.34946092 | select count(1) from index_a where stock between 1000 and 1050 |

| 8 | 0.03301252 | select count(1) from index_b where stock between 1000 and 1050 |

| 9 | 0.31163826 | select count(1) from index_c where stock between 1000 and 1050 |

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

9 rows in set (0.00 sec)

# 通过时间对比，以及explain，比较符，between可以命中索引[不全面]

# 测试and

MariaDB [samp_db]> select * from index_a where name='name_555666' and sn='sn_777888';

......

MariaDB [samp_db]> select * from index_b where name='name_555666' and sn='sn_777888';

......

MariaDB [samp_db]> select * from index_c where name='name_555666' and sn='sn_777888';

......

MariaDB [samp_db]> show profiles;

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

| Query_ID | Duration | Query |

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

| 1 | 0.61768279 | select * from index_a where name='name_555666' and sn='sn_777888' |

| 2 | 0.00109431 | select * from index_b where name='name_555666' and sn='sn_777888' |

| 3 | 0.00079209 | select * from index_c where name='name_555666' and sn='sn_777888' |

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

3 rows in set (0.00 sec)

# 综上: 单列以及组合所以可以命中 「最左前缀匹配原则」