mysqlreturn语句

索引:

索引是帮助MYSQL高效获取数据的排好序的数据结构

1)假设现在进行查询数据，select * from user where userID=89

2)没有索引是一行一行从MYSQL进行查询的，还有就是数据的记录都是存储在MYSQL磁盘上面的，比如说插入数据的时候是向磁盘上面的某一个点位去存放，当去尝试插入第二条记录的时候并不是挨着点(第一条记录)位去存放的，磁盘写数据是一个磁道一个磁道写的，如果是过了好几天才插入第二条记录，一张表的所有记录是可能随机分布在磁盘上的，在磁盘上并不是连续的，所以会产生随机IO；

3)一次读取一条记录，一次IO，如果表中的数据非常多，就需要进行多次IO；

4)二叉树，key是主键值，value是数据所在的物理磁盘的地址；

5)红黑树也是二叉平衡树，一个节点只能存放一条记录，高度也非常高，当数据量高的时候，高的高度也是比较高，也是不可控的，所以可以在一个页里面，存储更多的数据让高度变得更低，B+树的叶子节点存储着所有的索引记录，非叶子节点就是一些冗余索引，来辅助B+数据的增删改查，但是B树叶子节点和叶子节点之间没有使用指针链接，MYSQL在进行查找的时候会加载一个目录页中的数据，然后使用二分法快速找到下一个数据的文件地址；

6)出现一个索引，那么旁边就会增加一个叶子结点的地址，假设使用bigInt来存储，是8个字节，指针是占据着6个字节，那么一个页中大概存放的数据就是16KB/8+6=1170条记录，但是叶子节点可能存放的数据量比较少，一张表大概一行记录大概是1KB，所以叶子节点大概一可以存放16条数据，所以B+树可以存放的数据载荷的个数就是1170*1170*16条数据，此时数据的高度就是3，最多经过三次的磁盘IO就可以找到最终想要的数据，所以千万级别的数据，如果要是走索引，那速度是非常快的，相比于磁盘遍历要扫描1000W次，但是使用到了索引，此时查询的速度就会非常快，B+树还支持范围查询，但是B树只能优先遍历，B树没有冗余索引；

7)在同等条件下，假设现在要存放2000万条记录使用B树，那么此时的树的高度，一个数据载荷是1KB，一个页数据只能存放16条索引数据，所以计算树的高度16^K=2000W，K>3，MYSIM存储引擎有三个文件，.frm是表结构文件，.myd是数据文件，.MYI是索引文件，但是innodb也是存储了两个文件，.frm是表结构文件，.ibd是索引+数据

二分折半查找算法找到30位于15和56之间，然后中间存放的是下一行的地址

8)哈希索引:不支持范围查询，大于小于

为啥要一定使用自增主键？

1)创建主键，默认采用主键索引充当聚簇索引，MYSQL就不会判断唯一索引，生成6个字节的隐藏索引，这样子可以减少MYSQL的工作

2)如果使用UUID，使用UUID的ASCILL码的值来进行比较，如果说有的UUID的前面很长串字符都相等，唯独后面的不相等，那么此时比较的效率就非常低下，但是整数比较效率很高，防止聚簇索引和非聚簇索引的空间非常大，显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

3)整形数据占用的空间小，要使用UUID做主键要解决固态硬盘存储空间的问题

4)防止造成页分裂

为什么非主键索引叶子节点存放的是主键值呢？

首先是为了节省内存空间，其次是为了一致性，如果主键索引和非主键索引都是放的是数据的完整记录，那么再进行数据更新的时候，一致性更新的成本就会非常高，更新值的话，都更新成功才算成功，这样子就会非常麻烦；

联合索引:假设此时根据age字段查询select * from user where age=30，此时会发现age字段不是有序的，此时就不能利用到索引有序的特性，非叶子节点的辅助索引就是叶子结点的第一条记录

Explain执行:

1)id:执行顺序，id越大，越先执行，要是id相等的记录，那么实际的执行顺序就是谁在上面谁先执行

2)explains中的rows:查询出来的行数，explain执行计划+show warning会展现出MYSQL得优化思路，可以看看MYSQL得优化思路

3)select_type:SQL语句执行的一种类型，在select后面 from前面查询出来的表是子查询，from后面要是还有SQL语句就是衍生查询，他们都会产生临时表；

primary是复杂查询，通常这种查询要关联好几种查询，是最外层的select；

simple：简单查询，查询不包含子查询和union；

subquery:包含在select中的子查询，不在from子句中；

derived:包含在from子句中的子查询，MySQL会将结果存放在一个临时表中，也称为派生表derived的英文含义，table字段derived，那么代表着这条SQL语句是从衍生表中查询出来的，后面的derived+数字，代表着是从拿一张表进行衍生过来的；

union:在union中的第二个和随后的select

4)table:当from子句中有子查询时，table列是<derivenN>格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询，当有union时，UNIONRESULT的table列的值为<union1,2>，1和2表示参与union的select行id 

5)type:system>const>eq_ref>ref>range>index>All，一般来说，得保证查询达到range级别，最好达到ref，这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围；

1)system:和查询一个常量一样效率非常高，只有一行记录，是const一种特殊情况；

2)const:唯一索引或者是主键索引和常数进行比较的时候，所以表最多有一个匹配行，读取1次，速度比较快，system是const的特例，表里只有一条元组匹配时为system；

用于primarykey或uniquekey的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快，system是const的特例，表里只有一条元组匹配时为system；

3)eq_ref:使用主键或者是唯一索引进行关联查询，去查询记录的时候直接使用主键ID去关联就可以了，最多只会返回一条符合条件的记录，这可能是在const之外最好的连接类型了，简单的select查询不会出现这种type；

4)ref:查询条件没使用主键索引或者是唯一索引，使用非唯一二级索引查询，并且记录有多条，使用普通索引或者是唯一索引的前缀，或者是联合索引的一部分，相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行

简单的select查询，name是非唯一索引

5)range:范围扫描通常出现在in()，between，>，<，>=等操作中，使用一个索引来检索给定范围的行，虽然使用到了索引，但是要在索引树向后查找；

6)index:扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些，查询的字段全部在索引中，如果使用覆盖索引(查询的所有字段在二级索引和在主键索引中)，优先使用二级辅助索引，因为主键索引扫描的数据量比较大，但是如果要查询的数据不在二级索引中都有而在主键索引中都有，那么此时走主键索引，而不走二级索引，从而保证避免回表查询，遍历索引效率也很低；

7)All:就是扫描主键索引的叶子节点，从头开始找扫描聚簇索引的所有叶子节点；

注意select name，age from user和select name age from user where id=1，操作索引的区别

All和Index的区别:通常Index是二级索引扫描，效率相对来说比较高一些，All是主键索引从头开始扫描，因为遍历的数据太大，扫描的数据量更多，所以效率最低；

而说用到索引时从根节点开始向下折半查找来确定某一个值，这样效率才高，索引一次可能只是找几条记录，几次折半查找搞定了，虽然有可能都使用到了索引，但是使用ref和使用index对于索引的操作是不同的，index是遍历索引的叶子节点，ref是通过二分法来查询要找的数据；

possible_key:可能用到的索引

key:真正用到的索引 

ref:这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有:const常量字段名

rows:这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数;

key-len:使用到的索引的长度，通常对联合索引有效，如果字段允许是null，需要使用到1个字节来记录null，索引的最大长度是768字节，当字符串长度过长的时候，MYSQL会做一个类似于最左前缀索引的处理，将左半部分的字符提取出来做索引；

1)Using index:mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有usingindex；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值；

2)Using where:使用where语句来处理结果，并且查询的列未被索引覆盖，没有使用到索引，如图下面的name字段就没有索引

3)Using index condition:查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

4)Usingtemporary:mysql需要创建一张临时表来处理查询，出现这种情况一般是要进行优化的，首先是想到用索引来优化

使用distinct查询:需要使用临时表进行去重，先把查询所有结果加载到内存里面，然后简称一张临时表进行去重，想要进行优化，直接在去重字段上加上覆盖索引，因为索引树本身是有序的，再扫描索引树的时候，因为数据有序，所以直接可以去重；

5)文件排序:Using fileSort，使用到文件排序

6)Selecttablesoptimizedaway:使用某些聚合函数比如max，min来访问存在索引的某个字段

1)在索引树的非叶子节点里面都没有这么一个东西，left(name，3)是无法走索引的，把字符串截取三个字符在索引树里面进行查找根本就查找不到对应的字符串，无法进行比较，如果那索引的一部分来查找数据，是没有顺序的，因为根本无法定位，如果前三个字符相同呢？，根本无法通过二分法来定位到对应的数据，最终还有可能找不到最终的数据；

2)可能有时候可能全表扫描比走索引扫描更好，MYSQL会进行一个评估，因为有时候查询二级索引的时候本来表中就没有几条记录，这个时候还要通过二级索引来进行回表查询，还要回表，此时还不如全表扫描走得快，MYSQL会选择最小成本，范围查询数据量太大，可以将大的查询范围拆分成小范围；

3)在第二个字段中如果是范围查询，那么第三个字段就不是有序的，第三个字段因为不是有序的，所以就不走索引；

4)不等于结果集太多了，基本不走索引，也有可能走索引；

5)%是因为不知道要跳过多少字符，%后面的字符串还是无序的，但是XXX%在索引树是有序的，用的是最左前缀的那一段字符串，本身就是有序的；

7)但是like "k%%%"无论数据量多少都走索引，存储引擎不能使用索引中范围条件右边的列

explain select 查询列 force index(索引名字) where 条件

in exist走索引，not in，not exist不走索引

like "K%%" 一定会使用到索引下推，但是>一般不使用索引下推，like结果集相比于>=数据量比较小，底层>=结果集太大了，剩下的结果集还要比对后面两个字段，有可能还会将整个表查出来，还要根据最终的过滤的数目条数；

select * from user where username like "lilei%" and age=20 and password="aa"

1)在MYSQL5.6之前，查询过程是现在二级索引中查询到username like"lilei%"的所有字段的ID，然后去主键索引树去查询所有的记录，然后在进行筛选And后面的age=20 and password="aa"的字段，因为找完username以后，age和password不是有序的，所以后面的字段不会看直接回表，因为使用到索引，一定是使用到索引的有序性；

2)但是MYSQL 5.6以后，使用到了索引下推，进行过滤的时候向下继续推断，减少回表次数

3)MYSQL会分析扫描行数和扫描成本(回表次数)，扫描行数不完全决定于扫描成本

4)select * from username in ("a""b") order by password ，age；在这里面一定会使用到文件排序，因为将username='a'和username='b'的结果加起来password和age仍然是没有顺序的

5)select * from user where username>"a" order by username，仍然不是用到索引，最后要走全表扫描，可能是数据量太大了，还要进行回表查询，但是如果修改成覆盖索引，select username from user where username>"a" order by username，就是用到覆盖索引，此时就使用到了索引排序；

1)MYSQL有时候联合索引第一个字段就使用范围查询，那么时候查询的数据量可能非常大，需要进行回表查询，效率非常低，可能还不如全表扫描可以考虑使用覆盖索引优化，可能MYSQL内部就认为第一个范围就使用范围，回表次数非常多，否则要想是强制走索引，要么走覆盖索引，会在辅助索引里面去扫，虽然强制走索引扫描行数减少了，但是回表次数也不少，扫描行数也不能完全决定SQL执行时间的长短；

2)所以基本可以认为like "k%%"是等值查询，把它当作等值查询的原因是因为他使用到了索引下推，like "%kkk"索引失效；

3)or和in有可能走索引也有可能不走索引，in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描，否则还要回表很麻烦，但是like前缀还是会使用到索引；

表中的记录比较多，in和or会走索引，因为要是全表扫描的话效率很低，百万数据中遍历来找要找几行数据不容易；

下面表中的记录比较少，直接全表扫描，都不需要回表了，遍历成本也不高；

4)在使用不等于！=或者<>，not in，not exists的时候无法使用索引会导致全表扫描，这种情况可以将大范围拆分成多个小范围；

5)对于范围查询来说，大于小于，between，MYSQL内部优化器都会根据检索比例和表的大小来多个因素整体评估判断是否使用索引；

6)解决like'%字符串%'索引不被使用的方法使用覆盖索引，查询字段必须是建立覆盖索引字段

单路排序和双路排序:Sort Buffer和max_length_for_sort_data不建议去动

首先了解一下，在MYSQL中排序支持两种方式:

1)FileSort排序:一般是在内存中进行排序，占用的CPU比较多，如果待排序数据比较多，那么会在磁盘中进行排序，会产生大量的临时IO

2)index排序:因为索引数据本身已经有序了，所以不需要额外进行排序

Using Index:使用读索引树来进行排序，二级索引扫描快，数据量少，扫描效率高；

Using fileSort:使用文件排序，拿聚簇索引，效率比较低；

文件单路排序(不回表):直接将所有行记录加载到缓冲池中能够进行排序，select后面需要扫描的字段全部加载到内存里面，就是整张表数据加载到内存，单路排序是一次性的取出所有满足条件行的所有字段，然后再sort buffer中进行排序；

文件双路排序(回表):只需要拿出结果集中的排序的字段和主键ID即可，然后排好序以后再来根据主键ID再进行回表查询，占用的空间小一些，双路排序是首先根据相应的条件取出相应的排序字段和直接可以定位到行记录的主键ID，然后再sortBuffer中进行排序，最后在从索引树中找到最终返回的字段，单路排序占用的内存高，最终的结果就是想要的，双路排序占用的内存低一些，但是最终需要进行回表；

0)注意:"number_of_tmp_files":3这个是执行计划里面的字段，表示使用临时文件的个数这个值为0表示使用的是sort_buffer进行的排序，否则使用的是磁盘文件进行排序，如果数据量比较小，使用sort_buffer进行排序，但是如果过滤的数据量比较大，那么就使用临时文件进行排序，最终还是需要使用临时文件加载到内存中进行排序的，但是一般来说临时文件排序效率低于内存排序，但是尽量也不要增大sort_buffer；

1)sort_buffer小，尽量使用双路排序，可以适当减少max_length_for_sort_data的值

2)sort_buffer大，尽量使用单路排序，可以适当增加max_length_for_sort_data的值

双路排序很慢:MYSQL4.1之前是使用双路排序，意思就是两次扫描磁盘，最终得到数据，先读取行指针和order by的列，对它们进行排序，首先取出order by的列和主键ID，先进行排序，排好序之后再根据行指针主键ID找完整数据，随机IO比较强，就是从磁盘中取出排序字段，然后再buffer中进行排序，然后再从磁盘中取出其他字段，要针对于磁盘做两次IO扫描

单路排序:从磁盘中读取所有需要查询的列，按照order by在buffer中进行排序，然后扫描排序中的列表进行输出，这个熊爱侣相对来说更快一些，因为避免了第二次读取数据，将随机IO改成了顺序IO，但是他会使用更多的空间，因为他把每一行都保存存在内存中了，所以说要尽量避免select *，但是第二种单路排序对于内存的要求比较高，将随机IO变成顺序IO，如果容量不够，就比较尴尬了，select *导致查询的字段比较多，会使用双路排序

单路排序是很好的，但是有问题，因为在sort buffer中，单路排序要比双路排序占用很多空间，因为单路排序是将所有的字段都取出来，但是也有可能取出来的数据总量超过了sort_buffer大小的容量，导致每一次只能取出buffer大小的数据，然后再来进行排序，创建tmp文件多路合并，sort_buffer排序完成以后再来取，从而进行多次IO，大怒排序本来想节省一次IO操作，反而导致了大量的IO操作得不尝失

所以要增加file_sort的容量大小或者是提高max_length_for_sort_data，这个参数是使用单路排序还是双路排序

索引的设计:

1)代码先行，索引至上:是建完表马上就建立索引吗？应该等到主体的业务都开发完成，把涉及到的SQL都需要拿出来分析一下再来建立索引；

2)联合索引尽量覆盖条件:大部分的SQL语句是好几个条件一起来查，如果你建立了太多的单值索引，最终MYSQL只会选择一个单值索引，况且很多5，6个单值索引加起来的存储空间要远远大于针对几个字段建立起来的联合索引，况且单值索引过滤的字段比较少，如果使用联合索引过滤的字段更多，但是如果是唯一索引防止数据重复，还要建立单值索引，不是查询字段快，而是为了保证数据字段的唯一性，比如可以设计一个或者两三个联合索引，尽量少建单值索引，让每一个联合索引都尽量去包含sql语句里的where、orderby、groupby的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则，范围查询应该是用在索引条件的最后一个字段；

3)针对于区分度不高的值不要建立索引，不要再小基数上面建立索引:

 4)长字符串可以建立前缀索引:减少磁盘空间，选择区分度高的长度建立前缀索引

5)如果范围查询不走索引，可以把大范围拆分成多个小范围

6)开启慢查询日志:慢SQL查询会写入到文件里面，后续程序员就可以分析了，超时时间命令也可以进行设置，但是开启慢SQL查询可能浪费性能；

社交场景App:用户筛选，根据地区，省市，性别，年龄，身高，爱好来进行过滤，按照评分(用户受欢迎程度)受欢迎程度进行排序，还要进行分页

select XX from user where 列名=XX and 名字=XX order by XX limit A offset B

1)根据省市城市性别进行筛选，还要分析业务场景，select * from user where provice=XX and city=XX and sex=XX可以建立联合索引，union_index(provice，city，sex)

2)根据省份，城市，年龄筛选:select * from user where provice=XX and city=XX and age>10and age<=10，union_index(provice，city，sex，age)，但是这个时候需要注意，age条件的查询一般都是范围查询，范围查询在联合索引后面一般不会走索引，建议范围的条件的列在后面建，如果把age放在前面，此时age后面字段直接索引失效；

3)但是上面这种情况age不走索引，但是这个时候可以优化SQL语句的写法:

where provice=XX and city=XX and sex in("femal"，“mela") and age>10and age<=10，但是这只是适用于基数比较大的情况，数据量比较小就不用了，但是数据量比较大，in一般在生产环境下都会走索引；

4)处理爱好的字段和sex处理相似union_index(provice，city，sex，hobbly，age)，把爱好全部加到SQL里面去，中间跳过的值直接拼接到SQL里面去，尽量走索引树里面去；

5)这个latest_login_time是为了过滤那些不经常登录的用户的，但是从下面的角度来看，也是需要使用到范围查询的，此时在age后面索引就会失效了，根据实际业务场景让更多的查询走索引；select * from user where provice=XX and city=XX and sex in("femal"，“mela") and age>10and age<=10 and login_time>10；

解决方案就是设置一个标志位，在表中重新建立一个字段，7日之内用户是否登录字段，如果这个用户是在7天前登陆的，就设置成0，如果是在7天之内登陆过的，就设置成1，搞成一个定时任务，7天内没有登陆过就设置成0；select * from user where provice=XX and city=XX and sex in("femal"，“mela") and login_time=1 and age >10and age<=10；

6)如果有的人只是想要根据性别和评分来进行查询，那么就可以根据性别和评分单独建立联合是，可以建立多个联合辅助索引；

7)假设此时出现了这么一种查询:select * from user where provice = XX and city=XX and sex in("femal"，“mela") and login_time=1 and age >10and age<=10 order by score，先满足where条件再来进行order by，因为经过where筛选以后，order by的数据量非常小，此时如果order by使用file sort也没关系，但是如果先order by排序，再来where条件筛选；

SQL优化:

一)分页优化:

select * from user limit 10000 offset 10，在MYSQL的底层是这么来做的，不是从第10000条记录向后查10条，MYSQL先进行查询11000条记录，然后删除前10000条记录，最终只是展现10条查询记录；

1)自增且连续的主键排序的分页查询的例子，前提是按照主键自增还是连续的，但是如果原来的记录删除了，那么这个优化就不好使了；

select * from use limit 90000，5；既不走索引，还是文件排序

最终可以修改成这样:select * from use>90000 offset limit 5；

2)name是联合索引的第一个字段

select * from user order by name limit 10000 offset 10

这里面可能不走索引，因为MYSQL感觉结果集太大，回表效率太低，还不如全表扫描

explain select * from user as s1 inner join (select userID from user order by username limit 10000 offset 10) as s2 on s1.userID=s2.userID;

优化:可以使用覆盖索引来优化，下面的子查询使用到了覆盖索引，本质上是使用二级覆盖索引来扫描索引树的，最终只是需要和s1表做关联即可，还是用到了索引

1)生成了临时表只有5条记录

2)关联的时候使用的是主键关联

二)关联查询优化:

关联查询的连接关系类型要相同，左连接，左表中的数据都要，大不了右边的数据填充为空

都有索引或者都没有索引，优化器会进行判断数据量决定谁是小表谁是大表

一)嵌套循环连接算法(NLG):默认有索引关联

1)对于内连接来说，在两张表都存在索引的情况下，会选择小表作为驱动表，大表作为被驱动表，都没有索引，也是小表驱动大表

2)在被驱动表中添加索引的时候，被驱动的字段要是主键索引效率会更高，因为主键关联字段不需要在进行回表查询了，对于外连接来说前面的表不一定是驱动表，因为查询优化器会将外连接优化成内连接；

一行一行地从第一张表中成为驱动表中读取行，这行中找到关联字段，根据关联字段在另一张表中，也就是被驱动表中取出满足条件的行，然后取出两张表的结果合集

 explain  select * from user inner join class on user.classID=class.id;

SQL语句的执行过程:

执行过程是此时先查询user t1表，在来进行查询class t2表

for(int i=0;i<user;i++){ for(int j=0;j<class;j++){ } }

此时假设根据class的ID这个列建立了唯一主键索引，这个时候仍然是拿着user表中的每一条记录去class表中进行匹配，但是此时假设user表中有100条记录，class表中有10000条记录，此时最终结果就是user表扫描了100次，class表其实因为走索引的原因，也是只扫描了100次，一共扫描的行其实就是200行；

如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低

mysql会选择BlockNested-LoopJoin算法

二)基于块的循环嵌套链接(BNL):不使用索引字段关联，减少驱动表加载到内存的次数，防止频繁读取磁盘IO，注意缓存的不仅是查询条件还有select要查询的条件，所以说用啥查啥，防止过多的无用的列占用joinbuffer的空间，防止多次加载

假设t1表10000条记录，t2表100条记录，把驱动表t2中的数据读取到join buffer中，然后扫描被驱动表，把被驱动表中的每一行记录取出来和join buffer中的记录做对比

Extra中的Using join Buffer说明该关联查询使用的是BNL算法

1)将t2中的所有数据也就是驱动表中的所有数据放到joinBuffer里面

2)把表t1中的每一行数据取出来，和joinbuffer中的数据做对比

3)返回满足join链接的数据

分析:整个过程对于表的t1和t2都做了一次全表扫描，因此扫描的总行数是10000+100条，况且join buffer中的数据是无序的，对于t1表中的每一行，都要扫描整个进行100次判断，所以在内存中的比较次数就是100000*100次

在这个例子中，t2表才100行记录，假设t2要是一张大表，joinbuffer放不下怎么办呢？

首先joinbuffer中的数据是依靠参数join_buffer_size决定的，默认值是256K，如果放不下t2表中的所有记录，那么就分段放，假设t2表中有10000条记录，joinbuffer中最多只能存放800条记录，那么执行过程就是先将t2表中的数据向join buffer中放800条记录，然后从t1表里取数据跟join_buffer中数据对比得到部分结果，然后清空join_buffer，再放入t2表剩余200行记录，再次从t1表里取数据跟join_buffer中数据对比,所以就多扫了一次t1表;

10000*800+10000*200，上面就是执行过程；

如果单纯的让第二种方式按照第一种方式来走的话，不适用内存，那么扫描磁盘的次数就是表1的所有记录*表2的所有记录，就是100W次；

4)被驱动表的关联字段没有索引但是为什么要选择使用BNL算法而不是用NLJ算法呢？

如果上面第二条SQL语句使用NLJ算法，那么扫描的行数就是两张表总行数的乘积，这个是磁盘扫描，很显然来说使用BNL磁盘扫描扫描次数更少，因为驱动表中的记录都已经加载到了内存里面，MYSQL对于被驱动表的关联字段没有索引的关联查询会使用BNL算法，如果有索引一般使用NLJ算法，有索引的情况下NLJ算法更厉害；

优化:

1)被驱动表也就是大表关联字段一定要加索引:你不加索引，走的是BNJ

2)小表驱动大表:先执行的表是小表，后执行的表是大表，大表和小表不应该按照参与的数据集来进行判断，当在写多表连接的SQL查询的时候如果明确知道哪一张表是小表可以采用straight_join的写法固定连接驱动方式，省去MYSQL优化器自己判断的时间，就拿第二种方式来说，要将小表的数据放到join buffer中去，但是如果使用的是大表，有可能join buffer存不下，要放两次，下一次还要加载，这种方式效率会很低；

1)参数设置,block_nested_loop,查看是否开启基于快的嵌套循环连接算法通过show variables like "%optimizer%"来进行查看block_block_nested_loop状态,默认是开启的 2)join buffer size show variables like "%join_buffer_size",默认情况下是256K 驱动表能不能加载完,要看join_buffer能否存储的下所有的数据

数据库基础（七）Mysql Join算法原理 - 知乎 (zhihu.com)

表的行数角度:小的结果集驱动大的结果集，假设此时A表有100条记录，B表有1000条记录，那么乍一看好像是A表是小表，但是再进行连接查询的时候加了一个条件 where b.id<10，过滤完以后B只剩下10条数据，所以此时要看过滤以后的数据，看最终的结果集，B是小表

光看结果集也不行:所以最终的判断体就是表的行数*每一行的大小，从往joinbuffer中方数据，放的每一行的纪录越小越好，从下面的第二条来看，要从查询结果集比较少的字段当小表，防止joinbuffer放满了；

加大join_buffer大小:减少被驱动表加载的次数

表大小的度量单位是:表的行数*每一行的大小，上面更推荐第一种写法，如果要是以t2作为驱动表，需要将t2的所有字段都放到buffer中，要是t1是驱动表，只是把t1.b放在buffer中

在这里面缓存的不仅仅是查询条件，还有select后面要展示的字段，所以说尽量不要使用select *，无用的列会占用join buffer的空间，join buffer中存放驱动表的条目数变少

straight_join解释:straight_join和join功能是类似的，但是可以让左边的表驱动右边的表，能改表优化器对于连表查询的执行顺序，比如说select * from t2 straight_join t1 on t2.a=t1.a

1)straight_join只是适用于inner_join，并不适用于left join，right join，因为他们已经默认制定了表的连接顺序

2)尽可能地让优化器做判断，因为大部分情况下，MYSQL优化器是比人要聪明的，所以使用straight_join一定要慎重，因为人不一定比优化器靠谱

对于小表定义的明确:再决定那张表做驱动表的时候，应该是两张表按照各自的条件进行过滤，计算参与join的各个字段的总数据量，数据小的那个表，就是小表应该作为驱动表

3)假设此时有t1表有100条记录，t2表只有20条记录，此时认为t1表是大表，t2表是小表，这里面大表和小表的定义，是参与关联的数据集，但是执行下面的SQL语句

select * from t1 inner join t2 on t1.id=t2.id where t1.id<10，此时那么t1就是小表，t2就是大表，t1表参与关联的记录只有10行；

4)两个表链接的类型必须是相同的，否则很有可能造成索引失效

使用事务提交:

三)in和exists的优化:

小表驱动大表:让小的数据集驱动大的数据集

1)in:当B表的数据集小于A表的数据集，in优于exists，先执行子查询，再来执行后面的语句

select * from A where id in(select id from B)for(select id in B){ select * from A where A.id=B.id; }

2)exists:当A的数据集小于B的数据集，exists优于in，将主查询中的数据，放到子查询中做条件验证，根据验证结果来判断主查寻中的数据是否保留，先拿出外边查询的结果集，然后再去里层去过滤，肯定是适合于A表比较小的场景；

select * from A where exists (select 1 from B where B.id=A.id);for(select * from A){ select * from B where A.id=B.id; }

1)exists子查询只返回true或者是false，因此子查询中的select *也是可以使用select 1进行替换，官方说法是实际执行的时候会忽略exists清单，因此没有任何区别

2)exists子查询的实际执行过程可能经过了优化而不是我们理解上的逐条进行对比

3)exists子查询往往也可以使用join来进行代替

四)查询总记录的行数

1)字段存在索引:count(*)=count(1)>count(字段)>count(主键)

当字段有索引的时候，count(字段)如果字段有索引，走二级索引，二级索引不包含所有字段，二级索引的存储的数据比主键索引少，所以count(字段)>count(主键)

2)字段没有索引:count(*)=count(1)>count(主键)>count(字段)

当字段没有索引，count(字段)走不了索引，count(主键ID)还可以走主键索引，所以count(主键)>count(id)

在上面的四种SQL只有count(字段)不会统计字段是null的数据行

1)count(1)和count(字段):他们都是扫描的是二级索引树，count(1)>count(name)，count(name)在进行扫描的时候会把name的索引值取出来放到内存中，然后额外会使用一个计数器++来统计name的个数，但是count(1)不会把name从索引树中拿出来放到内存中，只是遍历非聚簇索引的叶子节点有一条记录就在内存中有一个计数器+1，不需要取出二级索引的数据，count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点；

2)count(*)不会把字段取出来，不取值，按行累加，所以说效率很高

3)对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)

4)count(字段)是不会统计null值得；

5)count(主键id)还可以走主键索引，所以count(主键id)>count(字段)

6)count(*)是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代count(*)，但是如果表中的数据量依然很大的话，这种count方式效率仍然很低，innodb存储引擎不同事务count(*)结果集不相同，不适合做count(*)的维护，但是MYSIM使用了一个额外的变量来记录总数据量，对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算，有条件就比较麻烦了，因为有条件还需要维护结果行记录；

如果对总记录数要求不是特别准确，可以采用下面的方式来进行统计

7)show table status如果只需要知道表总行数的估计值可以用如下sql查询，性能很高

8)将总数维护到Redis里插入或删除表数据行的时候同时维护redis里的表总行数key的计数值用incr或decr命令，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性，redis维护一个原子计数器，可能使用缓存，无法达到数据百分之百一致

9)创建一个计数表:维护表的总数:<表名，总数>，然后走索引，效率非常高

1)在MYSQL中子查询会先执行，先从数据库中得到子查询结果缓存到内存里面，然后再从这个结果和主查询进行查询进行筛选；

2)对于链接查询来说，嵌套循环算法更高效；

一般来说子查询的效率不高，因为

1)执行子查询的时候，MYSQL需要对内层查询语句的查询结果创建一个临时表，然后外层查询语句会从临时表中查询记录，查询完毕之后再进行撤销这些临时表，这样可能会导致消耗过多的CPU和IO资源，产生大量的慢查询

2)子查询的结果需要存储临时表，不论是内存临时表还是磁盘临时表都是不会创建索引的，这样会导致查询结果受到影响，尤其是针对于查询结果集比较大的子查询去，效率是特别低的，所以在MYSQL中可以使用连接查询来代替子查询，连接查询不需要创建临时表如果被驱动表上面创建了索引那么查询效率还会变得更高，还可以将查询替换成多个查询

五)排序优化:order by也要注意最左前缀法则

这条SQL语句是先按照age降序排序，age相同使用phone降序排序

1)order by不加limit索引失效

1)假设根据age，classID和name建立了联合索引

unique_index(age，classID，name)

select * from user order by age，classID

从结果上看没有使用到索引，明明有联合索引但是没有使用，这就涉及到查询优化器的优化策略了，如果使用上索引，就用这个age和classID，使用完成这个二级索引之后发现数据量很大，还是需要进行回表查询进行查询完整数据，此时还不如filesort花的时间少，内存级别重新排序即可；

2)但是select age，classID from user order by age，classID此时没有回表使用的是覆盖索引

3)select * from user order by age，classID limit 3，加上limit，此时就使用到了索引，此时数据量有限制，已经排好序了，回表操作只是发生了十次

2)order by顺序错误，索引失效

第一种和第二种不使用联合索引，索引失效，第三种使用到了联合索引，三个字段都会用到索引，第四种(name还是有序的状态)和第五种也会使用到索引

3)order by排序规则不一致，不使用索引，联合索引顺序错，不索引，方向反，不索引

第一种和第二中不使用索引因为顺序是反的，第三种也不能使用到索引(顺序又要逆序来一遍，和数据特点有关系)，只有第四种可以(同是升序或者是降序)

还会提示backword index scan

 4)无过滤不索引:先进行过滤数据

第一种，第二种在where条件已经过滤掉了大量大部分的数据，剩下的进行order by的数据已经很少了，可能就不再使用索引排序了，只有age使用索引，没有管classID，经过age筛选后数据已经有序了；

第三种数据量太大，不用索引；

第四种order by先index age排序，再从排序的结果中找到classID=45的记录，最后限制10条，最后索引全部用上了；

第一种情况只会使用到age的索引，第二种会使用到age和stuno的索引

六)Insert语句的优化

1)Insert语句的优化:如果向数据库中插入数据，可以从下面几个方面来进行优化:

1.1)批量插入数据，values后面加上多个括号

1.2)手动控制事务，关闭自动提交，避免大量用到事务，因为DML语句会自动开启事务

1.3)按照主键顺序插入

2)大批量插入数据，比如说是几百万这样的数据，使用insert语句插入的性能比较低，此时可以使用MYSQL数据库提供的load指令来进行插入，直接将本地磁盘中的数据记录直接加载到磁盘中，本地磁盘中的文件并不是符合要求的SQL语句，而是符合表中记录的数据

2.1)客户端连接服务端的时候，加上参数 local -infile

mysql --local -infile -u root -p

2.2)设置全局参数local_infile 是1，开启从本地文件导入数据的开关:

set global local_infile=1；

2.3)我们可以进行查看指令是否开启:select @@local_infile，默认开关关闭

2.4)执行数据:将文件上传到指定路径

要知道文件路径load data local infile "+"文件路径/文件名"

 into table 表名 fileds terminated by ' ，' lines terminated by '\n'；

主键优化:

1)在满足业务需求的情况下，尽量降低主键的长度，尽量短，因为主键多了，二级索引的叶子结点数据就会比较多，占用空间比较大，逐渐越长，二级索引的叶子节点也是越长的

2)插入数据的时候，尽量选择自增主键，按照顺序插入使用auto_increment，否则如果说不适用顺序插入，可能会产生页分裂的现象

3)尽量不要使用UUID或者其他自然主键比如说身份证号，生成的UUID插入就是乱序插入，身份证号长度过长浪费大量磁盘IO

4)业务操作，尽量避免对主键的修改，因为可能会修改页

索引组织表:在我们的InnoDB引擎中，表的数据都是按照主键顺序进行存放的，这种存储方式的表称之为索引组织表

1)行数据都是存储在聚簇索引上面的叶子节点上面的，数据行是存储在逻辑结构页里面的，每一个页的大小是固定的，就是16K，一个页中存储的行也是固定的，如果说插入的行再该页中存储不下去了，将会存储到下一个页里面，叶和叶之间将会通过指针来进行连接

2)其实索引简单来说，其实就是通过二分法不断减少要筛选的数据，而主键值就是筛选的标准，以尽快定位到我们需要的数据

七)分组查询查询:

group by进行分组操作:主要是有Using temporary

1)在分组操作的时候，要可以根据索引来进行提高查询效率，况且尽量使用覆盖索引

2)在进行分组操作的时候，索引的使用也是满足最左前缀法则的(多字段分组)，先过滤

3)再进行分组操作的时候，查询的语句要写分组的字段以及聚合函数

页分裂:

页可以为空，也可以填充一半，也可以填充100%

主键顺序插入效果:

1)在磁盘中申请页，主键顺序插入

2)申请一个页，第一个页没有满，向第一个页进行插入

3)当第一个页写满之后，在进行写入第二个页，页和页之间通过指针进行连接

4)第二页写满之后，在继续向第三页进行写入

主键乱序插入:

1)现在咱们想要插入一条id=50的记录，此时我们不应该创建一个第三页，把50写到里面，因为主键是按照顺序来进行排放的，也就是说索引结构的叶子节点是存放有数据的，按照顺序应该排在47之后；

2)但是此时47所在的第一页的数据已经存放满了，存储不了50了，那么此时的解决办法就是开辟一个新的页3；

3)但是此时并不会将50直接存入到第三页，而是将第一页中一半的数据放到第三页，然后在第三页里面插入50；

但是此时的指针指向是存在问题的

所以说此时的解决方法就是改变指针的指向，重新设置链表指针

页合并:

当对已有的数据进行删除的时候，实际上记录并没有被物理删除，而是说记录被标记删除并且他的空间允许被其他有用的记录所进行覆盖

想进行删除15,16的数据的时候，只是做了一个标记，并没有真正的进行删除 

叶子中删除的记录达到页的50%的时候，InnoDB会去寻找最靠近的页，前面或者后面，看看是否可以将两个页合并在一起进行优化空间使用，就可以把17 18 19和前面的页进行合并

 

1)三张表不要join，可以在java代码进行拆解，MYSQL是不容易扩展的

2)定长字符串使用char

3)索引的长度和区分度是一对矛盾体，一般对于字符串类型来说，长度为20的索引，区分度会高达90%，可以使用count(distinct(left(列名，索引长度))/count(*)的区分度来确定

4)页面搜搜禁止左模糊或者是全模糊，如果想要优化使用覆盖索引或者是搜索引擎来解决

5)如果有order by的场景，请注意利用索引的有序性，order by在以后的字段是组合索引的一部分吗，并且放在索引组合顺序的最后避免出现文件排序

where a=? and b=? order by c，建立索引a_b_c

索引如果使用范围查询，那么索引有序性可能就无法使用，比如 where a>10 order by b，此时索引a_b无法排序；

4)in得记录数比较少不会走索引

5)utf8字符集一个字符三个字节，utf8mb4一个字符四个字节，表情是4个字节，尽量使用utf8mb4来存放；

6)如果可以确定一个一个数字的取值范围，那么使用更小的范围来存储，如果确定是由符号无符号一定要构；

7)timestampe尽量小公司使用，因为占用的空间比较小datetime时间无限长但是占用空间大

8)尽量少使用BloB和text，如果实在要使用可以考虑将Blob和text字段单独存放在一张表面，使用id关联，意思是如果非要使用blob或者是text，就创建一张新的表，使用id进行关联，因为blob或者是text大不多数业务场景下经常不需要查找这样的纪录，使用id关联创建一张新表，如果blob或者是text放在聚簇索引上，那么会对整体的查询效率性能有影响，所以说当扫描主键索引的时候，只要不去查找两个大字段，效率就比较高