MySql 优化基本原理--索引篇（全面，实时更新）

最新推荐文章于 2022-04-24 18:44:04 发布

紫竹风凌雪

最新推荐文章于 2022-04-24 18:44:04 发布

阅读量296

点赞数

分类专栏： # MySql 文章标签： mysql Sql优化索引

本文链接：https://blog.csdn.net/qq_34065636/article/details/102820631

版权

MySql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySql 优化基本原理–索引篇（全面，实时更新）

本文章参考自：

学习MySQL优化原理，这一篇就够了！
MySql优化原理

根据平时积累的索引总结而出，如有错误或者需要添加的内容欢迎大家评论！

索引理解（稍微讲讲）

索引是什么？它是一种特殊的文件，包含着对数据表里所有记录的引用指针。通俗的说，数据库索引就像是一本书的目录部分，这能加快数据库的查询速度。在没有索引的情况下，数据库会遍历全部数据然后选择符合条件的；然而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。

1.聚簇索引

聚簇索引是按照数据存放的物理位置为顺序的，聚簇索引的顺序就是数据的物理存储顺序，索引的叶节点就是数据节点
表中记录的物理顺序与键值的索引顺序相同。一个表只能有一个聚集索引(简单理解)

2.非聚簇索引

非聚簇索引是索引顺序与数据物理排列顺序无关，叶节点仍然是索引节点，只不过有一个指针指向对应的数据块，所以一个表最多只能有一个聚簇索引

3.组合索引

MySQL可以在多个列上创建索引，执行查询时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的（获取结果集记录数量最少）的索引。例如，我们在为WHERE查询的列加上索引的话，查询会非常迅速。使用场景如下：

遵循最左前缀匹配原则；
匹配第一列范围查询（可以用like a%，但是不能用like %b）；
精确匹配某一列和范围匹配另外一列；4、匹配第一列，全字段匹配。
高性能索引策略：（参考菜鸟教程：索引优化全攻略：http://www.runoob.com/w3cnote/mysql-index.html）
索引列不能是函数的一部分或者是表达式的一部分；
索引列要有良好的选择性（不重复性），【select count（DISTINCT city）/COUNT(*) FROM city_demo ：计算区分度，表示字段不重复的比例。】
前缀索引会使索引变小，但是这会导致order by和group by失效；
前缀索引
二级索引的叶子节点保存的指向行主键值的指针；
插入性能依赖聚簇索引书序，所以聚簇索引通常设置为id自增；
覆盖索引
数据查询字段是索引字段时，可以使用覆盖索引，减少二次查询操作；
索引排序一样需要满足最左前缀原则；
举例说明：
数据表test_order有索引（a,b,c），字段a,b,c,d,e,f。’
可以用到索引举例：
select e,f from test_order where a='2014-1-1' order by b,c； //符合最左前缀原则
select e,f from test_order where a='2014-1-1' order by b; //符合最左前缀原则
select e,f from test_order where a>'2014-1-1' order by a,b;//符合最左前缀原则无法用到索引举例：
select e,f from test_order where a='2014-1-1' order by b,e; //b,e索引无序，无法使用索引
select e,f from test_order where a='2014-1-1' order by c;//不符合最左前缀原则
select e,f from test_order where a>'2014-1-1' order by b,c;//第一列为范围条件，无法使用其余列作为索引
select e,f from test_order where a='2014-1-1' and b in (1,2) order by c;//b列有多个等于条件，对于排序来说这是范围查询。

什么情况下需要索引？

数据在磁盘上是以块的形式存储的，为了确保对磁盘操作的原子性，访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表是类似的，它们都包含了一个数据段和指针，指针指向下一个节点（数据块）的内存地址，而且它们都不需要连续存储（即逻辑上相邻的数据块在物理存储上可以相隔很远）。

一、操作索引

一、在创建表的时候声明索引（此处key和index作用相同）
KEY `idx_passport_name` (`passport_name`),
KEY `idx_account_phone` (`account_phone`)
 
二、使用ALTER TABLE <tab_name> ADD INDEX方式增加索引
ALTER TABLE <tab_name> ADD (unique,primary key,fulltext,index)[索引名](字段名)
ALTER TABLE `my_user` ADD INDEX `inx_usr_id` (`usrid`); //普通索引，索引值可出现多次。
 
三、使用CREATE INDEX命令来创建索引
CREATE INDEX `index_usr_id` ON `my_user` (`usrid`);
 
四、删除索引
DROP INDEX `idx_usr_id` ON `my_user`;
 
五、查看索引   
SHOW INDEX FROM `my_user`;
SHOW KEYS FROM `my_user`;

应用索引应该注意事项（重点！）

索引遵循最左前缀匹配原则。mysql会从左至右匹配，遇到范围查询（例如，>，<，between、like）停止匹配，比如对于建立（a，b，c，d）的顺序索引，如果查询语句为a = 1 and b = 2 and c > 4 and d = 4，则d列索引没有应用；假设d列索引在c列之前，则可以应用。【 select e,f from test_order where a>‘2014-1-1’ order by b,c;//第一列为范围条件，无法使用其余列作为索引】
尽量使用区分度较高的列作为索引，区分度的公式为【 count(distinct column) / count(*) 】，用来表示字段不重复的比例，比例越大则代表扫描到的记录数越少，唯一键的区分度是1，而一些状态、性别等字段在大数据量下则无限趋近于0，所以类似性别字段不宜作为区分度的索引列。
尽量扩展索引列而不是新建索引列（这会增加系统负担）。比如，表中已经有了a列的索引，现在需要加入b列的索引，则需要修改原来的索引即可。

Alter Table tableA Add Index Index_Name(column1,column2,column3)

对于经常存取的列避免建立索引。
索引一般建立在JOIN、WHERE、ORDER BY排序的字段上，避免在含有大量重复值的字段建立索引。
避免在where之后对字段进行null的判断

select * from tableA where userName is null

这会导致引擎放弃使用索引而进行全表扫描。在设计数据库表的时候，应尽量使用NOT NULL来填充数据库。注意：不要以为NULL不会占用存储空间，比如CHAR（100），在字段建立时，空间就是固定了，都会占用100个字符空间。而varchar（100）这样的变长字段，null是不占用空间的。

所有的“非”类型的判断不会走索引【 not , not in, not like, <>, != ,!>,!< 】。
避免在where中使用or来连接条件。万一一个字段有索引，而另外一个没有索引，则将导致放弃索引进行全表扫描。where 语句里面如果带有or条件, myisam表能用到索引，innodb不行大多数数据库都是innodb形式，以及后来的TiDB,所以尽量不要使用or，可以使用 in 或者 union
可以使用语句代替：

/*不走索引*/
select id from tab where num = 10 or Nme = "***"
/*上述语句替换为*/
select id from tab where num = 10
union all
select id from tab where Nme = "***"
/*默认情况下，UNION操作会选取不同的值，而UNION ALL操作可以允许重复值出现*/

like %abc%的模糊查询：
如果现实业务需要实现相关操作，可以参考Like 影响索引的解决方案

//走索引 
select * from `myuser` where usrname like keyword%;
//索引失效，也无法使用反向索引 
select * from `myuser` where usrname like %keyword%  
// 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。
select * from `myuser` where usrname like %keyword

避免在where字段中进行表达式操作（如，num/2、substring(name,1,3)、num=@num），这会导致放弃索引直接进行全表扫描。尽量不要在“=”左边进行函数、算数运算符或其他表达式操作。

//不走索引
select * from TableA where lower(UserID) ='zhangsan'

索引可以提高select查询效率，但是降低了insert及update的效率，因为insert或者update可能重建索引，所以建立索引需要慎重。
MySQL查询只使用一个索引，如果where字段已经使用了索引的话，则order by中不会使用索引。
MySQL不会使用索引的情况：非独立的列
“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。比如：
select * from where id + 1 = 5
我们很容易看出其等价于 id = 4，但是MySQL无法自动解析这个表达式，使用函数是同样的道理。
前缀索引：如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率。
避免多个范围条件
实际开发中，我们会经常使用多个范围条件，比如想查询某个时间段内登录过的用户：

select user.* from user where login_time > '2017-04-01' and age between 18 and 30;

这个查询有一个问题：它有两个范围条件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但无法同时使用它们。

覆盖索引
如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处：
索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量
索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多
使用索引扫描来排序：
MySQL有两种方式可以生产有序的结果集，其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的。如果explain的结果中type列的值为index表示使用了索引扫描来做排序。
扫描索引本身很快，因为只需要从一条索引记录移动到相邻的下一条记录。但如果索引本身不能覆盖所有需要查询的列，那么就不得不每扫描一条索引记录就回表查询一次对应的行。这个读取操作基本上是随机I/O，因此按照索引顺序读取数据的速度通常要比顺序地全表扫描要慢。
在设计索引时，如果一个索引既能够满足排序，又满足查询，是最好的。
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向也一样时，才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有ORDER BY子句引用的字段全部为第一张表时，才能使用索引做排序。ORDER BY子句和查询的限制是一样的，都要满足最左前缀的要求（有一种情况例外，就是最左的列被指定为常数，下面是一个简单的示例），其它情况下都需要执行排序操作，而无法利用索引排序。
// 最左列为常数，索引：(date,staff_id,customer_id)

select  staff_id,customer_id from demo where date = '2015-06-01' order by staff_id,customer_id

冗余和重复索引：冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应当尽量避免这种索引，发现后立即删除。比如有一个索引(A,B)，再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时，比如有人新建了索引(A,B)，但这个索引不是扩展已有的索引(A)。大多数情况下都应该尽量扩展已有的索引而不是创建新索引。但有极少情况下出现性能方面的考虑需要冗余索引，比如扩展已有索引而导致其变得过大，从而影响到其他使用该索引的查询。
删除长期未使用的索引：定期删除一些长时间未使用过的索引是一个非常好的习惯。