MySQL介绍、SQL语句概括、索引类型、调优

（一）MySQL介绍

MySQL 是一种关系型数据库管理系统（RDBMS），是一款广泛应用于 Web 应用开发的开源数据库。它是由瑞典 MySQL AB 公司开发的，现在隶属于 Oracle 公司。MySQL 支持多种操作系统，包括 Windows、Linux、macOS 等，同时还支持多种编程语言接口，包括 C、C++、Java、Python 等。

MySQL 具有以下优势：

1. 开源免费：MySQL 是一款开源软件，不仅可以免费使用，而且用户可以自由修改和分发源代码，方便二次开发和定制。

2. 高效性能：MySQL 采用了高效的查询算法和存储引擎，能够快速地处理大量数据，并支持高并发访问。

3. 可扩展性和灵活性：MySQL 支持插件式架构和多种存储引擎，具有良好的可扩展性和灵活性，能够根据不同需求进行定制和优化。

4. 安全性：MySQL 提供了多种安全机制，包括访问控制、密码加密、数据备份等，可以有效保障数据的安全性和稳定性。

5. 开放标准：MySQL 严格遵循 SQL 标准，保证了与其他数据库系统的兼容性，方便数据的迁移和交互。

总之，MySQL 是一款成熟、稳定、高效、灵活、安全的数据库系统，应用广泛，是 Web 应用开发中不可缺少的基础设施之一。

（二）MySQL常用语句

1. 创建表

   CREATE TABLE table_name (
      column1 datatype,
      column2 datatype,
      column3 datatype,
      ...
   );

   2.插入数据

   INSERT INTO table_name (column1, column2, column3, ...)
   VALUES (value1, value2, value3, ...);

   3.查询数据

   SELECT column1, column2, ...
   FROM table_name
   WHERE condition;

   4.更新数据

   UPDATE table_name
   SET column1 = value1, column2 = value2, ...
   WHERE condition;

   5.删除数据

   DELETE FROM table_name
   WHERE condition;

   6.创建索引

   CREATE INDEX index_name
   ON table_name (column1, column2, ...);

   7.删除索引

   DROP INDEX index_name
   ON table_name;

   8.查看表结构

   DESC table_name;

   9.修改表结构

   ALTER TABLE table_name
   ADD column_name datatype;
   
   ALTER TABLE table_name
   MODIFY column_name datatype;
   
   ALTER TABLE table_name
   DROP COLUMN column_name;

   10.外键约束

   CREATE TABLE table_name (
      column1 INT NOT NULL,
      column2 VARCHAR(50) NOT NULL,
      column3 INT NOT NULL,
      PRIMARY KEY (column1),
      FOREIGN KEY (column2) REFERENCES other_table(column_name),
      FOREIGN KEY (column3) REFERENCES other_table(column_name) ON DELETE CASCADE
   );

   11.内置函数

   SELECT COUNT(*), SUM(column_name), AVG(column_name), MAX(column_name), MIN(column_name)
   FROM table_name;

   12.分组和聚合

   SELECT column1, COUNT(*), SUM(column2), AVG(column2), MAX(column2), MIN(column2)
   FROM table_name
   GROUP BY column1;

   13.连接查询

   SELECT table1.column1, table2.column2
   FROM table1
   JOIN table2 ON table1.column3 = table2.column3;

   1.  SQL概述

1.1　什么是SQL

SQL（Structured Query Language）是“结构化查询语言”，它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中，例如：MySQL、Oracle、SQL Server等。SQ标准（ANSI/ISO）有：

l  SQL-92：1992年发布的SQL语言标准；

l  SQL:1999：1999年发布的SQL语言标签；

l  SQL:2003：2003年发布的SQL语言标签；

这些标准就与JDK的版本一样，在新的版本中总要有一些语法的变化。不同时期的数据库对不同标准做了实现。

虽然SQL可以用在所有关系型数据库中，但很多数据库还都有标准之后的一些语法，我们可以称之为“方言”。例如MySQL中的LIMIT语句就是MySQL独有的方言，其它数据库都不支持！当然，Oracle或SQL Server都有自己的方言。

1.2　语法要求

l  SQL语句可以单行或多行书写，以分号结尾；

l  可以用空格和缩进来来增强语句的可读性；

l  关键字不区别大小写，建议使用大写；

2　分类

l  DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；

l  DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；

l  DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；

l  DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

3　DDL

3.1　基本操作

l  查看所有数据库名称：SHOW DATABASES；　

l  切换数据库：USE mydb1，切换到mydb1数据库；

3.2　操作数据库

l  创建数据库：CREATE DATABASE [IF NOT EXISTS] mydb1；

创建数据库，例如：CREATE DATABASE mydb1，创建一个名为mydb1的数据库。如果这个数据已经存在，那么会报错。例如CREATE DATABASE IF NOT EXISTS mydb1，在名为mydb1的数据库不存在时创建该库，这样可以避免报错。

l  删除数据库：DROP DATABASE [IF EXISTS] mydb1；

删除数据库，例如：DROP DATABASE mydb1，删除名为mydb1的数据库。如果这个数据库不存在，那么会报错。DROP DATABASE IF EXISTS mydb1，就算mydb1不存在，也不会的报错。

l  修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8

修改数据库mydb1的编码为utf8。注意，在MySQL中所有的UTF-8编码都不能使用中间的“-”，即UTF-8要书写为UTF8。

3.3　数据（列）类型

MySQL与Java一样，也有数据类型。MySQL中数据类型主要应用在列上。

常用类型：

l  int：整型

l  double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；

l  decimal：浮点型，在表示钱方面使用该类型，因为不会出现精度缺失问题；

l  char：固定长度字符串类型；

l  varchar：可变长度字符串类型；

l  text：字符串类型；

l  blob：字节类型；

l  date：日期类型，格式为：yyyy-MM-dd；

l  time：时间类型，格式为：hh:mm:ss

l  datatime：时间戳类型；

3.4　操作表

l  创建表：

CREATE TABLE 表名(

  列名 列类型,

  列名 列类型,

  ......

);

例如：

CREATE TABLE stu(          sid        CHAR(6),          sname      VARCHAR(20),          age            INT,          gender     VARCHAR(10) );

再例如：

CREATE TABLE emp(          eid             CHAR(6),          ename     VARCHAR(50),          age            INT,          gender     VARCHAR(6),          birthday  DATE,          hiredate  DATE,          salary       DECIMAL(7,2),          resume    VARCHAR(1000) );

l  查看当前数据库中所有表名称：SHOW TABLES；　

l  查看指定表的创建语句：SHOW CREATE TABLE emp，查看emp表的创建语句；

l  查看表结构：DESC emp，查看emp表结构；

l  删除表：DROP TABLE emp，删除emp表；

l  修改表：

1. 修改之添加列：给stu表添加classname列：

ALTER TABLE stu ADD (classname varchar(100));

1. 修改之修改列类型：修改stu表的gender列类型为CHAR(2)：

ALTER TABLE stu MODIFY gender CHAR(2);

1. 修改之修改列名：修改stu表的gender列名为sex：

ALTER TABLE stu change gender sex CHAR(2);

1. 修改之删除列：删除stu表的classname列：

ALTER TABLE stu DROP classname;

1. 修改之修改表名称：修改stu表名称为student：

ALTER TABLE stu RENAME TO student;

4　DML

4.1　插入数据

语法：INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)

INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male');

语法：

INSERT INTO 表名 VALUES(值1,值2,…)

因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值：

INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');

　　注意：所有字符串数据必须使用单引用！

4.2　修改数据

语法：

UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

UPDATE stu SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’;

UPDATE stu SET sname=’liSi’, age=’20’ WHERE age>50 AND gender=’male’;

UPDATE stu SET sname=’wangWu’, age=’30’ WHERE age>60 OR gender=’female’;

UPDATE stu SET gender=’female’ WHERE gender IS NULL UPDATE stu SET age=age+1 WHERE sname=’zhaoLiu’;

4.3　删除数据

语法：

DELETE FROM 表名 [WHERE 条件]

DELETE FROM stu WHERE sid=’s_1001’003B

DELETE FROM stu WHERE sname=’chenQi’ OR age > 30;

DELETE FROM stu;

语法：

TRUNCATE TABLE 表名

TRUNCATE TABLE stu;

虽然TRUNCATE和DELETE都可以删除表的所有记录，但有原理不同。DELETE的效率没有TRUNCATE高！

TRUNCATE其实属性DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的（回滚是事务的知识！）。

5　DCL

5.1　创建用户

语法：

CREATE USER 用户名@地址 IDENTIFIED BY '密码';

CREATE USER user1@localhost IDENTIFIED BY ‘123’;

CREATE USER user2@’%’ IDENTIFIED BY ‘123’;

5.2　给用户授权

　　语法：

GRANT 权限1, … , 权限n ON 数据库.* TO 用户名

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;

GRANT ALL ON mydb1.* TO user2@localhost;

5.3　撤销授权

　　语法：

　　REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名

REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

5.4　查看用户权限

语法：

SHOW GRANTS FOR 用户名

SHOW GRANTS FOR user1@localhost;

5.5　删除用户

语法：

DROP USER 用户名

DROP USER user1@localhost;

5.6　修改用户密码

语法：

USE mysql;

UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’;

FLUSH PRIVILEGES;

UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2' and Host=’localhost’; FLUSH PRIVILEGES;

MySQL索引：

• 唯一索引：就是索引列中的值必须是唯一的，但是允许出现空值。这种索引一般用来保证数据的唯一性，比如保存账户信息的表，每个账户的id必须保证唯一，如果重复插入相同的账户id时会MySQL返回异常。
• 主键索引：是一种特殊的唯一索引，但是它不允许出现空值。
• 普通索引：与唯一索引不同，它允许索引列中存在相同的值。例如学生的成绩表，各个学科的分数是允许重复的，就可以使用普通索引。
• 联合索引：就是由多个列共同组成的索引。一个表中含有多个单列的索引并不是联合索引，联合索引是对多个列字段按顺序共同组成一个索引。应用联合索引时需要注意最左原则，就是Where查询条件中的字段必须与索引字段从左到右进行匹配。比如，一个用户信息表，用姓名和年龄组成了联合索引，如果查询条件是姓名等于张三，那么满足最左原则；如果查询条件是年龄大于20，由于索引中最左的字段是姓名不是年龄，所以不能使用这个索引。
• 全文索引：前面提到了，MyISAM引擎中实现了这个索引，在5.6版本后InnoDB引擎也支持了全文索引，并且在5.7.6版本后支持了中文索引。全文索引只能在CHAR,VARCHAR,TEXT类型字段上使用，底层使用倒排索引实现。要注意对于大数据量的表，生成全文索引会非常消耗时间也非常消耗磁盘空间。

▌2.索引实现

如右面的模块，索引实现共分4种形式：

• B+树实现：b+树比较适合用作'>'或'<'这样的范围查询，是MySQL中最常使用的一种索引实现。
• R-tree：是一种用于处理多维数据的数据结构，可以对地理数据进行空间索引。不过实际业务场景中使用的比较少。
• Hash：是使用散列表来对数据进行索引，Hash方式不像Btree那样需要多次查询才能定位到记录，因此Hash索引的效率高于B-tree，但是不支持范围查找和排序等功能.实际使用的也比较少。
• FullText：就是我们前面提到的全文索引，是一种记录关键字与对应文档关系的倒排索引。

MySQL调优：

一般MySQL调优有图中的4个纬度：

• 针对数据库设计、表结构设计以及索引设置纬度进行的优化；
• 对业务中使用的SQL语句进行优化，例如调整Where查询条件；
• 对mysql服务的配置进行优化，例如对链接数的管理，对索引缓存、查询缓存、排序缓存等各种缓存大小进行优化;
• 对硬件设备和操作系统设置进行优化，例如调整操作系统参数、禁用Swap、增加内存、升级固态硬盘等等。

这四个纬度从优化的成本角度来讲，从左到右优化成本逐渐升高；从优化效果角度来看，从右到左优化的效果更高。

1.表结构和索引的优化

如左面的模块，应该掌握如下6个原则：

第1个原则：要在设计表结构时，考虑数据库的水平与垂直扩展能力，提前规划好未来1年的数据量、读写量的增长，规划好分库分表方案。比如设计用户信息表，预计1年后用户数据10亿条，写QPS约5000，读QPS30000，可以设计按UID纬度进行散列，分为4个库每个库32张表，单表数据量控制在KW级别；

第2个原则：要为字段选择合适的数据类型，在保留扩展能力的前提下，优先选用较小的数据结构。例如保存年龄的字段，要使用TINYINT而不要使用INT；

第3个原则：可以将字段多的表分解成多个表，必要时增加中间表进行关联。假如一张表有4、50个字段显然不是一个好的设计；

第4个原则：是设计关系数据库时需要满足第三范式，但为了满足第三范式，我们可能会拆分出多张表。而在进行查询时需要对多张表进行关联查询，有时为了提高查询效率，会降低范式的要求，在表中保存一定的冗余信息，也叫做反范式。但要注意反范式一定要适度；

第5个原则：要擅用索引，比如为经常作为查询条件的字段创建索引、创建联合索引时要根据最左原则考虑索引的复用能力，不要重复创建索引；要为保证数据不能重复的字段创建唯一索引等等。不过要注意索引对插入、更新等写操作是有代价的，不要滥用索引。比如像性别这样唯一很差的字段就不适合建立索引；

第6个原则：列字段尽量设置为Not Null，MySQL难以对使用Null的列进行查询优化，允许Null会使索引、索引统计和值更加复杂。允许Null值的列需要更多的存储空间，还需要MySQL内部进行特殊处理。

▌2.SQL语句进行优化的原则

如右面的模块，共分5个原则：

第1个原则：要找的最需要优化的SQL语句。要么是使用最频繁的语句，要么是优化后提高最明显的语句，可以通过查询MySQL的慢查询日志来发现需要进行优化的SQL语句；

第2个原则：要学会利用MySQL提供的分析工具。例如使用Explain来分析语句的执行计划，看看是否使用了索引，使用了哪个索引，扫描了多少记录，是否使用文件排序等等。或者利用Profile命令来分析某个语句执行过程中各个分步的耗时；

第3个原则：要注意使用查询语句是要避免使用Select *，而是应该指定具体需要获取的字段。原因一是可以避免查询出不需要使用的字段，二是可以避免查询列字段的元信息；

第4个原则：是尽量使用Prepared Statements，一个是性能更好，另一个是可以防止SQL注入；

第5个原则：是尽量使用索引扫描来进行排序，也就是尽量在有索引的字段上进行排序操作。