出现问题:
性能下降SQL慢
执行时间长
等待时间长

问题:
数据过多 - 分库分表
关联了太多表,太多 - join SQL优化
没有充分利用到索引 - 索引建立(优化效果最好)
服务器调用及各个参数设置 - 调整my.cnf

join图
image.png

索引简介

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，
这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：
image.png
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上
优势 :

  • 查询快,提高检索的效率,减低数据库的IO成本
  • 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗

劣势 :

  • 大大提高查询速度,降低更新表的速度,如果对表进行insert,update,delete. 更新表时,mysql不仅要保存数据,还要保存索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
  • 实际上索引也就是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的

Btree索引

image.png
【初始化介绍】
一颗b树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），
如磁盘块1包含数据项17和35，包含指针P1、P2、P3，
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
【查找过程】
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

时间跨度
同一问题可用不同算法解决，而一个算法的质量优劣将影响到算法乃至程序的效率。算法分析的目的在于选择合适算法和改进算法。
image.png
image.png

B+tree

image.png

聚簇索引和非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
术语‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。
如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。
image.png

聚簇索引的好处：

  • 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多个数据块中提取数据，所以节省了大量的io操作。

聚簇索引的限制：

  • 对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
  • 由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。
  • 为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。

索引分类

  • 单值索引 : 一个索引只能包含单个列,一个表可以有多个单列索引
  • 唯一索引 : 索引列必须唯一,但允许有空值
  • 主键索引 : 设定主键或数据库会自动建立索引,innodb为聚簇索引
  • 复合索引 : 一个索引包含多个列

查看表的索引 : SHOW INDEX FROM table_name
创建索引 : CREATE [UNIQUE ] INDEX [indexName] ON table_name(column);
创建复合索引 : CREATE INDEX idx_no_name ON customer(customer_no,customer_name);
删除索引 : DROP INDEX [indexName] ON mytable;

跟着表一起创建索引 :
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name), //单值索引
UNIQUE (customer_name), //唯一索引
KEY (customer_no,customer_name) //复合索引
);
有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

哪些情况要创建索引 :

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他表关联的字段,外键关系建立索引
  • 单键/组合索引的选择问题,组合索引性价比更高
  • 查询排序的字段,排序字段若通过索引去访问将大大提高排序速度
  • 查询中统计或者分组字段

哪些情况不建索引

  • 表记录太少
  • 经常增删该的表或者字段
  • Where条件里用不到的字段不创建索引
  • 过滤性不好的不适合键索引

性能分析 (Explain)

是什么 : 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是
如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

能干什么 :

  • 表的读取顺序
  • 那些索引可以使用
  • 数据读取操作的操作类型
  • 那些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被物理查询

怎么玩 : Explain 查询语句
image.png

各个字段解释 :

详细介绍— >
explain查询字段的解释

删除索引

索引表所在的位置
information_schema > statistics

SELECT index_name FROM information_schema.STATISTICS WHERE table_name=’t_emp’ AND table_schema=’mydb’
AND index_name <>’PRIMARY’ AND seq_in_index = 1

批量删除表中所有索引的函数 :
DELIMITER
CREATE PROCEDURE proc_drop_index(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT ‘’;
DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>’PRIMARY’ ;
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>’’ DO
SET @str = CONCAT(“drop index “,_index,” on “,tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index=’’;
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END

执行删除非主键的索引:
CALL proc_drop_index(“dbname”,”tablename”);

单表的查询优化 —>例题解释 单表索引优化

  • 全值索引我最爱
  • 最佳左前缀法则 : 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
  • 不要在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
  • 把范围索引建在前面,后面的索引全部失效
  • mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
  • is not null 也无法使用索引,但是is null是可以使用索引的
  • 字符串不加单引号索引失效

一般性建议

  • 对于单键索引,尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好
  • 在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
  • 在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面
  • 书写sql语句的时候,尽量避免造成索引失效的情况

关联查询优化 : 关联,子查询,分组索引优化

建议 :

  • 保证被驱动表的join字段已经被索引
  • left join 时,选择小表作为驱动表,大表作为被驱动表
  • inner join 时,mysql会自动帮你把小结果集的表选为驱动表
  • 子查询尽量不要放在驱动表,有可能使用不到索引
  • 能够直接多表关联的尽量直接关联,不用子查询

子查询优化 : 尽量不要使用not in 或者 not exists 使用left outer join on xxx is null 替代

排序分组优化 : 关联,子查询,分组索引优化

无过滤,不索引
顺序错,必排序
方向反,必排序

  • ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序
  • mysql会自动选择最优的索引进行优化
  • image.png
  • group by 关键字优化 : group by 使用索引的原则几乎跟order by一致 ，唯一区别是group by 即使没有过滤条件用到索引，也可以直接使用索引。

覆盖索引 : 覆盖索引是 select 的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。关联,子查询,分组索引优化