MySQL索引相关问题总结

[xx19941215]

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
2. 多表关联返回全部列，指定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()。增加汇总表，增加缓存。

[xmnsl]

第4条索引优化有错误，联合索引where a=1 and c=3,是会使用索引的。

[xx19941215]

@xmnsl 这个不会的 是遵循前缀匹配原则的

[xmnsl]

@xx19941215 我在本地进行了测试，发现索引是会被使用的，它遵循的是最左前缀原则，并不包括中间字段，作者有兴趣的话可以进行测试一下看看。

[xx19941215]

@xmnsl 你是为(a,c) 创建的联合索引吗 这样肯定是会用到的

[xmnsl]

@xx19941215 没有，三个字段（a,b,c）

[xx19941215]

@xmnsl 好的 我稍后会测试下

[xx19941215]

@xmnsl 您好 我测试了下 这样会使用部分索引 谢谢您的认真阅读 如果有任何其他问题 希望您及时指正

[pro911]

3.3 mysql5.6之后的版本支持InnoDB的全文索引

[xx19941215]

@CareyLoserUp 感谢您提供的信息 现在已经更正。