SQL优化方式

抚月code

于 2024-08-10 09:28:23 发布

阅读量524

点赞数 20

分类专栏： Mysql 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/m0_63016075/article/details/141086020

版权

Mysql 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

⭐️⭐️⭐️索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息

🎀 优点：

1️⃣高效性：查找效率高

2️⃣唯一性：确保所查的数据的唯一性

3️⃣完整性：以加速表和表之间的连接，实现表与表之间的参照完整性

4️⃣优化隐藏器，提高系统性能。

🌛 缺点：

1️⃣增删改需要维护索引（新华字典）

2️⃣占用内存空间

3️⃣多种组合索引，导致索引文件膨胀很快

索引的分类(4个)

主键索引

表定义一个主键将自动创建主键索引，主键索引只能有一个

确保数据记录的唯一性

 CREATE TABLE `表名` (
 `字段1` INT(11) AUTO_INCREMENT PRIMARY KEY,
 #或 PRIMARY KEY(`字段1`)
 )

唯一索引(可有多个)

不允许具有索引值相同的行，从而禁止重复的索引或键值。

 CREATE TABLE `表名` (
 `字段1` INT(11) NOT NULL UNIQUE,
 #或 UNIQUE KEY(`字段1`)
 )

常规索引

有重复值，使用常规索引

不宜添加太多常规索引，影响数据的插入、删除和修改操作

 CREATE TABLE `表名` (
 `字段1` INT(11) NOT NULL,
 INDEX/KEY(`字段1`)
 )

全文索引

在不同的字段里，使用全文索引(结构是hash)

创建规则

Mysql 5.6之前版本，只有myisam支持全文索引，5.6之后，Innodb和myisam均支持全文索引。
只有char、varchar、text类型字段能创建全文索引。
当大量写入数据时，建议先写入数据，后再建立全文索引，提高效率。
Mysql内置ngram 解析器，可以解析中日韩三国文字。有汉字的一定要启用它。
英文分词用空格，逗号；中文分词用 ngram_token_size 设定. 全文索引的注意：

注意需要在配置文件中加入： [mysqld] ngram_token_size=2

 -- 创建索引
 CREATE TABLE `表名` (
 `字段1` VARCHAR(32) NOT NULL,,
 fulltext key (字段名，字段名，字段名) with parser ngram
 )ENGINE=innodb
 
 
 -- 使用索引
 SELECT <字段表> FROM <表名> 
 WHERE MATCH(字段) 
 AGAINST (‘要搜索的关键词’);
 
 -- 修改索引
 ALERT TABLE 表名 ADD 索引类型（数据列名）;
 ALTER TABLE <表名> add FULLTEXT INDEX <索引名>(字段名1，字段2，，) [ WITH PARSERngram];
 
 -- 删除索引
 ALTER TABLE 表名 DROP PRIMARYKEY;
 DROP INDEX 索引名 ON 表名;
 ALTER TABLE 表名 DROP INDEX 索引名;

例子：

 create table wenzhang(
     wid int PRIMARY KEY auto_increment,
     title varchar(20),  
     content text, 
     zuozhe varchar(20), 
     FULLTEXT(title,content,zuozhe) with parser ngram -- 中日韩
 );
 
 insert into wenzhang(title,content,zuozhe) 
 values
 ('西安往事','这是一个古老的城市，在这个城市中有很多的人，工厂，建筑物','小杨'),
 ('山西往事','这是一个古老的城市，这里有很多的人，工厂，建筑','老陶'),
 ('地球往事','这是一个古老的星球，这里有很多的人','老刘在西安'),
 ('银河往事','这是一个系，打算在这个系之外造一个西安','小彭');
 
 select * from wenzhang 
 where match(title,content,zuozhe) AGAINST('西安');
 
 select * from wenzhang where match(title,zuozhe) AGAINST('西安');
 ALTER table wenzhang add FULLTEXT(title,zuozhe)with parser ngram;

索引的结构

⭐️🎋 B 树 ---->B+树

⭐️B树 : 所有节点都携带数据

⭐️B+树： 数据只在叶子结点，上面只有索引位，不存数据，通过映射到下层数据，所有叶⼦节点之间都有⼀个链指针

使用双向链表解决了B树的范围查找问题

⭐️⭐️⭐️创建索引---优化原则

 1.【强制】InnoDB表必须主键为id int/bigint auto_increment，且主键值 禁止被更新 。
 2.【强制】InnoDB和MyISAM存储引擎表，索引类型必须为 BTREE 。
 3.【建议】主键的名称以 pk 开头，唯一键以 uni 或 uk 开头，普通索引以 idx 开头，一律
 使用小写格式，以字段的名称或缩写作为后缀。
 4.【建议】多单词组成的columnname，取前几个单词首字母，加末单词组成column_name。如:
 sample 表 member_id 上的索引：idx_sample_mid。
 5.【建议】单个表上的索引个数 不能超过6个 。
 6.【建议】在建立索引时，多考虑建立 联合索引 ，并把区分度最高的字段放在最前面。
 7.【建议】在多表 JOIN 的SQL里，保证被驱动表的连接列上有索引，这样JOIN 执行效率最高。
 8.【建议】建表或加索引时，保证表里互相不存在 冗余索引 。 比如：如果表里已经存在key(a,b)，
 则key(a)为冗余索引，需要删除。

⭐️⭐️⭐️SQL编写---优化原则

 1.【强制】程序端SELECT语句必须指定具体字段名称，禁止写成 *。
 2.【建议】程序端insert语句指定具体字段名称，不要写成INSERT INTO t1 VALUES(…)。
 3.【建议】除静态表或小表（100行以内），DML语句必须有WHERE条件，且使用索引查找。
 4.【建议】INSERT INTO…VALUES(XX),(XX),(XX).. 这里XX的值不要超过5000个。 值过多虽然上线很
 快，但会引起主从同步延迟。
 5.【建议】SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以
 内。
 6.【建议】线上环境，多表 JOIN 不要超过5个表。
 7.【建议】减少使用ORDER BY，和业务沟通能不排序就不排序，或将排序放到程序端去做。ORDER
 BY、GROUP BY、DISTINCT 这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
 8.【建议】包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句，WHERE 条件过滤出来的结果
 集请保持在1000行以内，否则SQL会很慢。
 9.【建议】对单表的多次alter操作必须合并为一次
 对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行，多个alter需整
 合在一起。 因为alter table会产生 表锁 ，期间阻塞对于该表的所有写入，对于业务可能会产生极
 大影响。
 10.【建议】批量操作数据时，需要控制事务处理间隔时间，进行必要的sleep。
 11.【建议】事务里包含SQL不超过5个。
 因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。
 12.【建议】事务里更新语句尽量基于主键或UNIQUE KEY，如UPDATE… WHERE id=XX;
 否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。

⭐️⭐️⭐️索引失效

最佳左前缀法则

在写条件时，按照索引顺序写语句

主键插入顺序
计算、函数导致索引失效
类型转换导致索引失效
范围条件右边的列索引失效
不等于(!= 或者<>)索引失效
is null可以使用索引，is not null无法使用索引
like以通配符%开头索引失效
OR 前后存在非索引的列，索引失效
数据库和表的字符集统一使用utf8mb4

面试题

⭐️⭐️⭐️聚簇和⾮聚簇索引

索引可以分为 2 种：聚簇（聚集）和⾮聚簇（⾮聚集）索引。我们也把⾮聚集索引称为⼆级索引或者辅助索引。

聚簇索引：

特点：

使⽤记录主键值的⼤⼩进⾏记录和⻚的排序，这包括三个⽅⾯的含义：
- ⻚内的记录是按照主键的⼤⼩顺序排成⼀个单向链表。
- 各个存放⽤户记录的⻚也是根据⻚中⽤户记录的主键⼤⼩顺序排成⼀个双向链表。
- 存放⽬录项记录的⻚分为不同的层次，在同⼀层次中的⻚也是根据⻚中⽬录项记录的主键⼤⼩顺序排成⼀个双向链表。
B+树的叶⼦节点存储的是完整的⽤户记录。
- 所谓完整的⽤户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。
优点：
- 数据访问更快，因为聚簇索引将索引和数据保存在同⼀个B+树中，因此从聚簇索引中获取数据⽐⾮聚簇索引更快
- 聚簇索引对于主键的排序查找和范围查找速度⾮常快
- 按照聚簇索引排列顺序，查询显示⼀定范围数据的时候，由于数据都是紧密相连，数据库不⽤从多个数据块中提取数据，所以节省了⼤量的io操作。
缺点：
- 插⼊速度严重依赖于插⼊顺序，按照主键的顺序插⼊是最快的⽅式，否则将会出现⻚分裂，严重影响性能。因此，对于InnoDB表，我们⼀般都会定义⼀个⾃增的ID列为主键
- 更新主键的代价很⾼，因为将会导致被更新的⾏移动。因此，对于InnoDB表，我们⼀般定义主键为不可更新
- ⼆级索引访问需要两次索引查找，第⼀次找到主键值，第⼆次根据主键值找到⾏数据 1. 索引 1.1. 分类 ● ● ● ● ● ● ● ● ● ● 2 ⼆级索引（辅助索引、⾮聚簇索引）：
  
  概念：回表以某列⼤⼩排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根某列的值查找到完整的⽤户记录的话，仍然需要到聚簇索引中再查⼀遍，这个过程称为回表。也就是根据某列的值查询⼀条完整的⽤户记录需要使⽤到 2 棵B+树！

Btree 平衡多路查找树

B-Tree结构的数据可以让系统⾼效的找到数据所在的磁盘块。为了描述B-Tree，⾸先定义⼀条记录为⼀个⼆元组(key,data]，key为记录的键值，对应表中的主键值，data为⼀⾏记录中除主键外的教据。对于不同的记录，key值互不相同。

B+tree

所有的叶⼦结点中包含了全部关键字的信息，⾮叶⼦节点只存储键值信息，及指向含有这些关键字记录的指针，且叶⼦结点本身依关键字的⼤⼩⾃⼩⽽⼤的顺序链接，所有的⾮终端结点可以看成是索引部分，结点中仅含有其⼦树根结点中最⼤(或最⼩)关键字。(⽽B树的⾮终节点也包含需要查找的有效信息
所有叶⼦节点之间都有⼀个链指针。
数据记录都存放在叶⼦节点中。