MySQL~从引擎选择、表的设计、索引设计、sql语句和连接池这五个方面优化数据库

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！

innodb是一个表对应一个文件（使用innodb存储引擎使用表ibdata1来保存数据和索引, .frm存储表结构）

InnoDB 和 MyISAM区别：

1、InnoDB支持主外键、事务；

2、InnoDB是行锁，操作时候只锁一行数据，适合高并发；MyISAM是表索；

3、InnoDB不仅缓存索引，还缓存真实数据；MyISAM只缓存索引；

4、InnoDB需要表空间大；

5、InnoDB关注事务，MyISAM关注性能（查）

使用选择

• MYISAM小巧玲珑, 节约空间, 速度快

• INNODB安全性好, 有事务的处理和多表多用户的操作

行锁升级表锁

---

事务的隔离级别为：可重复读时，

如果有索引（包括主键索引），以索引列为条件更新数据，会存在间隙锁，行锁，页锁，而锁住一些行。

如果没有索引，更新数据时会锁住整张表。

事务隔离级别为：串行化时，读写数据都会锁住整张表。（一次只能一个连接玩表）

众多资料中都说innodb使用的是行级锁，但实际上是有限制的。只有在你增删改查时匹配的条件字段带有索引时，innodb才会使用行级锁，在你增删改查时匹配的条件字段不带有索引时，innodb使用的将是表级锁。因为当你匹配条件字段不带有所引时，数据库会全表查询，所以这需要将整张表加锁,才能保证查询匹配的正确性。在生产环境中我们往往需要满足多人同时对一张表进行增删改查，所以就需要使用行级锁，所以这个时候一定要记住为匹配条件字段加索引。

提到行级锁和表级锁时我们就很容易联想到读锁和写锁，因为只有触发了读写锁，我们才会谈是进行行级锁定还是进行表级锁定。那么什么时候触发读锁，就是在你用select 命令时触发读锁，什么时候触发写锁，就是在你使用update,delete,insert时触发写锁，并且使用rollback或commit后解除本次锁定。

表的设计

---

• 表的设计优化就脱离不开那三个范式

将数据规范化

• 防止信息重复

• 防止数据更新异常

• 防止数据插入异常, 也就是无法显示信息

• 防止数据的删除异常,也就是丢失有效的信息

第一第二第三范式

• 第一范式就是原子性, 保存每一列不可再次分割

• 第二范式是必须先满足第一范式, 增加的是表的原子性, 也就是这张表只能专注于干一件事

• 第三范式必须满足第一和第二范式, 增加的是每一列中的数据都要和主键直接关联

但是,必须要知道的规范性和性能问题有时候是不可兼得的, 我们一般关联查询的表不能超过三张表

• 考虑商业化的需求和目标的时候, (成本和用户体验)数据库的性能是更加重要的

• 所以在规范的时候,需要适当的考虑下性能问题

• 如果只是单纯的分割表来增加表的数据,会造成一些冗余字段(从单表变成多表查询)

• 并且有时候在单表中增加索引,会提高查询的效率,但是也让表变得更加的沉重

• 所以在表的设计上考虑表的范式优化, 又得考虑不能太过分

索引优化

---

• 索引是帮助高效获取数据的数据结构

• 所以索引优化就是在列上使用适当的索引

• MySql索引类型有:唯一索引,B+树索引(主键(聚集)索引,非聚集索引),全文索引, 联合索引(复合索引)

索引分类

---

• 主键索引(Promary Key) , 聚集索引

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

• 非聚集索引

• • 使用非聚集索引是俩次查, 有回表问题, 可以使用联合索引代理非聚集索引

• 联合索引

• • 联合索引就是使用索引覆盖, 在非聚集索引上就可以找到数据. 但是会有最左匹配原则, 也就是只要第一个索引没有匹配到, 就会导致索引失效

• 唯一索引(Unique key)

• • 这个唯一是保证列的唯一,避免重复列的出现,唯一索引可以重复,多个列都可以标识唯一索引

• 常规索引(key/index)(默认的)

• • 默认的索引, index或者key 关键字设置

• 全文索引(FullText)

• • 在特定的数据库引擎下才有, 比如MySAM, 但是现在高版本的InnoDB中也有全文索引了

• 目的是为了快速查找数据比如text数据

索引数据结构

• 不使用hash表是因为不方便进行范围查找

• 不使用二叉树的原因是树的深度太高了

• 不使用b树的原因是有更好的b+树

• 首先要知道在b树和b+树中, 数据都是放在叶子上的, 而且已过父节点可以有多个子节点

• b树虽然已过节点可以有多个分支, 树的深度降低不少, 但是在进行范围查找的时候还是不如b+树

• 因为b+树在叶子节点上是连续的

索引使用原则

• 索引不是越多越好, 他只会提高查找效率, 删除修改, 增加的效率只会降低

• 不要给经常变更的数据加索引

• 小数据量有没有必要加索引

• 索引一般用在查询上, 在增加删除修改方便索引的效率是降低的, 所以用在差多改少的情况

• 如果某一个字段选择性很少, 比如性别, 类型, 他们的取值范围很小, 也不适合添加索引

sql语句优化

---

1. 排序的索引问题

• mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

2. like语句操作

• 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 和like “%aaa” 不会使用索引而like “aaa%”可以使用索引。

3. 在SQL文里面对某个字段经过运算后再与一个常量比较：那么这将会对运行性能产生很大的影响：

请看下面SQL的执行效率：

select * from iroomtypeprice where amount/30< 1000（11秒）

当改写成下面的SQL语句时效率明显提高了：

select * from iroomtypeprice where amount< 1000*30（<1秒）

语句1因为要对没没条记录的字段amount做一个/的运算，所以必须进行全表扫描，表的合适索引不会起作用；而语句二就会用到表上的合适索引。因此效率明显提高。

• 另外，在where后面尽量少用substring等函数，这样也可以提高执行效率，如：

select * from iroomtypeprice where substring(card_no,1,4)=‘5378’(13秒)改成

select * from iroomtypeprice where card_no like ‘5378%’(<1秒)

4. 不使用NOT IN和<>操作

• NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。

5. 使用连接（JOIN）来代替子查询(Sub-Queries)

• 使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）…替代。如果使用连接（JOIN）…来完成这个查询工作，速度将会快很多。

6. 尽量使用覆盖索引，只访问索引的查询（索引列和查询列一致），减少selec *

7. 存在NULL值条件，我们在设计数据库表时，索引列应该尽力避免NULL值出现，所以给索引类要加not null 或者unique，如果非要不可避免的要出现NULL值，也要给一个DEFAULT值，数值型可以给0、-1之类的， 字符串有时候给空串有问题，就给一个空格或其他。如果索引列是可空的，是不会给其建索引的，索引值是少于表的count(*)值的，所以这种情况下，执行计划自然就去扫描全表了。

最后

本人也收藏了一份Java面试核心知识点来应付面试，借着这次机会可以送给我的读者朋友们

目录：

Java面试核心知识点

一共有30个专题，足够读者朋友们应付面试啦，也节省朋友们去到处搜刮资料自己整理的时间！

Java面试核心知识点

已经有读者朋友靠着这一份Java面试知识点指导拿到不错的offer了

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！
应付面试啦，也节省朋友们去到处搜刮资料自己整理的时间！**

[外链图片转存中…(img-8KL5axUM-1714711091013)]

Java面试核心知识点

已经有读者朋友靠着这一份Java面试知识点指导拿到不错的offer了

[外链图片转存中…(img-hkrfVQy8-1714711091013)]

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！