mysql-总结列表
------------------------------------------------------------
mysql有多种日志，常见的有:
错误日志（ErrorLog）
更新日志（UpdateLog）
二进制日志（Binlog）
查询日志（QueryLog）
慢查询日志（SlowQueryLog）
Binlog可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，此外Binlog是事务安全型的。
Binlog一般作用是可以用于实时备份，与master/slave主从复制结合。
基础
常用命令
| 命令 | 说明 |
|---|---|
| net start mysql | 启动mysql服务 |
| net stop mysql | 关闭mysql服务 |
| update mysql.user set authentication_string=password('root') | where user='root' and Host = 'localhost';修改mysql密码 |
| flush privileges | 刷新权限 |
| mysql -u root -p | 登录mysql |
| show databases | 查看所有数据库 |
| use 数据库名 | 切换数据库 |
| describe 表名 | 显示表信息 |
| crate 表名 | 创建数据库 |
| exit | 退出 |
结构化查询语言分类
| 名称 | 说明 | 命令 |
|---|---|---|
| 数据定义语言(DDL) | 定义和管理数据对象，如数据库，数据表等 | CREATE,DROP,ALTER |
| 数据操作语言(DML) | 用于操作数据库对象中所包含的数据 | INSERT,UPDATE,DELETE |
| 数据查询语言(DQL) | 用于查询数据库的数据 | SELECT |
| 数据控制语言(DCL) | 用于管理数据的语言，包括全选及数据更改 | GRANT,COMMIT,ROLLBACK |
数据库的字段属性
2.4.1 Unsigned
- 无符号的整数
- 声明了该列不能为负数
2.4.2 zerofill
- 0填充的
- 不足的位数，使用0来填充，int(3), 5 -> 005
2.4.3 auto_Increment
- 自动增长的，每条数据，自动在上一个记录数上加1(默认)
- 通常设置主键，且必须是整数类型
- 可以自定义设置主键自增的起始值和步长
2.4.4 null 和 not null
- null ,如果不填写值，默认就是null
- not null,如果不填写值就会报错
业务中每个表必须存在的5个字段
| 字段名 | 说明 |
|---|---|
| id | 主键 |
| version | 乐观锁 |
| is_delete | 逻辑删除 |
| gmt_create | 创建时间 |
| gmt_update | 修改时间 |
MYSIAM 和 INNODB 对比
| 名称 | MYISAM | INNODB |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大，约为MYISAM2倍 |
常规使用操作：
- MYISAM 节约空间，速度快
- INNODB 安全性高，事务的处理，多表多用户操作
2.6.1 物理空间存在的位置
所有的数据库文件都存在date目录下，每一个文件对应一个数据库;
本质还是文件的存储！
MySQL 引擎在物理文件上的区别:
-
InnoDB在数据库表中只用一个*.frm文件，以及上级目录的ibdata1文件
-
MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
delete 和 truncate 区别**
- 相同点：都能删除数据，都不会删除表结构
- 不同点
- truncate 重新设置自增列，计数器会归零
- truncate 不会影响事务
delete删除的问题，重启数据库，现象 （了解）
- InnoDB 自增列会从1开始（存在内存中的，断电及失去）
- MyISAM 继续从上一个自增量开始（存在文件中，不会失去）
分页
注意limit后面的参数必须是具体值，不能是表达式；
-- 100 万条数据
-- 为什么要分页？
-- 缓解数据库的压力，给人的体验感更好(瀑布流，无限刷)
-- 分页，每页只显示五条数据
-- 语法：limit 起始数据下标，页面大小
-- 网页应用：当前页，总页数，页面大小
-- limit 0,5 1~5
-- limit 1,5 2~6
-- limit 5,5 6~10
-- 注意limit后面的参数必须是具体值，不能是表达式
select s.studentno,studentname,subjectname,studentresult
from student s
inner join result r
on s.studentno = r.studentno
inner join `subject` sub
on r.subjectno = sub.subjectno
where subjectname = '数据库结构-1'
order by studentresult asc
limit 5,5;
-- 第一页 limit 0,5 (1-1)*5
-- 第二页 limit 5,5 (2-1)*5
-- 第三页 limit 10,5 (3-1)*5
-- 第N页 limit (n-1)*pagesize,pagesize
-- pagesize:页面大小
-- (n-1)*pagesize 起始值
-- n当前页
-- 总页数 = 数据总数/页面大小
语法：limit(起始下标，pagesize)
常用函数
5.1.1 数学函数(单行函数)
| 函数 | 说明 |
|---|---|
| select abs(-8) | 绝对值 |
| select ceiling(9.3) | 向上取整 |
| select floor(9.8) | 向下取整 |
| select round(num,x) | 四舍五入，保留为小数 |
| select rand() | 0到1之间的随机数 |
| select sign(-10) | 判断一个数的符号 0返回0，负数返回，整数返回1 |
| select truncate(96.88,1) | 截断，第二个参数表示小数位 |
5.1.2 字符函数
| 函数 | 说明 |
|---|---|
| select char_length('我爱MySQL') | 字符串长度 |
| select concat('l','love','you') | 字符串拼接 |
| select insert('我爱你',1,2,'小可爱') | 查询，从指定替换指定长度 |
| select lower('HUAYU') | 转小写 |
| select upper('huayu') | 转大写 |
| select instr('i love mysql','l') | 返回第一次出现的字串的索引 |
| select replace('坚持就能成功','坚持','努力') | 替换指定字符 |
| select substr('狂神说坚持就能成功',4,6) | 截取指定字符substr(源字符，字符起始下标，字符长度) |
| select reverse('开始反转吧') | 反转 |
| select replace(str,'-',''); | 字符替换replace(uuid(),'-',''); |
5.1.3 时间日期函数(单行函数
| 函数 | 说明 |
|---|---|
| select current_date() | 返回当前日期 |
| select curdate() | 返回当前日期 |
| select now() | 获取当前的时间 |
| select localtime() | 获取本地时间 |
| select sysdate() | 获取系统时间 |
| select year(now()) | 年 |
| select month(now()) | 月 |
| select day(now()) | 日 |
| select hour(now()) | 时 |
| select minute(now()) | 分 |
| select second(now()) | 秒 |
| select date_format(now(),'%Y年%m月%d日 %H:%i:%s'); | 日期格式化 |
5.1.4 系统函数
| 函数 | 说明 |
|---|---|
| select system_user() | 系统用户 |
| select user() | 用户 |
| select version() | 版本 |
5.2 聚合函数（多行函数）
注意聚合函数，不能放在where之后，必须在一个已经确定的集合中使用聚合函数；大多放在select或者having之后，对你确定的集合进行过滤；
| 函数 | 说明 |
|---|---|
| count() | 计数 count(1) count(主键) |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
数据库连接池
11.10.1 池化技术
数据库连接 -- 执行完毕 -- 释放；
连接 -- 释放 十分浪费系统资源；
池化技术：准备一些预先得资源，过来就连接预先准备好；
最小连接数，最大连接数，等待超时；
编写一个连接池，只要实现一个接口 DataSource;
11.10.2 开源数据源实现
- DBCP
- C3P0
- Druid:阿里巴巴
区别
- C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。
- c3p0与dbcp区别：
- dbcp没有自动回收空闲连接的功能；
- c3p0有自动回收空闲连接功能。
------------------------------
常用命令
1、连接到本机上的MYSQL。
首先打开DOS窗口，然后进入目录mysql\bin，再键入命令mysql -u root -p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密码前必须没有空格，否则让你重新输入密码。
如果刚安装好MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL中了，MYSQL的提示符是： mysql>
2、连接到远程主机上的MYSQL。假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：
mysql -h110.110.110.110 -u root -p 123;（注:u与root之间可以不用加空格，其它也一样）
3、退出MYSQL命令： exit （回车）
第三招、增加新用户
格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码"
如，增加一个用户user1密码为password1，让其可以在本机上登录， 并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql，然后键入以下命令：
grant select,insert,update,delete on *.* to user1@localhost Identified by "password1";
如果希望该用户能够在任何机器上登陆mysql，则将localhost改为"%"。
如果你不想user1有密码，可以再打一个命令将密码去掉。
grant select,insert,update,delete on mydb.* to user1@localhost identified by "";
第四招： 操作数据库
登录到mysql中，然后在mysql的提示符下运行下列命令，每个命令以分号结束。
1、 显示数据库列表。
show databases;
缺省有两个数据库：mysql和test。 mysql库存放着mysql的系统和用户权限信息，我们改密码和新增用户，实际上就是对这个库进行操作。
2、 显示库中的数据表：
use mysql;
show tables;
3、 显示数据表的结构：
describe 表名;
4、 建库与删库：
create database 库名;
drop database 库名;
5、 建表：
use 库名;
create table 表名(字段列表);
drop table 表名;
6、 清空表中记录：
delete from 表名;
7、 显示表中的记录：
select * from 表名;
第五招、导出和导入数据
1. 导出数据：
mysqldump --opt test > mysql.test
即将数据库test数据库导出到mysql.test文件，后者是一个文本文件
如：mysqldump -u root -p123456 --databases dbname > mysql.dbname
就是把数据库dbname导出到文件mysql.dbname中。
2. 导入数据:
mysqlimport -u root -p123456 < mysql.dbname。
1.导出整个数据库
导出文件默认是存在mysql\bin目录下
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u user_name -p123456 database_name > outfile_name.sql
2.导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u user_name -p database_name table_name > outfile_name.sql
3.导出一个数据库结构
mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql
-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table
4.带语言参数导出
mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile_name.sql
例如，将aaa库备份到文件back_aaa中：
[root@test1 root]# cd /home/data/mysql
[root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa
当前自己的数据版本是8.0.22
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.22 |
+-----------+
1 row in set (0.00 sec)
数据库隔离级别(默认隔离级别)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
自动提交关闭
mysql> select @@autocommit;查看分析死锁日志
可以用 show engine innodb status，查看最近一次死锁日志哈，执行后，死锁日志如下(只展示部分日志)：
LATEST DETECTED DEADLOCK----------------------------------------------
一、事务ACID
什么是事务
- 要么都成功，要么都失败。
- 事务就是将一组SQL语句放在同一批次内去执行；
- 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
⑴ 原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
⑵ 一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
⑶ 隔离性（Isolation）
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
⑷ 持久性（Durability）
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
二、隔离级别
脏读
脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
不可重复读
不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。
虚读(幻读)
幻读是事务非独立执行时发生的一种现象。
四种隔离级别：
① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
③ Read committed (读已提交)：可避免脏读的发生。
④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
3、事务隔离级别
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
| 读未提交（read-uncommitted） | 是 | 是 | 是 |
| 不可重复读（read-committed） | 否 | 是 | 是 |
| 可重复读（repeatable-read） | 否 | 否 | 是 |
| 串行化（serializable） | 否 | 否 | 否 |
数据库事务隔离级别分为四种(级别递减)：
1、Serializable (串行化)：最严格的级别，事务串行执行，资源消耗最大;
2、REPEATABLE READ(重复读) ：保证了一个事务不会修改已经由另一个事务读取但未提交(回滚)的数据。避免了“脏读取”和“不可重复读取”的情况，但不能避免“幻读”，但是带来了更多的性能损失。
3、READ COMMITTED (提交读)：大多数主流数据库的默认事务等级，保证了一个事务不会读到另一个并行事务已修改但未提交的数据，避免了“脏读取”，但不能避免“幻读”和“不可重复读取”。该级别适用于大多数系统。
4、Read Uncommitted(未提交读) ：事务中的修改，即使没有提交，其他事务也可以看得到，会导致“脏读”、“幻读”和“不可重复读取”。
三、锁介绍
表级锁：开销小，加锁快；不会出现死锁(因为MyISAM会一次性获得SQL所需的全部锁)；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。
行锁 和 表锁 的区别：
表锁：开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
行锁：开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
表锁和行锁应用场景
表级锁使用与并发性不高，以查询为主，少量更新的应用，比如小型的web应用；
而行级锁适用于高并发环境下，对事务完整性要求较高的系统，如在线事务处理系统。
悲观锁 和 乐观锁
（1）悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。
传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
（2）乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。
四、SQL查询执行全过程解析
五、索引的分类
索引（index）是帮助 MysQL高效获取数据的数据结构。
提取句子主千，就可以得到索引的本质：索引是数据结构。
| 索引名 | 说明 |
|---|---|
| PRIMARY KEY | 主键索引 |
| UNIQUE KEY | 唯一索引 |
| KEY/INDEX | 常规索引 |
| FullText | 全文索引(MyISAM) |
7.2.1 PRIMARY KEY 主键索引
- 唯一的标识，主键不可以重复，只能由一个列作为主键；
7.2.2 UNIQUE KEY 唯一索引
- 避免重复的列出现，唯一索引可以重复，多个列多可以标识为唯一索引；
7.2.3 KEY/INDEX 常规索引
- 默认的，index,key关键字来设置；
7.2.4 FullText 全文索引
-
在的顶的数据引擎下才有，MyISAM；
-
快速定位；
MySQL主要的几种索引类型：1.普通索引、2.唯一索引、3.主键索引、4.组合索引、5.全文索引。
1.普通索引
是最基本的索引，它没有任何限制。
2.唯一索引
与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
3.主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
主键索引和唯一索引的区别：
主键必唯一，但是唯一索引不一定是主键；
一张表上只能有一个主键，但是可以有一个或多个唯一索引。
4.组合索引
一个索引包含多个列，实际开发中推荐使用复合索引。
5.全文索引
FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
索引的优点缺点
优点：
（1）提高数据检索的效率，降低数据库IO成本。
（2）通过索引对数据进行排序，降低数据的排序成本，降低CPU的消耗。
缺点：
（1）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
（2）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
注意
1.不是索引越多越好，数据库底层要管理索引，也需要耗费资源和性能(数据库性能会下降)；
2.如果当前列数据重复率较高，比如性别，不建议使用索引；
3.如果当前列内容，经常改变，不建议使用索引，因为数据频繁修改要频繁的维护索引，性能会下降；
4.小数据量的表也不推荐索引，因为小表的查询效率本身就很快；
InnoDB 索引原理
MySQL表中的所有数据被存储在一个空间内，称之为表空间，表空间内部又可以分为段(segment)、区(extent)、页(page)、行(row),
InnoDB行记录格式
InnoDB提供了两种格式来存储行记录：Redundant格式、Compact格式、Dynamic格式、Compressed格式，Redudant格式是为了兼容保留的。
- 列数据：此行存储着列字段数据，Null是不占存储空间的；
- 隐藏列：事务id和回滚列id,分别占用6、7字节，若此表没有主键，还会增加6字节的rowid列。
整体分三层：内存结构，Cache，磁盘结构。
- 内存结构
内存结构又包括四大组件
Buffer Pool：缓冲池：是主内存中的一个区域，在InnoDB访问表和索引数据时会在其中进行高速缓存，大量减少磁盘IO操作，提升效率。
Change Buffer：写缓冲区：避免每次增删改都进行IO操作，提升性能。
Adaptive Hash Index：自适应哈希索引：使用索引关键字的前缀构建哈希索引，提升查询速度。
Log Buffer：日志缓冲区：保存要写入磁盘上的日志文件的数据，缓冲区的内容定期刷新到磁盘。
- 磁盘结构
Tables：数据表的物理结构。
Indexes：索引的物理结构。
Tablespaces：表空间，数据存储区域。
Data Dictionary：数据字典，存储元数据信息的表，例如表的描述，结构，索引等。
Doublewrite Buffer：位于系统表空间的一个存储区域，InnoDB在BufferPool中刷新页面时，会将数据页写入该缓冲区后才会写入磁盘。
Redo Log：记录DML操作的日志，用来崩溃后的数据恢复。
Undo Logs：数据更改前的快照，可以用来回滚数据。
(3)、特点描述
- 支持事务
事务内在执行一组SQL语句时，要么全部成功，要么全部失败。
- 支持分布式事务
分布式事务指即使不同操作位于不同的服务应用上，仍然需要保证事务的特性。常见场景：订单和库存在不同的服务中，但却能保持一致性。
- 支持行级锁
加锁时锁定一行数据的锁机制就是行级别锁定(row-level)。MySQL5.7版本中只有InnoDB引擎支持。锁定的粒度小，自然支持的并发就高，锁定的机制也随之变的复杂。
- 支持MVCC
多版本并发控制，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。
- 支持聚簇索引
是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。
B树与B+树
B树（B-TREE）满足如下条件，即可称之为m阶B树：
- 每个节点之多拥有m棵子树；
- 根结点至少拥有两颗子树（存在子树的情况下);
- 除了根结点以外，其余每个分支结点至少拥有 m/2 棵子树；
- 所有的叶结点都在同一层上；
- 有 k 棵子树的分支结点则存在 k-1 个关键码，关键码按照递增次序进行排列；
- 关键字数量需要满足ceil(m/2)-1 <= n <= m-1；
B+树满足如下条件，即可称之为m阶B+树：
- 根结点只有一个，分支数量范围为[2，m]
- 分支结点，每个结点包含分支数范围为[ceil(m/2), m]；
- 分支结点的关键字数量等于其子分支的数量减一，关键字的数量范围为[ceil(m/2)-1, m-1]，关键字顺序递增；
- 所有叶子结点都在同一层；
B树与B+树区别：
以m阶树为例：
- 关键字不同：B+树中分支结点有m个关键字，其叶子结点也有m个，但是B树虽然也有m个子结点，但是其只拥有m-1个关键字。
- 存储位置不同：B+树非叶子节点的关键字只起到索引作用，实际的关键字存储在叶子节点，B树的非叶子节点也存储关键字。
- 分支构造不同：B+树的分支结点仅仅存储着关键字信息和儿子的指针，也就是说内部结点仅仅包含着索引信息。
- 查询不同（稳定）：B树在找到具体的数值以后，则结束，而B+树则需要通过索引找到叶子结点中的数据才结束，也就是说B+树的搜索过程中走了一条从根结点到叶子结点的路径。
聚簇索引
每个InnoDB的表都拥有一个索引，称之为聚簇索引，此索引中存储着行记录，一般来说，聚簇索引是根据主键生成的。为了能够获得高性能的查询、插入和其他数据库操作，理解InnoDB聚簇索引是很有必要的。
聚簇索引按照如下规则创建：
- 当定义了主键后，InnoDB会利用主键来生成其聚簇索引；
- 如果没有主键，InnoDB会选择一个非空的唯一索引来创建聚簇索引；
- 如果这也没有，InnoDB会隐式的创建一个自增的列来作为聚簇索引。
辅助索引
除了聚簇索引之外的索引都可以称之为辅助索引，与聚簇索引的区别在于辅助索引的叶子节点中存放的是主键的键值。
MyISAM引擎
(1)、基础描述
MySQL5.1和之前版本的默认存储引擎，不支持事务和行级锁，自然崩溃之后不能自动恢复。
(2)、特点描述
- 锁表机制
对整张表加锁，不针对行加锁，读数据加共享锁，写数据加排他锁。
- 全文索引
支持全文索引，一种基于分词创建的索引，可以支持复杂的检索查询。
六、索引的注意事项
1.尽量少使用模糊查询，如果要使用那么，通配符%可以出现在结尾，不能在开头。
如：name like ‘张%’ ，索引有效
而：name like ‘%张’ ,索引无效，全表查询
2：or 会引起全表扫描
3：不要使用NOT、!=、NOT IN、NOT LIKE等
4.尽量少使用select*，而是根据需求来选择需要显示的字段
5.索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
6.不要在列上进行运算，这将导致索引失效而进行全表扫描
7.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作.
8、union并不绝对比or的执行效率高
我们前面已经谈到了在where子句中使用or会引起全表扫描，一般的，我所见过的资料都是推荐这里用union来代替or。事实证明，这种说法对于大部分都是适用的。
有一点不适用:如果or两边的查询列是一样的话，那么用union则反倒和用or的执行速度差很多，虽然这里union扫描的是索引，而or扫描的是全表。
1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16'' or fariqi=''2004-2-5''
用时：6423毫秒。扫描计数 2，逻辑读 14726 次，物理读 1 次，预读 7176 次。
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-2-5''
用时：11640毫秒。扫描计数 8，逻辑读 14806 次，物理读 108 次，预读 1144 次。
9、索引有如下有点：减少服务器扫描的数据量、避免排序和临时表、将随机I/O变为顺序I/O。
可使用B+树索引的查询方式
- 全值匹配：与索引中的所有列进行匹配，也就是条件字段与联合索引的字段个数与顺序相同；
- 匹配最左前缀：只使用联合索引的前几个字段；
- 匹配列前缀：比如like 'xx%'可以走索引；
- 匹配范围值：范围查询，比如>,like等；
- 匹配某一列并范围匹配另外一列：精确查找+范围查找；
- 只访问索引查询：索引覆盖，select的字段为主键；
七、索引方式（结构）
mysql有两种所以方式：Hash和BTree。
Hash索引
所谓Hash索引，当我们要给某张表某列增加索引时，将这张表的这一列进行哈希算法计算，得到哈希值，排序在哈希数组上。所以Hash索引可以一次定位，其效率很高，而Btree索引需要经过多次的磁盘IO。
因为Hash索引比较的是经过Hash计算的值，所以在= in <=>(安全等于的时候）塔的效率是非常，但我们开发一般会选择Btree，Hash会存在如下一些缺点。
（1）Hash索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。
（2）Hash 索引无法被用来避免数据的排序操作。
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash值，而且Hash值的大小关系并不一定和 Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；
（3）Hash索引不能利用部分索引键查询。
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。
（4）Hash索引在任何时候都不能避免表扫描。
前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
（5）Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
BTREE
B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型。简单理解，塔就像一棵树，B-Tree索引需要从根节点到枝节点，就能才能访问到页节点的具体数据。
btree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索，根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找,通过比较节点页的值和要查找的值可以找到合适的指针进入下一层子节点，这些指针实际上定义了子节点页中值的上限和下限，最终存储引擎要么是找到对应的值，要么是该记录不存在。
B-tree 索引可以用于使用 =, >, >=, <, <= 或者 BETWEEN 运算符的列比较。如果 LIKE 的参数是一个没有以通配符起始的常量字符串的话也可以使用这种索引。
八、explain 分析sql语句
mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2"; -- 结果： id: 1 select_type: SIMPLE -- 查询类型（简单查询,联合查询,子查询） table: user -- 显示这一行的数据是关于哪张表的 type: range -- 区间索引（在小于1990/2/2区间的数据),这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表扫描了全表才确定结果。一般来说,得保证查询至少达到range级别,最好能达到ref。 possible_keys: birthday -- 指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。 key: birthday -- 实际使用到的索引。如果为NULL,则没有使用索引。如果为primary的话,表示使用了主键。 key_len: 4 -- 最长的索引宽度。如果键是NULL,长度就是NULL。在不损失精确性的情况下,长度越短越好 ref: const -- 显示哪个字段或常数与key一起被使用。 rows: 1 -- 这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。 Extra: Using where; Using index -- 执行状态说明,这里可以看到的坏的例子是Using temporary和Using
select_type
simple： 简单的select 查询，查询中不包含子查询或者union
primary： 查询中若包含任何复杂的子查询，最外层查询则被标记为primary
subquery： 在select或where 列表中包含了子查询
derived： 在from列表中包含的子查询被标记为derived（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
union： 若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为：derived
union result： 从union表获取结果的select
type
这是一个非常重要的参数，连接类型，常见的有：all , index , range , ref , eq_ref , const , system , null 八个级别。
性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all
对java程序员来说，若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。
all： 全表扫描。全表扫描无疑是最差，若是百万千万级数据量，全表扫描会非常慢。
index： 索引全表扫描。全索引文件扫描比all好很多，毕竟从索引树中找数据，比从全表中找数据要快。
range： 索引范围扫描。只检索给定范围的行，使用索引来匹配行。范围缩小了，当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between，in，>，< 等查询。
ref： 非唯一性索引扫描，本质上也是一种索引访问，返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事，匹配的结果是多个并非唯一值。
eq_ref： 唯一性索引扫描，对于每个索引键，表中有一条记录与之匹配。比如查询公司的CEO，匹配的结果只可能是一条记录，
const： 表示通过索引一次就可以找到，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快，若将主键至于where列表中，MySQL就能将该查询转换为一个常量。
system：表只有一条记录（等于系统表），这是const类型的特列，平时不会出现，了解即可
possible_keys
显示查询语句可能用到的索引(一个或多个或为null)，不一定被查询实际使用。仅供参考使用。
key
显示查询语句实际使用的索引。若为null，则表示没有使用索引。select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。
key_len
显示索引中使用的字节数，可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。
ref
如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
rows
这里是执行计划中估算的扫描行数，不是精确值，值越大越不好。
extra
Using filesort： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
Using temporary： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。
Using index： 表示相应的select 操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。
Using index condition： 在5.6版本后加入的新特性，优化器会在索引存在的情况下，通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
Using where： 表明使用了where 过滤
Using join buffer： 表明使用了连接缓存
impossible where： where 语句的值总是false，不可用，不能用来获取任何元素
distinct： 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
filtered
一个百分比的值，和rows 列的值一起使用，可以估计出查询执行计划(QEP)中的前一个表的结果集，从而确定join操作的循环次数。小表驱动大表，减轻连接的次数。
总结：
通过explain之后，我们可以通过一些属性的优化
id属性（通过id属性我们能够准确知道查询语句的执行属性，同时结合小表驱动大表的原则进行优化。
type属性（至少优化到range级别），
key_len属性（在不损失精确性的情况下索引长度越短越好）
rows属性 (看是否减少扫描行数)。
extra属性（如果出现Using filesort Using temporary必须优化，如果能出现Using index那就完美了）。
十、慢查询
1、参数说明
slow_query_log ： 慢查询开启状态（默认关闭）
slow_query_log_file ： 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限， 一般设置为MySQL的数据存放目录）
long_query_time ： 查询超过多少秒才记录(默认10秒）
show variables like 'slow_query%';
2.如何捕获低效sql
1）slow_query_log
这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。
2）ong_query_time
当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。
3）slow_query_log_file
记录日志的文件名。
4）log_queries_not_using_indexes
这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。
2.慢查询优化的基本步骤
1)先运行看看是否真的很慢，注意设置SQL_NO_CACHE
2）where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
3)explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
4)order by limit 形式的sql语句让排序的表优先查
5)了解业务方使用场景
6)加索引时参照建索引的几大原则
7)观察结果，不符合预期继续从1开始分析
2.优化原则
- 查询时，能不要*就不用*，尽量写全字段名
- 大部分情况连接效率远大于子查询
- 多使用explain和profile分析查询语句
- 查看慢查询日志，找出执行时间长的sql语句优化
- 多表连接时，尽量小表驱动大表，即小表 join 大表
- 在千万级分页时使用limit
- 对于经常使用的查询，可以开启缓存
数据库表优化
- 表的字段尽可能用NOT NULL
- 字段长度固定的表查询会更快
- 把数据库的大表按时间或一些标志分成小表
- 将表拆分
3.配置慢查询
它有两种配置方式，一种是全局变量配置，一种是配置文件配置
（1）全局变量配置
--将 slow_query_log 全局变量设置为“ON”状态
set global slow_query_log='ON';
--设置慢查询日志存放的位置
set global slow_query_log_file='/usr/local/mysql/data/slow.log';
--查询超过1秒就记录
set global long_query_time=1;
（2）修改配置文件my.cnf(linux环境下)
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1
十一、变量的分类
1、概述
说明：变量由系统定义，不是用户定义，属于服务器层面。注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别。
使用步骤：
# 1、查看所有系统变量
show global|【session】variables;
# 2、查看满足条件的部分系统变量
show global|【session】 variables like '%char%';
# 3、查看指定的系统变量的值
select @@global|【session】.系统变量名;
# 4、为某个系统变量赋值
# 方式一：
set global|【session】系统变量名=值;
# 方式二：
set @@global|【session】.系统变量名=值;
2、全局变量
作用域：针对于所有会话（连接）有效，但不能跨重启。
一般修改系统全局变量都需要相关权限。
示例
# ①查看所有全局变量
SHOW GLOBAL VARIABLES;
# ②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
# ③查看指定的系统变量的值
SELECT @@global.autocommit;
# ④为某个系统变量赋值
# 方式一
SET @@global.autocommit=0;
# 方式二
SET GLOBAL autocommit=1;
3、会话变量
作用域：针对于当前会话（连接）有效。
# ①查看所有会话变量
SHOW SESSION VARIABLES;
# ②查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
# ③查看指定的会话变量的值
#方式1
SELECT @@autocommit;
#方式2
SELECT @@session.tx_isolation;
# ④为某个会话变量赋值
#方式1
SET @@session.tx_isolation='read-uncommitted';
#方式2
SET SESSION tx_isolation='read-committed';2、自定义变量：
用户变量
①声明并初始化(三种方式） SET @变量名=值; SET @变量名:=值; SELECT @变量名:=值; # SELECT只能用:=
局部变量
# 和上面用户变量不同的是: 局部变量只能申明在bengin和end内 # ①声明 DECLARE 变量名 类型; DECLARE 变量名 类型 【DEFAULT 值】;
十二、Mysql几种约束列出来：
主键约束 外键约束 唯一性约束 非空约束 默认值约束 自增约束
查看约束
SHOW INDEX FROM 表名;
单独建约束
上面的约束是建表时候建的，下面是在表建好后独立添加。
-- 1.添加非空约束
ALTER TABLE student MODIFY COLUMN id INT auto_increment;
-- 2.添加默认约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 INT DEFAULT 16;
-- 3、主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
-- 4、唯一约束
ALTER TABLE 表名 ADD UNIQUE(字段名); #字段名可以多个,多个用逗号隔开
-- 5、主键自增
# 注意自增一张表只有一个,同时只能在主键上
ALTER TABLE 表名 MODIFY COLUMN 字段名 INT auto_increment;删除约束
-- 1、删除非空约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 VARCHAR(20) NULL;
-- 2、删除主键
-- 这里需要注意如果主键设置为自增的情况下是不能删除成功的,因为自增只能设置在主键上，你删主键自增还存在当然不行。
ALTER TABLE 表名 DROP PRIMARY KEY;
-- 3、删除唯一
ALTER TABLE 表名 DROP INDEX 字段名;十三、解决MySQL死锁问题
1、查看正在进行中的事务
SELECT * FROM information_schema.INNODB_TRX
2、查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3、查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
4、查询是否锁表
SHOW OPEN TABLES where In_use > 0;
在发生死锁时，这几种方式都可以查询到和当前死锁相关的信息。
5、查看最近死锁的日志
show engine innodb status
当前自己的数据版本是8.0.22
mysql> select @@version;数据库隔离级别(默认隔离级别)
mysql> select @@transaction_isolation;自动提交关闭
mysql> select @@autocommit;查看分析死锁日志
可以用 show engine innodb status，查看最近一次死锁日志哈，执行后，死锁日志如下(只展示部分日志)：
实际开发中如何尽量避免死锁发生
1）不同的应用访问同一组表时，应尽量约定以相同的顺序访问各表。对一个表而言，应尽量以固定的顺序存取表中的行。这点真的很重要,它可以明显的减少死锁的发生。
举例：好比有a,b两张表，如果事务1先a后b,事务2先b后a,那就可能存在相互等待产生死锁。那如果事务1和事务2都先a后b，那事务1先拿到a的锁，事务2再去拿a的锁，如果
锁冲突那就会等待事务1释放锁，那自然事务2就不会拿到b的锁，那就不会堵塞事务1拿到b的锁，这样就避免死锁了。
2）在主键等值更新的时候，尽量先查询看数据库中有没有满足条件的数据，如果不存在就不用更新，存在才更新。为什么要这么做呢，因为如果去更新一条数据库不存在的数据，
一样会产生间隙锁。
举例：如果表中只有id=1和id=5的数据，那么如果你更新id=3的sql，因为这条记录表中不存在，那就会产生一个(1,5)的间隙锁，但其实这个锁就是多余的，因为你去更新一个
数据都不存在的数据没有任何意义。
3）尽量使用主键更新数据,因为主键是唯一索引，在等值查询能查到数据的情况下只会产生行锁，不会产生间隙锁，这样产生死锁的概率就减少了。当然如果是范围查询，
一样会产生间隙锁。
4）避免长事务，小事务发送锁冲突的几率也小。这点应该很好理解。
5）在允许幻读和不可重复度的情况下，尽量使用RC的隔离级别，避免gap lock造成的死锁，因为产生死锁经常都跟间隙锁有关，间隙锁的存在本身也是在RR隔离级别来
解决幻读的一种措施。
数据库备份
9.1 为什么要备份
- 保证重要的数据不丢失
- 数据转移
9.2 MySQL数据库备份的方式
- 直接拷贝物理文件
- 在可视化界面手动导出
- 使用命令行导出 mysqldump 命令行使用
-- 使用命令行导出
-- mysqldump -h 主机 -u 用户名 -p 密码 数据库 [表名...] > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
-- 导入
-- 登录的情况下，切换到只当数据库
-- source 备份的文件
source D:/a.sql
-- mysql命令
mysql -u用户名 -p密码 库名<备份的文件十四、MVCC原理详解
多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。
MVCC只在已提交读（Read Committed）和可重复读（Repeatable Read）两个隔离级别下工作，其他两个隔离级别和MVCC是不兼容的。因为未提交读，总数读取最新的数据行，而不是读取符合当前事务版本的数据行。而串行化（Serializable）则会对读的所有数据多加锁。
MVCC的实现原理主要是依赖每一行记录中两个隐藏字段，undo log，ReadView
2、隐藏字段
对于InnoDB存储引擎，每一行记录都有两个隐藏列trx_id、roll_pointer,如果数据表中存在主键或者非NULL的UNIQUE键时不会创建row_id,否则InnoDB会自动生成单调递增的隐藏主键row_id。
| 列名 | 是否必须 | 描述 |
|---|---|---|
| row_id | 否 | 单调递增的行ID，不是必需的，占用6个字节。 这个跟MVCC关系不大 |
| trx_id | 是 | 记录操作该行数据事务的事务ID |
| roll_pointer | 是 | 回滚指针，指向当前记录行的undo log信息 |
undo log分为如下两类：
1）insert undo log : 事务对insert新记录时产生的undo log, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
2）update undo log : 事务对记录进行delete和update操作时产生的undo log，不仅在事务回滚时需要，快照读也需要，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被删除。
ReadView 保存了不应该让这个事务看到的其他事务 ID 列表。
ReadView是如何保证可见性判断的呢？我们先看看 ReadView 的几个重要属性
-
trx_ids: 当前系统中那些活跃(未提交)的读写事务ID, 它数据结构为一个List。(
重点注意:这里的trx_ids中的活跃事务，不包括当前事务自己和已提交的事务，这点非常重要) -
low_limit_id: 目前出现过的最大的事务ID+1，即下一个将被分配的事务ID。
-
up_limit_id: 活跃事务列表trx_ids中最小的事务ID，如果trx_ids为空，则up_limit_id 为 low_limit_id。
-
creator_trx_id: 表示生成该 ReadView 的事务的 事务id
十五、主从复制原理
主从复制的原理
上面主要分成了三步，下面会详细说明。
(1) Master的更新事件(update、insert、delete)会按照顺序写入bin-log中。当Slave连接到Master的后,Master机器会为Slave开启
binlog dump线程,该线程会去读取bin-log日志
(2) Slave连接到Master后,Slave库有一个I/O线程 通过请求binlog dump thread读取bin-log日志,然后写入从库的relay log日志中。
(3) Slave还有一个 SQL线程,实时监控 relay-log日志内容是否有更新,解析文件中的SQL语句,在Slave数据库中去执行。
总结
(1) 既然是要把事件记录到bin-log日志，那么对于Master就必须开启bin-log功能。
(2) 整个Mysql主从复制一共开启了3个线程。Master开启 IO线程，Slave开启 IO线程 和 SQL线程。
(3) 这点也很重要那就是Master和Slave交互的时候，记住这里是Slave去请求Master,而不是Master主动推给Slave。Slave通过IO线程
连接Master后发起请求,Master服务器收到Slave IO线程发来的日志请求信息，io线程去将bin-log内容返回给slave IO线程。
主从复制的模式
(1)异步复制
MySQL主从同步 默认是异步复制的。就是上面三步中,只有第一步是同步的(也就是Mater写入bin log日志),就是主库写入binlog日志后即可成功返回客户端，无须等待binlog
（2)同步复制
对于同步复制而言，Master主机将事件发送给Slave主机后会触发一个等待，直到所有Slave节点（如果有多个Slave）返回数据复制成功的信息给Master。这种复制方式最安
全，但是同时，效率也是最差的。
（3)半同步复制
对于半同步复制而言，Master主机将事件发送给Slave主机后会触发一个等待，直到其中一个Slave节点（如果有多个Slave）返回数据复制成功的信息给Master。由此增强了
如何查看主从延迟时间
通过监控 show slave status 命令输出的Seconds_Behind_Master参数的值来判断：
mysql> show slave status\G;
// 状态一
Seconds_Behind_Master: NULL
// 状态二
Seconds_Behind_Master: 0
// 状态三
Seconds_Behind_Master: 79
Seconds_Behind_Master=0: 表示主从复制良好；
Seconds_Behind_Master=NULL: 表示io_thread或是sql_thread有任何一个发生故障；
Seconds_Behind_Master=79: 数字越大表示从库延迟越严重。
影响延迟因素
这里整理了影响主从复制延迟大致有以下几个原因：
1）主节点如果执行一个很大的事务，那么就会对主从延迟产生较大的影响
2）网络延迟，日志较大，slave数量过多
3）主上多线程写入，从节点只有单线程同步
4）机器性能问题，从节点是否使用了“烂机器”
5）锁冲突问题也可能导致从机的SQL线程执行慢
优化主从复制延迟
1）大事务：将大事务分为小事务，分批更新数据
2）减少Slave的数量，不要超过5个，减少单次事务的大小
3）MySQL 5.7之后，可以使用多线程复制，使用MGR复制架构
4）在磁盘、raid卡、调度策略有问题的情况下可能会出现单个IO延迟很高的情况，可用iostat命令查看DB数据盘的IO情况，再进一步判断
5）针对锁问题可以通过抓去processlist以及查看information_schema下面和锁以及事务相关的表来查看
主从数据库的好处和缺点
优点
容灾：做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
扩展：架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
锁表：读写分离，使数据库能支撑更大的并发。当复杂sql锁表时，不会影响查询业务
缺点
成本：主从数据库的存储是冗余存储的，同一份数据多分存储是对服务器资源的消耗来满足业务需求
主从不一致：采用异步赋值模式，会出现更新的数据在从库读取有延时问题
十六、性能优化全攻略
1、SQL语句优化
Sql语句优化工具
·慢日志
如果发现系统慢了，又说不清楚是哪里慢，那么就该用这个工具了。只需要为mysql配置参数，mysql会自己记录下来慢的sql语句。配置很简单，参数文件里配置：
slow_query_log=d:/slow.txt
long_query_time = 2
就可以在d:/slow.txt里找到执行时间超过2秒的语句了，根据这个文件定位问题吧。
Explain
文件、图片等大文件用文件系统存储，不用数据库
选择合适的引擎
Mysql提供了很多种引擎，我们用的最多的是myisam，innodb，memory这三类。
数据库参数配置
在数据库优化上有两个主要方面：
-
安全：数据可持续性。
-
性能：数据的高性能访问。
优化的范围有哪些
存储、主机和操作系统方面：
-
主机架构稳定性
-
I/O 规划及配置
-
Swap 交换分区
-
OS 内核参数和网络问题
应用程序方面：
-
应用程序稳定性
-
SQL 语句性能
-
串行访问资源
-
性能欠佳会话管理
-
这个应用适不适合用 MySQL
数据库优化方面：
-
内存
-
数据库结构（物理&逻辑）
-
实例配置
数据库优化维度有如下四个：
-
硬件
-
系统配置
-
数据库表结构
-
SQL 及索引
优化选择：
-
优化成本：硬件>系统配置>数据库表结构>SQL 及索引。
-
优化效果：硬件<系统配置<数据库表结构<SQL 及索引。
检查问题常用的 12 个工具：
-
MySQL
-
mysqladmin：MySQL 客户端，可进行管理操作
-
mysqlshow：功能强大的查看 shell 命令
-
SHOW [SESSION | GLOBAL] variables：查看数据库参数信息
-
SHOW [SESSION | GLOBAL] STATUS：查看数据库的状态信息
-
information_schema：获取元数据的方法
-
SHOW ENGINE INNODB STATUS：Innodb 引擎的所有状态
-
SHOW PROCESSLIST：查看当前所有连接的 session 状态
-
explain：获取查询语句的执行计划
-
show index：查看表的索引信息
-
slow-log：记录慢查询语句
-
mysqldumpslow：分析 slowlog 文件的工具
不常用但好用的 7 个工具：
-
Zabbix：监控主机、系统、数据库（部署 Zabbix 监控平台）
-
pt-query-digest：分析慢日志
-
MySQL slap：分析慢日志
-
sysbench：压力测试工具
-
MySQL profiling：统计数据库整体状态工具
-
Performance Schema：MySQL 性能状态统计的数据
-
workbench：管理、备份、监控、分析、优化工具（比较费资源）
系统层面
CPU方面：vmstat、sar top、htop、nmon、mpstat。
内存：free、ps-aux。
IO 设备（磁盘、网络）：iostat、ss、netstat、iptraf、iftop、lsof。
vmstat 命令说明：
-
Procs：r 显示有多少进程正在等待 CPU 时间。b 显示处于不可中断的休眠的进程数量。在等待 I/O。
-
Memory：swpd 显示被交换到磁盘的数据块的数量。未被使用的数据块，用户缓冲数据块，用于操作系统的数据块的数量。
-
Swap：操作系统每秒从磁盘上交换到内存和从内存交换到磁盘的数据块的数量。s1 和 s0 最好是 0。
-
IO：每秒从设备中读入 b1 的写入到设备 b0 的数据块的数量。反映了磁盘 I/O。
-
System：显示了每秒发生中断的数量（in）和上下文交换（cs）的数量。
-
CPU：显示用于运行用户代码，系统代码，空闲，等待 I/O 的 CPU 时间。
iostat 命令说明：
-
实例命令：iostat -dk 1 5；iostat -d -k -x 5 （查看设备使用率（%util）和响应时间（await））。
-
TPS：该设备每秒的传输次数。“一次传输”意思是“一次 I/O 请求”。多个逻辑请求可能会被合并为“一次 I/O 请求”。
-
iops ：硬件出厂的时候，厂家定义的一个每秒最大的 IO 次数。
-
"一次传输"请求的大小是未知的。
-
KB_read/s：每秒从设备（drive expressed）读取的数据量。
-
KB_wrtn/s：每秒向设备（drive expressed）写入的数据量。
-
KB_read：读取的总数据量。
-
KB_wrtn：写入的总数量数据量；这些单位都为 Kilobytes。
系统层面问题解决办法
你认为到底负载高好，还是低好呢？在实际的生产中，一般认为 CPU 只要不超过 90% 都没什么问题。当然不排除下面这些特殊情况。
CPU 负载高，IO 负载低：
-
内存不够
-
磁盘性能差
-
SQL 问题：去数据库层，进一步排查 SQL 问题
-
IO 出问题了（磁盘到临界了、raid 设计不好、raid 降级、锁、在单位时间内 TPS 过高）
-
TPS 过高：大量的小数据 IO、大量的全表扫描
IO 负载高，CPU 负载低：
-
大量小的 IO 写操作
-
autocommit，产生大量小 IO；IO/PS，磁盘的一个定值，硬件出厂的时候，厂家定义的一个每秒最大的 IO 次数。
-
大量大的 IO 写操作：SQL 问题的几率比较大
IO和 CPU 负载都很高：
-
硬件不够了或 SQL 存在问题
基础优化
优化思路
定位问题点吮吸：硬件>系统>应用>数据库>架构（高可用、读写分离、分库分表）。
处理方向：明确优化目标、性能和安全的折中、防患未然。
硬件优化
①主机方面
根据数据库类型，主机 CPU 选择、内存容量选择、磁盘选择：
-
平衡内存和磁盘资源
-
随机的 I/O 和顺序的 I/O
-
主机 RAID 卡的 BBU（Battery Backup Unit）关闭
②CPU 的选择
CPU 的两个关键因素：核数、主频。根据不同的业务类型进行选择：
-
CPU 密集型：计算比较多，OLTP 主频很高的 CPU、核数还要多。
-
IO 密集型：查询比较，OLAP 核数要多，主频不一定高的。
③内存的选择
OLAP 类型数据库，需要更多内存，和数据获取量级有关。OLTP 类型数据一般内存是 CPU 核心数量的 2 倍到 4 倍，没有最佳实践。
④存储方面
根据存储数据种类的不同，选择不同的存储设备，配置合理的 RAID 级别（raid5、raid10、热备盘）。
对于操作系统来讲，不需要太特殊的选择，最好做好冗余（raid1）（ssd、sas、sata）。
主机 raid 卡选择：
-
实现操作系统磁盘的冗余（raid1）
-
平衡内存和磁盘资源
-
随机的 I/O 和顺序的 I/O
-
主机 raid 卡的 BBU（Battery Backup Unit）要关闭
⑤网络设备方面
使用流量支持更高的网络设备（交换机、路由器、网线、网卡、HBA 卡）。注意：以上这些规划应该在初始设计系统时就应该考虑好。
服务器硬件优化
服务器硬件优化关键点：
-
物理状态灯
-
自带管理设备：远程控制卡（FENCE设备：ipmi ilo idarc）、开关机、硬件监控。
-
第三方的监控软件、设备（snmp、agent）对物理设施进行监控。
-
存储设备：自带的监控平台。EMC2（HP 收购了）、 日立（HDS）、IBM 低端 OEM HDS、高端存储是自己技术，华为存储。
系统优化
CPU：基本不需要调整，在硬件选择方面下功夫即可。
内存：基本不需要调整，在硬件选择方面下功夫即可。
SWAP：MySQL 尽量避免使用 Swap。阿里云的服务器中默认 swap 为 0。
IO ：raid、no lvm、ext4 或 xfs、ssd、IO 调度策略。
Swap 调整(不使用 swap 分区)：
十七、配置参数优化全攻略
1、max_connections
MySQL的最大连接数，如果服务器的并发连接请求量较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，MySQL回味每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
数值过小经常会出现ERROR 1040：Too mant connetcions错误，可以通过mysql>show status like ‘connections'；通配符来查看当前状态的连接数量（试图连接到MySQL（不管是否连接成功）的连接数），以定夺该值的大小。
show variadles like ‘max_connections'最大连接数
show variables like ‘max_used_connection'相应连接数
max_used_connection/max_connections*100%（理想值约等于85%）
如果max_used_connections和max_connections相同，那么就是max_connections值设置过低或者超过服务器的负载上限了，低于10%则设置过大了。
2、back_log
MySQL能够暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，他就会起作用。如果MySQL的连接数据达到max_connections时，新的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈数量即back_log，如果等待连接的数量超过back_log，将不被接受连接资源。
3、wait_timeout和interative_timeout
wait_timeout：指的是MySQL再关闭一个非交互的连接之前所需要等待的秒数。
interative_timeout：指的是关闭一个交互的连接之前所需要等待的秒数。
对性能的影响
wait_timeout
（1）如果设置太小，那么连接关闭的很快，从而使一些持久的连接不起作用
（2）如果设置太大容易造成连接打开时间过长，在show processlist时，能够看到太多的sleep状态的连接，从而造成too many connections错误。
（3）一般希望wait_timeuot尽可能的低
interative_timeout的设置将对你的web application没有多大的影响
2）缓冲区变量
全局缓冲
4、key_buffer_size
key_buffer_size指定索引缓冲区的大小，他决定索引的处理速度，尤其是索引读的速度。通过检查状态值 key_read_requests和key_reads，可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低，至少是1：100，1：1000更好（上述状态值可以使用show status like ‘key_read%'获得）
未命中缓存的概率：
key_cache_miss_rate = key_reads/key_read_requests*100%
key_buffer_size只对MAISAM表起作用。
如何调整key_buffer_size的值
默认的配置数时8388608（8M），主机有4G内存可以调优值为268435456（256M）
5、query_cache_size（查询缓存简称QC)
使用查询缓存，MySQL将查询结果存放在缓冲区中，今后对同样的select语句（区分大小写），将直接从缓冲区中读取结果。
一个SQL查询如果以select开头，那么MySQL服务器将尝试对其使用查询缓存。
注：两个SQL语句，只要相差哪怕是一个字符（例如 大小写不一样：多一个空格等），那么两个SQL将使用不同的cache
通过 show ststus like ‘Qcache%' 可以知道query_cache_size的设置是否合理
Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示过大，则说明Query Cache中的内存碎片较多了。
注：当一个表被更新后，和他相关的cache block将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用 flush query cache语句来清空free blocks。
Qcache_free_memory:Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察当前系统中的Query Cache内存大小是否足够，是需要增多还是过多了。
Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询能缓存的效果。数字越大缓存效果越理想。
Qcache_inserts：表示多少次未命中而插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert带查询缓存中。这样的情况次数越多，表示查询缓存 应用到的比较少，效果也就不理想。
Qcache_lowmen_prunes：多少条Query因为内存不足而被清除出Query Cache，通过Qcache_lowmem_prunes和Qcache_free_memory 相互结合，能够更清楚的了解到我们系统中Query Cache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query被换出。这个数字最好是长时间来看，如果这个数字在不断增长，就表示可能碎片化非常严重，或者内存很少。
Qcache_queries_in_cache：当前Query Cache 中cache的Query数量
Qcache_total_blocks：当前Query Cache中block的数量
查询服务器关于query_cache的配置
各字段的解释：
query_cache_limit：超出此大小的查询将不被缓存
query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄双刃剑，默认是 4KB ，设置值大对大数据查询有好处，但是如果你查询的都是小数据查询，就容易造成内存碎片和浪费。
query_cache_size：查询缓存大小（注：QC存储的单位最小是1024byte，所以如果你设定的一个不是1024的倍数的值。这个值会被四舍五入到最接近当前值的等于1024的倍数的值。）
query_cache_type：缓存类型，决定缓存什么样子的查询，注意这个值不能随便设置必须设置为数字，可选值以及说明如下：
0：OFF 相当于禁用了
1：ON 将缓存所有结果，除非你的select语句使用了SQL_NO_CACHE禁用了查询缓存
2：DENAND 则只缓存select语句中通过SQL_CACHE指定需要缓存的查询。
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成在读表获取结果。
查询缓存碎片率：Qcache_free_block/Qcache_total_block*100%
如果查询缓存碎片率超过20%，可以用flush query cache整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
查询缓存利用率：（query_cache_size-Qcache_free_memory)/query_cache_size*100%
查询缓存利用率在25%以下的话说明query_cache_size设置过大，可以适当减小：查询缓存利用率在80%以上而且Qcache_lowmem_prunes>50
的话说明query_cache_size可能有点小，要不就是碎片太多
查询缓存命中率：Qcache_hits/(Qcache_hits+Qcache_inserts)*100%
Query Cache的限制
a）所有子查询中的外部查询SQL 不能被Cache：
b）在p'rocedure，function以及trigger中的Query不能被Cache
c）包含其他很多每次执行可能得到不一样的结果的函数的Query不能被Cache
6、max_connect_errors：
是一个MySQL中与安全有关的计数器值，他负责阻止过多尝试失败的客户端以防止暴力破解密码的情况，当超过指定次数，MySQL服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hotos命令清空此host的相关信息。（与性能并无太大的关系）
7、sort_buffer_size：
每个需要排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY 或 GROUP BY操作
sort_buffer_size是一个connection级的参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。
sort_buffer_size：并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统的内存资源。例如：500个连接将会消耗500*sort_buffer_size(2M)=1G
8、max_allowed_packet=32M
根据配置文件限制server接受的数据包大小。
9、join_buffer_size=2M
用于表示关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
10、thread_cache_size=300
服务器线程缓存，这个值表示可以重新利用保存在缓存中的线程数量，当断开连接时，那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁（前提时缓存数未达上限），如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，这个线程将被重新请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能，通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。
设置规则如下：1G内存配置为8，2G内存为16.服务器处理此客户的线程将会缓存起来以响应下一个客户而不是被销毁（前提是缓存数未到达上限）
Threads_cached：代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected：代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created：代表最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，说明MySQL服务器一直在创建线程，这也比较消耗资源，可以适当增加配置文件中thread_cache_size值
Threads_running：代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。
3）配置Innodb的几个变量
11、innodb_buffer_pool_size
对于innodb表来说，innodb_buffer_pool_size的作用相当于key_buffer_size对于MyISAM表的作用一样。Innodb使用该参数指定大小的内存来缓冲数据和索引。最大可以把该值设置成物理内存的80%。
12、innodb_flush_log_at_trx_commit
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0，1，2.
实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要两秒，设置为0时只需要一秒，设置为1时，则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅度提高速度。
13、innodb_thread_concurrency=0
此参数用来设置innodb线程的并发数，默认值为0表示不被限制，若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍。
14、innodb_log_buffer_size
此参数确定日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。
15、innodb_log_file_size=50M
此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能。
16、innodb_log_files_in_group=3
为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3
17、read_buffer_size=1M
MySQL 读入缓冲区大小。对表进行顺序扫描的请求将分配到一个读入缓冲区MySQL会为他分配一段内存缓冲区
18、read_rnd_buffer_size=16M
MySQL 的随机读（查询操作）缓冲区大小。当按任意顺序读取行时（例如，按照排序顺序），将分配到一个随机都缓冲区。进行排序查询时，MySQL会首先扫描一遍该缓冲区，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但是MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存消耗过大。
注：顺序读是根据索引的叶节点数据就能顺序的读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找侍其巷进行数据，而辅助索引和主键所在的数据端不同，因此访问方式是随机的。
19、bulk_insert_buffer_size=64M
批量插入数据缓存大小，可以有效的提高插入效率，默认为8M
20、binary log
binlog_cache_size=2M //为每个session分配的内存，在事务过程中用来存储二进制日志的缓存，提高记录bin-log的效率。
max_binlog_cache_size=8M //表示的是binlog能够使用的最大cache内存大小
max_binlog_size=512M //指定binlog日志文件的大小。不能将变量设置为大于1G或小于4096字节。默认值为1G.在导入大容量的sql文件时，建议关闭，sql_log_bin，否则硬盘扛不住，而且建议定期做删除。
expire_logs_days=7 //定义了mysql清除过期日志的时间
十八、MyISAM与InnoDB 的区别
InnoDB：
- 支持事务
- 行锁
- 读操作无锁
- 4种隔离级别，默认为repeatable
- 自适应hash索引
- 每张表的存储都是按主键的顺序记性存放
- 支持全文索引（InnoDB1.2.x - mysql5.6）
- 支持MVCC（多版本并发控制）实现高并发
MyISAM：
- 不支持事务
- 表锁
- 支持全文索引
区别：
1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
3.支持特性的图
MySQL常用存储引擎介绍
1.InnoDB 引擎(MySQL5.5以后默认使用)
MySQL 5.5 及以后版本中的默认存储引擎，他的优点如下：
- 灾难恢复性好
- 支持事务
- 使用行级锁
- 支持外键关联
- 支持热备份
- 对于InnoDB引擎中的表，其数据的物理组织形式是簇表（Cluster Table），主键索引和数据是在一起的，数据按主键的顺序物理分布
- 实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引以加快数据的获取
- 支持热备份
2.MyISAM引擎
特性如下：
- 不支持事务
- 使用表级锁，并发性差
- 主机宕机后，MyISAM表易损坏，灾难恢复性不佳
- 可以配合锁，实现操作系统下的复制备份、迁移
- 只缓存索引，数据的缓存是利用操作系统缓冲区来实现的。可能引发过多的系统调用且效率不佳
- 数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能
3.MEMORY 存储引擎
提供内存表，也不支持事务和外键。显著提高访问数据的速度，可用于缓存会频繁访问的、可以重构的数据、计算结果、统计值、中间结果。
缺点如下：
- 使用表级锁，虽然内存访问快，但如果频繁的读写，表级锁会成为瓶颈
- 只支持固定大小的行。Varchar类型的字段会存储为固定长度的Char类型，浪费空间
- 不支持TEXT、BLOB字段。当有些查询需要使用到临时表（使用的也是MEMORY存储引擎）时，如果表中有TEXT、BLOB字段，那么会转换为基于磁盘的MyISAM表，严重降低性能
- 由于内存资源成本昂贵，一般不建议设置过大的内存表，如果内存表满了，可通过清除数据或调整内存表参数来避免报错
- 服务器重启后数据会丢失，复制维护时需要小心
MySQL存储引擎MyISAM与InnoDB如何选择
两种存储引擎的大致区别表现在：
1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
3）InnoDB支持外键，MyISAM不支持
4）从MySQL5.5.5以后，InnoDB是默认引擎
5）InnoDB不支持FULLTEXT类型的索引
6）InnoDB中不保存表的行数，如select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表。
7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。
8）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。
9）InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’
有人说MYISAM只能用于小型应用，其实这只是一种偏见。
如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，读写分离，而不是单纯地依赖存储引擎。
现在一般都是选用InnoDB了，主要是MyISAM的全表锁，读写串行问题，并发效率锁表，效率低，MyISAM对于读写密集型应用一般是不会去选用的。
innoDB与MyISAM
1、InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2、InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3、InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
4、MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
5、InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；
6、Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了
MyISAM表格可以被压缩后进行查询操作
7、InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。
InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有
Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
8、Innodb：frm是表定义文件，ibd是数据文件
Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
总之：
1.MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。
2.MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。
十九、一级缓存和二级缓存的区别：
主要的不同是它们的作用范围不同。
一级缓存是session级别的。
也就是只有在同一个session里缓存才起作用，当这个session关闭后这个缓存就不存在了。
而二级缓存是sessionFactory级别的。
其缓存对同一个sessionFactory生产出来的session都有效，二级缓存我们通常使用其他的一些开源组件，比如hibernate经常使用的就是ECache，这个缓存在整个应用服务器中都会有效的。
首先要明白缓存是干什么的，缓存就是要将一些经常使用的数据缓存到内存或者各种储存介质中，当再次使用时可以不用去数据库中查询，减少与数据库的交互，提高性能。
再说明一级与二级缓存的作用：一级缓存是Session级别的，也就是说在一个事务中才会启作用。比如在一个事务中同时查询同一个对象，则不会两次去数据库中查询。
而二级缓存是sessionFactory级别的，不同的事务之间是可以共享的，有些权限，当用户首次登陆后便将关联的权限放到二级缓存中，这样每次需要权限时就无需再查询数据库了。
mybaits提供一级缓存，和二级缓存。
一级缓存（默认开启）：
SqlSession级的缓存：在同一个SqlSession 查询同一个数据，不需要再通过数据库查询
二级缓存：
SqlSessionFactory级的缓存：在所有的SqlSession 查询同一个数据，不需要再通过数据库查询 在mybatis的mapper.xml文件中加入标签：
利用mybatis自身本地缓存结合redis实现分布式缓存
mybatis中应用二级缓存默认PepreCache SqlSessionFactory级别的缓存 所有SqlSession会话共享 如何开启（二级缓存） ---- 本地缓存 默认是使用了
mybatis整合分布式缓存ehcache
整合方法：mybatis提供了一个cache接口，如果要实现自己的缓存逻辑，实现cache接口开发即可。mybatis本身默认实现了一个，但是这个缓存的实现无法实现分布式缓存，所以我们要自己来实现。
MyBatis和Hibernate
比较这两款优秀的持久层框架的特点:
| 技术 | 优点 | 缺点 |
| jdbc | 简单,纯粹,一切均可见,最基础的一种技术 |
1:需要手动关闭连接 2:结果集不能自动映射为对象 3:SQL夹杂在代码中,耦合度高,导致硬编码内伤 4:实际开发中SQL经常随需求变动,导致频繁修改,不易维护 |
| jdbcTemplate |
简单、纯粹、自动会话管理、结果集映射 |
需要手动拼装SQL，SQL与Java代码混合在一起，长的SQL管理混乱 |
|
Hibernate JPA |
编程效率高，无需编写SQL。 较完善的二级缓存、自动防SQL注入 |
完全掌握的门槛高； |
| MyBatis |
学习成本低、可以进行更为细腻的SQL优化，减少查询字段、统一的SQL管理 |
需要手动编写维护SQL、表结构变更之后需要手动维护SQL与映射； |
MyBatis的定位
MyBatis专注于SQL本身,其为SQL映射,而非完整的SQL映射,它是一个半自动的ORM框架,需要自己编写SQL语句,这是其优点,也是缺点.
优点:SQL语句单独维护,便于SQL优化,便于发挥SQL的最大性能.
缺点:当数据库表和字段更改后,实体和数据库的映射关系需要手动维护,耗费时间长.
使用场景:是用于性能要求高,有大量的查询操作,适用于互联网项目,如:电商,O2O
myBatis的主要构件及其相互关系
从MyBatis代码实现的角度来看，MyBatis的主要的核心部件有以下几个：
- SqlSession 作为MyBatis工作的主要顶层API，表示和数据库交互的会话，完成必要数据库增删改查功能
- Executor MyBatis执行器，是MyBatis 调度的核心，负责SQL语句的生成和查询缓存的维护
- StatementHandler 封装了JDBC Statement操作，负责对JDBC statement 的操作，如设置参数、将Statement结果集转换成List集合。
- ParameterHandler 负责对用户传递的参数转换成JDBC Statement 所需要的参数，
- ResultSetHandler 负责将JDBC返回的ResultSet结果集对象转换成List类型的集合；
- TypeHandler 负责java数据类型和jdbc数据类型之间的映射和转换
- MappedStatement MappedStatement维护了一条<select|update|delete|insert>节点的封装，
- SqlSource 负责根据用户传递的parameterObject，动态地生成SQL语句，将信息封装到BoundSql对象中，并返回
- BoundSql 表示动态生成的SQL语句以及相应的参数信息
- Configuration MyBatis所有的配置信息都维持在Configuration对象之中。
互联网项目对持久层的需求:
1:对数据库的访问更加纯粹
2:尽可能不要使用数据库做运算
3:SQL语句尽可能命中索引(字段排序,查询字段,查询条件,尽可能命中索引)
