【MySQL】索引总结

1. 索引作用
   （1）约束
   （2）加速查找

2. 索引分类

• 主键索引：加速查找 + 不能为空 + 不能重复
• 普通索引：加速查找
• 唯一索引：加速查找 + 不能重复
• 联合索引（多列）：
  • 联合主键索引
  • 联合唯一索引
  • 联合普通索引

3.普通索引创建方式

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

create index ix_name on userinfo3(email);  --对userifo3表中email列创建普通索引，名字ix_name

--删除普通索引
drop index 索引名称 on 表名

4.唯一索引创建方式（也可在建表时创建，见文章Mysql唯一索引，加SQL操作补充）

也可在建表时创建
create unique index 索引名称 on 表名(列名)
drop unique index 索引名称 on 表名

5.组合索引（遵循最左前缀匹配，否则无法命中索引）：

创建组合索引
create unique index 索引名称 on 表名(列名,列名)
删除组合索引
 drop unique index 索引名称 on 表名

注意最左前缀匹配，如
create index ix_name_email on userinfo3(name,email,) --创建name与email组合索引

select  * from userinfo3 where name='alex';  --查询条件name，遵循最左前缀匹配
select  * from userinfo3 where name='alex' and email='asdf';  -- 查询条件name与email ，遵循最左前缀匹配
select  * from userinfo3 where email='alex@qq.com';  --不遵循最左前缀匹配，无法命中索引，即索引不生效

6 覆盖索引

在索引文件中直接获得数据，若已经为email列创建索引

select email from userinfo3 where email='1243@qq.com';

7 索引合并
指把多个单列索引合并使用，但效率没有组合索引高，若为name与email列都创建单独创建索引，下面3种查询方式都支持，而不用创建索引合并

select  * from userinfo3 where name='alex' and email='asdf';
select  * from userinfo3 where name='alex';
select  * from userinfo3 where email='alex';

8 创建索引之后，必须命中索引，才能生效，以下是无法命中索引的情况

• like ‘%xx’

select * from tb1 where email like '%cn';

• 使用函数

select * from tb1 where reverse(email) = 'yangzi';

• or

id列为索引，但name列不是索引，此时无法命中
select * from tb1 where nid = 1 or name = 'seven@live.com';
但下面情况会走索引，id，email为索引，name列不是索引
select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'

• 类型不一致

如果列是字符串类型，传入条件是必须用引号引起来，不然不走索引，即email创建索引是字符串类型，但是查询是整数
select * from tb1 where email = 999;

• !=

已为email列创建索引
select * from tb1 where email != 'alex'

特别的：如果是主键，则还是会走索引
     select * from tb1 where nid != 123

• 大于>

select * from tb1 where email > 'alex'
    特别的：如果是主键或索引是整数类型，则还是会走索引
     select * from tb1 where nid > 123
     select * from tb1 where num > 123

• order by

select name from tb1 order by email desc;
    
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
     select * from tb1 order by nid desc;

• 组合索引最左前缀

如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

9 执行计划：让mysql预估执行操作(一般预估结果是正确)

效率从小到大排列：
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

以下语句为直接查询

select * from userinfo3 where name='alex'

加上explain关键字就是预估操作

explain select * from userinfo3 where name='alex'

显示type: ALL(全表扫描)，但修改查询语句为

select * from userinfo3 limit 1;

虽然显示type: ALL(全表扫描)，但查询效率依然很快，若显示type: const(走索引)

9 DBA工作，记录慢日志，即记录查询效率低的sql语句

查看mysql配置方法
    show variables like '%query%'
 修改配置方法1
    set global 变量名 = 值（set slow_query_log = ON）
修改配置方法2
创建配置文件

mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
    
    my.conf内容：
     slow_query_log = ON
     slow_query_log_file = D:/....
     
    注意：修改配置文件之后，需要重启服务