mysql进阶知识_MySQL学习-进阶知识

本篇主要记录我学习过的一些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自带的工具