mysql学习（三）

1.一个表中可以都多少个索引？　　--可以有多个，具体还是用户自己去决定

2.如果一个表中有3个索引，那么数据要存储几份？

　　---无论索引有多少个，数据都只存储一份。

3,。如果数据只存储一份的话，那么其他的索引是如何检测到数据的呢？　　

　　--回表。数据在存储的时候，是必须要与 索引存储在一起的，在innodb中的存储引擎中，如果表中有主键，则数据就和主键绑定在一起，如果没有主键，则与唯一键在一起，如果没有唯一键，则会生成一个6字节的rowid，数据和rowid在一起，无论如何，都是需要一个索引列的。其他的非数据所在索引列的b+树中，叶子结点存放的数据都是数据所在索引的key值。

使用mysql所有都有什么原则？--回表、索引覆盖、最左匹配、索引下推

 

 

 

 

 详细解释如下:

 

mysql的组合索引的结构是什么样的

 

思考题：如下两个表结构以及索引情况

mysql> desc user_t;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int          | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(40)  | NO   | MUL | NULL    |                |
| password  | varchar(255) | NO   |     | NULL    |                |
| age       | int          | NO   |     | NULL    |                |
| likes     | varchar(255) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> desc user_t2;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int          | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(40)  | NO   | MUL | NULL    |                |
| password  | varchar(255) | NO   |     | NULL    |                |
| age       | int          | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> show index from user_t;
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user_t |          0 | PRIMARY         |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user_t |          1 | idx_name_pd_age |            1 | user_name   | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user_t |          1 | idx_name_pd_age |            2 | password    | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user_t |          1 | idx_name_pd_age |            3 | age         | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)

mysql> show index from user_t2;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user_t2 |          0 | PRIMARY         |            1 | id          | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user_t2 |          1 | idx_user_pd_age |            1 | user_name   | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user_t2 |          1 | idx_user_pd_age |            2 | password    | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user_t2 |          1 | idx_user_pd_age |            3 | age         | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)

当用explain查看如下SQL的执行计划时，为啥会有如下的差异：

 

mysql> explain select * from user_t where password='123456' and age=99;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_t | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user_t2 where password='123456' and age=99;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | user_t2 | NULL       | index | idx_user_pd_age | idx_user_pd_age | 893     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

即索引会丢失。原因如下

 

 

 

MVCC的学习