您的位置: 首页  >  文章  >  Mysql笔记

Mysql笔记

分类: 文章 • 2024-10-30 15:51:40

数据类型的优化

通常情况下，选择数据类型的最小数据类型。更小的数据类型通常更快，因为它们占用更少的的磁盘，内存和cpu缓存

  • 1 简单就好

简单的数据类型的操作通常需要更少的CPU周期。比如 整形(int)
比字符操作的代价更低，因为字符集合和排序规则使字符比较比整形比较更复杂。

  • 2 尽量避免可为NULL

如果查询可为NULL的列，对于MySQL来说更难优化，因为可以为NULL的列使得索引，索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理
优化可为NULL的列，性能提升比较小

整形类型

TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT(8,16,24,32,64位存储空间)
可选 UNSIGNED 表示不允许负值，那么正数上限就会被放大一倍。比如
TINYINT.UNSIGNED 储存范围0~255，而TINYINT 存储范围-128~127

MySQL可指定int(50)类型宽度，只是规定了对于MySQL的交互工具显示的数据字符个数而已，对于储存和计算来说 INT(1) 和 INT(100)是一样的，但是varchar类型不一样 指定了多少就是多少

实数类型

实数是带有小数部分的数字，这里只详细说DECIMAL 类型

DECIMAL

可以存储比BIGINT还大的整数
DECIMAL 类型用来存储精确的小数

字符串类型

VARCHAR

比如字符串末尾有空格，不会删除
可变字符串长度，越短的字符串使用越少的空间
在UPDATA时可能会使行长度变得更长，就会导致需要额外的工作

MyIASM会将行拆成不同的片段储存，InnoDB则需要分裂页来使行可以放进页内

CHAR

比如字符串末尾有空格，会删除
固定长度，MySQL总是根据定义的字符串长度分配足够的空间
适合存储很短的字符串，比如 MD5加密后的字符串 定长，这里就比使用VARCHAR类型好

对于长度不变的数据来说 CHAR比VARCHAR更好

索引

b-tree

B树是一种多路自平衡搜索树，它类似普通的二叉树，但是B数允许每个节点有更多的子节点。B树示意图如下：
Mysql笔记

b+tree

B+树是B树的变体，也是一种多路平衡查找树，B+树的示意图为：
Mysql笔记

两者的区别

从图中也可以看到，B+树与B树的不同在于：

（1）所有关键字存储在叶子节点，非叶子节点不存储真正的data

（2）为所有叶子节点增加了一个链指针

为什么采用b-/+ tree的结构来实现索引呢？

答：红黑树等结构也可以用来实现索引，但是文件系统及数据库系统普遍使用B/B+树结构来实现索引。MySQL是基于磁盘的数据库，索引是以索引文件的形式存在于磁盘中的，索引的查找过程就会涉及到磁盘IO(为什么涉及到磁盘IO请看文章后面的附加理解部分)消耗，磁盘IO的消耗相比较于内存IO的消耗要高好几个数量级，所以索引的组织结构要设计得在查找关键字时要尽量减少磁盘IO的次数。为什么要使用B/B+树，跟磁盘的存储原理有关。

索引的类型

b-tree(技术上叫b+tree) 最广泛的使用

哈希索引(hash index) 只有Memory存储引擎支持

索引(b-tree)的优点

1 索引大大减少了服务器需要扫描的数量

2 索引可以帮助服务器避免排序和临时表

3 索引可以将随机的I/O变为有序的I/O

MySQL索引的实现

  • MyISAM MyISAM中索引检索的算法为首先按照b+tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
    Mysql笔记

  • innoDB innodb中采用的也是b+tree。区别就是它储存的数据文件就是一个索引文件，data域就保存着数据，key作为表的主键，，如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
    Mysql笔记

查询的优化

查询SQL优化

查询不必要的字段：尽量不适用select * … 需要什么字段查什么字段
切分查询 把大查询切分为多块的小查询
分解复杂的关联查询

添加索引优化：

添加，删除，查看索引SQL语句

添加索引
alter table 表名 add index 索引名 (索引的表字段)
alter TABLE employees add index y (birth_date,first_name,last_name,gender,hire_date)

查看索引
show index from 表名
show index from employees

删除索引
DROP index 索引名 on 表名
DROP index y on employees

explain 执行计划

explain 执行各种查询语句
explain SELECT * FROM employees;

返回的结果：

id :id相同则从上至下的顺序执行，不同则数值大的先执行

select_type :查询类型

Mysql笔记

table :查询的表

type :一般优化到ref，index阶段就差不多了,从上往下性能级别

Mysql笔记

possbile keys: 可能会使用到的索引

keys : 实际用到的索引

keys len : 索引用到的字节数，越少越好

ref 显示索引的哪一列被使用，也有可能是const常数

row 根据表信息及表索引引用情况，统计需要记录需要读取的行数

Extra 包含不合适在列展示的十分重要信息

  • Using filesort 和 Using temporary 会加慢查询速度 反之 USING index加快查询速度 Mysql笔记

实例

单表查询

  • 1 查询表中所有数据explain SELECT * FROM employees ;
    Mysql笔记
    可以看出 type=all 表示全盘查询，所有会导致查询变慢

  • 2 添加索引alter TABLE employees add index y (birth_date,first_name,last_name,gender,hire_date)

  • 3 再次查询 explain SELECT * FROM employees ;
    Mysql笔记
    添加索引后,type=index 及指扫描了索引 查询速度变快 对比 all

索引失效

最左前缀原则：带头大哥不能死，中间兄弟不能断，但是MySQL底层会自动调优顺序显得不那么重要了比如 index(aa,bb,cc)索引，查询的时候只使用了bb,cc字段查询 那么索引会失效，要从最左开始使用才不会失效。

不在索引列中使用函数（计算，自动or手动的类型转换）：就是对索引不使用MySQL的函数，否者会失效

MySQL中使用不等于（！= 或者<>）的时候无法使用索引 ：使用后type=range的索引失效 范围之后全部失效 <>（不等号） 如图：
Mysql笔记

like 以通配符(%abcd)开头 索引失效 --覆盖索引不会失效–

Mysql笔记

**尽量使用覆盖索引 避免 select * **

字符串类型不加单引号索引会失效
Mysql笔记

少用 or 连接 ，索引会失效

group by 基本上都需要进行排序 会有临时表的产生

为排序(order by)使用索引

例：KEY a_b_c(a,b,c)

order by 能使用索引最左前缀

  • order by a
  • order by a,b
  • order by a,b,c
  • order by a desc ,b desc,c desc

如果where使用索引的最左前缀定义为常量，则order by能使用索引

  • where a = const order by b ,c
  • where a = const and b = const order by c
  • where a = const order by b ,c
  • whrer a = const and b>const order by b,c

不能使用索引进行排序

  • order by a asc,b desc,c desc /排序不一致/
  • where g=const order by b,c /丢失a索引/
  • where a=const order by c /丢失b索引/
  • where a=const order by a,d /d 不是索引的一部分/
  • where a in(…) order by b,c /对于排序来说，多个相等条件也是范围查询/

查询优化分析

1 优化SQL的步骤

  • 1 观察，至少跑一天，看看生产的慢SQL的情况

  • 2 开启慢查询日志，设置阙值，比如超过五分钟的SQL就是慢SQL，并把它提取出来

  • 3 explain+慢SQL分析

  • 4 show profile 查询SQL再MySQL服务器里面的执行细节和生命周期情况

  • 5 运维经理 or DBA 进行SQL数据库服务器的参数调优

2 慢查询日志

set global slow_query_log = 1 -- 开启慢查询日志

show VARIABLES like '%slow_query_log%' -- 查看慢查询日志开启状态 以及日志地址

show VARIABLES like 'long_query_time' -- 查看慢查询的阙值 默认10s

set global long_query_time = 3 -- 设置慢查询的阙值 重新连接数据库生效

select sleep(4)  -- 查询4秒 类似线程休眠 这里是做慢查询的日志 看看是否会被记录

show global status like '%Slow_queries%' -- 当前有多少条慢查询

MySQL 自带的慢查询日志的分析工具(mysqldumpslow)
Mysql笔记

3 show profile

是MySQL提共用来分析当前会话SQL语句执行的资源消耗情况。可以用于SQL的调优测量

默认情况下报存最近运行15次的结果

使用步骤：

  • 1 查看是否开启 show variables like 'profiling'
    • 1.1 开启show profile set profiling = on
  • 2 show profiling 查看最近十五次的语句
  • 3 show profile cpu,block io for query 92 查看查询id为92的语句
    • 以下是可以带的参数
      Mysql笔记
  • 4 分析SQL语句的生命周期 cpu block…的耗时
  • 5 如果SQL生命周期出现以下几种 则说明性能不好
    • status(生命周期)列中出现 Mysql笔记

锁

定义

  • 锁是数据库系统区别于文件系统的一个关键特性之一。锁的机制用于管理对共享资源的并发访问。

区别

  • MyISAM 存储引擎,其锁是表锁设计。并发情况下的读没问题，但是并发插入时的性能就要差一些了

  • InnoDB 存储引擎,提供一致性的非锁定读、行级锁支持。行级锁没有相关联额外的开销，并可以同时得到并发性和一致性

InnoDB存储引擎中的锁

锁类型

  • 共享锁(S Lock) 允许事务读一行数据 也称之为 读锁
  • 排他锁(S Lock) 允许事务删除或更新一行数据 也称之为 写锁

锁的兼容

  • 一个事务T1已经获得了行R的共享锁,另一个事务T2也可以获取行R的共享锁,因为读取没有改变R行的数据，这称为锁兼容
  • 一个事物T3想获得行R的排他锁，则必须等待T1,T2释放共享锁，这称之为锁不兼容

意向锁

  • 意向共享锁(IS Lock) 事务想获得一张表中某几行的共享锁
  • 意向排他锁(IS Lock) 事务想获得一张表中某几行的排他锁

复制(replication)

是数据库高可用高性能的解决方案，一般在大型项目上。

步骤
- 1 主服务器(master)把数据更改(不是数据)到二进制日志(biglog)中.

- 2 从服务器(slave) 把主服务器的二进制日志复制到中间的中继(relay log)日志中

- 3 从服务器重做中继日志中的日志，把更改应用到自己的数库上，做到数据一致性

Mysql笔记

备份

通常案例一般都是主服务器运行了一段时间后,再开启一个从服务器进行复制，但是从服务器还没有主服务器数据,所以就需要备份。

分类

1 冷备份

把主库停止，直接把数据复制到从库上

2 热备份

ibbcakup 是InnoDB官方提供的热备份工具
XtraBcakup 开源的热备份工具
如果仅使用MyISAM表，可以在主库运行时使用mysqlhocopy 或rsync 来复制数据

3 使用mysqldump备份(逻辑备份)

mysqldump [arguments] > file_name

arguments:

--all-database 备份所有数据库 到 xxx.sql 文件中

--datatbase db1 db2 db3 指定备份的数据库

--single-transaction db1 仅备份db1数据库的架构

--help 查看所有参数

4 SELECT…INTO (逻辑备份)

相关推荐