文章《MySQL查询剖析》报告了运用MySQL慢查询和explain敕令来定位mysql机能瓶颈的要领，定位出机能瓶颈的sql语句后，则需要对低效的sql语句举行优化。本文主要议论MySQL索引道理及经常使用的sql查询优化。
一个简朴的对照测试
前面的案例中，c2c_zwdb.t_file_count表只要一个自增id，FFileName字段未加索引的sql实行状况以下：
在上图中，type=all，key=null，rows=33777。该sql未运用索引，是一个效力异常低的全表扫描。如果加上团结查询和其他一些约束前提，数据库会猖獗的斲丧内存，并且会影响前端递次的实行。
这时刻给FFileName字段增添一个索引：
alter table c2c_zwdb.t_file_count add index index_title(FFileName);
再次实行上述查询语句，其对照很显著：
在该图中，type=ref，key=索引名（index_title），rows=1。该sql运用了索引index_title，且是一个常数扫描，依据索引只扫描了一行。
比起未加索引的状况，加了索引后，查询效力对照异常显著。
MySQL索引
经由历程上面的对照测试可以看出，索引是疾速搜刮的症结。MySQL索引的竖立关于MySQL的高效运转是很主要的。关于少许的数据，没有适宜的索引影响不是很大，然则，当跟着数据量的增添，机能会急剧下降。如果对多列举行索引(组合索引)，列的递次异常主要，MySQL仅能对索引最左侧的前缀举行有用的查找。
下面引见几种罕见的MySQL索引范例。
索引分单列索引和组合索引。单列索引，即一个索引只包括单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包括多个列。
1、MySQL索引范例
(1) 主键索引 PRIMARY KEY
它是一种特别的唯一索引，不许可有空值。平常是在建表的时刻同时建立主键索引。
固然也可以用 ALTER 敕令。记着：一个表只能有一个主键。
(2) 唯一索引 UNIQUE
唯一索引列的值必需唯一，但许可有空值。如果是组合索引，则列值的组合必需唯一。可以在建立表的时刻指定，也可以修正表构造，如：
ALTER TABLE table_name ADD UNIQUE (column)
(3) 一般索引 INDEX
这是最基本的索引，它没有任何限定。可以在建立表的时刻指定，也可以修正表构造，如：
ALTER TABLE table_name ADD INDEX index_name (column)
(4) 组合索引 INDEX
组合索引，即一个索引包括多个列。可以在建立表的时刻指定，也可以修正表构造，如：
ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)
(5) 全文索引 FULLTEXT
全文索引（也称全文检索）是现在搜刮引擎运用的一种症结手艺。它可以应用分词手艺等多种算法智能剖析出文本笔墨中症结字词的频次及主要性，然后根据肯定的算法划定规矩智能地筛选出我们想要的搜刮效果。
可以在建立表的时刻指定，也可以修正表构造，如：
ALTER TABLE table_name ADD FULLTEXT (column)
2、索引构造及道理
mysql中广泛运用B+Tree做索引，但在完成上又依据聚簇索引和非聚簇索引而差别，本文暂不议论这点。
b+树引见
下面这张b+树的图片在许多处所可以看到，之所以在这里也拔取这张，是由于以为这张图片可以很好的解释索引的查找历程。
如上图，是一颗b+树。浅蓝色的块我们称之为一个磁盘块，可以看到每一个磁盘块包括几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包括数据项17和35，包括指针P1、P2、P3，P1示意小于17的磁盘块，P2示意在17和35之间的磁盘块，P3示意大于35的磁盘块。
实在的数据存在于叶子节点，即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储实在的数据，只存储指引搜刮方向的数据项，如17、35并不实在存在于数据表中。
查找历程
在上图中，如果要查找数据项29，那末起首会把磁盘块1由磁盘加载到内存，此时发作一次IO，在内存顶用二分查找肯定29在17和35之间，锁定磁盘块1的P2指针，内存时候由于异常短（比拟磁盘的IO）可以忽略不计，经由历程磁盘块1的P2指针的磁盘地点把磁盘块3由磁盘加载到内存，发作第二次IO，29在26和30之间，锁定磁盘块3的P2指针，经由历程指针加载磁盘块8到内存，发作第三次IO，同时内存中做二分查找找到29，完毕查询，合计三次IO。实在的状况是，3层的b+树可以示意上百万的数据，如果上百万的数据查找只需要三次IO，机能进步将是庞大的，如果没有索引，每一个数据项都要发作一次IO，那末统共需要百万次的IO，明显本钱异常异常高。
性子
(1) 索引字段要只管的小。
经由历程上面b+树的查找历程，或许经由历程实在的数据存在于叶子节点这个现实可知，IO次数取决于b+数的高度h。
假定当前数据表的数据量为N，每一个磁盘块的数据项的数目是m，则树高h=㏒(m+1)N，当数据量N肯定的状况下，m越大，h越小；
而m = 磁盘块的大小/数据项的大小，磁盘块的大小也就是一个数据页的大小，是牢固的；如果数据项占的空间越小，数据项的数目m越多，树的高度h越低。这就是为何每一个数据项，即索引字段要只管的小，比方int占4字节，要比bigint8字节少一半。
(2) 索引的最左婚配特征。
当b+树的数据项是复合的数据构造，比方(name,age,sex)的时刻，b+数是根据从左到右的递次来竖立搜刮树的，比方当(张三,20,F)如许的数据来检索的时刻，b+树会优先比较name来肯定下一步的所搜方向，如果name雷同再顺次比较age和sex，末了获得检索的数据；但当(20,F)如许的没有name的数据来的时刻，b+树就不晓得下一步该查哪一个节点，由于竖立搜刮树的时刻name就是第一个比较因子，必需要先依据name来搜刮才晓得下一步去那里查询。比方当(张三,F)如许的数据来检索时，b+树可以用name来指定搜刮方向，但下一个字段age的缺失，所以只能把名字即是张三的数据都找到，然后再婚配性别是F的数据了， 这个是异常主要的性子，即索引的最左婚配特征。
建索引的几大准绳
(1) 最左前缀婚配准绳
关于多列索引，老是从索引的最前面字段最先，接着今后，中心不能跳过。比方建立了多列索引(name,age,sex)，会先婚配name字段，再婚配age字段，再婚配sex字段的，中心不能跳过。mysql会一向向右婚配直到碰到局限查询(>、<、between、like)就住手婚配。
平常，在建立多列索引时，where子句中运用最频仍的一列放在最左侧。
看一个补相符最左前缀婚配准绳和相符该准绳的对照例子。
实例：表c2c_db.t_credit_detail建有索引(Flistid,Fbank_listid)
不相符最左前缀婚配准绳的sql语句：
select * from t_credit_detail where Fbank_listid='201108010000199'G
该sql直接用了第二个索引字段Fbank_listid，跳过了第一个索引字段Flistid，不相符最左前缀婚配准绳。用explain敕令检察sql语句的实行计划，以下图：
从上图可以看出，该sql未运用索引，是一个低效的全表扫描。
相符最左前缀婚配准绳的sql语句：
select * from t_credit_detail where Flistid='2000000608201108010831508721' and Fbank_listid='201108010000199'G
该sql先运用了索引的第一个字段Flistid，再运用索引的第二个字段Fbank_listid，中心没有跳过，相符最左前缀婚配准绳。用explain敕令检察sql语句的实行计划，以下图：
从上图可以看出，该sql运用了索引，仅扫描了一行。
对照可知，相符最左前缀婚配准绳的sql语句比不相符该准绳的sql语句效力有极大进步，从全表扫描上升到了常数扫描。
(2) 只管挑选区分度高的列作为索引。
比方，我们会挑选学号做索引，而不会挑选性别来做索引。
(3) =和in可以乱序
比方a = 1 and b = 2 and c = 3，竖立(a,b,c)索引可以恣意递次，mysql的查询优化器会帮你优化成索引可以辨认的情势。
(4) 索引列不能介入盘算，坚持列“清洁”
比方：Flistid+1>‘2000000608201108010831508721‘。缘由很简朴，如果索引列介入盘算的话，那每次检索时，都邑先将索引盘算一次，再做比较，明显本钱太大。
(5) 只管的扩大索引，不要新建索引。
比方表中已经有a的索引，现在要加(a,b)的索引，那末只需要修正本来的索引即可。
索引的不足
虽然索引可以进步查询效力，但索引也有本身的不足之处。
索引的分外开支：
(1) 空间：索引需要占用空间；
(2) 时候：查询索引需要时候；
(3) 保护：索引需要保护（数据变动时）；
不发起运用索引的状况：
(1) 数据量很小的表
(2) 空间慌张
经常使用优化总结
优化语句许多，需要注重的也许多，针对日常平凡的状况总结一下几点：
1、有索引但未被用到的状况（不发起）
(1) Like的参数以通配符开首时
只管防止Like的参数以通配符开首，不然数据库引擎会摒弃运用索引而举行全表扫描。
以通配符开首的sql语句，比方：select * from t_credit_detail where Flistid like '%0'G
这是全表扫描，没有运用到索引，不发起运用。
不以通配符开首的sql语句，比方：select * from t_credit_detail where Flistid like '2%'G
很显著，这运用到了索引，是有局限的查找了，比以通配符开首的sql语句效力进步不少。
(2) where前提不相符最左前缀准绳时
例子已在最左前缀婚配准绳的内容中有举例。
(3) 运用！= 或 <> 操纵符时
只管防止运用！= 或 <>操纵符，不然数据库引擎会摒弃运用索引而举行全表扫描。运用>或<会比较高效。
select * from t_credit_detail where Flistid != '2000000608201108010831508721'G
(4) 索引列介入盘算
应只管防止在 where 子句中对字段举行表达式操纵，这将致使引擎摒弃运用索引而举行全表扫描。
select * from t_credit_detail where Flistid +1 > '2000000608201108010831508722'G
(5) 对字段举行null值推断
应只管防止在where子句中对字段举行null值推断，不然将致使引擎摒弃运用索引而举行全表扫描，如： 低效：select * from t_credit_detail where Flistid is null ;
可以在Flistid上设置默认值0，确保表中Flistid列没有null值，然后如许查询： 高效：select * from t_credit_detail where Flistid =0;
(6) 运用or来衔接前提
应只管防止在where子句中运用or来衔接前提，不然将致使引擎摒弃运用索引而举行全表扫描，如： 低效：select * from t_credit_detail where Flistid = '2000000608201108010831508721' or Flistid = '10000200001';
可以用下面如许的查询替代上面的 or 查询： 高效：select from t_credit_detail where Flistid = '2000000608201108010831508721' union all select from t_credit_detail where Flistid = '10000200001';
2、防止select *
在剖析的历程当中，会将'*' 顺次转换成一切的列名，这个事情是经由历程查询数据字典完成的，这意味着将消耗更多的时候。
所以，应当养成一个需要什么就取什么的好习惯。
3、order by 语句优化
任安在Order by语句的非索引项或许有盘算表达式都将下降查询速率。
要领：1.重写order by语句以运用索引；
2.为所运用的列竖立别的一个索引 3.相对防止在order by子句中运用表达式。
4、GROUP BY语句优化
进步GROUP BY 语句的效力, 可以经由历程将不需要的记录在GROUP BY 之前过滤掉
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP by JOB
HAVING JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT'
OR JOB = ‘MANAGER'
GROUP by JOB
5、用 exists 替代 in
许多时刻用 exists 替代 in 是一个好的挑选： select num from a where num in(select num from b) 用下面的语句替代： select num from a where exists(select 1 from b where num=a.num)
6、运用 varchar/nvarchar 替代 char/nchar
只管的运用 varchar/nvarchar 替代 char/nchar ，由于起首变长字段存储空间小，可以节约存储空间，其次关于查询来讲，在一个相对较小的字段内搜刮效力明显要高些。
7、能用DISTINCT的就不必GROUP BY
SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
可改成：
SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10
8、能用UNION ALL就不要用UNION
UNION ALL不实行SELECT DISTINCT函数，如许就会削减许多不必要的资本。
9、在Join表的时刻运用相称范例的例，并将其索引
如果应用递次有许多JOIN 查询，你应当确认两个表中Join的字段是被建过索引的。如许，MySQL内部会启动为你优化Join的SQL语句的机制。
而且，这些被用来Join的字段，应当是雷同的范例的。比方：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就没法运用它们的索引。关于那些STRING范例，还需要有雷同的字符集才行。（两个表的字符集有能够不一样）
本篇文章到这里就悉数完毕了，关于MySQL的更多学问人人可以关注ki4网的MySQL教程栏目！！！
以上就是MySQL索引以及查询优化的细致引见的细致内容，更多请关注ki4网别的相干文章！
