阳泉推广型网站建设,营销型网站设计分析案例,公众号怎么开,网页搜索关键词快捷键一个简单的对比测试
前面的案例中#xff0c;c2c_zwdb.t_file_count表只有一个自增id#xff0c;FFileName字段未加索引的sql执行情况如下#xff1a; 在上图中#xff0c;typeall#xff0c;keynull#xff0c;rows33777。该sql未使用索引#xff0c;是一个效率非常低…一个简单的对比测试
前面的案例中c2c_zwdb.t_file_count表只有一个自增idFFileName字段未加索引的sql执行情况如下 在上图中typeallkeynullrows33777。该sql未使用索引是一个效率非常低的全表扫描。如果加上联合查询和其他一些约束条件数据库会疯狂的消耗内存并且会影响前端程序的执行。
这时给FFileName字段添加一个索引
alter table c2c_zwdb.t_file_count add index index_title(FFileName);
再次执行上述查询语句其对比很明显 在该图中typerefkey索引名index_titlerows1。该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中普遍使用BTree做索引但在实现上又根据聚簇索引和非聚簇索引而不同本文暂不讨论这点。
b树介绍
下面这张b树的图片在很多地方可以看到之所以在这里也选取这张是因为觉得这张图片可以很好的诠释索引的查找过程。 如上图是一颗b树。浅蓝色的块我们称之为一个磁盘块可以看到每个磁盘块包含几个数据项深蓝色所示和指针黄色所示如磁盘块1包含数据项17和35包含指针P1、P2、P3P1表示小于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由磁盘加载到内存发生第二次IO29在26和30之间锁定磁盘块3的P2指针通过指针加载磁盘块8到内存发生第三次IO同时内存中做二分查找找到29结束查询总计三次IO。真实的情况是3层的b树可以表示上百万的数据如果上百万的数据查找只需要三次IO性能提高将是巨大的如果没有索引每个数据项都要发生一次IO那么总共需要百万次的IO显然成本非常非常高。
性质
(1) 索引字段要尽量的小。
通过上面b树的查找过程或者通过真实的数据存在于叶子节点这个事实可知IO次数取决于b数的高度h。
假设当前数据表的数据量为N每个磁盘块的数据项的数量是m则树高h㏒(m1)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_listid201108010000199\G
该sql直接用了第二个索引字段Fbank_listid跳过了第一个索引字段Flistid不符合最左前缀匹配原则。用explain命令查看sql语句的执行计划如下图 从上图可以看出该sql未使用索引是一个低效的全表扫描。
符合最左前缀匹配原则的sql语句
select * from t_credit_detail where Flistid2000000608201108010831508721 and Fbank_listid201108010000199\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) 索引列不能参与计算保持列“干净”
比如Flistid1‘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 numa.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类型还需要有相同的字符集才行。两个表的字符集有可能不一样
