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: 0mysql> 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 |+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+