概念:索引(Index)是帮助MySQL高效获取数据和排序的数据结构。
索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。
常见的索引的创建
- 普通索引: ALTER TABLE `table_name` ADD INDEX index_name(`field`);
- 唯一索引: ALTER TABLE `table_name` ADD UNIQUE (`field`);
- 主键索引---
- 组合索引: CREATE INDEX index_name On `table_name`(`field1`, `field2`, ...);
- 全文检索: ALTER TABLE table_name ADD FULLTEXT(`field1`, `field2`);
删除索引: DORP INDEX index_name ON `table_name`;
索引的优点:
- 主键索引和唯一索引可以保证列数据唯一;
- 提高查询效率;
- 表连接、分组、排序命中索引可极大缩短查询时间;
索引的缺点:
- 索引会在数据存储外额外消耗磁盘存储索引;
- 更新、新增数据需消耗额外的资源去更新索引;
使用索引注意事项:
- 最左原则:a、b、c 创建索引 相当于创建了3个: a ab abc;
- 表连接字段加索引可提高查询速度;
- 经常搜索的字段加普通索引提供查询效率;
- group by 字段 加索引可提高查询效率;
- order by 字段加索引需考虑是否配合使用limit 或者where 等 如果单独使用不命中;
- like 'a%' 会命中索引 如果是'%a%' 则不命中;
- null不会被索引,索引字段请设置为0或者'';
- 定长字段 比如char(32) 尽可能指定索引长度;
- mysql直接进行列运算不命中索引;
- 值很少的列不添加索引,比如性别、学历等;
- 写多读少的表不添加索引;
- enum、blob、text、时间格式等字段不添加索引;
检查索引命中:EXPLAIN
mysql> explain select * from test where name='test';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 1572863 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
查询结果每个字段的解释网上有很多。