mysql 基础

java
字数统计: 8.5k   |   阅读时长≈ 32 分钟

MySql学习

  1. 数据库指令
  2. mysql 数据库函数
  3. 事务
  4. 索引
  5. 权限管理和备份
  6. mysql 备份
  7. 数据库设计
  8. sql 语句性能优化

1、数据库指令

1.1、TRUNCATE 命令

  1. 作用：完全清空一个数据库表，表的结构和索引约束不会变
1
2
-- 清空student表
TRUNCATE 'student'
  1. delete 和 TRUNCATE 的区别
    • 相同点：都能删除数据，都不会删除表结构
    • 不同点：
      • TRUNCATE 重新设置自增列，计数器会归零
      • TRUNCATE 不会影响事务

1.2、select

1
2
3
4
5
6
7
8
9
SELECT [all | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT ] -- 指定查询的记录从哪条至哪条

1.2.1、查询别名

1
2
3
select 'StudentNo' as 学号,'StudentName' as 学生姓名 from student as s
-- 函数 CONCAT(a,b) 连接两个字符串
select CONCAT('姓名',StudentName) as 新名字 FROM student

1.2.2、去重 distinct

作用：去除 SELECT 查询出来的结果中重复的数据，重复的数据只显示一条

1
SELECT DISTANCT 'StudentNo' FROM result -- 去除重复数据

1.2.3、数据库的列

1
2
3
4
5
6
SELECT VERSION() -- 查询mysql版本（函数）
SELECT 100*3-1 AS 计算结果 -- 计算结果（表达式）
SELECT @@auto_increment_increment -- 查询自增的步长（变量）

-- 学员考试成绩 +1 分查看
SELECT 'StudentNo','StudentResult'+1 as '提分后' FROM result

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量….

select 表达式 from 表

1.2.4、where 条件子句

作用：检索数据中 符合条件 的值

搜索的条件由一个或者多个表达式组成！结果为 布尔值

  • 逻辑运算符

    运算符 语法 结果描述
    and && a and b a && b 逻辑与
    or || a or b a || b 逻辑或
    not ! not a ! a 逻辑非

    尽量使用英文字母

  • 模糊查询：比较运算符

运算符 语法 描述
is null a is null 如果操作符为null，结果为真
is not null a is not null 如果操作符不为null，结果为真
between a between b and c 若 a 在 b 和 c 之间，则结果为真
like a like b 如果a 匹配到 b， 则结果为真
in a in (a1,a2,a3….) 假设a在a1，或者a2… 其中的某一个值中，结果为真 
1
2
3
-- like 结合 %（代表0到任意个字符） _（一个字符）
SELECT figure_name as 历史人物名 FROM history_figure 
WHERE figure_name LIKE '周%'
1
2
3
--查询年龄等于 50，60，70的历史人物 in() 里面是一个具体的值
SELECT figure_name,age FROM history_figure
WHERE age in (50,60,70)

1.2.5、联表查询

  • left join、right join、inner join

    img

    1
    2
    3
    4
    5
    6
    7
    8
    -- join（连接的表） on（判断的条件） 连接查询
    -- where 等值查询
    -- 交集 找到两张表的交叉点
    SELECT figure_name,age,sort FROM history_figure as hf
    INNER JOIN figure_location as fl ON hf.id = fl.figure_id
    -- left join 展示左表所有的数据，右表符合 on 条件的数据，右表不符合的则为空显示
    -- inner join 显示两表的信息
    -- 左右join 以哪张表为基准
    操作 描述
    Inner join 如果表中至少有一个匹配，则返回行
    left join 返回左表中所有的值、即使右表中没有匹配
    right join 返回右表中所有的值、即使左表中没有匹配

  • 自连接

    自己的表和自己的表连接，核心：一张表拆为两张一摸一样的表

    父类

    pid（一级id） categoryid categoryName
    1 2 信息技术
    1 3 软件开发
    1 5 美术设计

    子类

    pid categoryid categoryName
    3 4 数据库
    2 8 办公信息
    3 6 web开发
    5 7 ps技术

    操作：查询父类对应的子类关系

    父类 子类
    信息技术 办公信息
    软件开发 数据库
    软件开发 web开发
    美术设计 ps技术 
    1
    2
    3
    -- sql 自连接解决分级问题，递归
    SELECT a.`name` as 父菜单,b.`name` as 子菜单 FROM acl_permission as a,acl_permission as b
    WHERE a.id = b.pid AND a.`is_deleted` = 0

    先分析要查询哪些字段，再连接表，注意别名，inner join

1.2.6、分页 limit 和排序 order by

  • 排序 升序 asc 降序 desc

  • limit 当前页,页面的大小 limit (current_page - 1)*size,size

    1
    2
    order by age asc
    limit 0,10

1.2.7、子查询

本质：where语句中嵌套一个子查询语句

1
2
3
4
5
6
-- 子查询（由里及外）
select 'StudentNo','SubjectNo','StudentResult' From 'result'
WHERE SubjectNo = (
	SELECT SubjectNo FROM 'subject'
    WHERE SUbjectName = '数据库结构-1'
)
1
2
3
4
5
6
-- 嵌套查询
SELECT StudentNo,StudentName FROM student WHERE StudentNo IN(
	SELECT StudentNo FROM result WHERE StudentResult > 80 AND SubjectNo = (
    	SELECT SubjectNo FROM subject WHERE SubjectName = '高等数据-2'
    )
)

2、mysql 数据库函数

2.1、常用函数

1
2
3
4
5
6
7
8
-- 常用函数 --
--数学运算
SELECT ABS(-8) --绝对值
SELECT CEILING(9.4) --向上取整
SELECT FLOOR(9.4) --向下取整
SELECT RAND（） -- 返回 0~1之间的随机数
SELECT SIGN（） -- 判断一个数的符号 0-0 复数返回-1，正数返回1
MD5() -- MD5加密
1
2
3
4
5
6
7
8
9
10
-- 字符串函数
SELECT CHAR_LENGTH('即使最小的帆也能远航') -- 字符串长度
SELECT CONCAT('我','是','谁')  -- 拼接字符串
SELECT INSERT('我爱编程HelloWorld',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长度
SELECT LOWER('UUUU') -- 转小写
SELECT UPER('uuuu') -- 转大写
SELECT INSTR('hqz','h') -- 返回第一次出现的子串索引
SELECT REPLACE('hqz','q',h) -- 替换出现的指定字符串
SELECT SUBSTR('hqz','2','1') -- 返回指定的子字符串（源字符串）
SELECT REVERSE('hqz') -- 反转
1
2
3
4
5
-- 时间日期函数
SELECT CURRENT_DATE() -- 获取当前日期 2021-03-29
SELECT CURDATE()
SELECT NOW() -- 获取当前时间 2021-03-29 22:09:00
SELECT YEAR(NOW()) -- 获取当前年
1
2
3
4
-- 系统
SELECT SYSTEM_USER() -- 获取当前登录的用户
SELECT USR()
SELECT VERSION() -- 获取当前sql 的版本

2.2、聚合函数

函数名称 描述
COUNT() 计数
SUM() 求和
AVG() 平均值
MAX() 最大值
MIN() 最小值
… … 
1
2
3
count(字段) -- 会忽略所有null值
COUNT(*) -- 不会忽略null值 本质：计算行数
COUNT(1) -- 不会忽略null值

2.3、分组和排序 HAVING

1
2
3
4
5
6
SELECT any_value(SubjectName),AVG(StudentResult) as 平均分,MAX(StudentResult) as 最高分,MIN(StudentResult) as 最低分
FROM RESULT r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubhectNO`
GROUP BY r.SubjectNo -- 通过什么字段分组
HAVING 平均分 > 80

3、事务

要么都成功，要么都失败

把一组sql放在一个批次中去执行

3.1、事务原则

  • ACID原则，原子性、一致性、隔离性、持久性

  • 原子性：要么一起成功，要么一起失败

  • 一致性：事务前后的数据完整性要保持一致

  • 隔离性：针对多个用户同时操作，排除其它事务对本次事务的影响

  • 持久性：事务结束后的数据不随外界原因丢失，事务一旦提交则不可逆被持久化到数据库中

  • 隔离导致的一些问题

    • 脏读：指一个事务读取了另外一个事务未提交的数据
    • 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同
    • 虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致（一般是行影响，多了一行）

  • ```mysql
    – 事务
    – MySQL 是默认开启事务自动提交的
    SET autocommit = 0 –关闭事务自动提交
    SET autocommit = 1 – 开启事务自动提交（默认）

    – 手动处理事务
    SET autocommit = 0 – 关闭自动提交

    – 事务开启
    START TRANSACTION – 标记一个事务的开始，从这个之后的sql都在同一个事务内
    – 执行sql
    INSERT xx
    INSERT xx

    – 提交：持久化（成功）
    COMMIT
    – 回滚：回到事务开始之前（失败）
    ROLLBACK

    – 事务结束
    SET autocommit = 1 – 开启自动提交

    – 了解
    SAVEPOINT 保存点名 – 设置一个事务的保存点
    ROLLBACK TO SAVEPOINT 保存点名 – 回滚到保存点
    RELEASE SAVEPOINT 保存点名 – 撤销保存点

    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
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53





    ## 4、索引



    索引（index）是帮助Mysql高效获取数据的数据结构，索引是一种数据结构



    ### 4.1、索引的分类



    - **主键索引（PRIMARY KEY）**

      - 唯一的标识，主键不可重复，只能有一个列作为主键

    - **唯一索引（UNIQUE KEY）**

      - 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引

    - **常规索引（KEY/INDEX）**

      - 默认的 index、key关键字来设置

    - **全文索引（FullText）**

      - 在特定的数据库引擎下才有，<font color='orange'>MylSAM</font>
      - 快速定位数据

    - ```mysql
      -- 索引的使用
      -- 1、在创建表的时候给字段增加索引
      -- 2、创建完毕后，增加索引
      
      -- 显示所有的所有信息
      SHOW INDEX FROM student
      
      -- 增加一个全文索引
      ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);
      
      -- EXPLAIN 分析sql执行的状况
      EXPLAIN SELECT * FROM student; -- 非全文索引
      
      EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');
      
      -- id_表名_字段名
      -- CREATE INDEX 索引名 on 表（字段）
      CREATE INDEX id_app_user_name ON app_user(`name`);

    索引在小数据量的时候用处不大，但是在大数据的时候，区别十分明显

4.2、索引原则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表不需要加索引
  • 索引一般加在常用来查询的字段上

索引的数据结构

Hash 类型的索引

Btree：InnoDB 的默认数据结构

5、权限管理和备份

用户管理

mysql.user 表

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
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER HQZ IDENTIFIED BY '123456'

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('123456')

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR HQZ = PASSWORD('111111')

-- 重命名 RENAME USER 原名 TO 新名
RENAME USER hqz TO HQZ2

-- 用户授权 ALL PRIVILEGES 全部的权限 ， 库 表
-- ALL PRIVILEGES 除了不能给别人授权，其它都能干
GRANT ALL PRIVILEGES ON *.* TO hqz

-- 查询权限
SHOW GRANTS FOR hqz -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost -- GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限 REVOKE 哪些权限 ， 在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM hqz

-- 删除用户
drop user hqz

6、Mysql备份

MySQL数据库备份的方式：

  • 直接拷贝物理文件

  • 在可视化数据库管理工具中手动导出

  • 使用命令行导出—— mysqldump 命令行使用

    1
    2
    # mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
    mysqldump -hlocalhost -uroot -psqlserver gp history_figure > E:/desktop/a.sql
    1
    2
    3
    -- 导入 sql 文件
    -- 登录的情况下
    source E:/desktop/a.sql

7、数据库设计

当数据库比较复杂的时候需要设计

糟糕的数据库设计：

  • 数据冗余，浪费空间
  • 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】

良好的数据库设计：

  • 节省内存空间
  • 保证数据库的完整性
  • 方便开发系统

数据库设计：

  • 分析需求：分析业务和需要处理的数据库的需求
  • 概要设计：设计关系图 E-R 图

设计数据库步骤：（个人博客）

  • 收集信息，分析需求
    • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
    • 分类表（文章分类，谁创建的）
    • 文章表（文章的信息）
    • 评论表
    • 友链表（友链信息）
    • 自定义表（系统信息，某个关键字，或者一些主字段）key：value
    • 说说表（发表心情 .. id ..content ..create_time）
  • 标识实体（把需求落地到每个字段）
  • 标识实体之间的关系
    • 写博客：user –> blog
    • 创建分类：user –> category
    • 关注：user –> user
    • 友链：links
    • 评论：user –user –blog

7.1、三大范式

为什么需要数据规范化？

  • 信息重复
  • 更新异常
  • 插入异常
    • 无法正常显示信息
  • 删除异常
    • 丢失有效的信息

三大范式

第一范式（1NF）

​ 原子性、要求数据库表的每一列都是不可分割的原子数据项

第二范式（2NF）

​ 前提：满足第一方式

​ 每张表只描述一件事情、消除依赖的传递性

第三范式（3NF）

​ 前提：满足第一方式和第二范式

​ 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

规范性 和 性能的问题

关联查询的表不得超过三张表

  • 考虑商业化的需求和目标，（成本，用户体验）数据库的性能更加重要
  • 在规范性能的问题的时候，需要适当的考虑一下规范性
  • 故意给某些表增加一些冗余字段。（从多表查询中变为单表查询）
  • 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）

8、SQL 语句性能优化

  1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

  2. 应尽量避免在 where 子句中对字段进行 null 值判断，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默 认值。

  3. 应尽量避免在 where 子句中使用 != 或 <> 操作符， MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。

  4. 应尽量避免在 where 子句中使用 or 来连接条件， 否则将导致引擎放弃使用索引而进行全表扫描， 可以 使用 UNION 合并查询：select id from t where num=10 union all select id from t where num=20

  5. in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用 between 就不要用 in 了：Select id from t where num between 1 and 3

  6. 下面的查询也将导致全表扫描：select id from t where name like '%abc%’ 或者 select id from t where name like ‘%abc’ 若要提高效率，可以考虑全文检索。而 select id from t where name like ‘abc%’ 才用到索引

  7. 如果在 where 子句中使用参数，也会导致全表扫描。

  8. 应尽量避免在 where 子句中对字段进行表达式操作，应尽量避免在where子句中对字段进行函数操作

  9. 很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b) 用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)

  10. 索引 固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

  11. 应尽可能的避免更新 clustered 索引数据列， 因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

  12. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

  13. 尽可能的使用 varchar/nvarchar 代替 char/nchar ， 因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

  14. 最好不要使用返回所有字段，select * from t ，用具体的字段列表代替 “*”，不要返回用不到的任何字段。

  15. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

  16. 使用表的别名( Alias )：当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个 Column 上.这样一来,就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。

  17. 使用 “ 临时表 ” 暂存中间结果，简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb 中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中 “共享锁” 阻塞 “更新锁”，减少了阻塞，提高了并发性能。

  18. 一些 SQL 查询语句应加上 nolock，读、写是会相互阻塞的，为了提高并发性能，对于一些查询，可以加上 nolock，这样读的时候可以允许写，但缺点是可能读到未提交的脏数据。使用 nolock 有3条原则。查询的结果用于 “插、删、改” 的不能加 nolock ，查询的表属于频繁发生页分裂的，慎用 nolock ！使用临时表一样可以保存 “数据前影”，起到类似 Oracle 的 undo 表空间的功能，能采用临时表提高并发性能的，不要用 nolock 。

  19. 常见的简化规则如下：不要有超过 5 个以上的表连接（JOIN），考虑使用临时表或表变量存放中间结果。少用子查询，视图嵌套不要过深,一般视图嵌套不要超过2个为宜。

  20. 将需要查询的结果预先计算好放在表中，查询的时候再Select。这在SQL7.0以前是最重要的手段。例如医院的住院费计算。

  21. 用 OR 的字句可以分解成多个查询，并且通过 UNION 连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用 UNION all 执行的效率更高.多个 OR 的字句没有用到索引，改写成 UNION 的形式再试图与索引匹配。一个关键的问题是否用到索引。

  22. 在 IN 后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。

  23. 尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是控制流语言的集合，速度当然快。反复执行的动态SQL,可以使用临时存储过程，该过程（临时表）被放在Tempdb中。

  24. 当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用 配制线程数量<最大连接数，启用 SQL SERVER 的线程池来解决,如果还是数量 = 最大连接数+5，严重的损害服务器的性能。

  25. 查询的关联同写的顺序

    1
    2
    3
    4
    5
    select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = ‘JCNPRH39681’ （A = B ,B = ‘号码’）

    select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = ‘JCNPRH39681’ and b.referenceid = ‘JCNPRH39681’ （A = B ,B = ‘号码’， A = ‘号码’）

    select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = ‘JCNPRH39681’ and a.personMemberID = ‘JCNPRH39681’ （B = ‘号码’， A = ‘号码’）

  26. 尽量使用 exists 代替 select count(1) 来判断是否存在记录，count 函数只有在统计表中所有行数时使用，而且 count(1) 比 count(*) 更有效率。

  27. 尽量使用 “>=”，不要使用 “>”。

  28. 索引的使用规范：索引的创建要与应用结合考虑，建议大的 OLTP 表不要超过 6 个索引；尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过 index index_name 来强制指定索引；避免对大表查询时进行 table scan，必要时考虑新建索引；在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用；要注意索引的维护，周期性重建索引，重新编译存储过程。

  29. 下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    SELECT * FROM record WHERE substrINg(card_no,1,4)=’5378’ (13秒)

    SELECT * FROM record WHERE amount/30< 1000 （11秒）

    SELECT * FROM record WHERE convert(char(10),date,112)=’19991201’ （10秒）

    # 分析：
    # WHERE子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样：

    SELECT * FROM record WHERE card_no like ‘5378%’ （< 1秒）

    SELECT * FROM record WHERE amount< 1000*30 （< 1秒）

    SELECT * FROM record WHERE date= ‘1999/12/01’ （< 1秒）

  30. 当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新!

  31. 在所有的存储过程中，能够用 SQL 语句的，绝不会用循环去实现!

    (例如：列出上个月的每一天，用 connect by 去递归查询一下，绝不会去用循环从上个月第一天到最后一天)

  32. 选择最有效率的表名顺序(只在基于规则的优化器中有效)：

    oracle 的解析器按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表(基础表 driving table)将被最先处理，在 FROM 子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

  33. 提高 GROUP BY 语句的效率, 可以通过将不需要的记录在 GROUP BY 之前过滤掉.下面两个查询返回相同结果，但第二个明显就快了许多.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    # 低效:

    SELECT JOB , AVG(SAL)

    FROM EMP

    GROUP BY JOB

    HAVING JOB =’PRESIDENT’

    OR JOB =’MANAGER’

    # 高效:

    SELECT JOB , AVG(SAL)

    FROM EMP

    WHERE JOB =’PRESIDENT’

    OR JOB =’MANAGER’

    GROUP BY JOB

  34. sql 语句用大写，因为 oracle 总是先解析 sql 语句，把小写的字母转换成大写的再执行。

  35. 别名的使用，别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快1.5倍。

  36. 避免死锁，在你的 存储过程和触发器中访问同一个表时总是以相同的顺序;事务应经可能地缩短，在一个事务中应尽可能减少涉及到的数据量;永远不要在事务中等待用户输入。

  37. 避免使用临时表，除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替;大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在 TempDb 数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。

  38. 最好不要使用触发器，触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程;如果能够使用约束实现的，尽量不要使用触发器;不要为不同的触发事件(Insert，Update和Delete) 使用相同的触发器;不要在触发器中使用事务型代码。

  39. 索引创建规则：

    • 表的主键、外键必须有索引；
    • 数据量超过300万的表应该有索引；
    • 经常与其他表进行连接的表，在连接字段上应该建立索引；
    • 经常出现在 Where 子句中的字段，特别是大表的字段，应该建立索引；
    • 索引应该建在选择性高的字段上；
    • 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
    • 复合索引的建立需要进行仔细分析，尽量考虑用单字段索引代替；
    • 正确选择复合索引中的主列字段，一般是选择性较好的字段；
    • 复合索引的几个字段是否经常同时以 AND 方式出现在 Where 子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
    • 如果复合索引中包含的字段经常单独出现在 Where 子句中，则分解为多个单字段索引；
    • 如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
    • 如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
    • 频繁进行数据操作的表，不要建立太多的索引；
    • 删除无用的索引，避免对执行计划造成负面影响；
    • 表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。
    • 尽量不要对数据库中某个含有大量重复的值的字段建立索引。

  40. mysql查询优化总结：使用慢查询日志去发现慢查询，使用执行计划去判断查询是否正常运行，总是去测试你的查询看看是否他们运行在最佳状态下。久而久之性能总会变化，避免在整个表上使用 count(*) ,它可能锁住整张表，使查询保持一致以便后续相似的查询可以使用查询缓存，在适当的情形下使用 GROUP BY 而不是 DISTINCT，在 WHERE, GROUP BY 和 ORDER BY 子句中使用有索引的列，保持索引简单,不在多个索引中包含同一个列，有时候 MySQL 会使用错误的索引,对于这种情况使用 USE INDEX，检查使用 SQL_MODE=STRICT 的问题，对于记录数小于5的索引字段，在 UNION 的时候使用 LIMIT 不是是用 OR。

    为了 避免在更新前 SELECT，使用 INSERT ON DUPLICATE KEY 或者 INSERT IGNORE ,不要用 UPDATE 去实现，不要使用 MAX,使用索引字段和 ORDER BY 子句，LIMIT M，N实际上可以减缓查询在某些情况下，有节制地使用，在 WHERE 子句中使用 UNION 代替子查询，在重新启动的 MySQL，记得来温暖你的数据库，以确保您的数据在内存和查询速度快，考虑持久连接，而不是多个连接，以减少开销，基准查询，包括使用服务器上的负载，有时一个简单的查询可以影响其他查询，当负载增加您的服务器上，使用SHOW PROCESSLIST 查看慢的和有问题的查询，在开发环境中产生的镜像数据中 测试的所有可疑的查询。

  41. MySQL 备份过程:

    从二级复制服务器上进行备份。在进行备份期间停止复制，以避免在数据依赖和外键约束上出现不一致。彻底停止MySQL，从数据库文件进行备份。

    如果使用 MySQL dump 进行备份，请同时备份二进制日志文件 – 确保复制没有中断。不要信任 LVM 快照，这很可能产生数据不一致，将来会给你带来麻烦。为了更容易进行单表恢复，以表为单位导出数据 – 如果数据是与其他表隔离的。

    当使用 mysqldump 时请使用 –opt。在备份之前检查和优化表。为了更快的进行导入，在导入时临时禁用外键约束。

    为了更快的进行导入，在导入时临时禁用唯一性检测。在每一次备份后计算数据库，表以及索引的尺寸，以便更够监控数据尺寸的增长。

    通过自动调度脚本监控复制实例的错误和延迟。定期执行备份。

  42. 查询缓冲并不自动处理空格，因此，在写SQL语句时，应尽量减少空格的使用，尤其是在SQL首和尾的空格(因为，查询缓冲并不自动截取首尾空格)。

  43. member 用 mid 做标准进行分表方便查询么？一般的业务需求中基本上都是以 username 为查询依据，正常应当是 username 做 hash 取模来分表吧。分表的话 mysql 的partition 功能就是干这个的，对代码是透明的；在代码层面去实现貌似是不合理的。

  44. 我们应该为数据库里的每张表都设置一个 ID 做为其主键，而且最好的是一个 INT 型的（推荐使用 UNSIGNED ），并设置上自动增加的 AUTO_INCREMENT 标志。

  45. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

  46. MySQL 查询可以启用高速查询缓存。这是提高数据库性能的有效 Mysql 优化方法之一。当同一个查询被执行多次时，从缓存中提取数据和直接从数据库中返回数据快很多。

  47. EXPLAIN SELECT 查询用来跟踪查看效果

    使用 EXPLAIN 关键字可以让你知道 MySQL 是如何处理你的 SQL 语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。

  48. 当只要一行数据时使用 LIMIT 1

  49. 当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去 fetch 游标，或是你也许会去检查返回的记录数。在这种情况下，加上 LIMIT 1 可以增加性能。这样一样，MySQL 数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

  50. 选择表合适存储引擎：

    myisam: 应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的。

    Innodb：事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。（Innodb有效地降低删除和更新导致的锁定）。对于支持事务的InnoDB类型的表来说，影响速度的主要原因是 AUTOCOMMIT 默认设置是打开的，而且程序没有显式调用 BEGIN 开始事务，导致每插入一条都自动提交，严重影响了速度。可以在执行 sql 前调用 begin，多条 sql 形成一个事务（即使 autocommit 打开也可以），将大大提高性能。

  51. 优化表的数据类型,选择合适的数据类型：

    原则：更小通常更好，简单就好，所有字段都得有默认值,尽量避免null。

    例如：数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型.( mediumint 就比 int 更合适)

    比如时间字段：datetime 和 timestamp, datetime占用8个字节，而 timestamp 占用4个字节，只用了一半，而 timestamp 表示的范围是1970—2037适合做更新时间

    MySQL 可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。

    因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，

    在定义邮政编码这个字段时，如果将其设置为 CHAR(255) ,显然给数据库增加了不必要的空间，

    甚至使用 VARCHAR 这种类型也是多余的，因为 CHAR(6) 就可以很好的完成任务了。同样的，如果可以的话，

    我们应该使用 MEDIUMINT 而不是 BIGIN 来定义整型字段。

    应该尽量把字段设置为 NOT NULL，这样在将来执行查询的时候，数据库不用去比较 NULL 值。

    对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为 ENUM 类型。因为在 MySQL 中，ENUM 类型被当作数值型数据来处理，

    而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

  52. 字符串数据类型：char，varchar，text选择区别

  53. 任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

  • Copyrights © 2022-2023 hqz

请我喝杯咖啡吧~

支付宝
微信