MySQL 分析

 数据库的概念

数据：描述事物的符号

数据库：在计算机内部长期存储，有组织的，可共享的数据集合

数据库管理系统：是计算机的基础软件，是数据组织，存储，管理和维护的系统

数据库系统：有数据库，数据库管理系统，应用程序和数据库管理人员组成的 存储，管理，维护数据的系统

 

文件系统的缺点：共享性差和数据冗余度高，数据独立性差。

 

数据模型的三要素： 数据结构；数据操纵；数据的完整性约束

 

数据模型：

• 概念模型：按照用户的观点来对数据和信息建模，用于数据库设计
• 逻辑模型：按照计算机系统的观点对数据建模，用于数据库管理系统的实现
• 物理模型：

 

常用的数据库模型： 层次模型  网状模型  关系模型

 

数据库系统的结构：

常用的   三级结构   ， 模式 ；外模式(用户模型） ；内模式

 

数据库的两级映像 功能：

    外模式 / 模式  数据的逻辑独立性 ；  模式 / 内模式 数据的物理独立性

 

数据库的组成： 软件 ，硬件平台及数据库；人员

 

 关系数据库

域：某一个数据类型的值的集合

笛卡尔积：域

 

关系：候选码，主属性，主码，非主属性

 

关系类型： 基本关系(基本表)   查询表( 查询的结果集) 视图表

 

关系模型 R( U, D ,DOM ,F ) R 关系名，表名 ；U 组成关系的属性名集合 ；D 为U中所有属性的值的集合 ； DOM 从属性 到 值 的集合 ；F 属性之间的依赖关系

 

基本关系的操作： 选择 ， 投影 ，并，差 ，笛卡儿积 

 

实体完整性约束：

• 实体完整性：如果一个关系存在主码，那么这个主码不能为空，及不能不存在。
• 参考完整性
• 用户自定义完整性

 

 SQL的特点：

综合统一；高度过程化；面向集合的操作方式；以同一种语法结构提供多种使用方式；语言简洁。

 

SQL也支持关系数据库的三层结构，及模式，外模式，内模式

如果一张表没有指定模式，系统会根据搜索路径来确定对象所属的模式。SHOW search_path   SET search_path TO table_name,PUBLIC

搜索路径的当前默认值 是 $user,PUBLIC 。其含义是如果 搜索与 用户名相同的模式名，如果没有，则使用PUBLIC。

 

SQL连接查询

内连接，外连接，左外连接，右连接，IN连接，EXISTS连接，UNION ALL连接

select * from a_table a inner join b_table b on a.a_id = b.b_id;

 

SELECT  * FROM a_table a left join b_table b ON a.a_id = b.b_id；

 

SELECT  * FROM a_table a right  join b_table b on a.a_id = b.b_id;

 

(select id,name from A ) union all (select id,name from B ) order by id; //有排序效果 ，将两个表的查询结果合并，同时会取出相同的行

 

select * from A where id in (select id from B); 这个先遍历B表，在遍历A表，这种情况B表必须是小表

select * from A where exitsts (select * from B where A.id=B.id) 这个先是遍历A表，再遍历B表，A表必须是小表。

 

更新表     update table_name set column = "xxx" 

删除数据 delete from table_name where 

 

 数据库德范式，反范式

第一范式：

第二范式：

第三范式：

BCNF：

多值依赖：

第四范式：

 

 数据库设计：

需求分析：数据字典，数据结构，数据流，数据存储描述

概念设计：概念模型，数据字典，ER图

逻辑结构设计：逻辑模型，ER图 向 关系模型的 转变

物理结构设计：数据在物理层的存储

数据库的实施，运行，维护

 

 

 大数据 NO-ONLY-SQL

非关系数据库常用的数据模型

• Key-Value模型
• BigTable模型
• 文档模型
• 图模型

 MapReduce技术

 

 

 

数据库隔离性

 

数据库引擎

 

数据库分区

 

数据库字符集选择

 

数据库基准测试

集成测试  单元测试

 

explain sql语句

id  select_type table type possible_key key key_len  ref row extra

　id列数字越大越表先执行；

   type: 常用的system>const>eq_ref>ref>range>index>ALL: 一般来说，得保证查询至少达到range级别，最好能达到ref

   key: 查询真正使用到的索引信息

   extra: 　using filesort：文件排序，出现这个东西不好，排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中 　　using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。using temporary：出现这个东西更不好，表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。 　　using where：使用了where，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。

   

开启慢查询日志，捕获慢SQL

超过这个long_query_time值的SQL，就会记录在日志中。调优的时候才开启，因为会影响数据库性能。

 slow_query_log_file：慢查询日志的路径

 

show VARIABLES LIKE '%slow_query_log%'; 查看是否开启慢查询日志

set global slow query log=1; 只对当前数据库生效，关闭服务后失效，想要永久生效，需要修改配置文件。

 SHOW GLOBAL STATUS LIKE '%Slow_queries%';查看多少SQL语句超过了阙值

MySQL提供的日志分析工具mysqldumpslow

进入MySQL的安装目录中的bin目录下，

执行 ./mysqldumpslow --help 查看帮助命令

常用参考： 得到返回记录集最多的10个SQL mysqldumpslow -s r -t 10 slow.log 得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 slow.log 得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "left join" slow.log 使用这些语句时结合| more使用

 

show profile查询SQL语句在服务器中的执行细节和生命周期

Show Profile是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优测量

默认关闭，并保存最近15次的运行结果

分析步骤 1、查看状态：SHOW VARIABLES LIKE 'profiling'; 2、开启：set profiling=on;3,执行SQL语句4、查看结果：show profiles; 展示最近15条SQL的ID,运行时间，SQL语句。 5、诊断SQL：show profile cpu,block io for query Query_ID;

show profile all 查看全部的信息

 

全局查询日志，只能在测试环境里使用。

set global general_log=1;

set global log_output='TABLE';

记录到general_log表中。

 

数据的性能刨析

应用级别的性能刨析

查询性能刨析：单条SQL语句的性能，使用explain ; 测试全部的SQL语句，使用慢查询日志，但需要设置SQL语句的慢查询时间和查询日志的存储位置 ； 使用set processlist =1 ,动态处理SQL语句

结构优化：数据类型优化； 索引优化 ； 范式和 反范式 优化

 

数据类型优化：尽可能使用合适的数据类型 ，尽量避免使用NULL，时间日期 DATETIME ，TIMESAMP，TIMESAMP的存储更加少，而且能根据时区的变化，自动更新。

 

范式的使用：可以减少冗余数据 ； 反范式的优点：减少关联查询。

 

修改表结构的 方法：

修改 . frm 文件

新建一张表，用select insert 方法把另一种表的全部数据插入另一张表

使用第三方工具

 

配置文件

log-bin 主从赋值

log-error 错误日志

log记录MYSQL查询SQL语句

数据文件 数据默认存放位置/var/lib/mysql； frm文件 存放表结构 ；myd文件 存放表数据； myi文件存放表索引；

linux的配置文件是conf; windows的配置文件是ini

 

索引

索引是引擎层实现的

B-Tree

key(column_name , name , name ) 三个列实现B树索引

 

hash索引

key using hash(colunmn_name)   三个哈希函           SHA1() MD5() CRC32()

 

R-Tree 空间数据索引

 

独立的列索引：如果列的值是一个很长的字符串，可以用字符串的前面某一个部分作为索引的条件。

多列索引，建立唯一需要考了的是 列的顺序

聚众索引和二级索引：聚众索引的叶子节点存储一整行的数据，占用空间大，而二级索引是辅助索引，可以叶子节点存储的是主键，可以通过主键获得数据。

 

覆盖索引：索引覆盖查询的条件，只需要通过索引就能得到具体的值，而不需要去数据表那里进行全文扫描。