1、索引-使用规则-验证索引效率失效（最左前缀法则、范围查询）
#索引-使用规则
##索引-使用规则-验证索引效率失效
###1、最左前缀法则：如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃了某一列，索引将部分失效（后面字段索引失效）
##通过以下来看
explain select * from tb_user where profession='软件工程' and age=31 and status='0';
explain select * from tb_user where profession='软件工程' and age=31;
explain select * from tb_user where profession='软件工程' ;
explain select * from tb_user where age=31 and status='0';
explain select * from tb_user where status='0';
##范围查询：联合索引中，出现范围查询(>,<),范围查询右侧的列索引失效
##列子：
explain select * from tb_user where profession ='软件工程' and age>'30' and status='0';
explain select * from tb_user where profession ='软件工程' and age>='30' and status='0';在xshell+xftp+VMware+mysql环境下：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| user |
+--------------------+
5 rows in set (0.00 sec)
mysql> use user;
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> select * from tb_user;
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 2 | 曹操 | 177799990001 | caocao666@qq.com | 应用数学 | 53 | 1 | 0 | 2001-04-03 00:00:00 |
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
| 4 | 兰陵王 | 177799990002 | lanlw666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-19 00:00:00 |
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
5 rows in set (0.03 sec)
mysql> insert into tb_user values(6,'不知道','177799990006','buzdao666@163.com','软件工程','31','1',0,'2001-09-20 00:00:00');
Query OK, 1 row affected (0.01 sec)
mysql> select * from tb_user;
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 2 | 曹操 | 177799990001 | caocao666@qq.com | 应用数学 | 53 | 1 | 0 | 2001-04-03 00:00:00 |
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
| 4 | 兰陵王 | 177799990002 | lanlw666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-19 00:00:00 |
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
6 rows in set (0.00 sec)
mysql> show index from tb_user;
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone | 1 | phone | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_name | 1 | name | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 1 | profession | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 2 | age | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 3 | status | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_email | 1 | email | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.02 sec)
mysql> select * from tb_user where profession='软件工程' and age='31' and status='0';
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)
mysql> explain select * from tb_user where profession='软件工程' and age='31' and status='0';
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status | 208 | const,const,const | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_user where profession='软件工程' and age='31';
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status | 203 | const,const | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_user where profession='软件工程';
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status | 201 | const | 2 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_user where age='31' and status='0';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> ##即可知explain select * from tb_user where age='31' and status='0';不满足最左前缀法则
mysql> ##下面运行的语句也是不满足最左前缀法则的
mysql> explain select * from tb_user where status='0';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_user where profession='软件工程' and status='0';
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status | 201 | const | 2 | 16.67 | Using index condition |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> ###即可以知道explain select * from tb_user where profession='软件工程' and status='0';跳跃了age这个字段，即后面的status字段失效，可以从以上的key_len看出来
mysql> explain select * from tb_user where age='31' and status='0' and profession='软件工程';
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status | 208 | const,const,const | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> ##explain select * from tb_user where age='31' and status='0' and profession='软件工程';说明了最左前缀法则是要求(第一个)字段必须存在，跟其顺序没有关系
mysql> #范围查询
mysql> explain select * from tb_user where profession ='软件工程' and age>'30' and status='0';
+----+-------------+---------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | range | idx_user_pro_age_status | idx_user_pro_age_status | 203 | NULL | 1 | 16.67 | Using index condition |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
mysql> ##在age>'30'处实行了(>,<)的范围查询，所以后面的status索引失效
mysql> explain select * from tb_user where profession ='软件工程' and age>='30' and status='0';
+----+-------------+---------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | range | idx_user_pro_age_status | idx_user_pro_age_status | 208 | NULL | 1 | 16.67 | Using index condition |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)2、索引失效情况一、索引失效情况二
##索引失效情况一
###1、不要在索引列上进行运算操作，索引失效
explain select * from tb_user where substring(phone,11,2)='06';##使用substring截取字符串
##2、字符串类型字段使用时，不加引号，索引将失效
explain select * from tb_user where phone=177799990006;
##3、模糊查询：如果仅仅时尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效
explain select * from tb_user where profession like '软件%';##不会失效
explain select * from tb_user where profession like '%工程';##会失效
explain select * from tb_user where profession like '%工%';##会失效
##索引失效情况二
###1、or连接的条件：用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
explain select * from tb_user where id =5 or age=22;
explain select * from tb_user where phone=' 177799990007' or age=22;
##由于age没有索引，所以即使id、phone有索引，索引也会失效，所以需要针对于age也要建立索引
create index idx_user_age on tb_user(age);##创建索引后，再运行下面两个语句
explain select * from tb_user where id =5 or age=22;
explain select * from tb_user where phone=' 177799990007' or age=22;
###2、数据分布影响：如果MySQL评估使用索引比全表更慢，则不使用索引
explain select * from tb_user where phone>='177799990005';
explain select * from tb_user where phone>='177799990015';
explain select * from tb_user where phone is null;
explain select * from tb_user where phone is not null;##(取决于表中数据的分布）在xshell+xftp+VMware+mysql环境下
mysql> select * from tb_user;
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 2 | 曹操 | 177799990001 | caocao666@qq.com | 应用数学 | 53 | 1 | 0 | 2001-04-03 00:00:00 |
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
| 4 | 兰陵王 | 177799990002 | lanlw666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-19 00:00:00 |
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
6 rows in set (0.00 sec)
mysql> show index from tb_user;
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone | 1 | phone | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_name | 1 | name | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 1 | profession | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 2 | age | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 3 | status | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_email | 1 | email | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.00 sec)
mysql> select * from tb_user where phone='177799990006';
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)
mysql> explain select * from tb_user where phone='177799990006';
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_user | NULL | const | idx_user_phone | idx_user_phone | 201 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> ###查询手机号最后两位是06的
mysql> select * from tb_user where substring(phone,11,2);##使用substring截取字符串
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 2 | 曹操 | 177799990001 | caocao666@qq.com | 应用数学 | 53 | 1 | 0 | 2001-04-03 00:00:00 |
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
| 4 | 兰陵王 | 177799990002 | lanlw666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-19 00:00:00 |
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
5 rows in set (0.00 sec)
mysql> select * from tb_user where substring(phone,11,2)='15';##使用substring截取字符串
Empty set (0.00 sec)
mysql> select * from tb_user where substring(phone,11,2)='06';##使用substring截取字符串
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)
mysql> explain select * from tb_user where substring(phone,11,2)='06';##使用substring截取字符串
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> ##即以上用substring,在索引列上进行运算操作，索引失效
mysql> ##下面演示字符串不加引号(字符串类型字段使用时，不加引号，索引失效
mysql> ##下面演示字符串不加引号(字符串类型字段使用时，不加引号，索引将失效
mysql> select * from tb_user where phone=177799990006;
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)
mysql> explain select * from tb_user where phone=177799990006;
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | idx_user_phone | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
mysql> ##模糊查询：如果仅仅时尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效
mysql> select * from tb_user where profession='软件%';
Empty set (0.00 sec)
mysql> select * from tb_user;
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 2 | 曹操 | 177799990001 | caocao666@qq.com | 应用数学 | 53 | 1 | 0 | 2001-04-03 00:00:00 |
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
| 4 | 兰陵王 | 177799990002 | lanlw666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-19 00:00:00 |
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
6 rows in set (0.00 sec)
mysql> select * from tb_user where profession like '软件%';
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
2 rows in set (0.04 sec)
mysql> explain select * from tb_user where profession like '软件%';
+----+-------------+---------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | range | idx_user_pro_age_status | idx_user_pro_age_status | 201 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_user where profession like '%工程';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select * from tb_user where profession like '%工%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> #or连接的条件：用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
mysql> select * from tb_user;
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 2 | 曹操 | 177799990001 | caocao666@qq.com | 应用数学 | 53 | 1 | 0 | 2001-04-03 00:00:00 |
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
| 4 | 兰陵王 | 177799990002 | lanlw666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-19 00:00:00 |
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
6 rows in set (0.00 sec)
mysql> select * from tb_user where id=5 or age =22;
+----+--------+--------------+--------------------+------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+--------+--------------+--------------------+------------+------+--------+--------+---------------------+
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
+----+--------+--------------+--------------------+------------+------+--------+--------+---------------------+
1 row in set (0.01 sec)
mysql> insert into tb_user values(7,'无名','177799990007','wuming666@sina,com','软件工程','22','2',0,'2001-09-08');
Query OK, 1 row affected (0.01 sec)
mysql> select * from tb_user;
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 2 | 曹操 | 177799990001 | caocao666@qq.com | 应用数学 | 53 | 1 | 0 | 2001-04-03 00:00:00 |
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
| 4 | 兰陵王 | 177799990002 | lanlw666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-19 00:00:00 |
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
| 7 | 无名 | 177799990007 | wuming666@sina,com | 软件工程 | 22 | 2 | 0 | 2001-09-08 |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
7 rows in set (0.01 sec)
mysql> select * from tb_user where id =5 or age=22;
+----+--------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+--------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
| 7 | 无名 | 177799990007 | wuming666@sina,com | 软件工程 | 22 | 2 | 0 | 2001-09-08 |
+----+--------+--------------+--------------------+--------------+------+--------+--------+---------------------+
2 rows in set (0.00 sec)
mysql> explain select * from tb_user where id =5 or age=22;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | PRIMARY | NULL | NULL | NULL | 7 | 26.53 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_user where phone=' 177799990007' or age=22;
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ALL | idx_user_phone | NULL | NULL | NULL | 7 | 26.53 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> ###上面演示两个or的语句，索引失效的原因是age没有索引
mysql> ##由于age没有索引，所以即使id、phone有索引，索引也会失效，所以需要针对于age也要建立索引
mysql> create index idx_user_age on tb_user(age);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from tb_user where phone=' 177799990007' or age=22;
+----+-------------+---------+------------+-------------+-----------------------------+-----------------------------+---------+------+------+----------+-------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+-----------------------------+-----------------------------+---------+------+------+----------+-------------------------------------------------------+
| 1 | SIMPLE | tb_user | NULL | index_merge | idx_user_phone,idx_user_age | idx_user_phone,idx_user_age | 201,2 | NULL | 3 | 100.00 | Using union(idx_user_phone,idx_user_age); Using where |
+----+-------------+---------+------------+-------------+-----------------------------+-----------------------------+---------+------+------+----------+-------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_user where id =5 or age=22;
+----+-------------+---------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
| 1 | SIMPLE | tb_user | NULL | index_merge | PRIMARY,idx_user_age | PRIMARY,idx_user_age | 4,2 | NULL | 3 | 100.00 | Using union(PRIMARY,idx_user_age); Using where |
+----+-------------+---------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql>3、SQL提示
##索引-使用规则-SQL提示
##SQL提示：SQL提示，是优化数据库的一个重要手段，就是在SQL语句中加入一些为人的提示来达到优化操作的目的
use index:##告诉数据库用哪个索引(建议)
explain select * from tb_user use index(idx_user_pro) where profession='软件工程';
ignore index:##告诉数据库不要哪个索引
explain select * from tb_user ignore index(idx_user_pro) where profession='软件工程';
force index:##告诉数据库必须用哪个索引(强制)
explain select * from tb_user force index(idx_user_pro) where profession='软件工程';在xshell+xftp+VMware+mysql环境下
mysql> select * from tb_user;
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 2 | 曹操 | 177799990001 | caocao666@qq.com | 应用数学 | 53 | 1 | 0 | 2001-04-03 00:00:00 |
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
| 4 | 兰陵王 | 177799990002 | lanlw666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-19 00:00:00 |
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
| 7 | 无名 | 177799990007 | wuming666@sina,com | 软件工程 | 22 | 2 | 0 | 2001-09-08 |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
7 rows in set (0.00 sec)
mysql> show index from tb_user;
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone | 1 | phone | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_name | 1 | name | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 1 | profession | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 2 | age | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 3 | status | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_email | 1 | email | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_age | 1 | age | A | 6 | NULL | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
8 rows in set (0.01 sec)
mysql> create index idx_user_pro on tb_user(profession);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from tb_user use index(idx_user_pro) where profession='软件工程';
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro | idx_user_pro | 201 | const | 3 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select * from tb_user ignore index(idx_user_pro) where profession='软件工程';
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status | 201 | const | 3 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_user ignore index(idx_user_pro_age_status) where profession='软件工程';
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro | idx_user_pro | 201 | const | 3 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_user force index(idx_user_pro) where profession='软件工程';
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro | idx_user_pro | 201 | const | 3 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)4、覆盖索引&回表查询
##索引-使用规则-覆盖索引&回表查询
###覆盖索引：尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select*
explain select id,profession from tb_user where profession='软件工程' and age='31' and status=0;
explain select id,profession,age,status from tb_user where profession='软件工程' and age='31' and status=0;
explain select id,profession,age,status,name from tb_user where profession='软件工程' and age='31' and status=0;
explain select * from tb_user where profession='软件工程' and age='31' and status=0;
###特别重视：
using index condition:查找使用了索引，但是需要回表查询数据
using where,using index:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据在xshell+xftp+VMware+mysql环境下
mysql> drop index idx_user_age on tb_user;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index idx_user_pro on tb_user;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index idx_user_email on tb_user;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from tb_user;
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone | 1 | phone | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_name | 1 | name | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 1 | profession | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 2 | age | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 3 | status | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.01 sec)
mysql> select id,profession from tb_user where profession='软件工程' and age='31' and status=0;
+----+--------------+
| id | profession |
+----+--------------+
| 6 | 软件工程 |
+----+--------------+
1 row in set (0.00 sec)
mysql> select * from tb_user where profession='软件工程' and age='31' and status=0;
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
+----+-----------+--------------+-------------------+--------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)
mysql> explain select * from tb_user where profession='软件工程' and age='31' and status=0;
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status | 208 | const,const,const | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select id,profession from tb_user where profession='软件工程' and age='31' and status=0;
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+--------------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status | 208 | const,const,const | 1 | 100.00 | Using where; Using index |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select id,profession,age,status from tb_user where profession='软件工程' and age='31' and status=0;
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+--------------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status | 208 | const,const,const | 1 | 100.00 | Using where; Using index |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select id,profession,age from tb_user where profession='软件工程' and age='31' and status=0;
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+--------------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status | 208 | const,const,const | 1 | 100.00 | Using where; Using index |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select id,profession,age,status,name from tb_user where profession='软件工程' and age='31' and status=0;
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status | 208 | const,const,const | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)5、前缀索引、单列&联合索引
##索引-使用规则-前缀索引
###前缀索引：当字段类型为字符串(varchar,text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时。浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样就可以大大节约索引空间，从而提高索引效率
###语法：create index idx_XXXX on table_name(column(n));
###前缀的长度可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择是1，这是最好的索引选择性，性能也是最好的
select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email,1,5)) /count(*) from tb_user;
##索引-使用规则-单列&联合索引
###单列索引与联合索引
####单列索引：即一个索引只包含单个列
####联合索引：即一个索引包含了多个列
explain select id,phone,name from tb_user use index(idx_phone_name) where phone='177799990003' and name='赵云';
####在业务场景中，如果存在多个查询条件，考虑对于查询字段建立索引时，建议建立联合索引，而非单列索引
####多条件联合查询时，MySQL优化器会评估哪个字段的索引效率高，会选择该索引完成本次查询（特别注重）在xshell+xftp+VMware+mysql环境下
mysql> select * from tb_user;
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 2 | 曹操 | 177799990001 | caocao666@qq.com | 应用数学 | 53 | 1 | 0 | 2001-04-03 00:00:00 |
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
| 4 | 兰陵王 | 177799990002 | lanlw666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-19 00:00:00 |
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
| 7 | 无名 | 177799990007 | wuming666@sina,com | 软件工程 | 22 | 2 | 0 | 2001-09-08 |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
7 rows in set (0.01 sec)
mysql> insert into tb_user values(8,'郝佳','177799990008','177799990@139.com','计算机','34','1',1,'2001-05-07 00:00:00');
Query OK, 1 row affected (0.06 sec)
mysql> select * from tb_user;
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 2 | 曹操 | 177799990001 | caocao666@qq.com | 应用数学 | 53 | 1 | 0 | 2001-04-03 00:00:00 |
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
| 4 | 兰陵王 | 177799990002 | lanlw666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-19 00:00:00 |
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
| 6 | 不知道 | 177799990006 | buzdao666@163.com | 软件工程 | 31 | 1 | 0 | 2001-09-20 00:00:00 |
| 7 | 无名 | 177799990007 | wuming666@sina,com | 软件工程 | 22 | 2 | 0 | 2001-09-08 |
| 8 | 郝佳 | 177799990008 | 177799990@139.com | 计算机 | 34 | 1 | 1 | 2001-05-07 00:00:00 |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
8 rows in set (0.00 sec)
mysql> select count(*) from tb_user;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.06 sec)
mysql> select count(email) from tb_user;
+--------------+
| count(email) |
+--------------+
| 8 |
+--------------+
1 row in set (0.00 sec)
mysql> select count(distinct email) from tb_user;
+-----------------------+
| count(distinct email) |
+-----------------------+
| 7 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select count(distinct email)/count(*) from tb_user;
+--------------------------------+
| count(distinct email)/count(*) |
+--------------------------------+
| 0.8750 |
+--------------------------------+
1 row in set (0.01 sec)
mysql> select count(distinct substring(email,1,11))/count(*) from tb_user;
+------------------------------------------------+
| count(distinct substring(email,1,11))/count(*) |
+------------------------------------------------+
| 0.8750 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select count(distinct substring(email,1,10))/count(*) from tb_user;
+------------------------------------------------+
| count(distinct substring(email,1,10))/count(*) |
+------------------------------------------------+
| 0.8750 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select count(distinct substring(email,1,12))/count(*) from tb_user;
+------------------------------------------------+
| count(distinct substring(email,1,12))/count(*) |
+------------------------------------------------+
| 0.8750 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select count(distinct substring(email,1,9))/count(*) from tb_user;
+-----------------------------------------------+
| count(distinct substring(email,1,9))/count(*) |
+-----------------------------------------------+
| 0.8750 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select count(distinct substring(email,1,4))/count(*) from tb_user;
+-----------------------------------------------+
| count(distinct substring(email,1,4))/count(*) |
+-----------------------------------------------+
| 0.8750 |
+-----------------------------------------------+
1 row in set (0.01 sec)
mysql> show index from tb_user;
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone | 1 | phone | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_name | 1 | name | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 1 | profession | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 2 | age | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 3 | status | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.02 sec)
mysql> create index idx_email_5 on tb_user(email(5));
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from tb_user;
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone | 1 | phone | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_name | 1 | name | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 1 | profession | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 2 | age | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 3 | status | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_email_5 | 1 | email | A | 7 | 5 | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.04 sec)
mysql> select * from tb_user where email='daqiao666@sina.com';
+----+--------+--------------+--------------------+------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+--------+--------------+--------------------+------------+------+--------+--------+---------------------+
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
+----+--------+--------------+--------------------+------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)
mysql> explain select * from tb_user where email='daqiao666@sina.com';
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_email_5 | idx_email_5 | 21 | const | 1 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show index from tb_user;
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone | 1 | phone | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_name | 1 | name | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 1 | profession | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 2 | age | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 3 | status | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_email_5 | 1 | email | A | 7 | 5 | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.01 sec)
mysql> select id,phone,name from tb_user where phone='177799990003' and name='赵云';
+----+--------------+--------+
| id | phone | name |
+----+--------------+--------+
| 3 | 177799990003 | 赵云 |
+----+--------------+--------+
1 row in set (0.01 sec)
mysql> explain select id,phone,name from tb_user where phone='177799990003' and name='赵云';
+----+-------------+---------+------------+-------+------------------------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+------------------------------+----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_user | NULL | const | idx_user_phone,idx_user_name | idx_user_phone | 201 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+------------------------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
mysql> create index idx_phone_name on tb_user(phone,name);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index idx_phone_name on tb_user;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create unique index idx_phone_name on tb_user(phone,name);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select id,phone,name from tb_user where phone='177799990003' and name='赵云';
+----+-------------+---------+------------+-------+---------------------------------------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------------------------------------+----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_user | NULL | const | idx_user_phone,idx_phone_name,idx_user_name | idx_user_phone | 201 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------------------------------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select id,phone,name from tb_user use index(idx_phone_name) where phone='177799990003' and name='赵云';
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | tb_user | NULL | const | idx_phone_name | idx_phone_name | 402 | const,const | 1 | 100.00 | Using index |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>6、索引设计原则
##索引-使用规则-索引设计原则
/*
1、针对于数据量较大，且查询比较频繁的表建立索引
2、针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
3、尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
4、如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
5、尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
6、要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
7、如果索引列不能存储NLULL值，请在创建表时使用NOT NULL 约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地
*/本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
