mysql> #联合索引
mysql> #是指对表上的多个列进行索引,联合索引的创建方法与单个索引的创建方法一样,不同之处仅在于多个所以列
mysql> create table indext(a int, b int, primary key(a), key idx_a_b(a,b))engine=innodb;
ERROR 1046 (3D000): No database selected
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table indext(a int, b int, primary key(a), key idx_a_b(a,b))engine=innodb;
Query OK, 0 rows affected (0.10 sec)
mysql> #此时两个键值就组成了联合索引
mysql> insert into indext values(1,1),(1,2),(2,1),(2,4),(3,1),(3,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into indext values(1,1),(2,2),(3,1),(4,4),(5,1),(6,2);
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from indext;
+---+------+
| a | b |
+---+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 4 |
| 5 | 1 |
| 6 | 2 |
+---+------+
6 rows in set (0.00 sec)
mysql> select * from indext where a=3,b=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b=1' at line 1
mysql> select * from indext where a=3 and b=1;
+---+------+
| a | b |
+---+------+
| 3 | 1 |
+---+------+
1 row in set (0.00 sec)
mysql> #此时a节点递增 可以使用b+索引,b不能
mysql>
mysql> create table buy_log(
-> userid int unsigned not null,
-> buy_date date)engine=innodb;
Query OK, 0 rows affected (0.11 sec)
mysql> insert into buy_log values(1,'2009-01-01'),(2,'2009-01-01'),(3,'2009-01-01'),(1,'2009-02-01'),(3,'2009-02-01'),(1,'2009-03-01'),(1,'2009-04-01');
Query OK, 7 rows affected (0.03 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from buy_log;
+--------+------------+
| userid | buy_date |
+--------+------------+
| 1 | 2009-01-01 |
| 2 | 2009-01-01 |
| 3 | 2009-01-01 |
| 1 | 2009-02-01 |
| 3 | 2009-02-01 |
| 1 | 2009-03-01 |
| 1 | 2009-04-01 |
+--------+------------+
7 rows in set (0.00 sec)
mysql> alter table buy_log add key(userid);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table buy_log add key(userid, buy_date);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from buy_log where userid=1 order by buy_date desc;
+--------+------------+
| userid | buy_date |
+--------+------------+
| 1 | 2009-04-01 |
| 1 | 2009-03-01 |
| 1 | 2009-02-01 |
| 1 | 2009-01-01 |
+--------+------------+
4 rows in set (0.00 sec)
mysql> select * from buy_log where userid=1 order by buy_date desc limit 3;
+--------+------------+
| userid | buy_date |
+--------+------------+
| 1 | 2009-04-01 |
| 1 | 2009-03-01 |
| 1 | 2009-02-01 |
+--------+------------+
3 rows in set (0.00 sec)
mysql> select * from buy_log where userid=1;
+--------+------------+
| userid | buy_date |
+--------+------------+
| 1 | 2009-01-01 |
| 1 | 2009-02-01 |
| 1 | 2009-03-01 |
| 1 | 2009-04-01 |
+--------+------------+
4 rows in set (0.00 sec)
mysql> #在以上两个中 有两个索引 编译器会自动择优选择
mysql>
mysql> #覆盖索引
mysql>
mysql> #方法: select key from table where key1=xxx;
mysql> #select primary key2,key2 from table where key1=xxx;
mysql> #select primary key1,key2 from table where key1=xxx;
mysql> #select primary key1, primary key2,key2 from table where key1=xxx;
mysql>
mysql> select count(*) from buy_log;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
mysql> #force 强制使用索引
mysql> exit
mysql> #是指对表上的多个列进行索引,联合索引的创建方法与单个索引的创建方法一样,不同之处仅在于多个所以列
mysql> create table indext(a int, b int, primary key(a), key idx_a_b(a,b))engine=innodb;
ERROR 1046 (3D000): No database selected
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table indext(a int, b int, primary key(a), key idx_a_b(a,b))engine=innodb;
Query OK, 0 rows affected (0.10 sec)
mysql> #此时两个键值就组成了联合索引
mysql> insert into indext values(1,1),(1,2),(2,1),(2,4),(3,1),(3,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into indext values(1,1),(2,2),(3,1),(4,4),(5,1),(6,2);
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from indext;
+---+------+
| a | b |
+---+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 4 |
| 5 | 1 |
| 6 | 2 |
+---+------+
6 rows in set (0.00 sec)
mysql> select * from indext where a=3,b=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b=1' at line 1
mysql> select * from indext where a=3 and b=1;
+---+------+
| a | b |
+---+------+
| 3 | 1 |
+---+------+
1 row in set (0.00 sec)
mysql> #此时a节点递增 可以使用b+索引,b不能
mysql>
mysql> create table buy_log(
-> userid int unsigned not null,
-> buy_date date)engine=innodb;
Query OK, 0 rows affected (0.11 sec)
mysql> insert into buy_log values(1,'2009-01-01'),(2,'2009-01-01'),(3,'2009-01-01'),(1,'2009-02-01'),(3,'2009-02-01'),(1,'2009-03-01'),(1,'2009-04-01');
Query OK, 7 rows affected (0.03 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from buy_log;
+--------+------------+
| userid | buy_date |
+--------+------------+
| 1 | 2009-01-01 |
| 2 | 2009-01-01 |
| 3 | 2009-01-01 |
| 1 | 2009-02-01 |
| 3 | 2009-02-01 |
| 1 | 2009-03-01 |
| 1 | 2009-04-01 |
+--------+------------+
7 rows in set (0.00 sec)
mysql> alter table buy_log add key(userid);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table buy_log add key(userid, buy_date);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from buy_log where userid=1 order by buy_date desc;
+--------+------------+
| userid | buy_date |
+--------+------------+
| 1 | 2009-04-01 |
| 1 | 2009-03-01 |
| 1 | 2009-02-01 |
| 1 | 2009-01-01 |
+--------+------------+
4 rows in set (0.00 sec)
mysql> select * from buy_log where userid=1 order by buy_date desc limit 3;
+--------+------------+
| userid | buy_date |
+--------+------------+
| 1 | 2009-04-01 |
| 1 | 2009-03-01 |
| 1 | 2009-02-01 |
+--------+------------+
3 rows in set (0.00 sec)
mysql> select * from buy_log where userid=1;
+--------+------------+
| userid | buy_date |
+--------+------------+
| 1 | 2009-01-01 |
| 1 | 2009-02-01 |
| 1 | 2009-03-01 |
| 1 | 2009-04-01 |
+--------+------------+
4 rows in set (0.00 sec)
mysql> #在以上两个中 有两个索引 编译器会自动择优选择
mysql>
mysql> #覆盖索引
mysql>
mysql> #方法: select key from table where key1=xxx;
mysql> #select primary key2,key2 from table where key1=xxx;
mysql> #select primary key1,key2 from table where key1=xxx;
mysql> #select primary key1, primary key2,key2 from table where key1=xxx;
mysql>
mysql> select count(*) from buy_log;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
mysql> #force 强制使用索引
mysql> exit