数据库基础
数据库
基本语法
聚合函数
AVG()
COUNT()
MIN()
MAX()
SUM()
GROUP_COUNT(col)返回属于一组的列值连接组合而成的结果
表连接
内连接
外链接
交叉连接
自连接
子查询 IN \ NOT IN \ EXIST \NOT EXIST\ = <>
子查询低于连接查询
事务的四个属性 原子性一致性隔离性持久性ACID
事务的四种隔离级别
1.read uncommitted(未提交读) 脏读
2.read committed提交读
3.repeatable read可重复读
4.serializable(可串行化) 最高隔离级别
MySQL中的事务
事务的实现基于数据库的存储引擎 不同的存储引擎对事物的支持度不一样
mysql的存储引擎支持事务的innoDB
事务的隔离级别分别是通过锁实现的,而事务的原子性一致性持久性是通过事务日志实现的
事务日志 重做日志redo undo只要为事务的回滚服务
索引
一种特殊的文件,InnoDB数据表上的索引是表空间的一个组成部分,包含数据表里所有文件的指针
索引是创建在数据库表对象上的,由表中的一个字段或者多个字段生成的键组成.这些键存储在数据结构上(B-树或者哈希表) 通过mysql可以快速有效的查找与兼职相关联的字段
根据存储类型分为B-树(InnoDB\MyISAM)和哈希索引(Memoery)
索引分类
普通索引最基本的索引无任何限制myiasm中默认的Btree类型的索引
--直接创建
create INDEX index_name ON table (colum(length))
--修改表结构添加
alter Table table_name ADD INDEX index_name ON (colum(length))
--创建表时同时创建索引
create table'table_name'(
'id' int(11) NOT NUll AUTO_increment,
'title' char(255) character set utf8 collate utf8_general_ci NOT NULL,
'content' character set utf8 collate utf8_general_ci NULL,
'time' int(10) NULL default NULL,
primary key('id'),
INDEX index_name (title(length))
)
--删除索引
DROP INDEX index_name ON table_name
唯一索引
--创建唯一索引 与普通类似 索引的列必唯一 但可有空值
create unique index ON table (colum(length))
主键索引 一支特殊的唯一索引不允许有空值
全文索引
FULLTEXT索引仅仅可用于MyISAM表
大容量的表使用全文索引索然速度更快,但产生全文索引是一个非常消耗时间和磁盘的做法
组合索引(最左前缀)
指定索引列时指定多个列
只有查询条件中使用了所关联的字段中的第一个索引字段,多列索引才会被使用
创建索引的情况
经常在where后出现的字段
分组所用的字段即groupby后边的字段
主键或者外键字段
设置有唯一完整性约束的字段
不适合创建索引的字段
查询中很少使用的\拥有很多重复值的字段
索引失效
- 使用or时索引失效,两者都想用需将or条件没咧都加索引
- like查询以%开头
- 多列索引不是使用第一部分索引则不会被使用
- 列类型是字符串,条件需将数据用引号括起来
- mysql中估计使用全表扫描比使用索引快,则不使用索引
- 对索引列进行运算导致索引失效
- 独立的列(聚合运算\类型转换)
- join操作中,mysql只有在主键和外键的数据类型相同才会使用索引
- 索引不会包含有NULL值的列,索引创建字段不要让默认值为NULL
- not in 和<>操作不会使用索引,可用not exist和> or <代替
索引的查看与优化
show table like "Handler_read%";--查看索引
索引优化
- 索引不回包含有null的列 创建表时不要让字短的默认值为null
- 使用短索引
- 索引列排序
- 不要在索引列上进行运算
存储过程
一组预编译的SQL语句集
优
- 只需创建一次过程后续重复使用
- 允许更快执行,大量重复操作存储过程比SQL语句更快
- 减少网络流量
- 更好地安全机制
缺
- 可移植性差,多个类型的数据库
- 学习成本高
- 复杂运算增加服务端成本,对集中性数据库可能会导致扩展性问题
存储过程的使用
存储过程
create procedure Rroc()
Begin
Select * from tb_person;
END;
存储函数
create function QUery_score(classID INT,studentID INT)
returns INT return
(select grage from tb_score where cID=classID and sID=studentID);
两者区别
- 存储过程更加复杂 函数功能针对性较强
- 存储过程可以返回参数,函数返回单一值或者表对象
- 存储过程做一个独立部分执行,函数可以作为查询的一部分来调用.可在查询语句中位于from之后
- 存储过程通过关键字call来调用,函数作为select的一部分嵌入到sql语句中
- 当存储过程和函数被执行的时候SQLmanager会到procedure cache中取相应的查询语句,如果PC没有,SQLManager就会对存储过程和存储函数编译
应用场景
- 复杂的数据处理用存储过程,比如报表
- 多条件多表联合查询并做分页处理,用存储过程也比较合适
优化
sql优化
- 对查询进行优化,尽量避免全表扫描,where 和orderby的列创建索引
- 避免where子句中字段进行null值判断
- 避免where子句使用!=或者<>
- 避免where子句使用or,一个条件的字段无索引,引擎放弃使用索引,建议使用union all代替
- in 和 not in会导致全表扫描 对连续数据可用between,也可以用exist代替in
- like"%a%" 导致索引失效,可使用全局索引
- 大数据量(几百条)的表join要先分页在join,否则逻辑读会很高,性能很差
- 少使用*
- 日期适用MySQL自带,少使用字符串存储,IP使用int不要使用字符串
- 尽可能使用varchar和nvarchar代替char/nchar,变长字段存储空间小.查询中对字段较小的搜索效率高
数据库优化
主从复制读写分离
- 使用spring可以实现读写分离
分库分表
- 分表就是吧一个表分为N多个小表
- 分库就是吧一张表分成N多个区,这些区可在不同磁盘
存储引擎
--查看MySQL的存储引擎
show engines
myisam和inndb的区别
| 区别 | myiasm | innodb |
|---|---|---|
| 存储结构 | 三个文件第一个以表的名字开始.扩展名指出文件类型.frm文件存储表定义.MYD数据文件.MYI索引文件 | 所有表都保存在同一个文件,表的大小只受限于操作系统文件的大小,一般为2g |
| 存储空间 | 可被压缩 | 需啊哟更多的内存和存储空间 |
| 可移植性\备份\恢复 | 数据是以文件的形式存储,跨平台数据转移很方便 | 免费的方案可以拷贝备份binlog或者mysqldump数据量达到几十个g就难受了 |
| 事务支持 | 不支持 | 提供事务支持事务 |
| 表锁差异 | 只支持表级锁 | 支持事务和行级锁 |
| Auto_increment | 可以和其他字段一起建立联合索引,自动增长列必须是索引,组合索引可以不是第一列 | 必须包含值又该字段的索引,自动增长列必须是索引,如果是组合索引必须是第一列 |
| 全文索引 | 支持FULLText类型的全文索引 | 不支持FullText,但可使用sphinx插件,效果更好 |
| 表主键 | 允许没有任何主键和索引的表存在,索引都是保存行的地址 | 如果没有设定主键或者非空唯一索引,会自动生成一个6字节的主键,数据是主索引的一部分,附加索引保存的是主索引的值 |
| 表的具体行数 | 保存有表的总行数,count(*)直接输出 | 没有保存总行数,count效率低,加了where,select两者相同 |
| CRUD | 适合大量select | 适合大量Insert\update |
| 外键 | 不支持 | 支持 |
锁
表锁
行锁(共享锁和排它锁)
共享锁:读锁
select ...lock in share mode
排它锁:写锁
select ...for update
乐观锁和悲观锁
悲观锁
select ...for update或者lock in share mode
乐观锁
版本(version)记录机制
时间戳(timetamp)
select语句完整执行顺序
from -> where ->group by ->having ->计算式所的表达式 ->group by ->select 输出
浙公网安备 33010602011771号