MySQL数据库及其优化

名词解释：mysqlD（……BMS）
数据库管理系统

从数据库存储引擎说起：

存储引擎会影响数据库的性能和功能。

存储引擎建立在表之上

• ISAM
  不支持事务，不能容错，所以（查询）速度飞快
• MyISAM
  ISAM扩展，5.5版本之前。增加索引、字段管理和一些工具，对于每个表都生成3个文件，所以（查询）速度快。
• InnoDB
  支持事务和外建，所以没前两个快。如今的默认引擎。
  InnoDB将表和索引信息存在一个文件（5.7以后分为两个存表结构和索引），索引为聚集索引
  可以和其他两个引擎的表进行连接

从引擎分析做数据库优化：

1.拥有事务的引擎慢，所以最好把多条SQL 语言放在begin transaction 和commit 之间，组成一个事务；
2.InnoDB是聚集索引，辅助索引找主键，主键找数据，
主键很重要，所以主键名字要起的短
3.InnoDB不保存具体的行，所以count(*)是整表查询。
MyISAM有个文件保存了表信息。
4.多引擎表连接

存储引擎管理

索引

索引的缺点：

• 创建和维护耗时，所以增删改慢
• 占用物理空间

MySQL中的索引种类

1. B-Tree索引
   广（宽）度换深度
   每个节点中存：以此为根节点树关键字个数，关键字，父节点指针，子节点指针。
2. Full-text索引（全文索引）
   结构也是B-Tree，主要解决部分like查询低效问题。
   （只能解决’xxx%’的like 查询。如：字段数据为ABCDE，索引建立为- A、AB、ABC、ABCD、ABCDE 五个。）

索引管理

从索引做数据库优化：

1. 经常搜索的列加索引（where后面的条件列都要加索引才有效）
2. 经常用在连接上的列加索引（外建）
3. 因为索引会自动排序，所以需要排序、查找范围的字段加索引。
4. 值很少的列不加索引（性别、部门）
5. 表主要用于增删改时不加索引
6. 只有使用like“a%”这种才能用全文索引。对于较大数据集，先插入数据，再添加全文索引。如果容量特别大，不建议使用全文索引。
7. 包含null的列会让索引失效，可以创建的时候加默认值
8. 不要在列上计算，会让索引失效
9. 查询只能使用一个索引，既有where又有order by会让索引失效。
10. or会让索引失效
11. 对字段做计算和函数都会使索引失效（用其他方式写sql）

SQL优化：

1. 避免全表扫描，避免使用*

2. 查记录数不用count（*），用字段，因为有索引

3. 记录少的表放from的最后面（查询机制），交叉最多的表放后面。

4. 查询记录多的条件放where的最后面。

5. 减少子查询

6. 使用内部函数

7. exists替代in

8. 表起别名