15.JAVA之事务，表强化、关联，索引、JDBC、Git、SQL优化

太彧

于 2023-04-27 20:16:40 发布

阅读量159

点赞数

文章标签： java 数据库 sql

本文链接：https://blog.csdn.net/weixin_56027329/article/details/130383081

版权

一、事务transaction

1.1概述

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

1.2事务4个特性ACID

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

1、原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

2、一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

3、隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

4、持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

1.3隔离级别

事务隔离分为不同级别，包括

1、读未提交（Read uncommitted）安全性最差,可能发生并发数据问题,性能最好

2、读提交（read committed）效率较低，安全性较高 Oracle默认的隔离级别

3、可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般(Mysql默认级别)

4、串行化（Serializable）表级锁，读写都加锁，效率低下,安全性高,不能并发

1.4查询mysql的隔离级别

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。

1、开启事务：start transaction或者begin；

2、结束事务：commit（提交事务）或rollback（回滚事务）一般不用。

在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！

SELECT @@tx_isolation;

Repeatable Read（可重读）

MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

二、表关联association

表table代表了生活中一个主体，如部门表dept，员工表emp。表关联则代表了表之间的关系，如：部门和员工，商品和商品分类，老师和学生，教室和学生。

同时，也要知道，表并不都有关系，它们形成自己的小圈子。如商品和商品详情一圈，部门和员工一圈，出圈就可能没关系了，如商品和员工无关，商品和学生无关。

下面我们讨论表的关系分为四种：

1、一对一 one to one QQ和QQ邮箱，员工和员工编号

2、一对多 one to many 最常见，部门和员工，用户和订单

3、多对一 many to one 一对多反过来，员工和部门，订单和用户

4、多对多 many to many 老师和学生，老师和课程

三、表强化：约束 constraints

1 非空约束 not null phone VARCHAR(20) NOT NULL,

2 唯一约束 unique phone VARCHAR(20) UNIQUE NOT NULL,

3 主键约束 primary key PRIMARY KEY (id)

4 外键约束 forgrein key FOREIGN KEY(user_id) REFERENCES tb_user(id)

5 默认约束 default sex CHAR(2) DEFAULT '男', #默认值

6 检查约束 check(基本不用)

四、索引 index

4.1概述

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。

4.2分类

1、单值索引：一个索引只包括一个列，一个表可以有多个列。

2、唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创建唯一索引。

3、复合索引：一个索引同时包括多列。

4.3创建索引

#查看索引，主键会自动创建索引

SHOW INDEX FROM empext(表名)

#创建索引

#CREATE INDEX empext_index(索引名) ON empext (cardno)(表名(字段))

# 创建唯一索引

ALTER TABLE empext(表名) ADD UNIQUE (phone)(字段)

ALTER TABLE empext(表名) ADD UNIQUE (cardno)(字段)

# 创建复合索引

ALTER TABLE empext(表名) ADD INDEX idx_phone_cardno(索引名) (phone,cardno)(字段)

# 创建复合唯一索引

ALTER TABLE empext ADD UNIQUE idx_phone_cardno(phone,cardno)

# 删除索引

ALTER TABLE empext DROP INDEX cardno

4.4索引扫描类型

type：

1、 ALL 全表扫描，没有优化，最慢的方式

2、 index 索引全扫描，其次慢的方式

3、 range 索引范围扫描，常用语<，<=，>=，between等操作

4、 ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中

5、 eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询

6、 const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况

7、 null MySQL不访问任何表或索引，直接返回结果

从最好到最差的类型：

system > const > eq_ref > ref > range > index > ALL

possible_keys：显示可能应用在这张表中的索引

key：真正使用的索引方式

4.5最左特性

EXPLAIN

SELECT * FROM empext WHERE phone='13572801415'

EXPLAIN

SELECT * FROM empext WHERE cardno='610113'

EXPLAIN

SELECT * FROM empext WHERE phone='13572801415' AND cardno='610113'

EXPLAIN

SELECT * FROM empext WHERE cardno='610113' AND phone='13572801415'

当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则，也称为最左特性。

4.6为何索引快

明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。

其过程如下图，先到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。

1、排序，tree结构，类似二分查找

2、索引表小

4.7索引的优缺点

优点:

1、索引是数据库优化

2、表的主键会默认自动创建索引

3、大量降低数据库的IO磁盘读写成本，极大提高了检索速度

4、索引事先对数据进行了排序，降低查询数据排序的成本，降低CPU的消耗

缺点:

1、索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间

2、索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”

3、虽然索引大大提高了查询的速度，但反向影响了增、删、改操作的效率。如表中数据变化之后，会造成索引内容不正确，需要更新索引表信息，如果数据量非常巨大，重新创建索引的时间就大大增加

4、随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引。

五、多表联查join

5.1笛卡尔积 Cartesian product

多表查询是指基于两个和两个以上的表的查询。在实际应用中，查询单个表可能不能满足你的需求，如显示员工表emp中不只显示deptno，还要显示部门名称，而部门名称dname在dept表中。

#把两个表的数据都拼接起来

SELECT * FROM dept,emp

上面这种查询两个表的方式称为：笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。

这点很值得注意，实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，现在内存中构建一个大大的结果集，然后再进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。

5.2三种连接join

1、内连接 inner join

2、左（外）连接 left join

3、右（外）连接 right join

案例：列出research部门下的所有员工的信息

SELECT * FROM emp

WHERE deptno = ( SELECT deptno FROM dept WHERE dname='research' )

案例：用内链接 INNER JOIN 实现上面的需求

select * from scores as a inner join students as b
on a.sno=b.sno
where a.sno=101;

案例：用内链接 left join 实现上面的需求

select * from scores as a left join students as b
on a.sno=b.sno
where a.sno=101;

案例：用内链接right join 实现上面的需求

select * from scores as a right join students as b
on a.sno=b.sno
where a.sno=101;

案例：列出tony的扩展信息
SELECT *FROM emp e INNER JOIN empext t

ON e.empno=t.empno

WHERE e.ename='tony'

5.3inner join、left join、right join的区别

1、INNER JOIN两边都对应有记录的才展示，其他去掉。

2、LEFT JOIN左边表中的数据都出现，右边没有数据以NULL填充。

3、 RIGHT JOIN右边表中的数据都出现，左边没有数据以NULL填充。

六、SQL面试题

1.23.1  查询所有记录
select * from emp

1.23.2  只查询指定列
SELECT id,ename,sal from emp

1.23.3  查询id为100的记录
select * from emp where id=100

1.23.4  模糊查询记录
select * from emp where ename like 'j%' #以j开头的记录

select * from emp where ename like '%k' #以k结束的记录

select * from emp where ename like '%a%' #包含a的记录

select * from emp where ename not like 'j%' #不 以j开头的记录

1.23.5  查询之间范围之间的所有记录
select * from emp where sal between 8000 and 20000 #[8000,20000]

select * from emp where sal>8000 and sal<20000 #(8000,20000)

1.23.6  查询满足两个条件的记录
SELECT * from user where age=19 or age=20 #或者关系

SELECT * from user where age in (19,20)

SELECT * from user where age=20 and name='xiongda' #并且关系

1.23.7  查询用户住址
SELECT distinct addr from user

1.23.8  查询19岁人的名字  distinct (去重)
SELECT distinct name from user where age=19

1.23.9  按age升序查询记录
SELECT * from user order by age asc #升序,默认

SELECT * from user order by age desc #降序

1.23.10    以name升序、age降序查询记录
SELECT * from user order by name asc,age desc #name升序,age降序

1.23.11      查询总人数
SELECT count(*) from user

SELECT count(1) from user

SELECT count(id) from user

1.23.12      查询各个城市的人数
select addr,count(addr) from user group by addr #聚合函数以外的列要分组

1.23.13      查询至少有2人的地址
SELECT addr,count(name) from user GROUP BY addr 

SELECT addr,count(name) X from user GROUP BY addr  having X>2 #条件过滤

1.23.14      查询记录中最年长和最年轻
select max(age),min(age) from user

1.23.15      查询大于平均年龄的记录
select * from user where age > (select avg(age) from user)

1.23.16      查询年龄最大的用户信息
select * from user where age = (select max(age) from user)

1.23.17      查询各部门的最高薪
select id,name,sal,max(sal) from emp GROUP BY deptno

1.23.18   查询各科的平均工资
select avg(comm) from emp

select ROUND(avg(comm),1) from emp #保留一位小数

SELECT * from emp where comm > (select avg(comm) from emp)

1.23.19   查询id是100或200的记录
select * from emp where id=100

select * from emp where id=200

select * from emp where id=100 or id=200

select * from emp where id in(100,200)

select * from emp where id=200

#UNION  #合并重复内容

union all #不合并重复内容

select * from emp where id=200

1.23.20      查询存在部门的员工信息
select * from emp where deptno in (select id from dept)

1.23.21      查询没划分部门的员工信息
select * from emp where deptno not in(select id from dept)

1.23.22      查询同名的员工记录
select * from emp WHERE ename in (

    select ename from emp GROUP BY ename HAVING count(ename)>1

)

1.23.23      全部学生按出生年月排行
select * from students order by sbirthday #数值从小到大，年龄就是大到小了

1.23.24      每个班上最小年龄的学员
select sname,class,max(sbirthday) from students group by class #数字最大，年龄是最小的

1.23.25      查询学生的姓名和年龄
select sname,year(now())-year(sbirthday) age from students

1.23.26      查询男教师及其所上的课程
#inner join内连接写法
SELECT * from teachers a inner JOIN courses b on a.tno=b.tno  AND a.tsex='男'
#笛卡尔积写法
SELECT * from teachers a,courses b where a.tno=b.tno AND a.tsex='男'

1.23.27   查询每个老师教的课程
SELECT c.cname,t.tname,t.prof,t.depart

FROM teachers t

LEFT JOIN courses c ON t.tno = c.tno

1.23.28   查询女老师的信息
SELECT * FROM teachers t

LEFT JOIN courses c ON t.tno = c.tno

where t.tsex='女'
第一种先连接数据后过滤数据，假如数据量很大，第一种中间过程要构建巨大的临时表。而第二种方式先过滤数据，构建的中间结果集自然就变的很小。所占内存，所加工的时间所网络传输的时间都变少了，所以效率高。

1.23.29   查询得分前3名的学员信息
select * from scores order by degree desc limit 3 #前三条

select * from scores order by degree desc limit 1,3

#从1位置(第二条)开始,总共取3条

1.23.30   查询课程是“计算机导论”的，得分前3名的学员信息
select * from scores where cno = (select cno from courses where cname='计算机导论')

order by degree desc limit 3

1.23.31      课程号“3-105”的倒数最后3名学员排行
select * from scores  where cno='3-105' order by degree limit 3

七、JDBC

7.1概述

JDBC（Java Database Connectivity）。但它只是规范，不做具体实现。于是数据库厂商又根据JDBC标准，实现自家的驱动Driver。如：mysql驱动com.mysql.cj.jdbc.Driver，Oracle的驱动oracle.jdbc.OracleDriver。有了这套解决方案，java就可以访问数据库中的数据了。

用来把java程序和数据库做一个连接。

可以理解成是java程序操作数据库的一套标准技术。

JDBC就是一套标准,本质上就是一堆的工具类一堆方法。

public interface Connection extends Wrapper, AutoCloseable {}

public interface Statement extends Wrapper, AutoCloseable {}

public interface PreparedStatement extends Statement {}

public interface CallableStatement extends PreparedStatement {}

public interface ResultSet extends Wrapper, AutoCloseable {}

Java中提倡面向接口开发，而最经典的接口设计莫过于JDBC数据库接口。

Connection链接、Statement语句、PreparedStatement预处理语句、CallableStatement存储过程、ResultSet结果集。

调用方式有三种：Statement语句、PreparedStatement预处理语句、CallableStatement存储过程(不怎么用)，推荐使用第二种PreparedStatement，防止SQL注入，其也是预编译性能高。

7.2IDEA导入jar包

1、创建工程-Eile-New-project-next-next-输入工程名-Finish。

2、创建lib目录-选中工程-右键-new-directory-输入lib-确定。

3、往lib里加jar包(找到jar包直接复制,选中lib粘贴)

4、创建Module-选中工程-右键-new-Module-new--输入Module名-Finsh

5、File-Project Structure-Libraries-点加号-选java-找到并选中jar包-ok-选择module-ok-Apply-ok

7.3测试JDBC

package cn.tedu;

import java.sql.*;
import java.util.Scanner;

//测试 JDBC
//JDBC用来把java程序和数据库连接起来,目的是通过java代码操作数据库
public class Testjdbc {
    public static void main(String[] args) throws Exception {
//                method();//普通方式
                method2();//暴露SQL注入问题
//        method3();//解决SQL注入问题
    }
    //需求:按照 用户名 和 密码 查询用户信息
    private static void method3() throws Exception{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //TODO  改造 调用工具类,获取和数据库的连接
            conn = JDBCUtils.getConnection();
            //获取传输器Statement,执行SQL
            //        Statement st = conn.createStatement();//SQL注入攻击问题
            //        String sql = "select * from user where name='"+a+"' and pwd='"+b+"'";

            //获取传输器PrepareStatement,执行SQL
            //?叫做占位符,PreparedStatement只把SQL骨架发送给数据库
            String sql = "select * from user where name=? and pwd=?";
            ps = conn.prepareStatement(sql);
            //给SQL设置参数
            ps.setString(1, "jack");//第一个?设置参数
            ps.setString(2, "123");//第二个?设置参数
            //执行SQL
            rs = ps.executeQuery();
            //解析结果集
            while (rs.next()) {
                for (int i = 1; i <= 3; i++) {
                    //按照索引查
                    System.out.println(rs.getString(i));
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally{ //释放资源,一定要被执行 !!!
            //调用工具,完成关闭资源
            JDBCUtils.close(rs,ps,conn);
        }

    }
    //暴露SQL注入问题--发生了SQL拼接
    //需求:按照 用户名 和 密码 查询用户信息
    private static void method2() throws Exception {
        //中文查不出来数据:
        //String url="jdbc:mysql://localhost:3306/demo?characterEncoding=utf8";
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接
        String url="jdbc:mysql://localhost:3306/demo";
        String name="root";
        String pwd="root";
        Connection conn = DriverManager.getConnection(url,name,pwd);
        //获取传输器Statement,执行SQL
        Statement st = conn.createStatement();

        //TODO 测试SQL注入攻击问题
        //是指当用户只输入数据时,恶意包含了特殊的SQL语句,改变了SQL语义
        //导致只需要名字不需要密码也可以查看所有数据(数据泄露)
        //名字输入后面两种值,都会产生SQL攻击问题: jack'# 或者 jack' or '1=1
        String a = new Scanner(System.in).nextLine();
        String b = new Scanner(System.in).nextLine();
        String sql = "select * from user where name='"+a+"' and pwd='"+b+"'";
        //执行SQL
        ResultSet rs = st.executeQuery(sql);
        //解析结果集
        while(rs.next()){
            for (int i = 1; i <= 3 ; i++) {
                //按照索引查
                System.out.println(rs.getString(i));
            }
        }
        //释放资源
        rs.close();
        st.close();
        conn.close();
    }
    //使用JDBC查询user表的数据
    private static void method() throws Exception {
        //1,注册驱动(jar包)
        Class.forName("com.mysql.jdbc.Driver");
        //2,连接数据库
        //getConnection(1,2,3)-1是要连接哪个数据-2是用户名-3是密码
        Connection con = DriverManager.getConnection(
                //                "协议//服务器的名字:数据库的端口号/数据库名",
                //                "jdbc:mysql://localhost:3306/jdbctest",
                "jdbc:mysql:///demo",
                //url简写(使用本机使用默认的端口号)
                "root","root");
        //3,获取传输器Statement
        Statement st = con.createStatement();
        //4,执行SQL
        String sql = "select * from user";
        ResultSet rs = st.executeQuery(sql);
        //5,解析结果集
        while(rs.next()){ //next()判断有数据吗
            //有数据就一个一个解析id/name/pwd
            for (int i = 1; i <= 3; i++) {
                System.out.println( rs.getString(i) );//按索引查;
            }

            String id2 = rs.getString("id");//按索引查
            String name2 = rs.getString("name");//按索引查
            String pwd2 = rs.getString("pwd");//按索引查
            System.out.println(id2+name2+pwd2);
        }
        //6,释放资源
        rs.close();
        st.close();
        con.close();
    }
}

封装常写代码

package cn.tedu;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JDBCUtils {
    /**
     * 获取数据库的连接
     *使用static,减少空间开辟，节省内存
     * @return Connection
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接
        String url = "jdbc:mysql://localhost:3306/demo?characterEncoding=utf8";
        String name = "root";
        String pwd = "root";
        Connection conn = DriverManager.getConnection(url, name, pwd);
        return conn;
    }

    public static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
        if(rs != null){//为了防止空指针异常
            try{
                rs.close();
            }catch (Exception e){
                e.printStackTrace();
            }finally { //为了close时又异常
                rs = null;
            }
        }
        if(ps != null){//为了防止空指针异常
            try{
                ps.close();
            }catch (Exception e){
                e.printStackTrace();
            }finally { //为了close时又异常
                ps = null;
            }
        }
        if(conn != null){//为了防止空指针异常
            try{
                conn.close();
            }catch (Exception e){
                e.printStackTrace();
            }finally { //为了close时又异常
                conn = null;
            }
        }
    }
}

八、Git控制版本

8.1概述

版本控制（Version Control System），它功能强大，不仅能备份你的文件，还可以实现很多：

1、记录文件的所有历史变化

2、错误恢复到某个历史版本

3、多人协作开发编辑同一个文件

4、实现功能差异版本（分支），如：mysql、mariadb、percona三个分支

8.2主流的控制版本产品

简而言之，各有优缺点，git要配合github远程仓库，可以避免分布式损坏。显然最大的不同在于git是分布式的。svn有权限控制，避免全被clone克隆走。git适合纯代码，svn适合综合性文档管理，git+svn结合起来，完美。

8.3组成结构图

1、工作空间：用来保存项目的元数据和对象数据库的地方。这是 Git 中最重要的部分，从其它计算机克隆仓库时，拷贝的就是这里的数据。

2、本地索引：保存了下次将提交的文件列表信息，一般在 Git 仓库目录中。有时候也被称作“索引”，不过一般说法还是叫暂存区域。

3、本地仓库，之所以说git 快，大部分提交都是对本地仓库而言的，不依赖网络，最后一次会推送的到远程仓库。

4、远程仓库：可以看做是github，它是一个远程仓库，它提供web服务供大家方便下载、查看、提交、存储。

8.4常用命令

1、克隆仓库（第一次时） clone 克隆

2、对工作目录中文件新增、修改、删除操作

3、暂存文件，将文件的快照放入暂存区 add 新增

4、把暂存区的内容提交到本地仓库 commit 提交

5、把本地仓库的内容提交到远程仓库 push 推送

6、从远程仓库下载最新内容 pull 拉取

创建本地仓库：准备要提交的数据(找一个盘列出文件写数据)

一、创建远程仓库:
Gitee上右上角头像旁边的+ --点新建仓库--起个名字--设置开源--创建

二、在本地仓库上,执行Git命令(在要上传的地方输入cmd进入路径如图(上方的位置路径输入))

第一次提交

Git 全局设置:

1、git config --global user.name "Demons"
2、git config --global user.email "2364187481@qq.com"

git config --list(检查是否配置成功)

创建 git 仓库:

mkdir java2023day01
cd java2023day01
3、git init 
touch README.md
4、git add README.md或者git add .
5、git commit -m "first commit"
6、git remote add origin https://gitee.com/barrytt/java2023day01.git
7、git push -u origin "master"

已有仓库?

cd existing_git_repo
git remote add origin https://gitee.com/barrytt/java2023day01.git
git push -u origin "master"

(第一次push要输入邮箱和密码)，检查数据是否成功的提交到了Gitee。

第二次提交(在要上传的地方输入cmd进入路径)

1、git add .

2.git commit -m "first commit"

3、git push -u origin "master"

三、在gitee远程仓库里面更新内容，如何更新到本地仓库里

git pull命令将远程仓库更新的内容更新到本地仓库

把远程仓库中的内容下载到本地：

$ cd cgb #进入到要管理的目录中

$ git pull #从服务器拉取最新内容

$ cat README.md #进行文件内容查看

九、基础SQL优化

**9.1 查询SQL尽量不要使用select *，而是具体字段**

反例：SELECT * FROM student

正例：SELECT id,NAME FROM student

9.2 避免在where子句中使用or来连接条件

查询id为1或者薪水为3000的用户：

反例：SELECT * FROM student WHERE id=1 OR salary=30000

正例：

# 使用union all

SELECT * FROM student WHERE id=1

UNION ALL

SELECT * FROM student WHERE salary=30000

9.3 使用varchar代替char

反例：`deptname` char(100) DEFAULT NULL COMMENT '部门名称'

正例：`deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'

9.4 尽量使用数值替代字符串类型

1、主键（id）：primary key优先使用数值类型int，tinyint

2、性别（sex）：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint

3、支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡

4、服务状态（state）：1-开启、2-暂停、3-停止

5、商品状态（state）：1-上架、2-下架、3-删除

9.5 查询尽量避免返回大量数据

如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。

通常采用分页，一页习惯10/20/50/100条。

9.6 创建name字段的索引

提高查询速度的最简单最佳的方式

ALTER TABLE student ADD INDEX index_name (NAME)

9.7 优化like语句

模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效

反例：

SELECT id,NAME FROM student WHERE NAME LIKE '%1'

SELECT id,NAME FROM student WHERE NAME LIKE '%1%'

正例：

SELECT id,NAME FROM student WHERE NAME LIKE '1%'

9.8 字符串怪现象

反例：

#未使用索引

SELECT * FROM student WHERE NAME=123

正例：

#使用索引

SELECT * FROM student WHERE NAME='123'

9.9 索引不宜太多，一般5个以内

1、索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率

2、索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间

3、再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯定要

4、insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定

5、一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要

9.10 索引不适合建在有大量重复数据的字段上

如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

9.11 where限定查询的数据

数据中假定就一个男的记录

反例：SELECT id,NAME FROM student WHERE sex='男'

正例：SELECT id,NAME FROM student WHERE id=1 AND sex='男'

9.12 避免在where中对字段进行表达式操作

反例：

SELECT * FROM student WHERE id+1-1=+1

正例：

SELECT * FROM student WHERE id=+1-1+1

SELECT * FROM student WHERE id=1

9.13 避免在where子句中使用!=或<>操作符

9.14 去重distinct过滤字段要少

#索引失效

SELECT DISTINCT * FROM student

#索引生效

SELECT DISTINCT id,NAME FROM student

SELECT DISTINCT NAME FROM student

9.15 where中使用默认值代替null

环境准备：

#修改表，增加age字段，类型int，非空，默认值0

ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;

#修改表，增加age字段的索引，名称为idx_age

ALTER TABLE student ADD INDEX idx_age (age);

反例：SELECT * FROM student WHERE age IS NOT NULL

正例：SELECT * FROM student WHERE age>0

十、高级SQL优化

10.1 批量插入性能提升

大量数据提交，上千，上万，批量性能非常快，mysql独有

多条提交：

INSERT INTO student (id,NAME) VALUES(4,'齐雷');

INSERT INTO student (id,NAME) VALUES(5,'刘昱江');

批量提交：

INSERT INTO student (id,NAME) VALUES(4,'齐雷'),(5,'刘昱江');

10.2 批量删除优化

避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。

反例：

#一次删除10万或者100万+？

delete from student where id <100000;

#采用单一循环操作，效率低，时间漫长

for（User user:list）{

delete from student;

}

正例：

//分批进行删除，如每次500

for(){

delete student where id<500;

}

delete student where id>=500 and id<1000;

10.3 伪删除设计(工作中很常见)

商品状态（state）：1-上架、2-下架、3-删除

理由：

1、这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查

2、同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。

3、通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用

4、操作速度快，特别数据量很大情况下

10.4 提高group by语句的效率

可以在执行到该语句前，把不需要的记录过滤掉

反例：先分组，再过滤

select job，avg（salary） from employee

group by job

having job ='president' or job = 'managent';

正例：先过滤，后分组

select job，avg（salary） from employee

where job ='president' or job = 'managent'

group by job;

太彧

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
15.JAVA之事务，表强化、关联，索引、JDBC、Git、SQL优化

JAVA之事务，表强化、关联，索引，JDBC，Git控制版本，SQL基础、高级优化。
复制链接

扫一扫

15.JAVA之事务，表强化、关联，索引、JDBC、Git、SQL优化

一、事务transaction

1.1概述

1.2事务4个特性ACID

1.3隔离级别

1.4查询mysql的隔离级别

二、表关联association

三、表强化：约束 constraints

四、索引 index

4.1概述

4.2分类

4.3创建索引

4.4索引扫描类型

4.5最左特性

4.6为何索引快

4.7索引的优缺点

五、多表联查join

5.1笛卡尔积 Cartesian product

5.2三种连接join

5.3inner join、left join、right join的区别

六、SQL面试题

七、JDBC

7.1概述

7.2IDEA导入jar包

7.3测试JDBC

八、Git控制版本

8.1概述

8.2主流的控制版本产品

8.3组成结构图

8.4常用命令

九、基础SQL优化

9.1 查询SQL尽量不要使用select *，而是具体字段

9.2 避免在where子句中使用or来连接条件

9.3 使用varchar代替char

9.4 尽量使用数值替代字符串类型

9.5 查询尽量避免返回大量数据

9.6 创建name字段的索引

9.7 优化like语句

9.8 字符串怪现象

9.9 索引不宜太多，一般5个以内

9.10 索引不适合建在有大量重复数据的字段上

9.11 where限定查询的数据

9.12 避免在where中对字段进行表达式操作

9.14 去重distinct过滤字段要少

9.15 where中使用默认值代替null

十、高级SQL优化

10.1 批量插入性能提升

10.2 批量删除优化

10.3 伪删除设计(工作中很常见)

10.4 提高group by语句的效率

“相关推荐”对你有帮助么？

**9.1 查询SQL尽量不要使用select *，而是具体字段**