day7-mysql引擎和索引
MySQL引擎
数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。
常用的数据引擎
ISAM
它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了，那就必须经常备份你所有的实时数据。
MyISAM
MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作
InnoDB（默认）
InnoDB支持事务处理（transaction process）和支持外来键。尽管要比ISAM和 MyISAM引擎慢很多，但是InnoDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。
MEMORY
MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。
MySQL 索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。
索引的查询原理
索引的的类型
- UNIQUE (唯一索引)：不可以出现相同的值，可以有NULL值
- NORMAL (普通索引)：允许出现相同的索引内容，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique
- PROMARY KEY (主键索引)：不允许出现相同的值
- FULLTEXT INDEX (全文索引)：可以针对值中的某个单词，但效率确实不敢恭维
- 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一，比如学生选课表中可以将学生ID和课程ID作为一个组合索引。
创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：
CREATE [UNIQUE] INDEX indexName ON tableName(columnName(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
修改索引
ALTER table tableName ADD INDEX indexName(columnName)；
删除索引
DROP INDEX [indexName] ON tableName;
查询表索引
SHOW INDEX FROM table_name;
是否执行索引
EXPLAIN select * from user_basic_info where phone = '66666666666';
table：显示这一行的数据是关于哪张表的type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ,一般来说，得保证查询至少达到range级别，最好能达到ref。
key： 实际使用的索引。如果为NULL，则没有使用索引。
使用索引的技巧
使用索引时，有一些技巧：
1.索引不会包含有NULL的列，只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的。
2.使用短索引，对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char（255）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.对于多列索引，不是使用的第一部分，则不会使用索引。
4.like语句操作，like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。
5.不要在列上进行运算
6.不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的
7.索引要建立在经常进行select操作的字段上。这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
8.索引要建立在值唯一的字段上。
9.在where和join中出现的列需要建立索引。
11.如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。
12.使用or查询的话，每个条件的字段都要加单独索引。
如下一下例子是不走索引的
1.索引列参入计算或者使用函数进行运算
SELECT * FROM stu WHERE age+10=30；
2.全字段模糊搜索和使用正则表达式
SELECT * FROM student WHERE uname LIKE "%小%"; -- 不走索引 SELECT * FROM student WHERE uname LIKE "小%"; -- 走索引
3.字符串与数字比较
SELECT * FROM student WHERE id="1" -- 走索引 SELECT * FROM student WHERE id=1 -- 不走索引
4.使用或者必须or条件的每一列都要加索引
SELECT * FROM student WHERE id="1" or name='张三'；
