MySQL索引常用操作及SQL优化

MySQL索引常用操作及SQL优化

基本操作

• 创建索引
• 查看索引
• 删除索引
• 索引使用及失效
• 索引建议
• Limit 优化
• Update 优化

基本操作

1. 创建索引

普通索引:
# 创建普通索引
create index idx_student_info_name on student_info(name);


唯一索引:
# 创建唯一索引
create unique index idx_student_info_code on student_info(code);


联合索引:
# 创建联合索引
create index idx_student_info_gender_age on student_info(gender, age);

# 创建索引时指定排序方式
# gender 字段升序排列, age 字段降序排列.
create index idx_student_info_gender_age on student_info(gender asc, age desc);


前缀索引:
# 创建前缀索引, 将 code 字段前两个字符截取出来作为索引, 将大大缩减索引存储容量.
create index idx_student_info_code on student_info(code(2));
# 使用前缀索引时需配合此 SQL 来评估索取字符长短(列数据重复率). 
# 此值越趋近于1越好, 而索引字符长度则是越短越好, 需在此值和索引字符长度间做取舍.
select count(distinct substring(code, 1, 2)) / count(*) from student_info

2. 查看索引

show index from student_info;

关键字解释:
Key_name:		索引名称
Seq_in_index:	索引排序
Column_name:	索引列
Collation:		索引排序方式, 取值: A升序, D降序
Cardinality:	索引中不重复记录的预估值
Sub_part:		前缀索引中的前缀长度, 如果整列被编入索引, 则为 NULL
Packed:			指示关键字如何被压缩, 如果没有被压缩, 则为 NULL
Null:			如果列可以含有NULL，则取值为YES.
Index_type:		索引类型

3. 删除索引

drop index idx_student_info_code on student_info;

4. 索引使用及失效

4.1. 最左前缀法则

最左前缀法则 是指使用联合索引时, 查询是从最左侧列开始, 并且不跳过索引中的列, 如果跳过某一列, 后面列的索引将失效.

# 例如:
# 创建联合索引为:
create index idx_student_info_name_age_score on student_info(name, age, score);

# 索引有效
select * from student_info where name = 'AA';
select * from student_info where name = 'AA' and age = 15 and score = 80;
# 查询顺序MYSQL会自动优化, 所以此处也会使用索引.
select * from student_info where age = 15 and score = 80 and name = 'AA';

# 索引失效
select * from student_info where age = 15;
select * from student_info where score = 80;
select * from student_info where age = 15 and score = 80;

# 部分索引失效, name 列将会使用索引, 因为跳过了 age 列, 索引 score 列将不会使用索引.
select * from student_info where name = 'AA' and score = 80;

4.2. 不在索引列使用运算操作

例如:
# 索引失效
explain SELECT * FROM `student_info` where substring(code, 3, 1) = '1';

4.3. 字符串类型不加引号

字符串类型字段(varchar, char 等) 查询时作为条件需要加引号.

例如:
# 索引有效
explain SELECT * FROM `student_info` where code = '100';

# 索引失效
explain SELECT * FROM `student_info` where code = 100;

4.4. 模糊查询( Like )

使用模糊查询时, 匹配符 % 在左侧时, 索引失效, 匹配符 % 在右侧时, 索引有效.

例如:
# 索引有效
select * from student_info where name like 'A%';

# 索引失效
select * from student_info where name like '%A';
select * from student_info where name like '%A%';

4.5. OR 查询规则

使用 OR 关键字作为查询条件是, 必须左右两侧同时有索引, 索引才会有效, 若只有一侧有索引, 则索引失效.

例如:
# 创建3列, name列, code列, age列, name和code创建索引. age不创建索引.
create index idx_student_info_name on student_info(name);
create index idx_student_info_code on student_info(code);

# 索引有效
select * from student_info where name = 'AA' or code = '100';

# 索引失效
select * from student_info where name = 'AA' or age = 18;
select * from student_info where age = 18 or code = '100';

4.6. 数据分布评估

mysql 优化器会对查询SQL及数据进行评估, 如果评估结果为使用全表扫描效率比使用索引高, 则也会索引失效.

例如:
# 索引有效
# 查询年龄小于15的人. 查询结果数据只有总数据的40%, 所以会使用索引.
select * from student_info where age <= 15

# 索引失效
# 查询年龄所有小于100的人, 该查询出来的数据已经占了总数据的100%, 所以不使用索引. 全表扫描比较快.
select * from student_info where age <= 100

4.7. IS NULL 和 IS NOT NULL

IS NULL 和 IS NOT NULL 是否使用索引也遵循数据分布评估.

5. 索引建议

当条件列存在多个索引时, 可以建议mysql 使用索引 sql优化的重要手段
use index 建议使用索引.
ignore index 不使用索引.
force index 强制使用索引.

例如:
# 建议使用 idx_student_info_name, 但mysql不一定会使用.
select * from student_info use index(idx_student_info_name) where name = 'AA';

#  不使用 idx_student_info_name 索引.
select * from student_info ignore index(idx_student_info_name) where name = 'AA';

#  强制使用 idx_student_info_name 索引.
select * from student_info force index(idx_student_info_name) where name = 'AA';

6. Limit优化

使用 limit 关键字分页时, 前面的便宜值越大, 查询效率越慢.

例如:
# 此查询效率高
SELECT * FROM student_info LIMIT 0, 10;
# 此查询效率低下
SELECT * FROM student_info LIMIT 1000000, 10;

建议使用 覆盖索引 + 子查询的方法优化

例如:
# 使SQL使用覆盖索引.
select gid from student_info order by gid limit 1000000, 10;

# 使用 覆盖索引 + 子查询 优化 limit 分页.
select a.* from student_info a, (select gid from student_info order by gid limit 1000000, 10) b where a.gid = b.gid

7. Update优化

InnoDB 的行锁是针对索引加的锁, 不是针对记录加的, 并且该索引不能失效, 否则会从行锁升级为表锁