MySQL索引优化实践！

        为了提高数据查询效率，就像目录一样的存在！在MySQL中，索引是在存储引擎层实现的，所以并没有统一的索引标准，不同的存储引擎的索引工作方式并不一样。

        常见模型

        哈希表：

        哈希表是一种key-value存储数据的结构，只需要输入待查找的值key，就可以找到其对应的值value。运用方式：把值放在数组里，用哈希函数把key换成一个确定的位置，然后把value放在数组的这个位置，如果key值经过哈希函数的换算，会出现同一个值的情况，就会拉出一个链表。

        问题：增加数据的时候，速度快，往后追加，但因为不是有序的，所以哈希索引做区间查询的速度很慢，查[***,***]这个区间的所有用户，就必须全部扫描一遍。

        所以：哈希表这种结构适用于只有等值查询的场景，比如Memcached以及其他一些NoSQL引擎。

        有序数组：

        有序数组在等值查询和范围查询场景中的性能都非常优秀。但是在更新数据的时候就很麻烦，中间插入一个记录就必须挪动后面所有的记录。

        所以：有序数组索引只适用于静态存储引擎，比如要保存的是以前的固定的信息，不会再修改的数据

        二叉树：

        二叉树每个节点的左儿子小于父节点，父节点又小于右儿子。也可以有多叉树，就是每个节点又多个儿子，儿子之间的大小保证从左到右递增。

        二叉树是搜索效率最高的，但实际上大多数的数据库存储却并不适用二叉树，因为索引不止存在内存中，还要写在磁盘上，所以，为了让查询过程访问尽量少的数据块，就需要使用“N”叉树，N叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中。

        总结：数据块底层存储的核心就是基于这些数据模型，通过分析数据模型即可分析出这个数据库的适用场景。

        InnoDB的索引模型

        表都是根据主键顺序以索引的形式存放的，这种存储方式的表现为索引组织表。InnoDB使用了B+树索引模型，所以数据都是存储在B+树之中的。每一个索引在InnoDB里面对应一科B+树。索引类型分为主键索引和非主键索引。

        主键索引（聚簇索引clustered index）：叶子节点存的是整行数据

        非主键索引（二级索引secondary index）：叶子节点内容是主键的值 

        主键索引和普通索引查询的区别

        如果语句select * from table where ID=**,（ID为主键），则只需要搜索ID这棵B+树；

        如果语句select * from table where ke=**,(k为字段加了索引），则需要先搜索k索引树，得到ID的值，再去ID索引树搜索一次。这个过程叫作：回表。所以基于非主键索引的查询需要多扫描一棵索引树，应用中应该尽量使用主键查询。

        索引维护

        B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。

        页分裂：如果插入数据的过程中，数据页满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂。 这种情况下，性能自然会受影响。页分裂操作还影响数据页的利用率，放在一个页的数据，分到两个页中，整体空间利用率降低了。

        页合并：相邻两个页由于删除了数据，利用率很低以后，会将数据页做合并。

        自增主键：指自增列上定义的主键，插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一跳记录的ID值，这种插入数据的模式，符合我们的递增插入的场景，每次插入一条新记录，都是追加操作，不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则不容易保证有序插入，这样写数据成本相对较高。主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间就越小。

        覆盖索引（索引优化），常用的性能优化手段

        通过索引优化，避免回表过程。 select * from T where k between 3 and 5，需要先通过二级索引k去查到主键ID，再回表根据主键值去查询ID的value，一次一次的去查，最后查询到k=6，不满足条件，循环结束。此时如果执行的语句改为 select ID from T where k between 3 and 5，这时只需查ID的值，二级索引k已经覆盖了我们的查询需求，我们称为覆盖索引。覆盖索引可以减少树的搜索次数，提升查询性能。

        【通过explain+SQL可以定位此语句底层如何查询，是否查询了全表还是通过索引等信息】    

        最左前缀原则

        B+树可以利用“最左前缀”，来定位记录，发生在联合索引里面（例：name,age），索引项是按照索引定义里面出现的字段顺序排序的，已经有了这个联合索引后，就不需要单独在name上建立索引，因此，如果通过调整顺序，可以少维护一个索引，这个顺序是需要优先考虑的。

索引下推（5.6以后引入）：

        二级联合索引遍历过程中，如果匹配到最左索引之后，还会对索引中所包含的所有字段先做判断，过滤掉不符合另外索引条件的记录（进一步去满足最左索引字段的数据里去比对联合索引的另外字段），最后会拿着筛选出来的数据对应的id再去聚簇索引中查找数据，回表的数据相对于以前更少了，减少回表次数。问题：为什么一般用 > 不会走索引下推，而like +**%基本都会走索引下推。可能是因为第二种方式结果集明显更少，比对的内容更多。底层根据自身逻辑进行优化执行的，会计算cost成本来计算进行选择方式。

        索引下推优化：

        可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，（例：（name,age）索引，查询select * from tuser where name like '张 %' and age=10 and ismale=1;的时候 在查询name索引的时候，已经将age条件进行了判断，满足的再回表），并不代表是like就会走索引下推，而是满足索引的特性的情况下，会尽量进行优化，匹配，缩小数据范围来达到最优执行效果。

trace工具:

        mysql如何选择索引可以用trace工具来分析sql的使用，但开启trace工具会影响性能，所以用完之后需要关闭。

set session optimizer_trace="enabled=on" ,end_markers_in_json=on; --开启trace
select * from user where name>'a' order by position;
select * from infomation_schema.OPTIMIZER_TRACE;

order by 与 group by

       同样的联合索引，第一条语句order by 用了索引，因为B+Tree的有序性，联合索引的有序性（参考mysql底层原理，看结构），当满足最左原则的时候，第二个索引是有序的，所以这条语句能直接排，走索引树。

       第二条语句order by用的文件排序，在内存中排序的，性能相对较低，第二条语句跳过了 age,用position排序，出现了 Using filesort;因为age不确定，如果age是常量，能确定 才可以走索引树。改造：可用 order by age,position 此时就会走索引树，如果 order by position,age 也不会走索引，索引排序的先后性。或者前面 条件加上 age='**' 后面order by 也可以走

       order by 默认升序，如果 order by age asc,position desc 也不会走索引，与索引的排序方式不同从而产生filesort的情况。（MySQL 8.0以上版本有降序索引可以支持该种查询方式）

# 以下情况也不会走索引，会Extra列提示 Using filesort
select * from client where name in('lily','lina') order by age,position;

         用in命中name索引，如果后面age,position也能确定是可以走索引，但如果用order by是走不了索引进行排序的，联合索引最左索引满足后，当前节点内是有序的，但目前用的in关键词，会有两个结果集，将结果集放在一起用age,position排序的时候，此时 整体并不是有序的。  

# 以下情况也不会走索引，会Extra列提示 Using filesort
select * from client where name > 'a' order by name;

        虽然命中索引，但如同之前 like '**%'原理一样，用> 数据量会很大，所以底层就走全表扫描filesort。此时用的 *  还需要回表操作。

# 以下情况会走索引，用覆盖索引优化上面的语句，会Extra列提示 Using index
select namej,age,position from client where name > 'a' order by name;

        使用覆盖索引的方式去优化，explain 分析得到 Using index

总结：

      ① MySQL支持两种方式的排序filesort和index,Using index指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

       ② order by满足两种情况会使用Using index : 

                1> order by 语句使用索引最左前列

                2> 使用where 子句与order by 子句条件列组合满足索引最左前列

        ③ 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时最左前缀法则。

        ④ 如果order by的条件不在索引列上，就会产生Using filesort

        ⑤ 能用覆盖索引尽量用覆盖索引的方式

        ⑥ group by 与order by 很类似，其实质是先排序后分组，按照索引创建顺序的最左前缀法则，对于group by的优化如果不需要排序的可以加上order by null禁止排序。where 高于 having,能写在where中的限定条件就不要去having中限定了！

Using filesort文件排序原理详解（扫聚簇索引）

        filesort文件排序方式：

         1>单路排序：是一次性取出满足条件行的所有字段，然后再sort buffer(默认1M)中进行排序；用trace工具可以看到sort_mode信息里显示<sort_key,additional_fieldss>或者<sort_key,packed_additional_fields>；把结果全部load进内存,然后根据要求的字段排序。

        2>双路排序（回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，只从结果集里面拿到id和排序字段，load进内存进行排序。排完，然后根据id进行回表操作。（占用内存小，但是要回表）用trace工具，sort_mode信息展示的是<sort_key,rowid>

       MySQL 通过比较系统变量max_length_for_sort_data（默认1024字节）的大小和需要查询的字段总大小来判断使用哪种排序模式。

        如果字段的总长度小于 max_length_for_sort_data，使用单路排序模式。

        如果字段的总长度大于max_length_for_sort_data ，使用双路排序模式。

        可手动设置 set max_length_for_sort_data=100; 100字节就会走双路排序

limit 分页优化

# 查询employee表里面的数据 从10001 至10010 共10条数据
select * from employee limit 10000,10;

        底层执行的时候，会将10010条数据全部查询出来，然后丢掉前10000条数据，只保留需要的这10条数据，性能不高的，越往后翻页，越慢。

        优化一：根据自增且连续的主键进排序的分页查询

# 会根据条件先通过索引树直接定位到id范围，取10条数据，
select * from employee where id >10000 limit 10;

        优化二：根据非主键字段排序的分页查询

        可能数据里面id被删除过，所以就不连续了，所以此时需要用二级索引来进行分页处理；关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录。

# 通过用索引排序 有可能不走索引，用索引可能成本更高 直接ALL全表扫描，用filesort 
select * from employees ORDER BY name limit 10000,10;
# 覆盖索引优化 也查了10010条数据，但是这里不回表，直接查id 覆盖索引得到结果集，再内连接同主表关联查询 得到相应的10条数据,
select * from employees e inner join (select id from employees order by name limit 10000,10) ed on e.id=ed.id;

 join 关联查询优化

        inner  join 为例

         t1表1w条数据，t2表100条数据

CREATE TABLE `t1` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`a` INT ( 11 ) DEFAULT NULL,
	`b` INT ( 11 ) DEFAULT NULL,
	PRIMARY KEY ( `id` ),
	KEY `idx_a` ( `a` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;
CREATE TABLE t2 LIKE t1;

# t2表有100条数据 t1表有1w条数据
select * from  t1 INNER JOIN t2 on t1.a=t2.a;

     1.嵌套循环连接Nested-Loop Join (NLJ）算法

      此时驱动表为t2表，先从t2表中获取一行一行的数据，将t1.a=t2.a 满足的数据获取，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。最终扫描的数据行就是200行。mysql 有时候也会选错

        如果是非索引的inner join 

        2.基于块的嵌套循环连接Block Nested-Loop Join(BNL)算法       join_buffer 分块

 select * from  t1 INNER JOIN t2 on t1.b=t2.b;

     把驱动表 t2的数据读入到内存 join_buffer中，然后扫描被驱动表，把被驱动表t1的每一行取出来根据条件，跟join_buffer（默认256k,可设置，如果放不下表t2的所有数据的话，就分段放）中的数据做对比（而且join_buffer中数据无序的，需要全部过滤一遍），如果关联到就代表结果集是自己想要的；t2表肯定扫描100次，t1表肯定是走聚簇索引扫全表，一共扫描 1w次+100次，内存中判断过滤的次数是100*1w ，如果强制用NLJ算法，因为没有走索引，所以磁盘一共扫描为100w次，明显BNL算法更快；

        优化器一般会优先选择小表作为驱动表，所以使用inner join 时，排在前面的表并不一定就是驱动表；使用inner join 过程中，如果mysql选择的驱动表不对，可以使用straight_join 让左边的表来驱动右边的表，能改变表优化器对于联表查询的执行顺序。

select * from  t2 STRAIGHT_JOIN t1  on t2.a=t1.a;

       关联sql的优化：不推荐做太多关联

        1.关联字段加索引，让mysql做join操作时尽量选择NLJ算法（大表关联的那个字段一定要走索引）

        2.小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去 mysql优化器自己判断的时间

in 和 exists 优化

select * from A where id in (select id from B)
 #等价于： 3 for(select id from B){ 4 select * from A where A.id = B.id 5 }

 select * from t2 where exists (select 1 from t1 where t1.id=t2.id);
 select * from t2 where exists (select 1 from t1 where t1.id=t2.id and t2.id>100);
 select * from t2 where exists (select * from t1 where t1.id=t2.id);

select * from A where exists (select 1 from B where B.id = A.id) 
#等价于: 
for(select * from A){
select * from B where B.id = A.id
}

 EXPLAIN select count(1) from employees; 
 EXPLAIN select count(id) from employees;
 EXPLAIN select count(name) from employees; 
 EXPLAIN select count(*) from employees; 
# 注意：以上4条sql只有根据某个字段count不会统计字段为null值的数据行

 索引设计原则：

        一、代码先行，索引后上。如果某个字段是范围查找，尽量将此字段建在联合索引的最后，因为范围查找之后的索引是不走的。

        二、联合索引尽量覆盖条件，尽量少建单值索引。让每一个联合索引都尽量去包含sql里的where，order by，group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。

        三、不要在小基数字段上建立索引。比如性别字段，该字段的基数就是2，不一样的值很少，意义不是很大，区分度不高。

        四、长字符串我们可以采用前缀索引。比如vachar(255)的大字段可能会比较占用磁盘空间，可以针对这个字段的前20个字符建立索引，对这个字段里的每个值的前20个字符放在索引树里面，类似于KEY index(name(20),age,position); order by 和 group by无法使用此类索引。

        五、where 与order by冲突时 优先where。大多数情况基于索引进行where筛选可以最快速度筛选出你要的少部分数据，然后做排序成本可能小很多。

        六、尽量利用一两个复杂的多字段联合索引，满足大部分的查询，然后用一两个辅助索引满足一些非典型查询。保证这种大数据量表的查询尽可能多的能充分利用索引，这样就能保证查询的速度和性能。

        七、基于慢sql查询做优化。设定超过多少秒的为慢sql,默认10秒

# 慢sql查询设定的时间 默认10秒 慢查询阈值
show variables like 'long_query_time'; 
# 手动设置 区分时间 超过时间的会存在一个单独的文件中
set global long_query_time=4;
# 开启慢sql查询会影响性能，一般从库开启，1为开启 0-关闭
slow_query_log: 1

       八、读多写少尽量多建索引，读和写都多，控制索引量（会影响插入删除等操作，会对索引操作）

索引规约：

        3.在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引KEY index(name(20),age,position);，根据实际文本区分度决定索引长度。索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

        4.页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。 索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

        5.不要使用 count(列名)或 count(常量)来替代 count(*)，count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。 说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

        6.in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内

        7.字符存储与表示，均采用utf8字符集，那么字符技术方法需要注意，一个字符是3个字节，如果需要存储表情(至少需要4字节)，需要选择utf8mb4；

补充：MySQL数据类型选择  

 建议：

         显示宽度一般没什么用，但如果有填充，比如tinyint 本来是1字节，显示宽度现在为2，值为1，查出来就是01； 

CREATE TABLE `user`( `id` TINYINT(2) UNSIGNED ZEROFILL);

 日期时间：

字符串 

   char(n),varchar(n)，数据库中的 单位代表的是字符！