mysql索引

在mysql中，索引是一种特殊的数据库结构，由数据表中的一列或多列组成。它可用于快速查询数据表中具有特定值的记录。

通过索引，查询数据时不必读取所有记录的信息，只需查询索引列即可。

通过索引，查询数据时不必读取所有记录的信息，只需查询索引列即可。否则，数据库系统将读取每条记录的所有信息进行匹配。

你可以把索引和《新华字典》的音标对照一下。比如查“ku”这个词，如果不用词序，就需要从字典的400页中一页一页地查。但是如果把拼音提取出来形成音标表，只需要直接从10多页的音标表中查找就可以了。这样可以大大节省时间。

因此，索引的使用可以大大提高数据库的查询速度，也可以有效地提高数据库系统的性能。

指数的利与弊

指数有其明显的优点，但也有其不可避免的缺点。

优势

索引的优点如下:

1.数据库表中每一行数据的唯一性可以通过创建唯一索引来保证。

2.您可以为所有MySQL列类型设置索引。

3.可以大大加快数据的查询速度，这是使用索引的主要原因。

4.在实现数据的引用完整性时，它可以加快表之间的连接。

5.使用分组和排序子句查询数据时，还可以显著减少查询中分组和排序的时间。

缺点

增加指数也有很多缺点，主要有以下几点:

1.创建和维护索引组需要时间，随着数据量的增加，花费的时间也会增加。

2.索引需要占用磁盘空空间。除了数据表占用data 空空间外，每个索引还占用一定的物理空空间。如果有大量索引，索引文件可能比数据文件更快达到最大文件大小。

3.在添加、删除、修改表中的数据时，索引也要动态维护，这样就降低了数据维护的速度。

在使用索引时，我们需要考虑索引的优缺点。

MySQL中如何设置唯一索引，联合索引？

1.UNIQUE　关键字建唯一索引

mysql> CREATE TABLE `wb_blog` (

-> `id` smallint(8) unsigned NOT NULL,

-> `catid` smallint(5) unsigned NOT NULL DEFAULT ‘0’,

-> `title` varchar(80) NOT NULL DEFAULT ”,

-> `content` text NOT NULL,

-> PRIMARY KEY (`id`),

-> UNIQUE KEY `catename` (`catid`)

-> ) ;

如果建好表了，可以用以下语句建

mysql> CREATE UNIQUE INDEX catename ON wb_blog(catid);

2.联合索引

ALTER TABLE `tasks`

ADD INDEX `testabc` (`title`, `created`) ;

3联合唯一索引（假设有这个需求，在同一天内不能建两个tiltle一样的任务）

ALTER TABLE `tasks`

ADD UNIQUE INDEX `testabc` (`title`, `created`) ;

数据库建索引的科学性事关数据库性能，索引也不是越多越好。

如何在mysql中设置唯一索引？

1.UNIQUE　关键字建唯一索引mysql>CREATETABLE`wb_blog`(->`id`smallint(8)unsignedNOTNULL,->`catid`smallint(5)unsignedNOTNULLDEFAULT’0′,->`title`varchar(80)NOTNULLDEFAULT”,->`content`textNOTNULL,->PRIMARYKEY(`id`),->UNIQUEKEY`catename`(`catid`)->);如果建好表了，可以用以下语句建mysql>CREATEUNIQUEINDEXcatenameONwb_blog(catid);2.联合索引ALTERTABLE`tasks`ADDINDEX`testabc`(`title`,`created`);3联合唯一索引（假设有这个需求，在同一天内不能建两个tiltle一样的任务）ALTERTABLE`tasks`ADDUNIQUEINDEX`testabc`(`title`,`created`);数据库建索引的科学性事关数据库性能，索引也不是越多越好。

mysql新增索引如何生效？

show index from `表名`; 或 show keys from `表名`; 然后看结果中的key_name是否包含你创建的索引名

mysql索引的使用顺序是怎样的？

0使用哪个索引是由where语名决定的 如果where语句写的不好，可能就不会走索引 索引适合从大数量里面取少量数据的情况

mysql多个索引怎么选择？

MySQL多索引查询选择

MySQL选择索引-引入

我们知道我们一个表里面可以有多个索引的，那么我们查询数据的时候不指定索引，MySQL就会帮我们自动选择。既然是MySQL程序帮我们自动选择的那么会不会有问题的呢？答案是会的，MySQL的优化器也有bug，有时候选择的索引并不是最优的。

案例1

假如一张表有10w的数据，有id主键和a，b普通索引,执行以下SQL

select * from t where a between 10000 and 20000;

select * from t force index(a) where a between 10000 and 20000;

在一定的前提下

执行第一句代码走的是全表查询，扫描了10w行

执行第二句，强制使用a索引，只扫描了10001行

为啥会出现这种情况呢？我们就从优化器的逻辑开始研究

优化器的逻辑

优化器优化判断的指标

有需要扫描的行数，是否使用临时表，是否排序等因素

扫描行数判断

上面的案例明显就是扫描行数的问题

那么优化器是怎么获取扫描的总行数的，其实就和抽样检查类似，因为索引是有序的，就可以使用采样统计这种算法算出大概的扫描行数，可以通过show index查看索引的Cardinality预估值。

案例分析

我们通过explain来查看案例的扫描行数的预估值

rows字段就是预计的扫描行数，可见第二个选择a索引查询的预估扫描行数存在比较大的偏差

问题？

根据结果我们发现走a索引就算是扫描3w7行，也还是比10w快啊，为啥还是选择了全表扫描，因为我们只考虑了扫描行数却没有考虑到回表这个操作，如果加上回表的一些操作那么优化器就会认为还不如走全表查询来的快，所以优化器选择了全表查询。

解决

我们知道问题出在了扫描行的预估不正确，要是出现预估和现实差别比较大的情况的就可以使用analyze table zx的命令来重新预估来改变。

案例2

还是上面的表数据的格式是(1,1,1)，10w条

select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;

explain

又又又选择错了

原因

为啥会选错呢，其实主要就是时排序的问题，优化器认为按索引a查询出来的数据b不是有序的，还要排序(其实是有序的)，所以它选择了b索引，查询出来的数据直接就是有序的，效率会更高

怎么避免这些错误选择索引呢

1.直接force index直接强制指定查询使用的索引

2.analyze table zx重新计算预估的扫描行

3.引导sql的索引选择，比如order by

4.合理设置索引