mysql 和索引优化

mysql分成Server层和存储引擎两部分：
    Server层包括：连接器、查询缓存、分析器（主要验证表和字段还有语句正确）、优化器、执行器等，以及所有的内置函数和存储过程、触发器、视图等。
        分析器：主要验证表和字段还有语句正确等
        优化器：选择索引以及表的连接顺序等
    存储引擎：InnoDB、MyISAM、Memory等。

mysql自带日值binlog--逻辑日志（原始执行日志）
InnoDB自带日志redo log--物理日志（操作日志） --> 目的： 实现crash safe 原来: WAL原理(write-ahead logging) 有改动先写日志，再写磁盘。
    实现： 二阶段提交：当改动完数据之后，写入redo log处于prepare阶段，然后写入binlog，再提交事务

mysql事务:ACID 原子性、一致性、隔离性、持久性
    隔离性:Isolation
        1）读未提交
        2）读已提交 解决 脏读 -- 更改语句还未commit，别的事务可以查到修改
        3）可重复读 解决 脏读、不可重复读 -- 更改语句commit后，别的事物可以查到修改
        4）串行化   解决 脏读、不可重复读、幻读 -- 新增语句提交后，别的事物可以查到

索引介绍
    InnoDB索引底层数据结构B+树，每一个索引都一个B+树，主键索引（聚簇索引）、非主键索引（非聚簇索引、二级索引）
    区别：主键索引存储是值的内容
         非主键索引存储主键
    覆盖索引：一种常见的性能优化手段，通过联合索引，不用回表可以得到想要的字段
    最左匹配原则：联合索引从最左的字段开始搜索
    索引下推：联合索引结果不断缩小，例如 name like "张%",age > 20， 索引为(name, age)，则先找到张姓的，再过滤age，使回表的数据减少

写sql顺序
select ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

执行时机
from ... on ... join ... where ... group by ... having ... select ... order by ... limit ...

explain 查询语句 常用参数：

type: join 类型 --> system > const > eq_ref > ref > range > index > all
    
    
    1) const: 根据主键索引或者唯一索引查询到的结果
    例：explain select id,name from employee where id = 1;

    2) ref: 使用非唯一性索引做查询,返回匹配的记录行,常见于多表查询中
    例：explain select * from department d join employee e on d.id = e.dept_id;

    3) range: 索引做范围查询,常见于<、<=、>、>=、between 等操作符
    例：explain select age from employee where age >= 20,age <= 30

    4) index: 索引全查询,MySQL 遍历整个索引来查找匹配的行。
    例：explain select age from employee where age >= 20

    注意:SQL 优化的目标之一要把 type 优化在 ref 到 index 之间,该值没有优化的情况下一般都是 all

possible_keys: 此次查询中可能选用的索引

key: 此次查询中确切使用到的索引

rows: 显示此查询一共扫描了多少行. 这个是一个估计值

extra: 额外的信息
    Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
    Using where：表示优化器需要通过索引回表查询数据
    Using index：表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表
    Using index condition：condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行

1. 不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

    原因1：减少网络传输影响性能，为数据库减少压力

    原因2：索引问题，可以用覆盖索引就用覆盖索引

2. where 语句中索引列参与算术计算,该索引失效

    select id from t where num/2 = 100

    应改为:

    select id from t where num=100*2

    原因：索引是针对原值建的二叉树，将列值计算后，原来的二叉树就用不上了

3. where 语句中索引列参与函数运算,该索引失效

    select id from t where substring(name,1,3)='abc'

    应改为:

    select id from t where name like 'abc%'

    原因同上

4. where 语句中使用 in 、not in 运算符有时会让索引失效, in 可以用 between或者exists  

    如果查询条件为等值或范围查询时，索引可以根据查询条件去找对应的条目。反过来当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描，这类的查询条件有：<>、NOT、in、not in

    In在5.7以前，如果是小范围的查询，还是走索引的，type属于range，在随着数据量的增大时会自动进行全表的扫描（并且与要查询的结果是否包含在索引树中决定走index还是all）；not in则不走索引；
    目前在8.0以后验证，发现无论是in not 或者<>,都会走索引；

    两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in；

5. where 语句中做不等于( != , <> ),会使该索引失效

    原因：索引是等值匹配

6. where 语句中发生类型转发,该索引失效

    原因：在MySQL中，数字和字符串的比较是将字符串转为数字再进行比较的。而如果查询时用的是数字，字段格式是字符串，意味着表中的数据格式需要转换，用字符串格式建立的索引是不匹配的，因此不使用索引

7. where 语句中模糊查询时以%开头,该索引失效，走搜索引擎来解决

    原因：索引是等值匹配

8. where 子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描
    
    原因：如果索引列是可空的，是不会给其建索引的，索引值是少于表的count(*)值的，所以这种情况下，执行计划自然就去扫描全表了。

9. 应尽量避免在 where 子句中使用 or 来连接条件,or之后的条件会全表扫描，所以优化器可能选择索引失效，除非or后的字段都有索引

10. 在复合索引的使用时跟声明时顺序不一致或者中间有列的缺失,该索引失效
如:声明了(a,b,c)的复合索引,但是在用是时候中间有列的缺失 where a = xx,c = xx 中间缺失了 b,所以无法使用该复合索引,只要不是缺失中间列,其他情况索引均有效

11. 超过三个表禁止join。需要join的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。

说明：即使双表join也要注意表索引、SQL性能。

12. 如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

13. 利用覆盖索引来进行查询操作，避免回表。

正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用explain的结果，extra列会出现：using index。

14. 利用延迟关联或者子查询优化超多分页场景。

说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

正例：先快速定位需要获取的id段，然后再关联： SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

15. 建组合索引的时候，区分度最高的在最左边。

说明：存在非等号和等号混合时，在建索引时，请把等号条件的列前置。如：where c>?,d=? 那么即使c的区分度更高，也必须把d放在索引的最前列，即索引idx_d_c。

正例：如果where a=?,b=? ，如果a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。

16.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。15%

17.应尽可能的避免更新聚簇索引数据列，因为聚簇索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 聚簇 索引数据列，那么需要考虑是否应将该索引建为 聚簇 索引。

18.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

19.尽可能的使用 varchar/nvarchar 代替 char/nchar 

    原因：因为首先变长字段存储空间小，可以节省存储空间。
         其次对于查询来说，在一个相对较小的字段内搜索，效率更高。

20. 慎用distinct关键字

    原因：带distinct的语句cpu时间和占用时间都高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较，过滤的过程会占用系统资源，cpu时间。

21、尽量用 union all 替换 union

    原因：如果使用union，不管检索结果有没有重复，都会尝试进行合并，然后在输出最终结果前进行排序。如果已知检索结果没有重复记录，使用union all 代替union，这样会提高效率。