MySQL优化、数据库优化

SQL性能下降原因

响应：执行时间长、等待时间长

1. 查询语句写的烂
2. 索引失效
3. 关联查询太多Join(设计缺陷或者不得已的需求)
4. 服务器调优及各个参数设置(缓存、线程等)

索引

1、索引是什么

1. 索引介绍
   MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。
   简单的理解为：排好序的快速查找数据结构
2. 索引详解(重要)
   在数据之外，数据库系统还维护着满足特点满足查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图是一种可能索引的方式实例：
   
                                              左边是数据表，共有两列七条记录，最左边的是数据记录的物理地址
   为了加快Col2的查询，可以维护一个右边所示的二叉查找树，每个节点分别包括索引键值和一个所指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取相应的数据，从而实现快速检索出符合条件的记录。
   一般来说，索引本身也很大，不可能全部存储在内存当中，因此索引往往会以索引文件的形式存储在磁盘上。
   我们平常所说的索引，如果没有特别指明的话，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚合索引、前缀索引、唯一索引默认都是使用B+树索引，统称索引当然，除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

2、索引的优势与劣势

• 优势
  • 类似大学图书馆一样建书目索引,提高数据检索的效率，降低数据库的IO成本
  • 通过索引对数据进行排序，降低数据库排序的成本，降低了CPU的消耗
• 劣势
  • 索引也是一张表，该表保存了主键与索引字段，并且指向实体表的记录，所以索引也是会占用空间的。
  • 虽然索引大大提高了查询速度，但是同时也会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表的时候，MySQL不仅需要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后索引信息

索引只是提高效率的一种方法，如果MySQL有大量的数据，需要花费大量时间研究建立优秀的索引，或者优化查询。

3、索引分类与创建

• 索引分类
  • 单值索引：即一个索引只包含了单个列，一张表中可以有多个单列索引
  • 唯一索引：索引列的值必须唯一，但是允许有空值
  • 复合索引：一个索引包含了多个列
• 索引基本语法
  • 创建：
    • 1、CREATE [UNIQUE] INDEX indexName ON myTable(列名(length));
    • 2、ALTER myTable ADD [UNIQUE] INDEX [indexName] ON (列名(length));
  • 删除：DROP INDEX [indexName] ON myTable;
  • 查看：SHOW INDEX FROM table_name\G

4、索引结构与检索原理

• 索引结构
  • B-Tree(B树索引)、Hash(哈希索引)、full-text(全文索引)、R-Tree(B+树索引)
• 检索原理
  • 假如需要查找数据29，首先会把磁盘1从磁盘中加载到内存，此时会执行一次IO，在内存中确定29在17~35之间，锁定磁盘块1的P2指针，然后通过磁盘块1的P2指针把磁盘地址加载到内存，发送第二次IO，确定29在26和30之间，再次锁定磁盘块3的P2指针，加载到内存当中，发送第三次IO，同时内存中使用二分查找法查询出29，结束查询，一共执行3次IO（注意：范围值会根据需要查询的值确定最大值和最小值，然后进行匹配）。
  • 一般来说，3层的B+树已经可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高是很大的，但是如果没有索引，每个数据线都需要执行一次IO，那么会由百万次的IO，显然成本是很高的。

5、索引建立的场景

• 什么情况下适合建立索引
  • 主键会自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询与其他表关联的字段，外键关系建立索引
  • 查询中排序的字段通过索引去访问会提高排序速度
• 什么情况下不适合建立索引
  • 频繁更新的字段不适合创建索引
  • Where条件中用不到的字段不创建索引
  • 表数据内容太少
  • 某个数据列包含重复内容较多(性别)，为它建立索引不会提高太多的查询速度