一、索引的分类及查询原理

一、常见索引及其区别

主键：用于约束数据库表记录的唯一性，不可为空；在创建主键的时候数据库会自动创建一个唯一索引，也叫主键索引；使用primary key进行定义

唯一索引：具有唯一性，一个表可具有多个唯一索引，用于加速查询；使用unique index定义

普通索引：与唯一索引类型类似，但不具有唯一性，也可以为空，用于加速查询；使用index定义

非主键索引：构成索引的属性列为非主键

复合索引：多个属性列构成的索引，注意属性列的顺序之分，离散度即唯一性越高的越靠前，查询效率也好；组合索引具有“最左前缀”的约束，简单的理解就是只从最左面的开始组合，
例(a,b,c)复合索引下，只有a、(a,b)及(a,b,c)这三个组合查询下才会使用索引，像(b,c)或c等等这种就不会使用索引查询

注意：虽然索引大大提高了查询速度，但是过多的索引却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

聚集索引：也叫聚簇索引，一个表只能有一个聚集索引；聚集索引存储记录是物理上连续存在；聚簇索引的叶节点就是数据节点。

非聚集索引：一个表可以有多个非聚集索引；非聚集索引存储记录是物理上不连续存在；非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。（例如：二级索引）

二级索引/非聚簇索引/辅助索引:如下图所示，name字段的B+树也会构建多层索引页，这个索引页里放的是下一层的页号和最小name字段值。
(1)使用原理
   假设你要根据name字段来搜索数据，比如：select * from user where name=‘xxx'，过程与主键索引一样的。从name索引的根节点开始找，一层一层的向下找，一直找到叶子
节点，定位到name字段值对应的主键值。但此时叶子节点的数据页没有完整所有字段，就需要根据主键到主键索引里去查找，从主键索引的根节点一路找到叶子节点，就可以找到这行数据
的所有字段了，这个过程就叫回表。（回表的含义）
(2)索引弊端
存储角度=>   首先，要创建索引，就要占用存储空间。我们每创建一个索引，MySQL就会搞出一个B+树，每棵B+树都要占用很多的磁盘空间啊，所以搞太多索引，也是很耗费磁盘空间的。
增删改维护角度=>   其次，你在进行增删改的时候，每次都需要维护各个索引的数据有序性，因为每个B+树都要求页内是按照值大小来排序的，页之间也是有序的。所以你不停的增删改，
各个索引的数据页要不停的分裂、增加新的索引页，如果你一个表里搞太多索引，增删改的性能就会比较差

二、总结
1、索引的定义
  索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是 索引是数据的目录。
2、存储引擎的定义
  存储引擎，说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。MySQL 存储引擎有 MyISAM 、InnoDB、Memory，其中 InnoDB 是
在 MySQL 5.5 之后成为默认的存储引擎。
3、MySQL的顶层结构图
待补充..................
4、索引分类
按「数据结构」分类： B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类： 聚簇索引（主键索引/聚集索引）、二级索引（辅助索引/非聚簇索引/非聚集索引）
按「字段特性」分类： 主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类： 单列索引、联合索引（复合索引）。
5、各存储引擎所支持的数据结构索引
InnoDB：B+tree索引
MyISAM：B+tree索引、Full-text索引
Memory：B+tree索引、Hash索引
6、在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：
（1）如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
（2）如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
（3）在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；
注：其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。 创建的主键索引和二级索引默认使用的是 B+Tree 索引。
7、B+树索引的存储及查询原理
（1）数据库的索引和数据都是存储在硬盘的，我们可以把读取一个节点当作一次磁盘 I/O 操作。假如一共经历了 3 个节点，也就是进行了 3 次 I/O 操作。
（2）B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以 B+Tree 相比于 B 树和二叉树来说，最大的
优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。
（3）主键索引的 B+Tree 和二级索引的 B+Tree 区别：
a. 主键索引的 B+Tree 的叶子节点存放的是（实际数据），（所有完整的用户记录）都存放在主键索引的 B+Tree 的叶子节点里；
b. 二级索引的 B+Tree 的叶子节点存放的是（主键值），而不是实际数据。
c. 会先检索二级索引中的 B+Tree 的索引值，找到对应的叶子节点，然后获取（主键值），然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。 这个过程
叫「回表」，也就是说要查两个 B+Tree 才能查到数据。
（4）覆盖索引的定义
  这种在二级索引的 B+Tree （就能查询到结果）不需要回表的（过程）就叫作「覆盖索引」，也就是只需要查一个 B+Tree 就能找到数据。
（5）B+Tree 相比于 B 树、二叉树或 Hash 索引结构的优势：磁盘I/O次数更少，查询速度更快
==>B+Tree vs B Tree
B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。
==>B+Tree vs 二叉树
对于有 N 个叶子节点的 B+Tree，其搜索复杂度为 O(logdN) ，其中 d 表示节点允许的最大子节点个数为 d 个。

在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 
操作就能查询到目标数据。

而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN) ，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。
==>
Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。

但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。
8、按特性分类
（1）主键索引：就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。
（2）唯一索引：唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
CREATE UNIQUE INDEX 索引名 ON 表名(index_column_1,index_column_2,...);
（3）普通索引：就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。
CREATE INDEX 索引名 ON 表名(index_column_1,index_column_2,...);
（4）前缀索引：是指对（字符类型字段的前几个字符）建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。
使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。
CREATE INDEX 索引名 ON 表名(column_name(length));
9、按列数分类
（1）单列索引
建立在单列上的索引称为单列索引，比如主键索引；
（2）复合索引
a. 建立在多列上的索引称为联合索引；
b. 因此，使用联合索引时，存在 最左匹配原则，也就是按照最左优先的方式进行索引的匹配。所以，在创建复合索引以及实际查询时要注意列的顺序，查询条件一定是要带上最左边的列，
才能命中索引，否则索引失效。需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。
c. 建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中 建立联合索引时，要把区分度大的字段排在前面，这样区分度大的
字段越有可能被更多的 SQL 使用到。

 

二、InnoDB和MyisAM引擎的区别

//MySQL本身并没有对单表最大记录数进行限制,这个数值取决于你的操作系统对单个文件的限制本身。业界流传是500万行。超过500万行就要考虑分表分库了。

//在操作系统中，我们知道为了跟磁盘交互，内存也是分页的，一页大小4KB。同样的在MySQL中为了提高吞吐率，数据也是分页的，不过MySQL的数据页大小是16KB。
（确切的说是InnoDB数据页大小16KB）


1、innodb支持事务，而myisam不支持事务。
2、innodb支持外键，而myisam不支持外键。
3、innodb默认表锁，使用索引检索条件时是行锁，而myisam是表锁（每次更新增加删除都会锁住表）。
4、innodb和myisam的索引都是基于b+树，但他们具体实现不一样，innodb的b+树的叶子节点是存放数据的，myisam的b+树的叶子节点是存放指针的。
5、innodb是聚簇索引，必须要有主键，一定会基于主键查询，但是辅助索引就会查询两次，myisam是非聚簇索引，索引和数据是分离的，索引里保存的是数据地址的指针，主键索引
和辅助索引是分开的。
6、innodb不存储表的行数，所以select count( * )的时候会全表查询，而myisam会存放表的行数，select count(*）的时候会查的很快。

总结:mysql默认使用innodb，如果要用事务和外键就使用innodb,如果更新删除增加频繁就使用innodb;如果这张表只用来查询，可以用myisam。


解决方案总结：
1、方法一：将频繁更新和读的库表的引擎由Myisam改为Innodb；

2、方法二：更改Myisam引擎的系统变量concurrent_insert为1或2，专门用以控制其并发插入的行为；
0：不允许并发操作
1：如果MyISAM表中没有空洞（即表的中间没有被***的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
2：无论MyISAM表中有没有空洞，都允许在表尾并发插入记录

3、方法三：降低写操作的优先级
使用--low-priority-updates启用mysqld。这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级。在这种情况下，在先前情形的最后的SELECT语句将在INSERT语句前执行。

4、方法四：控制读写锁数量
为max_write_lock_count设置一个低值，使得在一定数量的WRITE锁定后，给出READ锁定

5、方法五：特定sql控制优先级
=>使用LOW_PRIORITY属性给于一个特定的INSERT，UPDATE或DELETE较低的优先级
=>使用HIGH_PRIORITY属性给于一个特定的SELECT
=>使用INSERT DELAYED语句

6、方法六：从sql设计上或业务上进行优化

 

三、慢查询分析方法

 

//1、Linux服务器进入mysql
[rich@localhost ~]$   isql -v mysql

//2、执行sql:举例
SQL> select * from addr.tb_user_detail_info where rm_userid='admin2';
此处建议使用：用 \G 代替‘;’，表示格式化，数据量大的时候可以这样用，更清晰。每一条数据的列属性都是纵向
SQL> select * from addr.tb_user_detail_info where rm_userid='admin2'\G

//SQL优化问题排查手段一：3、慢sql分析：如何记录和找出哪些是慢sql？
（1）慢查询日志的开关
查看：SHOW VARIABLES LIKE '%slow_query_log%';
设置：SET GLOBAL slow_query_log=1;       //1：开启；0：关闭
（2）慢查询日志的存储路径
查看：SHOW VARIABLES LIKE '%slow_query_log_file%';
设置：SET GLOBAL slow_query_log_file='xxx';
（3）慢SQL的阈值
查看：SHOW [GLOBAL] VARIABLES LIKE '%long_query_time%';
设置：SET GLOBAL long_query_time=3;
（4）查看多少SQL语句超过了阙值
查看：SHOW GLOBAL STATUS LIKE '%Slow_queries%';
（5）使用日志分析工具mysqldumpslow
----进入MySQL的安装目录中的bin目录下；
 执行 ./mysqldumpslow --help 查看帮助命令；
----
s:是表示按照何种方式排序
c:访问次数
i：锁定时间
r：返回记录
t：查询时间
al:平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：即为返回前面多少条数据
g：后边搭配一个正则匹配模式，大小写不敏感
 
----常用参考：
 得到返回记录集最多的10个SQL:
 ./mysqldumpslow -s r -t 10 /home/rich/var/mysql/slow-query.log;
 得到访问次数最多的10个SQL:
 ./mysqldumpslow -s c -t 10 /home/rich/var/mysql/slow-query.log;
 得到按照时间排序的前10条里面含有左连接的查询语句:
 ./mysqldumpslow -s t -t 10 -g "left join" /home/rich/var/mysql/slow-query.log;
 使用这些语句时结合| more使用


//SQL优化问题排查手段二：4、(执行计划)explain关键词使用：如何分析sql语句的执行问题，是否缺少索引等？
=>explain 具体sql，得到具体的执行结果
(1)id
id相同，读表顺序由上到下；id越大执行的优先级越高
(2)select_type
数据读取的操作类型：
a. SIMPLE     简单的select查询，查询中不包含子查询或union查询
b. PRIMARY    若查询中包含复杂的子查询部分，则最外层查询被标记为PRIMARY
c. SUBQUERY   在select或where列表中包含了子查询
d. DERIVED    在from列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归这些子查询，把结果放在临时表里
e. UNION  在union之后的select会被标记为union，若union包含在from子句的子查询里，最外层被标记DERIVED
f. UNION RESULT  从union表获取到结果的select
(3)table
表名
(4)type
访问类型：性能由高到低为=>
a. system：只有一行记录，相当于系统表，可以忽略不记
b. const：常量，表示通过索引一次就找到了，常用于匹配主键或者unique索引，值匹配一行数据
c. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
d. ref：非唯一性索引扫描，返回匹配到的某个值的所有行
e. range：只检索给定范围的行，一般情况下是where语句后加了between,>,<,in等条件
f. index和ALL：index遍历全部索引，ALL读全表，index比ALL块因为index从索引读取，ALL从硬盘读取
(5)prossible_key
表可能应用的索引，可理解为该表定义了哪些索引
(6)key
执行过程中实际使用即命中到的索引
(7)key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精度的情况下，长度越短越好
(8)ref
显示和索引匹配的哪一列被使用了，展示的就是与索引列做等值匹配的条件，如常量或某个列
(9)rows
MySQL执行过程中认为必须检查的行数，rows值越少越好
(10)extra
执行计划里很重要的一个附加列，可根据此列进一步分析sql存在的问题，以作进一步的优化：
a. Using filesort:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySql中无法利用索引完成的排序操作称为”文件排序“
b. Using temporary:用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。
c. Not exists:MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了。
d. Using index:说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现using where，表明索引被用来执行索引键值的查找，
没有using where，表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的，但无需再回表查询记录。
e. Using index condition:这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了
不必要的IO操作，但是只能用在二级索引上。
f. Using where:使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件
过滤。
g. Using join buffer:使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接
h. impossible where:where子句的值总是false，不能用来获取任何元组
i. select tables optimized away:在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，
查询执行计划生成的阶段即完成优化。
j. distinct:优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作


//SQL优化问题排查手段三：5、show profile使用：查询SQL语句在服务器中的执行细节和生命周期

 

 

四、常见SQL优化问题

 

//1、什么时候需要/不需要索引？
==>需要索引
（1）字段有唯一性限制的，比如商品编码；
（2）经常用于 WHERE 查询条件的字段；
（3）经常用于 GROUP BY 和 ORDER BY 的字段；
==>不需要索引
（1）WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的；
（2）字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女；
（3）表数据太少的时候，不需要创建索引；
（4）经常更新的字段不用创建索引，比如电商项目的用户余额，因为索引字段频繁修改，那就意味着需要频繁的重建索引；

//2、常见索引优化
（1）前缀索引优化
   前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些
大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。
==>局限性：
order by 就无法使用前缀索引；
无法把前缀索引用作覆盖索引；
（2）覆盖索引优化
  覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。
  我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。
（3）主键索引最好是自增的
a. 如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，（不需要移动已有的数据），当页面写满，就会（自动开辟一个新页面）。因为不需要重新移动
数据，因此这种插入数据的方法效率非常高。

b. 如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将（不得不移动其它数据）来满足新数据
的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为 （页分裂）。 页分裂还有可能会（造成大量的内存碎片），导致索引结构不紧凑，从而影响查询效率。
（4）防止索引失效
===>用上了索引并不意味着查询的时候会使用到索引，所以我们心里要清楚有哪些情况会导致索引失效，从而避免写出索引失效的查询语句，否则这样的查询效率是很低的。
a. 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效；
b. 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下索引失效是因为查询过程需要扫描整个索引并回表，代价高于直接全表扫描，所以优化最终选择走全表扫描。
c. 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
d. 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
e. 为了更好的利用索引，索引列要设置为 NOT NULL 约束。
f. 使用select *不走覆盖索引，因为还需要作回表处理
g. 范围索引列没有放最后
h. 字符类型数据没有使用引号''
i. 使用is null或is not null条件时没有注意该字段是否允许为空

//3、SQL优化问题：加索引？变更SQL结构？SQL优化不了，从业务层面去优化：变更查询条件还是加缓存？
常见sql问题优化=>
(1)避免使用select *
不会走覆盖索引，即需要进行回表来查询所有字段，效率低
(2)用union all替代union
unionall不去重，但union是去重的：需要增加排序和比较的时间花费
(3)小表驱动大表
核心思想：即用少数据量作为条件去查询表
==>如果sql语句包含了in关键词，那么它会优先执行in里面的子查询，然后才执行外面的查询；
==>如果sql语句包含了exist关键词，那么它优先执行exist左边的查询人，然后才执行外面的查询；
总结：假如查询语句左边是大表，右边是小表，明显使用in关键词的查询速度要更快，因为先进行的是in里面的子查询，根据这些条件查询速度会更快；而使用exist先查大表，把它作为
条件明显数据更多，再去匹配右边的，查询速度相对要慢。所以，如果左边是大表右边是小表则优先使用in关键词，反之则优先使用exist关键词。
(4)批量操作
==>不能在遍历里执行SQL。因为执行SQL需要基于TCP/IP三次握手和四次释放来建立或释放数据库连接，查询过程也需要进行磁盘I/O，如果循环执行SQL则会发生频繁连接处理和
磁盘I/O的情况，这是极其耗时的甚至如果循环次数过大时可能会把数据库搞挂。
总结：所以，涉及批量业务时，我们最好是使用批量SQL执行一次处理即可。但是这个批量数据也不是没有上限的，极大的时候也存在数据库响应慢的问题，一般建议是1000个作为一次批量
即可。
(5)多用limit
==>当查询数据时如果只想获取其中几条数据可用limit关键词，例如按创建时间升序获取第一条则可以使用limit 1，而不需要把所有的查出来再在程序里通过get(0)来获取，这样效率
更高。
==>当执行更新/删除操作时，使用limit可以有效避免因为传参错误等误操作导致大范围数据受影响
(6)in里的值太多了
==>顾名思义，in里面的值不能太多，否则过多也会造成数据库响应慢，如果查询的值比较多，则需要程序分批处理，例如每次处理1000个，如果有3000个则分成3个1000，例：可以生成
条件为，
字段名 in (1,2,..,1000) or 字段名 in (1001,1002,..,2000) or 字段名 in (2001,2002,..,3000)。
(7)增量查询
==>假如有个场景，需要将某一张表的数据从A库复制到B库，那么我们可以按id或创建时间进行数据升序，控制每次固定只查询x条记录，并且记录查询出来的记录最大的id或创建时间，下
次查询就可以只增量查询大于id或创建时间的x条记录即可。
(8)高效分页
===>当数据量过大时，使用limit分页其实也可能存在性能问题。例如，limit 1000000,20，这样会摒弃前100万条数据，只查后20条数据，这样是非常耗费资源的。
===>优化方案：
方案一：记录上一次分页的最大id，使用id索引去查询，例：将上面的分页条件改为id>1000000 limit 20。要求：id是连续且有序的。
方案二：使用between去查询，例：id between 1000000 and 1000020。要求：要在唯一索引上进行分页，否则会出现分页大小不一致的情况。
(9)用连接查询代替子查询
===>子查询可以使用in关键词，使得一个Select查询的条件落在了另一select查询的结果集里。它的优点是简单、结构化，前提是表少的时候；但它的缺点是MySQL会基于子查询创建
临时表，查询完毕之后还要删除临时表，这无疑增加了一些额外的时间损耗
===>优化方案：使用连接查询替代子查询，一般效率会高很多。
(10)join的表不宜过多
===>某里大厂开发手册要求join的表不能超过3个。如果连接的表过多时，那么MySQL在选择索引时会非常复杂，很容易选错索引，并且如果没有命中索引的话，那么nested loop join
(嵌套循环连接)会将两个表的数据进行两两对比，复杂度为n²。
(11)join时要注意
===>我们在作多表连接查询时，一般使用的比较多的是inner join和left join，前者取的是交集，后者包含交集及左表剩余的数据。在满足业务的情况下优先使用inner join，原因
如下：
===>inner join：当使用inner join时，MySQL会自动进行小表驱动大表，执行效率会更高；
===>left join：默认会用左表来驱动右表，加入左表为大表时，那么可能就会出现性能问题
(12)控制索引的数量
===>索引数量并不是越多越好，某里大厂开发手册要求单表的索引个数尽量控制在5个之内，复合索引的字段数不能超过5个。因为过多的索引会占用过大的磁盘空间，而且在进行数据
增删改时，MySQL还要去重建索引树去维护索引的有序性，这样也会造成一定的性能问题。
(13)选择合理的字段类型
===>对于固定长度的实际数据，字段选择char固定长度字符串类型，例手机号；
===>对于可变长度的实际数据，字段选择varchar可变长度字符串类型，例企业名称，避免使用char可能会造成空间浪费
===>能用数字类型，就不用字符串类型，因为字符串处理要比数字慢
===>尽可能用小的类型，如用bit存布尔值，用tinyint存枚举值
===>金额字段用decimal类型，避免精度丢失问题
(14)提升group by的效率
===>group by可以放在表名后，也可以放在where条件后。但是它们的执行过程是不一样的，前者是先对全表数据进行分组后再进行过滤查询，而后者是先条件过滤再对这些需要的数据
进行分组即可。显然group by放在where条件之后的执行效率要更高。
(15)索引优化
===>有时候，同条sql，传入不同的参数，可能走的索引情况不一样，这时候一般需要使用explain命令来查看一下MySQL的执行计划，从而作进一步的优化

 

 

五、MySQL常见锁及处理

 

一、常见锁类型
1、按锁粒度分类
（1）表级锁，锁定整张表，不会产生死锁
（2）页级锁，锁定一页
（3）行级锁，行级锁并不是直接锁记录，而是锁索引。当查询没有使用到索引，那么InnoDB会对表所有数据进行锁定，其实效果跟表级锁一样。
2、按加锁机制分类
（1）悲观锁，抽象性质，其实不真实存在，可以理解为一种思想
//定义：顾名思义，就是对于数据的处理持悲观态度，总认为会发生并发冲突，获取和修改数据时，总认为别人会修改数据。所以在整个数据处理过程中，需要先将数据锁定。
//作用：悲观锁在并发控制上采取的是先上锁然后再处理数据的保守策略，虽然保证了数据处理的安全性，但也降低了效率。
//实现：通常依靠数据库提供的锁机制实现，比如mysql的排他锁，select .... for update来实现悲观锁。
//基本使用过程：
第一步：select .... for update    //开启事务，上锁
第二步：update...
第三步：commit;      //提交事务，同时释放锁
//注意事项：使用悲观锁，需要关闭mysql的自动提交功能，将 set autocommit = 0;
（2）乐观锁，抽象性质，其实不真实存在，可以理解为一种思想
//定义：顾名思义，就是对数据的处理持乐观态度，乐观的认为数据一般情况下不会发生冲突，只有提交数据更新时，才会对数据是否冲突进行检测。
//作用：乐观锁是基于程序实现的，所以不存在死锁的情况，适用于读多的应用场景。如果经常发生冲突，上层应用不断的让用户进行重新操作，这反而降低了性能，这种情况下悲观锁就
比较适用。
//实现：乐观锁的实现不依靠数据库提供的锁机制，需要我们自已实现，实现方式一般是记录数据版本，一种是通过版本号，一种是通过时间戳。
//基本使用过程：
第一步：表增加版本号或时间戳字段
第二步：查询数据的同时，把版本号或时间戳也查出来
第三步：update更新数据时，并判断版本号或时间戳是否与之前查出来的一样，一样则成功更新；不一样则表示该数据已过期即别人更改过，拒绝更新，让用户重新操作。
3、按兼容性分类
（1）共享锁，也叫S锁，在MyISAM中也叫读锁
//定义：对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源（也可以再继续加共享锁，即 共享锁可多个共存），但无法修改。要想修改就必须等所有共享锁都释放完之后。
//使用：
select * from table lock in share mode;
（2）排他锁，也叫X锁，在MyISAM中也叫写锁
//定义：对某一资源加排他锁，自身可以进行增删改查，其他人无法进行任何操作。
//使用：
select * from table for update;
注意：数据库规定同一资源上不能同时共存共享锁和排他锁，如果某线程对A表加了共享锁，则另一线程需要等它释放了共享锁才能给它加排他锁；另外，不管是表级锁还是行级锁，都会存
在共享锁和排他锁；for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

4、按锁模式分类
（1）Record锁：锁记录，不锁gap
（2）gap锁：不锁记录，锁gap
（3）next-key锁：既锁记录，又锁gap，等同于record锁+gap锁
（4）意向锁
（5）插入意向锁

二、死锁发生的常见场景及解决
1、情景一
（1）过程：（不同表之间=>用户1访问A又访问B，用户2访问B后又访问A）
   ⼀个⽤户A 访问表A(锁住了表A),然后⼜访问表B；另⼀个⽤户B 访问表B(锁住了表B)，然后企图访问表A；这时⽤户A由于⽤户B已经锁住表B，它必须等待⽤户B释放表B才能继续，同样
⽤户B要等⽤户A释放表A才能继续，这就死锁就产⽣了。
（2）解决方法：（分析程序逻辑，保证按相同的顺序去处理多表）
   这种死锁⽐较常见，是由于程序的BUG产⽣的，除了调整程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进⾏处理，尽量避免同时锁定
两个资源，如操作A和B两张表时，总是按先A后B的顺序处理，必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。
 2、情景二
 （1）过程：（同表同记录=>用户1查某条数据后又修改该记录，用户2修改该记录）
   ⽤户A查询⼀条纪录，然后修改该条纪录；这时⽤户B修改该条纪录，这时⽤户A的事务⾥锁的性质由查询的共享锁企图上升到独占锁，⽽⽤户B⾥的独占锁由于A 有共享锁存在所以必须等
A释放掉共享锁，⽽A由于B的独占锁⽽⽆法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁⽐较隐蔽，但在稍⼤点的项⽬中经常发⽣。如在某项⽬中，页⾯上的按钮点击后，
没有使按钮⽴刻失效，使得⽤户会多次快速点击同⼀按钮，这样同⼀段代码对数据库同⼀条记录进⾏多次操作，很容易就出现这种死锁的情况。
 （2）解决方法：
a. 对于按钮等控件，点击后使其⽴刻失效(防抖动)，不让⽤户重复点击，避免对同时对同⼀条记录操作。
b. 使⽤乐观锁进⾏控制。

三、其它

 

 

六、一些易混淆知识点的区分

一、多表连接查询之间的区别
//1、全表连接               //使用","或join或CROSS JOIN
select * from A,B         //查询的结果是A*B，即A与B之间的乘积，得到的数据量极其大，要小心使用

//2、内连接
select * from A inner join B on A.a=B.b      //得到的是A与B的交集，即两边都符合条件的记录

//3、外连接
3-1. 左外连接
select * from A left join B on A.a=B.b       //得到的是A的全部结果以及与B的交集

3-2. 右外连接
select * from A right join B on A.a=B.b      //得到的是B的全部结果以及与A的交集

3-3. 全外连接
select * from A full join B on A.a=B.b       //得到的是A与B的并集


//4、联合查询：主要用于相同表结构的多表联合进行统计查询
作用：对多个select的结果集进行联合，即select语句跟select语句之间进行union/union all
（1）union 合并数据库内表格，会自动去重。
（2）union all合并数据库内表格，不去重。

//多表连接：用于不同的表进行连接获取不同的字段信息
内连接: 就是 左右表中 的数据 都满足on条件才会显示出来
外连接（Left Join):就是 左表 中 的行一定都会显示出来，无论右表中是否有数据满足on的条件
外连接（Right Join）：就是 右表 中 的行一定都会显示出来，无论左表中是否有数据满足on的条件

//联合查询：主要用于相同表结构的多表联合进行统计查询
作用：对多个select的结果集进行联合，即select语句跟select语句之间进行union/union all
（1）union 合并数据库内表格，会自动去重。
（2）union all合并数据库内表格，不去重。


二、count(*)和count(1)和count(列名)的区别
1、执行效果上 ：  
（1）count(*)包括了所有的列，相当于行数，在统计结果的时候， 不会忽略列值为NULL 
（2）count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候， 不会忽略列值为NULL 
（3）count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数， 即某个字段值为NULL时，不统计。

2、执行效率上：  
（1）列名为主键，count(列名)会比count(1)快  
（2）列名不为主键，count(1)会比count(列名)快  
（3）如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）  
（4）如果有主键，则 select count（主键）的执行效率是最优的  
（5）如果表只有一个字段，则 select count（*）最优。

总结：效率一般由高到低，count(主键)>count(1)>count(*)。
（1）如果目的是为了统计行数，忽略所有列，统计包含了列值为NULL，一般使用count(1)去统计行数比较多；
（2）如果需要基于某一列去统计行数，并且需要去除字段值为NULL的行时，一般使用count(列名)去统计行数；
（3）如果表只有一个字段，则 select count(*)最优

//limit用法
（1）limit 起始位置,pageSize
（2）查询任意一条记录查看，我这里选择最后一条记录，SQL语句为：select * from tables limit 141,1\G由于在客户端中，默认查询结果显示不友好，所以，可以把语句后面
的分号改成\G，他会让一条记录显示一行，看起来不那么乱。\G只支持在客户端中用，在其他连接数据库的软件中，使用\G会报错。


三、Mybatis的#{}和${}的区别
1、#{}将传入的数据当作一个字符串，会对传入的数据加上一个双引号。
比如，
select * from student where student_name = #{studentName}
如果传入的值为xiaoming，那么解析成sql的值为student_name="xiaoming"。

2、${}将传入的数据直接显示生成在sql中。
如 :
select ${fieldNmae} from student where student_age = 18
此时，传入的参数作为要查询的字段，如果传入的值为student_name，则解析成的sql为:
select student_name from student where age = 18

3、#{}方式能够很大程度上防止sql注入。

4、${}无法防止sql注入。

5、${}方式一般用于传入数据库对象，例如列表和表名。

6、由于#{}方式具有更高的安全行，所以能用#{}的地方尽量不要使用${}。

7、Mybatis排序时使用order by动态参数时需要注意，用${}而不是#{}。