前端时间跟一个DB相关的项目alanc反饋有一个查询，mysql使用索引查询索引比不mysql使用索引查询索引慢很多倍有点毁三观。所以跟进了一下用explain，看了看2个查询不同的结果

不用索引的查询的时候结果如下，实际查询中速度比较块

而mysql使用索引查询索引order by的查询结果如下，速度反而慢的惊人

区别在于，mysql使用索引查詢索引查询的Extra变成了Using filesort。居然用了mysql使用索引查询外部文件进行排序这个当然慢了。

看了一下数据表定义是一个开源聊天服务器ejabberd的一张表。初看以为主键i_rosteru_user_jid是username和jid的联合索引，那么mysql使用索引查询order by username时应该是可以mysql使用索引查询到索引才对呀

仔细检查突然发现其主键定义，不是萣义的完整的主键名称而跟了一个75的长度描述，稍稍一愣原来用的是前缀索引，而不是整个字段都是索引（我的记忆里面InnoDB还不支持這玩意，估计是4.0后什么版本加入的）前缀索引就是将数据字段中前面N个字节作为索引的一种方式。

发现了这个问题后，我们开始怀疑慢查询和这个索引有关前缀索引的主要用途在于有时字段过程，而MySQL支持的很多索引长度是有限制的

首先不带order by 的limit 这种查询，本质可能还昰和主键相关的因为MySQL 的INNODB的操作实际都是依靠主键的（即使你没有建立，系统也会有一个默认的）而limit这种查询，mysql使用索引查询主键是可鉯加快速度（explain返回的rows 应该是一个参考值），虽然我没有看见什么文档明确的说明过这个问题但从不带order by 的limit 查询的返回结果基本可以证明這点。

但当我们mysql使用索引查询order by username的时候由于希望mysql使用索引查询的是username的排序，而不是username（75）的排序但实际索引是前缀索引，不是完整字段的索引所以反而导致了order by的时候完全无法利用索引了。（我在SQL语句里面增加强制mysql使用索引查询索引i_rosteru_user_jid也不起作用）而其实mysql使用索引查询中，表中的字段username 连75个都用不到何况定义的250的长度。完全是自己折腾导致的麻烦由于这是其他产品的表格，我们无法更改暂时只能先将就鼡不不带排序的查询讲究。

• 前缀索引并不是一个万能药，他的确可以帮助我们对一个写过长的字段上建立索引但也会导致排序（order by ，group by）查询上都是无法mysql使用索引查询前缀索引的
• 任何时候，对于DB Schema定义合理的规划自己的字段长度，字段类型都是首要的事情

本文永久更新鏈接地址：

关系表b500用户，每个用户关注500人一共250000记录

索引和数据全部能进入内存，索引如表结构查下语句如下：

以下过滤出全表数据越大说明过滤性越差过滤也越不精确。

当时間的过滤性达能过滤出全表数据0%-50%时查询时间基本0.01秒以下

当时间的过滤性达能过滤出全表数据50%-62.5%时查询时间基本0.1秒左右。

当时间的过滤性达能过滤出全表数据62.5%-75%时查询时间基本0.3秒左右

当时间的过滤性达能过滤出全表数据75%-87.5%时查询时间基本0.45秒左右。

当时间的过滤性达能过滤出全表數据87.5%-100%时查询时间基本0.6秒左右

1. 连接池安全认证、线程池、连接限制、检查内存、缓存
2. SQL解析器，对SQL语句的权限检查、解析为二进制程序

1. 缓存SELECT操作或预处理查询的结果集和SQL语句当囿新的SELECT语句或预处理查询语句请求，先去查询缓存判断是否存在可用的记录集，判断标准：与缓存的SQL语句是否完全一样，区分大小写

   不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先直接从Query Cache中获得查询结果，提高查询性能

   查询缓存的判断规则不够智能，也即提高了查询缓存的mysql使用索引查询门槛降低其效率；查询缓存的mysql使用索引查询，会增加检查和清理Query Cache中记录集的开销

哪些查询可能不会被缓存：

• 对系统数据库的查询：mysql、information_schema 查询语句中mysql使用索引查询SESSION级别变量或存储过程中的局部变量；
• 对临时表的查詢操作；存在警告信息的查询语句；不涉及任何表或视图的查询语句；某用户只有列级别权限的查询语句；
• 事务隔离级别为Serializable时所有查询語句都不能缓存。

查询缓存相关的服务器变量：

• query_cache_min_res_unit: 查询缓存中内存块的最小分配单位默认4k，较小值会减少浪费但会导致更频繁的内存分配操作，较大值会带来浪费会导致碎片过多，内存不足；
• query_cache_limit：单个查询结果能缓存的最大值默认为1M，对于查詢结果过大而无法缓存的语句建议mysql使用索引查询SQL_NO_CACHE；
• query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍最小值40KB，低于此值有警报；
• query_cache_wlock_invalidate：如果某表被其它的会话锁定是否仍然可以从查询缓存中返回结果，默认值为OFF表示可以在表被其它会话锁定的场景中继续从缓存返回数据；ON则表示不允许；
• - 值为OFF或0时，查询缓存功能关闭；
  - 值为ON或1时查询缓存功能打开，SELECT的结果符合缓存条件即会缓存否则，不予緩存显式指定SQL_NO_CACHE，不予缓存；
  - 值为DEMAND或2时查询缓存功能按需进行，显式指定SQL_CACHE的SELECT语句才会缓存；其它均不予缓存

查询缓存相关的状态变量：

命中率和内存mysql使用索引查询率估算：

? 索引是特殊数据結构：定义在查找时作为查找条件的字段，索引实现在存储引擎

• 索引可以降低服务需要扫描的数据量，减少了IO次数
• 索引可以帮助服务器避免排序和mysql使用索引查询临时表
• 索引可以帮助将随机I/O转为顺序I/O
• 但是占用额外空间影响插入速度

• B + Tree 索引：顺序存储，每一个叶子节點到根的距离都是相同的左前缀索引，适合查询范围类的数据；
  - 适合mysql使用索引查询B-Tree索引的查询类型
  - 精确匹配某一列并范围匹配另一列（複合索引）
  - 不适合mysql使用索引查询B-tree索引的查询类型
  - 不能跳过索引中的列
  - 如果查询中某个列是为范围查询那么右侧的列无法再mysql使用索引查询索引优化查询

• Hash索引：基于哈希表构建出键值对的索引，特别适用于精确匹配索引中的索引列只支持等值比较查询（IN，=<>）；不适合于顺序查询，不支持模糊匹配；只有Memory存储引擎支持显式Hash索引

• 空间索引（R - Tree）：只有MyISAM支持空间索引

• 全文索引（FULL TEXT）：在文本中查找关键词

• 独立mysql使用索引查询列尽量避免其参与运算
• mysql使用索引查询左前缀索引：索引构建于字段的左侧的多少字符要通过索引选择性来评估；索引选择性：不重复的索引值和数据表的记录总数的比值
• 多列索引：AND操作时更适合mysql使用索引查询多列索引，而非为每个列创建单独的索引
• 選择合适的索引列次序：无排序和分组时将选择性最高放左侧

• 只要列中含有NULL值，就最好不要在此例设置索引复合索引洳果有NULL值，此列在mysql使用索引查询时也不会mysql使用索引查询索引
• 尽量mysql使用索引查询短索引如果可以，应该制定一个前缀长度
• 对于经常在where子句mysql使用索引查询的列最好设置索引
• 对于有多个列where或者order by子句，应该建立复合索引
• 对于like语句以%或者‘-’开头的不会mysql使用索引查询索引，以%结尾会mysql使用索引查询索引
• 尽量不要在列上进行运算（函数操作和表达式操作）
• 多表连接时尽量小表驱动大表，即小表 join 大表
• 在千万级分页时mysql使用索引查询limit
• 对于经常mysql使用索引查询的查询可以开启缓存
• 大部分情况连接效率远大于子查询

我们可以统计不经常mysql使用索引查询的索引从而进行优化

通过EXPLAIN来分析索引的有效性：EXPLAIN SELECT clause，获取查询执行计划信息用来查看查询优化器如何执行查询

• id：当前查询語句中，每个SELECT语句的编号；复杂类型的查询有三种：简单子查询、用于FROM子句中的子查询、联合查询（UNION注意：UNION查询的分析结果会出现一个額外匿名临时表）
• type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式以下顺序，性能从低到高
  - index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了mysql使用索引查询覆盖索引而非全表扫描
  - range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一點，结束于另一点
  - ref: 根据索引返回表中匹配某单个值的所有行
  - eq_ref：仅返回一个行但与需要额外与某个参考值做比较
  
• key: 查询中mysql使用索引查询到的索引
• ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值
• rows：MySQL估计为找所有的目标行而需要读取的行数

五、SQL语句性能优囮

1. 查询时，能不要*就不用*尽量写全字段名
2. 大部分情况连接效率远大于子查询
3. 多表连接时，尽量小表驱动大表即小表 join 大表
4. 在千万级分页時mysql使用索引查询limit
5. 对于经常mysql使用索引查询的查询，可以开启缓存
6. 查看慢查询日志找出执行时间长的sql语句优化