December 6, 2018

MySQL：mysql索引优化, mysql全文检索, mysql 不等于优化, 全值匹配, 最佳左前缀法则

By Gideon Php / Pear / Mysql / Node.js 0 Comments

Explain优化查询检测

所谓索引就是为特定的mysql字段进行一些特定的算法排序,比如二叉树的算法和哈希算法,哈希算法是通过建立特征值,然后根据特征值来快速查找,而用的最多,并且是mysql默认的就是二叉树算法 BTREE,通过BTREE算法建立索引的字段,比如扫描20行就能得到未使用BTREE前扫描了2^20行的结果,具体的实现方式后续本博客会出一个算法专题里面会有具体的分析讨论;

EXPLAIN可以帮助开发人员分析SQL问题,explain显示了mysql如何使用索引来处理select语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句.

使用方法,在select语句前加上Explain就可以了：

Explain select * from blog where false;

mysql在执行一条查询之前,会对发出的每条SQL进行分析,决定是否使用索引或全表扫描如果发送一条select * from blog where falseMysql是不会执行查询操作的,因为经过SQL分析器的分析后MySQL已经清楚不会有任何语句符合操作;

Example

mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2";
-- 结果：
id: 1

select_type: SIMPLE -- 查询类型（简单查询,联合查询,子查询）

table: user -- 显示这一行的数据是关于哪张表的

type: range -- 区间索引（在小于1990/2/2区间的数据),这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表扫描了全表才确定结果。一般来说,得保证查询至少达到range级别,最好能达到ref。

possible_keys: birthday  -- 指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。 

key: birthday -- 实际使用到的索引。如果为NULL,则没有使用索引。如果为primary的话,表示使用了主键。

key_len: 4 -- 最长的索引宽度。如果键是NULL,长度就是NULL。在不损失精确性的情况下,长度越短越好

ref: const -- 显示哪个字段或常数与key一起被使用。 

rows: 1 -- 这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。

Extra: Using where; Using index -- 执行状态说明,这里可以看到的坏的例子是Using temporary和Using

索引的类型

UNIQUE唯一索引

不可以出现相同的值,可以有NULL值

INDEX普通索引

允许出现相同的索引内容

PRIMARY KEY主键索引

不允许出现相同的值,且不能为NULL值,一个表只能有一个primary_key索引

fulltext index 全文索引

上述三种索引都是针对列的值发挥作用,但全文索引,可以针对值中的某个单词,比如一篇文章中的某个词,然而并没有什么卵用,因为只有myisam以及英文支持,并且效率让人不敢恭维,但是可以用coreseek和xunsearch等第三方应用来完成这个需求

索引的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 \G;

索引的更改

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

创建索引的技巧

1.维度高的列创建索引

数据列中不重复值出现的个数,这个数量越高,维度就越高
如数据表中存在8行数据a ,b ,c,d,a,b,c,d这个表的维度为4
要为维度高的列创建索引,如性别和年龄,那年龄的维度就高于性别
性别这样的列不适合创建索引,因为维度过低

2.对 where,on,group by,order by 中出现的列使用索引

3.对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键

4.为较长的字符串使用前缀索引

5.不要过多创建索引,除了增加额外的磁盘空间外,对于DML操作的速度影响很大,因为其每增删改一次就得从新建立索引

6.使用组合索引,可以减少文件索引大小,在使用时速度要优于多个单列索引

组合索引与前缀索引

注意,这两种称呼是对建立索引技巧的一种称呼,并非索引的类型;

组合索引
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,这样可以减少索引文件大小,加快索引查询速度

什么样的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估计使用全表扫描要比使用索引快,则不使用索引

多表关联时的索引效率

MySQL：mysql索引优化, mysql全文检索, mysql 不等于优化, 全值匹配, 最佳左前缀法则

从上图可以看出,所有表的type为all,表示全表索引;也就是6_6_6,共遍历查询了216次;

除第一张表示全表索引(必须的,要以此关联其他表),其余的为range(索引区间获得),也就是6+1+1+1,共遍历查询9次即可;

所以我们建议在多表join的时候尽量少join几张表,因为一不小心就是一个笛卡尔乘积的恐怖扫描,另外,我们还建议尽量使用left join,以少关联多.因为使用join 的话,第一张表是必须的全扫描的,以少关联多就可以减少这个扫描次数.

索引的弊端

不要盲目的创建索引,只为查询操作频繁的列创建索引,创建索引会使查询操作变得更加快速,但是会降低增加、删除、更新操作的速度,因为执行这些操作的同时会对索引文件进行重新排序或更新;

但是,在互联网应用中,查询的语句远远大于DML的语句,甚至可以占到80%~90%,所以也不要太在意,只是在大数据导入时,可以先删除索引,再批量插入数据,最后再添加索引;

Explain

使用explain能够知道自己写的sql语句在mysql中到底是怎样运行的，到底扫描了多少行，是否使用了索引，返回的结果如下：

+------+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | t_blogger | ALL  | NULL          | NULL | NULL    | NULL |    2 |       |
+------+-------------+-----------+------+---------------+------+---------+------+------+-------+

下面将会针对上面的值详细讲解

id

sql执行查询的序列号，决定了查询中select子句的查询顺序，分为三种情况，如下：

id相同

查询的select子句从上到到下执行，如下：

explain select * from t_blog ,t_blogger;
+------+-------------+-----------+------+---------------+------+---------+------+------+------------------------------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra                              |
+------+-------------+-----------+------+---------------+------+---------+------+------+------------------------------------+
|    1 | SIMPLE      | t_blogger | ALL  | NULL          | NULL | NULL    | NULL |    2 |                                    |
|    1 | SIMPLE      | t_blog    | ALL  | NULL          | NULL | NULL    | NULL |   16 | Using join buffer (flat, BNL join) |
+------+-------------+-----------+------+---------------+------+---------+------+------+------------------------------------+

那么执行的孙顺序就是先查询t_blogger，之后查询t_blog

id不同

id的值越大优先级越高，就先执行，剩下相同的id的值，按照顺序从上到下执行

table

select语句执行查询的表，如果是使用联合查询的，那么会使用这个值可能是虚拟的表

索引优化

全值匹配

全部使用了索引，并且如果是复合索引，一定要按照复合索引的顺序查询，这样才能达到最高效的查询，如下：

-- 为user表创建组合索引 index_nameAgePos
-- 全值匹配的实例 ,查询的条件的顺序必须和创建索引的顺序一致
select * from t_user where name="Tom" and age=22 and pos="1"

最佳左前缀法则

如果使用了组合索引（索引了多列），那么一定查询要从最左前列开始并且不能跳过索引中的列

比如index_nameAgePos这个索引，实例如下：

-- 全值匹配，最为高效
explain select * from t_user where name="Tom" and age=22 and pos="1"  
-- 去掉最后一个，使用前两个，那么前两个索引会有效，使用了部分索引
 explain select * from t_user where name="Tom" and age=22
-- 去掉后面两个，只是用第一个，索引依然有效，使用了第一个索引的类，部分索引
explain select * from t_user where name="Tom"
-- 去掉第一个，使用后面两个索引查询，没有使用做前缀，索引失效，
explain select * from t_user where and age=22 and pos="1"  
-- 去掉中间的一个，只使用第一个和第三个,中间断了，不能查找到索引，索引失效，即使有了做前缀依然会失效
explain select * from t_user where name="Tom" and pos="1"

通过上面的例子得出：使用组合索引的时候，一定要带上左前缀，并且不能跳过中间的索引，否则将会索引失效

不在索引上列上做任何操作

不要在索引列上做任何的操作，包括计算、函数、自动或者手动类型的转换，这样都会导致索引失效

select * from user where name=2000  ---- 我们知道name是一个varchar类型的，但是用name=2000虽然能够查到，但是在内部其实是将name转换成了数值类型，因此不能使用索引
select * from user where left(name,4)="TOm"    -- 这里将对name使用了left这个函数，索引失效

不能使用索引中范围条件右边的列（范围之后的索引全失效）

在使用组合索引的时候，一旦索引中有列使用了范围查询（>=…in….like,between子句），那么在其右边的索引将会失效

假设创建了组合索引，顺序为name，age，address

-- age使用了范围查询，那么在其右边的address将不会使用索引查询，但是name和age使用了索引
explain select age from user where name="JOhn" and age>22 and address="江苏"；

使用覆盖索引，少使用select*

需要用到什么数据就查询什么数据，这样可以减少网络的传输和mysql的全表扫描
尽量使用覆盖索引，比如索引为name，age，address的组合索引，那么尽量覆盖这三个字段之中的值，mysql将会直接在索引上取值（using index）。并且返回值不包含不是索引的字段

mysql在使用不等于(!=或者<>)的时候无法使用导致全表扫描

在查询的时候，如果对索引使用不等于的操作将会导致索引失效，进行全表扫描

在使用or的时候，前后两个都是索引的时候才会生效

比如我们创建组合索引name，age，address

select * from user where name="John" or age=22;  -- name和age都是索引，生效
select * from user where name="John" or pos=22;    -- pos不是索引，因此导致全表扫描，索引失效

is null和is not null 导致索引失效

索引条件一旦是is null或者is not null 将会导致索引失效

like使用%开头的将会导致索引失效

如果使用模糊查找的时候，使用%a%的时候将会导致索引失效

explain select * from user where name like "%a%";   --  索引失效
explain select * from user where name like "a%";  -- 索引生效、
explain select * from user where name like "%a";  --- 索引失效

解决方法

需求就需要使用%$%查询，那么我们如何解决索引失效？我们可以使用覆盖索引避免索引失效

假设我们的索引为name，age

select * from user where name like "%aa%";   -- 索引失效，没有使用覆盖索引而是select*
select name from user where name like "%a%" ;   -- 索引生效，使用了覆盖索引，返回索引列name
select name,age from user where name like "%aa%"  -- 索引生效，name和age都是索引
select naem，pos from user where name like "%a"  -- 索引失效，pos不是索引

字符串不加单引号导致索引失效

select * from user where pos=2000，将会导致name这个索引失效，因为mysql在底层会自动为name这个字段进行类型转换

单表查询优化

在经常查询或者排序的字段建立索引

两表查询优化

我们一般会使用联合查询，比如left Join，right Join

我们在不建立索引的情况下，如下：

-- 没有索引，全表扫描
explain select * from user left join image on user.url=image.url

那么我们这个索引应该建在哪张表上呢？我们验证之后知道，应该在image表中对url建立索引
总结：左连接在右边的表上加索引，右连接在左表添加索引

三表查询优化

三表建立索引，依然按照左连接在右表上建立索引，右连接在左表上建立索引。

-- 没有建立索引，全表扫描
select * from t1 left jon t2 t1.name=t2.name left join t3 t2.url=t3.url

我们可以在t2的表上为name字段建立索引，在t3表上为url字段建立索引，那么将会使用索引查询

小表驱动大表

在链接查询的时候，比如left Join，这种查询是左边的表驱动右边的表，那么我们应该小表驱动大表的策略，对于左连接的时候，左边的表应该是小表，右连接反之

order by 排序的索引生效

假设组合索引为name，age，address
对于order by排序问题，只有满足以下两种情况才会使用索引排序（using index)
- 对于组合索引，order by 语句使用最左前缀查询
  - select * from user order by name：使用索引
  - select * from user order by age：不使用索引
  - select * from user order by name,age：使用索引，因为排序规则一样并且是左前缀查询
  - select * from user order by name asc,age desc：不使用索引，因为排序规则不同，即使使用了最佳左前缀
- 使用where子句与order by子句条件列组合满足索引最左前缀查询
  - select * from user where name="John" order by age ：使用索引，因为where中的name和order by中的age组合在一起符合最佳左前缀原则
  - select * from user where age=22 order by address：不使用索引
总结：order by排序应该遵循最佳左前缀查询，如果是使用多个索引字段进行排序，那么排序的规则必须相同（同是升序或者降序）

总结

使用最佳左前缀
提高sort_buffer_size的值：不管是使用单路排序还是双路排序，提高这个参数都会提高查询效率
提高max_length_for_sort_data的值：提高这个参数的值，会增加使用单路排序算法的概率，但是如果设置的太高，数据总容量超出sort_buffer_size的概率增大，明显症状是磁盘I/O活动和低的处理器使用率

最后一句话总结：

1.不要使用in操作符，这样数据库会进行全表扫描，
推荐方案：在业务密集的SQL当中尽量不采用IN操作符

2.not in 使用not in也不会走索引
推荐方案：用not exists或者（外联结+判断为空）来代替

3<> 操作符（不等于）使用<>同样不会使用索引，因此对它的处理只会产生全表扫描
推荐方案：用其它相同功能的操作运算代替，如
a<>0 改为 a>0 or a<0

a<>’’ 改为 a>’’

4.IS NULL 或IS NOT NULL操作（判断字段是否为空）

判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。
推荐方案：用其它相同功能的操作运算代替，如
a is not null 改为 a>0 或a>’’等。

5.> 及 < 操作符（大于或小于操作符）

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段 A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

6.LIKE操作符

LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘5400%’则会引用范围索引。
可以采用substr(column,1,4)=’5400’

7.UNION操作符

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select * from justcode 
union 
select * from ikeepstudying

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

8.SQL书写的影响（针对oracle而言）

同一功能同一性能不同写法SQL的影响

如一个SQL在A程序员写的为

select * from justcode_php

B程序员写的为

Select * from ikeepstudying.justcode_php（带表所有者的前缀）

C程序员写的为

select * from IKS.JUSTCODE（大写表名）

D程序员写的为

select * fromIKS. JUSTCODE（中间多了空格）

以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个 SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。

9.WHERE后面的条件顺序影响

WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如

select * from ikeepstudying where php = '1K以下' and mysql=1 
select * from ikeepstudying where mysql=1 and php = '1K以下'

本文：MySQL：mysql索引优化, mysql全文检索, mysql 不等于优化, 全值匹配, 最佳左前缀法则

Tags:mysql 不等于优化, MySQL：mysql索引优化, mysql全文检索, 全值匹配, 最佳左前缀法则