MySQL数据库引擎选取与索引优化

在设计数据库时,存储引擎和创建索引是我们首要考虑的问题,不同的应用场景选择不同的引擎会给系统带来既然不同的效率,是否考虑分表,事务机制,读写比例等都是决定引擎选取的因素。索引是帮我们高效获取数据的数据结构,是每个开发人员都应该关注和学习。

1.数据库存储引擎定义

MySQL中的数据用各种不同的技术存储在文件(或者内存)中,这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力,这些不同的技术以及配套的相关功能在MySQL中称为存储引擎(也称为表类型)。

2.MySQL数据库主要存储引擎

存储引擎
功能&特点
MyISAM 拥有较高的插入,查询速度,但不支持事务,表级锁
InnoDB 5.5版本后Mysql的默认数据库存储引擎,事务型数据库的首选引擎,支持ACID事务,支持行级锁定
Memory 所有数据置于内存的存储引擎,拥有极高的插入,更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失,处理非事务表
Archive 非常适合存储大量的独立的,作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度,但其对查询的支持相对较差
CSV 逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.CSV文件。这是一种普通文本文件,每个数据行占用一个文本行。CSV存储引擎不支持索引
BlackHole 黑洞引擎,写入的任何数据都会消失,一般用于记录binlog做复制的中继
MRG_MYISAM 将一定数量的MyISAM表联合而成一个整体,在超大规模数据存储时很有用,实现大数据的水平分表(如创建一个MRG_MyISAM引擎作为主表,可以实现读操作,更新,删除操作,在多个MyISAM引擎的子表中插入存储数据)
PERFORMANCE_SCHEMA 主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表

3.选取存储引擎的场景

不同引擎的核心功能不同,考虑因素:支持的字段和数据类型、锁定类型、索引和事务处理
  • 字段和数据类型

虽然所有这些引擎都支持通用的数据类型,例如整型、实型和字符型等,但是,并不是所有的引擎都支持其它的字段类型,特别是BLOG(二进制大对象)或者TEXT文本类型。其它引擎也许仅支持有限的字符宽度和数据大小。

  • 锁定

数据库引擎中的锁定功能决定了如何管理信息的访问和更新,大多数锁定机制主要是为了防止多个处理更新同一个数据。

不同的存储引擎在不同的对象级别支持锁定,而且这些级别将影响可以同时访问的信息。

得到支持的级别有三种:表锁定、页锁定和行锁定

支持最多的是表锁定,这种锁定是在MyISAM中提供的。在数据更新时,它锁定了整个表。这就防止了许多应用程序同时更新一个具体的表。这对应用很多的多用户数据库有很大的影响,因为它延迟了更新的过程。

页级锁定使用Berkeley DB引擎,并且根据上载的信息页(8KB)锁定数据。当在数据库的很多地方进行更新的时候,这种锁定不会出现什么问题。

但是,由于增加几行信息就要锁定数据结构的最后8KB,当需要增加大量的行,也别是大量的小型数据,就会带来问题。

行级锁定提供了最佳的并行访问功能,一个表中只有一行数据被锁定。这就意味着很多应用程序能够更新同一个表中的不同行的数据,而不会引起锁定的问题。InnoDB存储引擎支持行级锁定。

  • 建立索引

建立索引在搜索和恢复数据库中的数据的时候能够显著提高性能。不同的存储引擎提供不同的制作索引的技术,有些存储引擎根本就不支持索引,其原因可能是它们使用基本表索引(如MERGE引擎)或者是因为数据存储的方式不允许索引(例如FEDERATED或者BLACKHOLE引擎)。

  • 事务处理

事务处理功能通过提供在向表中更新和插入信息期间的可靠性。这种可靠性是通过如下方法实现的,它允许你更新表中的数据,但仅当应用的应用程序的所有相关操作完全完成后才接受你对表的更改。

4.MySQL:索引是数据结构

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
即在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构,就是索引。
这里主要讨论MyISAM和InnoDB两个存储引擎的索引:
  • MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址,主索引和辅助索引(Secondary key)在结构上没有任何区别。

image2015-11-210-51-12

MyISAM中索引检索的算法为首先按照算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。

  • InnoDB的数据文件本身就是索引文件,辅助索引data域存储相应记录主键的值而不是地址

image2015-11-210-50-26

聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

5.索引优化

  • 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

  • 使用短索引

对于字符类型的字段创建索引,尽量指定长度,这样不仅可以提高查询速度而且可以节省磁盘空间和I/O操作

例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引(即一个字段可以选取字段长度的前N个字符作为索引数据)

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

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。因为索引是从前往后匹配的,不会跳过前面的内容而去匹配后面的内容。

  • 不要在列上进行运算
  • 不使用NOT IN和<>操作

MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。

  • 组合索引

必须要从左到右依次精确匹配索引,能匹配多少匹配多少,直到最后一个可以匹配范围索引,只要用了某列范围索引,后面的列的索引就无效了。

例如:对a,b,c设置为组合索引(a_b_c),最左索引指的是在索引查找以此为a,b,c。若查找a=1,b>2,c=3,则索引只能用到前两列字段(a,b)

6.补充

  1. 设计数据表时可以适当增加组合索引实现数据唯一性限制
  2. 预估数据量过大时,除了增加索引优化外,分表操作也是一种不错的解决方案
  3. 数据表中自增的主键可以解决需要唯一值的处理的应用场景(如订单id,序列号等)
  4. 数据表字段命名时,一定要避免数据库关键字
  5. 若多表数据量都很多,适当拆分联合查询

7.结语

在学习中,会遇到各种数据库的设计及应用场景,以上内容是自己在查阅了文献资料的基础上,加上自己的学习积累,仅供编程爱好者参考,如有错误,敬请指正。

打赏此文

如果您觉得本站的内容对您有所帮助,您可以扫描下面的二维码小额支付请我喝杯茶,感谢!打赏记录
支付宝
微信
承诺:凡打赏捐助的朋友,留言备注自己的邮箱,在打赏捐助时间点的6个月内,本站会每周邮件推送原创专业技术博文,供大家学习和参考!

留下评论

All fields marked (*) are required