《高性能mysql》之高性能索引创建(第五章)

①索引的类型：

     -- B-Tree索引：

          B-Tree索引加快访问数据的速度，因为存储引擎不再需要全表扫描而是从索引根节点开始搜索

           

          使用范围： 全值匹配、最左前缀、列前缀、范围值、精确匹配某一列并范围匹配另一列、只访问索引查询

          注：索引列的顺序很重要！！！比如：先用索引过滤然后and一个like,因为like使索引失效，如果放前面就会首先全部顺序检索。

     -- 哈希索引：

          MySql中仅memory支持，InnoDB有自动哈希索引(当注意到某些索引用的频繁时开启)

          对于不支持哈希的，可模拟创建如下：

               假如本身以URL为索引如下：

                    SELECT id FROM url WHERE url='http://www.mysql.com';

               以上做法存储大，查询性能慢，对此可额外添加一索引列(url_crc)用于伪哈希，并删除url上索引

               

               注：以上做法能提高性能但需要触发器 维护索引

                            

     注：索引并不是最优解决方案，对于TB级数据应用块级别元数据技术替代。

②高性能索引策略：

     -- 独立的列：索引列单独放在比较符号一侧

          SELECT id FROM test WHERE id+1=5;     X

     

     -- 前缀索引：对于过大数据，模拟哈希支持不好，此时用前缀索引替代(如对BLOB、TEXT或过长VARCHAR必须用前缀索引)

          首先判断索引建到多少合适：

select count(distinct left(time,5))/count(*) as no1,     count(distinct left(time,6))/count(*) as no2,     count(distinct left(time,7))/count(*) as no3,     count(distinct left(time,8))/count(*) as no4,     count(distinct left(time,9))/count(*) as no5,     count(distinct left(time,10))/count(*) as no6,     count(distinct time)/count(*) as nofrom test.demo;

                

          结果如下：从中找出与no最相近的，这里是10，然后就可以建索引为-->ALTER TABLE test.demo ADD KEY (time(10));

     -- 多列索引：不少人会为每一个列建索引，这样联合查找时性能不佳(尽管mysql5.0以上会自动将独立索引合并为联合索引)

          对于独立索引在OR时可用UNION ALL优化：

          

     注：优化器不会吧独立索引自动合并计算到查询成本，导致成本低估。

          

     -- 选择合适索引列顺序：

          where中将选择性最高的列放在最前列(趁早过滤完)，选择方法如下：

select count(distinct id)/count(*) as no1,     count(distinct uid)/count(*) as nofrom test.demo;

          注：上面如果需要两个一起做条件，则选择较大值放前面(如no>no1,选no)

          

     -- 聚簇索引：暂不记录

     -- 覆盖索引：select的数据列只用从索引中就能够取得，不必从数据表中读取，如下列子：

          EXPLAIN SELECTid,name FROM test.demo \G;   #其中id,name列都为索引

          有时覆盖索引很难达到，这时候就部分覆盖(延迟关联)：

          

          

     -- 扫面索引排序：order by排序中的字段应全为索引列，如下：

          EXPLAIN SELECT id,name FROM demo where id<1000 order byid,name\G;  #其中id,name都为索引列

          EXPLAIN SELECT id,name FROM demo where id<1000 order by id,content\G;  #content不为索引列，所以索引覆盖失效

     

     

     -- 前缀索引压缩：不多说，具体在CREATE TABLE中指定pack_keys控制

     -- 冗余和重复索引：有时候需要同时 多条件查询或独立查询，为了快速，可在同一列上建立多种索引，如联合索引与独立索引(创建多种索引会是性能比单个稍低)

          例：创建(A, B)，就不必创建(A)，但如果要单独搜B，则要创建(B)，因为Innodb仅支持前缀(A是前缀，B不是)

注：where尽可能将范围查询置于索引后面，如in、between.