1、MySQL有哪些引擎
查看引擎的方式
mysql> show engines; mysql常用引擎包括：MYISAM、Innodb、Memory、MERGE
（1）Innodb
Innodb:行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键 约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些
是MySQL5.5版本之后默认的存储引擎。
存储数据更加安全，支持事务，默认的事务隔离级别为可重复度
（2）MYISAM
读音：mv（第二声）sen（第四声）
MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对 较小，对事务完整性没有要求，以select、insert为主的应用基本上可以使用这引擎
是MySQL5.5版本之前默认的存储引擎。
速度比innodb快，但我们更加注重数据的安全。
（3）Memory
Memory:全表锁，存储在内容中，速度快，但会占用和数据量成正比的内存空间且数据在mysql重 启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变 化不频繁的代码表
内存引擎（数据全部放在内存中），断电数据丢失。
（4）MERGE
MERGE：是一组MYISAM表的组合
表锁：开销小、加锁快、不会出现死锁、锁的粒度大、发生锁冲突的概率大、并发性低
行锁：开销大、加锁慢、会出现死锁、锁的粒度小、冲突的发生概率更低、并发性更好。
（5）Innodb和MyIsAM区别
（1） InnoDB支持事务，MyISAM不支持
（2）InnoDB支持外键，而MyISAM不支持。
（3） InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。而MyISAM是非聚集索引，数据文件是分离的，索引保存的 是数据文件的指针。主键索引和辅助索引是独立的。
（4） Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；
| MyIsAM | InnoDB | |
|---|---|---|
| 存储限制 | 无限制 | 64TB |
| 锁机制 | 表锁 | 行锁 |
| B树索引 | 是 | 是 |
| Hash索引 | 不支持 | 不支持 |
| 全文索引 | 支持 | 不支持 |
| 集群索引 | 不支持 | 支持 |
| 数据可压缩 | 支持 | 不支持 |
| 空间使用率 | 低 | 高 |
| 内存使用率 | 低 | 高 |
| 批量插入速度 | 高 | 低 |
| 外键 | 不支持 | 支持 |
| 事务安全 | 不支持 | 支持 |
如何选择引擎？
如果没有特别的需求，使用默认的 Innodb 即可。
MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。 Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如 OA自动化办公系统。
2、MySQL索引
mysql 有4种不同的索引：
主键索引（PRIMARY）
数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引（UNIQUE）
数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
普通索引（INDEX）
可以通过 ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
可以通过 ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引
全文索引（FULLTEXT）
可以通过 ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引
索引并非是越多越好，创建索引也需要耗费资源，一是增加了数据库的存储空间，二是在插入和删除时 要花费较多的时间维护索引
索引加快数据库的检索速度
索引降低了插入、删除、修改等维护任务的速度
唯一索引可以确保每一行数据的唯一性
通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
索引需要占物理和数据空间
3、索引设计原则
（1）对于经常查询的字段，建议创建索引。即出现在where后面的字段，而不是select后面。
（2）索引不是越多越好，一个表如果有大量索引，不仅占用磁盘空间，而且会影响INSERT，DELETE，UPDATE等语句的性能。
（3）避免对经常更新的表进行过多的索引，因为当表中数据更改的同时，索引也会进行调整和更新，十分消耗系统资源。
（4）数据量小的表建议不要创建索引，数据量小时索引不仅起不到明显的优化效果，对于索引结构的维护反而消耗系统资源。
（5）不要在区分度低的字段建立索引。比如性别字段，只有 “男” 和 “女” ，建索引完全起不到优化效果。
（6）当唯一性是某字段本身的特征时，指定唯一索引能提高查询速度。
（7）在频繁进行跑排列分组（即进行 group by 或 order by操作）的列上建立索引，如果待排序有多个，可以在这些列上建立组合索引。
4、索引未命中
（1）like，如果以%开头，不会用到索引：
（2）索引列不能参与计算（+-*/），保持列“干净”。
（3）最左前缀匹配原则，非常重要的原则。不符合规范则不会命中索引。
对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)。
比如：a = 1 and b = 2 and c > 3 and d = 4， 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
（4）使用函数不能命中索引
select * from tb1 where reverse(email) = ‘duoduo’;
（5） order by ：
当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
select email from s1 order by email desc;
特别的：如果对主键排序，则还是速度很快：
select * from tb1 order by nid desc;
（6）text类型，必须制定长度，否则无法命中：
create index xxxx on tb(title(19))
其他注意事项
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
4、SQL优化
（1）查询语句中不要使用select * ，应用具体字段名
（2）尽量减少子查询，使用关联查询（left join,right join,inner join）替代
（3）减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代
（4）or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union all会更好)
（5）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
（6）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫 描
5、联接
内联接（Inner Join）：
匹配2张表中相关联的记录。
SELECT * FROM t_order a INNER JOIN product b ON a.`name`=b.`name`;
等价于
SELECT * FROM t_order a,product b WHERE a.`name`=b.`name`;左外联接（Left Outer Join）：
除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录， 右表中未匹配到的字段用NULL表示。
SELECT * FROM a_table a left join b_table b ON a.a_id = b.b_id;右外联接（Right Outer Join）：
除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录， 左表中未匹配到的字段用NULL表示。在判定左表和右表时，要根据表名出现在Outer Join的左右 位置关系
SELECT * FROM a_table a right outer join b_table b on a.a_id = b.b_id;全联接（Union 或 Union all）
用来连接2条查询语句的结果集，这两个结果集要求有相同的列数、数据类型、字段名及字段顺序。
union可以去除重复数据，union all查询全部。
SELECT id,SERIAL FROM payment1
UNION ALL
SELECT id,SERIAL FROM payment2;6、case when
来自MySQL触发器里的流程控制语句.
case when语句，用于计算条件列表并返回多个可能结果表达式之一。
CASE 具有两种格式：简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。 CASE 搜索函数计算一组布尔表达式以确定结果。 两种格式都支持可选的 ELSE 参数。
简单函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END： 枚举这个字段所有可能的值*
SELECT
NAME '英雄',
CASE NAME
WHEN '德莱文' THEN
'斧子'
WHEN '德玛西亚-盖伦' THEN
'大宝剑'
WHEN '暗夜猎手-VN' THEN
'弩'
ELSE
'无'
END '装备'
FROM
user_info;
搜索函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END：搜索函数可以写判断，并且搜索函数只会返回第一个符合条件的值，其他case被忽略
# when 表达式中可以使用 and 连接条件
SELECT
NAME '英雄',
age '年龄',
CASE
WHEN age < 18 THEN
'少年'
WHEN age < 30 THEN
'青年'
WHEN age >= 30
AND age < 50 THEN
'中年'
ELSE
'老年'
END '状态'
FROM
user_info;
7、中文乱码问题如何解决
（1）修改配置文件
vim /etc/my.cnf最底部添加
character_set_server=utf8（2）重启mysql
systemctl restart mysqld
# 查看状态
systemctl status mysqld（3）修改已存在的库表的字符集
修改数据库字符集
alter database mydb character set 'utf8';修改数据表的字符集
alert table mytbl convert to character set 'utf8';
