MySQL数据库和SQL优化与JDBC_jdbc sql 优化器-优快云博客

本文探讨了MySQL中InnoDB与MyISAM存储引擎的区别，强调InnoDB对事务的支持。讲解了SQL查询优化技巧，如避免全表扫描、使用索引等。此外，还讨论了数据库创建表的注意事项，如使用varchar、建立主键和索引等。同时，提到了MySQL的执行计划、分库分表、锁机制（表级锁、行级锁、乐观锁、悲观锁）以及数据库性能调优策略。最后，简述了JDBC在数据库连接中的作用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

为什么InnoDB支持事务而myisam不支持

MyISAM:这个是默认类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法.与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。如果执行大量的SELECT，MyISAM是更好的选择。
InnoDB:这种类型是事务安全的.它与BDB类型具有相同的特性,它们还支持外键.InnoDB表格速度很快.具有比BDB还丰富的特性,因此如果需要一个事务安全的存储引擎,建议使用它.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑，应该使用InnoDB表

SQL语句中关于查询语句的优化你们是怎么做的？

应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
in 和 not in 也要慎用，否则会导致全表扫描
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
使用连接（JOIN）来代替子查询(Sub-Queries)
使用联合(UNION)来代替手动创建的临时表

MySQL索引使用限制

不要在列上进行运算

select * from users where YEAR(adddate)<2007; 
将在每个行上进行运算，这将导致索引失效而进行全表扫描，
因此我们可以改成select * from users where adddate<‘2007-01-01’;

like查询是以%开头不使用索引

    如果使用like。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
    select * from users where name like '%aaa%'不会使用索引
    select * from users where name like 'aaa%'可以使用索引

使用短索引

例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，
那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

索引不会包含NULL列，IS NULL /IS NOT NULL不使用索引

复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ' '字符串

最左匹配，任何一个索引的最左前缀可以通过使用优化器来查找行

不按索引最左列开始查询（多列索引） 例如：
index(‘c1’, ‘c2’, ‘c3’) ，
where ‘c2’ = ‘aaa’ 不使用索引,
where ‘c2’ = ‘aaa’ and ‘c3’ = ‘sss’ 不能使用索引。
where ‘c1’ = ‘aaa’ and ‘c2’ = ‘bbb’ 可以使用索引。
index('c1')靠最左可以使用索引。

多列索引，不是使用的第一部分，则不会使用索引

查询中某个列有范围查询，则其右边的所有列都无法使用查询（多列查询）。
where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ 
该查询只会使用索引中的前两列,c3将不能使用到索引,因为like是范围查询。

检索排序

一个查询语句中，既有检索又有排序并且是不同的字段，且这两个列上都有单列索引（独立索引），
那么只有其中一个列用到索引，因为查询优化器在做检索和排序中不能同时使用两个不同的索引。

索引散列度

通过索引扫描的记录超过了表总行数的30%（估计值），则查询优化器认为全表扫描的效率更高，所以会变成全表扫描查询。

隐式转换：如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

隐式转换导致的索引失效。比如，表的字段tu_mdn定义为varchar(20),
但在查询时把该字段作为number类型当做where条件,这样会导致索引失效. 
错误的例子：select * from test where tu_mdn=13333333333; 
正确的例子：select * from test where tu_mdn='13333333333’;

条件中有or

即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

使用全表扫描要比使用索引快,则不使用索引，数据唯一性差（一个字段的取值只有几种时）的字段不要使用索引

比如性别，只有两种可能数据。意味着索引的二叉树级别少，多是平级。这样的二叉树查找无异于全表扫描

频繁更新的字段不要使用索引

比如logincount登录次数，频繁变化导致索引也频繁变化，增大数据库工作量，降低效率

where 子句里对索引列使用不等于（<>），使用索引效果一般，不使用索引

数据库创建表的时候会有哪些考虑呢？

项目中使用的是MySQL数据库，数据库创建表时要考虑：

大数据字段最好剥离出单独的表，以便影响性能
使用varchar，代替char，这是因为varchar会动态分配长度，char指定为20，即时你存储字符“1”，它依然是20的长度
给表建立主键，看到好多表没主键，这在查询和索引定义上将有一定的影响
避免表字段运行为null，如果不知道添加什么值，建议设置默认值，特别int类型，比如默认值为0，在索引查询上，效率立显。
建立索引，聚集索引则意味着数据的物理存储顺序，最好在唯一的，非空的字段上建立，其它索引也不是越多越好，索引在查询上优势显著，在频繁更新数据的字段上建立聚集索引，后果很严重，插入更新相当忙。
组合索引和单索引的建立，要考虑查询实际和具体模式

有了解过大数据层面的分库分表吗？以及mysql的执行计划吗？

分库：通过Mycat结点来管理不同服务器上的数据库，每个表最多存500万条记录
分表：重直切割，水平切割

MySql提供了EXPLAIN语法用来进行查询分析，在SQL语句前加一个"EXPLAIN"即可。mysql中的explain语法可以帮助我们改写查询，优化表的结构和索引的设置，从而最大地提高查询效率。

有了解过数据库中的表级锁和行级锁吗？乐观锁和悲观锁你有哪些了解？

MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；InnoDB存储引擎既支持行级锁（ row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL主要锁的特性可大致归纳如下:

表级锁：开销小，加锁快；不会出现死锁(因为MyISAM会一次性获得SQL所需的全部锁)；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
乐观锁：通过version版本字段来实现
悲观锁：通过for update来实现

Sql层面：

一、悲观锁

排它锁，当事务在操作数据时把这部分数据进行锁定，直到操作完毕后再解锁，其他事务操作才可操作该部分数据。这将防止其他进程读取或修改表中的数据。
实现：大多数情况下依靠数据库的锁机制实现

一般使用 select ...for update 对所选择的数据进行加锁处理，例如select * from account where name=”Max” for update，这条sql 语句锁定了account 表中所有符合检索条件（name=”Max”）的记录。本次事务提交之前（事务提交时会释放事务过程中的锁），外界无法修改这些记录。

二、乐观锁

如果有人在你之前更新了，你的更新应当是被拒绝的，可以让用户重新操作。
实现：大多数基于数据版本（Version）记录机制实现

具体可通过给表加一个版本号或时间戳字段实现，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断当前版本信息与第一次取出来的版本值大小，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，拒绝更新，让用户重新操作。

代码层面：

悲观锁:一段执行逻辑加上悲观锁,不同线程同时执行时,只能有一个线程执行,其他的线程在入口处等待,直到锁被释放.
乐观锁:一段执行逻辑加上乐观锁,不同线程同时执行时,可以同时进入执行,在最后更新数据的时候要检查这些数据是否被其他线程修改了(版本和执行初是否相同),没有修改则进行更新,否则放弃本次操作。

Mysql优化有没有工具

三个MySQL性能测试工具：The MySQL Benchmark Suite、MySQL super-smack、MyBench。除了第一个为MySQL性能测试工具，其他两个都为压力测试工具。

你有了解mysql的隔离级别吗？mysql默认的隔离级别是什么？

数据库事务的隔离级别有四种，隔离级别高的数据库的可靠性高，但并发量低，而隔离级别低的数据库可靠性低，但并发量高，系统开销小。

READ UNCIMMITTED（未提交读）
READ COMMITTED（提交读）
REPEATABLE READ（可重复读）
SERIALIZABLE（可串行化）

mysql默认的事务处理级别是'REPEATABLE-READ',也就是可重复读。

怎样进行数据库性能调优

一：应用程序优化

把数据库当作奢侈的资源看待，在确保功能的同时，尽可能少地动用数据库资源。
不要直接执行完整的SQL 语法，尽量通过存储过程实现数据库操作。
客户与服务器连接时，建立连接池，让连接尽量得以重用，以避免时间与资源的损耗。
非到不得已，不要使用游标结构，确实使用时，注意各种游标的特性。

二：基本表设计优化

表设计遵循第三范式。在基于表驱动的信息管理系统中，基本表的设计规范是第三范式。
分割表。分割表可分为水平分割表和垂直分割表两种：水平分割是按照行将一个表分割为多个表。
引入中间表。

三：数据库索引优化

索引是建立在表上的一种数据组织，它能提高访问表中一条或多条记录的特定查询效率。

聚集索引：该索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引确定表中数据的物理顺序。
非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同.

MySQL存储过程

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

存储过程的优点：

增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

MySQL存储过程的创建

语法：

CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名数据类型[,[IN|OUT|INOUT] 参数名数据类型…]]) [特性 ...] 过程体

DELIMITER //
  CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
    //
DELIMITER ;

分隔符：MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些码；“DELIMITER ;”的意为把分隔符还原。

参数：存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:

IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT:该值可在存储过程内部被改变，并可返回
INOUT:调用时指定，并且可被改变和返回

过程体：过程体的开始与结束使用BEGIN与END进行标识。

怎么实现数据量大、并发量高的搜索

创建Elasticsearch/solr索引库，数据量特别大时采用Elasticsearch/solr分布式集群。

JDBC的理解

JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。有了JDBC，向各种关系数据发送SQL语句就是一件很容易的事。换言之，有了JDBC API，就不必为访问Sybase数据库专门写一个程序，为访问Oracle数据库又专门写一个程序，或为访问Informix数据库又编写另一个程序等等，程序员只需用JDBC API写一个程序就够了，它可向相应数据库发送SQL调用。