1、前綴索引

當(dāng)字段類型為字符串（varchar，text等）時，有時候需要索引很長的字符串，這會讓索引變得很大，查詢時，浪費(fèi)大量的磁盤IO，影響查詢效率。此時可以只將字符串的一部分前綴，建立索引，這樣可以大大節(jié)約索引空間，從而提高索引效率。

create index idx_xxxx on table_name(column(n))

2、前綴長度

可以根據(jù)索引的選擇性來決定，而選擇性是指不重復(fù)的索引值（基數(shù)）和數(shù)據(jù)表的記錄總數(shù)的比值，索引選擇性越高則查詢效率越高，唯一索引的選擇性是1，這是最好的索引選擇性，性能頁是最好的。

select count(distinct email)/count(*) from tb_user;
select count(distinct substring(email,1,5))/count(*) from tb_user;

3、查詢表數(shù)據(jù)

mysql> select * from tb_user;
+----+--------+-------------+-----------------------+----------------------+------+--------+--------+---------------------+
| id | name   | phone       | email                 | profession           | age  | gender | status | createtime          |
+----+--------+-------------+-----------------------+----------------------+------+--------+--------+---------------------+
|  1 | 呂布   | 17799990000 | lvbu666@163.com       | 軟件工程             |   23 | 1      | 6      | 2001-02-02 00:00:00 |
|  2 | 曹操   | 17799990001 | caocao666@qq.com      | 通訊工程             |   33 | 1      | 0      | 2001-03-05 00:00:00 |
|  3 | 趙云   | 17799990002 | 17799990@139.com      | 英語                 |   34 | 1      | 2      | 2002-03-02 00:00:00 |
|  4 | 孫悟空 | 17799990003 | 17799990@sina.com     | 工程造價             |   54 | 1      | 0      | 2001-07-02 00:00:00 |
|  5 | 花木蘭 | 17799990004 | 19980729@sina.com     | 軟件工程             |   23 | 2      | 1      | 2001-04-22 00:00:00 |
|  6 | 大喬   | 17799990005 | daqiao666@sina.com    | 舞蹈                 |   22 | 2      | 0      | 2001-02-07 00:00:00 |
|  7 | 露娜   | 17799990006 | luna_love@sina.com    | 應(yīng)用數(shù)學(xué)             |   24 | 2      | 0      | 2001-02-08 00:00:00 |
|  8 | 程咬金 | 17799990007 | chengyaojin@163.com   | 化工                 |   38 | 1      | 5      | 2001-05-23 00:00:00 |
|  9 | 項羽   | 17799990008 | xiaoyu666@qq.com      | 金屬材料             |   43 | 1      | 0      | 2001-09-18 00:00:00 |
| 10 | 白起   | 17799990009 | baiqi666@sina.com     | 機(jī)械工程及其自動
化 |   27 | 1      | 2      | 2001-08-16 00:00:00 |
| 11 | 韓信   | 17799990010 | hanxin520@163.com     | 無機(jī)非金屬材料工
程 |   27 | 1      | 0      | 2001-06-12 00:00:00 |
| 12 | 荊軻   | 17799990011 | jingke123@163.com     | 會計                 |   29 | 1      | 0      | 2001-05-11 00:00:00 |
| 13 | 蘭陵王 | 17799990012 | lanlinwang666@126.com | 工程造價             |   44 | 1      | 1      | 2001-04-09 00:00:00 |
| 14 | 狂鐵   | 17799990013 | kuangtie@sina.com     | 應(yīng)用數(shù)學(xué)             |   43 | 1      | 2      | 2001-04-10 00:00:00 |
| 15 | 貂蟬   | 17799990014 | 84958948374@qq.com    | 軟件工程             |   40 | 2      | 3      | 2001-02-12 00:00:00 |
| 16 | 妲己   | 17799990015 | 2783238293@qq.com     | 軟件工程             |   31 | 2      | 0      | 2001-01-30 00:00:00 |
| 17 | 羋月   | 17799990016 | xiaomin2001@sina.com  | 工業(yè)經(jīng)濟(jì)             |   35 | 2      | 0      | 2000-05-03 00:00:00 |
| 18 | 嬴政   | 17799990017 | 8839434342@qq.com     | 化工                 |   38 | 1      | 1      | 2001-08-08 00:00:00 |
| 19 | 狄仁杰 | 17799990018 | jujiamlm8166@163.com  | 國際貿(mào)易             |   30 | 1      | 0      | 2007-03-12 00:00:00 |
| 20 | 安琪拉 | 17799990019 | jdodm1h@126.com       | 城市規(guī)劃             |   51 | 2      | 0      | 2001-08-15 00:00:00 |
| 21 | 典韋   | 17799990020 | ycaunanjian@163.com   | 城市規(guī)劃             |   52 | 1      | 2      | 2000-04-12 00:00:00 |
| 22 | 廉頗   | 17799990021 | lianpo321@126.com     | 土木工程             |   19 | 1      | 3      | 2002-07-18 00:00:00 |
| 23 | 后羿   | 17799990022 | altycj2000@139.com    | 城市園林             |   20 | 1      | 0      | 2002-03-10 00:00:00 |
| 24 | 姜子牙 | 17799990023 | 37483844@qq.com       | 工程造價             |   29 | 1      | 4      | 2003-05-26 00:00:00 |
+----+--------+-------------+-----------------------+----------------------+------+--------+--------+---------------------+
24 rows in set (0.00 sec)mysql>

4、查詢表的記錄總數(shù)

mysql> select count(*) from tb_user;
+----------+
| count(*) |
+----------+
|       24 |
+----------+
1 row in set (0.00 sec)mysql>

5、計算并返回具有電子郵件地址（email）的用戶的數(shù)量

mysql> select count(email) from tb_user;
+--------------+
| count(email) |
+--------------+
|           24 |
+--------------+
1 row in set (0.00 sec)mysql>

6、從tb_user表中計算并返回具有不同電子郵件地址的用戶的數(shù)量

mysql> select count(distinct email) from tb_user;
+-----------------------+
| count(distinct email) |
+-----------------------+
|                    24 |
+-----------------------+
1 row in set (0.00 sec)mysql>

7、計算唯一電子郵件地址（email）的比例相對于表中的總行數(shù)

mysql> select count(distinct email)/count(*) from tb_user;
+--------------------------------+
| count(distinct email)/count(*) |
+--------------------------------+
|                         1.0000 |
+--------------------------------+
1 row in set (0.00 sec)mysql>

• 其中1表示所有用戶都有唯一的電子郵件地址，而0表示沒有用戶有電子郵件地址（盡管這在現(xiàn)實中不太可能）。
• 用來衡量 email 字段的去重比例，即表示不重復(fù)的 email 占總記錄數(shù)的比例。
• 用來評估數(shù)據(jù)中電子郵件地址的唯一性程度，或者說檢測是否存在大量的重復(fù)郵箱賬戶。如果結(jié)果接近1，說明幾乎每個行都有一個唯一的電子郵件地址；如果遠(yuǎn)小于1，則表示有較多的電子郵件地址重復(fù)。

8、從每個電子郵件地址中提取前10個字符，并計算這些前10個字符唯一值的數(shù)量與總用戶數(shù)量的比率。

mysql> select count(distinct substring(email,1,10))/count(*) from tb_user;
+------------------------------------------------+
| count(distinct substring(email,1,10))/count(*) |
+------------------------------------------------+
|                                         1.0000 |
+------------------------------------------------+
1 row in set (0.00 sec)mysql>

9、電子郵件地址的前9個字符的唯一值的數(shù)量與總用戶數(shù)量的比率

mysql> select count(distinct substring(email,1,9))/count(*) from tb_user;
+-----------------------------------------------+
| count(distinct substring(email,1,9))/count(*) |
+-----------------------------------------------+
|                                        0.9583 |
+-----------------------------------------------+
1 row in set (0.00 sec)mysql>

10、電子郵件地址的前8個字符與前9個字符在唯一性方面的表現(xiàn)是相似的

mysql> select count(distinct substring(email,1,8))/count(*) from tb_user;
+-----------------------------------------------+
| count(distinct substring(email,1,8))/count(*) |
+-----------------------------------------------+
|                                        0.9583 |
+-----------------------------------------------+
1 row in set (0.00 sec)mysql>

11、前 6 個字符的不重復(fù)數(shù)量占總行數(shù)的比例

mysql> select count(distinct substring(email,1,6))/count(*) from tb_user;
+-----------------------------------------------+
| count(distinct substring(email,1,6))/count(*) |
+-----------------------------------------------+
|                                        0.9583 |
+-----------------------------------------------+
1 row in set (0.00 sec)mysql>

12、前 5 個字符的不重復(fù)數(shù)量占總行數(shù)的比例

mysql> select count(distinct substring(email,1,5))/count(*) from tb_user;
+-----------------------------------------------+
| count(distinct substring(email,1,5))/count(*) |
+-----------------------------------------------+
|                                        0.9583 |
+-----------------------------------------------+
1 row in set (0.00 sec)mysql>

13、隨著截取長度的減少，電子郵件地址前綴的唯一性也在減少

mysql> select count(distinct substring(email,1,4))/count(*) from tb_user;
+-----------------------------------------------+
| count(distinct substring(email,1,4))/count(*) |
+-----------------------------------------------+
|                                        0.9167 |
+-----------------------------------------------+
1 row in set (0.00 sec)mysql>

14、查看MySQL中tb_user表的索引

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         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            2 | age         | A         |          22 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            3 | status      | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)mysql>

15、在tb_user表的email列上創(chuàng)建一個前綴索引，其中只包括email列的前5個字符

mysql> create index idx_email_5 on tb_user(email(5));
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> 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         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            2 | age         | A         |          22 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            3 | status      | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_email_5          |            1 | email       | A         |          23 |        5 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.01 sec)mysql>

16、查詢 email=‘daqiao666@sina.com’ 的用戶

mysql> select * from tb_user where email='daqiao666@sina.com';
+----+------+-------------+--------------------+------------+------+--------+--------+---------------------+
| id | name | phone       | email              | profession | age  | gender | status | createtime          |
+----+------+-------------+--------------------+------------+------+--------+--------+---------------------+
|  6 | 大喬 | 17799990005 | daqiao666@sina.com | 舞蹈       |   22 | 2      | 0      | 2001-02-07 00:00:00 |
+----+------+-------------+--------------------+------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)mysql>

17、執(zhí)行計劃 email=‘daqiao666@sina.com’

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 | 23      | const |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)mysql>