1.索引

模拟八百万量级数据进行单表查询，大概需要 4~5 秒

使用索引后，相同的查询指令，只需要 0.03 秒 提高了两个数量级

原理

不加索引进行的是全表扫描

加索引形成了的数据结构，具体到MySQL的实现是B+树（InnoDB、MYISAM 两种引擎都是 B+ 树）

干货：mysql索引的数据结构

代价

1. 磁盘占用
2. 对DML（update delete insert）语句的效率影响（没有最好的技术，只有最合适的技术），实际开发中，90%的操作都是select。

类型

1. 主键索引：主键自动的为主索引（类型Primary key）
2. 唯一索引（UNIQUE）
3. 普通索引（INDEX）
4. 全文索引（FULLTEXT）【适用于MyISAM】：不适用mysql自带的全文索引，俄日时使用全文搜索 Solr 和 ElasticSearch（ES）

设立索引的原则

1. 作为查询条件被查询的比较频繁的字段适合创建索引
2. 唯一性太差的字段不适合单独创建索引，即使频繁的作为查询条件 举例： 为性别创建索引的作用不大，由分层查找体现出来的优势不明显 10w用户，5w男 5w女，优化空间不大
3. 更新频繁的字段不适合创建索引（索引数据结构维护的成本比较大）
4. 不会出现在 WHERE 子句中的字段不该创建索引

2.事务

事务用于保证数据的一致性，它由一组相关的 DML 语句组成，该组的 DML 语句要么全成功，要么全失败。

举例：转账需要使用事务处理，用以保证数据一致性

事务和锁

当执行事务时，MySQL会在表上加锁，防止其他用户修改表中的数据

史上最全的MySQL select加锁分析

start transaction       -- 或者 set aotucommit=off 开启一个事务
savepoint [保存点名]        -- 设置保存点
rollback to [保存点名]      -- 回滚事务
rollback                -- 回退全部事务
commit                  -- 提交事务，所有操作生效，不能回退

回退事务

保存点（savepoint），类似存档，保存点是事务中的点，用于取消部分事务，当结束事务时（commit），会自动删除该事务所定义的所有保存点，当执行回退事务时，通过指定保存点可以回退到指定的点。

提交事务

使用 commit 语句可以提交事务，当执行了commit语句后，会确认事物的变化，结束事务、删除保存点、释放锁、数据生效。当使用commit语句结束事务后。其他会话【其他链接】将可以看到事务变化后的新数据【所有数据正式生效】

使用细节

1. 如果不开始事务，默认情况下，DML操作是可以自动提交的
2. 如果开始一个事务，没有创建保存点，执行rollback，将会回退到事务开始
3. 可以在事务还没有提交时，船舰多个保存点，并决定回退到哪一个
4. MySQL的事务机制需要InnoDB的存储引擎才可以使用， MyISAM不好用

隔离级别

1. 多个连接开启个各自事务操作数据库中的数据时，数据库系统要负责隔离操作，以保证各个链接获取数据时的准确性
2. 如果不考虑隔离性，可能会造成以下问题
   1. 脏读（dirty read）：读取未提交的事务
   2. 不可重复读（nonrepeatable read）：前后多次读取，数据内容不一致
   3. 幻读（phantom read）：前后多次读取，数据总量不一致

快速理解脏读、不可重复赌读、幻读

MySQL隔离级别	脏读	不可重复读	幻读	加锁读
读未提交（Read uncommmited）	√	√	√	不加锁
读已提交（Read commited）	×	√	√	不加锁
可重复读（Repeatable read）	×	×	×（※※）	不加锁
可串行化（Serializable）	×	×	×	加锁

注释：

√ 可能出现 × 不会出现

MySQL为什么Repeatable Read不会出现幻读？

设置事务隔离级别

1. 查看当前会话隔离级别SELECT @tx_isolation;
2. 查看系统当前隔离级别SELECT @@global.tx_isolation;
3. 设置当前会话隔离级别SET SESSION TRANSACTION ISOLATION LEVEL Repeatable read;
4. 设置系统当前隔离级别SET GLOBAL TRANSACTION ISOLATION LEVEL Repeatable read;
5. MySQL 默认的事务隔离界别是 repeatable read，一般情况下，没有特殊要求，没有必要更改（可以符合大部分需求）
6. 全局修改：修改 my.ini 配置文件，在最后加上 [mysqld]transaction-isolation = REPEATABLE-READ 可选参数 [ READ-UNCOMMITED | READ-COMMITED | REPEATABLE-READ | SERIALIZABLE ]

事务ACID特性

原子性【Atomicity】：事务是一个不可分割的工作单位，一个事务中的操作要么全发生，要么要全回滚

一致性【Consistentency】：事务必须使数据库从一个一致性状态变换到另一个一致性状态，事务修改前后的数据总体保证一致

隔离性【Isolation】：对于多个事务同时进行时，事务与事务之间是相互隔离的，不会造成影响，要看隔离级别而定

持久性【Durability】：事务一旦被提交，他对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

3.表类型和存储引擎

基本介绍

MySQL 的表类型由存储引擎（Storage Engines）决定，主要包括MyISAM、InnoDB、Memory等

MySQL 数据表主要支持六种类型，分别是：SCSBV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoDB

这六种又分为两类，一类是 “ 事务安全型 ” （transaction-safe）比如：InnoDB；

其余属于第二类 “ 非事务安全型 ” （non-transaction-safe）

主要的存储引擎特点

特点	InnoDB	MyISAM	Memery（使用内存）	Archive
批量插入的速度	低	高	高	极高
事务安全	支持
全文索引		支持
锁机制	行锁	表锁	表锁	行锁
存储限制	64TB	无	有（视内存而定）	无
B树索引	支持	支持	支持
哈希索引	支持		支持
集群索引	支持
数据缓存	支持		支持
索引缓存	支持	支持	支持
数据可压缩		支持		支持
空间使用	高	低	N / A	非常低
内存使用	高	低	中等	低
支持外键	支持

细节说明

0. MySISAM 不支持事务、不支持外键、但是访问速度快，对事物完整性没有要求
1. InnoDB 提供了具有提交、回滚和崩溃恢复能力的食物安全，但是相比MyISAM，InnoDB写的处理效率差一些，并且占用更多磁盘空间以保留数据和索引。
2. Memory 使用存在内存中的内容来创建表。每个Memory表只实际对应一个磁盘文件。Memory类型的表访问非常的快，因为数据都是放在内存中的，并且默认使用Hash索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉，表的结构还在。

如何选择存储引擎

0. 如果你的应用不需要事务，处理的只是基本的 CRUD 操作，那么MyISAM是不二选择，速度快。
1. 如果需要支持事务，选择InnoDB。
2. Memory 存储引擎就是将数据存储在内存中，由于没有 I/O 的等待，速度极快，但是由于是内存存储引擎，所做的任何修改在服务器重启之后都将消失。
3. Memory常用于存储用户的在线状态

修改存储引擎

ALTER TABLE `table_name` ENGINE = 存储引擎;

4.视图

基本介绍

形成一张表的简化表（不同的用户可以操作数据的权限不同，需要显示不同的范围）

实质是一种虚拟表，与基表形成映射，可以对基表的数据操作。

基本使用

1. create view 视图名 as select语句;

CREATE VIEW emp_view01
    AS
    SELECT empno, ename, job, deptno FROM emp;

2. alter view 视图名 as select 语句
3. show create view 视图名
4. drop view 视图名1， 视图名2

使用细节

1. 使用驶入后，到数据库去看，对应视图只有一个视图结构文件（形式：视图名.frm）
2. 视图数据变化会影响到基表，击败哦的数据变化会影响到视图
3. 视图可以再使用视图，数据仍然来自基表

最佳实现

1. 安全：一些数据表有着重要的信息。有些字段都是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能看保密的字段。
2. 性能：关系数据库的数据常常会分表存储，使用外键简历这些表之间的关系。这时，数据库查询通常会用到连接（JOIN）。这样做不但麻烦，效率也相对较低，如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用 JOIN 查询数据。
3. 灵活：如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃，然而，很多应用都是基于这张表，不宜修改。这是就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以稍作很多改动，也达到了升级数据表的目的。

5.MySQL用户管理

作用

1. 实际开发中，针对不同开发者，对数据存在着不同的操作权限（root权限太高了），因此，应该有用户管理的概念。
2. mysql中的用户，存储在系统数据库 mysql 的 user 表中
3. user 表 字段说明
   1. host：允许登录的 “位置”，localhost 表示该用户只允许本机登录，也可以指定ip地址，比如：192.168.1.100
   2. user：用户名
   3. authentication_string：密码，是mysql通过 password() 函数加密之后的密码

常见操作

1. 创建用户，同时指定密码

create user '用户名' @'允许登陆位置' identified by '密码';

2. 删除用户

drop user '用户名' @'允许登录位置';

3. 修改用户密码

# 修改自己密码
set password = password('密码');
​
# 修改他人密码（需要有修改用户密码的权限）
set password for '用户名' @'登陆位置' = password('密码');

4. 给用户授权

# 基本语法
grant 权限列表 on 库，对象名 to '用户名' @'登录位置' [identified by '密码'];
# 说明
1. 权限列表
grant select on.....
grant select, delete, create on .....
grant all
​
2. 特别说明
*.*：代表本系统中所有数据库的所有对象（表、视图、存储过程）
库.*：代表某个数据库中所有数据对象（表、视图、存储过程）
​
3.identified by可以省略，也可以写出
(1)如果用户存在，就是修改该用户的密码。
(2)如果该用户不存在，就是创建该用户。

• 权限

5. 回收用户授权

# 基本语法
revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置'

6. 权限生效指令

FLUSH PRIVILEGES;

细节

1. 创建用户的时候，如果不指定 Host， 则为%，%表示所有 IP 都有连接权限

create user xxx;

2. 也可以这样指定

create user ‘xxx’@‘129.168.1.%’ 表示用户再192.168.1.* 的 IP 可以登录mysql

3. 在删除用户的时候，如果host不是%，需要明确指定 ‘用户’@‘host值’