mysql：索引优化细节

一、细节优化

       1、当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层，比如：

             select * from actor where actor_id + 4 = 5; 应该改为：

              select * from actor where actor_id = 1;

       2、尽量使用主键查询，而不是其他索引，避免触发回表查询；

       3、使用前缀索引：

                   如果索引很长，需要占用更多的存储空间，以及执行时间；通常情况下可以使用其开始的部分字符串，从而提高索引              的效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T到1之间。

            案例：

                    --查找最常见的名称列表，
                   select count(*) as cname, name from student group by name order by cname desc limit 10;

                    --查找最频繁出现的名称前缀，先从3个前缀字母开始，发现比原来出现的次数更多，可以分别截取多个字符查看名               称出现的次数
                    select count(*) as cname,left(name ,3) as pref from student group by pref order by cname desc limit 10;
                    select count(*) as cname,left(name ,7) as pref from student  group by pref order by cnamedesc limit 10;

                   --计算完成之后可以创建前缀索引
                   alter table student  add key(name (7));

                    --注意：前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by 和                  group by。 

       4、使用索引扫描来排序

       5、union all,in,or都能够使用索引，但是推荐使用in

       6、范围列可以用到索引     

             --注意：范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列（组合索引）

       7、强制类型转换会全表扫描

             -- 比较  explain select * from user where phone=13800001234 与 explain select * from user where                                               phone=‘13800001234’

       8、更新十分频繁，数据区分度不高的字段上不宜建立索引

             （1）、更新频繁的字段，也会使B+树频繁更新，降低数据库性能

               (2）、区分不大的属性，无法过滤无关数据

               (3)、一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算

       9、创建索引的列，不允许为null，可能会得到不符合预期的结果

       10、当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致

       11、能使用limit的时候尽量使用limit

       12、单表索引建议控制在5个以内

       13、单索引字段数不允许超过5个（组合索引）

       14、创建索引的时候应该避免以下错误概念

            （1）、越多越好

            （2）、过早优化，在不了解系统的情况下进行优化

二、索引监控

       show status like 'Handler_read%';

       Handler_read_first：读取索引第一个条目的次数

       Handler_read_key：通过index获取数据的次数

       Handler_read_last：读取索引最后一个条目的次数

       Handler_read_next：通过索引读取下一条数据的次数

       Handler_read_prev：通过索引读取上一条数据的次数

       Handler_read_rnd：从固定位置读取数据的次数

       Handler_read_rnd_next：从数据节点读取下一条数据的次数