数据库索引

1.索引的意义在于提升select的查询效率

2.设计索引的原则;

（1）考虑某列中值的分布，索引的列的基数越大，则索引效果越好。

例如：出生日期具有不同的值，但是性别只有男女，那么出生日期作为索引，区分度比较好。

（2）为经常需要查询的字段建立索引

（3）为经常需要order by 、group by 、union 等操作字段建立索引，减少性能消耗。

（4）并不是索引建立的越多越好

首先索引是要占据一定的空间的，多个索引会降低数据的写操作的性能，因为多个索引在更新数据时要进行同步更新。

（5）使用短索引。

在对字符串建立索引时，应指定一个前缀长度，无需对其全部进行索引，因为索引太长占用区间，导致磁盘io性能下降。

此外，一般前缀10到20个已经能达到的区分度很高了。

3.索引类型：b树索引，hash索引，全文索引

hash索引的特性：

（1）只能使用=操作符的等式进行比较，不能确定在某个范围内存在多少值，因此不支持> < >= <= between !=等操作符，而b树则能支持，因此b树索引的使用范围更广

（2）优化器不能使用hash索引来加速order by 的操作

memory默认使用hash索引

select * from t1 where key_col = 1 or key_col in (15,18,20);//使用hash索引和b树索引均可

select * from t1 where key_col >=1;//只能使用b树索引

4.索引的使用场景

create table stu(
id int primary key,
name varchar(20),
age int,
subject varchar(20),
hobby varchar(20),
des varchar(20)
);

创建两个单值索引：

CREATE INDEX nameindex  ON stu (name) ;
CREATE INDEX desindex  ON stu (des) ;

插入值

insert into stu values(1,'shq',18,'computer','swimming','haha');
insert into stu values (2,'shqs',26,'materials','dancing','dada');
insert into stu values(3,'xiaoming',28,'bio','train','ha');
insert into stu values(4,'huahua',20,'computer','swimming','hahas');
insert into stu values(5,'shq',30,'computer','hiding','haha');

（1）匹配全值

（2）匹配值的范围查询

（3）匹配最左前缀

CREATE INDEX mulindex  ON stu (name,des) ;

5.索引失效的场景

（1）以%开头的模糊查询

（2）复合索引的情况下，查询条件不包含索引的最左列部分

（3）sql 估计使用索引查询比全表查询更慢

（4）使用or分割进行查询的时候，如果or前的条件中的列有索引，但是后面的列没有索引，则所涉及到的列都不会使用索引

6.使用索引对order by 、group by进行优化

（1）order by

CREATE INDEX ageindex  ON stu (age) ;

在以下几种order by 中不使用索引：

• order by 的字段混合 asc 和 desc
• 用于查询行的关键字和排序字段不同 select * from stu where name="shq" order by age;
• 对不同的关键字使用order by

group by 与 order by 类似

优化嵌套子查询：子查询的效率不如关联查询（join），因为子查询需要在内存中创建临时表

优化or条件查询：

mysql在处理含有or查询时的子句时，实际是对or的各个字段分别查询后的结果进行了union操作。但建有复合索引时却用不到。因为复合索引事根据and条件来生成的，所有or条件用不上

要提高数据库的访问效率，还可以对数据库表进行拆分，包括水平拆分和垂直拆分

（1）垂直拆分：

按照访问次数，将主码和一些列放到一个表中，将主码和剩下不常用的列放到另一个表中。

垂直拆分能够使数据行变小，一个数据页能存放更多的数据，缺点是需要冗余管理。

（2）水平拆分：

根据不同的划分，比如把最近三个月的账单放到一个表中，把以前的放到另一个表中，或者是根据地区分类。