MySQL索引管理
一.索引简介
- 索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
- 让获取的数据更有目的性，从而提高数据库检索数据的性能。
二.索引类型（算法）介绍
- BTREE:B+树索引
- HASH：HASH索引
- FULLTEXT：全文索引
- RTREE：R树索引
说明：目前数据库索引所使用的的算法为BTREE。
优点： - 1.在叶子节点，添加了相邻节点的指针
- 2.优化了，范围查询
三.索引分类及创建原则
1.索引分类
- 主键索引（聚集索引）
- 普通索引
- 联合索引
- 前缀索引
- 唯一索引（唯一键）
2.创建索引
创建索引的条件限制
主键：唯一 、 非空 primary key
唯一键：唯一 、 可以为空 unique key
primary key = unique key + not null
索引创建的原则
-
1.创建唯一索引
-
2.如果该列重复值较多采用联合索引
-
3.经常需要排序、分组和联合的字段建立索引
-
4.常作为查询条件的字段添加索引
没有重复值建立唯一索引，重复值较多建立联合索引
- 5.尽量使用前缀索
引如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度
- 6.限制索引数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间
- 7.删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
3索引管理操作
#创建主键索引 primary key
mysql> alter table student2 add primary key pri(name);
#创建普通索引 index
mysql> alter table student2 add index idx_name(name);
#创建唯一索引 unique key
mysql> alter table student2 add unique key uni_age(age);
#如何判断，某列是否可以创建唯一索引
count（列名）查看该列共有多少行
count(distinct(列名))去重查看行数是否有变化
如果行数相等则创建唯一索引，否则可以拫据情况创建联合索引。
mysql> select count(name) from student4;
+-------------+
| count(name) |
+-------------+
| 9 |
+-------------+
1 row in set (0.00 sec)
mysql> select count(distinct(name)) from student4;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
| 2 |
+-----------------------+
mysql> select count(name) from country;
+-------------+
| count(name) |
+-------------+
| 239 |
+-------------+
1 row in set (0.00 sec)
mysql> select count(distinct(name)) from country;
+-----------------------+
| count(distinct(name)) |
+-----------------------+
| 239 |
+-----------------------+
1 row in set (0.00 sec)
mysql> alter table country add unique key uni_name(name);
#查看索引
mysql> show index from student2;
mysql> desc student2;
mysql> show create table student2;
#删除索引
mysql> alter table student2 drop index idx_all;
mysql> alter table student2 drop index uni_age;
mysql> alter table city drop primary key;
前缀索引
给表中数据量大的列，创建前缀索引
#创建前缀索引
mysql> alter table student2 add index idx_name(name(3));
说明：
1.避免对大列建索引
2.如果必须有，就使用前缀索引（如果数据较多创建时间会很长，使用前缀索引减少时间并且增加了查询速率）
联合索引
性别
长相
身材
收入
年龄
爱好
...
id 名字 性别 长相 身材 身高 QQ 微信 手机 收入
mysql> create table xiangqin(id int,name varchar(10),gender enum('0','1'),face varchar(10),height int,weight int,salary int,hobby varchar(10),QQ varchar(11));
mysql> alter table xiangqin add index idx_all(gender,salary,face,weight);
#联合索引，走索引情况
全部走索引:
select * from xiangqin where A;
select * from xiangqin where A B;
select * from xiangqin where A B C;
select * from xiangqin where A B C D;
#部分走索引：
select * from xiangqin where A C D;
select * from xiangqin where A B D;
select * from xiangqin where A D;
#全不走索引:
select * from xiangqin where B C D;
select * from xiangqin where C D;
select * from xiangqin where D;
4.索引优化
使用desc、explain 分析执行效率(优化SQL语句)，查看explain语句结果中的type对应的值。
命令格式：
explain sql语句/desc sql语句
1. 常见的索引扫描类型:
1）index
2）range
3）ref
4）eq_ref
5）const
6）system
7）null
说明：从上到下，性能从最差到最好，我们仍未至少要达到range级别。
index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。
eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A
const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
Extra（扩展)
Using temporary
Using filesort 使用了默认的文件排序（如果使用了索引，会避免这类排序）
mysql> explain select * from city where countrycode='CHN' order by population;
#解决方案
mysql> explain select * from city where population>30000000 order by population;
注意：
#查询结果中如下对应的值越小越好
row：越小越好
key_len:越小越好
使用前缀索引，控制key_len
2.优化
1.没有查询条件，或者查询条件没有建立索引
a)添加查询条件
b)为查询条件创建索引
2.查询结果集是全表的大部分数据，应该是25%以上不走索引
1）如果业务允许，可以使用limit控制。
2）结合业务判断，有没有更好的方式。如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面.
3.索引本身损坏（失效），不走索引
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。重建索引就可以解决
1）监控索引
2）删除索引，重新建立索引
4.使用函数在索引列上对索引做运算
5.隐式转换导致索引丢失
6.<>,not in 不走索引
7.like'%'百分号在前不走索引
8.单独应用联合索引非第一位的索引列不走索引
