Mysql总结

基础概念

数据库是存储数据对象的容器。
DBMS数据库管理系统

数据库：存放数据的仓库，按照一定的数据结构来组织存储。可以通过多种方法来管理数据库中的数据。

结构化查询语言
数据查询语言DQL：数据检索
数据操作语言DML：增删改
数据定义语言DDL：表的操作，定义表
事务处理语言TPL：事务管理
数据控制语言DCL：权限管理
指针控制语言CCL：一个或者多个表的操作

表，固定的列数，任意的行数。
关系型每个实体类对应表中的一行，相当于一条记录。

ORM对象关系映射

关系型数据库	面向对象
表	类
列（字段）	属性
一行数据	一个对象

数据库对象：存储管理和使用不同数据库的不同结构形式，表，视图，存储过程，函数，触发器，事件。

视图：视图在行为上和数据表没有区别，都可以执行查询命令，也可以执行增删改操作(看视图定义)，为什么创建视图？

1：安全，有时候不想让某个用户对某个表得某些数据进行访问，但是另一部分是可以访问的，比如员工个人信息可以访问，但是薪水却不能访问。就可以为这个数据库表创建一个视图，通过权限控制，只能访问这个视图而不能访问视图背后的数据表。
2：方便，有时候人们经常执行同样的sql去一个或者多个数据表中查询数据，让这么多用户去输入同一个非常复杂的sql就可以定义一个视图给大家使用。

镜像复制：把某个数据库的内容动态的复制到其他计算机，
原因1：为了避免系统故障而中断服务，即使一台计算机出了问题，也可以安排另一台计算机投入使用。
2是为了加快数据库查询速度。

事务：把多个数据库当成一个整体来对待，数据库系统确保操作要么全部正确执行，要么全部不执行，即使在事务过程中，出现了停电计算机崩溃等情况也要如此，这样就不会发生比如银行转账的时候因为机器坏了等原因造成交易出现错误。

事务机制也可以让程序安全及时的终止一组命令，并将数据库恢复到命令开始前的状态。

数据库分为两种：

系统数据库：不可以修改
Information_schema：数据库对象信息，列信息，权限，字符分区信息
Performance_schema：数据库服务器性能参数
MySQL：储存数据库用户权限信息
Test：任何用户都可以使用的测试库。

用户数据库：一般一个项目一个用户数据库

---

数据类型：

一．整数Int
既包括正数也包括复数，但是如果int列定义了unsigned无符号属性，他的取值范围仅限于正数。但是在做减法的时候，返回值仍然是无符号的，导致结果不正确。
取值范围是-128~127.，如果是无符号的，范围就变成了0-255.如果超过范围，就会自动替换成最大可取值或者最小可取值。

Int（2）和int 在取值范围上没有任何区别。最大的用处是和填充零进行配合。当字段选择了零填充，并且显示宽度设置为int(2)之后，所有不足显示宽度的数字前面都会被零填充。比如1会显示为01。这才是int(2)的作用。

但是需要借助临时表才能完成的复杂查询的时候，数据会被截短，导致数据不正确。

1．auto_increment整数，列的属性自增
1.必须和非空 主键或者唯一索引同时使用
2.每个表最多只有一个自增列
3.只有在insert时，并且没有指定值或者null时才起作用，如果用户给了值，并且还没有在列中出现过，mysql则用用户指定的值。
4.如果想知道刚才生成的值是多少，在执行完insert操作之后，还是在本事务中，立即执行select last_insert_id命令。
5.如果计数器到达最大值（不同类型最大值不同），将不再继续递增，insert将不再进行，所以最好使用bigint类型。
2.bit和bool，二进制数据。
3.浮点数 float和double。

二．定点数decimal
在使用float和double的时候会进行舍入，但是在涉及金钱的时候应该选择decimal，decimal数据类型是以字符串的形式来保存数据，并且不允许使用指数形式。所以会占用更多的空间。

三，日期类型 DATE，TIME，datetime，timestamp

1. timestamp 是一个特殊的类型，这个字段类型会在数据记录的其他字段被修改的时候自动刷新。
2. 但是也可以设置它的属性，比如在创建新纪录和修改当前记录时刷新，创建时设置为当前时间，修改时刷新，创建时设置为0，修改时刷新，创建时设置为给定值，修改时刷新。
3. 所以这个字段类型不适合用户使用，而是适合内部管理使用。

四，字符串 char，varchar，xxxtext

Char类型的字符串长度是有严格限制的，不管字符串实际长度是多少，char（20）字段在每个记录里，都将占用20个字节，字符串的前导空格会被去掉。比较短的在字符串尾部加空格补足，在读取数据的时候自动去除空格。缺点就是不能保存尾部确实有空格的数据。

Varchar和xxxtext其长度都是可变的，他们占用存储空间由他们的实际长度决定。看起来可以存65535个字符，但是varchar必须声明数据表时设置，超长的会被截短，text不允许设置最大长度。
Binary属性，如果给char和varchar加上这个属性，MySQL将把他们当作二进制文件来对待，排序时可以把字母的大小写分开，与字符串相比，二进制字符串内部管理更简单，花费时间更短。

---

F5 mysql的命令界面

常用命令
1：查看数据库中有哪些数据库
Show databases；
2：使用哪个数据库，切换库
Use 表名；
3：查看该库有哪些表
Show tables;

---

库一定要有database

4：创建数据库
Create database 库名；
5：删除数据库
Drop database 库名；

6：查看表结构
Desc 表名；
7：查看表的详细定义
Show create table 表名；
8：删除表
Drop table 表名；

---

Linux 下数据的备份和还原
备份 MySQLdump -uroot -p 库名 > /文件夹路径/表名.sql
还原MySQL -uroot -p 库名 < /文件夹路径/表名.sql

---

创建用户
Create user ‘名’@‘IP’ identified by ‘密码’
登录
Mysql -h localhost -uroot -p密码 -p3306
删除用户
Drop user 名@‘IP’

---

修改事务的隔离级别
Set sesstion tx_isolation=‘级别’；

表的约束
• 非空约束(not null)
• 默认值（default）
• 唯一性约束(unique)
• 自增约束（auto increment）
• 主键约束(primary key) PK
• 外键约束(foreign key) FK
• 检查约束(目前MySQL不支持、Oracle支持)
主键设计
1：单字段主键
2：复合主键
主键分为两种
1：自然主键 有特殊意义，身份证
2：代理主键 无业务含义，只作为主键使用

1建表语句
create table flower(
id int(10) not null auto_increment,
name varchar(100) not null,
price double not null,
prodution varchar(100) not null,
primary key(id)
)

单表操作

SQL基础
Mysql没有全连接，所以用union来解决

SELECT empno,ename,dname FROM emp LEFT JOIN dept USING (deptno)
UNION
SELECT empno,ename,dname FROM emp RIGHT JOIN dept USING (deptno)

1 Select count() 或者count(id) 等任何一列，mysql优化之后都是返回数据表数据的记录数，而不是读取真正的内容。
count(字段)，根绝字段判断为不为不空，根据字段定义，考虑要不要累加返回值，既然你引擎都返回值了，那我server层 “ +1 ”
count(id),根据id主键取值，累加返回值，也是server层 “ +1 ”
count(1)，同样会遍历，但不取值，引擎告诉不为空那我就 “+1”
count(),也不取值，而且人家还是经过优化的

根据上面的推倒，搜主键肯定比搜正常字段快， 不取值的一定比取值的快（我就是查数统计一下，你给我这一行所有的值也没啥用啊）， 优化过的比没优化过的快

以下排行是按照效率，而不是时间
count（*） >或（≈） count（1） > count（id） > count（字段）

反正我觉得count()不错，我再本地测试的时候分析性能的时候（数据量不大，且条件不是主键，基本二者差不多有的时候count() 可能会快一点。）
原文链接：https://blog.csdn.net/wo1901446409/article/details/100098685

2 限制查询结果中记录个数 limit
Select id from table limit 2 查2条记录
如果想看下两条记录，就需要设置偏移值，从0开始，比如第5.6条数据
Select id from table limit 3，2 查2条记录
确定总记录数SQL_CALC_FOUND_ROWS,但是使用这个会使MySQL无法对limit查询进行某些特定的优化，所以如果必须使用found_rows（）函数的时候才用SQL_CALC_FOUND_ROWS
Select SQL_CALC_FOUND_ROWS id from table limit 2

3 排序 order by，即使没有用order by，mysql的结果也是有顺序的，就是查询储存的顺序。

4 筛选数据

Where having like
在使用like的时候，在操作比较大的数据表的时候往往非常慢，因为mysql需要读取和分析表中的全部记录，，二是这类查询无法用索引来优化，这种情况可以使用全文索引来进行全文检索。
MySQL不支持colname=null这样的语法，可以用isnull(colname)函数。

5合并
Unio自动去除重复数据。union ALL 会把数据全部保留。

自动修改数据库表设计
在创建或者修改表的时候，MySQL会在特定情况下对这个数据库表的设计方案进行一些修改，理由是可以让数据库表的效率更高，或者是设计思路MySQL无法实现。所以建表之后一定要用show create table去看一下设计方案。
什么情况下会对表进行修改呢？
1：如果n<4 varchar（n）会被修改成char（n）。
2：如果n>3并且在同一数据表还有其他的varchar，text或者blob数据列，char（n）将被修改为varchar（n）,如果数据表只有固定长度的数据列，char（n）将不在变化。
3：mysql允许timestamp带有null或者not null属性，但他们没有实际的效果。这是因为null不能存入timestamp中，如果寸null，实际存的是0000-00-00 00：00：00。
4：即使没有设定，MySQL给会给主键加上非空设定。
5：如果没有给数据列加上默认值，MySQL也会给他加上 null 0 或者空字符串。

以上不是关于数据库表内容二是数据表本身属性得信息称为元数据

---

主键和外键

数据库表之间的关系是靠主键和外键建立起来的。

主键
主键的作用是帮助MySQL以最快的速度把一条特定的数据记录在数据库表中的位置确定下来，而这种寻址操作在一切需要从多个数据库表中提取数据的场合都会发生，非常频繁。

主键唯一
主键紧凑。
1加快寻址定位的速度，主键必须有索引（主索引），主键字段越紧凑，主索引的管理工作效率越高。所以整数类型就比非固定长度的字符型更适合作为主键。
2，主键几乎被用作另一个表的外键，外键越紧凑，效率越高。
人们为数据库表建立各种关系就是为了避免太多的冗余数据浪费存储空间，如果主键和外键占用空间过大，那就失去了他们应有的意义。

外键
外键的目的就是引用另一个表的某条记录。

---

什么是存储引擎

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
存储引擎：
MySQL用不同的存储技术存储在文件和内存中，这些技术，每种技术都使用不同的存储机制，索引技巧，锁定水平，最终提供不同的功能和能力。通过不同的技术获得不同的速度和功能，改善应用的整体功能。
innoDB=提供了具有提交回滚和崩溃恢复的能力的事务安全，效率比MyISAM稍差。会占用更多的磁盘空间以保留数据和锁引。

存储引擎对比

在对数据库进行myisam数据库进行处理时，MySQL不能进行热备份（无须锁定数据库表就可以对数据表进行处理的同时对其进行备份）5.1之后支持热备份。

Myisam静态，如果数据库表中的数据列都预先定义好了固定的长度，myisam将自动选择这种数据库表类型，存取效率非常高，即使对数据库表修改非常频繁，也是如此，这种类型的安全性也很高，当出现文件受损或其他问题，数据记录和提取恢复也比其他类型容易。

Myisam动态，如果表定义有且只有一个varchar，text或者blob字段，mysql将自动选择这种类型，这种类型突出优点数据表的空间需求量小很多，存取字符串和二进制对象所需要的字节数仅仅是他们的实际长度（再加上几个字节的开销），这样一来，如果记录被修改了，他们的数据库文件里存储位置就可能发生变化并且在原先的位置上留出来一个空洞，于是在数据库文件里，同一个记录的各个字段就不一定存储在一个连续的字节块里，而是会散布在各处。当被编辑的数据库表变得越来越碎片化数据存取时间就会变得越来越长，因此这种类型的数据库表要求人们经常用优化工具来进行碎片整理。或者用SQL命令OPTIMIZE TABLE。

Myisam压缩：静态和静态都可以用myiasmchk工具压缩。这种压缩效果往往可以使数据表的空间尽量减少到原来的一半以下（与表内容有关），虽然以后在读取数据记录时需要对他们进行解压缩，但是在某些场合，数据库的访问速度甚至会变得更快，在低速硬盘+告诉CPU的系统上体现的更为明显。

innoDB使用的是他内建的行级锁定机制，而不是MySQL提供的表所机制。被锁定的只是正在接受事务处理的记录，当有许多用户同时对一个大数据库表进行修改，行锁会提高效率。innoDB会自动识别死锁（两个进程各自占用对方的资源，同时又在等待对方先释放资源，结果谁也不能执行）并自动终止两个进程中的一个。

可以把数据库中不同的表按照需求设置成不同的类型，他们可以同时存在一个库中。
不能说innoDB支持事务就会慢，因为myisam有时候会表锁。

innoDB的表空间管理和myisam不同。
Myisam会把每个数据分别保存在自己的文件里，这些文件会根据实际情况增大或缩小。innoDB却是把所有的数据和索引都保存在一个表空间里，表空间由一个或者多个文件构成，他们形成了一个虚拟文件系统，这些文件在创建后只能增大不能缩小。如果想复制某个innoDB数据表，把服务器停下来是不可能的，所以要使用mysqldump命令。
innoDB的单条记录最多可以占用8000个字节，不包括text和blob数据列，他们只有前边的512个字节是随其他数据列一起储存在数据库里，超过这个长度的其他数据列将被存在表空间的其他页面。
因为支持事务，所以count的时候，速度要比myisam慢很多。

查看数据库的支持的引擎。

---

什么是索引

索引是一种特殊文件，innoDB数据表索引是表空间的一部分。他包含对数据表里所有引用的指针。

Mysql限制每张表最多16个索引。

在innoDB中，索引不仅在搜索记录的时候发挥作用，还是数据的行级锁定的基础，事务行级锁定就是在事务操作的执行过程中锁定正在处理的某条记录，不让其他用户进行访问。出于效率考虑，innoDB数据表的行级锁定发生在他们的索引上而不是数据表本身，所以数据行锁只有有关数据表有一个合适的索引可供锁定的时候才会发挥效力。

1普通索引：由关键字key或者index定义的索引，唯一任务就是加快数据的访问速度。因此应该只为那些经常出现在查询条件或者排序条件中的数据创建索引。尽量选择一个数据最整齐最紧凑的数据列（整数）来创建索引。
2 唯一索引：普通索引可能包含重复的值，比如有人同名，或者同一数据在表中出现多次，如果能确定某个列中的值都是唯一的，可以为这个数据列建立唯一索引，1是可以简化MySQL对这个索引的管理工作，这个索引也因此变得很有效率，二是MySQL有新的纪录插入时，会自动检查是否已经出现过某值，如果是，MySQL拒绝插入这条记录，也就是说，唯一索引可以保证数据的唯一性。大多数情况，创建唯一索引也是为了避免数据重复出现。
3 主索引，主键索引就是主索引。
4外键索引，定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。
5 复合索引，索引涵盖多个数据列。好处是可以有选择的使用这个索引，比如你用到了index（A,B,C）你可以只用A，或者AB，但是不能用B,C。
索引长度限制，在只用char varchar类型的数据定义索引时，可以把索引长度限制为一个给定的字符个数（必须小于这个字段所限制的最大个数）。好处是可以生成一个尺寸较小的，检索速度却比较快的索引文件。在绝大多数情况，索引长度设置为10-15个字符，就已经可以把搜索范围缩小到很小很小的几条数据记录了。
在blob和text类型的数据列创建索引时，必须对索引的长度进行限制，MySQL所允许的最大索引长度是255个字符。
6 全文索引：
全文检索是对大数据文本进行索引，在建立的索引中对要查找的单词进行进行搜索，定位哪些文本数据包括要搜索的单词。因此，全文检索的全部工作就是建立索引和在索引中搜索定位，所有的工作都是围绕这两个来进行的。
建立全文索引中有两项非常重要，一个是如何对文本进行分词，一是建立索引的数据结构。分词的方法基本上是二元分词法、最大匹配法和统计方法。索引的数据结构基本上采用倒排索引的结构。

只有当数据库里已经有足够多的测试数据的时候，它的性能测试结果才有实际参考价值，如果只有几百条数据，他们往往都会在执行完第一条查询命令后就被全部加载到内存中，这使后续查询执行得非常快，不管有没有使用索引。只有数据库记录超过1000条，数据总量也超过MySQL服务器上的内存总量时，数据库性能测试才有意义。
在不确定应该在哪些数据列上创建索引得时候，可以用explain select命令来观察，就是在普通select查询前加上执行计划explain，有了这个关键字，mysql将不是执行那条sql，而是对他进行分析，会把执行的过程和执行的索引等信息列出来。

基数小，一本书几千页索引只有男女两种，查询也不是很快。




创建索引，

因为用了函数肯定是一个新的值，跟数据库的列结果肯定不一致，所以就不走索引。

Exist 是一个函数，返回一个布尔值，只有返回true 才会继续查询外边。

MySQL数据库使用索引的条件

SQL解决方案

Like，模板匹配可以使用regexp来代替like。

锁定

为了避免同一时间不同用户操作同一资源出现问题，可以用事务处理机制来解决，但是只能在innoDB上使用，如果正在使用的是myisam，只能依靠锁定机制，在一个用户操作表数据时，其他用户不能操作甚至不能读取。根据锁的类型。

Lock table table1 locktype，table2 locktype。。
Locktype表示锁定类型。
Read读锁，可读 不允许修改。
Read Lock 可读，不允许修改，但是允许insert，因为不影响现有记录。
Write 只允许当前用户读写。
Write Lock只能在表上没有任何Read Lock才生效。
Low priority write，类似于Write的锁定机制，但是在他等待其他Read Lock和Write Lock全部结束的期间，允许其他用户先施加一个Read Lock，如此安排是为了不影响其他用户的查询。但可能让Write Lock开始生效前的等待期变得更长。
解除锁定是 unlock table[s]，他不需要任何参数，这条命令将解除当前客户设置的全部Lock。为了降低对其他客户的影响，应该尽早解除锁定。

注意：MySQL总是以这样的方式来执行单条命令，不让他受到其他命令的影响，因此在执行单条命令的时候，不用加锁。只有连续执行多条命令时，才需要用锁定机制。比如先查询再更新/删除。
不要在innoBD表中使用Lock和UnLock命令，那样做会使mysql和innoDB的锁机制互相干扰。Lock会终止当前事务。

事务

事务就是一组逻辑操作单元，使数据从一组状态转换到另一组状态。
innoDB支持事务，意味着我们可以把多条SQL命令封装成一个操作来执行。
发生错误，回滚此事务。（不能回滚别的事务）

ACID原则，信息安全方面的基本要求。

原子性
事务要么都执行，要么都不执行

稳定性（一致性）
事务执行完毕之后，有一个稳定的状态（转账，扣钱和增加，数据的完整性不能遭到破坏）

隔离性
多个事务可以同时独立运行，在执行过程中不会干扰彼此，每个事务都在一个事务空间（提出4种隔离级别来解决问题）

可靠性（持久性）
事务一旦提交是不可逆的，永久性的。
事务本身经得起软件硬件或其他故障，在故障消除后仍能正确执行。ACID都是如此，即使发生计算机崩溃等极端事件也要符合ACID原则。innoDB的可靠性是把所有修改都先写入一个日志，如果系统在修改被实际写入数据库之前发生崩溃，MySQL在服务器重启之后，innoDB表驱动程序将利用日志文件重新构造出所有的修改在传输给数据库。高可靠和高速度不可兼得，所以许多数据库都采取一种折中方案，提供一个刷新提交的选项，决定在什么时候才把这个事务保存到日志文件中。

– TPL事务控制
– begin 表示开启一个新的事务，存在一个新的事务空间
– 新的事务空间的数据来源于数据库的快照
– B客户端 事务未提交的时候 查询的数据仅仅是当前事务空间的数据。和数据库没有关系。看到的也是当前事务空间的数据
– 提交commit的时候，就是把当前事务空间的数据同步到数据库。
– 回滚就是 begin之后，回滚之前的操作全都删除了rollback。
发生错误，回滚此事务。（不能回滚别的事务）

事务的控制

在默认情况下，mysql自动提交模式运行，这意味着每条SQL命令都是一个小事务，这与我们正在使用的数据表是否支持事务没有关系。

数据库事务的并发问题

存在五种并发问题 4种现象

– 脏读
（读取了别人还没提交的数据，脏数据，不应该自己读取到的数据）

– 不可重复读（增删改都可能）
第一类丢失更新（回滚丢失，A事务撤销时，把已经提交的B事务的更新数据覆盖了。），
第二类丢失更新（commit 丢失 覆盖丢失，A事务覆盖B事务已经提交的数据，造成B事务所做操作丢失），以上两种统称数据丢失，造成的现象叫不可重复读。

–幻读（只针对新增的事务。在A事务中读取到了B事务新增加的数据，在事务内不应该读取到其他事务的新增加的内容,使用锁机制，防止新增加事务）。
一个事务的两次不同时间的相同查询返回了不同的的结果集

为了解决事务中并发可能出现的问题，我们提出了事务隔离级别的概念，
– 不同的隔离级别 就可以阻止我们所期望的并发问题

– 　　MySQL数据库为我们提供的四种隔离级别：

① Serializable (串行化，一个一个来，一般不用)：可避免脏读、不可重复读、幻读的发生。
② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
③ Read committed (读已提交)：可避免脏读的发生。
④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

– mysql默认用的 可重复读 也可以自己更改

– MySQL 因为自身的机制，不会出现幻读，read view(或者说 MVCC)实现了一致性不锁定读，从而避免了(非当前读下)幻读。

一致性读是通过 MVCC（多版本并发控制） 为查询提供了一个基于时间的点的快照。这个查询只能看到在自己之前提交的数据，而在查询开始之后提交的数据是不可以看到的。MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

当前读，快照读和MVCC的关系
准确的说，MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。仅仅是一个理想概念
而在MySQL中，实现这么一个MVCC理想概念，我们就需要MySQL提供具体的功能去实现它，而快照读就是MySQL为我们实现MVCC理想模型的其中一个具体非阻塞读功能。而相对而言，当前读就是悲观锁的具体功能实现
要说的再细致一些，快照读本身也是一个抽象概念，再深入研究。MVCC模型在MySQL中的具体实现则是由 3个隐式字段，undo日志 ，Read View 等去完成的，具体可以看下面的MVCC实现原理
链接：https://www.jianshu.com/p/8845ddca3b23

他是基于索引机制的，操作事务要用innoDB，所以也不会出现第一类丢失更新
– 所以选择可重复读的事务隔离级别，只会出现第二类丢失更新
使用锁机制进行控制 乐观锁和悲观锁

– 悲观锁 SELECT …FOR UPDATE; 加在最末尾，这样别人进行悲观锁的增删改查的时候
– 别人连查都查不到

– 乐观锁 使用版本控制，每次改的时候看一下版本，要是和预期的版本一致可以，不一致就不能进行操作

SQL的预编译。

就是先编译SQL语句，用占位符占位，等数据传进来的时候就认为他是参数，而不是一个SQL，这样防止SQL注入，运行的时候将不再重新编译了。

建立一个存储引擎为myisam的表。

表检查 没有损坏，都是ok

对表压缩，之后只能读操作，不能进行写操作。
空的不能压缩，反而会变大。

为什么使用数据库连接池？

普通的JDBC数据库连接使用driverManager来获取，每次向数据库建立连接的时候。都要将connection加载到内存中，再验证用户的密码花费0.05到1s的时间验证数据库连接的时候就要向数据库要一个。
执行完成后再断开链接，这样的方式将消耗大量的资源和时间。数据库的连接资源，并没有得到很好的连接和利用。
用户量大的时候频繁地进行数据连接将会占用很多的系统资源，甚至造成服务器崩溃。对于每一次数据库连接，用完后都要需要断开。否则程序出现异常而未能关闭，将会导致数据库系统中内存泄露，最终导致重启数据库。
这种开发不能控制被创建的对象连接数。系统资源被毫无顾忌的分配出去，如果连接过多也会造成内存泄露，服务器崩溃。

影响数据库性能的因素

大部分的问题都可以通过sql优化进行解决
避免大表

查询这种大表数据，比如根据日志统计各大电商网站的来源销量排行榜，显示给用户
就非常影响数据库性能。




不要再高并发的时候在主库进行备份，比如双十一时候，把备份迁到从库进行。

CPU密集型

不同的存储引擎缓存索引和数据的位置不同。


不光是读操作缓存收益高，写操作一样可以利用缓存，比如用户浏览数据的次数，如果每次都写入磁盘，需要频繁的IO，但是如果通过内存做一个计数器，当计数到100的时候，将数据写入到磁盘，那么就可以将100次操作，合并为1次，写入磁盘。缓存收益还是很大的。

不建议这样。

5.58
支持事务

5.6之后默认独立表空间！
看表空间是否打开

**数据库阻塞的现象：**第一个连接占有资源没有释放，而第二个连接需要获取这个资源。如果第一个连接没有提交或者回滚，第二个连接会一直等待下去，直到第一个连接释放该资源为止。对于阻塞，数据库无法处理，所以对数据库操作要及时地提交或者回滚。
**数据库死锁的现象：**第一个连接占有资源没有释放，准备获取第二个连接所占用的资源，而第二个连接占有资源没有释放，准备获取第一个连接所占用的资源。这种互相占有对方需要获取的资源的现象叫做死锁。对于死锁，数据库处理方法：牺牲一个连接，保证另外一个连接成功执行。

数据库结构优化



异步复制二进制日志

高版本做主库的备库，可以测试是否兼容。

查看二进制文件的格式，行，改成基于段的。

三个值解决方案，括号内。记录方式不同。

镜像复制；

索引优化

帮助数据库高效获取数据的数据结构。

索引是在基于数据库表创建的，它包含一个表中某些列的值以及记录对应的地址，并且把这些值存储在一个数据结构中。最常见的就是使用哈希表、B+树作为索引。

B树是为了充分利用磁盘预读功能来而创建的一种数据结构，也就是说B树就是为了作为索引才被发明出来的的。

预读；每一次IO时，不仅仅把当前磁盘地址的数据加载到内存，同时也把相邻数据也加载到内存缓冲区中。

1️从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。

2️从应用层次来分：普通索引，唯一索引，复合索引

3️根据中数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引。

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列
聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。
例子，字典，比如，我们要查“安”字，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
非聚簇索引：不是聚簇索引，就是非聚簇索引。

mysql默认存储引擎innodb只显式支持B-Tree( 从技术上来说是B+Tree)索引，对于频繁访问的表，innodb会透明建立自适应hash索引，即在B树索引基础上建立hash索引，可以显著提高查找效率，对于客户端是透明的，不可控制的，隐式的。

B+树

1每一个父节点的元素都出现在子结点中，是子节点的最大或者最小元素，需要注意的是，根节点的最大元素必须是整个B+树的最大元素。
2叶子节点包含了全量元素信息，并且每一个叶子节点都带有指向下一个节点的指针，形成一个有序链表。
3，索引元素所指向的数据记录叫卫星数据，比如数据库的某一行，B树中所有节点都有卫星数据。B+树中，只有叶子节点带有卫星数据。
4，聚集索引中，叶子节点直接包含卫星数据，非聚集索引中，叶子节点带有指向卫星数据的指针。

B树和B+树的区别
B+树中间没有卫星数据，所以同样大小的磁盘页可以容纳更多的节点元素。这就意味着，在同样数据量的情况下B+树更矮胖，查询时IO次数也更少。
其次，B树只要找到匹配元素即可，B+树却要最终查找到叶子节点上，所以B+树查找性能更加稳定。B树最好情况是查询到根节点，最坏情况是查到叶子节点。
第三，做范围查询，B树只能靠中序遍历。

而B+树只在链表上做遍历即可。
总结：IO次数少，性能更稳定，范围查找效率更高。

不同的数据引擎，即使是相同的数据类型，索引的底层实现也是不一样的。
Myisam，是通过数据的物理位置来进行引用行的。
Innodb是通过主键来引用行的。

InnoDB一磁盘页大小16K

覆盖索引：通常开发人员会根据查询的where条件来创建合适的索引，但是优秀的索引设计应该考虑到整个查询。其实mysql可以使用索引来直接获取列的数据。如果索引的叶子节点包含了要查询的数据，那么就不用回表查询了，也就是说这种索引包含（亦称覆盖）所有需要查询的字段的值，我们称这种索引为覆盖索引。
（如果你的查询条件和过滤条件是同一个索引列，那就不需要回表，select phone from t_user where phone = ‘123’，查询条件和过滤条件都是phone，phone为唯一索引，此时不需要回表，直接在phone的B+树叶子节点取值）

Explain 查询计划

创建索引

SQL优化

先对in中的数据进行排序，然后通过二分查找的方式来确定列表中的值是否符合要求。所以mysql比其他的数据库快。

不管包含每个阶段的信息，还包括CPU的信息

主从复制同步数据的时间。

1：现在从改，然后主从切换，再次修改旧从。
2：先建立一个新表，结构是新结构，再把旧表的数据进行复制，再旧表加一个触发器，可以对旧表的数据进行同步到新表，然后在对旧表加一个排他锁。将新表改成旧表的名字。然后删掉旧表就可以了。

可以用工具

存储过程

回滚点

主键冲突----2种解决

蠕虫复制—意义，1：从已有表拷贝数据到新表
2：可以迅速的让表中的数据膨胀到一定的数据集，测试表的压力还有效率

只是表结构
蠕虫是先查数据，数据成倍增长

一种是别的表，一种是自己的表

SQL优化

索引的原理
把无序的数据变成有序的结果

建立索引的执行流程
Select * from emp where id=6
如果没有索引

如果有索引
倒排索引

一个索引一棵树

Join on的on 两边表的字段名不一致，不能使用索引

必须是等值才能走索引，hash无序。

查看执行计划

调整SQL语句顺序去让MYSQL选择我们想走的索引。

SET是mysql的命令，设置mysql的环境变量，开启mysql的一些功能。
每一条SQL的记录都会记录下来，先关闭再看。生命周期就是一个窗口，关闭就没了

Join优化
尽量少的用join

Join优化原则

2.2的意思是在第二张大表中的字段加索引去循环。
减少外层循环，提高内层循环的速度。两种角度。

另一种SQL
先过滤有可能让后表比前表小。是结果集，并不是表。

和

的区别
SQL优化的原则

不是要优化慢的，而是优化应该优化的，比如线程连接请求多的SQL。而查询慢的，是报表类的大数据，这种连接非常少，而连接多的SQL即使速度快，一旦阻塞时间长就会影响后边的连接，造成连接请求翻倍。

如果要降低IO，就要从业务逻辑，索引，数据量去考虑，到底怎么优化，而不是直接从SQL入手改SQL，去尝试缩短时间，所以要明确优化目标。

Order by 对速度影响很大，数据量大的时候，可以把数据读取到内存中，再进行排序。
为什么慢，列多量大缓存区放不下，所以就只留下排序的列的值，再去取数据，多次请求。
可是一般不用考虑。

事务

查看执行计划没有IO，说明已经把数据读取到缓存中了。

先在事务空间执行SQL的DML操作，并且将DML的SQL语句保存的一个日志文件中，DQL的操作是不会存到文件中的，等到真正commit的时候，是在真正的数据库把那个日志文件执行一遍。

Mysql主从复制原理

写主读从。

有专门得线程去做这些事情，并且日志都是读写，没有select，而且这些SQL都是别人已经执行过的，肯定没有问题，不需要检查，直接做，所以很快。