一.存储引擎的选择
选择不同的存储引擎实质就是选择了不同的文件处理机制,不同的文件处理机制决定了mysql的速率
如何选择:
功能:事务,外键,表级/行级锁定
性能:读,写
服务器兼容性:是否支持
1.MyISAM(擅长插入和查询,不擅长处理大量的更新、删除业务)
1)插入数据速度较快(按照插入顺序进行存储的,即插入顺序与存储顺序一致).
2)索引文件和数据是分开的。
3)只有MyISAM支持全文索引
缺点:
1)数据删除,新数据并不补位,造成空间漏洞。所有要定期执行修复空间漏洞的操作。
修复空间漏洞语句:myisamchk -r 表名
2)只支持表级锁定,并发处理相对较弱
2.Innodb(删除写操作)
1)Innodb表支持事务,支持外键,支持行级锁定和表级锁定
2)执行插入数据较慢。(插入数据的同时要按照主键顺序进行存储)
3)表的数据和索引都存储到一个文件中,该文件叫innodb的表空间文件
4)擅长处理并发
5)擅长处理复杂数据完整性,一致性
6)若应用程序,需要大量的安全数据或需要大量的并发写操作,选择innodb
3.Memory
内存型存储引擎
将数据存储到内存中,服务器关闭/重启,会导致数据丢失
缓存型为主
4.Archive
存档型
仅仅提供高效的插入和读
适合日志类
二.列类型的选择
1.尽可能占用更少的存储空间
Tinyint:1字节 int:4字节
2.尽可能选择占用空间固定数据
Varchar:变长 Char: 定长
Double:双精度浮点数,定长8个字节
Decimal:定点数,变长,随着数值的增大,而占用更多的空间
3.尽可能使用整数类型
整型的运算速度最快
ip转整:select inet_aton('192.168.21.199'); 3232236010
整转ip:select inet_ntoa(3232236010); 192.168.21.199
4.尽可能使用not null
null值,特殊值,mysql都需要额外的存储空间存储
例如:商品表的cat_id int unsigned not null default 0
三.索引的管理
索引一定需要一定的存储空间
注意:通常主键索引上都有自动增长,而自动增长依赖于主键
若要删除主键索引,需要先删除自动增长
1.普通索引 index
对关键字,没有任何要求
2.唯一索引 unique index
要求索引关键字不能重复
3.主键索引 primary index
要求关键字不能重复,同时不能null
4.全文索引 fulltext index
生成关键字的方式不同
除全文索引外,其他的索引都是,直接使用字段数据作为索引关键字。
而全文索引:先对需要索引的字段,进行关键字提取,分析内容,将其中有意义的词提取作为关键字
5.复合索引
若索引关键字,依赖于多个字段的数据,该索引就是复合索引
四.语句优化
1.查询时,尽量避免全表扫描
避免使用!= < >操作符
避免使用or
in , not in慎用
like '%a%'模糊查询 慎用
*改为查找的指定字段
2.不使用count(id),而使用count(*)
3.批量插入语句,节省交互
4.limit基数大时,使用between
5.避免在列上做运算,会导致索引失败
6.通配符%写在后面会快一些
7.最左原则
8.order by null 禁止排序
9.order by rand() 禁止
目的是,随机获取记录。
Select * from table-name order by rand() limit 10;
Rand()需要执行 :有几条记录,执行几次。
10.单表查询
一次操作仅仅操作一张表,避免多张表同时占用。
分离数据,提升查询缓存的利用率,为了并发
11.Union & union all
Union distinct 涉及到结果组合(去重)。Union all直接联合即可!
12.避免使用子查询,使用join代替