MySql优化
@
Mysql逻辑架构
总体分层
连接层
与客户端进行连接的服务
主要完成一些类似连接处理,授权认证 及相关的安全方案.
在该层上引入了连接池的概念,
服务层
提供核心的服务功能
如果sql接口,完成缓存的查询,sql的分析和优化部分及内置函数的执行.
所有跨存储引擎的功能都在这一层实现.
服务器会解析查询并创建相应的内部解析权,并对其完成相应的优化,生成相应的执行操作
服务器还会查询内部的缓存,如果缓存空间足够大,这样可以解决大量读操作的环境中,能够很好的提升系统性能
引擎层
存储引擎是真正负责MYSQL中数据的存储和提取,服务器通过API与存储引擎进行通信
不同的存储引擎提供的功能不同,可以根据自己的实际需求来进行选取
存储层
主要是将数据存储在运行的计算机文件系统之上,并完成与存储引擎的交互
逻辑架构图
逻辑架构组成
- connect连接层
Connection Pool连接池 - 业务层
- Mangement Service
备份,安全,复制,集群 - SQL interface
存储过程,视图,触发器 - Parser解析
查询事务,对象权限 - Optimizer优化器
自带优化器，Mysql自认为最优的形式去优化 - Cache Buffers
缓存
- Mangement Service
- 可拔插组件式存储引擎
常用的MyISAM,InnoDB
不同的存储引擎擅长的操作不同 - File system文件存储
MySql分层好处
哪个出问题,就去找哪个
插件式的存储引擎,将查询处理和其它的系统任务以及数据的提取相分离
可以根据业务的需求和业务的需要选择合适的存储引擎
存储引擎
存储引擎的概念
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。
这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。
通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎
例如：如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。
也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力) 选择支持事务的存储引擎
查看存储引擎
show engines;
查看当前使用的存储引擎
show variables like '%storage_engine%';
MyISAM和InnoDB区别
主外键
MyISAM：不支持
InnoDB：支持
事务
MyISAM：不支持
InnoDB：支持
行表锁
MyISAM：表锁 操作一条记录也会锁住整个表，不适合高并发的操作
InnoDB：行锁 操作时,只锁某一行,不对其它行有影响，适合高并发的操作
缓存
MyISAM：只缓存索引,不缓存数据
InnoDB：不仅缓存索引,还要缓存真实数据,对内存要求比较高,而且内存大小对性能有决定性的影响
表空间
MyISAM：小
InnoDB：小
关注点
MyISAM：性能
InnoDB：事务
默认安装
MyISAM：是
InnoDB：是
文件结构
MyISAM
b.frm ：描述表结构文件，字段长度等
b.MYD(MYData)：数据信息文件，存储数据信息(如果采用独立表存储模式)
b.MYI(MYIndex)：索引信息文件
InnoDB
b.frm ：描述表结构文件，字段长度等
b.ibd：存储数据信息和索引信息
Sql的执行顺序
代码编写顺序
select distinct 查询字段
from 表名
JOIN 表名
ON 连接条件
where 查询条件
group by 分组字段
having 分组后条件
order by 排序条件
limit 查询起始位置, 查询条数
Mysql读取顺序
from 表名
ON 连接条件
JOIN 表名
where 查询条件
group by 分组字段
having 分组后条件
select distinct 查询字段
order by 排序条件
limit 查询起始位置, 查询条数
整体过种
1.先对多表进行关系,根据条件找出符合条件的记录
2.在符合条件的基础上进行再次where条件筛选
3.对筛选出来的内容进行分组操作
4.分组完成后, 使用having再次筛选出满足条件的记录
5.取所满足条件的记录
6.对取出的记录进行排序
7.最终从取出的记录当中获取多少条记录显示出来
JOIN连接
示例表数据
部门表
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptName` varchar(30) DEFAULT NULL,
`address` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `department` VALUES ('1', '研发部(RD)', '2层');
INSERT INTO `department` VALUES ('2', '人事部(HR)', '3层');
INSERT INTO `department` VALUES ('3', '市场部(MK)', '4层');
INSERT INTO `department` VALUES ('4', '后勤部(MIS)', '5层');
INSERT INTO `department` VALUES ('5', '财务部(FD)', '6层');
员工表
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`salary` decimal(10,2) DEFAULT NULL,
`cus_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;
INSERT INTO `employee` VALUES ('1', '鲁班', '1', '10', '1000.00', '1');
INSERT INTO `employee` VALUES ('2', '后裔', '1', '20', '2000.00', '1');
INSERT INTO `employee` VALUES ('3', '孙尚香', '1', '20', '2500.00', '1');
INSERT INTO `employee` VALUES ('4', '凯', '4', '20', '3000.00', '1');
INSERT INTO `employee` VALUES ('5', '典韦', '4', '40', '3500.00', '2');
INSERT INTO `employee` VALUES ('6', '貂蝉', '6', '20', '5000.00', '1');
INSERT INTO `employee` VALUES ('7', '孙膑', '6', '50', '5000.00', '1');
INSERT INTO `employee` VALUES ('8', '蔡文姬', '30', '35', '4000.00', '1');
内连接
图示
作用：查询两张表的共有部分
语句
Select <select_list> from tableA A Inner join tableB B on A.Key = B.Key
示例
SELECT * from employee e INNER JOIN department d on e.dep_id= d.id;
左连接
图示
作用：把左边表的内容全部查出,右边表只查出满足条件的记录
语句
Select <select_list> from tableA A Left Join tableB B on A.Key = B.Key
示例
SELECT * from employee e LEFT JOIN department d on e.dep_id= d.id;
右连接
图示
作用：把右边表的内容全部查出,左边表只查出满足条件的记录
语句
Select <select_list> from tableA A Left Join tableB B on A.Key = B.Key
示例
SELECT * from employee e RIGHT JOIN department d on e.dep_id= d.id;
查询左表独有数据
图示
作用：查询A的独有数据
语句
Select <select_list> from tableA A Left Join tableB B on A.Key = B.Key where B.key IS NULL
示例
SELECT * from employee e LEFT JOIN department d on e.dep_id= d.id WHERE d.id IS NULL;
查询右表独有数据
图示
作用：查询B的独有数据
语句：
Select <select_list> from tableA A Right Join tableB B on A.Key = B.Key where A.key IS NULL
示例
SELECT * from employee e RIGHT JOIN department d on e.dep_id= d.id WHERE e.id IS NULL;
全连接
图示
作用：查询两个表的全部信息
语句
Select <select_list> from tableA A Full Outter Join tableB B on A.Key = B.Key
注:Mysql 默认不支持此种写法 Oracle支持
示例
SELECT * from employee e LEFT JOIN department d on e.dep_id= d.id
UNION
SELECT * from employee e RIGHT JOIN department d on e.dep_id= d.id
查询左右表各自的独有的数据
图示
作用： 查询A和B各自的独有的数据
语句
Select <select_list> from tableA A Full Outter Join tableB B on A.Key = B.Key where A.key = null or B.key=null
示例
SELECT * from employee e LEFT JOIN department d on e.dep_id= d.id WHERE d.id is NULL
UNION
SELECT * from employee e RIGHT JOIN department d on e.dep_id= d.id WHERE e.depart_id is NULL
优化分析
sql性能下降的表现
- 执行时间长
- 等待时间长
性能下降的原因
- 查询语句写的不好
各种连接,各种子查询导致用不上索引或者没有建立索引 - 建立的索引失效
建立了索引,在真正执行时,没有用上建立的索引 - 关联查询太多join
- 服务器调优及和个配置参数导致
如果设置的不合理,比例不恰当,也会导致性能下降,sql变慢
学习
如何写出高质量的sql
如何保证索引不失效
索引
什么是索引
帮助Mysql高效获取数据的数据结构
索引就是数据结构
类似新华字典的索引目录,可以通过索引目录快速查到你想要的字
排好序的快速查找数据
为什么要建立索引
提高查询效率
没有排序之前一个一个往后找
通过索引进行排序之后,可以直接定义到想要的位置
排好序的快速查找数据结构-->就是索引
优势
索引类似大学图书馆建立的书目索引,提高数据检索的效率,降低数据库的IO成本
通过索引对数据项进行排序,降低数据排序成本,降低了CPU的消耗
劣势
一般来说, 索引本身也很大, 索引往往以文件的形式存储到磁盘上
索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录.所以索引也是要占磁盘空间的
虽然索引提高了查询速度,但是会降低更新表的速度.
因为更新表时, MYSQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,
会调整因为更新所带来的键值变化后索引的信息
索引分类
单值索引
一个索引只包含间个列,一个表可以有多个单值索引
一般来说, 一个表建立索引不要超过5个
唯一索引
索引列的值必须唯一,但允许有空值
复合索引
一个索引包含多个列
全文索引
MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。
索引为什么能快速查找数据
概述
在我们存数据时, 如果建立索引
数据库系统会维护一个满足特定查找算法的数据结构,这些数据结构以某种方式引用数据
可以在这些数据结构之上,实现高级查找算法,这种结构就是索引
一般来说, 索引本身也很大, 不可能全部存储在内存中, 因此索引往往以索引文件的形式存储在磁盘上
为了加快数据的查找,可以维护二叉查找树, 每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针,
这样就可以运用二叉查找在一定的复杂度内获取相应的数据,从而快速的检索出符合条件 的记录
除了二叉树还有Btree索引
我平时所说的索引,如果没有特别指定, 都是指B树结构组织的索引
其中聚焦索引,次要索引,复合索引,前缀索引,唯一默认都是B+树索引
除B+树索引之外, 还有哈希索引(Hash index)等
二叉查找树
特性：左子树的键值小于根的键值，右子树的键值大于根的键值
B-Tree
平衡多路查找树
特性
m阶B-Tree满足以下条件：
0.根节点至少包括两个孩子
1.树中每个节点最多有m个孩子(m>=2)
2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
3. 所有叶子节点都在同一层
5. ki(1=1…n)为关键字,且关键字按顺序升序排列k(i-1) < k 8 < 9
6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1 (非叶子节点关键字个数比指向孩子的指针少1个)
7. 非叶子结点的指针p[1],p[2],…p[m] 其中p1指向关键字小于k[1]的子树 3 < 8
p[m]指针关键字大于k[m-1]的子树 15 > 12
p[i]指向关键字属于(k[i-1],k[i])的子树 9,10 是位于8 和 12之间
示例图
B+Tree
B+树是B树的变体,基本与B-Tree相同
不同点
-
非叶子节点的子树指针与关键字个数相同
-
非叶子节点的子树指针,指向关键字值[k[i],k[i+1]]的子树
(10 < 18 < 20) -
非叶子节点仅用来做索引,数据都保存在叶子节点中
-
所有叶子节点均有一个链指针指向下一个 叶子节点
链接起来, 能够方便我们在直接在叶子节点做范围统计
而不是再回到子节点中
一旦定位到某个叶子节点, 便可以从该叶子节点横向的去跨子树去做统计
采用B+Tree做为主流索引数据结构的原因
- 更适合用来做存储索引
- B+树的磁盘读写代价更低
内部的结构并没有指向关键字的具体指针
不存放数据,只存放索引信息
内部节点相对B树更小 - B+树的查询效率更加稳定
内部节点并不是最终指向文件内容的节点,只是叶子节点中关键字的索引,
所以它任何关键字的查找,必须走一条从根节点到叶子节点的路
所有关键字查询的长度相同,导致每一个数据查询的效率也几乎是相同 - B+树更有利于对数据库的扫描
B树在提高IO性能同时,并没有解决元素遍历效率底下问题
B+树只需要遍历叶子节点,就可以解决对全部关键字信息的扫描
对数据库中, 频繁使用的范围查询,性能更高
基本语法
创建索引
create [UNIQUE] index 索引名称 ON 表名(字段(长度))
alter 表名 add [unque] index[索引名称] on(字段(长度))
查看索引
show index from 表名
删除索引
drop index[索引名称] on 表名
更改索引
alter table tab_name add primary key(column_list)
#添加一个主键,索引必须是唯一索引,不能为NULL
alter table tab_name add unque index_name(column_list)
# 创建的索引是唯一索引,可以为NULL
alter table tab_name add index index_name(column_list)
# 普通索引,索引值可出现多次
alter table tab_name add fulltext index_name(column_list)
#全文索引
索引建立选择
适合建立索引
- 主键自动建立唯一索引
primary - 频繁作为查询条件的字段应该创建索引
比如银行系统银行帐号,电信系统的手机号 - 查询中与其它表关联的字段,外键关系建立索引
比如员工,部门外键 - 频繁更新的字段不适合建立索引
每次更新不单单更新数据,还要更新索引 - where条件里用不到的字段不建立索引
- 查询中排序的字段,排序的字段若通过索引去访问将大提升排序速度
索引能够提高检索的速度和排序的速度 - 查询中统计或分组的字段
分组的前提是必排序
不适合建立索引
- 记录比较少
- 经常增删改的表
索引提高了查询的速度
同时却会降低更新表的速度,如果对表的INSERT,UPDATE和DELETE
因为建立索引后, 更新表时, MYSQL不仅要保存数据,还要保存一下索引文件 - 数据重复的表字段,
如果某个数据列包含了许多重复的内容,为它建立索引 就没有太大在的实际效果
比如表中的某一个字段为国籍,性别
数据的差异率和重复率不高,这种建立索引就没有太多意义
性能分析
表设计准则
满足关系数据库的三范式:
1NF
是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值
第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库
2NF
要求数据库表中的每个实例或行必须可以被惟一地区分
设置主键
3NF
要求一个数据库表中不包含已在其它表中已包含的非主关键字信息
两张表不要重复的字段 ,通常都是设置外键
大表拆小表，有大数据的列单独拆成小表
在一个数据库中,一般不会设计属性过多的表;
在一个数据库中,一般不会有超过500/1000万数据的表 拆表
有大数据的列单独拆成小表(富文本编辑器,CKeditor);
DQL的执行过程
SQL的执行过程；
- 客户端发送一条查询给服务器；
- 服务器通过权限检查之后,先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
- 服务器端进行SQL解析、预处理，再由优化器根据该SQL所涉及到的数据表的统计信息进行计算，生成对应的执行计划；
- MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
- 将结果返回给客户端。
查询优化器
写的任何sql,到底是怎么样真正执行的,按照什么条件查询,最后执行的顺序,可能都会有多个执行方案
查询优化器根基对数据表的统计信息(比如索引,有多少条数据),在真正执行一条sql之前,会根据自己内部的数据,进行综合的查询,
根据mysql自身的统计信息, 从多种执行方案当中, 选择一个它认为是最优的执行方案,来去执行
做优化,做什么
做优化, 就是想让查询优化器按照我们的想法,帮我们选择最优的执行方案,
让优化器选择符合程序员计划的执行语句,来减少查询过程中产生的IO
MYSQL常见瓶颈
CPU饱和
磁盘I/0读取数据大小
服务器硬件比较底
如何去判定一个SQL写的好与坏
Explain
查询执行计划
使用explain关键字,可以模拟优化器执行的SQL语句
从而知道MYSQL是如何处理sql语句的
通过Explain可以分析查询语句或表结构的性能瓶颈
作用
查看表的读取顺序
数据读取操作的操作类型
查看哪些索引可以使用
查看哪些索引被实际使用
查看表之间的引用
查看每张表有多少行被优化器执行
Explain
使用方法
explain sql语句
分析包含信息
id
select查询的序列号
包含一组数字,表示查询中执行select子句或操作表的顺序
值的三种情况
- id相同,执行顺序由上到下
EXPLAIN SELECT * from employee e,department d,customer c
where e.dep_id = d.id and e.cus_id = c.id;
- id不同,如果是子查询,id的序号会递增,id值越大优先级越高,优先被执行
EXPLAIN SELECT * from department
WHERE id = (SELECT id from employee WHERE id=(SELECT id from customer WHERE id = 1))
如果是子查询,id的序号会递增,id值越大优先级越高,优先被执行
- id相同不同,同时存在
可以认为是一组,从上往下顺序执行
在所有组中,id值越大,优先级越高,越先执行
deriverd 衍生出来的虚表
EXPLAIN select * from department d,
(select * from employee group by dep_id) t
where d.id = t.dep_id;
总结
相同,顺序走
不同,看谁大
大的先执行
select_type
作用： 查询类型,主要用于区别普通查询,联合查询,子查询等复杂查询
结果值：
- SIMPLE
简单select查询,查询中不包含子查询或者UNION - PRIMARY：查询中若包含任何复杂的子查询,最外层查询则被标记为primary
- SUBQUERY
在select或where中包含了子查询
- DERIVED
在from列表中包含的子查询被标记为derived(衍生)
把结果放在临时表当中
- UNION
若第二个select出现的union之后,则被标记为union
若union包含在from子句的子查询中,外层select将被标记为deriver - UNION RESULT
从union表获取结果select
两个UNION合并的结果集在最后
table
显示这一行的数据是关于哪张表的
partitions
如果查询是基于分区表的话, 会显示查询访问的分区
type
访问类型排列
结果值最好到最差
- system
表中有一行记录(系统表) 这是const类型的特例,平时不会出现 - const
表示通过索引一次就找到了
const用于比较primary 或者 unique索引. 直接查询主键或者唯一索引
因为只匹配一行数据,所以很快
示例
- eq_ref
唯一性索引扫描
对于每个索引键,表中只有一条记录与之匹配
常见于主键或唯一索引扫描
- ref
非唯一性索引扫描,返回匹配某个单独值的所有行
本质上也是一种索引访问
它返回所有匹配某个单独值的行
可能会找到多个符合条件的行,
所以它应该属于查找和扫描的混合体
- range
只检索给定范围的行,使用一个索引来选择行
key列显示使用了哪个索引
一般就是在你的where语句中出现between<>\ in等查询
这种范围扫描索引比全表扫描要好
因为它只需要开始于索引的某一点.而结束语另一点
不用扫描全部索引
- index
Full Index Scan
index与All区别为index类型只遍历索引树,通常比All要快,因为索引文件通常比数据文件要小
all和index都是读全表,但index是从索引中读取,all是从硬盘当中读取
- ALL
将全表进行扫描,从硬盘当中读取数据
如果出现了All 切数据量非常大, 一定要去做优化
要求
一般来说,保证查询至少达到range级别
最好能达到ref
possible_keys
key与keys主要作用,是查看是否使用了建立的索引, 也即判断索引失效
在建立多个索引 的情况下, mysql最终用到了哪一个索引
possible_keys
显示可能应用在这张表中的索引,一个或者多个
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
可能自己创建了4个索引,在执行的时候,可能根据内部的自动判断,只使用了3个
key
实际使用的索引,如果为NULL,则没有使用索引
查询中若使用了覆盖索引 ,则该索引仅出现在key列表中
possible_keys与key关系 理论应该用到哪些索引 实际用到了哪些索引
覆盖索引 查询的字段和建立的字段刚好吻合,这种我们称为覆盖索引
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引长度 .
ref
索引是否被引入到, 到底引用到了哪几个索引
rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
每长表有多少行被优化器查询过
- 没有建立索引
- 建立了索引后
filtered
满足查询的记录数量的比例，注意是百分比，不是具体记录数
值越大越好，filtered列的值依赖统计信息，并不十分准确
Extra
产生的值
- Using filesort
说明mysql会对数据使用一个外部的索引排序,
而不是按照表内的索引顺序进行
Mysql中无法利用索引完成排序操作称为"文件排序"
示例
- Using temporary
使用了临时表保存中间结果,Mysql在对查询结果排序时, 使用了临时表,
常见于排序orderby 和分组查询group by
示例
- use index
表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好
如果同时出现using where 表明索引被用来执行索引键值的查找
如果没有同时出现using where 表明索引 用来读取数据而非执行查找动作
- using where
表明使用了wher过滤 - using join buffer
使用了连接缓存 - impossible where
where 子句的值总是false 不能用来获取任何元组
索引失效
全值匹配(最好)
建立复合索引(name,age,salary)
最佳左前缀法则
如果索引的多列,要遵守最左前缀法则,指的就是从索引的最左列开始 并且不跳过索引中的列
跳过第一个,索引失效
跳过前两个, 索引失效
跳过中间一个 ,只有第一个生效
顺序可以乱,
不在索引列上做任何操作
计算,函数,类型转换
会导致索引失效而转向全表扫描
正常状态
添加了运算
范围条件右边的索引失效
全部使用
使用了范围
mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
等于
不等于
is not null 无法使用索引
少用or 用or连接时, 会导致索引失效
like以通配符开头(%qw)索引失效变成全表扫描
使用%开头
使用%结尾
使用覆盖索引解决两边%
字符串不加引号索引失效
尽量使用覆盖索引
覆盖索引 查询的字段和建立的字段刚好吻合,这种我们称为覆盖索引
排序与分组优化
索引做为排序时
只用到了 name和age salary是作为排序,而不是查找
使用order by排序时, 如果没有按照索引顺序,会出现Using filesort
当使用*时 order by即使使用了 全部索引,也会也filesort
当索引字段为常量时 可以当作是存在索引的
使用排序一升一降会造成filesort
使用group by时,使用不当, 会出现Using temporary
解决办法和排序一样, 都要按索引顺序进行分组
索引相关面试题
说出以下语法使用索引的情况
假设建立复合索引(a,b,c),请说出以下条件是否使用到了索引及使用情况
- where a = 4
使用到了索引a - where a = 4 and b = 6;
使用到了索引a,b - where a = 4 and c = 5 and b = 6 ;
使用到了a,b,c - where b = 4 or b=5;
没有使用到索引 - where a = 4 and c = 6;
使用到了索引a - where a = 4 and b > 5 and c=6;
使用到索引a,b - where a = 4 and b like 'test%' and c=4
使用到了a,b kk%相当于范围 - where a = 4 order by b,c
使用到了a,不会有filesort - where b = 5 order by a
没用到索引 会有filesort - where b = 5 order by c
没有索引,会有filesort - where a = 5 group by c,b
使用到了索引a, 造成Using temporary;
大批量数据时分页操作如何优化
批量插入数据
建立表
DROP TABLE IF EXISTS `testemployee`;
CREATE TABLE `testemployee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`salary` decimal(10,2) DEFAULT NULL,
`cus_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;
随机生成字符串
#随机生成一个指定个数的字符串
delimiter $$
create function rand_str(n int) RETURNS VARCHAR(255)
BEGIN
#声明一个str 包含52个字母
DECLARE str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
#记录当前是第几个
DECLARE i int DEFAULT 0;
#生成的结果
DECLARE res_str varchar(255) default '';
while i < n do
set res_str = CONCAT(res_str,substr(str,floor(1+RAND()*52),1));
set i = i + 1;
end while;
RETURN res_str;
end $$
delimiter ;
set global log_bin_trust_function_creators=TRUE
批量插入数据
delimiter $$
create procedure insert_emp(in max_num int)
BEGIN
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into testemployee (name,dep_id,age,salary,cus_id) values(rand_str(5),floor(1 + rand()*10),floor(20 + rand()*10),floor(2000 + rand()*10),floor(1 + rand()*10));
until i = max_num
end REPEAT;
commit;
end $$
delimiter ;
传统分析查询
使用limit 随着offset增大, 查询的速度会越来越慢,
会把前面的数据都取出,找到对应位置
优化后分页查询
- 使用子查询优化
select * from employ e inner join (SELECT id from employ limit 500000 ,10 ) et on e.id = et.id
select * from employee where id >=(SELECT id from employee limit 500000 , 1) limit 10
- 使用 id 限定优化
记录上一页最大的id号 使用范围查询
限制是只能使用于明确知道id的情况，不过一般建立表的时候，都会添加基本的id字段，这为分页查询带来很多便利
select * from employee where id between 1000000 and 1000100 limit 100;
select * from orders_history where id >= 1000001 limit 100;
小表驱动大表
小表驱动大表，即小的数据集驱动大得数据集
类似嵌套循环
for(int i=5;.......)
{
for(int j=1000;......)
{}
}
如果小的循环在外层，对于数据库连接来说就只连接5次，进行1000次操作
如果1000在外，则需要进行1000次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表
in与exists
exist可以替代in
in
exist
in与exist选择
当A 表中数据多于 B 表中的数据时,这时我们使用IN 优于 EXISTS
当B表中数据多于 A 表中的数据时，这时我们使用EXISTS 优于 IN
因此是使用IN 还是使用EXISTS 就需要根据我们的需求决定了。但是如果两张表中的数据量差不多时那么是使用IN 还是使用 EXISTS 差别不大
EXISTS 子查询只返回TRUE 或 FALSE ，因此子查询中的SELECT * 可以是SELECT 1 或者其他
锁
概念
锁，在现实生活中是为我们想要隐藏于外界所使用的一种工具
在计算机中，是协调多个进程或线程并发访问某一资源的一种机制
在数据库当中，除了传统的计算资源（CPU、RAM、I/O等等）的争用之外，数据也是一种供许多用户共享访问的资源
如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，
锁的冲突也是影响数据库并发访问性能的一个重要因素
举例
在购买商品时, 商品库存只有1个时 ,两个人同时买时, 谁买到的问题
会用到事务, 先从库存表中取出物品的数据, 然后插入订单,付款后,插入付款表信息
更新商品的数量, 在这个过程中, 使用锁可以对有限的资源进行保护,解决隔离和并发的矛盾
锁分类
- 按操作分
读锁(共享锁)：针对同一份数据,多个读取操作可以同时进行而不互相影响
写锁(排它锁)：当前写操作没有完成前,会阻断其他写锁和读锁 - 按粒度分：表锁、行锁、页锁
表锁
偏向MYISAM存储引擎,开销小, 加锁快, 无死锁,锁定粒度大, 发生锁冲突的概率最高, 并发最底
整张表就只能一个人使用
示例
1.建立一张Myisam引擎的表
CREATE TABLE `locktest` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
2.查看表有没有被锁过
show open tables;
3.对表加锁
lock table locktest read, locktest2 write;
4.对表进行解锁
unlock tables
读写锁对操作和性能产生哪些影响
-
对locktest添加读锁 lock table locktest read;(共享)
当前连接
1. 是否可以查看自己：可以
2. 是否可以更新：不可以
3. 能不能读别的表：不可以 当前表还没有解锁,不能放下当前, 操作别的内容
另一个连接
1. 是否可以查看：可以
2. 是否可以更新：当更新时, 处理阻塞状态,等待解锁后, 才能进行更新
3. 能不能读别的表：可以 -
对locktest添加写锁 lock table locktest write;(排它)
当前连接- 能否读自己锁过的表：可以
- 能否改自己锁过的表：可以
- 能否读取别的表：不可以
另个连接
- 能否没有加过锁的表：可以
- 能否对被锁过的表进行操作： 阻塞：等待解锁时, 才能查到
表锁分析
show status like 'table%';
Table_locks_immediate
产生表级锁定的次数
表示可以立即获取锁的查询次数
Table_locks_waited:
出现表级锁定争用而发生等待的次数
Myisam的读写锁调度是写优先,这也是myisam不适合做写为主表的引擎
因为写锁后, 其它线程不能做任何操作,大量更新会使用查询很难得到锁, 从而造成永久阻塞
行锁
偏向InnoDB存储引擎,开销大, 加锁慢, 会出现死锁;锁定粒度最小, 发生锁冲突的概率最底,并发度也最高
InnoDB与MyISAM的最大不同点:一是支持事务, 二是采用了行级锁
事务
ACID属性
- 原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚 - 一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
让数据保持一定上的合理
一个商品出库时，仓库商品数量减1，对应用户的购物车中商品加1 - 隔离性（Isolation）
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。 - 持久性（Durability）
持久性是指一个事务一旦被提交了，就不能再回滚了，已经把数据保存到数据库当中了。
并发事务处理带来的问题
- 更新丢失
两个或多个事务选择同一行,然后基于最初选定的值更新该行时,
由于每个事务都不知道其它事务的存在 , 就会发生丢失更新 问题,
最后 的更新覆盖了其它事务所做的更新 - 脏读
老板要给程序员发工资，程序员的工资是3.6万/月。
但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，
就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。
但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交
实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。
事务A读到了事务B已修改,但尚未提交的数据
解决办法：Read committed！读提交，能解决脏读问题
- 不可重复读
程序员拿着工资卡（卡里当然是只有3.6万），当他买单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，
就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。
当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了。程序员就会很郁闷，明明卡里是有钱的…
一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读 事务A读取到了事务B已经提交的修改数据
解决办法：Repeatable read
- 重复读
程序员拿着工资卡（卡里还是有3.6万），当他买时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。
这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。
- 幻读
程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（妻子事务开启）
看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。
当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。
解决办法：Serializable
但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
对应关系
| 事务隔离级别 |脏读 | 不可重复读|幻读 |
|--|--|--|--|--|--|--|
| 读未提交（read-uncommitted） | 是 |是| |是|
| 不可重复读（read-committed） | 否 |是| |是|
| 可重复读（repeatable-read） | 否 |否 | |是|
| 串行化（serializable） | 否 |否 | |否|
事务隔离级别
- Read uncommitted
就是一个事务可以读取另一个未提交事务的数据 - Read committed
一个事务要等另一个事务提交后才能读取数据 - Repeatable read
就是在开始读取数据（事务开启）时，不再允许修改操作 - Serializable
在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读
查看隔离级别
select @@global.tx_isolation,@@tx_isolation;
设置隔离级别
全局的
set global transaction isolation level read committed;
当前会话
set session transaction isolation level read committed;
演示行锁
5.5以后, 默认后, 事务会自动提交
由于演示,开两个连接 都关闭自动提交
set autocommit = 0
做以下操作
- 执行更新操作,
自己可以查看到更新的内容
连接2看不到更新的内容,只有commit后, 才能看到更新的内容 - 连接1执行更新操作,连接2也执行更新操作,更新同一条记录
连接1没有提交事务时, 连接2更新处于阻塞状态
当commit时, 连接2才会继续执行 连接2更新也要commit - 连接1和连接2同时更新数据,但更新的不是同一条记录
不会影响
索引失效,行锁变表锁
使用varchar类型时, 没有添加引号, 导致索引失效
就会造成行锁变表锁, 另一个连接更新数据时, 会造成阻塞
间隙锁
概念
当我们使用范围条件,而不是相等条件检索数据,并请求共享或排它锁时,
InnoDB会给符合条件的已有数据记录的索引项加锁
对于键值在条件范围内但并不存在的记录 叫做"间隙"
InnoDB也会对这个"间隙"加锁,我种锁机制,就是所谓的间隙锁
如何锁定一行数据
在查询之后添加for update
其它操作会被阻塞,直到锁定的行提交commit;
show status like 'innodb_row_lock%';
悲观锁
就是很悲观，它对于数据被外界修改持保守态度，认为数据随时会修改，
所以整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系数据库提供的锁机制，
事实上关系数据库中的行锁，表锁不论是读写锁都是悲观锁
乐观锁
顾名思义，就是很乐观，每次自己操作数据的时候认为没有人回来修改它，所以不去加锁
但是在更新的时候会去判断在此期间数据有没有被修改，需要用户自己去实现
不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性
为什么要使用乐观锁呢？
对于读操作远多于写操作的时候，大多数都是读取，这时候一个更新操作加锁会阻塞所有读取，降低了吞吐量。
最后还要释放锁，锁是需要一些开销的，我们只要想办法解决极少量的更新操作的同步问题。
换句话说，如果是读写比例差距不是非常大或者你的系统没有响应不及时，吞吐量瓶颈问题，那就不要去使用乐观锁，它增加了复杂度，也带来了额外的风险。
乐观锁的实现方式
- 版本号
就是给数据增加一个版本标识，在数据库上就是表中增加一个version字段
每次更新把这个字段加1
读取数据的时候把version读出来，更新的时候比较version
如果还是开始读取的version就可以更新了
如果现在的version比老的version大，说明有其他事务更新了该数据，并增加了版本号
这时候得到一个无法更新的通知，用户自行根据这个通知来决定怎么处理，比如重新开始一遍。
- 时间戳
和版本号基本一样，只是通过时间戳来判断而已，注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间
同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，
字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比
如果一致则OK，否则就是版本冲突。
