MySQL_基础知识

MySQL 基础

MySQL 基础知识

1. 视图：有查询结果生成的一张虚拟表。

   create view view_name as select ...

2. 存储过程：一组为了完成特定功能的 SQL 语句集。

   create procedure 存储过程名(参数,参数,…)
   begin
       //代码
   end

3. 触发器：有事件引发的操作。

   create trigger 触发器名 before|after 触发事件 on 表名 for each row 执行语句;

MySQL 的常用引擎

MySQL 的引擎包括：ISAM ，MyIASM ，Innodb ，HEAP，MERGE 等。

ISAM 执行读取操作的速度很快，且不占用大量的内存和存储资源，但不支持事务处理，并且不能够容错。

MyIASM 强调了快速读取数据的操作，但不能在表损坏之后恢复数据。

Innodb 适合处理多重并发，并且支持事务操作，且具有自动恢复数据，同时又支持外键。默认的搜索引擎。如果更新和查询都比较频繁，会存在锁表的可能。

查看 MySQL 提供了什么存储引擎：show engines;

查看当前表使用了什么引擎：show create table tb_content;

MyIASM 使用的索引是 B+ 树，但数据结构中存储的内容实际上是数据的地址，索引和数据是分开的。

Innodb 使用的索引也是 B+ 树，数据结构中存储的是实际的数据。

MySQL 中的事务

事务：一个最小的不可再分的工作单元。

事务具有四大特性：

1. 原子性：最小单元，不可再分。
2. 一致性：同时成功或者同时失败。
3. 隔离性：两个事务之间必须隔离。
4. 持久性：对数据的修改是永久的。

MySql 事务隔离级别

1. 读取未提交。事务中发生了修改，即使没有提交，其它事务也是可见的。—— > 会引起脏读。

   例如：A 原来的值是 50。修改成了 100。但还没提交，另外一个事务 B 看到了这个修改，但此刻 A 发生了回滚还是 50 。但 B 却读到了 100。

2. 读提交。对于一个事务从开始直到提交之前，所做的任务修改其它事务都不可见。—— > 会引起不可重复读。

   例如：A 原来是 50。提交修改 100 。另外一个事务 B 在这之前读取到 A 是 50。刚读完 A 就被修改成了 100。这个时候另外一个事务 C 在读 A 就是 100了。

3. 可重读。（MySql 默认隔离级别）不管事务有没有提交，每次读取的结果集都相同。—— > 会引起幻读。

4. 序列化。隔离级别最严格，代价最高。但能避免脏读，不可重复读，幻读。

MySQL 如何定位并优化查询慢的 SQL

1. 根据日志定位查询慢的 SQL。
2. 使用 Explain 等工具分析。
3. 修改 SQL 或者让 SQL 走索引以优化查询效率。

MySql 数据库优化

如何判断对应的字段就是索引？

1. Where 使用比较频繁的字段。
2. Order By 涉及到的列。

MySQL 索引是建立越多越好吗？

1. 数据量小的表不需要建立索引，建立索引会增加额外的索引开销。
2. 数据变更需要维护索引，因此更多的索引意味着更多的维护成本。
3. 更多的索引也意味着更多的空间。

MySQL 索引失效的条件

查看索引失效的语句：explain 执行该语句 key=null 。

1. 查询条件包含 or。
2. like 以 % 开头。
3. 组合索引，但不使用第一列索引。
4. 列类型是字符串，查询条件一定要用括号括起来，否则索引失效。
5. Where 查询条件里使用了不等号。
6. Where 查询条件里使用了函数。

MySQL 索引优化思路

1. Where 条件顺序，Oracle 采用自上而下的顺序解析，涉及到最大数量记录的条件写在末尾。
2. 先查小表，再联合大表，当使用一行数据的时候，使用 Limit。
3. 能用 Inner Join 就用 Inner Join （等值连接）。Left Join 左边结果集尽量少，有条件放左边。
4. 避免使用子查询，使用 Join 代替。
5. 避免使用 Having 。Having 是检索出所有记录后才对结果集进行过滤。On 是先把不符合的记录过滤后才统计。
6. 避免使用 Select * ,而用具体的列。
7. 使用关键字 Top，Distinct，关键字去重。
8. 使用临时表暂存中间结果。
9. 尽量避免在 Where 中使用 != 或 <>。
10. 尽量避免在 Where 中对索引进行 Is Null，Is Not Null。（可以将 Null 字段设置成默认值 0 ，改变查询条件 ——> 原因：索引不索引空值）

MySQL 最左匹配原则

mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配了。

1. 建立 a,b,c,d 的索引，a=3 and b=4 and c>5 and d=6 。（d 就用不到索引）但是如果 a=3 and b=4 and d=6 and c>5 。则 a,b,c,d 都会用到索引。
2. 建立 a,b,c,d 的索引，c>5 and b=4 and d=6 and a=3 也会全部用到 a,b,c,d 索引 。（查询优化器会重新编排）
3. 建立 a,b,c 索引 那么只要查询条件有 a 即可用到 a,b,c 索引（如 a,b,c a,b a,c a）， 没有 a 就用不到。
4. 索引 a 的字段类型是 int 。查询的时候 where a = ‘123’。会使用到索引。
5. 索引 a 的字段类型是 varchar 。查询的时候 where a = 123。不会使用到索引。

MySQL 如何保证原子性

想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用回滚日志中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

MySQL 中的锁

1. select * from … where … for update;
2. update … set …;

MySQL 中的事务

事务：一个最小的不可再分的工作单元。

事务具有四大特性：

1. 原子性：最小单元，不可再分。
2. 一致性：同时成功或者同时失败。
3. 隔离性：两个事务之间必须隔离。
4. 持久性：对数据的修改是永久的。

MySql 事务隔离级别

1. 读取未提交。事务中发生了修改，即使没有提交，其它事务也是可见的。—— > 会引起脏读。

   例如：A 原来的值是 50。修改成了 100。但还没提交，另外一个事务 B 看到了这个修改，但此刻 A 发生了回滚还是 50 。但 B 却读到了 100。

2. 读提交。对于一个事务从开始直到提交之前，所做的任务修改其它事务都不可见。—— > 会引起不可重复读。

   例如：A 原来是 50。提交修改 100 。另外一个事务 B 在这之前读取到 A 是 50。刚读完 A 就被修改成了 100。这个时候另外一个事务 C 在读 A 就是 100了。

3. 可重读。（MySql 默认隔离级别）不管事务有没有提交，每次读取的结果集都相同。—— > 会引起幻读。

4. 序列化。隔离级别最严格，代价最高。但能避免脏读，不可重复读，幻读。

MySQL 如何定位并优化查询慢的 SQL

1. 根据日志定位查询慢的 SQL。
2. 使用 Explain 等工具分析。
3. 修改 SQL 或者让 SQL 走索引以优化查询效率。

MySql 设计的时候需要注意的事项

设计表

1. 表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。
2. 尽量使用INT而非BIGINT，如果非负则加上UNSIGNED(这样数值容量会扩大一倍)，当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
3. 使用枚举或整数代替字符串类型。
4. 尽量使用TIMESTAMP而非DATETIME。
5. 单表不要有太多字段，建议在20以内。
6. 用整型来存IP。

索引

1. 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描。
2. 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描。
3. 值分布很***的字段不适合建索引，例如”性别”这种只有两三个值的字段。
4. 字符字段只建前缀索引。
5. 字符字段***不要做主键。
6. 不用外键，由程序保证约束。
7. 尽量不用UNIQUE，由程序保证约束。
8. 使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引。

SQL 的编写需要注意优化

1. 使用limit对查询结果的记录进行限定。
2. 避免select *，将需要查找的字段列出来。
3. 使用连接(join)来代替子查询。
4. 拆分大的delete或insert语句。
5. 可通过开启慢查询日志来找出较慢的SQL。
6. 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
7. sql语句尽可能简单：一条sql只能在一个cpu运算;大语句拆小语句，减少锁时间;一条大sql可以堵死整个库。
8. OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内。
9. 不用函数和触发器，在应用程序实现。
10. 避免%xxx式查询。
11. 少用JOIN。
12. 使用同类型进行比较，比如用’123’和’123’比，123和123比。
13. 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
14. 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5。
15. 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大。

分区

分区的好处是：

1. 可以让单表存储更多的数据
2. 分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
3. 部分查询能够从查询条件确定只落在少数分区上，速度会很快
4. 分区表的数据还可以分布在不同的物理设备上，从而搞笑利用多个硬件设备
5. 可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
6. 可以备份和恢复单个分区

分区的限制和缺点：

1. 一个表最多只能有1024个分区
2. 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
3. 分区表无法使用外键约束
4. NULL值会使分区过滤无效
5. 所有分区必须使用相同的存储引擎

分区的类型：

1. RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区
2. LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
3. HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
4. KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值
5. 具体关于mysql分区的概念请自行google或查询官方文档，我这里只是抛砖引玉了。