建立索引的原则
1.如果可以建立唯一键索引，就建立唯一键索引
2.为经常需要排序、分组和联合操作的字段建立索引
3.为常作为查询条件的字段建立索引
4.尽量使用前缀索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度
5.限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
6.删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
不走索引的情况总结
1.没有查询条件，或者查询条件没有索引
|
1 2 3 4 5 6 |
#查询所有数据 mysql> explain select * from city; #删除索引，然后查询 mysql> alter table city drop index District_key; mysql> explain select * from city where District='heilongjiang'; |
2.查询结果集是原表中的大部分数据，应该是15％以上
|
1 2 3 4 5 |
#表中数据一共4079，查询数据539条，走索引 13.2% mysql> explain select * from city where population > 500000; #表中数据一共4079，查询数据737条，不走索引 18% mysql> explain select * from city where population > 400000; |
3.索引坏了
|
1 |
反复插入删除容易损坏索引 |
4.查询条件使用了运算符号
|
1 2 3 4 5 |
#运算符号如果在等号左边，则不走索引 mysql> explain select * from city where id-1=2; #运算符号如果在等号右边，则走索引 mysql> explain select * from city where id=2+1; |
5.隐式转换
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
# 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 2 3 4 5 6 7 8 9 |
#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.联合索引，插叙条件不包含建立联合索引排第一的字段时
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
#1.查看联合索引 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 | +-------+------------+-----------+--------------+-------------+ #2.只要包含排第一的字段条件，就走索引 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'; #3.不包含建立联合索引排第一的字段时，不走索引 mysql> explain select * from user where age='30' and money='100000000' and look='beautiful'; |
8. <> ，not in 不走索引
|
1 2 3 |
mysql> explain select * from phone where number not in (110); mysql> explain select * from phone where number <> '110'; |