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级别的时间复杂度)
总结：
454
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
