什么是索引?
索引是一种能帮助 MySQL 提高查询效率的数据结构。
索引分别有哪些优点和缺点?
索引的优点如下:
- 快速访问数据表中的特定信息,提高检索速度。
- 创建唯一性索引,保证数据表中每一行数据的唯一性。
- 加速表与表之间的连接。
- 使用分组和排序进行数据检索时,可以显著减少查询中分组和排序的时间。
索引的缺点:
- 虽然提高了的查询速度,但却降低了更新表的速度,比如 update、insert,因为更新数据时,MySQL 不仅要更新数据,还要更新索引文件;
- 建立索引会占用磁盘文件的索引文件。
使用索引注意事项:
- 使用短索引,短索引不仅可以提高查询速度,更能节省磁盘空间和 I/O 操作;
- 索引列排序,MySQL 查询只使用一个索引,因此如果 where 子句中已经使用了索引的话,那么 order by 中的列是不会使用索引的,因此数据库默认排序可以符合要求的情况下,不要进行排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引;
- like 语句操作,一般情况下不鼓励使用 like 操作,如果非使用不可, 注意 like “%aaa%” 不会使用索引,而like "aaa%"可以使用索引;
- 不要在列上进行运算;
- 不适用 NOT IN 和 <> 操作。
以下 SQL 有什么问题?该如何优化?
select * from t where f/2=100;
该 SQL 会导致引擎放弃索引而全表扫描,尽量避免在索引列上计算。可改为:
select * from t where f=100*2;
为什么 MySQL 官方建议使用自增主键作为表的主键?
因为自增主键是连续的,在插入过程中尽量减少页分裂,即使要进行页分裂,也只会分裂很少一部分;并且自增主键也能减少数据的移动,每次插入都是插入到最后,所以自增主键作为表的主键,对于表的操作来说性能是最高的。
自增主键有哪些优缺点?
优点:
- 数据存储空间很小;
- 性能最好;
减少页分裂。
缺点:
- 数据量过大,可能会超出自增长取值范围;
- 无法满足分布式存储,分库分表的情况下无法合并表;
- 主键有自增规律,容易被破解;
综上所述:是否需要使用自增主键,需要根据自己的业务场景来设计。如果是单表单库,则优先考虑自增主键,如果是分布式存储,分库分表,则需要考虑数据合并的业务场景来做数据库设计方案。
索引有几种类型?分别如何创建?
MySQL 的索引有两种分类方式:逻辑分类和物理分类。 按照逻辑分类,索引可分为:
- 主键索引:一张表只能有一个主键索引,不允许重复、不允许为 NULL;
- 唯-一索引:数据列不允许重复,允许为 NULL 值,一张表可有多个唯一索引,但是一个唯一索- - - 引只能包含一列,比如身份证号码、卡号等都可以作为唯一索引;
- 普通索引:一张表可以创建多个普通索引,一个普通索引可以包含多个字段,允许数据重复,允许 NULL 值插入;
- 全文索引:让搜索关键词更高效的一种索引。
按照物理分类,索引可分为:
-
聚集索引:一般是表中的主键索引,如果表中没有显示指定主键,则会选择表中的第一个不允许为 NULL 的唯一索引,如果还是没有的话,就采用 Innodb 存储引擎为每行数据内置的 6 字节 ROWID 作为聚集索引。每张表只有一个聚集索引,因为聚集索引的键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引在精确查找和范围查找方面有良好的性能表现(相比于普通索引和全表扫描),聚集索引就显得弥足珍贵,聚集索引选择还是要慎重的(一般不会让没有语义的自增 id 充当聚集索引);
-
非聚集索引