MYSQL数据库索引类型都有哪些

在满足语句需求的情况下，尽量少的访问资源是数据库设计的重要原则，这和执行的 SQL 有直接的关系，索引问题又是 SQL 问题中出现频率最高的，常见的索引问题包括：无索引（失效）、隐式转换。

1 SQL 执行流程看一个问题，在下面这个表 T 中，如果我要执行 select from T where k between 3 and 5; 需要执行几次树的搜索 *** 作，会扫描多少行？mysql> create table T (    -> ID int primary key,    -> k int NOT NULL DEFAULT 0,    -> s varchar(16) NOT NULL DEFAULT '',    -> index k(k))    -> engine=InnoDB;mysql> insert into T values(100,1, 'aa'),(200,2,'bb'),\      (300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

这分别是 ID 字段索引树、k 字段索引树。

这条 SQL 语句的执行流程：

1 在 k 索引树上找到 k=3，获得 ID=3002 回表到 ID 索引树查找 ID=300 的记录，对应 R33 在 k 索引树找到下一个值 k=5，ID=5004 再回到 ID 索引树找到对应 ID=500 的 R4

5 在 k 索引树去下一个值 k=6，不符合条件，循环结束

这个过程读取了 k 索引树的三条记录，回表了两次。因为查询结果所需要的数据只在主键索引上有，所以必须得回表。所以，我们该如何通过优化索引，来避免回表呢？

2 常见索引优化21 覆盖索引覆盖索引，换言之就是索引要覆盖我们的查询请求，无需回表。

如果执行的语句是 select ID from T wherek between 3 and 5;，这样的话因为 ID 的值在 k 索引树上，就不需要回表了。

覆盖索引可以减少树的搜索次数，显著提升查询性能，是常用的性能优化手段。

但是，维护索引是有代价的，所以在建立冗余索引来支持覆盖索引时要权衡利弊。

22 最左前缀原则

B+ 树的数据项是复合的数据结构，比如 (name,sex，age) 的时候，B+ 树是按照从左到右的顺序来建立搜索树的，当 (张三,F,26) 这样的数据来检索的时候，B+ 树会优先比较 name 来确定下一步的检索方向，如果 name 相同再依次比较 sex 和 age，最后得到检索的数据。

# 有这样一个表 P

mysql> create table P (id int primary key, name varchar(10) not null, sex varchar(1), age int, index tl(name,sex,age)) engine=IInnoDB;

mysql> insert into P values(1,'张三','F',26),(2,'张三','M',27),(3,'李四','F',28),(4,'乌兹','F',22),(5,'张三','M',21),(6,'王五','M',28);

# 下面的语句结果相同

mysql> select from P where name='张三' and sex='F';     ## A1

mysql> select from P where sex='F' and age=26;         ## A2

# explain 看一下

mysql> explain select from P where name='张三' and sex='F';

+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra       |

+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+

|  1 | SIMPLE      | P     | NULL       | ref  | tl            | tl   | 38      | const,const |    1 |   10000 | Using index |

+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+

mysql> explain select from P where sex='F' and age=26;

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

|  1 | SIMPLE      | P     | NULL       | index | NULL          | tl   | 43      | NULL |    6 |    1667 | Using where; Using index |

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

可以清楚的看到，A1 使用 tl 索引，A2 进行了全表扫描，虽然 A2 的两个条件都在 tl 索引中出现，但是没有使用到 name 列，不符合最左前缀原则，无法使用索引。所以在建立联合索引的时候，如何安排索引内的字段排序是关键。评估标准是索引的复用能力，因为支持最左前缀，所以当建立（a，b）这个联合索引之后，就不需要给 a 单独建立索引。原则上，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。上面这个例子中，如果查询条件里只有 b，就是没法利用（a，b）这个联合索引的，这时候就不得不维护另一个索引，也就是说要同时维护（a，b）、（b）两个索引。这样的话，就需要考虑空间占用了，比如，name 和 age 的联合索引，name 字段比 age 字段占用空间大，所以创建（name，age）联合索引和（age）索引占用空间是要小于（age，name）、(name）索引的。

23 索引下推

以人员表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是26岁的所有男性”。那么，SQL 语句是这么写的mysql> select from tuser where name like '张%' and age=26 and sex=M;

通过最左前缀索引规则，会找到 ID1，然后需要判断其他条件是否满足在 MySQL 56 之前，只能从 ID1 开始一个个回表。到主键索引上找出数据行，再对比字段值。而 MySQL 56 引入的索引下推优化(index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。这样，减少了回表次数和之后再次过滤的工作量，明显提高检索速度。

24 隐式类型转化

隐式类型转化主要原因是，表结构中指定的数据类型与传入的数据类型不同，导致索引无法使用。所以有两种方案：修改表结构，修改字段数据类型。

修改应用，将应用中传入的字符类型改为与表结构相同类型。

3 为什么会选错索引31 优化器选择索引是优化器的工作，其目的是找到一个最优的执行方案，用最小的代价去执行语句。在数据库中，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

32 扫描行数

MySQL 在真正开始执行语句之前，并不能精确的知道满足这个条件的记录有多少条，只能通过索引的区分度来判断。显然，一个索引上不同的值越多，索引的区分度就越好，而一个索引上不同值的个数我们称为“基数”，也就是说，这个基数越大，索引的区分度越好。# 通过 show index 方法，查看索引的基数mysql> show index from t;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| t     |          0 | PRIMARY  |            1 | id          | A         |       95636 |     NULL | NULL   |      | BTREE      |         |               || t     |          1 | a        |            1 | a           | A         |       96436 |     NULL | NULL   | YES  | BTREE      |         |               || t     |          1 | b        |            1 | b           | A         |       96436 |     NULL | NULL   | YES  | BTREE      |         |               |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

MySQL 使用采样统计方法来估算基数：采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

on 表示统计信息会持久化存储。默认 N = 20，M = 10。

off 表示统计信息只存储在内存中。默认 N = 8，M = 16。

由于是采样统计，所以不管 N 是 20 还是 8，这个基数都很容易不准确。所以，冤有头债有主，MySQL 选错索引，还得归咎到没能准确地判断出扫描行数。

可以用 analyze table 来重新统计索引信息，进行修正。

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]

33 索引选择异常和处理1 采用 force index 强行选择一个索引。2 可以考虑修改语句，引导 MySQL 使用我们期望的索引。3 有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

数据库优化，首先最初硬件方面就可以优化硬盘IO，内存分配，就是安装时候调整的一系列 *** 作系统级的内核参数，之后就是数据库架构上的优化了，逻辑、数据结构等等，最后就是代码上的优化。当然优化是一个长期的工作，没有最优只有更优。

1 保证在实现功能的基础上，尽量减少对数据库的访问次数；通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担；能够分开的 *** 作尽量分开处理，提高每次的响应速度；、使用SQL时，尽量把使用的索引放在选择的首列；算法的结构尽量简单；在查询时，不要过多地使用通配符，而且要用到几列就选择几列，如：

SELECT C1,C2 FROM T1；

在可能的情况下尽量限制尽量结果集行数，如：

SELECT TOP 300 C1,C2FROM T1,因为某些情况下用户是不需要那么多的数据的, 避免用!=或<> ISNULL或IS NOT NULL、IN ，NOT IN等这样的 *** 作符,因为这会使系统无法使用索引,而只能直接搜索表中的数据。例如:

SELECT C1 FROM T1 WHERE C1 != 'B%'

2 合理使用EXISTS,NOT EXISTS子句。如下所示：

1)SELECT SUM(T1C1)FROM T1 WHERE((SELECTCOUNT(1)FROM T2 WHERE T2C2=T1C2)>0)

2)SELECT SUM(T1C1) FROM T1　WHERE EXISTS( SELECT 1 FROM T2 WHERET2C2=T1C2)两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。如果你想校验表里是否存在某条纪录，不要用count()那样效率很低，而且浪费服务器资源。可以用EXISTS代替。如：

IF (SELECT COUNT(1) FROM table_name WHEREcolumn_name = 'xxx')>0

可以写成：

IF EXISTS (SELECT 1 FROM table_name WHEREcolumn_name = 'xxx')

3 经常需要写一个T_SQL语句比较一个父结果集和子结果集，从而找到是否存在在父结果集中有而在子结果集中没有的记录，如：

1) SELECTaC1 FROM T1 a

WHERE NOT EXISTS (SELECT 1 FROM T2 b WHERE aC1= bC1)

2) SELECT aC1 FROM T1 a

LEFT JOIN T2 b ON aC1 = bC1 WHERE bC1IS NULL

3) SELECT aC1 FROM T1 a

WHERE aC1 NOT IN (SELECT C1 FROM T2)

三种写法都可以得到同样正确的结果，但是效率依次降低。

4 能够用BETWEEN的就不要用IN

SELECT FROM T1 WHERE ID IN (10,11,12,13,14)

改成：

SELECT FROM T1 WHERE ID BETWEEN 10 AND 14

因为IN会使系统无法使用索引,而只能直接搜索表中的数据。

一、使用不同：

主键索引是在创建主键时一起创建的，是基于主键约束而建立的，是不可以为空，也不可以重复。

唯一索是引基于唯一约束而建立的，可以为空不可以重复，主键索引本身就具备了唯一索引的功能。

二、作用不同：

唯一索引的作用跟主键的作用一样。不同的是，在一张表里面只能有一个主键，主键不能为空，唯一索引可以有多个，唯一索引可以有一条记录为空，即保证跟别人不一样就行。

比如学生表，在学校里面一般用学号做主键，身份z则弄成唯一索引；而到了教育局，他们就把身份z号弄成主键，学号换成了唯一索引。

三、定义不同：

普通索引：这是最基本的索引类型，而且它没有唯一性之类的限制。

唯一性索引：这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。

扩展资料：

要对一个表建立唯一索引，可以使用关键字UNIQUE。对聚簇索引和非聚簇索引都可以使用这个关键字。

例子

CREATE UNIQUE CLUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

其中：CLUSTERED INDEX是用来建立聚簇索引的关键字，此语句的意思是在表mytable上的mycolumn字段上创建一个名为myclumn_cindex的聚簇索引，且为唯一索引。

参考资料来源：百度百科-唯一索引

商品评论系统数据量为十亿量级，因此对评论数据库做分库分表，单表的评论数据在百万级别。

每个商品的所有评论都是放在一个库的一张表里，确保作为用户在分页查询一个商品的评论时，一般都是直接从一个库的一张表里执行分页查询语句即可。

热门商品销量多达上百万，商品评论可能多达几十万条。有些用户就喜欢看商品评论，他就喜欢不停对某个热门商品评论不断进行分页，一页一页翻，有时候还会用上分页跳转功能，就是直接输入自己要跳到第几页。

这就涉及针对一个商品几十万评论的深分页问题。

简化后的对评论表进行分页查询的SQL：

比如用户选择了查看某个商品的评论，因此必须限定 Product_id ，同时还选了只看好评，所以 is_good_commit 也要限定，

接着看第5001页评论，则limit的offset=(5001 - 1) 20，20是每页的数量， 此时起始offset就是100000，所以limit后100000,20。

评论表最核心的索引 index_product_id ，所以正常肯定走这索引：

该过程有几十万次回表查询，还有十多万条数据的磁盘文件排序，所以要跑个1~2s。如何优化呢？

但本案例不是这样，因为

这俩条件不是一个联合索引，所以会出现大量回表，耗时严重。

因此对该案例，一般采取如下方式改造分页查询语句：

该SQL的执行计划就会彻底改变其执行方式。

通常先执行括号里的子查询，子查询反而会使用PRIMARY聚簇索引，按聚簇索引id值的倒序方向进行扫描，扫描过程中就把符合

的数据筛选出来。

比如这里筛选出10w条数据，并不需要把符合条件的数据都找到，因为limit 100000,20，理论上，只要有100000+20条符合条件的数据，且按id有序的，此时就能执行根据limit 100000,20提取到5001页的这20条数据。

接着你会看到执行计划里会针对这个子查询的结果集，一个临时表，进行全表扫描，拿到20条数据，再对20条数据遍历，每条数据都按id去聚簇索引查找一下完整数据。

所以本案例，反而是优化成这种方式来执行分页，更合适，他只有一个扫描聚簇索引筛选符合你分页所有数据的成本：

然后再做一页20条数据的20次回表查询即可。当时做了该分页优化后，发现分页语句一下子执行时间降低到了几百ms，达到优化目的。

SQL调优没有银d：

不同场景，要具体情况具体分析，到底慢在哪儿，再针对性优化。

以上就是关于MYSQL数据库索引类型都有哪些全部的内容，包括:MYSQL数据库索引类型都有哪些、数据库优化是什么意思、MSSQL数据库如何优化等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！