MySQL之索引和SQL优化

我谢谢

已于 2022-09-23 17:12:02 修改

阅读量460

点赞数

分类专栏： MySQL 文章标签： mysql sql 数据结构

于 2022-08-31 21:00:44 首次发布

本文链接：https://blog.csdn.net/X18160013/article/details/126630449

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1.索引

1.1 索引概述

索引 (index)是高效获取数据的数据结构。
MySQL 的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，MySQL5.6后的默认引擎是InnoDB；
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。叶节点形成双向链表。
在这里插入图片描述
为什么InnoDB存储引擎选择使用B+tree索引结构?

相对于二叉树,层级更少,搜索效率高;
对于B-tree, 无论是叶子节点还是非叶子节点，都会保存数据,这样导致一页中存储的键值减少，指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;
相对Hash索引， B+tree支持范围匹配及排序操作;

1.2 索引优缺点

优势

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

劣势

索引列也是要占用空间的。
索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

1.3 索引分类

主键索引(PRIMARY)：针对于表中主键创建的索引,默认自动创建，只能有一个；
唯一索引(unique)：避免同一个表中某数据列中的值重复，可以有多个；
单列索引：一个索引只包含单个列，一个表中可以有多个单值索引；
联合索引：一个索引包括多个列；

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

聚集索引(Clustered Index) 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，必须有,而且只有一个；
二级索引(Secondary Index)将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个；

聚集索引选取规则:

如果存在主键, 主键索引就是聚集索引。
如果不存在主键，将使用第一个唯-一(UNIQUE) 索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。

回表查询：先二级索引查询，后聚合索引查询。

1.4 索引操作

创建：

create [unique] index 索引名 on 表名(字段名,...);

例如：创建以name和age字段的索引
create index inx_name_age on myp(name, age);

查看

show index from 表名;

删除：

drop index 索引名 on 表名;
注意：只能删除一个索引

1.5 SQL性能分析

explain 执行计划 :
直接在select语句之前加上关键字explain / desc

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

EXPLAIN执行计划各字段含义:
- ld：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)。
- select_type：表示SELECT的类型，常见的取值有SIMPLE (简单表，即不使用表连接或者子查询)、PRIMARY（主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询）等。
- type：表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。
- possible_key：显示可能应用在这张表上的索引，一个或多个。
- Key：实际使用的索引，如果为NULL，则没有使用索引。
- Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
- rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
- filtered：表示返回结果的行数占需读取行数的百分比, filtered的值越大越好。

1.6 索引使用

1.6.1 最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。
最左前缀法则：最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

1.6.2 SQL提示

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

use index: 使用这索引。
ignore index: 不使用这索引。
force index: 必须使用这索引。

例：
explain select * from 表名 [use|ignore|force] index(索引名) where 条件;

1.6.2 前缀索引

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

语法:
create index 索引名 on 表名(字段名(n)) ;

n：前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,

唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
select count(distinct substring(字段名,1,5)) / count(*) from 表名;

1.7 索引失效

在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描。
模糊查询like以通配符开头(‘%abc’)会导致索引失效，违反最左前缀法则。
存储引擎不能使用索引中范围条件右边的列，举例：select id，name from student where id > 50 and name = ‘张三’，会导致name索引失效。
尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，不要select *(回表查询)。
数据分布影响：如果MySQL评估使用索引比全表更慢，则不使用索引。
字符串不加单引号(隐式转换–>函数操作)索引失效。
用or连接时会导致索引失效。（注意：or前后都有索引不会失效）

1.8 索引设计原则

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

什么情况下不适合建立索引？

表数据太少。
频繁更新的字段。
where后面用不到的字段。

2.SQL优化

2.1 insert 优化

批量插入：

insert into 表名 values(记录),(记录)；

手动提交事务：

tart transaction;

insert into tb. test values(1,'Tom'),(2,'Cat')(3,'lerry');
insert into tb_ test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_ test values(7,'Tom'),(8,'Cat'),(9,'erry');

commit;

主键顺序插入

主键乱序插入
主键顺序插入

主键顺序插入性能高干乱序插入

大批量插入数据

1.客户端连接服务端时，加上参数--local-infile
mysql --local-infile -u root -p

2.设置全局参数local infile为1,开启从本地加载文件导入数据的开关
set global local_infile =1; 

3.执行load指令将准备好的数据，加载到表结构中
load data local infile '路径+文件名' into table 表名 fields terminated by ',' lines terminated by '\n' ;

2.2 主键优化

数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table I0T)。
主键乱序插入会导致页分裂
页分裂：页可以为空,也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据多大，会行溢出)，根据主键排列。
页合并
当删除一行记录时，实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到MERGE_ THRESHOLD ( 默认为页的5Q%)，InnoDB会开始寻找最靠近的页( 前或后)看看是否可以将两个页合并以优化空间使用。
MERGE_ THRESHOLD:合并页的阈值，可以自己设置，在创建表或者创建索引时指定。
主键设计原则
- 满足业务需求的情况下，尽量降低主键的长度。
- 插入数据时，尽量选择顺序插入，选择使用AUTO_ INCREMENT自增主键。
- 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
- 业务操作时，避免对主键的修改。

UUID：一大串十分长无序的的字符串，是唯一识别码。

2.3 order by 优化

Using filesort：通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫FileSort排序。

Using index：通过有序索引顺序扫描直接返回有序数据,这种情况即为using index,不需要额外排序,操作效率高。

使用联合索引时，一个索引字段升序，另一个降序会导致Using filesort。
解决：

#创建索引
create index 索引名 on 表名(字段 asc ,字段 desc);

#根据age, phone进行降序一个升序， -一个降序
explain select id,age,phone from tb_user order by age asc , phone desc;

order by优化：

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引。.
多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC) 。
如果不可避免的出现filesort,大数据量排序时，可以适当增大排序缓冲区大小sort_ buffer_size(默认256k)。

2.4 group by 优化

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

2.5 limit优化

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

例：
explain select * from tb_ sku t , (select id from tb_ sku order by id limit 2000000,10) a wheret.id = a.id;

2.6 update优化

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

我谢谢

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL之索引和SQL优化

索引 (index)是高效获取数据的数据结构。MySQL 的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，MySQL5.6后的默认引擎是InnoDB；MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。叶节点形成双向链表。为什么InnoDB存储引擎选择使用B+tree索引结构?相对于二叉树,层级更少,搜索效率高;...
复制链接

扫一扫