索引的建立与不走索引的情况总结

1.建立索引的原则

1.如果可以建立唯一键索引，就建立唯一键索引
2.为经常需要排序、分组和联合操作的字段建立索引
3.为常作为查询条件的字段建立索引

4.尽量使用前缀索引
    如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度

5.限制索引的数目
    索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

6.删除不再使用或者很少使用的索引
    表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

2.不走索引的情况总结

1）没有查询条件，或者查询条件没有索引

#查询所有数据
mysql> explain select * from city;

#删除索引，然后查询
mysql> alter table city drop index District_key;
mysql> explain select * from city where District='heilongjiang';

2）查询结果集是原表中的大部分数据，应该是15％以上

#表中数据一共4079，查询数据539条，走索引    13.2%
mysql> explain select * from city where population > 500000;

#表中数据一共4079，查询数据737条，不走索引    18%
mysql> explain select * from city where population > 400000;

3）索引坏了

反复插入删除容易损坏索引

4）查询条件使用了运算符号

#运算符号如果在等号左边，则不走索引
mysql> explain select * from city where id-1=2;

#运算符号如果在等号右边，则走索引
mysql> explain select * from city where id=2+1;

5）隐式转换

# 1.建表
mysql> create table phone(id int,name varchar(10),number varchar(20));

#2.建立索引
mysql> alter table phone add unique key uni_key(number);

#3.插入数据
mysql> insert phone values(1,'警察局',110),(2,'消防',119),(3,'医院',120);

#4.测试查询数据是否走索引
1）不走索引
mysql> explain select * from phone where number=120;
2）走索引
mysql> explain select * from phone where number='120';
#因为120存到数据库中的字段是字符类型，那么查询时字符类型必须加引号

6）使用 like + % 的模糊匹配，当条件以%开头时

#1. % 在最前面时不走索引
mysql> explain select * from city where countrycode like '%H';
mysql> explain select * from city where countrycode like '%H%';

#2. % 在后面时走索引
mysql> explain select * from city where countrycode like 'H%';

#3. % 在中间时也走索引
mysql> select * from city where countrycode like 'C%N';

7）联合索引，插叙条件不包含建立联合索引排第一的字段时

#0.查看联合索引
mysql> show index from user;
+-------+------------+-----------+--------------+-------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name |
+-------+------------+-----------+--------------+-------------+
| user  |          1 | index_all |            1 | sex         |
| user  |          1 | index_all |            2 | age         |
| user  |          1 | index_all |            3 | money       |
| user  |          1 | index_all |            4 | look        |
+-------+------------+-----------+--------------+-------------+

#1.只要包含排第一的字段条件，就走索引
mysql> select * from user where sex='fmale' and age='30';
mysql> explain select * from user where age='30' and money='100000000' and look='beautiful' and sex='fmale';

#2.不包含建立联合索引排第一的字段时，不走索引
mysql> explain select * from user where age='30' and money='100000000' and look='beautiful';

8） <> ，not in 不走索引

mysql> explain select * from phone where number not in (110);

mysql> explain select * from phone where number <> '110';