MySQL学习笔记

一、前言

此文是对MySQL索引、性能优化、连接查询学习的记录

二、索引

学习资料：MySQL3：索引 - 五月的仓颉 - 博客园

什么是索引

索引是对数据库表中一列或者多列的值进行排序的一种结构，索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间越多，如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜索数据文件，而不必查看所有数据。

索引的含义和特点

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

索引是在存储引擎中实现的，因此，每一种存储引擎的索引不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的额限制，MySQL中索引的存储类型有两种：BTREE和HASH，具体和表的存储引擎相关；MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎可以支持HASH和BTREE缩影。

索引的主要优点有：

1、通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。

2、可以大大加快数据的查询速度，这也是创建索引最主要的原因

3、在实现数据的完整性方面，可以加速表和表之间的连接

4、在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序时间

增加索引也有很多不利的方面，比如：

1、创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

2、索引需要占用磁盘空间，除了数据表占空间之外，每一个索引还要占用一定的物理空间，如果有大量索引，索引文件可能比数据文件更快到达最大文件尺寸

3、当对表中数据进行增加、删除和修改的时候，索引也要动态维护，这样不降低了数据的维护速度

索引的分类

MySQL的索引可以分为以下几类：

1、普通索引和唯一索引

（1）普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值

（2）唯一索引，索引列的值必须唯一，但允许有空值，主键索引是一种特殊的唯一索引，不允许有空值

2、单列索引和组合索引

（1）单列索引即一个索引只包含单个列，一个表可以有多个单列索引

（2）组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用

3、全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建，MySQL中只有MyISAM存储引擎支持全文索引

索引的设计原则

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍，高效的索引对于获得良好的性能非常重要，设计索引时，应该考虑一下：

1、索引并非越多越好，一个表中有大量的索引，不仅占用磁盘空间，而且会影响insert、delete、update等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新

2、避免对经常更新的表设计过多的索引，并且索引中的列尽可能要少，而对经常用于查询的列应该创建索引，但要避免添加不必要的字段。

3、数量量小的表最好不要使用索引，由于数据量较少，查询花费的时候可能比遍历索引的时候还要短，索引可能不会产生优化效果

4、在条件表达式中经常用到的不同值较多的列上建立索引，在不同值较少的列上不要建立索引，比如性别字段只有男和女，就没必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度

5、当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。

6、在频繁排序或分组的列上建立

创建表的时候创建索引

CREATE TABLE table_name[col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL]
[INDEX|KEY]
[index_name](col_name[length])
[ASC|DESC]

解释一下：

1、UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引

2、INDEX和KEY为同义词，二者作用相同，用来指定创建索引

3、col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择

4、index_name为指定索引的名称，为可选参数，如果不指定则MySQL默认col_name为索引值

5、length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度

6、ASC或DESC指定升序或者降序的索引值存储

在已经存在的表上创建索引

1、使用ALTER TABLE语句创建索引

ALTER TABLE创建索引的基本语法为：

ALTER TABLE table_name ADD [UNIQUE|FUUTEXT|SPATIAL]
[INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

2、使用CREATE INDEX语句创建索引

CREATE INDEX语句可以在已经存在的表上添加索引，MySQL中CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...)[ASC|DESC]

删除索引

最后一项工作就是删除索引了，可以使用ALTER TABLE和DROP INDEX删除索引。

1、ALTER TABLE

ALTER TABLE的基本语法为：

ALTER TABLE table_name DROP INDEX index_name

2、DROP INDEX

DROP INDEX的基本语法为：

DROP INDEX index_name ON table_name

数据库优化

关于数据库的优化，在性能达不到要求的情况下，我大致给出以下几个方向：

（1）优化表结构，对常用字段和非常用的字段分开存储

（2）优化SQL，合理使用索引

（3）做数据库读写分离，减少IO压力，由于数据库对记录做了持久化并存储在磁盘上，对磁盘的I/O又是非常消耗性能的操作，因此读、写都在一个库中会大大增加I/O的压力

（4）尝试使用缓存，不要让数据都走数据库

（5）对业务做垂直拆分

（6）对表做水平拆分，这一步比较麻烦，要注意主键生成规则以及请求路由规则

三、性能优化

学习资料：MySQL5：性能优化 - 五月的仓颉 - 博客园

MySQL数据库优化是多方面的，有几条优化原则：

1、减少系统的瓶颈

2、减少资源的占用

3、增加系统的反应速度

优化查询

1、使用索引查询

使用索引查询有几种特殊情况，特别提一下：

（1）使用LIKE关键字的查询语句

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为"%"，索引不会起作用，只有"%"不在第一个位置，索引才会起作用

（2）使用多列索引的查询语句

MySQL可以为多个字段创建索引，一个索引可以包含16个字段，对于多列索引，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用

（3）使用OR关键字的查询语句

查询语句的查询条件中只有OR关键字，且OR前后两个条件都是索引时，查询才会使用索引，否则，查询将不使用索引

2、避免使用SELECT *命令

从表中读取的数据越多，查询会变得越慢，始终指定需要的列，是一个良好的习惯

3、永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID作为其主键，而且最好是一个INT型的，并设置上自动增加的AUTO_INCREMENT标识。就算有些VARCHAR字段类型的值，它永远不会重复，也不要设置为主键，使用VARCHAR类型来当主键会使性能下降

数据库结构优化

一个好的数据库设计方案对于数据库的性能常常会起到事半功倍的效果，合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面内容。

下面提供几条优化数据库结构的建议：

1、将字段很多的表分解为多个表

对于字段很多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢，通过分解字段，可以提高表的查询效率

2、增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率

3、优化插入记录的速度

插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等，根据这些情况，可以分别进行优化（以使用InnoDB的表为例）：

（1）禁用唯一性检查

插入数据时，MySQL会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度，为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕之后再开启：

SET UNIQUE_CHECKS = 0;

开启唯一性检查：

SET UNIQUE_CHECKS = 1;

（2）禁用外键检查

插入数据之前禁止对外键的检查，数据插入之后再恢复对外键的检查，禁用外键检查的语句如下：

SET foreign_key_checks = 0;

恢复对外键检查的语句为：

SET foreign_key_checks = 1;

（3）禁止自动提交

插入数据之前禁止事物的自动提交，数据导入完成之后，执行恢复自动提交操作。禁止自动提交的语句如下：

SET autocommit = 0;

恢复自动提交的语句为：

SET autocommit = 1;

四、连接查询

学习资料：MySQL8：连接查询 - 五月的仓颉 - 博客园

连接查询

连接是关系型数据库模型的主要特点。

连接查询是关系型数据库中最主要的查询，主要包括内连接、外连接等通过联结运算符可以实现多个表查询。

在关系型数据库管理系统中，表建立时各种数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中，当查询数据时通过连接操作查询出存放在多个表中的不同实体信息，当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。

内连接inner join

内连接（inner join）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。换句话说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。

左外连接left join

连接查询将查询多个表中相关联的行，内连接时返回查询结果集合中的仅仅是符合查询条件和连接条件的行。但有时候需要包含没有关联的行中的数据，即返回查询结果集合中的不仅仅包含符合的连接条件的行，而且还包含左表或右表中的所有数据行。外连接分为左外连接和右外连接，这里先看一下左外连接。

左外连接，返回的是左表中的所有记录以及由表中连接字段相等的记录。

右外连接right join

右外连接是左外连接的反向连接，将返回右表中的所有行，如果右表中的某行在左表中没有匹配的行，左表将返回空值。