北大青鸟设计培训：mysql数据库的优化方法？

我们都知道，服务器数据库的开发一般都是通过java或者是PHP语言来编程实现的，而为了提高我们数据库的运行速度和效率，数据库优化也成为了我们每日的工作重点，今天，昌平IT培训就一起来了解一下mysql服务器数据库的优化方法。

成都创新互联公司服务项目包括泸县网站建设、泸县网站制作、泸县网页制作以及泸县网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，泸县网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到泸县省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！

为什么要了解索引真实案例案例一：大学有段时间学习爬虫，爬取了知乎300w用户答题数据，存储到mysql数据中。

那时不了解索引，一条简单的“根据用户名搜索全部回答的sql“需要执行半分钟左右，完全满足不了正常的使用。

案例二：近线上应用的数据库频频出现多条慢sql风险提示，而工作以来，对数据库优化方面所知甚少。

例如一个用户数据页面需要执行很多次数据库查询，性能很慢，通过增加超时时间勉强可以访问，但是性能上需要优化。

索引的优点合适的索引，可以大大减小mysql服务器扫描的数据量，避免内存排序和临时表，提高应用程序的查询性能。

索引的类型mysql数据中有多种索引类型，primarykey，unique，normal，但底层存储的数据结构都是BTREE;有些存储引擎还提供hash索引，全文索引。

BTREE是常见的优化要面对的索引结构，都是基于BTREE的讨论。

B-TREE查询数据简单暴力的方式是遍历所有记录;如果数据不重复，就可以通过组织成一颗排序二叉树，通过二分查找算法来查询，大大提高查询性能。

而BTREE是一种更强大的排序树，支持多个分支，高度更低，数据的插入、删除、更新更快。

现代数据库的索引文件和文件系统的文件块都被组织成BTREE。

btree的每个节点都包含有key，data和只想子节点指针。

btree有度的概念d=1。

假设btree的度为d，则每个内部节点可以有n=[d+1，2d+1)个key，n+1个子节点指针。

树的大高度为h=Logb[(N+1)/2]。

索引和文件系统中，B-TREE的节点常设计成接近一个内存页大小(也是磁盘扇区大小)，且树的度非常大。

这样磁盘I/O的次数，就等于树的高度h。

假设b=100，一百万个节点的树，h将只有3层。

即，只有3次磁盘I/O就可以查找完毕，性能非常高。

索引查询建立索引后，合适的查询语句才能大发挥索引的优势。

另外，由于查询优化器可以解析客户端的sql语句，会调整sql的查询语句的条件顺序去匹配合适的索引。

SQL优化万能公式：5 大步骤 + 10 个案例

在应用开发的早期，数据量少，开发人员开发功能时更重视功能上的实现，随着生产数据的增长，很多SQL语句开始暴露出性能问题，对生产的影响也越来越大，有时可能这些有问题的SQL就是整个系统性能的瓶颈。

1、通过慢查日志等定位那些执行效率较低的SQL语句

2、explain 分析SQL的执行计划

type由上至下，效率越来越高

Extra

3、show profile 分析

了解SQL执行的线程的状态及消耗的时间。默认是关闭的，开启语句“set profiling = 1;”

4、trace

trace分析优化器如何选择执行计划，通过trace文件能够进一步了解为什么优惠券选择A执行计划而不选择B执行计划。

5、确定问题并采用相应的措施

案例1、最左匹配

索引

SQL语句

查询匹配从左往右匹配，要使用order_no走索引，必须查询条件携带shop_id或者索引( shop_id , order_no )调换前后顺序

案例2、隐式转换

索引

SQL语句

隐式转换相当于在索引上做运算，会让索引失效。mobile是字符类型，使用了数字，应该使用字符串匹配，否则MySQL会用到隐式替换，导致索引失效。

案例3、大分页

索引

SQL语句

对于大分页的场景，可以优先让产品优化需求，如果没有优化的，有如下两种优化方式， 一种是把上一次的最后一条数据，也即上面的c传过来，然后做“c xxx”处理，但是这种一般需要改接口协议，并不一定可行。另一种是采用延迟关联的方式进行处理，减少SQL回表，但是要记得索引需要完全覆盖才有效果，SQL改动如下

案例4、in + order by

索引

SQL语句

in查询在MySQL底层是通过n*m的方式去搜索，类似union，但是效率比union高。in查询在进行cost代价计算时（代价 = 元组数 * IO平均值），是通过将in包含的数值，一条条去查询获取元组数的，因此这个计算过程会比较的慢，所以MySQL设置了个临界值(eq_range_index_pe_limit)，5.6之后超过这个临界值后该列的cost就不参与计算了。因此会导致执行计划选择不准确。默认是200，即in条件超过了200个数据，会导致in的代价计算存在问题，可能会导致Mysql选择的索引不准确。

处理方式，可以( order_status , created_at )互换前后顺序，并且调整SQL为延迟关联。

案例5、范围查询阻断，后续字段不能走索引

索引

SQL语句

范围查询还有“IN、between”

案例6、不等于、不包含不能用到索引的快速搜索。（可以用到ICP）

在索引上，避免使用NOT、!=、、!、NOT EXISTS、NOT IN、NOT LIKE等

案例7、优化器选择不使用索引的情况

如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右），优化器会选择通过聚集索引来查找数据。

查询出所有未支付的订单，一般这种订单是很少的，即使建了索引，也没法使用索引。

案例8、复杂查询

如果是统计某些数据，可能改用数仓进行解决；如果是业务上就有那么复杂的查询，可能就不建议继续走SQL了，而是采用其他的方式进行解决，比如使用ES等进行解决。

案例9、asc和desc混用

desc 和asc混用时会导致索引失效

案例10、大数据

对于推送业务的数据存储，可能数据量会很大，如果在方案的选择上，最终选择存储在MySQL上，并且做7天等有效期的保存。那么需要注意，频繁的清理数据，会照成数据碎片，需要联系DBA进行数据碎片处理。

SQL优化（二）

SQL优化一： sql优化（一）

上片文章已经详细介绍了explain各个字段的含义，以及什么情况应该建立索引，什么情况不需要建立索引以及sql语句性能的判断依据，接下来我介绍下如何合理的建立索引。

sql语句：select id，author_id from article where category_id = 1 and comments1 order by views desc limit 1;

分析：首先我们根据where后面的条件建立符合索引，然后根据order by后面的字段建立索引，因此建立索引idx_article_ccv，即以（category_id，comments，views）数据列建立复合索引，但由于comments是一个范围，按照BTree索引的原理，先排序category_id,如果遇到相同的category_id则再排序comments，如果遇到相同的comments则再排序views，又因为comments字段在复合索引里处于中间位置，而comments1是一个条件（是一个范围值），在复合索引的一个范围值的数据列后面的索引全部失效，mysql无法利用索引再对后面的views部分进行检索，也就是说views无法按照索引排序，所以explain下此sql语句，type为range，extra使用的是Using filesort，这是比较糟糕的。所以我们放弃comments这个范围字段，建立索引idx_article_cv，即以（category_id，views）数据列建立复合索引，explain 此sql，type变成了ref，extra的using filesort也变成了using index，这就变得好多了。

索引：idx_article_cv，即以（category_id，views）数据列建立复合索引

前段时间做了一个销售精细化项目，是公司crm项目的一个大模块，大致就是为销售人员制定指标，实现销售目标从区域到团到业务员到客户，实时跟踪业务员所负责客户的下单量的情况。这就存在许多关联关系，区域-团，团-业务员，业务员-客户，这使得sql常常需要关联多张表。

sql语句：SELECT

tu.fuserid,

tu.faccount,

tu.fphone,

tu.fcertificationtype,

tu.fcertificatename,

tu.fkeyarea,

tu.fkeyareatext,

DATE_FORMAT(tcr.fupdatetime,'%Y-%m-%d %H:%i:%s') as fupdatetime,

tag.forggroupid,

tag.forggroupname,

tug.forguserid,

tug.fusername,

tug.fuserphone,

tag.fcitycode

FROM t_finedt_user AS tu

LEFT JOIN t_finedt_customer_relation AS tcr

ON tu.fuserid = tcr.fuserid

LEFT JOIN t_finedt_usergroup AS tug

ON tcr.forguserid = tug.forguserid

and tcr.forggroupid = tug.forggroupid

LEFT JOIN t_finedt_areagroup AS tag

ON tug.forggroupid = tag.forggroupid

where tu.fkeyarea=? and tu.fuserid=? and tug.forggroupid = ?

分析：上面的sql是左连接，左边的表一定是全表查询，所以要建立右边表对应关联字段的索引，在表t_finedt_user上建立tu_fuserid_fkeyarea索引，即以(fuserid，fkeyarea)字段建立索引，在表t_finedt_customer_relation 上建立tcr_forguserid_forggroupid索引，即以（forguserid，forggroupid）字段建立索引，在表t_finedt_usergroup 上建立tug_forguserid_forggroupid索引，即以（forguserid,forggroupid)字段建立索引，在表t_finedt_areagroup上建立tag_forggroupid索引，即以（forggroupid)字段建立索引。建立索引后，sql查询速度明显快了很多

索引：tcr_forguserid_forggroupid，tu_fuserid_fkeyarea，tug_forguserid_forggroupid，tag_forggroupid

1、尽可能减少join语句中的NestedLoop的循环次数，永远用小结果集驱动大结果集

2、优先优化NestedLoop的内层循环

3、保证join语句总被驱动表上的join字段已经被索引

4、当无法保证被驱动表join条件字段被索引，且内存资源充足的前提下，不要太吝啬joinBuffer的设置

1、全值匹配我最爱

2、最佳左前缀原则——如果索引了多列，要遵守最左前缀原则，指的是查询从索引的最左前列开始并且不跳过索引中的列

3、并在索引列上做任何操作（计算、函数、自动or手动类型转换），这些会导致索引失效而转向全表扫描

4、存储引擎不能使用索引中范围条件右边的列，范围之后的索引全失效

5、尽量使用覆盖索引（之访问索引的查询（索引列和查询的列一致）），减少select *

6、mysql在使用不等于（！=、、）的时候无法使用索引会导致全表扫描。

7、is null、is not null也无法使用索引。

8、like以通配符开头（"%abc.."），mysql索引失效也会变成全表扫描的操作。

9、字符串不加单引号也会引起索引失效

10、少用or，用它来连接时会索引失效。

1、对于单值索引，尽量选择针对当前query过滤性更好的索引

2、在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好

3、在选择组合索引的时候，尽量选择尽可能包含当前query中的where字句中更多字段的索引

4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

全值匹配我最爱，最左前缀要遵守

带头大哥不能死，中间兄弟不能断

索引列上少计算，范围之后全失效

like百分写最右，覆盖索引不写里

不等空值还有or，索引失效要少用

var引号不可丢，sql高级也不难

网页标题：mysql不等于怎么优化 mysql不等于null如何写
URL标题：http://scyanting.com/article/dogjcec.html