mysql以及sql优化（简述）

最新推荐文章于 2024-09-21 11:29:25 发布

哭着改bug

最新推荐文章于 2024-09-21 11:29:25 发布

阅读量413

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/qq_43473129/article/details/103225677

版权

java 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

Mysql

使用mysql好处

1）数据永久保存
2）使用SQL(结构化查询语言)，查询方便效率高。
3）管理数据方便

查看mysql的字符集

show character set;

修改mysql默认的编码格式

找到mysql安装目录下的my.ini文件，修改client和mysqld下的default-character-set:utf8，重启mysql

三大范式

反范式化设计

mysql的存储引擎：

MyISAM(默认存储引擎)、InnoDB【事务】、BDB【事务】、MEMORY、MERGE、EXAMPLE、NDBCluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED

mysql索引分类：

fullText：全文索引，目前只有MyISAM引擎支持(针对模糊查询效率较高)。可以在CREATE TABLE,ALTER TABLE,CREATE INDEX
hash：   由于hash的唯一以及类似键值对的形式，很适合作为索引。
btree： 索引就是一种将索引值按一定的算法，存入一个树形的数据结构中，每次查询都是从树的入口root，依次遍历node，获取leaf。【mysql默认的最常用索引类型】
rtree： rtree索引仅支持geometry数据类型。btree和rtree的优势在于范围查找。

索引相关操作

创建索引：
        create index index_name on table_name(col_name);
        CREATE UNIQUE INDEX index_name ON table_name(col_name);  //创建唯一索引
        CREATE INDEX index_name ON table_name(col_name_1,col_name_2);  //创建组合索引
展示索引：
        show index from table_name;
删除索引：
        drop index index_name on table_name;
**注意**： 如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。

mysql的binlog有几种录入格式，分别有什么区别？

statement：statement模式下，会把每一条修改数据的sql记录到binlog中，不需要记录每一行的变化，节省了资源，减少了io，提高了性能。由于sql执行是有上下文信息的，保存时需要保存相关的信息，使用了函数之类的语句无法被记录复制。
row ：不记录sql语句的上下文相关信息，仅保存了哪条记录被修改。记录单元为一行的改动。大量的改动会导致文件保存的信息太多，日至量太大。
mixed ：一种折中的方案。普通操作使用statement，无法使用statement时，使用row。

注意：新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

MyISAM和INnoDB区别：

   1、INnoDB采用聚集性索引(叶节点存放key和具体的行数据)，MyISAM采用非聚集性索引(叶节点存放行数据的地址，需要再次寻址才能拿到数据)
   2、MyISAM用的是表级锁，INnoDB用的是行级锁
  ３、MyISAM：以读写插入为主的应用程序；INnoDB：更新（删除）操作频率也高，或者要保证数据的完整性；

B数和B+数:

B数:内部节点存放的是键值对，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。需要进行全数据遍历时，B数则需要对树的每一层进行遍历，需花费大量时间。（适合随机检索）
B+数：内部节点存放键，不存放值。一次读取，可以再内存页中获取更多的键，有利于更快的缩小查找范围。当需要进行一次全数据遍历时，B+数需要使用O(logN)时间找到最小的一个节点，然后通过O(N)的顺序遍历即可。（支持随机检索和顺序检索）
B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率

hash索引和B±Tree的区别：

1、哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而B+树则可以通过最左前缀原则快速找到对应的数据。
2、如果我们要进行范围查找，例如查找ID为100 ~ 400的人，哈希表同样不支持，只能遍历全表。
3、索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大增加。

百万级别以上的数据删除：

因为索引需要额外的维护成本，而且索引文件是单独存在的文件，所以我们对数据的insert、delete、update都会产生额外的索引文件，这些操作需要额外的io，会降低增删改的执行效率。所以删除百万级数据的速度和创建索引的数量是成正比的。

解决方案：
1、我们想要删除百万级别数据的时候，可以先删除索引
2、然后删除其中的无用数据
3、删除完成之后重新创建索引(此时数据量较少，速度相对较快)

前缀索引：

语法： index(field(5)) ,使用字段的前5哥字符创建索引，默认的是使用字段的全部内容创建索引。
使用前提： 前缀的标识度较高。

最左前缀原则，最左匹配原则：

1、定位到最左边，然后向右遍历寻找，【联合索引，如果最左侧的索引失效，那么整个索引将失效。】

2、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的顺序可以任意调整。

聚簇索引(主键索引)和非聚簇索引(普通索引)：

1、聚簇索引(INnoDB)：将索引和数据存放到一块，找到索引也就找到了数据。（由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引）
2、非聚簇索引(MyISAM)：将数据于索引分开，索引结构的叶节点指向数据对应的行。MyISAM通过key_buffer把索引先缓存到内存储中，当需要访问数据时(通过索引访问数据)，在内存储中直接搜索索引，然后通过索引找到磁盘对应的数据。索引在key_buffer(内存中)没有命中时，速度会慢。

聚簇索引[主键索引]和非聚簇索引[非主键索引]：
在这里插入图片描述

其中R代表一整行数据。
区别：非主键索引的叶节点存放的是主键的值，而主键索引那个的叶节点存放的是整行数据，其中非主键索引也称为二级索引，主键索引也被称为聚簇索引。

eg：
select * from table where ID = 100,及主键查询的方式，只需要搜寻ID这棵B+数。
select * from table where k = 1,及非主键索引，则先搜索k的索引树，得到ID=100，再通过ID索引树搜索一次，这个过程也成为回表。

非聚簇索(非主键索引)引一定会回表查询数据吗？

不一定，查询语句所要求的字段是否命中了索引，如果全部命中了索引，那就没必要回表查询了。

事务的四大特征(ACID)：

原子性 ：事务执行的最小单元。（事务要么一起成功，要么一起失败）
一致性 ：执行事务前后，数据保持一致，多个事务对同一个数据的读取结果是相同的。
隔离性 ：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。
持久性 ：一个事务提交之后。他对数据库中的数据的改变是持久的，即使数据库发生故障也不该对其有任何影响。

事务的隔离级别：（解决脏读、不可重复读、幻读）

1、READ-UNCOMMITTED(读未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读，幻读或不可重复读
2、READ-COMMITTED(读已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，仍会发生幻读或不可重复读
3、REPRATABLE-READ(可重复读)：对于同一字段的多次读取结果都是一致的，可以解决脏读和不可重复读，仍会发生幻影读。
4、SERIALIZABLE(序列化读)：最高的隔离级别，完全服从ACID的隔离级别。

注意：mysql默认的事务隔离级别是可重复读，oracle默认是读已提交
锁分类：关系型数据库中，按照锁的粒度把数据库锁分为行级锁、表级锁、页级索

Mysql中锁对比：

表级索：Mysql中粒度最大的一种锁，对当前操作的整张表加锁。开销小，加锁快，不会出现死锁。
行级锁：只针对当前操作的行进行加锁。开销大，加锁慢，会出现死锁。
页级锁：Mysql一种折中的方案。开锁和加锁的时间界于表级锁和行级锁之间，会出现死锁，并发一般。

锁的类别：
共享锁：又叫读锁。当用户进行读数据时，对数据加共享锁，共享锁可以同时加上多个。
排他锁：又叫写锁。当用户进行写入时，对数据加排他锁。(只可以加一个)和其他的锁相互排斥。

死锁及解决方案：

死锁指的是两个或多个事务在同一个资源上相互占用，并请求锁定对方资源，从而导致恶性循环。
解决：
    1、如果程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
    2、在同一事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生的概率。
    3、对于非常容易产生死锁的业务部分你可以尝试使用升级锁定粒度，通过表级锁来减少死锁的概率。如果业务处理不好，可以使用分布式事务锁或乐观锁。

数据库中的乐观锁和悲观锁：

悲观锁：假定发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据是就把事务锁起来，直到事务提交。(适用于写多的场景)

乐观锁：假定不会发生并发冲突，只在提交操作是检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。(适用于读多写少的场景)

char和varchar的区别：

char：固定长度的字符串，不够就补空格，存取速度快。(牺牲空间换时间，效率高)，最多255个字符。存储定长字符串效率较高。
varchar：不定长字符串，插入多长就是多长。存取速度慢，节省空间。最多存放字符个数65532。适用于存放不定长字符串。

Mysql存储过程：

https://www.runoob.com/w3cnote/mysql-stored-procedure.html

一、概念
sql语言都是先编译后执行的。存储过程呢就是一组为了完成特定功能的sql语句集，它经过编译存储在数据库中，用户通过存储过程的名字并且指定参数来调用和执行。
存储过程能够实现多条sql语句的封装
二、写法：

1、创建存储过程：(in代表传入的意思)
    create procedure test_name(in a int,in b int)
    BEGIN
    
       //定义变量 
       declare c Int default 10;
       declare result Int default 0;
       select a+b+c ; 
       
       //if流程控制
       if a+b+c<10 then
       select a+b+c;
       else
       select a-b-c;
       end if;

       //while循环
       while c<13 do
            //set用于赋值
        set result = result+c;
        set c=c+1;
       end while; 
       select c;    

    END         
2、调用存储过程：
    call test_name(1,2);    
3、删除存储过程：
    drop procedure test_name;

注意创建存储过程时，命名后面跟小括号(写法类似java中的函数)。
Mysql修改自增主键的值
在这里插入图片描述

sql优化

1.对查询进行优化，应劲量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引。

2.应尽量避免在where 子句中对字段进行null值判断，否则将导致引擎放弃索引而进行全表扫描。

eg:
select id form t_user where num is null;

3.应该尽量避免在where子句中使用 != 或<> 操作符，否则引擎将放弃索引而进行全表扫描。（“<>”表示不等于）

注：Mysql只对以下操作符才是用索引：< , <= , > , >= ,between,in,以及某些时候的like。可以再like
操作中使用索引的条件是：另一个操作数不是以通配符(% 或者 _ )开头的情形。
eg：
select id from t_user where col like ‘Mich%’; 这个查询将使用索引
select id from t_user where col like ‘%Mich’; 这个查询将不会使用索引

4.应尽量避免在where子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。

eg：
select id from t_user num =10 or num =20;
可以使用UNION语法(UNION 操作符用于合并两个或多个 SELECT 语句的结果集)
UNION ALL:结果集中的列名总是等于第一个SELECT的列名
eg:
select id from t_user where num=10 union all select id from t_user where num =20;

5.in 和 not in 也要慎用，否则将导致全表扫描。

eg:
select id from t_user where num in(1,2,3); //对于连续的数据，能用 between 就不要用 in
select id from t_user whrer num between 1 and 3;

6.如果where子句中使用参数，也会导致全表查询也会导致全表查询。因为sql只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择输入项。

eg:
select id from where num =@num;
可以强制查询，使用索引
select id from t_user with(index(索引名)) where num = @num;

7.应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

eg:
select if from where num/2=100应该为：
select id from t_user where num=100*2

8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

eg:

select id from t_user where substring(name,1,3)=‘abc’ --name以abc开头的id

应该为：select id from t_user where name like ‘abc%’

9.不要在where子句中的 " = " 左边进行函数操作、算数运算或其他表达式运算，否则系统将可能无法正确使用索引。

10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序于索引顺序相一致。

11.不要写一些没有意义的查询，如果需要生成一个空结构：

select col1,col2 into #t from t_user where 1=0 —不会生成结果集，但会消耗系统资源，

改成：create table #t(…)

12.并不是所有的索引对查询都有效，SQL是根据表中的数据进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会取利用索引。索引提高了select效率，但也降低了insert及update的效率，一个表中的索引不要超过6个。

13.尽量使用数字类型的字段，若只含数值信息的字段尽量不要设计称为字符型，这会降低查询和连接的性能，并会增加存储开销。因为引擎在处理查询和连接时会逐个比较字符串中的每一个字符，而对于数字类型而言只需要比较一次就够了。

14.尽可能的使用varchar/nvarchar 代替char/nchar，因为首先变成字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率效率显然要高。

15.任何地方都不要使用select * from t_user，不要返回用不到的字段。

16.尽量使用表变量来代替临时表，如果表变量包含大量数据，请注意索引非常有限。

17.避免频繁创建和删除临时表，以减少系统表资源的消耗。

18.范围条件放最后

19.禁止使用不含字段列表的 INSERT 语句、

这样写：

insert into t(c1,c2,c3) values ('a','b','c');

20.避免使用子查询，可以把子查询优化为JOIN操作

子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

=======================================================

EXPLAIN优化sql：

使用explain模拟优化器执行sql查询语句，可以知道Mysql是如何处理你的sql语句。分析你的查询语句或是表结构的性能瓶颈。

通过explain，我们可以分析出一下结果：
    1、标的读取顺序
    2、数据读取操作的操作类型
    3、那些索引可以使用
    4、那些索引实际被使用
    5、表之间的索引
    6、每张表有多少行被优化器查询

使用方法：
    explain + SQL语句
    explain select * from t1;
查询(显示)结果为：
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
id：查询的序号，表示查询select语句或操作表的顺序
    id分为3中情况：
        id相同：从上之下依次执行
        id不同：id值越大，越先执行。
        id有相同有不同：相同id为一组，从上到下一次执行；不同组之间，id越大越先执行。
select type：主要用于区别普通查询，联合查询和子查询的复杂查询查询。
    有以下几种类型：
        simple：简单的查询语句，查询语句中不包含子查询或union
        primary：查询中若有任何复杂的子部分，都会被标记为primary
        subQuery：在select或where列表中包含了子查询。
        derived：在from列表中包含子查询，被标记为dervied
        union：若第二个select出现在union之后，被标记为union；若union包含在from子查询语句中，外层的select将被标记为dervied
table ： 指的就是当前执行的表。
type ： 所显示的是查询使用了那种类型。
      type包括的类型(从最好到最差)：
           system > const > eq_ref > ref > range > index > all       
      注意：  一般来说，查询能保证达到range级别，最好使ref级别。      
    system：系统表（只有一行记录），这是const类型的特殊列。
    const：通过索引一次就找到了，只匹配一行数据，很快。
    eq_ref：唯一索引扫描，对于每个索引键，表中只有一天记录与之匹配。
    ref：非唯一索引扫描，返回匹配某个单独值的所有行。
    range：只检索给定范围的行，使用索引来选择，key列显示使用了哪个索引，这种范围扫描要比全表扫描要好。                    
    index：index只遍历索引树。通常比all快，因为索引文件通常比数据文件小。
    all ： 遍历全表，找到匹配的行。
possible_key： 显示可能应用在这张表中的索引，一个或多个。(该索引比一定被使用)
key： 实际使用的索引，为null，则没有使用的索引。
      若查询中使用了“覆盖索引”(select后要查询的字段刚好和创建的索引字段完全相同) ，则该索引出现在key列表中。         
key_len： 表示索引的字节数，可通过该列计算查询中使用的索引的长度。长度越短越好。
ref ：显示索引被那一列使用了。
rows： 根据表统计信息及索引选用情况，大致估算出找到所需要的记录所需要读取的行数   
Extra：包含不适合在其他列中显示但十分重要的额外信息。