mysql架构和历史
目录
提到锁首先要和并发以及维护锁的资源开销联系起来。
按照读写可以分为读锁(共享锁)和写锁(排它锁)。读锁之间不互斥,写锁之间互斥,并且读锁和写锁之间之间也存在一定的互斥关系,例如被加了写锁的资源不可读。
ps:在给定的资源上,被锁定的数据量越少,系统所能支持的并发越高。
表锁:
锁定一张表。由于表的数量少,所以每次维护表锁所耗费的资源少,但是表级的操作出现资源互斥的概率大,所以使用表锁不利于提高并发。
行间锁:
锁定行级别的数据。行锁会消耗较多的硬件资源,但是它能提高并发。
mysql中的事务
事务的特点: 原子性atomicity,一致性consistency,隔离性isolation,持久性durability。
提到事务也要和死锁联系起来。行间锁模式下的事务之间可能会出现死锁。存储引擎会自动处理这个问题(返回错误并释放锁)。
原子性:事务操作是最小执行单元不可切分。
一致性:数据库总是从一个一致性状态(执行事务前)切换到另一个状态(执行事务后)。
隔离性:事务之间相互隔离,在事务提交前对于其它事务是不可见的。
持久性:事务一旦执行成功,其修改久永久性地保存到数据库中了。
ps:sql语句在执行时会自动封装成事务并自动提交。
mysql中的存储引擎
InnoDB: 支持事务,使用行间锁提高并发,崩溃后有较好的数据恢复能力。
MyISAM:全文索引,压缩表,不支持事务,使用表锁。 (适合日志型业务)
mysql5.5以后默认使用InnoDB存储引擎。
除非需要使用到InnoDB不具备的特性而且没有其它方案可以替代,否则都应该使用InnoDB。
选择存储引擎的时候考虑几个因素:事务,热备份,崩溃恢复,特有特性
创建高性能的索引
例如:select * from tb where id=5;
如果对id字段建了索引,那么它会先去索要里面查找id值为5的所有行,然后返回。如果一个索引包含多个列,那么列的顺序也至关重要。
索引是存储引擎层实现的,而不是服务器层。
常见的索引类型:
目录
b+树索引
如果一个索引里面包含多个字断,那么索引的结构是如左图中所示。它会存储索引列里面的具体值。
b+树索引的优势:
-
所有叶子节点到跟节点的距离都一致,索引查询的耗时相对固定。
-
由于节点是的存放是有序的,所有b+树索引很适合范围查找以及查找结果集排序。
b+树索引的限制:
-
在包含多列的索引中,如果不是按照最左列(可以是其中的一个查询条件)进行查找。那么则无法使用索引。例如无法直接查找生日是哪天的所有纪录。
-
在使用索引过程中,如果跳过列,例如直查询姓何生日,跳过名字这列,则无法完全使用索引。它只能使用索引的最左列。
-
在多列索引里面,如果某一列使用模糊匹配,则该列右边的所有索引都无法在使用。当然按照最左列查找时,如果使用右模糊匹配,则也是无法使用索引的。
hash索引
实现方式为:将索引列的值按照一定规则组合起来,计算出其hash值,将结果存放到hash表中。如果有hash冲突时,在冲突的key上维护一个链表。
hash索引的优势:
-
结构紧凑,只存储hash值和行指针。不保存索引列的具体内容。所以占用的空间小。
-
适合于按照索引结构查询特定的行数据。相当于只查一次hash表,遍历一次冲突链表,找到目标数据行。
hash索引的限制:
-
使用起来不灵活。只能按照索引的定义去查找,否则无法使用hash索引。
-
索引存储是无序的,所以无法做范围查找,也不能自动对结果集做排序。
-
表数据大了以后容易出现hash冲突,hash冲突多了以后性能下降比较厉害。容易丢失hash索引原本具备的优势。
innodb存储引擎中实现了一种自适应hash索引。即它会对访问次数多的行再做一次hash索引。加快这些行的查询速度。
当存放长度不固定的字符串列需要作为索引时,可以考虑创建自定义hash索引。思路为:增加一列用语存放字符串列的hash值,同时对该列进行索引。当然这种模式有几个问题需要注意:1,由于存在hash冲突的问题,所以在查询的时候,除了匹配hash值列以外,必须增加原始值匹配的条件。2,不要使用md5或者sha1这种加密算法,因为他们本身比较复杂,计算结果也是一串字符。如果数据量不大可以考虑使用crc32,否则自己去实现一个64位的hash算法,或者截取md5的其中一段转成整数存放到索引列中。
索引的优点:
-
减少了查询需要扫描的数据量,提升查询速度
-
可以是查询数据由随机IO变成顺序IO(连续存放在磁盘中的,所以可以读取一片)
-
可以避免排序和创建零时表(b+索引有序,而且索引里面存储了索引列的具体内容)
高性能索引策略
explain select * from tb where tb.cel1=1 or tb.cel2=2; 使用explain能够查看表达式执行时会做什么事情。
三星原则:
索引将相关的纪录放在一起,则获得一星。索引中数据的排序和查询结果排序一致,则获得二星。索引中包涵查询所需要的所有列泽获得三星。
1,独立的列
所谓独立的列就是指在查询中,索引列不能在表达式中或者为其它方法的参数。例如 select * from tb where tb.cel1+1=5 or Func(tb.cel2)=6; cel1和cel2列都无法使用索引。
2,前缀索引和索引的选择性
索引字段很长的话,一来是可以考虑单独维护一个hash字段作为索引字段。二来是考虑使用前缀索引。前缀索引的意思就是在一个字段中使用字段的前几位作为索引。
使用前缀索引最重要的是找到合适的前缀长度作为索引。使前缀选择性接近于完整列选择性(1)的选择性。如何计算不同长度的前缀的选择性:select count(*) as total, cout(distinct left(cel1,3))/count(*) as ct3, count(distinct left(cel1, 4))/count(*) as ct4, count(distinct left(cel5, 5))/count(*) as ct5 from table tb; 一般来说数值越大的话越合适,因为这样在b+树中的节点就越分叉。但是在某个数值以后,越往上增加位数,选择性变化都不大的时候,此时就应该选择该数值长度作为前缀索引的长度。 alter table tb add key (city(7));
使用前缀索引的缺点是无法使用order by 和 group by等操作,也无法使用前缀索引做覆盖扫描。
3,多列索引
有时候对where查询条件的每列都做单独的索引,不如将这几列聚合到一个索引里面。
现在的mysql查询语句已经支持使用多个索引组合了。不再是一条语句只能使用一个索引。
根据建立的索引调整查询的sql语句,尽量使sql语句可以使用单条索引或者索引合并。
索引合并有时候是一种优化,但是很多时候也说明是表或索引建的不够好。使用explain发现语句中有索引合并的话应该好好检查一下表和sql都是否最优。
关于索引合并的介绍可以参考另一篇博客:https://yq.aliyun.com/articles/27344
4,选择合适的索引列顺序
一个可以参考但不是任何场景都使用的原则是:挑选选择性最高的列作为最前列。当然在实际应用中,最常作为查询条件的列也要考虑放在最左列。
举个简单的例子,一个表中由姓和名字段,查询的时候需要匹配姓名。那么此时需要对firstname和lastname俩列做个索引。那究竟是哪列在前呢,还是都一样?
可以通过select count(*) as ct, count(distinct(firstname))/count(*) as fn, count(distinct(lastname))/count(*) as ln from table tb; 观察两个字断的选择性。选择高的那个作为最左列。
5,聚族索引
它不是一种索引类型,而是一种数据存储方式。叶子页包含了行的完整数据,但是节点中只包涵了索引数据。
优点是:
可以减少磁盘读写次数,多次io操作可以合并成一次。
数据访问更快,因为行数据就保存在索引中,不需要在通过行指针索引到具体的行。
使用覆盖索引扫描的查询可以直接使用索引中保存的主键。
缺点是:
如果把数据都存放在内存中,则对于io密集的查询操作就没有优势了。
聚族索引列更新的代价很高,更新以后需要重新挪动每个索引的位置。
。。。(网上在找些资料看看他的实际使用)
6,覆盖索引
如果一个索引包涵了索要查询的所有字段(并不是where判断语句的所有字段,而是结果集中的所有字段),那么称之为覆盖索引。即查询不需要再去索引到行里面取数据。
由于索引的数量肯定要比实际行数少很多,所以扫描的数据量也少很多。并且它不需要通过索引再去查询具体的行数据。另外,索引中的字段是按照顺序存放的,所以可以避免对查询结果再做二次排序。
7,使用索引扫描来做排序
如果要对查询的结果集做排序,有可能有两种实现方式。1,通过排序操作;2,通过按索引顺序扫描。如果explain出来type顺序为index的话,则表示此次查询使用索引扫描来排序。(注意和extrali列的using index区分开)。但是如果索引不能包含查询所需要的所有列,那么就还需要重新回查表,而且都是随机io操作,它的速度要比全表扫描慢,尤其在io密集时段更为明显。
当索引列的顺序和order by的顺序一样,而且所有列的排序方式也一致时(即不能对a字段降序,但是又对b字段升序排列),可以使用索引扫描来做排序
8,冗余和重复的索引
在使用中根据实际情况,去除一些冗余的索引。 例如key(cel1) 就和 key(cel1, cel2, cel3) 冗余了。
实战攻略
1,明确所有sql查询语句中最常出现在where语句中的列,考虑将其放到所有合适索引的最左列。(希望这个列里面的值不会有太多选项,例如性别,季节等字段)
2,如果在查询中不需要用到该常见的索引列,可以通过in去绕过它,比如增加 and sex in(‘male’,’female’) 让查询进入索引即可。当然如果有太多选项则不是和用in这种模式去绕过最左列。
3,将范围查询的列放在索引的最右。例如年龄,出生日期等。因为一旦出现范围查找,那么该字段往右的所有字段的索引都无法使用了。
4,如果要对索引中间的某个列做范围查找,思考下是否可以用in去绕过它。使得该列右边的列还能使用索引。例如大于10岁,小与14岁的孩子,用age in (11,12,13)去代替age>11 and age<14。
5,避免多个范围条件。
6,如果查找的结果集会很大而且有需要排序的话,可以在创建索引的时候考虑索引列顺序,或者创建单独的索引用与加速结果集排序。
7,解决翻页可能存在的问题(为什么存在深翻页问题,如果limit 100000,10 数据是按照B+树格式存储的,并不知道某个元素在所有数据中的排序,它只能按照order字段索引排序以后从前往后找。如果改成用条件模式锁定上次查找的最后一个元素,这样最快可以是logn级别的时间复杂度)
总结: