MySQL索引

索引

索引概述

索引，是帮助mysql高效获取数据的数据结构（有序）；在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优缺点：

优点：提高数据检索的效率，降低数据库的IO成本；通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗；

缺点：

索引会占用额外空间；索引提高查询效率的同时，也降低了更新表的速度，如对表进行insert、update、delete时，效率低下；

image-20220430114052857

image-20220430114111974

image-20220430120310081

索引结构

索引分类

在innodb存储引擎中，根据索引的存储形式，又可以分为以下两种；

聚集索引：Clustered Index ，将数据与索引放到一块，索引结构的叶子节点保存了行数据，（必须有，且只有一个）

二级索引；Secondary Index ,将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键（可以存在多个）

聚集索引选取规则：

  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一索引作为聚索引
  • 如果表没有主键，或没有合适的唯一索引则，innodb会生成一个rowid作为隐藏的聚集索引

image-20220430192252721

image-20220430192158641

3、索引语法

# 创建索引
create [unique | fulltext] index index_name on table_name(index_col_nam,...)
# 常规索引
show index from table_name;
# 删除索引
drop index index_name on table_name;

4、SQL性能分析

SQL执行频次

Mysql客户端连接成功后，通过show [session | global] status 命令，可以提供服务器状态信息。通过如下命令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次；

show global status like 'com_______'; # 7个_

image-20220430211911594

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志；

MYSQL的慢查询日志默认没有开启，需要在mysql的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢查询日志开关
slow_query_log=1

# 设置慢查询日志的时间为2秒，SQL语句执行时间超过2秒
long_query_time=2

配置完毕后，重启mysql服务器进行测试，查看慢查询日志文件中记录的信息/var/lib/mysql/localhost-slow.log
image-20220430213300188

SQL性能分析

1、profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费在哪里了，通过have_profiling参数，能够看到当前mysql是否支持profile操作；

slect @@have_profiling;

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling

set profiling = 1;

select @@profiling 查看是否开启开关

image-20220430233217125

show profiles; # 查看每一条SQL耗时基本情况
show profile for query query_id; # 查看指定query_id 的SQL语句各个阶段的耗时情况
show profile cpu for query_id; # 查看指定query_id的SQL语句CPU的使用情况

2、explain执行计划

explain或desc命令获取mysql如何执行select语句的信息，包括在select语句执行过程中，表如何连接和连接的顺序；

explain执行计划，各字段含义

select查询的序列号，表示查询中执行select字句或者操作表的顺序（ID相同，执行顺序从上到下；ID值不同，值越大，越先执行）

表示select类型，simple（简单表，即不使用表连接或子查询）、primary（主查询，外层的查询）、UNION（union中的第二个或者后面的查询语句）、subquery(select/where之后包含的子查询)

表示连接类型，性能由好到差的连接类型，null(不访问表)，system（主键、系统表），const（主键、唯一索引）,eq_ref,ref（非唯一性索引）,range,index,all

显示可能应用在这张表上的索引，一个或多个

实际使用的索引如果没有为null，则没有使用索引

表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用的长度，在不损失精度的前提下，长度越短越好；

mysql 认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的

表示返回结果的行数占，需读取行数的百分比，filtered的值越大越好。

5、索引使用

  • 最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始（存在即可，不一定完全按照顺序写在where条件中），并且不跳过索引中的列。

如果跳跃某一列，索引将部分失效（后面的字段索引失效）

image-20220501164828417

  • 范围查询

联合索引中，出现范围查询（>,< )，范围查询右侧的列索引失效

业务允许的情况下，尽量使用>=,<=，来进行范围查询，这种情况下全部索引都可以生效

  • 索引列运算

不要再索引列上进行运算操作，索引将失效

  • 字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

  • 模糊查询

如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引将失效

  • or连接的条件

用or分割开的条件，如果or前的条件中有的列有索引，而后面的列中没有索引，那么设计的索引都不会被用到。

explain select * from tb_user where id = 10 or age = 23;
explain slect * from tb_user where phone = '1700000' or age=23;

由于age没有索引，所以即使id,phone有索引，索引也会失效，所以需要针对于age也要建立索引。

  • 数据分布影响

如果mysql评估使用索引比全表更慢，则不使用索引

is null，is not null

id_num >='1200'

  • SQL提示

SQL提示，是优化数据库的一个重要手段，是在SQL语句中加入一些人为的提示来达到优化操作的目的；

explain select * from tb_user use index(id_user_pro) where profession = '软工'; # 建议使用该索引，不一定mysql会接受
explain select * from tb_user ignore index(idx_user_pro) where profession = 
'软工'; # 忽略某索引
explain select * from tb_user force index(idx_user_pro) where profession = '软软工'; # 强制使用某索引

image-20220501171307844

  • 覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中科院全部能够找到），减少select *

explian返回结果中最后一列，extra不用版本的返回内容不同

using index condition；查找使用了索引，但是需要回表查询数据；

using where；using index ：查找使用了索引，但是需要的数据都在索引列中能够找到，所以不需要回表查询数据

image-20220501175243045

思考题：一张表，有四个字段（id,username,password,status)，由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案；

select id,username,password from tb_user where username = 'itcast';

答：建立关于username,password联合索引，id作为索引的叶子节点，规避了回表查询

  • 前缀索引

当字段类型为字符串(varchar,text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：

create index idx_xxx on table_name(column(n));

前缀长度：

可以根据索引的选择性来决定，而选择性是指不重复

select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email,1,5)) / count(*) from tb_user;

image-20220501210316836

  • 单列索引与联合索引

单列索引：一个索引只包含单个列

联合索引：一个索引包含多个列

在业务场景中，如果存在多个查询条件，考虑对于查询字段建立索引，建议建立联合索引，而非单列索引；

对两个查询条件，建立单列索引，mysql会自动判断那个索引，更高效从而只使用一个索引，从而进行回表查询；

image-20220501223644762

image-20220501223754977

索引设计原则

  1. 针对于数据量较大，且查询比较频繁的表建立索引（百万数据）
  2. 针对于常用于查询条件where，排序order by，分组（group by）操作的字段建立索引
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
  7. 如果索引不能存储null值，请在创建表时使用not null约束它。当优化器知道每列是否包含null值时，它可以更好地确定哪个索引最有效地用于查询

SQL优化-插入数据

insert

批量插入 500-1000，每次插入一条，都要经历建立连接，断开连接的过程

insert into tb_test values(1,'tom'),(2,'ketty');

手动提交事务 vs 频繁事务关闭开启

start transcation;
insert into tb_test values(),();
insert into tb_test values(),();
insert into tb_test values(),();
commit;

主键顺序插入

image-20220502000615362

大批量插入数据

如果一次性需要插入大批量数据（百万），使用insert语句插入性能较低，此时可以使用mysql数据库提供的load指令进行操作；

# 客户端连接服务器时，加上参数 --local-infile
mysql --local-infile -u root -p
# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
# 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';

主键优化

  • 数据组织方式

在innodb存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT)

Innodb : 表空间 > 段 > 区 > 页 > 行

image-20220503103020068

image-20220503103312051

页合并

当删除一行记录，实际上记录并没有被物理删除，只是被标记（flaged）为删除，并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），Innodb会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化

image-20220503103816602

主键设计原则：

  • 在满足业务需求的情况下，尽量降低主键的长度
  • 插入数据时，尽量选择顺序插入，选择auto_increatement自增主键
  • 尽量不要使用UUID做主键或者其他自然主键，如身份证号
  • 业务操作时，避免对主键的修改

order by 优化

  1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后再排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序
  2. Using index ：通过有序索引排序扫描直接返回有序数据，这种情况即为using index，不需要额外排序

Extra

Backward index scan;
order by A,B; # 如果建立联合索引时，先B，A，则违背最左前缀法则，索引失效
create index idx_user_age_pho_ad on tb_user(age asc,phone desc);
  • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
  • 尽量使用覆盖索引
  • 多字段索引，一个升序，一个降序，此时需要注意联合索引在创建时的规则（asc/desc）
  • 如果不可避免的出现filesort，大数据量排序时，可以适当增大缓冲区大小sort_buffer_size（莫仍256K

group by优化

在分组操作时，可以通过索引来提高效率；分组操作时，索引的使用也是满足最左前缀法则的；

create index idx_user_pro_age_sta on tb_user(profession,age,status);
explain select age,count(1) from tb_user where profession = '软件工程' group age; # 依然会使用到联合索引

Limit优化

一个常见又头疼的问题，limit 2000000,10，此时需要mysql排序前20000010记录，仅仅返回20000000-200000010的记录，其他记录丢弃，查询排序的代价非常大

优化思路：一般分页查询，通过创建 覆盖索引，能够比较好的提高性能，可以通过覆盖索引加子查询形式进行优化：

explain select * from tb_sku b , (select id from tb_sku order by id limist2000000,10) a where b.id = a.id;

count优化

MyISAM 引擎把一个表的总行数，存在了磁盘上，因此执行count（*）时，会直接返回这个数，效率很高；

Innodb引擎，执行count（*）时，需要把数据一行一行地从引擎里读取出来，然后累积计数。

优化思路：自己计数，借助redis，较复杂

count(*) Innodb引擎不会把全部字段取出来，而是专门做了优化，不取值，服务层直接进行按行累加；

count（1）：Innodb引擎遍历整张表，但不取值，服务层对于返回的每一行，放一个数字1进行，直接进行累加；

count（字段）：没有not null约束，innodb引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加；

有not null约束，innodb引擎遍历整张表，把每一行的字段值取出来，返回给服务层，直接按行进行累加；

count（主键）：innodb引擎遍历整张表，把每一行的主键id取出来，返回给服务层；服务层拿到主键后，按行进行累加

update优化

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

update student set no = '2022001' where id = 1;
update sutdent set no = '2022001' where name = '韦小宝';

image-20220503222303746

posted @ 2022-05-28 16:42  三两研几  阅读(190)  评论(0)  编辑  收藏  举报