MySQL简单介绍换个角度认识MySQL

　　一、引擎篇
　　1、InnoDB存储引擎
　　Mysql版本5。5默认的存储引擎，MySQL推荐使用的存储引擎。支持事务，行级锁定，外键约束。事务安全型存储引擎。更加注重数据的完整性和安全性。
　　存储格式：数据，索引集中存储，存储于同一个表空间文件中。
　　InnoDB的行锁模式及其加锁方法：InnoDB中有以下两种类型的行锁：共享锁（读锁：允许事务对一条行数据进行读取）和互斥锁（写锁：允许事务对一条行数据进行删除或更新），对于update，insert，delete语句，InnoDB会自动给设计的数据集加互斥锁，对于普通的select语句，InnoDB不会加任何锁。
　　InnoDB行锁的实现方式：InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。InnoDB这种行锁实现特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。
　　（1）在不通过索引条件查询时，InnoDB会锁定表中的所有记录。
　　（2）Mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果使用相同的索引键，是会出现冲突的。
　　（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，但都是通过行锁来对数据加锁。
　　优点：
　　1、支持事务处理、ACID事务特性；
　　2、实现了SQL标准的四种隔离级别（原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持续性（Durability））；
　　3、支持行级锁和外键约束；
　　4、可以利用事务日志进行数据恢复。
　　5、锁级别为行锁，行锁优点是适用于高并发的频繁表修改，高并发是性能优于MyISAM。缺点是系统消耗较大。
　　6、索引不仅缓存自身，也缓存数据，相比MyISAM需要更大的内存。
　　缺点：
　　因为它没有保存表的行数，当使用COUNT统计时会扫描全表。
　　使用场景：
　　（1）可靠性要求比较高，或者要求事务；（2）表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。
　　2、MyISAM存储引擎
　　MySQL5。5MySQL默认的存储引擎。ISAM：IndexedSequentialAccessMethod（索引顺序存取方法）的缩写，是一种文件系统。擅长与处理，高速读与写。
　　功能：
　　（1）支持数据压缩存储，但压缩后的表变成了只读表，不可写；如果需要更新数据，则需要先解压后更新。
　　（2）支持表级锁定，不支持高并发；
　　（3）支持并发插入。写操作中的插入操作，不会阻塞读操作（其他操作）；
　　优点：
　　1。高性能读取；
　　2。因为它保存了表的行数，当使用COUNT统计时不会扫描全表；
　　缺点：
　　1、锁级别为表锁，表锁优点是开销小，加锁快；缺点是锁粒度大，发生锁冲动概率较高，容纳并发能力低，这个引擎适合查询为主的业务。
　　2、此引擎不支持事务，也不支持外键。
　　3、INSERT和UPDATE操作需要锁定整个表；
　　使用场景：
　　（1）做很多count的计算；（2）插入不频繁，查询非常频繁；（3）没有事务。
　　InnoDB和MyISAM一些细节上的差别：
　　1、InnoDB不支持FULLTEXT类型的索引，MySQL5。6之后已经支持（实验性）。
　　2、InnoDB中不保存表的具体行数，也就是说，执行selectcount（）fromtable时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count（）语句包含where条件时，两种表的操作是一样的。
　　3、对于AUTOINCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
　　4、DELETEFROMtable时，InnoDB不会重新建立表，而是一行一行的删除。
　　5、LOADTABLEFROMMASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。
　　6、另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。二、索引篇
　　1索引概述
　　利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），建立与记录位置的对应关系，就是索引。索引的关键字一定是排序的。索引本质上是表字段的有序子集，它是提高查询速度最有效的方法。一个没有建立任何索引的表，就相当于一本没有目录的书，在每次查询时就会进行全表扫描，这样会导致查询效率极低、速度也极慢。如果建立索引，那么就好比一本添加的目录，通过目录的指引，迅速翻阅到指定的章节，提升的查询性能，节约了查询资源。
　　2索引种类
　　从索引的定义方式和用途中来看：主键索引，唯一索引，普通索引，全文索引。
　　无论任何类型，都是通过建立关键字与位置的对应关系来实现的。索引是通过关键字找对应的记录的地址。
　　以上类型的差异：对索引关键字的要求不同。
　　关键字：记录的部分数据（某个字段，某些字段，某个字段的一部分）。
　　普通索引，index：对关键字没有要求。
　　唯一索引，uniqueindex：要求关键字不能重复。同时增加唯一约束。
　　主键索引，primarykey：要求关键字不能重复，也不能为NULL。同时增加主键约束。
　　全文索引，fulltextkey：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。
　　PS：这里主键索引和唯一索引的区别在于：主键索引不能为空值，唯一索引允许空值；主键索引在一张表内只能创建一个，唯一索引可以创建多个。主键索引肯定是唯一索引，但唯一索引不一定是主键索引。
　　3。索引原则
　　如果索引不遵循使用原则，则可能导致索引无效。
　　（1）列独立
　　如果需要某个字段上使用索引，则需要在字段参与的表达中，保证字段独立在一侧。否则索引不会用到索引，例如这条sql就不会用到索引：selectfromAwhereid110；
　　（2）左原则
　　Like：匹配模式必须要左边确定不能以通配符开头。例如：selectfromAwherenamelike小明，不会用到索引，而selectfromAwherenamelike小明就可以用到索引（name字段有建立索引），如果业务上需要用到小明这种方式，有两种方法：1。可以考虑全文索引，但mysql的全文索引不支持中文；2。只查询索引列或主键列，例如：selectnamefromAwherenamelike小明或selectidfromAwherenamelike小明或selectid，namefromAwherenamelike小明这三种情况都会用到name的索引；
　　复合索引：一个索引关联多个字段，仅仅针对左边字段有效果，添加复合索引时，第一个字段很重要，只有包含第一个字段作为查询条件的情况才会使用复合索引（必须用到建索引时选择的第一个字段作为查询条件，其他字段的顺序无关），而且查询条件只能出现and拼接，不能用or，否则则无法使用索引。
　　（3）OR的使用
　　必须要保证OR两端的条件都存在可以用的索引，该查询才可以使用索引。
　　（4）MySQL智能选择
　　即使满足了上面说原则，MySQL也能弃用索引，例如：selectfromAwhereid1；这里弃用索引的主要原因：查询即使使用索引，会导致出现大量的随机IO，相对于从数据记录的第一条遍历到最后一条的顺序IO开销，还要大。
　　4。索引的使用场景
　　（1）索引检索：检索数据时使用索引。
　　（2）索引排序：如果orderby排序需要的字段上存在索引，则可能使用到索引。
　　（3）索引覆盖：索引拥有的关键字内容，覆盖了查询所需要的全部数据，此时，就不需要在数据区获取数据，仅仅在索引区即可。覆盖就是直接在索引区获取内容，而不需要在数据区获取。例如：selectnamefromAwherenamelike小明；
　　建立索引索引时，不能仅仅考虑where检索，同时考虑其他的使用场景。（在所有的where字段上增加索引，就是不合理的）
　　5。前缀索引
　　前缀索引是建立索引关键字一种方案。通常会使用字段的整体作为索引关键字。有时，即使使用字段前部分数据，也可以去识别某些记录。就比如一个班级里，我要找王xx，假如姓王的只有1个人，那么就可以建一个关键字为王的前缀索引。语法：Indexindexname（indexfield（N））使用indexname前N个字符建立的索引。
　　6。索引失效
　　（1）应尽量避免在where子句中使用！或操作符，否则将引擎放弃使用索引而进行全表扫描；
　　（2）应尽量避免在where子句中使用or来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描；
　　（3）应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描；
　　（4）应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描；如selectidfromtwherenum2100；
　　（5）应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描；如：selectidfromtwheresubstring（name，1，3）’abc’；
　　（6）应尽量避免在where子句中对字段进行类型转换，这将导致引擎放弃使用索引而进行全表扫描；如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，如selectidfromtwhereid1；如果id字段在表设计中是varchar类型，那么即使id列上存的是数字，在查询时也一定要用varchar去匹配，sql应改为selectidfromtwhereid1；
　　（7）应尽量避免在where子句中单独引用复合索引里非第一位置的索引；三、JOIN篇
　　join的两种算法：BNL和NLJ
　　NLJ（NestedLoopJoin）嵌套循环算法；以如下SQL为例：
　　selectfromt1joint2ont1。at2。a
　　SQL执行时内部流程是这样的：
　　1。先从t1（假设这里t1被选为驱动表）中取出一行数据X；
　　2。从X中取出关联字段a值，去t2中进行查找，满足条件的行取出；
　　3。重复1、2步骤，直到表t1最后一行循环结束。
　　这就是一个嵌套循环的过程，如果在被驱动表上查找数据时可以使用索引，总的对比计算次数等于驱动表满足where条件的行数。假设这里t1、t2都是1万行，则只需要1万次计算，这里用到的是IndexNestedLoopsJoin（INLJ，基于索引的嵌套循环联接）。
　　如果t1、t2的a字段都没有索引，还按照上述的嵌套循环流程查找数据呢？每次在被驱动表上查找数据时都是一次全表扫描，要做1万次全表扫描，扫描行数等于1万1万1万，这个效率很低，如果表行数更多，扫描行数动辄几百亿，所以优化器肯定不会使用这样的算法，而是选择BNL算法；
　　BNLJ（BlockNestedLoopJoin）块嵌套循环算法；
　　1。把t1表（假设这里t1被选为驱动表）满足条件的数据全部取出放到线程的joinbuffer中；
　　2。每次取t2表一行数据，去joinbuffer中进行查找，满足条件的行取出，直到表t2最后一行循环结束。
　　这个算法下，执行计划的Extra中会出现Usingjoinbuffer（BlockNestedLoop），t1、t2都做了一次全表扫描，总的扫描行数等于1万1万。但是由于joinbuffer维护的是一个无序数组，每次在joinbuffer中查找都要遍历所有行，总的内存计算次数等于1万1万。另外如果joinbuffer不够大放不下驱动表的数据，则要分多次执行上面的流程，会导致被驱动表也做多次全表扫描。
　　BNLJ相对于NLJ的优点在于，驱动层可以先将部分数据加载进buffer，这种方法的直接影响就是将大大减少内层循环的次数，提高join的效率。
　　例如：
　　如果内层循环有100条记录，外层循环也有100条记录，这样的话，每次外层循环先将10条记录放到buffer中，内层循环的100条记录每条与这个buffer中的10条记录进行匹配，只需要匹配内层循环总记录数次即可结束一次循环（在这里，即只需要匹配100次即可结束），然后将匹配成功的记录连接后放入结果集中，接着，外层循环继续向buffer中放入10条记录，同理进行匹配，并将成功的记录连接后放入结果集。后续循环以此类推，直到循环结束，将结果集发给client为止。
　　可以发现，若用NLJ，则需要100100次才可结束，BNLJ则需要100blocksize10010100次就可结束，大大减少了循环次数。
　　JOIN按照功能大致分为如下三类：
　　JOIN、STRAIGHTJOIN、INNERJOIN（内连接，或等值连接）：取得两个表中存在连接匹配关系的记录。
　　LEFTJOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。
　　RIGHTJOIN（右连接）：与LEFTJOIN相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。
　　注意：mysql不支持Fulljoin，不过可以通过UNION关键字来合并LEFTJOIN与RIGHTJOIN来模拟FULLjoin。
　　mysql多表连接查询方式，因为mysql只支持NLJ算法，所以如果是小表驱动大表则效率更高；反之则效率下降；因此mysql对内连接或等值连接的方式做了一个优化，会去判断join表的数据行大小，然后取数据行小的表为驱动表。
　　INNERJOIN、JOIN、WHERE等值连接和STRAIGHTJOIN都能表示内连接，那平时如何选择呢？一般情况下用INNERJOIN、JOIN或者WHERE等值连接，因为MySQL会按照小表驱动大表的策略进行优化。当出现需要排序时，才考虑用STRAIGHTJOIN指定某张表为驱动表。
　　两表JOIN优化
　　a。当无orderby条件时，根据实际情况，使用leftrightinnerjoin即可，根据explain优化；
　　b。当有orderby条件时，如selectfromainnerjoinbwhere11andotherconditionorderbya。使用explain解释语句；
　　1）如果第一行的驱动表为a，则效率会非常高，无需优化；
　　2）否则，因为只能对驱动表字段直接排序的缘故，会出现usingtemporary，所以此时需要使用STRAIGHTJOIN明确a为驱动表，来达到使用a。col上index的优化目的；或者使用leftjoin且Where条件中不含b的过滤条件，此时的结果集为a的全集，而STRAIGHTJOIN为innerjoin且使用a作为驱动表。注：使用STRAIGHTJOIN虽然不会usingtemporary，但也不是一定就能提高效率，如果a表数据远远超过b表，那么有可能使用STRAIGHTJOIN时比原来的sql效率更低，所以怎么使用STRAIGHTJOIN，还是要视情况而定。
　　在使用leftjoin（或rightjoin）时，应该清楚的知道以下几点：
　　（1）。on与where的执行顺序
　　ON条件（ALEFTJOINBON条件表达式中的ON）用来决定如何从B表中检索数据行。如果B表中没有任何一行数据匹配ON的条件，将会额外生成一行所有列为NULL的数据，在匹配阶段WHERE子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。
　　所以我们要注意：在使用Left（right）join的时候，一定要在先给出尽可能多的匹配满足条件，减少Where的执行。
　　（2）。注意ON子句和WHERE子句的不同
　　即使右表的数据不满足ON后面的条件，也会在结果集拼接一条为NULL的数据行，但WHERE后面的条件不一样，右表不满足WHERE的条件，左表关联的数据也会被过滤掉。
　　（3）。尽量避免子查询，而用join
　　往往性能这玩意儿，更多时候体现在数据量比较大的时候，此时，我们应该避免复杂的子查询。四、sql简单优化
　　（1）in和notin要慎用，如：selectidfromtwherenumin（1，2，3）对于连续的数值，能用between就不要用in：selectidfromtwherenumbetween1and3很多时候用exists代替in是一个好的选择：selectnumfromawherenumin（selectnumfromb）用下面的语句替换：selectnumfromawhereexists（select1frombwherenuma。num）
　　（2）Update语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。
　　（3）join语句，MySQL里面的join是用小表去驱动大表，而由于MySQLjoin实现的原理就是做循环，比如leftjoin就是对左边的数据进行循环去驱动右边的表，左边有m条记录匹配，右边有n条记录那么就是做m次循环，每次扫描n行数据，总扫面行数是mn行数据。左边返回的结果集的大小就决定了循环的次数，故单纯的用小表去驱动大表不一定的正确的，小表的结果集可能也大于大表的结果集，所以写join的时候尽可能的先估计两张表的可能结果集，用小结果集去驱动大结果集。值得注意的是在使用leftrightjoin的时候，从表的条件应写在on之后，主表应写在where之后。否则MySQL会当作普通的连表查询；
　　（4）selectcount（）这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的；
　　（5）selectfromt这种语句要尽量避免，使用具体的字段代替，更有实际意义，需要什么字段就返回什么字段；
　　（6）数据量大的情况下，limit要慎用，因为使用limitm，n方式分页时，mysql每次都是查询前mn条，然后舍弃前m条，所以m越大，偏移量越大，性能就越差。比如：selectfromAlimit1000000，20这钟，查询效率就会非常低，当分页的页数大于一定的数量之后，就可以换种方式来分页：selectfromAajoin（selectidfromAlimit1000000，20）bona。idb。