char、varchar 类型的字段加索引,都得为索引获取设置一个长度。合适的长度可以节省资源,而不是把整个字段全加上索引。
例如,我要给地址字段建立索引
mysql> desc customer;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| c_custkey | int(11) | NO | PRI | NULL | |
| c_name | varchar(25) | YES | | NULL | |
| c_address | varchar(40) | YES | | NULL | |
| c_nationkey | int(11) | YES | MUL | NULL | |
| c_phone | char(15) | YES | | NULL | |
| c_acctbal | double | YES | | NULL | |
| c_mktsegment | char(10) | YES | | NULL | |
| c_comment | varchar(117) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
#查看c_address 最大长度是多少 40个字符
mysql> select * from customer order by length(c_address) desc limit 1;
+-----------+--------------------+------------------------------------------+-------------+-----------------+-----------+--------------+---------------------------------+
| c_custkey | c_name | c_address | c_nationkey | c_phone | c_acctbal | c_mktsegment | c_comment |
+-----------+--------------------+------------------------------------------+-------------+-----------------+-----------+--------------+---------------------------------+
| 65 | Customer#000000065 | ak7rtta,tWG,jR,cTSXflW6RVQ3alna3P4Q,zF03 | 23 | 33-733-623-5267 | 8795.16 | AUTOMOBILE | slyly regular excuses about the |
+-----------+--------------------+------------------------------------------+-------------+-----------------+-----------+--------------+---------------------------------+
1 row in set (0.25 sec)
#测试合适索引的长度
#计算公式是,当前字符串出现的数量/总量的一个比例,比例在95%左右最合适的。
mysql> select count(distinct left(c_address,20))/count(*) from customer;
+---------------------------------------------+
| count(distinct left(c_address,20))/count(*) |
+---------------------------------------------+
| 1.0000 |
+---------------------------------------------+
1 row in set (0.40 sec)
mysql> select count(distinct left(c_address,10))/count(*) from customer;
+---------------------------------------------+
| count(distinct left(c_address,10))/count(*) |
+---------------------------------------------+
| 1.0000 |
+---------------------------------------------+
1 row in set (0.37 sec)
mysql> select count(distinct left(c_address,5))/count(*) from customer;
+--------------------------------------------+
| count(distinct left(c_address,5))/count(*) |
+--------------------------------------------+
| 0.9988 |
+--------------------------------------------+
1 row in set (0.30 sec)
mysql> select count(distinct left(c_address,6))/count(*) from customer;
+--------------------------------------------+
| count(distinct left(c_address,6))/count(*) |
+--------------------------------------------+
| 0.9999 |
+--------------------------------------------+
1 row in set (0.31 sec)
mysql> select count(distinct left(c_address,4))/count(*) from customer;
+--------------------------------------------+
| count(distinct left(c_address,4))/count(*) |
+--------------------------------------------+
| 0.9534 |
+--------------------------------------------+
1 row in set (0.30 sec)
mysql> alter table customer add index(c_address(4));
Query OK, 0 rows affected (5.64 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from customer where c_address = "j5JsirBM9PsCy0O1m";
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | customer | NULL | ref | c_address | c_address | 7 | const | 1 | 100.00 | Using where |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+