mysql阅读笔记

----------mysql-------------------------
1,设计字段长度最优,字段设置为非空,部分字段可以设计成enum
2, 首先，最好是在相同类型的字段间进行比较的操作
3, 其次，在建有索引的字段上尽量不要使用函数进行操作
4,SELECT * FROM books WHERE name like "MySQL%"
5,SELECT * FROM books WHERE name ＞= "MySQL" and name ＜"MySQM"
6,like关键字影响性能.
7.explain 查看sql执行计划
-----
执行计划Id字段解释
1. id相同时，执行顺序由上至下
2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3.id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

执行计划select_type字段解释
示查询中每个select子句的类型
(1) SIMPLE(简单SELECT,不使用UNION或子查询等)
(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果)
(6) SUBQUERY(子查询中的第一个SELECT)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
----
table
显示这一行的数据是关于哪张表的，
有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

四、type
表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，
就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例
,当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，
例如从一个索引列里选取最小值可以通过单独索引查找完成。

五、possible_keys
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提
高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

六、Key
key列显示MySQL实际决定使用的键（索引）
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，
在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

七、key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，
并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
不损失精确性的情况下，长度越短越好

八.ref
表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

九、rows
表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

十、Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：
Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,
这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。
如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where：这个值强调了where语句会导致没有符合条件的行。
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

总结：
? EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
? EXPLAIN不考虑各种Cache
? EXPLAIN不能显示MySQL在执行查询时所作的优化工作
? 部分统计信息是估算的，并非精确值
? EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。
-----------------------------------------------------------------------------------------------------------
ISAM:利用索引顺序存储数据的方法.
MyIsAM;
Myisam是Mysql的默认存储引擎，当create创建新表时，未指定新表的存储引擎时，默认使用Myisam。
每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、
.MYD(MYData，存储数据)、.MYI(MYIndex，存储索引)。数据文件和索引文件可以放置在不同的目录，
平均分布io，获得更快的速度。
mSQL（mini SQL）是一个单用户数据库管理系统，个人使用免费，商业使用收费。
由于它的短小精悍，使其开发的应用系统特别受到互联网用户青睐。
innodb:四个事物隔离级别
MEMORY:存储引擎,速度快
使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。
虽然在内存中存储表数据确实会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。
获得速度的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着
不能使用BLOB和TEXT这样的长度可变的数据类型，
VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。
一般在以下几种情况下使用Memory存储引擎：
1.目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过
参数max_heap_table_size控制Memory表的大小，
设置此参数，就可以限制Memory表的最大大小。
2.如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。
3.存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。
Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是，可以使用部分查询和通配查询，
也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快，但是对“范围比较”
的速度就慢多了，因此散列索引值适合使用在=和<>的操作符中，不适合在<或>操作符中，也同样不适合用
在order by子句中。

存储过程:procedure ;
function:函数
trigger:触发器
TPCC值:
TPCC值被广泛用于衡量C/S环境下,由服务器和客户端构筑的整体系统的性能,它由事物处理性能委员会
（TPC，Transaction Processing Corp）制定,TPC为非赢利性国际组织。
TPCC值可以反映出系统的性能价格比。TPCC测试系统每分钟处理的任务数,单位为tpm,(transactions per minute)。
系统的总体价格(单位为美元)除以TPCC值,就可以衡量出系统的性价比,系统的性价比值越大,系统的性价比越好。
需要注意的是,TPC-C值描述的是C/S整体系统的性能,它与系统的服务器和客户机的性能都有关系,也就是说
,同样的服务器配置不同的客户端将会影响TPCC值,任何厂商和测试者都可以根据TPC提供的测试规范构造出自己
最优的系统,当然测试的结果要经过TPC审核。

--show engines +(可以加表名字):数据库引擎展示;

使用主从同步的好处：
1.通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，
可以动态地调整从服务器的数量，从而调整整个数据库的性能。
2.提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而
不破坏主服务器相应数据
3.在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能
mysql书写顺序:
select[distinct] from join（如left join）on where group by having union order by limit

mysql执行顺序： from on join where group by having select distinct union order by

配置主从同步

有很多种配置主从同步的方法，可以总结为如下的步骤：
1．在主服务器上，必须开启二进制日志机制和配置一个独立的ID
2．在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号
3．在开始复制进程前，在主服务器上记录二进制文件的位置信息
4．如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件）
5．配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置
----
mysql物理组成
日志文件
错误日志 error log
二进制日志 binary log
更新日志 update log
查询日志 query log
慢查询日志 slow query log

数据文件
MyISAM ====.MYD
Innodb ====.ibd
Archive ====.arc
CSV ====.csv

mysql layer包含模块
1,初始化模块
2,核心api
3,网络交互模块
4,client&server交互协议模块
5,用户模块
6,访问控制模块
7,连接管路,连接线程和线程管理
8,query解析和转发模块
9,query Cache模块
10,query 优化器模块
11,表变更管理模块
12,表维护模块
13,系统状态管理模块
14,表管理器
15,日志记录模块
16,复制模块
17,存储引擎接口模块
---------------
myISAM 支持三种索引
B-Tree索引
R-Tree索引
Full-Text 索引

check table 表名字 ; 检查表
repair table 表名字;修复表

innodb
1,支持事物安装
2,数据多版本读取
3.锁定机制的改进 实现行锁-->通过索引来实现.
4.实现外键
物理结构分为两大部分:
1.数据文件
2.日志文件
NDB Cluster 存储引擎 ;mysql集群
Merge存储引擎:
Memory 存储引擎;

------mysql安全管理--------
1.外围网络---局域网
2.主机 层防线
3.数据库防线
4.代码
权限系统简介
权限级别
1,global level
2,database level

----
逻辑备份恢复方法
1,insert语句文件的恢复
2,纯数据文本备份的恢复

不适合在数据库中存放的:
1.二进制多媒体数据
2.流水队列数据
3.超大文本数据
什么样的数据适合Cache技术
1.系统各种配置及规则数据
2.活跃用户的基本信息数据
3.活跃用户的个性化定制信息数据
4.准实时的统计信息数据
5.其他一些访问频繁但是变更较少的数据
query语句对性能的影响
1 mysql server 接收到线程Client端发送过来的sql请求之后,
会经过一系类的分解Parse,进行相应的分析,然后通过查询优化器(optimizer)
执行计划.--explain
profile---关于IO和cpu 资源问题
开启 profiling 功能:set profiling = 1; show profiles
2.schema设计对系统性能的影响,硬件环境对系统性能的影响
3.硬件环境对系统性能的影响
IOPS:每秒IO访问次数, 每秒的IO总量:IO的吞吐量.
Cpu处理能力也不能忽略
数据库主机的网络设备的性能也可能成为系统的瓶颈
OLTP与OLAP的介绍
数据处理大致可以分成两大类：联机事务处理OLTP（on-line transaction processing）、
联机分析处理OLAP（On-Line Analytical Processing）。OLTP是传统的关系型数据库的主要应用，
主要是基本的、日常的事务处理，例如银行交易。OLAP是数据仓库系统的主要应用，支持复杂的分析操作，
侧重决策支持，并且提供直观易懂的查询结果。
OLTP 系统强调数据库内存效率，强调内存各种指标的命令率，强调绑定变量，强调并发操作；
OLAP 系统则强调数据分析，强调SQL执行市场，强调磁盘I/O，强调分区等。
=======7章=======mysql数据库锁定机制========
MyISAM Innodb
锁机制:行级锁定,页级锁定,表级锁定.
随着锁定资源颗粒度的减小,锁定相同数量的数据所需要消耗的内存数量是越来越多的,
实现算法也会越来越复杂.不过,随着锁定资源粒度的减小,应用程序的访问请求遇到
锁等待的可能性也会随之降低,系统整体并发也随之提升.
表级锁定:MyISAM Mermory CSV ----非事物性存储引擎.
行级锁定:Innodb NDB Cluster
页级锁定:BerkeleyDB
表锁: 一种是读锁定,一种是写锁定---通过4个队列维护2种锁,正在锁定中的读和写锁信息.
等待中的读和写锁信息.
行级锁:共享锁, 排它锁. 意向共享锁,意向排他锁
innodb的锁定实现方式被称为NEXT-KEY locking (间隙锁)
通过索引实现锁定的方式存在几个较大的性能隐患
1,当query无法利用索引的时候,innodb会放弃使用行级锁定而改用表级别的锁定
造成性能的将降低
2,当query使用的索引并包含所有的过滤条件的时候,数据检索使用的索引键只想的数据
可能有部分并不属于该query的结果集的行列,但是也会被锁定,因为间隙锁锁定的是一个
范围的,而不是具体的索引键,
3, 当query在使用索引定位数据的时候,如果使用的索引键一样但是访问的数据行不同的
时候,一样会被锁定,
MyISAM表锁优化建议:
如何让锁定时间变短,提高并发.
1,缩短锁定时间---
---尽量尽量减少大的复杂query,将复杂query分拆几个小的query分布进行
---尽可能的建立足够高效的索引,让数据检索更迅速;
---尽量让MyISAM存储引擎的表只存放必要的信息,控制字段类型;
---利用合适的机会优化MyISAM表数据文件;
2,分离能并行的操作
3,合理利用读写优先级

innodb 行锁优化建议
尽可能让所有的数据检索通过索引完成,从而避免innodb因为无法通过索引加锁而升级为表级锁定
================================
=================8)mysql 数据库query的优化============
----------mysql query Optimizer
11.常用存储引擎优化
myIsam存储引擎 .MYI
state:主要是整个索引文件的基本信息
base:各个索引的相关信息,主要是索引的限制信息
keydef:每个索引的定义信息和
recinfo:每个索引记录的相关信息
innodb存储结构
tablespace--包括一个或多个segment--包括一个或多个extent---为64个page--一个page默认16KB
---
QPS--每秒query量
TPS--每秒事物量
Key Buffer 命中率
==========================结束==============================
==========================mysql修炼之道=======================
用于赋予查询、插入、修改、删除权限，并进行密码设置
：>grant select,insert,update,delete on db_name.* to user_name@ ‘10.%‘ identified by ‘password‘;
令用于回收上面所赋予的权限
：>revoke select,insert,update,delete on db_name.* from user_name@ ‘10.%‘;

长连接、短连接、连接池

原文：https://www.cnblogs.com/liufei-90046109/p/11510336.html