MySQL学习笔记

MySQL学习笔记

在我们的日常学习和开发中，数据库是必不可少的。虽然我们平时使用数据库很频繁，但是作为一个后端开发的人员讲，我们都是在做一些很粗浅的curd活（curd大家应该都懂），真正去了解数据库的，我相信不会太多。而今天，我们就要来弥补这个不足。

1.数据库范式

开发人员在设计数据库的时候，需要遵循不同的规范要求，这些规范要求用专业术语来讲，就是范式，范式一共有五种，以下列出：

• 第一范式（1NF）：数据库的每一列都不可再分，确保每一列的原子性。
  • 例子：用户表（user），里面包含用户ID，用户姓名，每一列都不可再单独拆分成另一个表
• 第二范式（2NF）：在满足1NF的基础上，要求表中的每一列都必须依赖于主键，说白了，就是保证每一列的唯一性。
  • 例子：订单表（orders）里面只能包含订单的信息，产品表（products）只能包含产品的信息，在同一张表中不能同时出现订单表（orders）和产品表（products）的信息
• 第三范式（3NF）：在满足2NF的基础上，表不包含其它表中已包含的非主关键字信息，说白了，就是保证数据库表的所以列不冗余
  • 例子：班级表（classes）里有主键班级ID，班级名称，而学生表（students）里有班级表（classes）的信息----班级ID和班级名称，班级名称明显是数据冗余了

2.数据库索引

索引是数据库里一个非常重要的知识，对数据库优化有着举足轻重的作用，索引是建立在表之上的一种存储机制，它需要额外的空间来存储和维护，其详细概念如下：

1.是一种快速查询表中内容的机制，类似于新华字典的目录

2.运用在表中某个些字段上，但存储时，独立于表之外

索引底层用的是B+树（二叉树的一种，实现可以参考这篇博文：MySQL索引背后的数据结构及算法原理），我们知道，普通数据库查询是进行全表查询，类似线性查找，而使用了索引之后，等于就是折半查找，在数据量非常的大的情况下，带来的性能提示超过上千倍！所以，用好了索引，你的数据库查询将变得十分迅速。

当然，万物有利有弊，索引的缺点也是存在的：

• 它会降低了插入、删除、修改等维护任务的速度(虽然索引可以提高查询速度，但是它们也会导致数据库系统更新数据的性能下降，因为大部分数据更新需要同时更新索引)
• 索引需要额外的空间进行存储和维护

了解以上这些，我们再来看看什么时候适合用索引呢？

• （1）表经常进行 SELECT 操作
• （2）表很大(记录超多)，记录内容分布范围很广
• （3）列名经常在 WHERE 子句或连接条件中出现
• 如果你的业务不包含以上情况，那请你不要使用索引

索引的类别：主键索引、唯一索引、普通索引、全文索引、组合索引

1. 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值

   ALTER TABLE table_name ADD PRIMARY KEY ( column )

2. 唯一索引：唯一索引，列值不允许有重复，可以为空

   ALTER TABLE table_name ADD UNIQUE ( column )

3. 普通索引：是最基本的索引，它没有任何限制

   ALTER TABLE table_name ADD INDEX index_name ( column )

4. 全文索引：用于检索文本信息

   ALTER TABLE table_name ADD FULLTEXT ( column)

5. 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

   ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

3.数据库引擎

常用的数据库引擎有两种：Innodb（也是最常用的）和Myisam，二者的区别在于：

• Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统，适合写密集数据库
• Myisam引擎不提供事务的支持，也不支持行级锁和外键，但是它性能好，适合读密集数据库

4.数据库调优

数据库调优是我们后端开发人员晋升高层次的必备技能，一般分为以下8点，常用的是前面5点：

1. 设计要合理，这是前期的工作，但也是非常重要的一步，一定不能忽略；
2. 添加索引，对那些经常需要查询的字段、表建立索引
3. 分库分表技术
   • 分库：将项目拆分成多个小项目，每个数据库也拆分成小数据库
   • 分表：一张200w数据的表，均匀地将数据分配的其它几张表里（这里要用到取模算法）
4. 读写分离
   • 所谓读写分离，就是搭建数据库集群，一个主机和多个备机，主机一般包含读/写两种权限，而备机只有读的权限，备机的作用有两个：1）当主机宕机时，备机挺身而出，坚强作战；2）帮助主机缓解压力，提高系统整体性能
   • 讲到读写分离，还要提到 主机和备机之间的同步问题：当主机进行写操作时，备机的数据怎么进行同步改变？它们之间会利用二进制SQL文件进行同步，速度非常之快而且支持重复试错机制，不用我们过多考虑这个同步问题
   • 再一个就是关于数据库中间件问题，客户端发起连接请求（读或者写），通过数据库中间件，将请求调配到主机或者备机（读就走备机，写就走主机）
5. SQL语句调优
   • 第一步要定位到慢SQL语句，判断是偶尔慢还是一直慢
   • 如果是偶尔慢的话，可能是其他原因，和你的SQL语句并无关系
   • 如果是一直慢的话，那就是SQL语句需要优化，这边可以参考以下博文：
     • MySQL SQL语句优化技巧
6. 存储过程
7. 在数据库配置文件my.ini里配置最大连接数
8. 服务器配置升级

5.总结

数据库这块基础知识要掌握牢固，特别那些原理性的更加要深入，初次之外，sql语句书写也要多练练（面试经常会考的，什么order、group by），最后推荐一些好博文

• 去 BAT 面试，总结了这 55 道 MySQL 面试题！
• 一个大牛的GitHub开源项目