mysql 主键索引，联合索引，单列索引使用场景

表button 

CREATE TABLE `button` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,  --主键索引

  `button_name` varchar(45) NOT NULL COMMENT '功能名称',

  `app_id` bigint(20) NOT NULL,

  `permission_id` bigint(20) DEFAULT NULL,  -- permission_id 和 app_id 联合索引。

  `api_id` bigint(20) NOT NULL, --api_id单独索引

  PRIMARY KEY (`id`),

  KEY `index_app_permission_lianhe` (`permission_id`,`app_id`) USING BTREE,

  KEY `index_api_id_dange` (`api_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

主键索引，单独索引，组合索引使用场景及优化

表button 有3个索引，分别是：id主键，联合索引(permission_id,app_id)，api_id(单列索引)

button_name 无索引

查询where条件中：

主键索引：

1、主键索引与联合索引同时存在，使用主键索引

2、主键索引与单个索引同时存在，使用主键索引

结论：只要主键索引在，使用主键索引。

联合索引 ：

1、联合索引与单列索引列 同时存在，使用单列索引

2、联合索引中列顺序颠倒无影响。

3、联合索引实行最左侧原则，即：单独查询条件中只有permission_id可以使用联合索引，单独查询条件中只有app_id不实用联合索引。

4、如果查询条件中只有app_id，但是select 条件中有 permission_id，则也使用联合索引。

5、select id,app_id from button where app_id=1001使用联合索引

6、explain select id,app_id,button_name from button where app_id=1001不使用联合索引

结论：索引优先级：主键索引，单列索引，组合索引

    联合索引中遵从最左侧列原则。

    当查询条件和返回结果中仅仅包含联合索引中索引项，也使用联合索引。如第4条。

    当查询条件中出现联合索引中非最左侧索引列，返回结果中含义联合索引中的列或者主键则也使用联合索引。

单个索引：

1、查询条件中有单列索引，则使用，无不使用。

事例：

select (*) from tb_name where create_time >xxx

最终得知是因为这个表数据行数已经超过 一千万了，然后create_time字段又没有索引 。

那解决办法肯定是加索引喽。

但是这个表是一直在线上运行，很重要和业务部分。如果给千万级的大表在线加索引 ，肯定会卡死。

然后就搜罗了一大筐解决方案，比如 在线无锁加索引使用

ALTER TABLE tbl_name ADD PRIMARY (column), ALGORITHM=INPLACE, LOCK=NONE

后来才发现，这个特性是 Mysql 5.6 以后才支持，然而我们的mysql用的是5.5版本

最后在 《高性能Mysql》一书中看到，可在通过 “影子拷贝”来解决，

就是 先创建一张和源表无关的新表，然后通过重命名和删表 *** 作交换两张表；

当给新表加完索引后，最上面那条查询直接就是0.0002s

场景：在给一张有几万条记录的表添加索引时，进度非常慢，导致其它查询无法进行

处理方式：

使用Navicat的命令行模式，执行以下命令：

show processlist

这时会看到有哪些线程正在执行，也可以查看锁表的线程。你会发现alter table * add key ****那个线程状态是Waiting for table metadata lock，后面有个这个表的所有 *** 作都是这个状态，很明显是这条加索引的语句把表给锁了。

查看线程ID，执行

kill 线程ID

这样被锁住的表就能立即被使用了。

由此得出一个结论，当一张表数据量很大时，不要轻易添加索引，会导致表被锁死！如果非要添加，那么应该先把数据表进行备份，然后进行空表添加索引。

只能通过ALTER TABLE不能create index

参数说明：

一、普通索引

这是最基本的索引，它没有任何限制。有以下几种创建方式：

1.创建索引

代码如下:

CREATE INDEX indexName ON mytable(username(length))

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

2.修改表结构

代码如下:

ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 创建表的时候直接指定。

CREATE TABLE mytable( ID INT NOT NULL,username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) )

-- 删除索引的语法：

DROP INDEX [indexName] ON mytable

二、唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

代码如下:

CREATE UNIQUE INDEX indexName ON mytable(username(length))

-- 修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

-- 创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL,username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) )

三、主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

代码如下:

CREATE TABLE mytable( ID INT NOT NULL,username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) )

当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

四、组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：

代码如下:

CREATE TABLE mytable( ID INT NOT NULL,username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL )

为了进一步榨取MySQL的效率，就要考虑建立组合索引。

二：使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

1.索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

2.使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O *** 作。

3.索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序 *** 作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4.like语句 *** 作

一般情况下不鼓励使用like *** 作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

5.不要在列上进行运算

select * from users where YEAR(adddate)<2007

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:

select * from users where adddate<‘2007-01-01'

6.不使用NOT IN和<> *** 作。

三：sql优化原则

常见的简化规则如下：

1.不要有超过5个以上的表连接（JOIN）

2.考虑使用临时表或表变量存放中间结果。

3.少用子查询

4.视图嵌套不要过深,一般视图嵌套不要超过2个为宜。

5.连接的表越多，其编译的时间和连接的开销也越大，性能越不好控制。

6.最好是把连接拆开成较小的几个部分逐个顺序执行。

7.优先执行那些能够大量减少结果的连接。

8.拆分的好处不仅仅是减少SQL Server优化的时间，更使得SQL语句能够以你可以预测的方式和顺序执行。

如果一定需要连接很多表才能得到数据，那么很可能意味着设计上的缺陷。

---