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

最新推荐文章于 2024-08-12 08:59:40 发布

Listen-Y

最新推荐文章于 2024-08-12 08:59:40 发布

阅读量456

点赞数 4

分类专栏： MySQL 文章标签：数据库 mysql 索引

本文链接：https://blog.csdn.net/Shangxingya/article/details/109992108

版权

MySQL 专栏收录该内容

18 篇文章 1 订阅

订阅专栏

文章目录

引擎选择

数据库引擎MYISAM与InnoDB的区别

MyISAM是Mysql5之前使用的, 现在大多使用InnoDB
MYISAM不支持事务, InnoDB支持事务
MYISAM支持的是表锁, 而InnoDB支持的是行锁, 所以在并发执行的效率上来说InnoDB是好好于MySAM的
MYISAM不支持外键约束, InnoDB支持外键约束, 所以在数据安全性来说InnoDB好点
MYISAM支持全文索引, 所谓全文索引就是数据也有索引, 比如text数据, 但是InnodB低版本中是不支持的, 高版本的支持的
因为InnoDB支持的东西多, 所以在表空间的大小上,InnoDB比MYSAM大2被左右
InnoDB的自增长列必须是索引, 而且是第一个列, MyIsam就没有这个要求, InnoDB的自增长考虑了并发情况, 使用了轻量级的互斥量实现的自增长锁
InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
系统奔溃后，MyISAM恢复起来更困难能否接受
所有的数据库文件都保存在一个data目录下, 一个文件夹对应一个数据库,一个文件夹里的文件对应这个数据库中的一张表, 所以数据看的本质还是文件的存储
INNODB在数据库表中, 只有一个.frm的文件,以及上级目录下的ibdata1文件 (.frm存储表结构, ibdata1存储数据)
MYISAM中的一张表有.frm 表结构定义文件, .MYD数据文件(data) .MYI索引文件
也就是说数据库中一张表保存的文件方式不同：
myisam是一个表对应三个文件
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语句优化

排序的索引问题

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

like语句操作

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

在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秒)

不使用NOT IN和<>操作

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

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

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

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

索引失效

1、当数据库认为数据量小,全表扫描更快的时候索引失效，或者我们给俩个数据列id和c_id都上索引的话，当我们差数据where id = c_id的时候索引优化器就会优化说还不如走全表扫描。
2、最左前缀法则：如果索引了多列，查询从索引的最左前列开始，且不能跳过索引中的列
3、不在索引列上做任何操作（计算，函数，类型转换），会导致索引失效而转向全表扫描
4、当where使用了所以, order中又使用索引时, order中的索引会失效
5、MySQL在使用不等于的时候无法使用索引会导致全表扫描
6、在索引字段上使用not，<>，!= 操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法： key<>0 改为 key>0 or key<0。
7、like 以通配符开头%aa 索引会失效，变成全表扫描
8、存在索引列的数据类型隐形转换，则用不上索引，比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
9、如果条件中有or，即使其中有部分条件带索引也不会使用(这也是为什么尽量少用or的原因), 要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引