索引

目录

• 什么是索引
• 为什么要使用索引
• 索引的底层原理
• 索引的种类
• 索引的创建
  • 主键索引
  • 唯一索引
  • 普通索引
• 索引的优缺点
• 不会命中索引的情况
• 组合索引最左前缀
• explain查询性能分析
• 慢查询日志

什么是索引

索引的本质是一个特殊的文件，是存储引擎快速找到记录的一种数据结构。

类比：查字典的过程，通过拼音索引

索引的本质：通过不断地缩小想要获取数据地范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

为什么要使用索引

使用索引就是为了提高查询效率

索引的底层原理

使用B+树

索引的种类

• 主键索引：加速查找、不为空、不能重复

• 唯一索引：加速查询、不为空的字段不能重复

• 联和唯一索引：unique（name, password）

• 普通索引：加速查找 index(name)

• 联合索引：index(name, password)

索引的创建

主键索引

新增主键索引

--创建的时候增加主键索引
create table t1(
	id int auto_increment primary key,
    name varchar(32) not null default ''
)charset utf8;


--表创建完了之后再增加主键索引
 alter table t2 add primary key(id);
 
 

删除主键索引

--删除主键索引
alter table t2 drop primary key;

唯一索引

新增唯一索引

--创建表的时候增加唯一索引
mysql> create table t2(
    -> id int auto_increment primary key,
    -> name varchar(32) not null default '',
    -> unique(name)    # 如果不设定唯一键的名字，默认唯一键的名字是作用的字段名
    -> )charset utf8;
    
    
    
--创建表后新增唯一索引键
# create unique index 索引名 on 表名(字段名);
create unique index un_name on t2(name);

--或者
alter table t2 add unique key(name);
--或者
alter table t2 add unique index un_name(name);

删除唯一索引键

alter table t2 drop index name;  # index后面是唯一键的名字

普通索引

新增

--创建表的时候新增普通索引
create table t3(
	id int auto_increment primary key,
    name varchar(32) not null default '',
    index u_name(name)   # index后面跟的是索引的名字，可以不写
)charset utf8;

--创建表后创建普通索引
create index 索引名 on 表名(字段名);

create index ix_name on t3(name);


--或者
alter table t3 add index ix_name (name);

删除

alter table t3 drop index u_name;

索引的优缺点

通过观察*.ibd文件可知：

• 索引加快了查询速度
• 但是加了索引之后，会占用大量的磁盘空间

索引不是加的越多越好

不会命中索引的情况

• 不能在SQL语句中，进行四则运算，会降低sql的查询效率
• 使用函数 如：select * from t1 where reverse(name)='abc1213';
• 类型不一致
  • 如果列是字符串类型，传入条件必须要用引号引起来，如select * from t1 where email=1241;
• order by 当排序条件为索引，则select字段必须也是索引字段，否则无法命中
  • select email from t1 order by email desc;
  • 如果对主键排序，速度还是很快的
• count(1)或count(列)代替count(*)在mysql中没有差别
• 组合索引最左前缀

组合索引最左前缀

什么时候会创建联合索引？

根据公司的业务场景，在最常用的几列上添加索引

select * from user where name='abc123' and email='abc123@qq.com'

遇到上述业务情况，错误的做法：
index ix_name(name),
index ix_email(email)


正确的做法：
index ix_name_email(name, email)

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

那么：

where name='abc' and email='xxx'   --索引命中
where name='abc'                   --命中索引
where email='abc123@qq.com'        --未命中索引

explain查询性能分析

mysql> explain select * from t1 where id=2141\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

各个参数的含义：

• id：这是select的查询序列号

• select_type：是select的类型

• table：显示这一行的数据是关于哪张表的

  • type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一

  结果值从好到坏依次是：
  
  system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
  
  一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。
  

• possible_keys：列出MySQL能使用哪个索引在该表中找到行

• key：显示MySQL实际使用的索引

• key_len：索引长度

• ref：显示使用哪个列或常数与key一起从表中选择行。

• rows：扫描的长度

• Extra：使用到了索引

索引覆盖：

select id from t1 where id=2132123;

当字段id 添加了主键索引，这时候是命中索引的；但查询了字段id，id本身有主键索引，这时候会产生索引覆盖，会比主键索引快。

慢查询日志

查看慢SQL的相关变量

mysql> show variables like '%slow%';
+---------------------------+-----------------------------------------------+
| Variable_name             | Value                                         |
+---------------------------+-----------------------------------------------+
| log_slow_admin_statements | OFF                                           |
| log_slow_slave_statements | OFF                                           |
| slow_launch_time          | 2                                             |
| slow_query_log            | OFF                                           |
| slow_query_log_file       | D:\mysql-5.7.28\data\DESKTOP-PAT2F9O-slow.log |
+---------------------------+-----------------------------------------------+

查看慢sql查询时间配置

mysql> show variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 10000     |
| performance_schema_events_statements_history_long_size   | 10000     |
| performance_schema_events_transactions_history_long_size | 10000     |
| performance_schema_events_waits_history_long_size        | 10000     |
+----------------------------------------------------------+-----------+

设置慢SQL日志配置

--开启慢SQL查询
set global  slow_query_log = on;

--设置慢SQL查询日志的路径
set global slow_query_log_file = "D:/mysql-5.7.28/data/mysql_slow";

--设置慢SQL查询时间
set global long_query_time = 2;