mysql数据库常见数据结构和语句优化

系统管理员 2022-05-31 11:45 148阅读 0赞

myisam 分为3个文件保存（需要设置绝对路径），没有事务管理，插入和查询快，不适合经常更新删除的情况，表锁  
\-静态表 固定长度，插入块，不保存空格  
\-动态表 变长，但是删除更新会产生碎片需要定期整理  
\-压缩表 每个记录单独压缩  
innodb 支持事务处理， 处理效率稍差，占用空间大，支持外键  
\-外键可以设置操作权限 RESTRICT、CASCADE、SET NULL 和 NO ACTION。来控制存在子表时是否允许更新删除  
\-共享空间存储 索引全部放在一个文件上，方便管理，但是出现大量删除时表空间会出现大量空隙，文件很大  
\-独占表空间存储 各表独立存储 alter table TableName engine=innodb 可回收空间  
memory 内存存储，访问非常快，但重启会丢失数据，适合存储变化不频繁的小表

OPTIMIZE TABLE 整理碎片

blob,text格式字段可以使用MD5()合成索引加速查询，但是只能精确匹配，如需模糊匹配，需要使用前缀索引（如截取前100个字符）

使用浮点数保存小数时，会发生四舍五入，且不准确，如货币等，最好使用定点数decimal

字符集选择 通常可以使用utf-8，如中文数据量很大，可以使用gbk，gbk汉字占2个字节，utf-8占用3个字节

Hash索引  
\-只用于使用=或<=>操作符的等式比较。  
\-优化器不能使用 HASH 索引来加速 ORDER BY 操作。  
\-MySQL 不能确定在两个值之间大约有多少行。 如果将一个 MyISAM 表改为 HASH 索  
引的 MEMORY 表，会影响一些查询的执行效率。  
\-只能使用整个关键字来搜索一行。  
Btree索引  
当使用>、<、>=、<=、BETWEEN、!=或者<>，或者 LIKE ‘pattern’（其  
中’pattern’不以通配符开始）操作符时，都可以使用相关列上的索引。

mysql本身存在触发器（未使用过，需要时再补充）

可以通过设置sql\_mode 进行数据控制

REGEXP -正则表达式匹配（1为匹配，0为不匹配）  
group by ? with rollup 分组后聚合信息

windows下表名大小写不敏感而unix敏感

可以通过explain select来查看查询语句信息

不使用索引的情况：  
当使用like时，如果%在第一个字符，索引不会被使用  
当索引比全表索引慢时，不使用索引（如范围查询，而分布均匀时）  
Hash索引未使用=时  
用or分割的条件，后面的列没有索引  
复合索引时，索引不是第一部分时  
字符串索引，查询时未使用引号时

使用groupby查询时可设置order by null以避免排序消耗  
where和order by使用同一索引时查询不需要额外排序  
可以使用连接（join）替代嵌套查询，速度会快很多  
使用or时，最好每个条件都用到了索引

在 MySQL 中，可以使用函数 PROCEDURE ANALYSE()对当前应用的表进行分析，该函数可  
以对数据表中列的数据类型提出优化建议， 用户可以根据应用的实际情况酌情考虑是否实施  
优化。

事务四要素：原子性，一致性，隔离型，持久性

InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是  
通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过  
索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！