关于mysql:Mysql索引优化

1 索引的类型

UNIQUE 惟一索引

不能够呈现雷同的值，能够有 NULL 值。

INDEX 一般索引

容许呈现雷同的索引内容。

PRIMARY KEY 主键索引

不容许呈现雷同的值，且不能为 NULL 值，一个表只能有一个 primary_key 索引。

fulltext index 全文索引

上述三种索引都是针对列的值发挥作用，但全文索引，能够针对值中的某个单词，比方一篇文章中的某个词，然而并没有什么卵用，因为只有 myisam 以及英文反对，并且效率让人不敢恭维，然而能够用 coreseek 和 xunsearch 等第三方利用来实现这个需要。

2 索引的 CURD

索引的创立

ALTER TABLE

实用于表创立结束之后再增加。

ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)索引名

ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名，可要可不要；如果不要，以后的索引名就是该字段名。ALTER TABLE `table_name` ADD UNIQUE (`column_list`) 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`) 
ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`)

CREATE INDEX

CREATE INDEX 可对表减少一般索引或 UNIQUE 索引。

-- 例：只能增加这两种索引 
CREATE INDEX index_name ON table_name (column_list) 
CREATE UNIQUE INDEX index_name ON table_name (column_list)

另外，还能够在建表时增加：

CREATE TABLE `test1` (`id` smallint(5) UNSIGNED AUTO_INCREMENT NOT NULL, -- 留神，上面创立了主键索引，这里就不必创立了 
  `username` varchar(64) NOT NULL COMMENT '用户名', 
  `nickname` varchar(50) NOT NULL COMMENT '昵称 / 姓名', 
  `intro` text, 
  PRIMARY KEY (`id`),  
  UNIQUE KEY `unique1` (`username`), -- 索引名称，可要可不要，不要就是和列名一样 
  KEY `index1` (`nickname`), 
  FULLTEXT KEY `intro` (`intro`) 
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='后盾用户表';

索引的删除

DROP INDEX `index_name` ON `talbe_name`  
ALTER TABLE `table_name` DROP INDEX `index_name` 
-- 这两句都是等价的, 都是删除掉 table_name 中的索引 index_name; 

ALTER TABLE `table_name` DROP PRIMARY KEY -- 删除主键索引，留神主键索引只能用这种形式删除

索引的查看

show index from tablename;

索引的更改

更改个毛线, 删掉重建一个既可

3 创立索引的技巧

• 维度高的列创立索引。
• 数据列中 不反复值 呈现的个数，这个数量越高，维度就越高。
• 如数据表中存在 8 行数据 a,b ,c,d,a,b,c,d 这个表的维度为 4。
• 要为维度高的列创立索引，如性别和年龄，那年龄的维度就高于性别。

性别这样的列不适宜创立索引，因为维度过低。

• 对 where,on,group by,order by 中呈现的列应用索引。
• 对较小的数据列应用索引，这样会使索引文件更小，同时内存中也能够装载更多的索引键。
• 为较长的字符串应用前缀索引。
• 不要过多创立索引，除了减少额定的磁盘空间外，对于 DML 操作的速度影响很大，因为其每增删改一次就得从新建设索引。
• 应用组合索引，能够缩小文件索引大小，在应用时速度要优于多个单列索引。

4 组合索引与前缀索引

留神，这两种称说是对建设索引技巧的一种称说，并非索引的类型。

组合索引

MySQL 单列索引和组合索引到底有何区别呢？

为了形象地比照两者，先建一个表：

CREATE TABLE `myIndex` ( 
  `i_testID` INT NOT NULL AUTO_INCREMENT,  
  `vc_Name` VARCHAR(50) NOT NULL,  
  `vc_City` VARCHAR(50) NOT NULL,  
  `i_Age` INT NOT NULL,  
  `i_SchoolID` INT NOT NULL,  
  PRIMARY KEY (`i_testID`)  
);

假如表内已有 1000 条数据，在这 10000 条记录外面 7 上 8 下地散布了 5 条 vc_Name=”erquan”的记录，只不过 city,age,school 的组合各不相同。来看这条 T-SQL：

SELECT `i_testID` FROM `myIndex` WHERE `vc_Name`='erquan' AND `vc_City`='郑州' AND `i_Age`=25; -- 关联搜寻;

首先思考建 MySQL 单列索引：

在 vc_Name 列上建设了索引。执行 T-SQL 时，MYSQL 很快将指标锁定在了 vc_Name=erquan 的 5 条记录上，取出来放到一两头后果集。在这个后果集里，先排除掉 vc_City 不等于”郑州”的记录，再排除 i_Age 不等于 25 的记录，最初筛选出惟一的符合条件的记录。尽管在 vc_Name 上建设了索引，查问时 MYSQL 不必扫描整张表，效率有所提高，但离咱们的要求还有肯定的间隔。同样的, 在 vc_City 和 i_Age 别离建设的 MySQL 单列索引的效率类似。

为了进一步榨取 MySQL 的效率，就要思考建设组合索引。就是将 vc_Name,vc_City,i_Age 建到一个索引里：

ALTER TABLE `myIndex` ADD INDEX `name_city_age` (vc_Name(10),vc_City,i_Age);

建表时，vc_Name 长度为 50，这里为什么用 10 呢？这就是下文要说到的前缀索引，因为个别状况下名字的长度不会超过 10，这样会减速索引查问速度，还会缩小索引文件的大小，进步 INSERT 的更新速度。

执行 T-SQL 时，MySQL 毋庸扫描任何记录就到找到惟一的记录！

如果别离在 vc_Name,vc_City,i_Age 上建设单列索引，让该表有 3 个单列索引，查问时和上述的组合索引效率一样吗？答案是大不一样，远远低于咱们的组合索引。尽管此时有了三个索引，但 MySQL 只能用到其中的那个它认为仿佛是最有效率的单列索引，另外两个是用不到的，也就是说还是一个全表扫描的过程。

建设这样的组合索引，其实是相当于别离建设了：

• vc_Name,vc_City,i_Age
• vc_Name,vc_City
• vc_Name

这样的三个组合索引！为什么没有 vc_City,i_Age 等这样的组合索引呢？这是因为 mysql 组合索引“最左前缀”的后果。简略的了解就是只从最右面的开始组合。并不是只有蕴含这三列的查问都会用到该组合索引，上面的几个 T-SQL 会用到：

SELECT * FROM myIndex WHREE vc_Name=”erquan”AND vc_City=”郑州”SELECT * FROM myIndex WHREE vc_Name=”erquan”

而上面几个则不会用到：

SELECT * FROM myIndex WHREE i_Age=20 AND vc_City=”郑州”SELECT * FROM myIndex WHREE vc_City=”郑州”

也就是，name_city_age(vc_Name(10),vc_City,i_Age) 从左到右进行索引，如果没有左前索引 Mysql 不执行索引查问。

前缀索引

如果索引列长度过长，这种列索引时将会产生很大的索引文件，不便于操作，能够应用前缀索引形式进行索引前缀索引应该管制在一个适合的点，管制在 0.31 黄金值即可（大于这个值就能够创立）。

SELECT COUNT(DISTINCT(LEFT(title,10)))/COUNT(*) FROM Arctic; — 这个值大于 0.31 就能够创立前缀索引，Distinct 去反复 ALTER TABLE user ADD INDEX uname(title(10)); — 减少前缀索引 SQL，将人名的索引建设在 10，这样能够缩小索引文件大小，放慢索引查问速度。

5 什么样的 sql 不走索引

要尽量避免这些不走索引的 sql

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会应用索引，因为所有索引列参加了计算 

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会应用索引，因为应用了函数运算，原理与下面雷同 

SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾 %' -- 走索引 

SELECT * FROM `houdunwang` WHERE `uname` LIKE "% 后盾 %" -- 不走索引 

-- 正则表达式不应用索引，这应该很好了解，所以为什么在 SQL 中很难看到 regexp 关键字的起因 

-- 字符串与数字比拟不应用索引; 
CREATE TABLE `a` (`a` char(10)); 
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引 
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引 

select * from dept where dname='xxx' or loc='xx' or deptno=45 -- 如果条件中有 or，即便其中有条件带索引也不会应用。换言之，就是要求应用的所有字段，都必须建设索引，咱们倡议大家尽量避免应用 or 关键字 

-- 如果 mysql 预计应用全表扫描要比应用索引快，则不应用索引

多表关联时的索引效率

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; — 不会应用索引，因为应用了函数运算，原理与下面雷同
SELECT * FROM `houdunwang` WHERE `uname` LIKE’后盾 %’— 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE“% 后盾 %”— 不走索引

从上图能够看出，所有表的 type 为 all，示意全表索引。也就是 6 6 6，共遍历查问了 216 次。

除第一张示意全表索引（必须的，要以此关联其余表），其余的为 range（索引区间取得），也就是 6 +1+1+1，共遍历查问 9 次即可。

所以咱们倡议在多表 join 的时候尽量少 join 几张表，因为一不小心就是一个笛卡尔乘积的恐怖扫描，另外，咱们还倡议尽量应用 left join，以少关联多。因为应用 join 的话，第一张表是必须的全扫描的，以少关联多就能够缩小这个扫描次数。

6 索引的弊病

不要自觉的创立索引，只为查问操作频繁的列创立索引，创立索引会使查问操作变得更加疾速，然而会升高减少、删除、更新操作的速度，因为执行这些操作的同时会对索引文件进行从新排序或更新。

然而，在互联网利用中，查问的语句远远大于 DML 的语句，甚至能够占到 80%~90%，所以也不要太在意，只是在大数据导入时，能够先删除索引，再批量插入数据，最初再增加索引。