#查询所有数据
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 whereid=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';
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步