一、慢sql

  1. 查询语句写的烂
  2. 索引失效

单值索引:在user表中给name属性建个索引, create index idx_user_name on user(name);
复合索引：在user表中给name、email属性建个索引，create index idx_user_nameEmail on user(name,email)

  1. 关联查询太多join(设计缺陷或不得已的需求)

    4.服务器调优及各个参数设置(缓冲、线程数等)

    二、join查询

    2.1、SQL 执行顺序

    我们手写的 SQL 顺序
    image.png


    MySQL 实际执行 SQL 顺序
    1.mysql 执行的顺序：随着 Mysql 版本的更新换代， 其优化器也在不断的升级， 优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。
    2.下面是经常出现的mysql机读查询顺序：
    image.png

  • 总结：mysql 从 FROM 开始执行~

image.png

2.2、JOIN 连接查询 [非常重要]


第一章 索引优化分析 - 图4
建表 SQL

  1. CREATE TABLE tbl_dept(
  2.     id INT(11) NOT NULL AUTO_INCREMENT,
  3.     deptName VARCHAR(30) DEFAULT NULL,
  4.     locAdd VARCHAR(40) DEFAULT NULL,
  5.     PRIMARY KEY(id)
  6. )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  8. CREATE TABLE tbl_emp (
  9.     id INT(11) NOT NULL AUTO_INCREMENT,
  10.     NAME VARCHAR(20) DEFAULT NULL,
  11.     deptId INT(11) DEFAULT NULL,
  12.     PRIMARY KEY (id),
  13.     KEY fk_dept_Id (deptId)
  14.     #CONSTRAINT 'fk_dept_Id' foreign key ('deptId') references 'tbl_dept'('Id')
  15. )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  17. INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
  18. INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
  19. INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
  20. INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
  21. INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
  23. INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
  24. INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
  25. INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
  26. INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
  27. INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
  28. INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
  29. INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
  30. INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);

  • tbl_dept 表结构

    1. mysql> select * from tbl_dept;
    2. +----+----------+--------+
    3. | id | deptName | locAdd |
    4. +----+----------+--------+
    5. |  1 | RD       | 11     |
    6. |  2 | HR       | 12     |
    7. |  3 | MK       | 13     |
    8. |  4 | MIS      | 14     |
    9. |  5 | FD       | 15     |
    10. +----+----------+--------+
    11. 5 rows in set (0.00 sec)

  • tbl_emp 表结构

    1. mysql> select * from tbl_emp;
    2. +----+------+--------+
    3. | id | NAME | deptId |
    4. +----+------+--------+
    5. |  1 | z3   |      1 |
    6. |  2 | z4   |      1 |
    7. |  3 | z5   |      1 |
    8. |  4 | w5   |      2 |
    9. |  5 | w6   |      2 |
    10. |  6 | s7   |      3 |
    11. |  7 | s8   |      4 |
    12. |  8 | s9   |     51 |
    13. +----+------+--------+
    14. 8 rows in set (0.00 sec)

    三、8 种 JOIN 示例

    3.1 笛卡尔积

  1. tbl_emp 表和 tbl_dept 表的笛卡尔乘积：select * from tbl_emp, tbl_dept;

  2. 其结果集的个数为：5 * 8 = 40

    1. mysql> select * from tbl_emp, tbl_dept;
    2. +----+------+--------+----+----------+--------+
    3. | id | NAME | deptId | id | deptName | locAdd |
    4. +----+------+--------+----+----------+--------+
    5. |  1 | z3   |      1 |  1 | RD       | 11     |
    6. |  1 | z3   |      1 |  2 | HR       | 12     |
    7. |  1 | z3   |      1 |  3 | MK       | 13     |
    8. |  1 | z3   |      1 |  4 | MIS      | 14     |
    9. |  1 | z3   |      1 |  5 | FD       | 15     |
    10. |  2 | z4   |      1 |  1 | RD       | 11     |
    11. |  2 | z4   |      1 |  2 | HR       | 12     |
    12. |  2 | z4   |      1 |  3 | MK       | 13     |
    13. |  2 | z4   |      1 |  4 | MIS      | 14     |
    14. |  2 | z4   |      1 |  5 | FD       | 15     |
    15. |  3 | z5   |      1 |  1 | RD       | 11     |
    16. |  3 | z5   |      1 |  2 | HR       | 12     |
    17. |  3 | z5   |      1 |  3 | MK       | 13     |
    18. |  3 | z5   |      1 |  4 | MIS      | 14     |
    19. |  3 | z5   |      1 |  5 | FD       | 15     |
    20. |  4 | w5   |      2 |  1 | RD       | 11     |
    21. |  4 | w5   |      2 |  2 | HR       | 12     |
    22. |  4 | w5   |      2 |  3 | MK       | 13     |
    23. |  4 | w5   |      2 |  4 | MIS      | 14     |
    24. |  4 | w5   |      2 |  5 | FD       | 15     |
    25. |  5 | w6   |      2 |  1 | RD       | 11     |
    26. |  5 | w6   |      2 |  2 | HR       | 12     |
    27. |  5 | w6   |      2 |  3 | MK       | 13     |
    28. |  5 | w6   |      2 |  4 | MIS      | 14     |
    29. |  5 | w6   |      2 |  5 | FD       | 15     |
    30. |  6 | s7   |      3 |  1 | RD       | 11     |
    31. |  6 | s7   |      3 |  2 | HR       | 12     |
    32. |  6 | s7   |      3 |  3 | MK       | 13     |
    33. |  6 | s7   |      3 |  4 | MIS      | 14     |
    34. |  6 | s7   |      3 |  5 | FD       | 15     |
    35. |  7 | s8   |      4 |  1 | RD       | 11     |
    36. |  7 | s8   |      4 |  2 | HR       | 12     |
    37. |  7 | s8   |      4 |  3 | MK       | 13     |
    38. |  7 | s8   |      4 |  4 | MIS      | 14     |
    39. |  7 | s8   |      4 |  5 | FD       | 15     |
    40. |  8 | s9   |     51 |  1 | RD       | 11     |
    41. |  8 | s9   |     51 |  2 | HR       | 12     |
    42. |  8 | s9   |     51 |  3 | MK       | 13     |
    43. |  8 | s9   |     51 |  4 | MIS      | 14     |
    44. |  8 | s9   |     51 |  5 | FD       | 15     |
    45. +----+------+--------+----+----------+--------+
    46. 40 rows in set (0.00 sec)

    3.2 inner join

  3. tbl_emp 表和 tbl_dept 的交集部分（公共部分）

  4. select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;

    1. mysql> select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;
    2. +----+------+--------+----+----------+--------+
    3. | id | NAME | deptId | id | deptName | locAdd |
    4. +----+------+--------+----+----------+--------+
    5. |  1 | z3   |      1 |  1 | RD       | 11     |
    6. |  2 | z4   |      1 |  1 | RD       | 11     |
    7. |  3 | z5   |      1 |  1 | RD       | 11     |
    8. |  4 | w5   |      2 |  2 | HR       | 12     |
    9. |  5 | w6   |      2 |  2 | HR       | 12     |
    10. |  6 | s7   |      3 |  3 | MK       | 13     |
    11. |  7 | s8   |      4 |  4 | MIS      | 14     |
    12. +----+------+--------+----+----------+--------+
    13. 7 rows in set (0.00 sec)

    3.3 left join

  5. tbl_emp 与 tbl_dept 的公共部分 + tbl_emp 表的独有部分

  6. left join：取左表独有部分 + 两表公共部分

  7. select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;

    1. mysql> select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;
    2. +----+------+--------+------+----------+--------+
    3. | id | NAME | deptId | id   | deptName | locAdd |
    4. +----+------+--------+------+----------+--------+
    5. |  1 | z3   |      1 |    1 | RD       | 11     |
    6. |  2 | z4   |      1 |    1 | RD       | 11     |
    7. |  3 | z5   |      1 |    1 | RD       | 11     |
    8. |  4 | w5   |      2 |    2 | HR       | 12     |
    9. |  5 | w6   |      2 |    2 | HR       | 12     |
    10. |  6 | s7   |      3 |    3 | MK       | 13     |
    11. |  7 | s8   |      4 |    4 | MIS      | 14     |
    12. |  8 | s9   |     51 | NULL | NULL     | NULL   |
    13. +----+------+--------+------+----------+--------+
    14. 8 rows in set (0.00 sec)

    3.4 right join

  8. tbl_emp 与 tbl_dept 的公共部分 + tbl_dept表的独有部分

  9. right join：取右表独有部分 + 两表公共部分
  10. select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
    1. mysql> select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
    2. +------+------+--------+----+----------+--------+
    3. | id   | NAME | deptId | id | deptName | locAdd |
    4. +------+------+--------+----+----------+--------+
    5. |    1 | z3   |      1 |  1 | RD       | 11     |
    6. |    2 | z4   |      1 |  1 | RD       | 11     |
    7. |    3 | z5   |      1 |  1 | RD       | 11     |
    8. |    4 | w5   |      2 |  2 | HR       | 12     |
    9. |    5 | w6   |      2 |  2 | HR       | 12     |
    10. |    6 | s7   |      3 |  3 | MK       | 13     |
    11. |    7 | s8   |      4 |  4 | MIS      | 14     |
    12. | NULL | NULL |   NULL |  5 | FD       | 15     |
    13. +------+------+--------+----+----------+--------+
    14. 8 rows in set (0.00 sec)

    3.5 left join without common part

    1.tbl_emp 表的独有部分：将 left join 结果集中的两表公共部分去掉即可：where d.id is nul
    2.select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null;
    1. mysql> select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null;
    2. +----+------+--------+------+----------+--------+
    3. | id | NAME | deptId | id   | deptName | locAdd |
    4. +----+------+--------+------+----------+--------+
    5. |  8 | s9   |     51 | NULL | NULL     | NULL   |
    6. +----+------+--------+------+----------+--------+
    7. 1 row in set (0.00 sec)

    3.6 right join without common part

    1.tbl_dept表的独有部分：将 right join 结果集中的两表公共部分去掉即可：where e.id is null
    2.select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;
    1. mysql> select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;
    2. +------+------+--------+----+----------+--------+
    3. | id   | NAME | deptId | id | deptName | locAdd |
    4. +------+------+--------+----+----------+--------+
    5. | NULL | NULL |   NULL |  5 | FD       | 15     |
    6. +------+------+--------+----+----------+--------+
    7. 1 row in set (0.01 sec)

    3.7 full join

    1.mysql 不支持 full join ,但是我们可以通过骚操作实现 full join,union 关键字用于连接结果集，并且自动去重
    2.tbl_emp 与 tbl_dept 的公共部分 + tbl_emp 表的独有部分 + tbl_dept表的独有部分：将 left join 的结果集和 right join 的结果集使用 union 合并即可
    3.select * from tbl_emp e left join tbl_dept d on e.deptId = d.id union select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
    1. mysql> select * from tbl_emp e left join tbl_dept d on e.deptId = d.id union select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
    2. +------+------+--------+------+----------+--------+
    3. | id   | NAME | deptId | id   | deptName | locAdd |
    4. +------+------+--------+------+----------+--------+
    5. |    1 | z3   |      1 |    1 | RD       | 11     |
    6. |    2 | z4   |      1 |    1 | RD       | 11     |
    7. |    3 | z5   |      1 |    1 | RD       | 11     |
    8. |    4 | w5   |      2 |    2 | HR       | 12     |
    9. |    5 | w6   |      2 |    2 | HR       | 12     |
    10. |    6 | s7   |      3 |    3 | MK       | 13     |
    11. |    7 | s8   |      4 |    4 | MIS      | 14     |
    12. |    8 | s9   |     51 | NULL | NULL     | NULL   |
    13. | NULL | NULL |   NULL |    5 | FD       | 15     |
    14. +------+------+--------+------+----------+--------+
    15. 9 rows in set (0.00 sec)

    3.8 full join without common part

    1.tbl_emp 表的独有部分 + tbl_dept表的独有部分
    2.select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null union select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;
    1. mysql> select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null union select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;
    2. +------+------+--------+------+----------+--------+
    3. | id   | NAME | deptId | id   | deptName | locAdd |
    4. +------+------+--------+------+----------+--------+
    5. |    8 | s9   |     51 | NULL | NULL     | NULL   |
    6. | NULL | NULL |   NULL |    5 | FD       | 15     |
    7. +------+------+--------+------+----------+--------+
    8. 2 rows in set (0.00 sec)

    四、索引简介

    4.1、索引是什么

  • 1、Mysql官方对索引的定义为：索引(Index) 是帮助Mysql高效获取数据的数据结构，可以得到索引的本质：索引是数据结构
  • 2、可以简单理解为“索引是**排好序的快速查找数据结构”，即：索引 = 排序 + 查找**
  • 3、一般来说索引本身占用内存空间也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在磁盘上
  • 4、我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。

  • 5、聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hash index)等

    4.2 索引原理

    4.1 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
    4.2 就是一种可能的索引方式示例：

  • 左边是数据表，一共有两列七条记录，最左边的十六进制数字是数据记录的物理地址

  • 为了加快col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

1.png

4.3、索引优劣势

优势：

  1. 类似大学图书馆的书目索引，提高数据检索效率，降低数据库的IO成本
  2. 通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗

劣势：


  • 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如果对表INSERT，UPDATE和DELETE。因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
  • 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句

    4.4、MySQL 索引分类

    参考连接: https://www.cnblogs.com/luyucheng/p/6289714.html
    1、普通索引：是最基本的索引，它没有任何限制，即一个索引只包含单个列，一个表可以有多个单列索引；建议一张表索引不要超过5个，优先考虑复合索引
    2、唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
    3、主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引
    4、复合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
    5、全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配

    4.5、索引基本语法

    1.创建索引

    如果是CHAR,VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定length。

CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));``<br />
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON(columnname(length));

2.删除索引

DROP INDEX [indexName] ON mytable;

3.查看索引（\G表示将查询到的横向表格纵向输出，方便阅读，mysql命令行中使用才有效）

SHOW INDEX FROM table_name\G

使用 ALTER 命令，有四种方式来添加数据表的索引：

  1. -- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
  2. ALTER TABLE table_name ADD PRIMARY KEY(column_list);
  4. -- 该句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
  5. ALTER TABLE tbl_name ADD UNIQUE index_name(column_list);
  7. -- 添加普通索引，索引值可出现多次
  8. ALTER TABLE tbl_name ADD INDEX index_name(column_list);
  10. -- 该语句指定了索引为FULLTEXT，用于全文索引
  11. ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)

查看表中的索引：**show index from tbl_emp;**

  1. mysql> show index from tbl_emp;
  2. +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  3. | Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  4. +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  5. | tbl_emp |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
  6. | tbl_emp |          1 | fk_dept_Id |            1 | deptId      | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
  7. +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  8. 2 rows in set (0.00 sec)

4.6、MySQL 索引结构

【思考： 为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？】

  • B+树的磁盘读写代价更低：B+树的内部结点并没有指向关键字具体信息的指针。 因此其内部结点相对 B 树更小。 如果把所有同一内部结点的关键字存放在同一盘块中， 那么盘块所能容纳的关键字数量也越多。 一次性读入内存中的需要查找的关键字也就越多。 相对来说 IO 读写次数也就降低了。
  • B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点， 而只是叶子结点中关键字的索引。 所以任何关键字的查找必须走一条从根结点到叶子结点的路。 所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

    4.7、何时需要建索引

    1、哪些情况下适合建立索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询的条件的字段应该创建索引
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 频繁更新的字段不适合创建索引
  5. Where 条件里用不到的字段不创建索引
  6. 单间/组合索引的选择问题，Who？（在高并发下倾向创建组合索引）
  7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度

  8. 查询中统计或者分组字段

    2、哪些情况不要创建索引

  9. 表记录太少

  10. 经常增删改的表
  11. 数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。案例分析：

    3、案例分析

  • 假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
  • 索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。
  • 一个索引的选择性越接近于1，这个索引的效率就越高。