发表于 2018-03-20 15:18
BIT[M]位字段类型，M表示每个值的位数，范围从1到64，如果M被忽略，默认为1
TINYINT [(M)] [UNSIGNED] [ZEROFILL] M默认为4。很小的整数。带符号的范围是-128到127。无符号的范围是0到255。
SMALLINT[(M)] [UNSIGNED] [ZEROFILL] M默认为6。小的整数。带符号的范围是-32768到32767。无符号的范围是0到65535。
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] M默认为9。中等大小的整数。带符号的范围是-8388608到8388607。无符号的范围是0到16777215。
INT[(M)] [UNSIGNED] [ZEROFILL] M默认为11。普通大小的整数。带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295。
BIGINT[(M)] [UNSIGNED] [ZEROFILL] M默认为20。大整数。带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615。
注意：这里的M代表的并不是存储在数据库中的具体的长度，以前总是会误以为int(3)只能存储3个长度的数字，int(11)就会存储11个长度的数字，这是大错特错的。其实当我们在选择使用int的类型的时候，不论是int(3)还是int(11)，它在数据库里面存储的都是4个字节的长度，在使用int(3)的时候如果你输入的是10，会默认给你存储位010,也就是说这个3代表的是默认的一个长度，当你不足3位时，会帮你不全，当你超过3位时，就没有任何的影响。
**int(M)M指示最大显示宽度。最大有效显示宽度是255。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示。
like，匹配字符串时，不以通配符开头，左侧必须固定，该字段索引才会起作用
复合索引，左侧的字段固定时，在索引匹配时，右侧的索引才有效。因为复合索引关键字排序，按照左边字段进行排序，如果左边字段相同，才依据右边字段。
优点：
创建索引可以大大提高系统的性能
通过唯一性索引，可以保证数据库表中每一行数据的唯一性
大大加快检索速度
加速表与表之间的连接
使用分组和排序子句进行数据检索时，减少查询中分组和排序的时间
通过使用索引，可以在查询过程中，使用优化隐藏器，提高系统性能
缺点：
创建索引和维护索引要耗费时间，随数据量的增加而增加
索引占用物理空间
对表中的数据进行增删改的时候，索引需动态维护，降低了数据的维护速度
网络的延迟
由于mysql主从复制是基于binlog的一种异步复制，通过网络传送binlog文件，理所当然网络延迟是主从不同步的绝大多数的原因，特别是跨机房的数据同步出现这种几率非常的大，所以做读写分离，注意从业务层进行前期设计。
主从两台机器的负载不一致
由于mysql主从复制是主数据库上面启动1个io线程，而从上面启动1个sql线程和1个io线程，当中任何一台机器的负载很高，忙不过来，导致其中的任何一个线程出现资源不足，都将出现主从不一致的情况。
max_allowed_packet设置不一致
主数据库上面设置的max_allowed_packet比从数据库大，当一个大的sql语句，能在主数据库上面执行完毕，从数据库上面设置过小，无法执行，导致的主从不一致。
key自增键开始的键值跟自增步长设置不一致引起的主从不一致。
mysql异常宕机情况下，如果未设置sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能出现binlog或者relaylog文件出现损坏，导致主从不一致。
mysql本身的bug引起的主从不同步。
版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面不支持该功能
| order_id | user_id | goods |
|---|---|---|
| 100000 | 100 | 苹果 |
| 100001 | 100 | 苹果 |
| 100002 | 101 | 橘子 |
| 100003 | 102 | 苹果 |
| 100004 | 102 | 香蕉 |
sql：
SELECT order_id,user_id,COUNT(order_id) AS count FROM order GROUP BY user_id ORDER BY count DESC limit 2
原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
隔离性（Isolation）
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
持久性（Durability）
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
如果不考虑事务的隔离性，会发生的几种问题：
脏读 (dirty read) ：指在一个事务处理过程里读取了另一个未提交的事务中的数据。
不可重复读 (unrepeated read)：指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。
幻读(phantom read)：一个事务执行两次查询，第二次查询比第一次多出或少一些数据，造成两次结果不一致。只是另一个事务在这两次查询中间插入或者 删除了数据造成的。
第一类丢失更新(lost update)： 在完全未隔离事务的情况下，两个事物更新同一条数据资源，某一事物异常终止，回滚造成第一个完成的更新也同时丢失 。
第二类丢失更新(second lost updates)：是不可重复读的特殊情况，如果两个事务都读取同一行，然后两个都进行写操作，并提交，第一个事务所做的改变就会丢失。
四种事务隔离级别：
1. Serializable 串行化
2. Repeatable Read 可重复读
3. Read Commited 可读已提交
4. Read Uncommited 可读未提交
并发控制：
数据库系统采用不同的锁类型来实现以上四种隔离级别，具体的实现过程对用户是透明的。用户应该关心的是如何选择合适的隔离级别。
对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读，而且具有较好的并发性能。
每个数据库连接都有一个全局变量@@tx_isolation，表示当前的事务隔离级别。JDBC数据库连接使用数据库系统默认的隔离级别。
在Hibernate的配置文件中可以显示地设置隔离级别。每一种隔离级别对应着一个正整数。
需要注意的是，在受管理环境中，如果Hibernate使用的数据库连接来自于应用服务器提供的数据源，Hibernate不会改变这些连接的事务隔离级别。在这种情况下，应该通过修改应用服务器的数据源配置来修改隔离级别。
当数据库系统采用Red Committed隔离级别时，会导致不可重复读和第二类丢失更新的并发问题，在可能出现这种问题的场合。可以在应用程序中采用悲观锁或乐观锁来避免这类问题。
mysql查看当前事务隔离级别：select @@tx_isolation
设置事务隔离级别：set [glogal | session] transaction isolation level 隔离级别名称;或set tx_isolation=’隔离级别名称;’
悲观锁
正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。
一个典型的依赖数据库的悲观锁调用：select * from account where name=”Erica” for update这条 sql 语句锁定了 account 表中所有符合检索条件 name=”Erica” ）的记录。本次事务提交之前（事务提交时会释放事务过程中的锁），外界无法修改这些记录。悲观锁，也是基于数据库的锁机制实现。
在Hibernate使用悲观锁十分容易，但实际应用中悲观锁是很少被使用的，因为它每次发送的SQL语句都会加上"for update"用于告诉数据库锁定相关数据，大大限制了并发性：
乐观锁
相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（Version）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个"version"字段来实现。
乐观锁的工作原理：读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。
概念：用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引); 复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引; 同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引; 设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效;
like写法一般为select cat from animal where name like %猫%
用explain解释来看sql语句并没有运用索引（name已经创建索引），而是全表扫描。
尽量不要使用 like '%..%'
对于 like '..%..' (不以 % 开头)
对于 like '%...' 的 (不以 % 结尾)，加个reverse 函数，又可以用上索引了'(需要反向索引的支持)SQL> select * from test_like where reverse(object_name)like reverse('%AS');
使用locate函数代替like
语法一 LOCATE(substr,str)返回字符串substr中第一次出现子字符串的位置 str。
语法二:LOCATE(substr,str,pos)返回 substr 在 str 中第一次出现的位置，如果 substr 在 str 中不存在，返回值为 0 。如果pos存在，返回 substr 在 str 第pos个位置后第一次出现的位置，如果 substr 在 str 中不存在，返回值为0。
sql语句优化
索引优化
选择合适的存储引擎
字段选择合适的数据类型
对表进行水平或者垂直拆分
针对存储引擎的优化
磁盘I/O优化
负载均衡
主从复制
Copyright © 2022.Company name All rights reserved. 冀ICP备14009098号-3
评论
您发表的评论需要审核通过后才会展示在评论区内，请勿重复评论！