mysql 索引

关于索引优化的问题

1 具体使用还是不使用索引这跟mysql执行sql语句的性能有关 因为它内部有优化器的存在

2. 比如在联合索引中 name age 如果在数据量比较大的时候 联合索引第一个字段就使用了大于小于 这样这个sql 可能根本就不会走索引 如果使用了覆盖索引 这个name字段会走索引 因为你只使用了覆盖索引 不需要再回表了 但是走的索引类型也是index 扫描索引树效率也不高。

3.尽量使用联合索引 不要创建单个的索引字段 在数据量比较大的时候 in or 会走索引 数据量小的时候不会走索引

4.like 字段% 这个字段在5.6之前是不会走索引的 在5.6之后会走索引 因为引入了索引下推的一个机制 就是在索引树上找到了这个以字段开头的数据的时候 在此时就开始比较剩下的字段 age 过滤掉了不符合的数据 然后再回表 减少了回表的次数 为什么大于小于 没有用到索引下推呢？ 这可能是因为mysql认为 大于小于 的结果集的范围比较大 （加上覆盖索引 也有很大的可能走索引） 所以 尽量使用覆盖索引 少使用 * 防止回表

5.对于order by group by 排序 也尽量要使用到索引 在explain 执行计划中的extra 字段下 如果是using filesort 排序的效率就会比较低 using index 的效率要高 针对这样 我们可以 在order by 后面添加完整的 联合索引 或者where 条件后 与order by 后 都满足最左前缀 能使用覆盖索引的 就使用覆盖索引

6.针对limit  当我们 写了一个 limit 10000  10 这个语句 mysql 会从 数据库查找10010条数据 然后丢弃掉前10000  这也就是深度分页的问题

    我们如何解决这个问题？

   第一种可以  通过where 条件 id 大于10000 然后再limit 10  通过过滤 可以解决这个问题 但是 这里的id 比如自增且中间没有间隔 的数据id 才行。

 

   第二种 可以使用子查询 首先 通过limit 10000  1  查找到 第10001条数据的主键id 然后 再根据 where 条件 大于这个id  limit 10  这样也可以查找到。 这样虽然里面的也会先拿到10001条数据然后抛弃前10000条 但是因为只走的主键 没有回表 效率也会提升很多。

 

7.针对 join连表查询 尽量 小表驱动大表 inner join 有内部自动指定 left join 左边的是小表 right join 右边的是小表 连表时 on 的条件尽量要走索引 走索引 的n L J算法 会比不走索引的bnl算法要速度快 为什么 不走索引的nlj 要比bnl 的效率要低呢？

一条sql语句很慢有什么原因导致？

  当然首先需要能定位找到这条sql 我们可以通过在mysql 得配置文件中进行配置 多少秒算是慢 以及日志的位置 然后就能看到慢sql

那么有什么原因会让sql很慢？

   1.首先sql 索引失效

   2. 多表join 

   3.查询的字段过多 回表次数过多

    4.数据库的连接不够了 也会导致

   5.锁与锁之间的冲突也会  比如2个 update 在有索引的情况下  因为update 是排他锁 所以会阻塞等待  这里说一下 锁 锁的是什么 在有索引的情况下 锁的是当前索引  行级锁  因为索引分为了当前字段的索引跟这条索引的主键索引 那么 这两索引会有冲突  比如 一个语句锁住了二级索引 正要去锁主键索引时  另外一个语句锁住了主键索引 那么第一个语句就会等待 比如下面这个例子。

首先创建了一整表

 对name字段创建了一个普通索引

开启2个事务

 第二个事务

 第一个事务  阻塞 因为没有拿到主键索引

 

   6.事务比较长

索引失效的一些情况

1.联合索引不遵循最左前缀

2.在索引字段上进行操作

3.隐士的类型转化 比较VARCHAR 类型不加‘ ’

4.在使用 in or 同一个索引字段时 可能不能走索引 这要根据表的大小 等因素来看是否会走索引

5.范围查询 大于 小于 没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是

由于单次数据量查询过大导致优化器最终选择不走索引

优化方法：可以将大的范围拆分成多个小范围

6..存储引擎不能使用索引中范围条件右边的列

1. like 以通配符开头的 mysql索引失效会变成全表扫描操作 最终走不走索引都是跟 时间花费有关系

   问题：解决like'%字符串%'索引不被使用的方法？

   使用覆盖索引，查询字段必须是建立覆盖索引字段 但是也只是index 虽然走索引 也是扫描的整个索引树 效率不高。

2. 如何查看当前语句的效率 可以看后面会写explain 分析里面会有对一些关键词的分析