1，SQL的优化：

下面就说说其中的一些方法：
那么对应SQL的优化主要有3点：

  1. 最大化利用索引；
  2. 那么就要对SQL语句的编写规范进行优化；
  3. 尽可能避免全表查询；
  4. 减少无效数据的查询；
  5. 如果大量数据的情况下，对数据进行划分；
    • 需要建立索引的场景：
  6. 高频查询，但是不会频繁更新（修改）的字段；比如：用户id，订单id等等；这些都可以对其建立唯一索引；
  7. 以及在消息队列的去重表中，也可以对消息id建立唯一索引；
  8. 选择度高的字段，（sex性别这种字段就不要建了）；
  9. 小字段可以建，但是数据长度特别大的字段就不要建了，比如说：文章内容关于长字符串的字段；
    • 数据库内部策略的优化：
    • 这个一般是在运维那边才有权限进行配置的，我了解大致就是对内存的配置什么的；
    • SQL语句的规范优化
    • 尽量不要在字段开头使用模糊查询：Like %XXX%；
    • 避免使用 NOT IN 和 IN，否则导致走全表查询；
    • 尽量避免使用 OR，也是同样会导致放弃索引走全表查询，除非or两边的都用到了索引；
  • 可以使用 UNION 代替 OR；
    • 尽量避免对null值进行查询，会导致放弃索引走全表查询；
  • 可对null值字段设置 数值 0；对 0 值进行判断；
    • 尽量避免在where条件中等号的左侧进行表达式，函数操作；会导致放弃索引走全表查询；

  • 应该将表达式和函数操作放在等式右边；

    • 最左匹配原则：在MySQL建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到, 如创建组合索引a,b,c那么查询条件中只使用b和c是使用不到索引的。

    • 这里就只有a用到了索引； select a,b,c from table_a where a>b and b=c;

    • 查询条件不能用 < > 或者 ！=；
    • order by 条件要和 where 条件一致，否则也会走全表查询，会走文件排序；
    • 避免出现 select * ；不要搜索全表，要尽量指明列；
    • 在多表关联查询时，小表在前，大表在后；
    • 使用表的别名；
    • 用where语句代替having语句；
    • 在大批量插入数据使用多个值的insert语句比多条insert语句插入更快；如果在海量数据的插入情况下还能使用load语句插入更快；
    • 那么如何定位哪一条SQL是需要进行优化的呢？
    • 在，mysql中有一个慢查询日志，在开启慢查询日志后，可以设置当sql执行时间的限制，如果某条sql语句的执行时间超过这个限制，那这条sql语句就会被记录在该日志里面；假如说：我设置了2s，那所有执行时间超过2s的sql语句都会被记录；这样就能定位到具体的sql；
    • 慢查询日志的开启：

    • 在mysql中有个my.ini日志文件，在里面配置 slow_query_log=1；开启慢查询日志，# 设置慢查询的阈值(默认: 10s)

      long_query_time=0.2; 这种方式开启慢查询是永久生效的； ```sql — 查看慢查询日志变量 show variables like ‘%slow_query_log%’; show variables like ‘%slow_query_log_file%’; show variables like ‘%long_query_time%’;

— 开启方式一: 只对当前数据库生效，MySQL重启后，会失效 0=OFF 1=ON set global slow_query_log=1;

— 开启方式二: 想永久生效，提供配置文件my.ini(推荐) slow_query_log=1 slow_query_log_file=日志文件存储路径

设置慢查询的阈值(默认: 10s)

long_query_time=0.2;

  2. - 如果说是直接去查看日志的情况下，效率会比较低下，那么mysql中呢有提供一个对慢查询日志信息的分析工具：但是官方提供的不太好用；
  3. - **如何判断定位到的SQL语句的执行顺序，以及是否命中索引：**
  4. - 那么在MySQL中有提供一个关键字Explain，这个关键字能让我们知道这条sql语句的执行顺序，是否命中索引，查询语句的性能瓶颈；
  5. ```sql
  6. -- 使用Explain关键字 放到sql语句前
  7. explain select * from system_user where id = 1;

image.png

  • 建表优化（建索引的注意）：
    • 在建表的时候多设置一些需要的冗余字段，避免在查询时，对多张表进行查询；
    • 在建表的时候，一定要建立主键索引；
    • 数据量大的情况下一定要建立索引；
    • 表中不要建立无用的索引；
    • 在建表时，一些能用数值类型的字段尽量使用数值类型而不要使用字符；因为这样会增加存储的开销（字符的大小要比数值大）；
    • 在建表时，能建立联合索引的字段尽量去建立联合索引，避免回表查询，也能节省内存空间；
    • 在建表时，如果遇到字符类型的字段，而且这些字段的字符量很大，又要频繁去查询这些字段的情况下呢，可以将这些字段建立一个前缀索引，然后通过计算这些字符串的长度来确定前缀索引的长度；
  • 特殊方案：
    • 如果说，数据量实在是太大了，而且业务复杂，还可以将数据导入到ElasticSearch或者是其他数据库中，比如：MongoDB；
    • 搜索的场景能用es，低价值海量数据用mgbd；

2，SQL的语句题：

  1. -- 建立联合索引(a,b,c)，请说出下列条件的索引使用情况
  2. select * from table where a=4 
  3.      使用到索引a
  5. select * from table where a=4 and b=6 
  6.     使用到了索引a,b
  8. select * from table where a=4 and c=5 and b=6 
  9.     使用到了索引a,b,c
  11. select * from table where b=4 or b=5 
  12.     没使用到索引
  14. select * from table where a=4 and c=6 
  15.     使用到索引a
  17. select * from table where a=4 and b>5 and c=6 
  18.     使用到索引a,b
  20. select * from table where a=4 and b like 'test%' and c=6
  21.     使用索引a,b b条件相当于范围查询
  23. select * from table where a=4 order by b,c
  24.     使用到索引a 不会产生Using FileSort
  26. select * from table where b=5 order by a
  27.     没使用索引 产生Using Filesort
  29. select * from table where b=5 order by c
  30.     没使用索引 产生Using Filesort
  32. select * from table where a=5 group by b,c
  33.     使用索引a 不会产生Using Filesort
  • 问题2: 什么是索引? 
    1. 数据库索引的本质是: 数据结构 是一种b+tree的数据结构，它有二叉树的特征，同时解决平衡和深度的问题，这种数据结构能够帮助我们快速的获取数据库中的数据。
  • 问题3: 索引的作用? 
    1. 当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段。索引能够轻易将查询性能提高好几倍。有了索引相当于我们给数据库的数据加了目录一样，可以快速的找到数据，简单来说是提高数据查询的效率。
  • 问题4: 索引的分类? 
    1. 1.普通索引
    2. 2.主键索引
    3. 3.唯一索引
    4. 4.联合索引（组合索引）
    5. 5.全文索引
  • 问题5: 索引的原理? ``` 索引的实现本质上是为了让数据库能够快速查找数据，而单独维护的数据结构，mysql实现索引主要使用的两种数据结构: hash 和 B+Tree，我们比较常用的MyISAM和InnoDB存储引擎都是基于B+Tree的。

hash:（hash索引在MySQL比较少用）他以把数据的索引以hash形式组织起来，因此当查找某一条记录的时候，速度非常快。但是因为是hash结构，每个键只对应一个值，而且是散列的方式分布，所以他并不支持范围查找和排序等功能。

B+树:B+Tree是(MySQL使用最频繁的一个索引数据结构)数据结构，B+Tree每个节点可以存放多个数据，相比二叉树，树的高度更低，磁盘IO更少，查询效率更高。因为是树型结构，所以更适合用来处理排序，范围查找等功能。

  3. -  问题6: 索引的优点?
  1. 可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性。
  2. 建立索引,可以大大提高检索的数据,以及减少表的检索行数。
  3. 建立索引,在表连接条件时,可以加速表与表直接的相连。
  4. 建立索引,在分组和排序时,可以减少查询时分组和排序所消耗的时间。 ```
  • 问题7: 索引的缺点? ```
  1. 在创建索引和维护索引时,会耗费时间,随着数据量的增加而增加。
  2. 索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间。
  3. 当对表进行INSERT,UPDATE,DELETE的时候,索引也要维护,这样就会降低数据的维护速度(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。 ```
  • 问题8: 如何分析索引使用情况? 
    1. explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。简单讲，它的作用就是分析查询性能。explain关键字的使用方法很简单，就是把它放在select查询语句的前面。MySQL查看是否使用索引，简单的看type类型就可以。如果它是all，那说明这条查询语句遍历了所有的行，并没有使用到索引。
  • 问题9: 哪些字段适合加索引? ```
  1. 在经常需要搜索的列上添加索引,可以加快搜索的速度。
  2. 主键列上可以确保列的唯一性。
  3. 在表与表的而连接条件上加上索引,可以加快连接查询的速度。
  4. 在经常需要排序(order by),分组(group by)和的distinct列上加索引可以加快排序查询的时间。 ```
  • 问题10: 哪些字段不适合加索引 ```
  1. 查询中很少使用到的列不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求。
  2. 很少数据的列也不应该建立索引,比如一个性别字段0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,MySQL需要扫描的行数很多,增加索引,并不能提高效率。
  3. 定义为text和image和bit数据类型的列不应该增加索引。
  4. 当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系。 ```
  • 问题11: 哪些情况会造成索引失效? ```
  1. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。
  2. 索引字段的值不能有null值，有null值会使该列索引失效。
  3. 对于联合索引，不是使用的第一部分，则不会使用索引（最左原则）。
  4. like查询以%开头。
  5. 如果列类型是字符串，那一定要在条件中将数据使用单引号引用起来,否则不使用索引。
  6. 在索引的列上使用表达式或者函数会使索引失效。 ```
  • 问题12: 联合索引最左原则? ``` 在MySQL建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到, 如创建组合索引a,b,c那么查询条件中只使用b和c是使用不到索引的。

这里就只有a用到了索引；

select a,b,c from table_a where a>b and b=c;

  3. -  问题13: 聚簇索引和非聚簇索引?

  1. MyISAM——非聚簇索引 MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的数据表和索引表是分开存储的。非聚簇索引的主键索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。

  2. InnoDB——聚簇索引 聚簇索引的数据和主键索引存储在一起，主键索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。 ```

  • 问题14: in和exists区别? 
    1. 1.当A表数据多于B表中的数据时，使用in优于exists。
    2. 2.当A表数据小于B表中的数据时，使用exists优于in。
    3. 3.如果两张表数据量差不多，那么它们的执行性能差不多。
  • 问题15: 我有三个表 A,B,C 现在有一个select * from A,B,C你能告诉我？A,B,C三个表在查询的的执行顺序是什么? 
    1. 一定通过explain查询id的值才能决定。如果排id相同那么至上而下运行。如果id不同，大的先执行。
  • 问题16: like查询中哪那些会走索引那些不会走索引？ 
    1. -- b 建立了一个索引
    2. select * from table where b like '%xxxx%' -- 不会
    3. select * from table where b like 'xxxx%'  -- 会
    4. select * from table where  b like '%xxxx' -- 不会
  • 问题17: MySQL事务隔离级别?
  • 问题18: MySQL中锁的分类? 
    1. 1.按操作分: 读锁(共享锁)、写锁(排它锁)
    2. 2.按粒度分: 表锁、页锁、行锁  MySQL一页是16kB
    3. 3.思想的层面分: 悲观锁、乐观锁
  • 问题19: MySQL中有几种连接查询? ```

  1. 内连接(inner join): 只有两个元素表相匹配的才能在结果集中显示。

  2. 外连接： 2.1 左外连接(left join): 左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。 2.2 右外连接(right join): 右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。

    union: 联合查询,将多个查询的结果组合在一起, 去重 union all: 联合查询,将多个查询的结果组合在一起, 不去重 ```

  • 问题20: MySQL如何综合性优化? ``` 1.选择表合适存储引擎: MyISAM: 以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的。 InnoDB: 事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。

1.1 合理的设计表(满足3范式)

2.索引优化: — 表一定要建立主键索引。 — 数据量大的表应该有索引。 — 经常与其他表进行连接的表，在连接字段上应该建立索引。 — 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引。 — 索引应该建在选择性高的字段上（sex 性别这种就不适合）。 — 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。 — 频繁进行数据操作的表，不要建立太多的索引。 — 删除无用的索引，避免对执行计划造成负面影响。

3.sql语句优化: — SELECT语句务必指明列的名称（避免直接使用select * ） — SQL语句要避免造成索引失效的写法 — SQL语句中IN包含的值不应过多 — 如果排序字段没有用到索引，就尽量少排序 — 尽量少用or — 尽量用union all代替 union — 避免在where子句中对字段进行null值判断 — 不建议使用%前缀模糊查询 — 避免在where子句中对列进行表达式或函数操作

4.缓存优化: 为了提高查询速度，我们可以通过不同的方式去缓存我们的结果从而提高响应效率。当我们的数据库打开了Query Cache（简称QC）功能后，数据库在执行SELECT语句时，会将其结果放到QC中，当下一次处理同样的SELECT请求时，数据库就会从QC取得结果，而不需要去数据表中查询。如果缓存命中率非常高的话，有测试表明在极端情况下可以提高效率238%。

5.读写分离: 如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构，读写分离，解决的是，数据库的写入，影响了查询的效率。读写分离的基本原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。

6.MySQL的分库分表: 数据量越来越大时，单体数据库无法满足要求，可以考虑分库分表 两种拆分方案： 垂直拆分, 水平拆分 表的垂直拆分:就是把原来一个有很多列的表拆分成多个表。 通常垂直拆分可以按以下原则进行： 1、把不常用的字段表单独存放到一个表中。 2、把大字段独立存放到一个表中。 3、把经常一起使用的字段放到一起。

  1. 表的水平拆分: 是为了解决单表数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的，将数据平均分为N份

分库分表常用中间件: MyCat、Sharding-JDBC

运维 修改数据库的配置

增加硬件 ```