MySql学习总结

常用语句

关于存储引擎

什么是存储引擎?
Mysql数据库使用不同的机制存取表文件, 机制的差别在于不同的存储方式/索引技巧/锁定水平等等, 在MySql中将这些不同的技术以及配套的功能称为存储引擎.
在关系型数据中数据的储存是以表的形式进行储存的, 所以存储引擎也可以称为表类型.
Oracle和SQLserver等数据库只有一种存储引擎, 而Mysql有多种, 针对不同的需求, 配置Mysql的不同引擎, 就会让数据库采用不同的处理数据的方式和拓展功能, 更好地发挥数据库的性能.
Mysql的存储引擎类型: InnoDB/MyISAM/Memory/Archive/Federate/CSV/Blackhole
各种引擎的特性:
MyISAM存储引擎:
访问快, 不支持事务和外键, 表结构在.frm文件中, 表数据保存在.MYD文件中, 索引保存.MYI文件中.
InnoDB存储引擎:
支持事务,占用磁盘空间大, 支持并发控制
Memory存储引擎:
内存存储, 速度快, 不安全, 适合小量快速访问的数据

性能优化

优化没有绝对真理, 应当针对实际业务场景通过测试来验证你的执行计划和响应时间的假设.

优化要考虑和了解的问题

1. 优化不总是针对一个单纯的环境进行, 还可能是一个复杂的已投产的系统
2. 优化手段有很大的风险, 一定要意识到和预见到
3. 任何技术可以解决一个问题, 但不然会带来一个问题的风险
4. 对于优化而带来问题, 控制在可接受的范围内才有成果
   所有优化工作, 都是业务需求驱使的, 稳定性和业务持续性比性能更重要
   优化主要针对两个方面: 安全和性能
   安全 --数据安全性
   性能 --数据的高性能访问

优化维度

数据库优化分为四个维度 : 硬件, 系统配置 , 数据库表结构 , SQL和索引
硬件: CPU, 内存 , 存储 , 网络设备
系统配置: 服务器系统, 数据库参数等
数据库表youhua: 高可用 , 分库分表 , 存储引擎 , 表设计
SQL即索引 : SQl语句, 索引使用
常规调优思路
某业务特别慢, 过了某时间段就好了

1. 查看slowlog , 分析slowlog, 分析出查询慢的语句
2. 调整索引或语句本身

缓存优化

在Mysql中有一种缓存机制, 在该缓存中记录着所有select查询的结果, 当我们开启Query Cache功能, Mysql在接收到一条select语句的请求之后, 如果语句满足Query Cache的要求, Mysql会直接根据预先设定好的Hash算法将接受到的select语句以字符串的方式进行hash, 然后到QueryCache中直接查找是否已经缓存, 如果已经在缓存中, 该select请求就会直接将数据返回, 从而省略了后面所有的步骤(如SQL语句的解析, 优化器优化以及向存储引擎请求数据等), 极大地提高性能.
当然, QueryCache也有一个致命的缺陷, 那就是当某个表的数据有任何变化, 都会导致所有引用了该表的select语句在Query Cache中缓存数据失效, 所以, 当我们的表结构数据变化非常频繁的情况下, 使用Query Cache可能会得不偿失
如果缓存命中率非常高的话, 有测试表明在极端情况下提高效率238%, 在糟糕时, QC会降低系统的13%处理能力
可以同步设置query_cache_size参数的大小, 设置缓存区域的大小

命令如下:
show variables like ‘%query_cache%’
have_query_cache : 表示此版本mysql是否支持缓存
query_cache_limit : 允许缓存的单条Query结果集的最大容量, 默认为1M,如果超过该值, 则此次结果不放进缓存
query_cache_size : 缓存区大小
query_cache_type : off 表示关闭缓存机制, on表示开启缓存
query_cache_wlock_invalidate : 控制当有写锁定在某表上时, 是否失效该表先关的Query Cache, 设置为1(true), 则相关缓存失效, 如果为0(false), 则不失效
以上参数可以在Mysql配置设置文件 my.ini 中设置, 该文件中还有存储引擎的一些设置参数

查询优化

Mysql查询流程

1. 客户端将查询发送到服务器
2. 服务器检查查询缓存, 如果找到了, 就从缓存中返回结果, 否则今次那个下一步
3. 服务器解析, 预处理
4. 查询优化器优化查询
5. 生成执行计划, 执行引擎调用存储引擎API执行查询
6. 服务器将结果发送到客户端
   
   查询缓存 : 在解析一个查询语句之前, 如果查询缓存是打开的, 那么Mysql

SQL是我们和数据库交流最重要的部分, 所以我们在调优时, 需要花费大量的时间在SQL调优上面. 常见的分析手段有慢查询日志, expain分析查询, 通过定位分析性能的瓶颈, 才能更好的优化数据库系统的性能.

慢查询

慢查询日志开启
在配置文件my.cnf或my.ini中在[mysqlid]一行下面加入两个配置参数
log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=5
log-slow-queries参数为慢查询日志存放的位置, 一般这个目录要有mysql的运行账号的可写权限, 一般都将这个目录设置为mysql的数据存放目录
long_query_time=5
还可以在my.cnf或者my.ini添加log-queries-not-using-indexes参数, 表示记录下没有使用索引的查询
相关命令:
show variables like ‘slow_query_log’ 查看慢查询日志是否开启
show variables like ‘slow_query_log_file’ 查看慢查询日志文件位置
show variables like ‘long_query_time’ 查看慢查询设置超越界限时间

set global slow_query_log=‘ON’ 开启或关闭慢查询日志
set global slow_query_log_file=’/var/lib/mysql/…’ 设置慢查询日志文件
set global long_query_time=1 设置慢查询时间超越界限时间(超过该时间的SQL输出到日志文件)

show profiles
Show profiles是MySql用来分析当前会话SQL语句执行的资源消耗情况, 可以用来SQL的调优测量.
Show Profiles默认情况下是关闭的
show variables like ‘profiling’ 查看show profiling是否开启
show profiles 查看最近执行的SQL语句执行情况
show profile cpu,block io for query 3 查看SQL语句的CPU, io等情况
参数:
Query_ID : 为每条执行SQL的编号, Duration为 执行时间, Query为SQL语句
Duration : 为执行时间
Query : 为SQL语句

explain执行计划
通过explain命令可以得知mysql是如何执行SQL语句的, 包括执行过程中的表如何连接和连接顺序
参数含义:
id : select查询的序列号, 是一组数字, 表示的是查询中执行select子句或者操作表的顺序
select_type : 表示select的类型, 常见取值有: SIMPLE(简单表, 即不使用表连接或子查询), primary(主查询即外层的查询), union(union中的第二个或者后面的查询语句), subquery(子查询中第一个select)
table : 表示表的连接类型, 性能由好到查连接类型为: (system–>const --> eq_ref --> ref --> ref_or_null --> index_merge --> index_subquery --> range --> index --> all)
possible_keys : 查询时, 可能使用的索引
key : 实际使用的索引
key_len : 索引字段的长度
rows : 扫描行的数量
extra : 执行情况的说明和描述

一般来说, 需要保证查询至少达到range级别, 最好达到ref

索引

索引是帮助mysql快速查询的数据结构, 功能是: 是实现高级查询算法
建立索引前查询方式为: 全局扫描

索引类型

B-Tree索引
最常见的索引类型, 大部分索引都支持B树索引, 平时所说索引, 如果没有特指, 都是B+Tree结构索引, 其中聚集索引, 复合索引, 前缀索引, 唯一索引默认都是使用B+Tree树索引, 统称为索引.
索引结构: 5叉BTree
key的数量 : 公式推导[cell(m/2)-1] <= n <= m-1 ,所以2<=n<=4, 当n>4时, 中间节点分裂到父节点, 两边节点分裂.



上面是BTree结构索引结构, B+Tree为BTree的变种, 两者的区别为:

1. n叉B+Tree最多含有n个key, 而BTree最多含有n-1个key
2. B+Tree的叶子节点保存所有的key信息, 依key大小顺序排列
3. 所有的非叶子节点都可以看做是key的索引部分
   
   Mysql中的B+Tree
   Mysql索引结构对经典的B+Tree进行了优化, 在原B+Tree的基础上, 增加了一个指向相邻叶子节点的链表指针, 就形成了带有顺序指针的B+Tree, 提高区间访问的性能
   

索引的使用

索引命令
创建索引 : create index 索引名 on 表名(列名,列名,…)
create index name_age_address on person(name,age,address)
删除索引 : drop index 索引名 on 表名
drop index name_age_address on person

最左前缀法则
如果索引内有多个列, 要遵守最左前缀法则: 查询从索引的最左前列开始, 并且不跳过索引中的列
匹配最左前缀法则, 才会走索引, 例如索引创建的顺序为 : name-status-address
和爬楼梯是一个道理, 查询条件为name and address ,会只爬到name第一层(只用到name索引, 用不到address索引), 没有爬第二层,到不了address第三层. 查询条件如果想要用索引就应该从第一层开始name, 只要查询条件中存在name即可, 比一定要是第一个查询条件, 查询条件不一定第一个就是name, 你也可以id+name+status可以爬到第二层
SQL实例 :
select * from text where name = ‘Tom’ and status = ‘1’ and address = ‘Beijing’ 索引3列(name,age,address)起作用
select * from text where name=‘Tom’ and address=‘Beijing’ 索引1(name)列起作用

索引类型

单值索引 : 即一个索引只包含单个列, 一个表可以有多个单列索引
唯一索引 : 索引列的值必须唯一, 但允许空值
复合索引 : 即一个索引包含多个列

索引存储结构
哈希索引(Hash Index)
空间索引(R-Tree)
全文索引(Full-text)

使用场景

1. 主键自动建立索引
2. 查询条件where或order by后面出现的列要建立索引
3. 作为排序的列要建立索引
4. 查询中其他表关联的字段, 外键关系建立索引
5. 高并发条件下倾向于建立组合索引
6. 用于聚合函数的列可以建立索引, 例如使用count(number)时, number要建立索引

不使用场景

有大量重复的列不单独建立索引
表记录太少不要建立索引, 因为没有太大的所用
不会作为查询的列不要建立索引

数据库优化

优化表结构

尽量将表字段定义为not null约束, 这时由于在Mysql中含有空值的列很难进行查询优化, null值会使索引以及索引的统计信息变的复杂
对于只包含特定类型的字段, 可以使用enum, set等数据类型
数值型字段的比较比字符串的比较效率高很多, 字段类型尽量使用最小, 最简单的数据类型, 例如:IP地址可以使用int类型
尽量使用tinyint, smallint, medium_int作为整数类型而非int, 如果非负则加上unsigned, 但对整数类型指定宽度, 比如int(11), 没有任何作用因为指定的类型标记范围已经确定
varchar的长度只分配真正想要的空间
尽量使用timestamp而非datetime, 但timestamp只能表示1970-2038年, 比datetime表示的范围小得多, 而且timestamp值因时区不同而不同
单表不要有太多字段, 建议在20以内

表拆分

垂直拆分

资源 : https://www.cnblogs.com/chenhaoyu/p/10737603.html
垂直拆分 : 一个数据库由很多表的构成, 每个表对应着不同的业务, 垂直切分是指按照业务将表进行拆分, 分布到不同的数据库上面, 这样也就将数据或者说压力分担到不同的库上面.
优点 :
拆分后业务清晰, 查分规则明确
系统之间整合或扩展容易
数据维护简单
缺点:
部分业务表无法join, 只能通过接口的方式, 提高了系统复杂度
受每种业务不同的限制存在单库性能瓶颈, 不容易扩展跟性能提高
事务处理复杂

水平拆分

垂直拆分遇到单机瓶颈, 可以使用水平拆分, 相对于垂直拆分的区别是: 垂直拆分是把不同的表拆到不同的数据库汇中, 而水平拆分是把同一个表拆到不同的数据库中.
水平拆分不是将数据分类, 而是按照该表中某个字段的某种规则来分散到多个库中, 每个表中包含一部分数据.
简单来说, 可以将数据水平切分理解为按照数据行的切分, 就是将表中的某些行切分到一个数据库中, 其他行切分到别的数据库中, 主要是分表 分库两种模式.
优点:
不存在单库大数据, 高并发的性能瓶颈
对应用透明, 应用端改造较少
按照合理拆分规则拆分, join操作基本避免跨库
提高了系统的稳定性和负载能力
缺点:
拆分规则难以抽象
分片事务一致性难以解决
数据多次扩展难度跟维护量极大
跨库join性能较差
拆分方案:
范围, 枚举, 时间, 取模 , 哈希, 指定等

表分区

分区适用于比如日志记录, 查询少, 一般用于后台的数据报表分析, 对于这些数据汇总需求, 需要很多日志表去做数据聚合, 我们能够容忍1s-2s的延迟, 只要数据准确能够满足需求就可以
MySql主要支持4种模式的分区 : range分区, list预定义列表分区, hash分区, key键值分区

读写分离

事务与锁

资源来自:
https://blog.csdn.net/fy_java1995/article/details/83445201
https://blog.csdn.net/nicepainkiller/article/details/102892874
事务隔离级别为: 读已提交时, 写数据只会锁住相应的行
事务隔离级别为: 可重复读时(Mysql默认), 如果检索条件有索引时, 默认加锁方式是next-key锁, 如果检索条件没有索引, 更新数据时会锁住整张表, 一个间隙被事务加了锁, 其他事务是不能在这个间隙插入记录的, 这样可以防止幻读
事务隔离级别为串行化时, 读写数据都会锁住整张表
隔离级别越高, 越能够保证数据的完整性和一致性, 但是对并发性能的影响也越大

表锁

表锁就是该锁会将整张表都锁起来, 针对存储引擎来说, 表锁更倾向于MyISAM存储引擎, 开销小,加锁快, 无死锁, 锁定粒度大, 发送锁冲突的概率最高, 并发性能最低
在MyISAM中, 当执行查询语句前, 会自动给相关表加读锁, 在执行增删改操作前自动加写锁
MySQL的表级锁有两种模式:
表共享锁 (读锁) | 表独占锁 (写锁)
读锁会阻塞写锁, 写锁会阻塞读和写
对MyISAM表的读操作, 不会阻塞其他进程的对于同张表的读请求, 但是会阻塞对同张表的写请求, 只有读锁释放了, 才会执行其他进程的写操作
对MyISAM表的写操作, 会阻塞其他进程对同张表的读和写操作, 只有当写操作完成之后, 才会执行其他进程的读写操作

行锁

行锁就是将某一行的数据加锁, 偏向于InnoDB存储引擎, 开销大, 加锁慢, 会出现死锁, 锁定粒度小, 发送锁冲突的概率最低, 并发读最高
当选中某一行时, 如果通过主键或者索引选中的话, 这个时候是行级锁; 如果是通过其他条件选中的, 这个时候行级锁会升级为表锁, 其他事务无法对当前表进行更新或插入

for update

如果在一条select语句后面加入for update, 则查询到的数据会被加上一条排他锁, 其他事务可以读取但是不能更新和插入
适用范围

• A用户消费, service层先查询该用户的账号余额(500), 如果余额足够, 则进行后续的扣款操作; 这种操作应该对该余额信息行进行加锁
• 否则, B用户在A查询余额后, 并且A还没有消费之前把余额转走(转走200,现在余额为300), 则此前A用户已经进性了查询, A还以为余额是500, 很可能在A消费时出现余额不足无法支付成功的问题
• 为了避免这种情况, 需要在A用户操作记录的时候进行for update

间隙锁

当我们使用范围条件查询检索数据时, 并请求共享或排他锁时, InnoDB会给符合条件的索引项加锁, 符合条件范围的数据, 叫做间隙
InnoDB会给合格间隙加锁, 这种锁机制就叫做间隙锁

优化建议

尽可能让所有数据检索都通过索引来完成, 避免无索引行锁升级为表锁
合理设计索引, 尽量缩小锁的范围
尽可能减少索引条件, 避免间隙锁
尽量控制事务大小, 减少锁定资源量和时间长度
尽量低级别事务隔离