MySql优化之路

执行计划是数据库根据sql语句和相关表的统计信息得出的一个查询方案。

这个计划是数据库查询优化器自动生成的。要生成一个正确的执行计划，需要注意两点：

1. SQL语句是否正确的告诉了查询优化器它想干嘛。
2. 查询优化器得到的数据库信息是否是最新的。

查询优化器创建内部数据结构(解析树)，并对其优化，例如决定表的读取顺序。选择合适的索引(当数据表数据不大自动选择全表扫描，当数据表数据很大选择索引)。我们可以使用特殊关键字提示(hint)来影响他的决策。

对于SELECT 语句，在解析之前，先去查询缓存query cache.如果有，直接返回。

1. 解决SQL语句是否正确的告诉了查询优化器它想干嘛。

1. .统一SQL的书写规范。如统一使用大写，统一使用一个空格间隔字符。如果不统一，哪怕是同一个sql语句，查询优化器也会认为是两个sql，生成两个执行计划。
2. 千万不要把SELECT语句写的很复杂。人看起来都很难懂，查询优化器也很难懂。SELECT 语句越简单越好，因为执行计划是可以重用的。复杂的SQL只要有一个改动，查询优化器就会重新解析SQL。
3. 多使用临时表暂存中间结果。因为使用临时表以后，临时表查询结果就在tempdb(tempdb是什么)中，避免了多次扫描主表，减少了程序执行中共享锁阻塞更新锁（共享锁阻塞更新锁），提高了并发。
4. 使用like进行模糊查询，不要在关键字前面加%。因为会导致全表扫描，使相关列的索引无法使用。解决方案：(1)修改前端，将文本框改为下拉列表，用户模糊输入信息后，前端自动定位。比如输入‘a’,自动关联出‘ba/a/ab’等等。(2)修改后端，根据用户输入值，切割分类，比如‘智能小风扇’，先切割成智能、小、风扇，用redis中存储的数字字典，匹配风扇属于哪种商品类型，用确定的属性来查询。
5. 及早确定索引列。在日常开发中，避免对索引列的一些操作，如计算、not、<>、！=、IS NULL、函数（or/in/ not in/）、数据类型转换等。

• (1)比如要对某列进行null判断，可以在该列上设置默认值，例如0，然后在where中使用=0来判断。
• (2)对于or操作，最好使用union all来替换。
• (3)对于在列上进行计算，可以将计算放在右边，例如where num/2=100 替换为where num=100*2
• (4)如果必须使用not，则应该使用括号将表达式括起来。如where not(num=2)
• (5)用EXISTS 代替In.

1. 一般在where条件跟order by涉及的列上建立索引。聚合索引，符合索引。索引不是越多越好，会降低insert、update操作的时间。一个表最多6个索引。
2. 多用Inner join.Inner join是返回两个表等值的行。

表结构设计：

 明确确定每列定义，能用数字类型的地方用数字类型，因为引擎在处理查询时候会比较字符类型每个字符，而数字类型只要比较一次就行。

明确每列的大小，严格按照定义来设计表结构，

索引

索引是排好序的快速查找数据结构

插拔式的存储引擎，不同的表使用不同的存储引擎。

订单表 插入多查询少，必须支持事务用innodb.

商品表 插入少，查询多，用myisam

myisam 查找效率，不支持事务，行锁。

innodb 支持事务 行锁，查找效率慢。(聚合索引)

还有ndb memory。

索引必须要基于存储引擎。数据存储在磁盘上，数据怎么存储影响到索引速度。

MySQL的索引分 Btree索引，hash 索引。其中hash索引局限性多。

Btree索引是 balance tree平衡树。原理模型：

id name

1    aa                                                     

2    bb

3    cc

4    dd

5    ee

6    ff

7    gg

先找到中间的值 4， 放在根节点。在找上半部分的中间值2与下半部分的中间值6，

                                4

                    2                    6

            1            3        5            7

最多查找3次，因为最多有3层。索引上存储指向数据行的指针，指针在内存中是一块地址，例如0x002，指向第2行，从而找到name值bb，这种方式是myisam存储引擎，表的数据文件有.myi（表示索引文件） .myd（表示数据文件） .frm（表结构文件）。先查询.myi,找到指向myd的一行地址，需要查询2次。查询索引文件非常快，因为数据量少，只存放的是索引值。找到行号去数据文件中找到该数据。

innodb的数据文件有 .frm（表结构文件） .idb（索引和数据文件），索引跟数据文件放在一起。

select * from user where id=1， myisam需要查询2次，innodb查询1次。

假设现在树的层数为N，最底层的叶子节点个数为？

第一层 2^0, 2^1,2^2，第N层是2^(N-1)个叶子节点。大概21亿多。建立索引后只需要需要查询32次。

如果是int类型的话是升序，如果是string类型的，按照底层的排序规则。

聚集索引与非聚集索引

区别在物理存储上数据文件与索引文件是否分开，myisam是非聚集索引，因为其.myi与myd是分开存放的。innodb是聚集索引。

innodb保留Btree索引的结构，但是在索引是存储的是该数据行的每列属性，

一个表中只会存在一个聚集索引。如果一个表中设置多个索引，innodb不会维护多个Btree结构。

    1.默认会拿主键做为聚集索引，

    2.如果没有主键，会拿非空的唯一索引做为聚集索引。

    3. 如果上面都没有，innodb会自己维护一个唯一的ID，做为聚集索引。

    如果select * from user where name='aa'，那聚集索引会先查找name的索引树，然后映射到innodb的聚集索引上，二者是有映射关系的。innodb也会有维护很多索引树，但是主索引只会有一个，主索引下存放着数据，副索引下不会存放数据，防止数据重复。副索引回映射到主索引上。以后Mysql会淡化myisam，推崇innodb。

索引的优点：提高检索速度。降低磁盘的读取IO

                    索引是排好序的，降低了数据运算成本，从而降低了CPU的消耗

缺点：索引也是文件，需要存储，占用额外的空间。

          索引建立在更新频繁的字段上面，会存在很大的性能损耗，如果更新索引所在的数据，不只要更新数据，还要更新索引树，有时候需要重新生成索引树，当数据量大的时候索引树的更新需要几个小时。但是在更新索引树的时候，需要停掉业务。如果不停，会阻塞。索引碎片就是比如2删掉了，但是他的索引还存在，就产生了索引碎片。

哪些情况不适合创建索引：

    1.频繁更新的字段不适合创建索引

    2.where条件中用不到的字段不适合创建索引，因为索引一般是用来查找，列的选取。

    3. 表的数据可以确定的而且数据量不多可以不创建索引

    4. 数据存放较多而且分布比较均匀的不适合创建索引，比如性别，boolean类型的字段。比如1上有很多，会产生地址冲突，

    5. 参与列计算的不适合创建索引。

适合创建索引：

    1.频繁做为where条件的字段

    2.排序字段

    3.关联字段，比如外键

    4.分组字段，分组前提是排序，

    5.统计字段，如sum count max