Mysql 索引知识点精华

<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">1、MyIsam最长索引不能超过1000,innodb不能超过</span><span style="color: rgb(85, 85, 85); font-family: 宋体, 'Arial Narrow', arial, serif; font-size: 14px; line-height: 28px; background-color: rgb(255, 255, 255);">767，索引长度越短越好</span>

例如：create table test(id int,name1 varchar(300),name2 varchar(300),name3 varchar(500))charset=latin1 engine=myisam;
create index test_name on test(name1,name2,name3);
此时报错：Specified key was too long;max key length is 1000 bytes.

2、组合索引遵循最左策略

例如：组合索引引为<emp_no, title, from_date>

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | titles | ref  | PRIMARY       | PRIMARY | 4       | const |    1 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

此例遵循最左策略，会找到索引emp_no，其后是from_date

3、函数和运算符对索引无效

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | titles | ref  | PRIMARY       | PRIMARY | 4       | const |    1 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+|  1 | SIMPLE      | titles | ALL  | NULL          | NULL | NULL    | NULL | 443308 | Using where |+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

4、索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。一般两种情况下不建议建索引。

第一种情况是表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，这个个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。

索引选择性：重复数据比例越大，则不需要建立索引，如性别(0,1)

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
|      0.0000 |
+-------------+

前缀索引:

例：

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+

SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+

第一个重复数据太多，第二个重复数据较少可以建组合索引，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9007 |

这时选择性已经很理想了，而这个索引的长度只有18，比<first_name, last_name>短了接近一半，我们把这个前缀索引建上：

ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

秒客网

Mysql 索引知识点精华

相关文章