本文转自互联网本系列文章将整理到我在GitHub上的《Java面试指南》仓库，更多精彩内容请到我的仓库里查看
https://github.com/h3pl/Java-Tutorial喜欢的话麻烦点下Star哈文章首发于我的个人博客：www.how2playlife.com本文是微信公众号【Java技术江湖】的《重新学习MySQL数据库》其中一篇，本文部分内容来源于网络，为了把本文主题讲得清晰透彻，也整合了很多我认为不错的技术博客内容，引用其中了一些比较好的博客文章，如有侵权，请联系作者。该系列博文会告诉你如何从入门到进阶，从sql基本的使用方法，从MySQL执行引擎再到索引、事务等知识，一步步地学习MySQL相关技术的实现原理，更好地了解如何基于这些知识来优化sql，减少SQL执行时间，通过执行计划对SQL性能进行分析，再到MySQL的主从复制、主备部署等内容，以便让你更完整地了解整个MySQL方面的技术体系，形成自己的知识框架。如果对本系列文章有什么建议，或者是有什么疑问的话，也可以关注公众号【Java技术江湖】联系作者，欢迎你参与本系列博文的创作和修订。除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量：尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNEDVARCHAR的长度只分配真正需要的空间使用枚举或整数代替字符串类型尽量使用TIMESTAMP而非DATETIME，单表不要有太多字段，建议在20以内避免使用NULL字段，很难查询优化且占用额外索引空间用整型来存IP索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描值分布很稀少的字段不适合建索引，例如”性别”这种只有两三个值的字段字符字段只建前缀索引字符字段最好不要做主键不用外键，由程序保证约束尽量不用UNIQUE，由程序保证约束使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引可通过开启慢查询日志来找出较慢的SQL不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库不用SELECT *OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内不用函数和触发器，在应用程序实现避免%xxx式查询少用JOIN使用同类型进行比较，比如用'123'和'123'比，123和123比尽量避免在WHERE子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大目前广泛使用的是MyISAM和InnoDB两种引擎：MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁不支持事务不支持外键不支持崩溃后的安全恢复在表有读取查询的同时，支持往表中插入新纪录支持BLOB和TEXT的前500个字符索引，支持全文索引支持延迟更新索引，极大提升写入性能对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用InnoDB在MySQL 5.5后成为默认索引，它的特点是：支持行锁，采用MVCC来支持高并发支持事务支持外键支持崩溃后的安全恢复不支持全文索引总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表首先是建表和导数据的过程。参考
https://nsimple.top/archives/mysql-create-million-data.html有时候我们需要对大数据进行测试，本地一般没有那么多数据，就需要我们自己生成一些。下面会借助内存表的特点进行生成百万条测试数据。SQLSQLSQLSQLSQLSQLSQLSQLSQLSQLSELECT * FROM vote_record；全表查询建完表以后开启慢查询日志，具体参考下面的例子，然后学会用explain。windows慢日志的位置在c盘，另外，使用client工具也可以记录慢日志，所以不一定要用命令行来执行测试，否则大表数据在命令行中要显示的非常久。
1 全表扫描select * from vote_record
慢日志SET timestamp=1529034398; select * from vote_record; Time: 2018-06-15T03:52:58.804850Z User
@Host: root[root] @ localhost [::1] Id: 74 Query_time: 3.166424 Lock_time: 0.000000 Rows_sent: 900500 Rows_examined: 999999耗时3秒，我设置的门槛是一秒。所以记录了下来。
explain执行计划id select_type table partitions type possible_keys key key_len ref rows filtered Extra1 SIMPLE vote_record N ALL N N N N 996507 100.00 N全表扫描耗时3秒多，用不到索引。
2 select * from vote_record where vote_num > 1000没有索引，所以相当于全表扫描，一样是3.5秒左右
3 select * from vote_record where vote_num > 1000
加索引create
CREATE INDEX vote ON vote_record(vote_num);
explain查看执行计划id select_type table partitions type possible_keys key key_len ref rows filtered Extra1 SIMPLE vote_record N ALL votenum,vote N N N 996507 50.00 Using where还是没用到索引，因为不符合最左前缀匹配。查询需要3.5秒左右最后修改一下sql语句EXPLAIN SELECT * FROM vote_record WHERE id > 0 AND vote_num > 1000;id select_type table partitions type possible_keys key key_len ref rows filtered Extra1 SIMPLE vote_record N range PRIMARY,votenum,vote PRIMARY 4 N 498253 50.00 Using where用到了索引，但是只用到了主键索引。再修改一次EXPLAIN SELECT * FROM vote_record WHERE id > 0 AND vote_num = 1000;id select_type table partitions type possible_keys key key_len ref rows filtered Extra1 SIMPLE vote_record N index_merge PRIMARY,votenum,vote votenum,PRIMARY 8,4 N 51 100.00 Using intersect(votenum,PRIMARY); Using where用到了两个索引，votenum,PRIMARY。这是为什么呢。再看一个语句EXPLAIN SELECT * FROM vote_record WHERE id = 1000 AND vote_num > 1000id select_type table partitions type possible_keys key key_len ref rows filtered Extra1 SIMPLE vote_record N const PRIMARY,votenum PRIMARY 4 const 1 100.00 N也只有主键用到了索引。这是因为只有最左前缀索引可以用>或会导致用不到索引。下面是几个网上参考的例子：一：索引是sql语句优化的关键，学会使用慢日志和执行计划分析sql背景：使用A电脑安装mysql,B电脑通过xshell方式连接，数据内容我都已经创建好，现在我已正常的进入到mysql中步骤1：设置慢查询日志的超时时间，先查看日志存放路径查询慢日志的地址，因为有慢查询的内容，就会到这个日志中：
2.开启慢查询日志3.查看慢查询日志的设置时间，是否是自己需要的
4.如果不是自己想的时间，修改慢查询时间，只要超过了以下的设置时间，查询的日志就会到刚刚的日志中，我设置查询时间超过1S就进入到慢查询日志中5.大数据已准备，进行数据的查询，xshell最好开两个窗口，一个查看日志，一个执行内容
发现查数据的总时间去掉了17.74S查看日志：打开日志
标记1：执行的sql语句标记2：执行sql的时间，我的是10点52执行的标记3：使用那台机器标记4：执行时间，query_tims,查询数据的时间标记5：不知道是干嘛的标记6：执行耗时的sql语句，我在想我1的应该是截取错了！但是记住最后一定是显示耗时是因为执行什么sql造成的6.执行打印计划，主要是查看是否使用了索引等其他内容,主要就是在sql前面加上explain 关键字
描述extra中，表示只使用了where条件，没有其他什么索引之类的7.进行sql优化，建一个fist_name的索引，索引就是将你需要的数据先给筛选出来，这样就可以节省很多扫描时间
注：创建索引时会很慢，是对整个表做了一个复制功能，并进行数据的一些分类（我猜是这样，所以会很慢）8.查看建立的索引
9.在执行查询语句，查看语句的执行时间
发现时间已经有所提升了，其实选择索引也不一开始就知道，我们在试试使用性别，gender进行索引10.删除已经有的索引，删除索引：11.创建性别的索引(性别是不怎么好的索引方式，因为有很多重复数据)在执行sql语句查询数据，查看查询执行时间，没有创建比较优秀的索引，导致查询时间还变长了，为嘛还变长了，这个我没有弄懂
12.我们在试试使用创建组合索引，使用性别和姓名在执行sql查看sql数据的执行时间速度提升了N多倍啊
查看创建的索引
索引建的好真的一个好帮手，建不好就是费时的一个操作 目前还不知道为什么建立性别的索引会这么慢二：sql优化注意要点，比如索引是否用到，查询优化是否改变了执行计划，以及一些细节场景我用的数据库是mysql5.6，下面简单的介绍下场景课程表数据100条学生表:数据70000条学生成绩表SC数据70w条查询目的：查找语文考100分的考生查询语句：执行时间：30248.271s晕,为什么这么慢，先来查看下查询计划：
发现没有用到索引，type全是ALL，那么首先想到的就是建立一个索引，建立索引的字段当然是在where条件的字段。先给sc表的c_id和score建个索引再次执行上述查询语句，时间为: 1.054s快了3w多倍，大大缩短了查询时间，看来索引能极大程度的提高查询效率，看来建索引很有必要，很多时候都忘记建索引了，数据量小的的时候压根没感觉，这优化感觉挺爽。但是1s的时间还是太长了，还能进行优化吗，仔细看执行计划：
查看优化后的sql:补充：这里有网友问怎么查看优化后开发云主机域名的语句方法如下：在命令窗口执行
有type=all按照我之前的想法，该sql的执行的顺序应该是先执行子查询耗时：0.001s得到如下结果：
然后再执行耗时：0.001s这样就是相当快了啊，Mysql竟然不是先执行里层的查询，而是将sql优化成了exists子句，并出现了EPENDENT SUBQUERY，mysql是先执行外层查询，再执行里层的查询，这样就要循环70007*11=770077次。那么改用连接查询呢？这里为了重新分析连接查询的情况，先暂时删除索引sc_c_id_index，sc_score_index执行时间是：0.057s效率有所提高，看看执行计划：
这里有连表的情况出现，我猜想是不是要给sc表的s_id建立个索引CREATE index sc_s_id_index on SC(s_id);show index from SC
在执行连接查询时间: 1.076s，竟然时间还变长了，什么原因？查看执行计划：
优化后的查询语句为：貌似是先做的连接查询，再执行的where过滤回到前面的执行计划：
这里是先做的where过滤，再做连表，执行计划还不是固定的，那么我们先看下标准的sql执行顺序：
正常情况下是先join再where过滤，但是我们这里的情况，如果先join，将会有70w条数据发送join做操，因此先执行where过滤是明智方案，现在为了排除mysql的查询优化，我自己写一条优化后的sql即先执行sc表的过滤，再进行表连接，执行时间为：0.054s和之前没有建s_id索引的时间差不多查看执行计划：
先提取sc再连表，这样效率就高多了，现在的问题是提取sc的时候出现了扫描表，那么现在可以明确需要建立相关索引再执行查询：执行时间为：0.001s，这个时间相当靠谱，快了50倍执行计划：
我们会看到，先提取sc，再连表，都用到了索引。那么再来执行下sql执行时间0.001s执行计划：
这里是mysql进行了查询语句优化，先执行了where过滤，再执行连接操作，且都用到了索引。总结：1.mysql嵌套子查询效率确实比较低2.可以将其优化成连接查询3.建立合适的索引4.学会分析sql执行计划，mysql会对sql进行优化，所以分析执行计划很重要由于时间问题，这篇文章先写到这里，后续再分享其他的sql优化经历。三、海量数据分页查找时如何使用主键索引进行优化 数据分页在网页中十分多见，分页一般都是limit start,offset,然后根据页码page计算start 这种分页在几十万的时候分页效率就会比较低了，MySQL需要从头开始一直往后计算，这样大大影响效率 我们可以用explain分析下语句，没有用到任何索引，MySQL执行的行数是16W+，于是我们可以想用到索引去实现分页
使用主键索引来优化数据分页 使用explain分析语句，MySQL这次扫描的行数是8W+，时间也大大缩短。 在数据量比较大的时候，我们尽量去利用索引来优化语句。上面的优化方法如果id不是主键索引，查询效率比第一种还要低点。我们可以先使用explain来分析语句，查看语句的执行顺序和执行性能。转载于:
https://my.oschina.net/alicoder/blog/3097141
结构化查询语言（SQL）是数据挖掘分析行业不可或缺的一项技能，总的来说，学习这个技能是比较容易的。对于SQL来说，编写查询语句只是第一步，确保查询语句高效并且适合于你的数据库操作工作，才是最重要的。这个教程将会提供给你一些步骤，来评估你的查询语句。首先，应该了…
免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。
微信扫一扫 
