MySQL数据库之索引与慢查询优化

2022-04-18 11:29:54

介绍索引

索引参考博客：https://www.cnblogs.com/linhaifeng/articles/7274563.html

知识回顾:数据都是存在硬盘上的，那查询数据不可避免的需要进行IO操作

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。

  • primary key
  • unique key
  • index key

注意foreign key不是用来加速查询用的，不在我们研究范围之内,上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询

索引就是一种数据结构，或者说数据组织方式，为数据建立索引就好比是为书本建目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据

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

索引的影响:

  • 在表中有大量数据的前提下，创建索引速度会很慢
  • 在索引创建完毕后，会大幅度提升查询效率，但会降低增、删、改的效率，好就好在读写比例10:1

开发人员最懂业务，任何一个软件都有其吸引用户的亮点 亮点背后对应的是热数据，这一点开发人员是最清楚的，

开发人员最了解热数据对应的数据库表字段有哪些，所以应该在开发软件的过程中就提前为相应的字段加上索引，

而不是等到软件上线后，让DBA发现慢查询sql后再做处理

  • innodb存储引擎索引分类：

    • 1、hash索引：更适合等值查询，不适合范围查询，虽然可以快速定位，但是没有顺序，IO复杂度高。

    • 2、B+树索引：

      • 聚集索引：以主键字段的值作为key创建的索引（一张表中只有一个）
      • 辅助索引：针对非主键字段创建的索引（一张表中可以有多个）
命中索引也未必能起到很好的提速效果
    1、对区分度高并且占用空间小的字段建立索引
    2、针对范围查询命中了索引，如果范围很大，查询效率依然很低，如何解决
        要么把范围缩小
        要么就分段取值，一段一段取最终把大范围给取完
    3、索引下推技术（默认开启）
    4、不要把查询字段放到函数或者参与运算

b+树

b+树通过一层一层进行区域范围划分逐步缩小查询范围来提高查询速度的

只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据

查询次数由树的层级决定，层级越低次数越少

一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项

思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段

聚集索引(primary key)

聚集索引其实指的就是表的主键，innodb引擎规定一张表中必须要有主键。先来回顾一下存储引擎。

myisam在建表的时候对应到硬盘有几个文件(三个)？

innodb在建表的时候对应到硬盘有几个文件(两个)？frm文件只存放表结构，不可能放索引，也就意味着innodb的索引跟数据都放在idb表数据文件中。

特点:叶子结点放的一条条完整的记录

辅助索引(unique,index)

辅助索引:查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引

特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})

回表查询：通过辅助索引拿到主键值，然后再回到聚集索引从根再查一下

覆盖索引：不需要回表就能拿到你要的全部数据

举例：

id字段为主键，我们为name字段创建了辅助索引：
select name,age,gender from user where name="egon";  # 命中的是辅助索引，需要回表

上述语句叫回表查询：虽然查询的时候命中了辅助索引字段name，但是要查的是age、gender字段，所以还需要利用主键才去查找

select name,age,gender from user where id =3;  # 命中的是聚集索引，所以压根不需要回表

上述语句叫覆盖索引：命中的是聚集索引，id字段对应的是一条完整的数据，直接找到了所有我们想要的数据

创建索引

#方法一：创建表时
    　　create table 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [unique | fulltext | spatial ] index | key
                [索引名]  (字段名[(长度)]  [asc |desc]) 
                );

#方法二：create在已存在的表上创建索引
        create  [unique | fulltext | spatial ] index  索引名 
                     on 表名 (字段名[(长度)]  [asc |desc) ;


#方法三：alter table在已存在的表上创建索引
        alter table 表名 add  [unique | fulltext | spatial ] index
                             索引名 (字段名[(长度)]  [asc |desc) ;
                             
#删除索引：drop index 索引名 on 表名字;

测试索引

# 1. 提前准备表，约300万数据导入需要时间
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

# 2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,‘egon‘,‘male‘,concat(‘egon‘,i,‘@oldboy‘));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

# 3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

索引的简单使用

# 在没有索引的前提下查看数据，相当于扫描全表，可以发现很慢
mysql> select count(*) from s1;
+----------+
| count(*) |
+----------+
|  2999999 |
+----------+
1 row in set (1.65 sec)

# 查询sql语句的执行计划
explain select count(*) from s1;

# 创建索引
create index idx_id on s1(id);

# 再次查询sql语句的执行计划，索引创建成功
explain select count(*) from s1;

# 可以发现查询速度很快
mysql> select count(*) from s1 where id = 333333;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

# 插入相对较慢但不是很明显
mysql> insert into s1 values(1111111,"xxx","male","xxx@qq.com");
Query OK, 1 row affected (0.50 sec)

# 删除也不是很明显
mysql> delete from s1 where id = 333333;
Query OK, 1 row affected (0.40 sec)

# 命中索引未必能起到很好的提速效果
select count(*) from s1 where id >3;
select couny(*) from s1 where id = 3;

# 范围小也很快，计划查询可以明显看到涉及行数不多
select couny(*) from s1 where id > 3 and id < 7000;
mysql> explain select couny(*) from s1 where id > 3 and id < 7000;

# 范围很大依旧很慢
select couny(*) from s1 where id != 3;

select couny(*) from s1 where id between 10 and 3000000;

# 没有命中索引查询非常慢
select count(*) from s1 where name = "xxx";

# 可以针对name字段创建索引
create index idx_name on s1(name);

# 创建索引后再次查询可以看到速度很快
mysql> select count(*) from s1 where name = "xxx";
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

正确使用索引

##### 索引命中也未必会加速

并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题

##### 1 、 范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like

##### 2 、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(\*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

##### 3 、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

##### 4 、索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)

##### 5 、索引下推技术的应用and/or

```python
# 1、and与or的逻辑
    条件1 and 条件2:所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
    条件1 or 条件2:只要有一个条件成立则最终结果就成立
         
# 2、and的工作原理
    条件：
        a = 10 and b = ‘xxx‘ and c > 3 and d =4
    索引：
        制作联合索引(d,a,b,c)
    工作原理:
        对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序

# 3、or的工作原理
    条件：
        a = 10 or b = ‘xxx‘ or c > 3 or d =4
    索引：
        制作联合索引(d,a,b,c)        
    工作原理:
        对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d
```



##### 6 、最左前缀匹配原则，非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

##### 7、注意事项

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合

MySQL数据库之索引与慢查询优化

上一篇：mysql查询表字段和注释


下一篇：mysql 存储引擎，基本数据类型