logo头像
ICQL

410_mysql

高性能MySQL/MySQL技术内幕 部分内容 阅读笔记

数据类型

  • 选择原则

    • 尽量选择小的
    • 选择简单的;整型 > 字符串
    • 避免可为null
  • 整数类型(singed存储范围 -2^(n-1) ~ 2^(n-1),unsingned存储范围0 ~ 2^n)

    • tinyint 1个字节
    • smallint 2个字节
    • mediumint 3个字节
    • int 4个字节
    • bigint 8个字节
  • 实数类型

    • float 单精度浮点型,存在数据精度丢失
    • double 双精度浮点型,存在数据精度丢失
    • decimal(a,b) 小数类型优先使用此类型,a代表总位数,包括整数位+小数点1位+小数位b
  • 字符串类型

    • varchar 可变字符串
    • char 定长字符串
      1
      2
      3
      4
      5
      6
      #存储字节数
      #varchr(10)变长字段且允许NULL = 10 * ( character set:utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
      #varchr(10)变长字段且不允许NULL = 10 *( character set:utf8=3,gbk=2,latin1=1)+2(变长字段)

      #char(10)固定字段且允许NULL = 10 * ( character set:utf8=3,gbk=2,latin1=1)+1(NULL)
      #char(10)固定字段且不允许NULL = 10 * ( character set:utf8=3,gbk=2,latin1=1)
    • text与smalltext同义,存储的是普通字符集
      • tinytext
      • smalltext
      • mediumtext
      • longtext
    • blob与smallblob同义,存储的是二进制数据
      • tinyblob
      • smallblob
      • mediumblob
      • longblob
  • 日期和时间类型

    • date 日期
    • time 时间
    • year 年份
    • datetime 日期时间精确到秒 1001-9999年,优先使用
    • timestamp 日期时间精确到秒 1970/1/1格林尼治时间开始后-2038年,范围小
  • 位数据类型

    • bit 存储0/1 true/false
    • set
  • 选择标识符

  • 特殊类型数据:

    • 钱相关可以使用bigint等整数存储,单位为分
    • ip可以使用32位无符号整数存储
    • SELECT INET_NTOA(‘2886736938’),INET_ATON(‘172.16.28.42’)

数据库三范式

  • 1NF:数据库表的每一列都是不可分割的原子数据项,而不能是集合,数组,记录等非原子数据项
  • 2NF:在1NF基础上,要求实体的属性完全依赖于主键。所谓完全依赖是指不能存在仅依赖联合主键中的一部分的列
  • 3NF:在2NF基础上,要求任何非主主键列不依赖于其它非主键列,消除传递依赖
  • 反范式

存储结构

  • 表空间(相当于库)

索引

  • B树/B+树:二叉搜索树->平衡二叉树->(2-3树:3阶b树)b树->b+树 ,依次演变而成

  • 索引种类

    • BTree索引

      • 1)聚集索引(生成唯一一颗叶子结点存储所有数据的B+树)

        • (1)如果有主键,则主键索引为主键字段
        • (2)如果没有主键,但表中的字段有”唯一非空”索引(多个的话取定义时的第一个),则以这个字段为聚集索引
        • (3)如果没有主键,也没有”唯一非空”索引字段,则内部隐式生成一个聚集索引
      • 2)非聚集索引(每个会生成一颗B+树,叶子结点只存储对应的聚集索引key,不存储数据)

        • 普通索引
        • 唯一索引(和普通索引一样,只起到新增数据时保证数据唯一的功能)
        • 多列索引(最左前缀原则)
    • 哈希索引

    • 全文索引

    • 空间索引

事务与锁

  • 事务:一组业务操作ABCD,要么全部成功,要么全部不成功

  • mysql事务基本命令:begin/commit/rollback

  • 事务基本特性ACID

    • 原子性Atomicity:整体
    • 一致性Consistency:完成
    • 隔离性Isolation:并发
    • 持久性Durability:结果
  • 1、事务隔离性Isolation(mvcc + 锁 实现)

    • 4个问题

      • 1)脏读:一个事务中,select到另一个事务没有提交的数据
      • 2)不可重复读:一个事务中,2次select读取同一条数据得到不同的结果(原因:别的事务对这条数据做了update,且别的事务已经提交)
      • 3)幻读:一个事务中,2次select读取相同条件的数据得到的条数不同(原因:别的事务在这个范围内做了insert或delete)
      • 4)丢失更新
    • 4种隔离级别

      • 1)Read Uncommitted:读未提交,未解决上述任何问题
      • 2)Read Committed:读已提交,解决:脏读,SqlServer/Oracle默认隔离级别
      • 3)Repeatable Read:可重复读,解决:脏读、不可重复读、幻读,Mysql默认隔离级别
      • 4)Serializable :串行化,解决4个问题
    • 实现原理

      • 1)非锁定读(快照读,mvcc)

        • 不需要等待其他事务释放数据行的锁,直接使用undo log读取快照数据
        • RC总是读取最新的快照数据,而RR读取事务开始时的行快照数据
        • mvcc原理:
          mvcc原理1
          mvcc原理2
      • 2)锁定读(当前读,使用锁,只能存在与事务中,commit或rollback结束将释放所有锁,InnoDb是行锁,而MyISAM是表锁)

        • 读锁(共享锁):select … lock in share mode;加入读锁时,不能有写锁,可以有读锁
        • 写锁(排他锁):select … for update;加入写锁时,该数据不能有任何的锁(包括读锁和写锁),否则会一直阻塞直至没锁
        • DML操作会隐式的对所需要操作的数据加写锁
        • 3种算法
          • (1)Record Lock:单个行记录上的锁
          • (2)Gap Lock:间隙锁,锁定范围但不包括记录本身
          • (3)Next-Key Lock:Record Lock + Gap Lock,锁定包含记录本身和记录相邻两侧的开区间范围的数据
          • (4)mysql默认隔离级别repeatable read,使用的是 Next-Key Lock 算法
            • a)使用的是主键索引:
              • 将只锁住主键树的对应的实际数据行
              • (使用的是Next-Key Lock中的Record Lock加锁)
            • b)使用的是唯一索引:
              • 将锁住唯一索引树对应的索引记录行 和 唯一索引对应的主键索引实际数据行
              • (使用的是Next-Key Lock中的Record Lock加锁)
            • c)使用的是普通索引
              • 将锁住 1普通索引的索引记录行 和 2普通索引对应的主键索引实际数据行 和 3普通索引相邻数据的闭区间
              • (使用的是Next-Key Lock中的Record Lock加锁1和2)
              • (使用的是Next-Key Lock中的Gap Lock加锁3)
            • d)丢失更新解决
              • 乐观锁:类似于 update table set field=value where id=? and version=? ,如果返回1代表成功,否则代表失败
              • 悲观锁:利用for update写锁保证

Next-Key Lock

sql优化

  • explain指令
    explain指令

  • 1)优化数据访问:

    • 确定只返回需要的数据:包括列和行
      • 使用limit限制行数据
      • 查询列尽量不要使用*,只取需要的列,优化器可能使用覆盖索引优化
      • 缓存查询结果集
    • mysql是否在扫描额外的记录
  • 2)COUNT():如果统计的是行数优先使用count(*),count(1)也一样,但是count(列)就只会统计不为null的记录数

  • 3)关联查询:on或using子句中的列上一定要有索引,优化器explain后是先A表后B表,则B表上的on中的列一定要建索引,group by和order by中的列最好只有一个表中的列

  • 4)使用覆盖索引

    • 一般来说,如果走的是非聚集索引,是要查询2次B+树的,第1次查询非聚集索引B+树找到对应的聚集索引的key,第2遍根据得到的key查询聚集索引B+树找到结果(又叫回表查询)
    • 如果select的列只有用到的走的索引的列(包含多列索引),那么将只会查询1次非聚集索引B+树,极大的提高查询效率,这样称之为覆盖索引,explain的Extra中显示Using index
  • 5)前缀索引:字符串类型的列作索引时只取前面的部分,能有效减小索引文件的大小,提高索引的速度

    • 坏处:不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引
  • 6)排序尽量使用索引排序

  • 7)尽可能重用索引,使用多列索引,避免建立多个索引

    • 将选择性低查询性高的列放到最前面,如性别等,查询时若不需要此列,可以使用如 in(0,1) 巧妙的绕过”最左前缀匹配”,从而使用索引
    • 将范围查询的列放到最后面,mysql无法在使用索引范围列后使用其他列,且范围查询列无法与其他列组成索引
  • 8)union:除非需要distinct,否则使用union all

  • 9)分页优化:分页查询后面的数据会比较耗时,可以利用覆盖索引和关联来优化

    1
    2
    3
    4
    5
    6
    7
    8
    9
    #优化前
    SELECT * FROM `tbl_works` WHERE `status`=1 LIMIT 100000, 10 // 78.3 秒

    #优化后
    SELECT * FROM tbl_works t1
    inner join (SELECT id from tbl_works WHERE status=1 limit 100000, 10) t2
    ON t1.id = t2.id
    #53.6 ms
    #这条SQL的含义是,通过自连接与join定位到目标 ids,然后再将数据取出。在定位目标 ids时,由于SELECT的元素只有主键ID,且status存在索引,因此MySQL只需在索引中,就能得到ids,不用回表查询。因而,查询效率非常高
  • 10)https://blog.csdn.net/student__software/article/details/82078786

    1
    2
    3
    4
    5
    6
    #全值匹配我最爱,最左前缀要遵守
    #带头大哥不能死,中间兄弟不能断
    #索引列上少计算,范围之后全失效
    #LIKE百分写最右,覆盖索引不写*
    #不等空值还有OR,索引影响要注意
    #VAR引号不可丢, SQL优化有诀窍

sql基本语法:旧笔记/暂存/未整理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
--sql语言分类
1)数据查询语言DQL:SELECT
2)数据操纵语言DML:INSERT/UPDATE/DELETE
3)数据定义语言DDL:用来创建数据库中的各种对象-----表、视图、
索引、同义词、聚簇,truncate等,DDL操作是隐性提交的,不能rollback
4)数据控制语言DCL:BEGIN/ROLLBACK/COMMIT等等逻辑控制语言

--创建删除数据库
create database TEST
drop database TEST
--切换数据库
use master
use TEST
--创建删除表
create table tbStudent
(
tsid int identity(1,1) primary key not null,
tsname nvarchar(50) not null,
tsaddress nvarchar(50) not null
)
drop table tbStudent
--约束
--非空约束 not null
--主键约束 primary key 唯一且不能为空
--检查约束 check constraint 范围以及格式约束(字段插入的条件)
--唯一约束 unique constraint 唯一,允许为空,但只能出现一次
--默认约束 default constraint 默认值
--外键约束 foreign key constraint 表关系
--
--查询数据
select * from tbStudent
--别名:as用来给查询结果的 列 起别名,as可省略,非法字符用''括起来
select tsname as 姓名,tsaddress as '/地址/' from tbStudent
--别名:用=号,等号还可以自定义添加列,后面可以跟任意函数,或者值
select
姓名=tsname,
'/地址/'=tsaddress,
啦啦啦='d',
当前系统时间=GETDATE()
from tbStudent
--select 可以单独使用,不跟from table
select
啦啦啦='d',
当前系统时间=GETDATE()
--distinct关键字,针对已经查询出的结果去除重复
select distinct tsname,tsaddress from tbStudent
--top关键字,获取前几条数据,top一般和order by一起使用 才有意义
--order by 列名 排序方式, 排序(desc降序,asc升序(默认))
--所有表达式要用()括起来
select top 5 * from tbStudent order by tsid desc
select top (2*2) * from tbStudent order by tsid desc
select top 30 percent * from tbStudent order by tsid desc
--条件查询 select 列 from 表 where 条件
select * from tbStudent where tsid<8 and tsid>3
select * from tbStudent where tsid between 3 and 5
--模糊查询(只针对字符串),关键字 like,not like
--通配符:转义将以下四个放入[]中
-- _ 表示任意的单个字符
-- % 表示任意多个任意字符
-- [] 表示筛选,范围
-- ^ 表示非
select * from tbStudent where tsaddress like '北京__'
select * from tbStudent where tsaddress like '北%'
select * from tbStudent where tsname like 'yang[0-5]'
--联合 union 两个处理过后的表必须 列个数,列数据类型相同
select 列名1,列名2,列名3 from1
union
select 列名1,列名2,列名3 from2
--union 和 union all 都能进行联合,区别在于union联合会去除重复,并且重新排列数据
--而 union all 不会去重也不会重排,一般用 union all
--连接查询
内连接(inner join)
select * from1 inner join2 on 条件 (条件:必须是布尔表达式)
外连接

--case 用法(条件选择查询)
--实现if else效果(case end相当于括号,when相当于if,then相当于{}里的结果)
select
*,头衔=case
when CARNO=7830001 then '菜鸟'
when CARNO=7830002 then '老鸟'
else '骨灰级'
end
from NE_CARD_FULLCARDINFO
--实现switch效果
select
*,头衔=case carno
when 7830001 then '菜鸟'
when 7830002 then '老鸟'
else '骨灰级'
end
from NE_CARD_FULLCARDINFO
--子查询(把一个查询的结果在另外一个查询中使用就叫子查询)
--分类
--独立子查询(子查询可单独执行)
select * from NE_CARD_FULLCARDINFO
where ptid=(select ptid from pt where ptname='亲人')
select * from (select * from NE_CARD_FULLCARDINFO where ID>10) a
(注意:如果将一个子查询作为另一个查询的源,必须起别名)
--相关子查询
select * from NE_CARD_FULLCARDINFO a where
exists(select * from pt b where a.ptid=b.ptid and b.ptname='亲人')
--exists()函数,只要查询到数据返回true,否则false
--子查询实现分页数据
--首先排序

--
--新增插入数据
insert into tbStudent(tsname,tsaddress) values('yhmi','30')
--数据库排序规则问题,一般在 字符串前加N防止中文乱码,字符串只能单引号
insert into tbStudent
values('yang3',N'北京')
--使用union 一次向表中插入多条数据
insert into student
select '杨米米',45,'男','上海',1
union all
select '杨米米',45,'男','上海',1
union all
select '杨米米',45,'男','上海',1
--
--更新数据
update tbStudent
set tsname=tsname+'(HAHA)',tsaddress=tsaddress+'地址'
where tsid=2
--
--删除数据
--delete删除数据,自动编号不会恢复默认值
delete from tbStudent where tsid=3
--truncate只能删除所有数据,自动编号恢复默认值
truncate table tbStudent

--聚合函数(只针对数值使用),对分组数据进行处理(无分组,默认整表为一组),注意:不统计空值null
--求和 sum()
--计数 count()
--平均 avg()
--最大 max()
--最小 min()
select SUM(tsid) as id总和 from tbStudent
select id总和=SUM(tsid) from tbStudent
select COUNT(*) from tbStudent
select COUNT(tsid) from tbStudent
select id平均值=SUM(tsid)/COUNT(*) from tbStudent
select id平均值=AVG(tsid) from tbStudent
select max(tsid) from tbStudent
select min(tsid) from tbStudent
--分组group by
--只要有group by,整个式子中的列名 只能是 group by的列名 或者 包含在聚合函数里的列名
--having 是筛选分组后的 组数据(按照组来筛选)-
select
'班级人数'=COUNT(*),
'最高分'=MAX(math),
'最低分'=MIN(math),
'分数和'=SUM(math),
'平均分'=avg(math),
'班级id'=classid
from student
group by classid
having classid=1
order by 平均分 desc

--
--复制一个新的表,但是约束并不能复制
--表可能不存在
select * into studentbackup20170628 from student
select top 0 * into studentbackup20170629 from student --只复制表结构
--表存在
insert into studentbackup20170629
select * from student where sex='男'
--
--null处理
--null值无法使用=<>,必须使用 is null , is not null
--任何值和null计算都是null,因此如果表内有null,先判断是否为null
--
--order by 语句排序
--降序 order by 列名 desc
--升序 order by 列名 asc 或者 升序 order by 列名
--order by 必须在整个sql语句的最后
--多列排序
--用了 order by排序后叫游标,不能再用作集合嵌套使用
--
--sql 语句执行顺序
-- 5> select 5.1>选择列,5.2>distinct,5.3>top()
-- 1> from 表
-- 2> where 条件
-- 3> group by 列
-- 4> having 筛选条件
-- 6> order by 列
--
--常用函数
--字符串函数
--len()字符个数
--datalength()字节的个数
--upper()大写
--lower()小写
--去掉两段空格 rtrim(),ltrim()
--字符串截取 left("",个数数字) right("",个数数字) substring("",开始数字1,个数数字2)
print datalength('杨得到')
print left('哈哈昂哈啊哈',5)
--日期函数
--getdate()
--类型转换函数
--cast(原表达式 as 要转换成的类型)
--convert(类型,表达式)一般
print convert(varchar(200),getdate(),109)
--
--视图view(只能用来查询)
视图是用来封装查询结果集的复杂sql语句,只作引用,虚拟的,查询操作和查询表一样
--
--T-Sql编程
--声明变量(局部)
declare @name nvarchar(50)
declare @age int
declare @name nvarchar(50),@age int --一次声明多个变量
--为变量赋值
set @name='123'
--输出
select 'sf',@name
print '526' --print 只能输出一个值
--while 循环
declare @i int=1 --声明变量同时赋值
while @i<=100
begin
print 'Hello!'
set @i=@i+1
end
--if
if @i>2
begin
print 's'
end
--系统变量@@
@@version
--
--事务 translation(保证多条更新插入语句同时成功)
开始事务 begin translation
提交事务 commit translation
回滚事务 rollback translation

begin translation
declare @sum int=0
update.....
set @sum=@sum+@@error

update.....
set @sum=@sum+@@error

insert.....
set @sum=@sum+@@error
if @sum<>0
begin
rollback
end else
begin
commit
end
--
--存储过程(类似方法函数)
--
--触发器
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
--重复数据只显示一条:
select min(id) id,b,c from tb group by b,c

--删除重复数据:
delete from tb where rowid not in (select min(rowid) from tb group by b,c)

--分页
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(order by A.CarModel) AS Row, A.*
from EnergyTypeSt_New A
WHERE 条件
) TT
WHERE TT.Row between {0} and {1}

startIndex * pageSize + 1, startIndex * pageSize + pageSize

--Oracle 自增id
--Oracle用 <序列sequence+触发器trigger>的方式使数据表的一列或多列实现自增

--1. 创建序列Sequences
create sequence NE_FULLCARDINFO_ID --创建的序列名
minvalue 1
nomaxvalue
start with 1
increment by 1
nocache;
--2. 在表中创建触发器trigger
create or replace trigger TRIGGER_ID --TRIGGER_ID触发器名
before insert
on NE_CARD_FULLCARDINFO --NE_CARD_FULLCARDINFO操作的表名
for each row
declare
-- local variables here
nextid number;
begin
IF :new.ID IS NULL or :new.ID=0 THEN --ID是列名
select NE_FULLCARDINFO_ID.nextval --NE_FULLCARDINFO_ID创建的序列名
into nextid
from sys.dual;
:new.ID:=nextid; --ID是列名
end if;
end TRIGGER_ID; --TRIGGER_ID触发器名
--3. Oracle中创建、修改、删除序列
--http://www.cnblogs.com/nicholas_f/articles/1525585.html
微信打赏

赞赏是不耍流氓的鼓励

s"> 415_redis