MySQL索引相关问题总结

xx19941215 commented 6 years ago

1.简单描述MySQL中，索引，主键，唯一索引，联合索引的区别?

1.索引类似于书籍的目录。存储引擎中的索引也是如此，查询时会先去索引中找到对应的值，然后根据匹配的值找到对应的行。 2.主键指字段唯一可以唯一指定一行的字段。 3.唯一索引指索引列的值必须唯一，但是可以有空值。主键是唯一索引，但是唯一索引不是主键。 4.联合索引将多个键放在一块创建联合索引，可以覆盖多个列。

2.索引对数据库的影响？

1.大大减少服务器需要扫描的数据量 2.改变随机IO为顺序IO 3.帮助服务器避免排序和临时表

3.索引的类型有哪些？

1.组合索引，即上面的联合索引。 2.外键索引，只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级连操作。 3.全文索引，MySQL MyISAM引擎中支持，且只支持英文搜索。 4.Mysql 5.6版本之后InnoDB存储引擎开始支持全文索引，5.7版本之后通过使用ngram插件开始支持中文。

4.MySQL索引的注意事项？

1.联合索引遵循从前缀原则。例如下面的key(a,b,c) 会在SQL例如： Where a = 1 and b = 2 and c =3 Where a = 1 and b = 2 Where a = 1 Where a = 1 and c = 3

生效但是下面的SQL不会生效 Where b = 2 and c = 3

2.like查询%不能在前面，这样会全表搜索。 3.column is null 可以使用到索引。 4.如果or前的条件中的列有索引，后面的没有，索引也不会用 5.列类型是字符串，查询的时候一定要加引号，否则索引失效

5.如何优化MySQL查询？

分析查询速度慢的方法

1.记录慢查询日志。可以使用pt-query-digest工具分析。 2.使用show profile。 set profiling = 1;服务器上执行的所有语句会检测消耗时间，存放到临时表中。 3.使用show status show status 会返回一些计数器，show global status 查看服务器级别的所有计数器有些时候，可以根据这些计数猜测出那些操作代价较高或者消耗的时间多 4.使用show processlist 观察是否有大量的线程处于不正常的状态或者特征 5.使用explain或desc分析单条语句。

优化查询过程中的数据访问

1.访问数据太多导致查询性能低下 2.确定应用程序是否在检索大量超过需要的数据，可能是太多行或者太多的列 3.确认Mysql服务器是否存在分析大量不必要的数据行

避免使用如下的SQL语句

1.查询不需要的记录使用 limit

多表关联返回全部列，指定A.id A.name B.age 3.总是取出全部列 SELECT * 会让优化器无法完成索引覆盖扫描的优化 4.重复查询想用的数据，可以使用缓存数据。下次读取直接使用缓存 5.查看是否在扫描额外的记录使用explain的时候，如果发现查询需要扫描大量的数据但只是返回少量的行 k可以使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要获取对应行就可以把结果返回。 6.修改数据库和表结构，修改数据库符范式，以空间换时间 7.重写SQL语句。让优化器可以更加优化的方式执行查询

优化长难的查询语句

1.切分查询，将一个大的查询分为多个小的查询，一次性删除1000万的数据比一次性删除1万，暂停一会的方案更加损耗服务器开销。 2.分解关联查询，可以将一条管理语句分解成多条SQL语句来执行，让缓存的效率更高，执行单个查询可以，减少锁的竞争，在应用层做关联可以更加容易对数据库进行拆分。

优化特定类型的查询语句

1.优化count查询，count(*)中的*会直接忽略所有的列直接统计所有的列数，因此不要使用count(列名) MyISAM中，没有任何WHERE条件的count()非常快，当有了WHERE条件，MyISAM的count统计不一定比其他的表引擎快。可以使用explain查询近似值，用近似值代替count()。增加汇总表，增加缓存。