mysql-mysql记录

分类于

重新捡起丢了六年的东西.

前篇

用户操作

添加用户

  • s

表操作

创建表

  • CREATE TABLE

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> CREATE TABLE IF NOT EXISTS `user`(
       `id` INT UNSIGNED AUTO_INCREMENT,
       `name` VARCHAR(100) NOT NULL,
       `age` INT UNSIGNED NOT NULL,
       PRIMARY KEY ( `id` )
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

    Query OK, 0 rows affected (22.32 sec) # 执行结果
字段类型

查看表结构

命令: desc tbl_name

1
2
3
4
5
6
7
8
9
10
mysql> desc user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
| password | varchar(32) | YES  |     | NULL    |                |
| email    | varchar(50) | YES  | UNI | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

增删查改

  • 增

    1
    2
    3
    INSERT INTO table_name (field1, field2,...fieldN) VALUES (value1, value2,...valueN);
    # 示例
    INSERT INTO Shop (name, url, col01) VALUES ('大王叫我来巡山, 哈哈哈', 'https://wolegequ.wilker.cn', 1);

  • 删

    1
    2
    3
    DELETE FROM table_name [WHERE Clause]
    # 示例
    DELETE FROM Shop WHERE id = 7;

  • 查

    1
    2
    3
    SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M]
    # 示例
    SELECT name, url FROM Shop where id = 2 LIMIT 0,5;

  • 改

    1
    2
    3
    UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
    # 示例
    UPDATE Shop SET name='我到人间看一看, 呵呵呵', url='https://blog.wilker.cn' WHERE id = 11;

alert

使用

  • 加索引
    mysql> alter table 表名 add index 索引名 (字段名1[，字段名2 …]);

    例子： mysql> alter table employee add index emp_name (name);

  • 加主关键字的索引
    mysql> alter table 表名 add primary key (字段名);

    例子： mysql> alter table employee add primary key(id);

  • 加唯一限制条件的索引
    mysql> alter table 表名 add unique 索引名 (字段名);

    例子： mysql> alter table employee add unique emp_name2(cardnumber);

  • 删除某个索引
    mysql> alter table 表名 drop index 索引名;

    例子： mysql>alter table employee drop index emp_name;

  • 增加字段
    mysql> ALTER TABLE table_name ADD field_name field_type;

  • 修改原字段名称及类型
    mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

  • 删除字段
    MySQL ALTER TABLE table_name DROP field_name;

  • 添加字段

    语法：alter table 表名 add 列名 列类型;
    演示：alter table student add username varchar(18) not null;
    演示：alter table student add password varchar(12) default "123456";

  • 修改列类型

    语法：alter table 表名 modify 列名 列类型;
    演示：alter table student modify password varchar(10) default "123456";

  • 修改列名

    语法：alter table 表名 change 原列名 新列名 列类型;
    演示：alter table student change id StudentID int(11) auto_increment;

  • 修改表名称

    语法：rename table 原表名 to 新表名;
    演示：rename table student to students;

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括: 读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

联合查询

索引

查看是否走了索引

在 sql 语句前加个 explain 关键字, 如

  • 走索引

    1
    2
    3
    4
    5
    6
    7
    mysql> explain select * from account where id = 100004;
    +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    | id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
    +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | account | NULL       | const | PRIMARY,id    | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
    +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    1 row in set (0.05 sec)

  • 不走索引

    1
    2
    3
    4
    5
    6
    7
    mysql> explain select * from account where username = 'wilker';
    +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | account | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
    +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
    1 row in set (0.06 sec)

    type 为 all, 表示 全表 搜索

索引 创建/删除/查看

创建

  1. 建表的时候创建

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLE `testIndex` (
      `a` int(10) NOT NULL AUTO_INCREMENT,
      `b` int(10) NOT NULL,
      `c` int(10) NOT NULL,
      `d` int(10) NOT NULL,
      PRIMARY KEY (`a`),
      KEY `idx_b_c_d` (`b`, `c`, `d`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

  2. 建完表后创创建, 单独用 CREATE INDEX 或 ALTER TABLE 来为表增加索引

    • ALTER TABLE 语法. ALTER TABLE 用来创建普通索引、UNIQUE 索引或 PRIMARY KEY 索引。

      1
      2
      3
      ALTER TABLE table_name ADD INDEX index_name (column_list)
      ALTER TABLE table_name ADD UNIQUE (column_list)
      ALTER TABLE table_name ADD PRIMARY KEY (column_list)

      • 示例

        1
        ALTER TABLE testIndex ADD INDEX `idx_b_c_d` (`b`, `c`, `d`)

    • CREATE INDEX 语法. CREATE INDEX 可对表增加普通索引或 UNIQUE 索引。不能用 CREATE INDEX 语句创建 PRIMARY KEY 索引

      1
      2
      CREATE INDEX index_name ON table_name (column_list)
      CREATE UNIQUE INDEX index_name ON table_name (column_list)
删除

可利用 ALTER TABLE 或 DROP INDEX 语句来删除索引。类似于 CREATE INDEX 语句，DROP INDEX 可以在 ALTER TABLE 内部作为一条语句处理，语法如下。

1
2
3
4
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name -- 等价于上一条

ALTER TABLE table_name DROP PRIMARY KEY
查看
1
2
SHOW INDEX FROM tblname;
SHOW KEYS FROM tblname;

主键，外键，索引，唯一索引, 联合索引

  • 主键

    主键(primary key) 能够唯一标识表中某一行的属性或属性组。一个表只能有一个主键，但可以有多个候选索引。主键常常与外键构成参照完整性约束，防止出现数据不一致。主键可以保证记录的唯一和主键域非空,数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。

  • 外键

    外键（foreign key） 是用于建立和加强两个表数据之间的链接的一列或多列。外键约束主要用来维护两个表之间数据的一致性。简言之，表的外键就是另一表的主键，外键将两表联系起来。一般情况下，要删除一张表中的主键必须首先要确保其它表中的没有相同外键（即该表中的主键没有一个外键和它相关联）。

  • 索引

    索引(index) 是用来快速地寻找那些具有特定值的记录。主要是为了检索的方便，是为了加快访问速度， 按一定的规则创建的，一般起到排序作用。所谓唯一性索引，这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。

总结

  • 主键一定是唯一性索引，唯一性索引并不一定就是主键。 一个表中可以有多个唯一性索引，但只能有一个主键。

  • 主键列不允许空值，而唯一性索引列允许空值。

  • 主键可以被其他字段作外键引用，而索引不能作为外键引用。

Sql vs NoSql

快速生成百万数据

  1. 使用存储过程, 参考: mysql百万数据实践-索引 - https://segmentfault.com/a/1190000012918964
  2. 使用编程语言, 如: go, 使用对应的驱动插入数据

整形类型 括号中的数字代表的含义, 如: int(11)

当存储的字符长度小于M值时，只有在设置了zerofill用0来填充，才能够看到效果，换句话就是说，没有zerofill，M值就是无用的。

总结：int(11)，tinyint(1)，bigint(20)，后面的数字，不代表占用空间容量。而代表最小显示位数。这个东西基本没有意义，除非你对字段指定zerofill。

所以我们在设计mysql数据库时，建表时，mysql会自动分配长度：int(11)、tinyint(4)、smallint(6)、mediumint(9)、bigint(20)。

所以，就用这些默认的显示长度就可以了。不用再去自己填长度，比如搞个int(10)、tinyint(1)之类的，基本没用。而且导致表的字段类型多样化。

整形 所占的空间大小在指定类型时候就决定了,

char 与 varchar 区别

字符串数据类型

MySQL数据类型 含义
char(n) 固定长度，最多 2^8−1 个字符，2^8−1 个字节
varchar(n) 可变长度，最多 2^16−1 个字符，2^16−1 个字节
tinytext 可变长度，最多 2^8−1 个字符，2^8−1 个字节
text 可变长度，最多 2^16−1 个字符，2^16−1 个字节
mediumtext 可变长度，最多 2^24−1 个字符，2^24−1 个字节
longtext 可变长度，最多 2^32−1 个字符，2^32−1 个字节

VARCHAR与CHAR字符型数据的差异

  1. char（n）和varchar（n）中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候(UTF8)意味着可以插入m个中文，但是实际会占用m*3个字节，m∗3<=255，m∗3<=65535。使用 utf8mb4的话插入m个中文,会占用m*4个字节。m∗4<=255，m∗4<=65535。
  2. 同时 char 和 varchar 最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的字节空间 L+1(L=0到255) 或 L+2(L>255)，并且实际字节空间 L+1<=255,或者 L+2<=65535.
  3. 超过 cha r和 varchar 的 n 设置后，字符串会被截断。
  4. char 的上限为 255字节，varchar 的上限 65535字节，text 的上限为 65535字节。
  5. char 在存储的时候会截断尾部的空格，varchar 和 text不会。
  6. varchar(M),M范围是0~65535.M的值以最大行大小(65535字节)为准.例如,如果使用UTF-8编码,一个字符占3个字节,那么M最大为21844.

  • 限制规则

    字段的限制在字段定义的时候有以下规则：

    　　a) 存储限制

    　　varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字节），因此最大长度不能超过65535。

    　　b) 编码长度限制

    　　字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766;

    　　字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845。

    　　若定义的时候超过上述限制，则varchar字段会被强行转为text类型，并产生warning。

    　　c) 行长度限制

    　　导致实际应用中varchar长度限制的是一个行定义的长度。 MySQL要求一个行的定义长度不能超过65535。若定义的表长度超过这个值，则提示

    　　ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs。

  • 计算例子

    举两个例说明一下实际长度的计算。

    　　a) 若一个表只有一个varchar类型，如定义为

    　　create table t4(c varchar(N)) charset=gbk;

    　　则此处N的最大值为(65535-1-2)/2= 32766。

    　　减1的原因是实际行存储从第二个字节开始’;

    　　减2的原因是varchar头部的2个字节表示长度;

    　　除2的原因是字符编码是gbk。

    　　b) 若一个表定义为

    　　create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;

    　　则此处N的最大值为 (65535-1-2-4-30*3)/3=21812

    　　减1和减2与上例相同;

    　　减4的原因是int类型的c占4个字节;

    　　减30*3的原因是char(30)占用90个字节，编码是utf8。

    　　如果被varchar超过上述的b规则，被强转成text类型，则每个字段占用定义长度为11字节，当然这已经不是“varchar”了。

    　　则此处N的最大值为 (65535-1-2-4-30*3)/3=21812

分页 - limit 使用

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。

  • 两个参数 :

    第一个参数指定第一个返回记录行的**偏移量，第二个参数指定返回记录行的最大数目**。初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

  • 一个参数 : 它表示返回最大的记录行数目：
    如: mysql> SELECT * FROM table LIMIT 5; // 检索前 5 个记录行, 换句话说，**LIMIT n 等价于 LIMIT 0,n**。

  • 常规使用

    1
    mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

分组 - group by

GROUP BY 语法

1
2
3
4
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG, MAX, MIN 等函数。

  • 示例1

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    mysql> SELECT * FROM employee_tbl;
    +----+--------+---------------------+--------+
    | id | name   | date                | singin |
    +----+--------+---------------------+--------+
    |  1 | 小明 | 2016-04-22 15:25:33 |      1 |
    |  2 | 小王 | 2016-04-20 15:25:47 |      3 |
    |  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
    |  4 | 小王 | 2016-04-07 15:26:14 |      4 |
    |  5 | 小明 | 2016-04-11 15:26:40 |      4 |
    |  6 | 小明 | 2016-04-04 15:26:54 |      2 |
    +----+--------+---------------------+--------+
    6 rows in set (0.00 sec)

    mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name; # 将数据表按 名字 进行分组，并统计每个人有多少条记录：
    +--------+----------+
    | name   | COUNT(*) |
    +--------+----------+
    | 小丽 |        1 |
    | 小明 |        3 |
    | 小王 |        2 |
    +--------+----------+
    3 rows in set (0.01 sec)

    ########## 使用 WITH ROLLUP

WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

  • 示例1

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
    +--------+--------------+
    | name   | singin_count |
    +--------+--------------+
    | 小丽 |            2 |
    | 小明 |            7 |
    | 小王 |            7 |
    | NULL   |           16 |
    +--------+--------------+
    4 rows in set (0.00 sec)

    其中记录 NULL 表示所有人的登录次数。

    我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

    1
    select coalesce(a,b,c);

    参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

    以下实例中如果名字为空我们使用总数代替：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
    +--------------------------+--------------+
    | coalesce(name, '总数') | singin_count |
    +--------------------------+--------------+
    | 小丽                   |            2 |
    | 小明                   |            7 |
    | 小王                   |            7 |
    | 总数                   |           16 |
    +--------------------------+--------------+
    4 rows in set (0.01 sec)

having 条件筛选

having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。

  • 示例

    1
    SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region HAVING SUM(area)>1000000

排序 - order by

以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：

1
2
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
  • 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
  • 你可以设定多个字段来排序。
  • 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
  • 你可以添加 WHERE…LIKE 子句来设置条件。

模糊查询 - like

SQL LIKE 子句中使用百分号 **%**字符来表示任意字符，类似于UNIX或正则表达式中的星号 *****。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：

1
2
3
SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

在 where like 的条件查询中，SQL 提供了四种匹配方式。

  1. **%**：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
  2. **_**：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
  3. **[]**：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
  4. [^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
  5. 查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。

  • 例如: 

    1
    2
    3
    4
    5
    6
    '%a'     //以a结尾的数据
    'a%'     //以a开头的数据
    '%a%'    //含有a的数据
    '_a_'    //三位且中间字母是a的
    '_a'     //两位且结尾字母是a的
    'a_'     //两位且开头字母是a的

  • 示例1:

    1
    2
    3
    SELECT * FROM position WHERE name LIKE 'java%'; # 查询以 java 字段开头的信息。
    SELECT * FROM position WHERE name LIKE '%java%'; # 查询包含 java 字段的信息。
    SELECT * FROM position WHERE name LIKE '%java'; # 查询以 java 字段结尾的信息。

组合数据 - union

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

MySQL UNION 操作符语法格式：

1
2
3
4
5
6
7
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
  • expression1, expression2, … expression_n: 要检索的列。
  • tables: 要检索的数据表。
  • WHERE conditions: 可选， 检索条件。
  • DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
  • ALL: 可选，返回所有结果集，包含重复数据。

  • 示例01:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    mysql> SELECT * FROM Websites;
    +----+--------------+---------------------------+-------+---------+
    | id | name         | url                       | alexa | country |
    +----+--------------+---------------------------+-------+---------+
    | 1  | Google       | https://www.google.cm/    | 1     | USA     |
    | 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
    | 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
    | 4  | 微博          | http://weibo.com/         | 20    | CN      |
    | 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
    | 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
    +----+---------------+---------------------------+-------+---------+

    mysql> SELECT * FROM apps;
    +----+------------+-------------------------+---------+
    | id | app_name   | url                     | country |
    +----+------------+-------------------------+---------+
    |  1 | QQ APP     | http://im.qq.com/       | CN      |
    |  2 | 微博 APP | http://weibo.com/       | CN      |
    |  3 | 淘宝 APP | https://www.taobao.com/ | CN      |
    +----+------------+-------------------------+---------+

    ########## UNION ALL
    SELECT country, name AS new_name
    FROM Websites
    WHERE country = 'CN'
    UNION ALL
    SELECT country, app_name
    FROM apps
    WHERE country = 'CN'
    ORDER BY country;
    +---------+----------+
    | country | new_name |
    +---------+----------+
    | CN      | 淘宝     |
    | CN      | 菜鸟教程 |
    | CN      | 微博     |
    | CN      | QQ APP   |
    | CN      | 微博 APP |
    | CN      | 淘宝 APP |
    +---------+----------+
    # 多个 select 语句的 字段数量 必须相同, 字段名以第一个 select 语句为准

MySQL 及 SQL 注入

所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。

  • 例如: 让我们看下在没有过滤特殊字符时，出现的SQL情况：

    1
    2
    3
    // 设定$name 中插入了我们不需要的SQL语句
    $name = "Qadir'; DELETE FROM users;";
    mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

    以上的注入语句中，我们没有对 $name 的变量进行过滤，$name 中插入了我们不需要的SQL语句，将删除 users 表中的所有数据。

防止SQL注入，我们需要注意以下几个要点：

  1. 永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双”-“进行转换等。
  2. 永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
  3. 永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
  4. 不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
  5. 应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
  6. sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

存储引擎选择

MyISAM

擅长直接读写，尤其是读。不支持回滚。插入时无序

InnoDB

聚集索引, 插入时会排序

可以处理事务功能，即可以回滚。读写性能相对较差，尤其是写的能力。 InnoDB是默认的MySQL引擎

所以，一般在没有事务处理的要求下，建议使用MyISAM引擎。

分库分表

读写分离 - 主从同步

MySQL读写分离介绍

MySQL读写分离基本原理是让master数据库处理写操作，slave数据库处理读操作。master将写操作的变更同步到各个slave节点。

MySQL读写分离能提高系统性能的原因在于：

  • 物理服务器增加，机器处理能力提升。拿硬件换性能。
  • 主从只负责各自的读和写，极大程度缓解X锁和S锁争用。
  • slave可以配置myiasm引擎，提升查询性能以及节约系统开销。
  • master直接写是并发的，slave通过主库发送来的binlog恢复数据是异步。
  • slave可以单独设置一些参数来提升其读的性能。
  • 增加冗余，提高可用性。

主从复制 开启 GTID

如果开启了 GTID, 导出数据库时会有 GTID 信息, 如:

1
SET @@GLOBAL.GTID_PURGED='72605d1c-2b9d-11ea-889f-00163e001b4e:1-382534';

导入这样的数据时, 需要 mysql 开启 GTID 才能导入. 如果不开启者注解注释掉 SET @@GLOBAL.GTID_PURGED 信息.

水平拆分

一个数据中的表, 分到多个数据库的表中, 表的结构完全相同. 实现的方式就是类似对 uid hash 后 取模. 获取到连接池中的 数据库连接, 进行表的操作. 也叫作 数据分片. (Redis 那种分片差不多)

垂直拆分

一个数据库总的 多个表, 分到多个数据库中, 业务逻辑中对不同业务连接不同数据库进行操作.

Mysql底层源码解析 记录

InnoDB 的 页

插入数据的时候会排序, 这样做是为了提高查询性能, 比如 查 id = 5, 那么 id > 5 之后的都不用查了

B+ Tree

索引数据 与 用户数据 在一起的就叫做 聚集索引, 在 innodb 里面可以等价于 主键索引

黄色部分 1, 2, 5 就是 主键值

联合索引 中的 橙色部分的 索引数据 肯定是小于 主键索引 中的 橙色部分的 完整数据, 所以 联合索引 相比 主键索引 可以用 更少的页 来存储更多的 索引数据

那么, 如果是 select b from t1; 看似会全表扫描, 但实际上 这种只查询索引数据的话, 会走 联合索引 而不是 全表扫描. 可以通过 查看是否走了索引 查看.

回表

也就是 联合索引 查找到后 (黄色部分), 再根据 主键值 去 主键索引 里找数据.

如果 联合索引 后不需要回表查找数据, 如: SELECT b,c,d FROM t1 WHERE b=1 AND c=1 AND d=1, 叫做: 覆盖索引

最左前缀原则

比如创建了 一个索引 (b,c,d)

那么只有给出 b/bc/bcd/bd 的条件才能走索引, c/cd/d 都不能走索引. bd 中 d 条件没用, 只能比较 b, 因为中间少了个 c, 导致 c 后面的条件都没用了

同样如果给 b 条件 like %123 这种, 也是不能走索引

也就是说, 只有最左边的条件给出了, 不管中间或右边有没有给出, 都可以走索引

查询优化器

  • select * 使用了 b>1 的条件,

    • 第一种查询流程是: 先到 (b,c,d) 索引中查找到 主键值, 再根据 主键值 回表查询到数据
    • 第二种查询流程是: 直接全表扫描

    mysql 会优化这个过程 , 因为是 * (所有字段信息), 会使用全表扫描的方式

  • select b,c,d 使用了 b>1 的条件, 只需要的是 b,c,d 信息

    那么就会走 (b,c,d) 索引, 但不需要回表查询.

索引字段运算导致索引失效

数字 与 字符 的比较

select 0='a' 结果是 1

select 0='1' 结果是 0

解释: 所有除 数字 外字符都是隐式转换为 0, 数字字符 转换为 对应的数字

引发的问题是就是:

如果 索引 字段e 是 vchar 类型, 但是如果使用数字去比较, 则会把 字段e 转换成 数字, 就不会走索引.

如果 索引 字段e 是 整形 类型, 与 数字字符 '1' 比较, 则数字字符会隐式转换成 整形, 则会走索引

如果 对 索引 字段e 进行运算(SELECT * FROM tbl WHERE a+1=1), 也会导致不走索引

总结

只要对 索引字段 进行操作 (运算/隐式转换), 都会导致索引失效, 进行全表扫描.

关于 limit 的优化

比如 分页 :

sql1: SELECT * FROM tbl limit 1, 10000

sql2: SELECT * FROM tbl limit 10001, 10000

sql1 的使用没问题, 速度也是很快, 但是 sql2 就速度很慢, 因为是全表扫描, 扫到第 10001 条开始才是需要数据

此时可以利用 主键索引, 直接定位到第 10001条数据, 也就是 sql1 得到的结果的最后一个 id 值 x, 用到 sql2 中, SELECT * FROM tbl WHERE id>x limit 1, 10000

关于 order by 排序

比如 SELECT * FROM t1 ORDER BY b asc, c asc, d asc

此时是不会走索引的, 虽然 (b,c,d) 是 联合索引, 且已经是 bcd 升序排序好了

因为是 * (所有字段信息), 所有 mysql 会直接 全表扫描, 而不是去 联合索引 中按到所有 主键值 数据, 再 回表 查询全表

如果只是查 bcd 的数据, 则会走索引, 因为这些数据在 联合索引 中已经存在, 且不需要 回表 查询.

此外, 使用排序时会使用到一个临时内存 sort_buff, 对 排序字段 进行排序, 拍完序就用主键去索引返回数据, explain 中会看到 extra 字段是 using filesort

2000万条以上的数据是大表

1
2
3
4
5
6
7
sort_buff = {
    111-pkey,
    222-pkey,
    235-pkey,
    311-pkey,
    ...
}

特殊情况如: SELECT b,c,d FROM t1 ORDER BY b ASC, c ASC, d DESC

如果排序的几个字段都是 联合索引, 且都是升序的, 且 select 的 字段都是 联合索引 中的一个或几个, 那么久不会用到临时内存去排序, 因为 联合索引 中的数据已经是排好序的数据 (B+ Tree). explain 中会看到 extra 字段是 using index

创建索引是指定排序规则

在 mysql8 之前, 创建的索引都是升序的, 如: CREATE INDEX idx_bcd ON t1 (b,c,d)

所以 B+ Tree 的 叶子节点 的排序都是升序的

在 mysql8 中, 创建索引的时候是允许指定排序规则, 如: CREATE INDEX idx_bcd ON t1 (b ASC,c ASC,d DESC)

B+ Tree 的 叶子节点 就会按照这个规则去生成, 所以 SELECT b,c,d FROM t1 ORDER BY b ASC, c ASC, d DESC 则不用全表扫描, 直接去 联合索引 中获取数据就行了

2000万以上数据为大表

todo:

bottom