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的学习