如何写出优质mysql实战

需求1：找出发布文章的用户姓名以及文章的信息

首先创建一张普通的表 文章表

create table article(
	id int PRIMARY key auto_increment,
	title varchar(10) not null,
	publish_id int not null,#文章的发布人
	gmt_create TIMESTAMP, # 发布时间
	comment_number INT,# 评论数
	like_number int ,#喜欢的人数
	view_number int ,#浏览数
	content  VARCHAR(1000)#文章内容
    category_id int#文章所属类型
)

用户表

create table users(
	id int primary key auto_increment,##用户的唯一索引
	publish_id int not null,##用户的账号
	`name` varchar(10) ,#用户的姓名
	user_pwd varchar(20) not null
)

select * from article left join users on users.publish_id = article.publish_id;

分析(病例书) select_type为SIMPLE说明是普通的查询 type=ALL 全表查询，position_keys 和key 说明没有用到索引，并且最重要的extra中使用了using join buffer,可见效率很差。

如何优化（治病）
双表建立索引时，对于左连接而言，连接的关键是右边的表，所以索引建在右边的表上即users上
添加索引
alter table users add index publish(publish_id);
治疗结果 extra里面了上面的额外条件。而且也用了索引。

需求2. 显示用户文章下评论数最多的3篇文章，并且显示用户的名字。

sql语句
select * from article left join users on users.publish_id = article.publish_id where article.publish_id =1 ORDER BY comment_number desc limit 3 ;
首先写下没有优化时的状态。

分析：extra里面使用了 using filesort 并且type为All 可以，此sql肯定需要优化
如果只给publish_id加上索引，虽然在很短的时间内可以找到用户,但是对于用户文章评论数的排序就要浪费时间，这会引起 using filesort.因此我们需要对publish_id和comment_number使用复合索引。

如何优化（治病）
alter table article add index index_demo(publish_id,comment_number);
效果，

需求3. 查找出在特定的一天内发布文章，并且发布类型为2的用户的信息。

sql 语句
select * from article where DATE_FORMAT(gmt_create,'%Y-%m-%d')='2020-04-02' and category_id=2

type ALL 有优化的空间
我们给日期和类型加上复合索引
alter table article add index index_demo(gmt_create,category_id);

结果却没有用
我们换一种索引的方式
alter table article add index index_demo(category_id,gmt_create);
发现下面可以使用

得出结论 不能对索引的列进行函数的操作 ，不然索引会失效。
但是上面那句也对日期进行操作了呀，因为索引的第一个字段是category_id，并且在查询的时候category_id在最开始因此没有失效,而最开始的时候是将gmt_create置于最开始，因此如果对最开始的gmt_create进行操作，索引就会完全失效。

如果我们在查询的时候调换下gmt_create 和category_id的位置不对索引进行函数操作会怎么样 如下。
sql语句
select * from article where gmt_create ='2020-04-02 08:13:31' and category_id=2

发现索引没有失效，这是为什么，我在知乎上看到，首先肯定的是mysql在执行的时候是按照（category_id和gmt_create）进行执行的，之所以上面可以查到，因为mysql有查询优化器，它会查询我们的索引，找出最佳的查找顺序并执行。

但是必须包含索引的第一个字段也就是category_id，否则就会失效 如下

需求4. 搜索与标题类似的文章

sql 语句
select * from article where title like ‘%spring%’

上述进行了全表扫描，在优化的过程中我们可以遵守 like关键字%加在字段前面索引失效的原则
修改语句
select * from article where title like 'spring%'

但是出现一个问题就是查询不出匹配的数据了。
那么我们如何做
这就涉及到覆盖索引的问题，我们用* 来查询时使用like肯定会索引失效，但是我们要明确写出查询的字段 就不会索引失效，如下
select title from article where title like '%spring%' 很明确的使用了覆盖索引，虽然possible_keys为null 但是key 实际上还是使用了索引，这就是覆盖索引的魅力。

需求5. 查找title为2000的文章。

sql语句
select * from article where title = 2000;
首先肯定的时能查询出来 虽然 name时varchar类型的。

但是explain 检查之后发现

type 为ALL 是全表扫描的
我们再中规中矩写sql 给title加上单引号
select * from article where title = '2000';

哈哈 又用上索引了，太棒了。
总结 varchar类型的字段一定注意要加上单引号。
注意在mybatis中#和$的区别
使用#{name}时 在sql语句中是加了双引号的
使用${name}时 在sql语句中是直接显示的。

select id,name,age from student where id =#{id}
当我们传递的参数id为 “1” 时，上述 sql 的解析为：
select id,name,age from student where id ="1"

$传入的参数在SqL中直接显示为传入的值‘selectid,name,agefromstudentwhereid=${id}当我们传递的参数id为 "1" 时，上述 sql 的解析为：select id,name,age from student where id =1`

优化口头禅
带头大哥不能死
中间兄弟不能断
varchar引号不能丢
范围之后全失效
索引列上无计算
like百分加右边