索引
1. 索引失效的几种状况
查询运用or查询
like查询运用了后匹配，即%name，一般想用索引只能前匹配name%。
组合索引，最左匹配，假如树立的组合索引次序是name,age,datafrom，那么查询条件需求带上name，原理是由于索引树的树立会依据最左匹配，先依据name排序，相同的值再依据age排，再依据dataform,假如直接用age和datafrom是没办法和索引树对应上的。（趁便提一点，假如数据量特别大，依据多个字段查找时，必定要用组合索引，假定name有个独自的索引，现在用name和age查找，这时name的查找尽管射中索引，可是假如name值对应的数据有几万条，这个时分查找age值匹配的数据时，就会在这几万条线性遍历匹配，查找功用会很低）
查询运用 != 查询
2. 索引类型
FULLTEXT 全文索引，合适对存储长文本的字段树立此索引类型
NORMAL 一般索引
SPATIAL 空间索引，只要MYISAM引擎的表支撑，用于对空间数据类型的字段树立索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
UNIQUE 仅有索引，束缚仅有的值
3. hash索引和BTREE索引比照
现在干流仍是运用BTREE索引，MyISAM 、InnoDB引擎运用BTREE索引（index method设置hash索引的时分显现成功，可是show index的时分显现仍是btree），MEMORY引擎能够挑选用BTREE或许hash索引。假如在InnoDB引擎下想要运用hash索引部分功用，能够设置体系参数innodb_adaptive_hash_index为on,innodb底层仍是用btree索引，可是会经过innodb监控主动针对一些热数据树立hash索引，进步=和in查询的功率(官方把这个称为adaptive hash，即自适应hash,关于自适应hash更多详细介绍能够看官方文档。https://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash.html)。
BTREE索引依据B+树完成，Hash索引保护一个hash表，索引字段hash后得到的值作为hash表索引位，一个方位对应多条数据（由于存在hash磕碰的问题，所以同个hash值或许对应多条数据）。
Hash 索引仅仅能满意”=”,”IN”和”<=>”查询，不能运用规模查询，由于hash索引依据核算hash值索引，hash后值的方位跟字段值原有的次序没有必定联系。
hash依据某个值的查找比b+树更快。可是假如同个hash值磕碰的量特别大，会导致索引需求线性遍历匹配值，速度不必定就有b+树的快了。
hash索引创立组合索引后，索引值是依据组合索引几个字段的值一块核算hash值，所以查找时有必要组合索引的几个字段都要用上才干射中索引。
hash索引不能用于加快order by。A8站源码交易平台
4. 聚簇索引和非聚簇索引
innodb运用的是聚簇索引，myisam运用的对错聚簇索引
聚簇索引叶子节点存的是数据。非聚簇索引叶子节点存的是数据的地址，需求多一次io查数据。
5. 二级索引
默许主键为一级索引，其他字段树立的索引为二级索引，一张表只要一个一级索引，没有树立主键mysql默许会设定一个躲藏列作为主键
innodb的二级索引叶子节点存的是主键的id,查询时获取数据需求依据id再去回查主键索引树。myisam由于运用非聚簇索引，二级索引和一级索引相同存的都是数据的地址。
6. BTREE索引
BTREE底层的数据结构是B+树（平衡多路查找树），树的层数低，每一层有多个节点，每个节点中存多个索引值key(一个节点为一个磁盘块)，由于层数低io次数也低，查询功率高。
B+树和B树的差异：
B+树只要叶子节点才存数据，非叶子节点只存索引值key不存数据，而B树非叶子节点也存储索引值和数据。尽管B树在非叶子节点存数据能够让某些查询不必查到叶子节点就获取到数据，可是也因而节点占用的空间更大（特别是假如数据特别大，那么会占用很大空间），这会导致非叶子节点（一个节点一个磁盘块）中能够存储的key变得更少，然后导致树的层树更深。也便是说B+树非叶子节点能够存储更多的key值，然后让树的深度能更低，且每次都要查到叶子节点，查询功率也比较稳定。A8站源码交易平台
B+树叶子节点之间用指针连成一个链表，在进行规模查询的时分，直接经过链表便可完成规模查询，而不必遍历树节点。
引擎比照
MYISAM、INNODB、MEMORY
InnoDB支撑业务和外键，MYISAM不支撑。因而MYISAM的功用会更高一点,可是数据共同性会弱一些。
InnoDB选用了行级锁，MYISAM选用的是表级锁。
全表计算行数InnoDB需求全表扫描，MYSAM会保护一个count值，直接回来。
InnoDB运用聚簇索引，MYISAM运用的对错聚簇索引，然后二级索引也不相同。（详见索引部分）
MEMORY引擎数据都是存储在内存中的，mysql重启或中止数据就会丢掉，合适存暂时数据。索引默许运用hash索引，也能够设置BTREE索引。（详见索引部分）
业务(mysql引擎中只要INNODB支撑业务)
1. ACID 业务的四个特性
原子性(Atomicity)：业务内的sql操作要么全履行,要么不履行。即假如业务履行期间犯错会回滚，而不会呈现部分履行成功，部分履行失利的状况。mysql业务原子性经过undo log日志完成，业务中履行的sql操作的前后信息都会记载在undo log日志中，履行犯错会经过undo log日志中的信息进行回滚。
阻隔性(Isolation）: 业务之间有必定的阻隔,业务之间不会呈现交织履行的状况。阻隔等级不同，业务间的阻隔程度也不相同。
耐久性(Durability）:业务的耐久性是指业务履行成功今后,该业务所对数据库所作的更改便是耐久的保存在数据库之中，不行回滚。mysql业务耐久性的表现之一，避免忽然宕机缓存丢掉数据丢掉，经过redo log完成，详细见下面讲的的mysql缓存）。
共同性(Consistency)：业务履行前后数据要确保共同性。数据库层面有类型束缚、长度束缚、外建束缚等来确保数据的共同性。运用层面比方下单，库存减1，销量就要加1，从运用层面确保数据的共同性。共同性是业务寻求的终究目标，只要确保原子性、独立性、耐久性，才干确保数据共同性。
假如没有业务的原子性，库存减1后，销量加1的时分犯错中断没有业务回滚，数据就不共同了。
假如没有业务间阻隔性，不同业务对相同数据的修正相互影响，也会导致数据失掉共同性，比方库存原本是4，两个业务都去减1，没有业务的阻隔性，导致两个业务都拿4去减1，终究库存剩余3，可是实际上应该是剩余2.数据就不共同了A8站源码交易平台
假如没有业务的耐久性，业务履行后提交了，其他业务对数据又做了修正，后边又把这个业务回滚了，导致数据呈现问题。
2. autocommit 主动提交业务
mysql默许选用的是主动提交,show variables like 'autocommit'能够看到启用状况。
在主动提交形式下，假如没有显性的start transaction敞开业务，则每一条句子的都会被当作一个业务提交。
假如把这个形式关掉，则一切sql操作都会在一个业务里，需求手动提交业务或回滚，才敞开另一个业务。
3. 业务阻隔等级
READ UNCOMMITTED（读未提交）。读未提交是业务最低的阻隔等级，会形成脏读。正如这个阻隔等级姓名，读未提交即一个业务修正了数据还没有提交业务就能够被其他业务读取到，这或许会导致读取了脏数据，由于在业务未提交之间或许会犯错导致回滚，那么其他业务假如在这个业务回滚前读取了这个业务修正的数据，就形成读了脏数据，即过错数据。（存在脏读、不行重复读、幻读问题）
READ COMMITTED（读已提交）。读已提交，即业务的修正只要提交业务才被其他业务可见。尽管只要提交业务才可见，处理了脏读。可是还会形成不行重复读。按照读已提交的界说，比方业务A提交后形成的数据改动便对其他业务B可见了，可是假如业务B在业务A提交前对一条数据读取过一次，业务A提交后又把这条数据修正掉了，那么业务B再次读取那条数据获取到的数据就跟之前获取到的不共同了，因而叫做不行重复读。（存在不行重复读、幻读问题）
REPEATABLE READ(可重复读)。默许的阻隔等级（为什么默许是RR与binlog相关，后边详解）。mysql经过快照读（即一般的select * from test where ..）避免了脏读、不行重复读和幻读，缺陷是读的数据或许不是实时数据，快照生成的时刻是业务第一次读取的时刻，快照生成后其他业务的增修正，不会影响当时业务再次读取的成果，由于当时读的是第一次读取生成的快照。快照读经过MVCC完成，即多版别操控，意图是不加锁，进步并发才能，快照在RR等级只要当时业务更新数据才会更新快照，不受其他业务影响，而RR以下其实也是存在快照读，可是每次读取都会更新快照，所以存在不行重复读和幻读。假如要获取实时数据，能够运用当时读（select * from test where ... lock in share mode(加同享锁); select * from test where .. for update;(加排他锁) ),当时读会经过空隙锁给读取到的规模的数据加上锁，在该业务没有提交前，其他业务在往空隙锁锁住的数据规模内刺进/修正/删去数据时会被堵塞。
SERIALIZABLE（串行化）。最高的阻隔界别，与可重复读相似，可是一切的查询都是当时读，即select * from test where ..都会被默许转成select * from test where ... lock in share mode，相同不存在脏读、不行重复读和幻读，可是由于一切查询存在记载锁（同享锁或排他锁）、空隙锁、next key锁等，并发功用会很差。（锁相关的常识后边讲）A8站源码交易平台
4. 业务运用的留意点
同一个业务内的sql操作锁占用的锁，都是在业务提交的时分开释，因而独占锁尽量放在后边。
业务能够设置折返点，呈现问题，能够跳回到折返点。
尽量不要服务层调用服务层（即尽量确保接口功用单一，避免长业务），锁的开释机遇是业务提交的时刻，确保短业务能够削减锁占用时刻。
在业务中履行DDL句子create table/drop table/alter/table等，会强制提交当时业务。
InnoDB存储引擎不会回滚超时引发的反常，除死锁外
锁
1. 同享锁shared（S）、排他锁exclusive(X)
innodb读写数据可请求的锁，阻隔等级为可重复读以上的当时读会请求同享锁、排他锁。（当时读的介绍看可重复读业务的介绍)。刺进一条新数据，在业务未提交前，这条数据会被加上排他锁。相同修正数据时，被修正的数据会被加上排他锁。
select * from test where ... lock in share mode的当时读加同享锁。
select * from test where .. for update的当时读加排他锁。
当一个业务持有同享锁读取一条数据时，其他数据持有同享锁读取不会被堵塞，可是假如持有排他锁去读取或许修正数据时会被堵塞。当一条数据被一个业务持有排他锁占用时，其他业务持有任何锁读取和修正都会被堵塞。
一个业务中假如有更新操作，应该直接请求排他锁，而不是先请求同享锁。假如一开始请求了同享锁，更新又请求排他锁，假如两头业务一起具有同享锁，而又都去对同一行请求排他锁，都在等候对方的同享锁开释，则会呈现死锁的状况。
2. 意向同享锁(IS)和意向排他锁(IX)
意向同享锁和意向排他锁是请求同享锁和排他锁时需求请求的锁（innodb主动加上的锁）。
意向锁是表级锁，只会跟表等级的同享锁和排他锁抵触，与行等级的同享锁排他锁之间不抵触。
意向锁的效果首要是为了处理行锁和表锁共存时导致的数据问题。假定业务1修正某行的数据，请求了某个行的排他锁，这时会先请求一个意向排他锁，业务2当时读一切数据，这时要请求一个表级同享锁，发现表现已存在一个意向排他锁了，由于意向排他锁与同享锁抵触，因而业务2会堵塞等候（假如没有意向锁，业务2想要知道有没有哪一行被加了锁，还要一行一行去找）。假如没有堵塞业务2，业务履行select * from test lock in share mode（由于行锁加锁是依据索引完成的，这条句子没有查询条件没有射中索引，所以会请求表级同享锁），之后业务1修正了某行的数据并提交，业务2再次执行查询句子，获取到的数据就跟之前的不相同了。
下图是mysql官方供给表等级的S、X、IS、IX之间的兼容性。
3.补充说--死锁的状况
InnoDB会呈现死锁的状况。假定业务A和业务B都先请求了同条数据上的同享锁，然后业务A请求排他锁要修正这条数据被业务B的同享锁堵塞，接着业务B也请求排他锁要修正这条数据被业务A堵塞，就形成死锁了。
InnoDB呈现死锁的时分，会主动将导致死锁的且持有较少排它锁的业务回滚，另一个业务则正常履行。
MyISAM选用表等级的锁，不会呈现死锁的状况，且锁分为读锁和写锁，读锁可重入，读写锁之间是互斥的。且在等候锁的时分，一起有等候写锁和读锁，写锁会优先分配锁（默许状况下，可经过装备修正）
前面讲的同享锁和排他锁相当所以锁的程度，下面讲的锁不同的是锁的方位，可是相同都有同享和排他两种程度，比方同享记载锁，排他记载锁。record lock , gap lock , next-key lock 组成完成INNODB的行锁
4. 记载锁 record lock
记载锁锁的当时修正/新增/读取/删去的数据，经过锁住索引完成。假如操作的行没有射中索引则锁的是全表。
假如表一个索引都没有，INNODB会保护一个躲藏的索引，并运用此索引用于记载确定。
5. 空隙锁 gap lock
空隙锁，mysql为处理幻读呈现的锁，可重复读等级以上的当时读会请求这个锁，mysql会对契合条件的数据规模加上锁（比方查询条件是id>10，那么大于10这个规模都会被锁住），当业务a经过查询条件去查询到一批数据的时分，会加上一个空隙锁，这时业务b往这批契合条件的数据进行修正或新增契合这个条件的数据都会被挡住，需求等候锁，多个条件时依据要看详细射中的索引，依据射中的索引加锁（行锁依据索引加锁完成）。
6. 下一键锁Next-key lock
7. MYISSAM表锁
myisam的表级锁，尽管会锁表，可是能够支撑并发刺进，即一个业务持锁读表时，另一个业务从表尾刺进数据。concurrent_insert默许为1，假如设置成2则即便读的表中心有被删去的行也能够并发刺进。
8. MVVC 多版别并发操控（不是锁，可是完成跟锁相同的意图）
MVCC 经过版别号来操控需求回来的数据，进步并发才能，经过无锁的版别操控避免了不行重复读，幻读等。
缺陷是读取的方法是快照读，或许不是实时数据。假如要读取实时数据需求当时读，经过锁来操控。
mysql缓存
mysql的缓存分为三种机制，INNODB的buffer pool，MyISAM的key cache，公有的mysql query cache。
1. buffer pool
缓存数据和索引。sql查询的数据进入buffer pool缓存，按页为单位存储,一个page的巨细默许为16kb。
INNODB的数据都是存在磁盘中，为避免每次读写数据都需求磁盘io,mysql供给了Buffer Pool缓存部分数据页，数据的修正定时从缓存刷到磁盘，可是存在体系宕机缓存丢掉的状况。所以数据存到缓存前会先经过fsync将数据存到redo log日志中，假如忽然宕机也能够从redo log中读取康复，确保了数据耐久性。
为什么不直接将数据直接存到硬盘再做缓存而是写到log中? 由于log是次序io的，数据修正的方位是随机的所以是随机io。还有便是buffer pool的写入和刷盘都是以一个数据页16k为单位写数据的，假如每次更新都去刷盘会形成不必要的io（只要一条数据变化，却要刷盘整个数据页）。A8站源码交易平台
经过LRU算法保护缓存的数据，缓冲池列表分为两个部分，一部分寄存热门数据new sublist（默许占5/8），一部分用来存储行将筛选的数据old sublist（默许占3/8）。两个sublist都分有头部和尾部，查询到的新数据页进入buffer pool的时分，会先进入old sublist的头部（不直接放到new sublist是为了避免影响到热门数据，某些查询只查一次，就很久没有再查，直接进new sublist会导致真实热门的数据被挤掉），而old sublist中的数据被拜访到达设定的每秒几回拜访后（innodb_old_blocks_time，这个参数能够让新进来的数据在一段时刻内重复拜访页无法进入new sublist,添加成为热门数据的难度），则会向new sublist头部移动成为热门数据，old sublist头部的数据一段时刻内没有被拜访，则会移动到old sublist的尾部，终究数据被筛选。
能够经过参数设置mysql启动时将热数据预加载到buffer pool中，也能够经过设置各种参数影响LRU算法，这儿不逐个详解，详见官方文档。https://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html。
show variables like '%innodb_buffer_pool%'; 查询buffer pool的装备。
show status like '%innodb_buffer_pool%'; 查询buffer pool的运转状况。A8站源码交易平台
2. key cache
只缓存索引键
3. query cache
