本篇主要记录我学习过的一些MySQL进阶知识,有联合查询、视图、索引、数据库的引擎、用Python操作MySQL数据库等等。
1、 联合查询:同时查询第一张表的数据和第二张表的数据,并且以第一张表的字段名为表头,应该尽量保证两张表所查询的字段数相同且为一类数据
Select t1.字段 from 表1 as t1 union(all) select t2.字段 from表2 as t2;(取别名是为了写语句方便,可以全都写表名,union会去除重复的字段,union all不会)
2、 数据库视图:视图是select语句查询的结果集,可以在不影响程序运行的情况下改变数据库
定义视图:create view 视图名 as select语句;
查看视图:show tables;
查询视图中数据:select * from 视图名;
删除视图:drop view 视图名;
视图的作用:1、重用性
2、保证数据安全,可以只给视图权限不给表权限
3、对数据库重构不影响程序运行
4、数据更加清晰
3、 索引:索引是一个特殊文件,存储着数据表中数据的逻辑指针,相当于目录
目的:提高查询速度,但同时会降低更新速度(数据少于300w的库不需要考虑索引)
查看索引:show index from 表名;
创建索引:create index 索引名字 on 表名(字段名(长度));
删除索引:drop index 索引名字 on 表名;
索引的分类:单值索引、复合索引、唯一索引
4、 事务:ACID:
原子性(atomicity)
一致性(consistency)
隔离性(isolation)
持久性(durability)
命令:开启:begin或者start transaction
提交:commit
回滚:rollback
5、 存储引擎:mysql体系结构:连接层、服务层(核心)、存储引擎层、系统文件
查看数据库引擎:show engines;
查看数据库默认存储引擎:show variables like ‘%storage_engine%’;
Myisam:分为系统表和临时表,MYD(数据文件) MYI(索引文件)
锁:按类型分为读锁和写锁,按级别分为表级锁和行级锁,
特点:是表级锁,并发性低,表损坏可以修复,但是会丢失数据, 数据可以压缩,但是压缩后只能查不能插入
适用于非事务性应用和只读类型应用
Innodb:表存储空间可以分为独立表空间和系统表空间
系统表空间会产生IO瓶颈,独立表空间是多个表同时刷新,性能相对较高
Csv:数据以文本存储,数据表以csv格式存储,不支持主键、自增、所有列都不允许为空
Memory:数据存储在内存,不支持blob和text大字段,varchar=char,重启后数据丢失,表结构还在
如何选择引擎,是否需要支持事务、是否需要备份、数据恢复问题
例如:银行系统、订单系统要用innodb,员工表等不经常更新的可以用myisam
6、 Python 操作数据库(pymysql):安装:pip3 install pymysql(python2中为mysqldb)
操作过程:导入模块:import pymysql
创建连接对象:对象名=pymysql.connect(host = ‘数据库ip’, port=’数据库端 口’,database=’数据库名’,user=’用户名’,password=’密码’,charset=’utf8’)
创建游标对象:游标对象名=connect.cursor()
执行语句:游标对象名.execute(‘sql语句’) 返回的是受影响的行数
获取结果:变量名=游标对象名.fetchall()返回所有查询结果,fetchone()返回第一条查询结果,fetchmany(返回条数)返回指定条数
提交:如果对数据库进行增删改这三项操作需要提交代码才会生效,查询不用
游标对象名.Commit()
关闭:数据库操作完之后要先关闭游标对象再关闭连接对象
游标对象名.close()
Connect.close()
7、 数据库的锁:表锁:偏向myisam,特点是开销小,加锁快,并发能力,锁力度大,不会产生死锁,被锁后会堵塞写
行锁:偏向innodb,特点是开销大,加锁慢,锁力度小,发生冲突概率小,会产生死锁,死锁概念:多个并发进程因争夺系统资源而产生相互等待的现象,会同时堵塞写和读
间隙锁:查看是否开启show variables like 'innodb_locks_unsafe_for_binlog';
在查询语句后加for update;就可以开启间隙锁,但是会把行锁变为表锁
分析锁定行:通过show status like”innodb_row_lock%”;后面可以加current_waits:当前正在等待锁的数量;time:从系统启动到现在锁定总时间;time_avg:每次等待的平均时间;max:从系统启动到现在等待最长的一次时间;waits:系统启动到现在总共等待的次数
8、 MySQL基准测试:建立性能的基准,简单直接,与业务逻辑无关
目的:1、建立MySQL服务器的基准线,确定当前服务器运行情况
2、模拟比当前系统更高的负载,已找出系统的扩展瓶颈,TPS:T ransactions Per Second,每秒处理事务的数量,QPS:每秒钟处理的查询数量
3、测试不同的硬件、软件和操作系统配置下的性能
4、证明新的硬件是否配置正确
工具:mysqlslap,MySQL自带的工具