Mysql从入门到放弃--索引

一.索引种类（磁盘上的数据结构，索引的扫描实际上是从磁盘读入到内存）

• 辅助索引(非聚集索引）:

一个表中可以有多个，叶子节点存放的不是一整行数据，而是键值，叶子节点的索引行中还包含了一个’书签’，这个书签就是指向聚簇索引的一个指针，从而在聚簇索引树中找到一整行数据。

好处：
1.优化了查询，减少CPU mem IO消耗
2.减少了文件排序

• 覆盖索引:

指从辅助索引中就能获取到需要的记录，而不需要查找聚簇索引中的记录。使用覆盖索引的一个好处是因为辅助索引不包括一条记录的整行信息，所以数据量较聚集索引要少，可以减少大量io操作。

• 聚集索引:

一个表中只能有一个，聚集索引的顺序与数据真实的物理存储顺序一致。查询速度贼快，聚集索引的叶子节点上是该行的所有数据，聚集索引能加快范围查询(聚集索引的顺序和数据存放的逻辑顺序一致)。主键!=聚集索引。

建表时，如果有主键列，会自动生成聚集索引，聚集索引实际上是没有叶子节点的，它的叶子节点我们可以理解成磁盘上的一行一行数据，而且这些数据是按照聚集索引的顺序存储的。没有主键列，会去找unique

好处:
1.减少回表查询的机率
2.将随机IO转换成顺序IO

• 联合索引：

就是由多列组成的的索引。遵循最左前缀规则。对where，order by，group by 都生效

聚集索引与辅助索引的区别：
叶子节点是否存放的为一整行数据

更多详细请参照：https://blog.csdn.net/u012006689/article/details/73195837

二.explain命令的应用

获取优化器选择后的执行计划

mysql> desc select * from city where countrycode='CHN'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
         type: ref
possible_keys: CountryCode  ---可能会走的索引
          key: CountryCode  ---真正走的索引
      key_len: 3
          ref: const        ---索引类型
         rows: 363
        Extra: Using index condition   ---额外信息
1 row in set (0.00 sec)

type：

ALL     ---->全表扫描
explain  select * from city;
线上业务一般不会出现全表扫描
一般会使用备库：数据处理分析

Index   ---->全索引扫描
explain select countrycode from city;
基本上很少出现


range:索引范围扫描
针对索引键做以下查询时
>  < in or like
我们觉得使用索引优化查询的最低级别
< >:最好执行上下
like:语句前导字符尽量唯一性强一些，不能出现%在前面
对于in和or尽量改写为union all


ref：辅助索引等值查询
mysql> explain select * from city where countrycode='CHN' union all  select * from city where countrycode='USA'\G'
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: city
         type: ref
possible_keys: CountryCode
          key: CountryCode
      key_len: 3
          ref: const
         rows: 363
        Extra: Using index condition
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: city
         type: ref
possible_keys: CountryCode
          key: CountryCode
      key_len: 3
          ref: const
         rows: 274
        Extra: Using index condition
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using temporary
3 rows in set (0.00 sec)

eq_ref:表连接时on的条件列是主键或者唯一键 
a join b on a.id=b.id
如果达不到，至少要有辅助索引，一般和where条件列建联合索引

system/const:where 条件列,是主键或唯一键的等值查询

NULL:索引中获取不到值的时候

Extra：Using filesort 回原表查询  需要进行优化

辅助索引 应用顺序(优化器选择的)
如果查询条件：符合覆盖索引的顺序时，优先选择覆盖索引
不符合顺序,优先会走where条件的索引
优化的方法，将where列和order列建立联合索引

mysql> alter table city add index idx_co_po(countrycode,population);  ---添加联合索引
mysql> explain select * from city where countrycode='CHN'  order by population desc limit 10\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
         type: ref
possible_keys: CountryCode,idx_co_po
          key: idx_co_po
      key_len: 3
          ref: const
         rows: 363
        Extra: Using where
1 row in set (0.00 sec)

explain 使用场景

1.mysql出现性能问题（排除硬件，架构原因，参数，锁）
2.获取到问题语句
       问题情况：数据库hang（卡了，资源耗尽）
       处理过程：
           (1)show full processlist ; 获取到导致数据库hang的语句
           (2)explain 分析sql的执行计划，有没有走索引，索引的类型情况
         （3)建索引,改语句
       问题情况：一段时间慢
        处理过程：
               (1)记录慢日志 slowlog,分析slowlog
               (2)explain 分析sql的执行计划，有没有走索引，索引的类型情况
               (3)建索引,改语句

三.建立索引的原则(运维规范)

为了使索引的使用率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引
1.建表时，一定要有主键，如果没有相关列可以作为主键，做一个无关列

2.选择唯一性索引：唯一性索引的值是惟一的，可以更快速的通过该索引来确定某条记录
例如：学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名的现象，从而降低查询速度.
主键索引和唯一键索引，在查询中使用是效率最高的
select count(*) from world.city

注意：如果重复值较多，可以考虑采用联合索引

3.为经常需要排序，分组和联合操作的字段建立索引
经常需要order by，group by，distinct和union等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作

4.为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度,因此，为这样的字段建立索引，可以提高整个表的查询速度
4.1 经常查询
4.2 列值的重复值少

注意：如果经常作为条件的列，重复值特别多，可以建立联合索引.

5.尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来建索引。例如：TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高索引速度

--------------------------------------------以上的是重点关注的，以下是能保证则保证-------------------------------------------------------------------------------
6.限制索引的数目
索引的数目不是越多越好，每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦，越多的索引，会使更新表变得很浪费时间

7.删除不再使用或者很少使用的索引（percona tookit）
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不在需要，数据库管理员应当定期找出这些索引，将他们删除，从而减少索引对更新操作的影响

8.大表加索引,要在业务不忙期间操作

建索引原则总结
1.必须要有主键，如果没有可以做为主键条件的列，创建无关列
2.经常做为where 条件列 order by group by join on 的条件
3.最好使用唯一值多的列作为索引，如果索引列重复值较多，可以考虑使用联合索引
4. 列值长度较长的索引列，我们建议使用前缀索引
5.降低索引条目，一方面不要创建没用的索引,不常使用的索引清理,使用percona tookit 工具
6.索引维护要避开业务繁忙期

-------------------------------------------------不走索引的情况(开发规范)---------------------------------------------------------------------------------------------------
重点关注：
1）没有查询条件，或者查询条件没有建立索引
select * from tab; 全表扫描
select * from tab where 1=1；
在业务数据库中，特别是数据量比较大的表，是没有全表扫描这种需求

2）select * from tab where name=‘张三’ name 列没有索引
改：
1.换成有索引的列作为查询条件
2.将name列建立索引

3）查询结果集是原表中的大部分数据，应该是25%以上。
查询的结果集，超过了总行数25%,优化器觉得就没必要走索引了
假如： tab表 id ，name id：1-100W ，id列有索引

select * from tab where id>5000000;
如果业务允许，可以使用limit控制

4）索引本身失效，统计数据不真实

索引有自我维护的能力
对于表内容变化比较频繁的情况，有可能会出现索引失效

5）查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,!等)
例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;

6. 隐式转换导致索引失效，这一点应当引起重视，也是开发中经常会范的错误，这样会导致索引失效
   错误例子：
   alter table tab and index inx_tel(telnum);
   
   7）< > ，not in 不走索引
   
   单独的>,<，in有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
   or 或in 尽量改成union

explain select * from teltab where telnum in (‘110’ , ‘119’);
改写成：
explain select * from teltab where telnum=‘110’
union all
select * from teltab where telnum=‘119’

8)like “%_” 百分号在最前面不走
explain select * from teltab where telnum like ‘31%’ 走range索引扫描
explain select * from teltab where telnum like ‘%110’ 不走索引

%linux% 类的搜索需求，可以使用elasticsearch

9）单独引用复合索引里非第一位置的索引列