MySQL第九章索引_9.MySQL索引

索引

索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。因此，数据库的数据表中频繁查找的列，需要创建索引。

索引分为两类：hash索引和btree(balance tree)索引。常用的是btree索引。

在创建主键(primary key)时，会自带聚集索引+非空约束+唯一约束。

在创建唯一约束(unique)时，会自带辅助索引+唯一约束。

重复率大于10%的字段不适合创建索引。

普通索引

普通索引仅有一个功能：加速查询

# 在创建表时+索引

create table in1(

nid int unsigned not null auto_increment primary key,

name varchar(32) not null,

email varchar(64) not null,

extra text,

index index_name (name)

)

# 为已经创建的表添加索引

create index on (字段)

create index index_name on table_name(column_name)

# 删除索引

drop on ;

drop index_name on table_name;

# 查看索引

show index from ;

show index from table_name;

# 注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。

create index index_extra on in1(extra(32));

唯一索引

唯一索引的作用是约束不能重复和加速查找

# 创建表 + 唯一索引

create table in1(

nid int not null auto_increment primary key,

name varchar(32) not null,

email varchar(64) not null,

extra text,

unique ix_name (name)

)

# 创建唯一索引

create unique index 索引名 on 表名(列名)

# 删除唯一索引

drop unique index 索引名 on 表名

示例

create table table1(

id bigint not null auto_increment primary key,

name char(64),

sex enum('male','female'),

id_card bigint,

email varchar(64)

) engine=innodb default charset=utf8;

create table table2(

id bigint not null auto_increment primary key,

username char(64) not null,

password char(64) not null,

user_id bigint not null,

constraint t1_t2_id foreign key(user_id) references table1(id),

# 唯一约束

unique uq (user_id)

)engine=innodb default charset=utf8;

主键索引

主键有三个功能：加速查询、不能为空和不能重复

# 创建表 + 创建主键

create table in1(

nid int not null auto_increment primary key,

name varchar(32) not null,

email varchar(64) not null,

extra text,

index ix_name (name)

)

# OR

create table in1(

nid int not null auto_increment,

name varchar(32) not null,

email varchar(64) not null,

extra text,

primary key(nid),

index ix_name (name)

)

# 创建主键

alter table 表名 add primary key(列名);

# 删除主键

alter table 表名 drop primary key;

alter table 表名 modify 列名 int, drop primary key;

联合索引(组合索引)

组合索引是将n个列组合成一个索引

其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = 'python' and n2 = 666

# 创建表

create table in3(

nid int not null auto_increment primary key,

name varchar(32) not null,

age int not null,

email varchar(64) not null,

extra text

)

# 创建联合索引

create index ix_name_email on in3(name,age,email);

最左前缀匹配，如上创建组合索引之后，查询：

name and age and email -- 使用索引

name and age -- 使用索引

name and email -- 使用索引

age and email -- 不使用索引

name -- 使用索引

age -- 使用索引

email -- 不使用索引

注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。

联合普通索引

create index 索引名称 on 表名(列名,列名,...)

联合唯一索引

create unique index 索引名称 on 表名(列名,列名,...);

联合主键索引

alter table 表名 add primary key(列名,列名,...);

专有名词

覆盖索引：在索引文件中直接获取数据。

索引合并：把多个单列索引合并使用。索引合并没有组合索引的效率高。

正确使用索引

select * from 表名称 where 索引列名=索引值;

无法命中索引的情况

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。

以下情况，即使建立索引，索引也不会生效：

# 模糊匹配，无法命中索引

select * from tb where name like '%cn';

# 使用函数，无法命中索引

select * from tb where reverse(name) = 'python';

# 使用范围，无法命中索引

select * from tb where name > 'python';

select * from tb where name != 'python';

# 虽然使用范围会无法命中索引，但如果是主键或索引是整数类型，则还是会走索引

select * from tb where nid > 123;

select * from tb where num > 123;

select * from tb where nid != 123;

# 使用逻辑运算符or，当or条件中有未建立索引的列时，无法命中索引

select * from tb where nid = 1 or email = 'seven@live.com';

# 但or的两个条件都是索引，或者使用and中有一个条件使用了索引，索引会生效

select * from tb where nid = 1 or name = 'seven';

select * from tb where nid = 1 or email = 'seven@live.com' and name = 'alex';

# 类型不一致，如果列是字符串类型，传入条件是必须用引号引起来

select * from tb where name = 999;

# 使用order by，select字段必须是索引字段，否则无法命中索引

select email from tb order by name desc;

# 但如果对主键排序，则还是走索引：

select * from tb order by nid desc;

# 组合索引最左前缀原则，必须带着最左边的列作为条件，索引才会生效，如果出现了范围，无法命中索引

如果组合索引为：(name,email)

name and email -- 使用索引

name -- 使用索引

email -- 不使用索引

其他注意事项

- 避免使用select *

- count(1)或count(列) 代替 count(*)

- 创建表时尽量使 char 代替 varchar

- 表的字段顺序固定长度的字段往前放，变长字段往后放

- 组合索引代替多个单列索引(经常使用多个条件查询时)

- 尽量使用短索引(使用字段短的列作为索引)

- 使用连接(JOIN)来代替子查询(Sub-Queries)

- 连表时注意条件类型需一致

- 索引散列值(重复少)不适合建索引，例：性别不适合做索引

执行计划

查看SQL语句有没有按照预计执行，查看索引的使用情况，以及type等级

explain select 语句

慢查询优化

首先从SQL的角度优化

把每一句SQL单独执行，找到效率低的表，优化这条SQL语句；

了解业务场景，适当创建索引，帮助查询；

尽量用连表代替子查询；

确认命中索引的情况；

考虑修改表结构

拆表；

把固定在的字段网前调整；

使用执行计划，观察SQL的type通过以上调整是否有所提高。

慢日志

在MySQL的配置中开启并设置当一条SQL语句运行超过一定时间后，会被记录下来，一边我们定位到这条SQL语句，对其进行优化。