Mysql_索引相关优化

索引的相关优化

索引的基本知识

​ 索引能够大大的提高系统的性能。

索引的优点

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
• 大大加快数据的检索速度，这也是创建索引的最主要原因
• 加快表与表之间的连接，在实现数据的参考完整性方面特别有意义
• 在使用分组和排序，子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

​ 当然索引的维护也需要相应的开销：

• 创建索引和维护索引需要时间，这种时间随着数据量的增加而增加
• 索引需要占用一定的物理存储空间
• 在对表进行增删改的时候，索引也需要动态的维护

索引的分类

• 主键索引：某一个属性或属性组可以唯一标识一行记录(一张表只能有一个)
• 唯一索引：同一张表中的某一个列没有重复的值(一张表可以有多个)
• 普通索引：基本的索引类型，没有唯一性之类的限制
• 全文索引：目前只有MyISAM引擎支持。目前只有在char、varchar、text列上可以创建
• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

回表：非主键索引的叶子节点存储的并不是一整行记录，而是这一行记录对应的主键。索引在非主键索引上查询的时候，得到非主键索引叶子节点的数据是得到目标记录的主键，通过此主键再查询主键索引B+树获取目标记录的过程叫回表(主键索引的叶子节点存储的是一整行记录)，此过程一共有两次B+树检索。例如：select * from emp where name=‘1’;在name列的索引的叶子节点得到的是主键索引，索引需要主键索引再查询整行记录。

覆盖索引：如果通过非主键索引查询的不是一行记录，而是主键(比如select id from emp where name=‘1’;而不是select * from emp where name=‘1’????.那么在非主键索引的B+树的叶子节点得到结果(即对于行的主键)，就不需要再去主键索引的B+树上进行查询，直接返回该索引值即可，减少了一次B+树的检索。

最左匹配：在组合索引（例如索引列为：name和age两列），在执行select * from emp where name=? and age=?;的时候，会先匹配name列，再匹配age列。但是如果执行select * from emp where age=?;，跳过了name列，这种情况下不会使用此索引。必须先有左边的属性，才能在使用右边的属性时候使用索引。

索引下推：select * from emp where name=? and age=?;在此语句的过程中，将数据从存储引擎到server层的时候有两种方案：1.先去的所有name=?的数据到server层，然后在server层过滤age=?的相关数据；2.从存储引擎中获取数据到时候直接过滤name=？和age=?的数据，在server不需要再进行过滤，此时从存储引擎读入到server的数据就表少了，同样减少了IO。第二种方式为索引下推。从mysql5.6开始引入索引下推

索引的匹配方式

create table staffs(
id int primary key auto_increment,
name varchar(24) not null default ‘’ comment ‘姓名’,
age int not null default 0 comment ‘年龄’,
pos varchar(20) not null default ‘’ comment ‘职位’,
add_time timestamp not null default current_timestamp comment ‘入职时间’
) charset utf8 comment ‘员工记录表’;
alter table staffs add index idx_nap(name, age, pos);

• 全值匹配：指的是和索引中所有的列进行匹配。

explain select * from staffs where name = 'July' and age = '23' and pos = 'dev';

• 匹配最左前缀：只匹配前面的几列

上图中，第三条的sql的where子句的列虽然也在索引里，但是由于最左匹配的原因，并没有使用索引。

• 匹配列前缀：可以匹配某一列的开头部分

上图中的第二条sql，通过列前缀匹配使用了索引。其执行计划中的type:range表示利用索引限制范围查询。

• 匹配范围值：查找某一个范围的数据

• 精确匹配某一列并范围匹配另一列：可以查询第一列的全部和第二列的部分

​ 上图中，第一条sql中where子句的两个列都用到了索引，第二条sql中where子句的两个列只有name使用了索引。因为最左匹配的原因，age列没有进行匹配，在age列右边的pos列也不会使用索引进行匹配。

​ 下图中，where子句中三列的顺序并不是索引定义顺序，但是三列均使用了索引。因为mysql优化器会对顺序进行优化处理。

• 只访问索引的查询：查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引.

组合索引

​ 当包含多个列作为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要。

例：建立组合索引a,b,c

SQL	索引是否起作用
where a = ?	是，只使用a列
where a = ? and b = ?	是，使用到a，b列
where a = ? and b = ? and c = ?	是，使用到a,b,c列
where b = ?	否
where c = ?	否
where a = ? and c = ?	是，只使用a
where a = ? and b > ? and c = ?	是，使用了a，b
where a = ? and b like ‘’%xx%’’ and c = ?	是，只使用a

聚簇索引

​ 不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起。

优点

• 可以把相关数据保存在一起，数据访问更快，因为索引和数据保存在同一个树中
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点

• 聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势
• 更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

聚簇索引的缺点主要是由于数据在增删的过程中需要对数据页面进行分裂和合并操作。

索引相关优化

1. 当使用索引列进行查询的时候尽量不要使用表达式，把相关计算放也业务层；

   select id from tab where id='5';

   select id from tab where id+1='4';

2. 尽可能的使用主键索引查询，因为主键的叶子节点是具体的数据，不会发生回表；

3. 使用前缀索引。通常情况下，对于某些列的前缀的选择性也是足够高的，可以满足查询的性能，但是对于BLOB，TEXT，VARCHAR，因为Mysql不允许索引这些列的完整长度，所以使用前缀索引选择足够的前缀来保证索引较高的选择性。

   ​ 索引的选择行指的是不重复的索引值和数据记录总数的比值，范围是1/T到1之间，索引的选择性越高，则查询效率越高。但是如果索引很长的字符串，就会使得索引变得很大且很慢，这是就需要前缀索引来减少索引的空间占用，减少磁盘的IO次数，并提供查询效率。

   alter table tab1 add key(col1(x));给表tab1的col1列新增长度为x的前缀索引。

4. 范围列可以用到索引。范围条件：>、<、>=、<=、between。范围列可以用索引，但是范围列后面的列就无法用到索引。where a = ? and b > ? and c = ?，这个就值使用了a,b列，c列就没有用到索引。

5. 强制类型转换会全表扫描。

   create table user(id int,name varchar(10),phone varchar(11));
   alter table user add index idx_1(phone);
   explain select * from user where phone=13800001234;--不触发索引
   explain select * from user where phone='13800001234';--触发索引
   

   

6. 更新频繁的数据或者说数据区分不高的字段不适合建立索引。

   跟新频繁将花费更多的性能在B+树的维护上。

   数据区分度较低，不能有效的过滤数据，简历索引的意义不大。

7. 能使用limit的时候尽可能的使用limit。比如，只有一行结果的时候使用limit 1.

8. 单表的索引建议控制在5个内。

9. 创建索引的时候应该避免以下错误概念：a.索引越多越好；b.过早优化，在不了解系统的情况下进行优化。