Mysql知识点
数据库设计范式
- 第一范式
1NF是对属性的原子性，要求属性具有原子性，不可再分解；
- 第二范式
2NF是对记录的惟一性，要求记录有惟一标识，即实体的惟一性，即不存在部分依赖；
- 第三范式
3NF是对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖；
存储引擎
MyISAM和InnoDB区别
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 主外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁 | 行锁（支持高并发） |
| 缓存 | 只缓存索引 | 缓存索引和数据，对性能要求比较高 |
| 表空间 | 小 | 大 |
| 关注点 | 性能 | 事务 |
Sql分析
- 性能下降sql慢、执行时间长、等待时间长
- 查询语句写的烂
- 索引失效
- 单值
- 复合
- 关联查询太多join(设计缺陷或不得已的需求)
- 服务器调优及各个参数设置(缓冲、线程数等)
常见普通的Join查询
笛卡尔积：多表查询时, 没有任何关联条件时，产生的数据会试两个表的数据量的乘积。
sql执行顺序
手写
机读
3种Join
INNER JOIN
保留有关联的两个表的数据
LEFT JOIN
以from表为主表，关联left表，使用on，减小笛卡尔积，如果主表没有对应的关联表数据，则检索出单独的一条，关联表字段的值为null；反之，则丢弃关联表数据。
RIGHT JOIN
以right表为主表，关联from表，使用on，减小笛卡尔积，，如果主表没有对应的关联表数据，则检索出单独的一条，关联表字段的值为null；反之，则丢弃关联表数据。
总结
join的on和where里面的区别：
如果把on的条件方法where里面会导致：主表中丢失与关联表没有关系的数据。
drop，delete与truncate的区别
索引
索引：索引是一种数据结构，帮助mysql更高效的获取数据的数据结构，总结：排好序的快速查找数据结构。
如果要查“mysql”，我们肯定需要定位“m”，在定位“y”，在找到剩下的sql。
优势与劣势
优势
- 提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗
劣势
- 索引数量过大，在新增和删除的时候，索引需要重排序，怎加的cpu的开销和内存的损耗
- 占用硬盘
建立条件
- 数据区分度大且需要检索
- 需其他表有关联
- where里面用不要的查询字段
- group by order by字段
- 数据记录太少的不用
- 经常增删改的表不用建
索引类型
| 对比项 | HASH | BTREE |
|---|---|---|
| 查找效率 | 不支持范围查找（like），指定查找效率高（（=、>、>=、<、<=、between）） | 支持like |
| 联合索引 | 不能利用部分索引键查询 | 可以 |
| 排序操作 | 由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。 | BTREE天生支持 |
| 全表扫描 | 是的 | 不一定是 |
| 支持最左前缀匹配原则 | 不支持 | 支持，用上索引的第一个字段就可以匹配索引 |
| MyISAM和InnoDB是否支持？ | 不支持（只有Memory和NDB引擎索引支持） | 支持 |
| 数据结构 | hash表，通过键去找值的一种数据结构hash表 | B-tree，多路搜索树，并不是二叉的 |
mysql常见瓶颈
性能分析
- CPU
- IO
- 服务器硬件的性能瓶颈
慢sql分析
- 观察，至少跑一天，看看生产的慢sql情况
- 开启慢日志查询，这是阀值，
- explain
- show profile
- 进行sql数据可服务器的参数调优
Explain
- 执行计划：分析查询语句或是结构的性能瓶颈
- 写法 Explian+ sql语句
- 执行计划信息
- id: select 查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序。
- id相同，执行顺序由上至下
- id不同，id值越大优先级越高，越先被执行
- select_type:
- simple：普通查询，不包含子查询和UNION
- primary：查询中包含任意复杂度字部份，最外层查询则被标记
- subquery：子查询，select 或者where中的子查询
- derived：临时表
- union：第二个select出现在union之后。
- union result：从union表获取结果
- type
- system：表只有一行记录，是const类型的特列，平时不常见
- const：便是通过索引一次就找到了。const用于比较primary key 或者uninque，因为只匹配一行数据，所以如将主键至于where列表中，Mysql就能将该查询转化为一个常量
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主见或唯一索引扫描。
- ref：非唯一索引扫描，返回匹配某个单独值得所有行。本质上也是一种索引访问，他返回所有匹配某个单独值得行，然而他，他可能会找到更过符合条件的行，所以他应该属于查到和扫描的混合体。
- range：值检索给定范围的行，使用一个索引来选择行。key行显示使用哪个索引一般就是在where语句中出现了between、<、>、in等的查询这种范围扫描索引比全表扫描要好，因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
- index：full index scan，index与all区别在于index类型之便利索引树。这通常比all快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）。
- ALL：full table Scan, 将遍历全表以找到匹配的行。
一般来说，需要保证查询至少到达range级别，最好能到达ref。
- possible_keys：可能用到的索引。
- key：实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引仅 出现在key列表中，如果在possible_keys出现，而在key中没有出现，可能是因为检索出的数据过多从而走的是全表扫描。
- ref：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值。
- rows：检索行数
- extra：
- Using filesort：mysql会对数据使用另一个外部的索引排序，而不是按照表内的索引顺序进行读取。无法利用索引完成的排序操作成为“文件排序”。
- Using temporary：使用临时表保存中间结果，Mysql在对查询结果排序时使用临时表。
- Using index：相应的select操作中使用了覆盖索引，避免访问了表的数据行，如果同时出现了using where， 表明索引被应用来执行索引值得查找，如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。覆盖索引：select的数据类只用从索引中就能获取到，不必读取数据行，Mysql可以利用索引返回select列表中的字段，二不必根据索引在此读取数据文件，换句话说查询列要被所建的索引覆盖。
- Using where：使用where过滤
- Using join buffer：使用了连接缓存，需要调整配置文件的join_buffer的大小。
- impossible where：where的语句为false，不能用来获取任何元组。
- select tables optimized awad：
- distinct：
- id: select 查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序。
sql优化
join优化：
小表关联大表；尽量减少join语句中nestedloop的内层循环；保证join语句中关联表上join条件字段已经被索引；无法保证关联表字段索引且内存资源充足的前提下，不要吝啬JoinBuff的设置
索引优化：
- 全值匹配
- 最佳左前缀法则：查询从索引的最左前列开始并且不跳过索引中的列。
- 不在索引列上做任何操作（计算，函数，（自动or手动）类型转化），会导致索引失效从而全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致））。减少select*
- mysql在使用不等于（！=或者<>）的时候不发使用索引会导致全表扫描
- is null, is not null 也无法使用索引
- like以通配符开头（‘%abc’）mysql索引失效会变成全表扫描的操作，如何解决两边%索引失效，
- 字符串不加单引号索引失效
- 少用or，用它来连接时会索引失效
- ：注意索引使用的时候是否产生 filesort。
order by优化
- order by 子句，尽量使用index方式排序，避免使用filesort方式排序
- mysql支持两种凡是的排序，filrsort和index，index效率高，它指mysql扫描索引本身完成排序。FileSort方式效率较低。
- order by 满足两种情况，会使用Index方式排序
- order by 语句使用索引最左前列
- 使用where子句与order by子句条件列组合满足索引最左前列
- 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
- 如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序
- 双路排序：
- mysql4.1之前使用双路排序，两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取磁盘去排序字段，在buffer进行排序，再从磁盘去其他字段。
- 单路排序：
- 从磁盘读取查询需要的所有列，按照orderby列都在buffer对它们进行排序，然后扫描排序后的列表进行输出，他的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中。
- 双路排序：
- 优化策略：
- 使用orderby，尽量不使用select* 防止，select字段过大导致srot_buffer缓冲区不够，产生多个临时tmp文件，导致多次IO。
- 尝试提高 sort_buffer_size和max_length_for_sort_data，提高效率。
group by优化
groupby实质是线排序后进行分组，遵照索引 建的最佳左前缀，当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数设置；where高于having，能写在where限定的条件就不要去having限定了。
慢查询日志分析
- 开启
- 默认是关闭：show variables like ‘%slow_query_log%’
- 开启： set global slow_query_log = 1;(支队当前有效)
事务
一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务
事务四大特征
- 原子性(A)：事务是最小单位，不可再分
- 一致性©：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
- 隔离性(I)：事务A和事务B之间具有隔离性
- 持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)
隔离级别
- 读未提交：read uncommitted。事务B读取到了事务A未提交的数据，可能发生脏读、不可重复读和幻读问题
- 读已提交：read committed。事务B读取到了事务A已提交的数据，可能发生不可重复读和幻读问题，但是不会发生脏读问题。
- 可重复读：repeatable read。事务A和事务B，事务A提交之后的数据，事务B读取不到，可能发生幻读问题，但是不会发生脏读和不可重复读的问题。
- 串行化：serializable。事务A和事务B，事务A在操作数据库时，事务B只能排队等待，各种问题都不会发生。
针对不同的隔离级别，并发事务可以发生不同严重程度的问题
- 脏读：事务B读取到了事务A未提交的数据
- 不可重复读：事务A依次读取两个值V1和V2（初始值都为0），事务B写入V1和V2，过程：事务A先读取V1的值（此时V1为0），过程中事务B修改V1和V2的值，并且提交了，事务A继续读取V2的值*（此时V2的值为1），事务A读取到了不一致的状态，这种情况是不合适的。
- 幻读：当同一个事务中，查询在不同的时间产生不同的结果集时，事务中就会出现所谓的幻象问题。
例如，一个事务A先根据某些条件查询出一些记录，在该事务未提交时，另外一个事务B写入了一些符合那些搜索条件的记录，此时事务A读取的数据实际上少了一些，发生了幻读现象。
不可重复读与 幻读感觉是对立面，一个是读到其他事务的数据有问题，一个是没有读到其他事务提交的数据？
MySQL的八种锁
表锁（TABLE LOCKS）
MyISAM只有表锁（共享读锁，独占写锁）
- lock table 表名 （read/write）
- In USE，查看锁的数据量
- lock table 表一 read, 表二 wirte;
- unlock table 表名
- 添加读/写锁，可以读自己加锁的表，不能写自己，不能读写其他表（不论该表是否加锁），其他的session需要等待自己加锁的表释放锁。
表锁分析
show open tables;
show status like ‘table%’
行锁（Record Locks）
- 在InnoDB中，写操作都是自动加锁的。
- 无索引/索引失效，行锁升级为表锁。
#添加行锁
select * from table where id ="1" for update;
查询锁信息
show status like 'innodb_row_lock%';
间隙锁（Gap Locks）
当使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引加锁；对于键值不在条件范围内并不存在的记录，叫做“间隙（GAP）”。
InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
例如,下表是table表的现有数据
| id | value |
|---|---|
| 1 | 0001 |
| 3 | 0003 |
| 4 | 0004 |
| 5 | 0005 |
| 6 | 0006 |
## 开启session1执行sql
set autocommit = 0；
update table1 set value='1' where id >'2' and id <'6';
此时开启session2：执行
insert into table values('2', '0002');
## 此时sql会阻塞，等待session1提交/取消
## session1执行
commit;
## session1执行commit之后，session展示的数据
insert into table values('2', '0002');
Query OK, 1 row affected(13.06 sec)
##session1 查询数据
select * from table
| id | value |
|---|---|
| 1 | 0001 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 2 | 0002 |
危害：
执行写操作的时候，通过范围查找的话，会锁定整个范围内所有的索引键值，及时这个主键并不存在，导致插入该键值的时候要等待之前范围写操作结束。
临键锁（Next-key Locks）
共享锁/排他锁（Shared and Exclusive Locks）
意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）
插入意向锁（Insert Intention Locks）
自增锁（Auto-inc Locks）
主从赋值
Mysql复制过程：
- master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志时间，binary log events;
- slave将master的binary log events 拷贝到他的中继日志（relay log）；
- slave重做中继日志中的事件，将改变应用到自己的数据库中。Mysql复制是异步的且串行化的。
规则：
- 每个slave只有一个master
- 每个slave值有一个唯一的服务器ID
- 每个master可以有多个slave
复制的最大问题：
网络延时。
一主一从
- mysql 版本一直且后台以服务运行，双向都可以ping通
- 主从都配合在[mysqld]节点下，都是小写。
- 主机修改配置文件：
- server-id=1 #主机服务器唯一ID 必填
- 启动二进制日志 必填
log-bin=本地的路径/mysqlbin (D:/devsoft/mysqlServer/data/mysqlbin)- 启用错误日志 可选
log-bin=本地的路径/mysqlbin (D:/devsoft/mysqlServer/data/mysqlerr)- 根目录 可选
basedir=“自己本地路径”(D:/devsoft/mysqlServer/)- 临时目录 可选
tmpdir=“自己本地路径”(D:/devsoft/mysqlServer/)- 数据目录 可选
tmpdir=“自己本地路径”(D:/devsoft/mysqlServer/data)- 设置需要和不需要的数据库 可选
binlog-ignore-db = mysql（mysql自带的）
binlog-do-db = 需要赋值的数据库命
- 修改从机配置文件
- log-bin =mysql-bin
- 不需要server-id
- 启动二进制日志 可填
- 重启主从mysql服务器
- 主从机关闭防火墙
- 在主机上建立账户并授权slave
- grant replication slave on . to ‘用户名’@‘从机数据库IP’ identified by ‘123456’;
- flush privileges;#刷新配置
- 查看信息 show master status
- 记录file和positon的值（执行show master status的结果）
- 从机
change master to master host=‘主机地址’
master_user = ‘授权账户’
master_password = ‘授权密码’
master_log_file = ‘文件地址’, master_log_pos=开始赋值行数;
start slave;
show slave status\G, 查看确认Slave_IO_Running和lave_SQL_Running必须都是Yes
10 停止从服务赋值功能：stop slave
1805
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
