emp/emp3是myisam存储引擎 emp1/emp4是innodb存储引擎、
emp/emp1 empno、ename、dept加了索引 emp3/emp4没有索引
补充:
InnoDB引擎: 数据文件和索引文件存储在一个文件中,主键索引默认直接指向数据存储位置。
MyAsm引擎
该引擎把每个表都分为几部分存储,比如员工表,包含emp.frm,emp.MYD和emp.MYI。
emp.frm负责存储表结构
emp.MYD负责存储实际的数据记录,所有的用户记录都存储在这个文件中
emp.MYI负责存储用户表的所有索引,这里也包括主键索引。
对于数据库性能的提高,索引应该是首选。有时候正确的索引能将性能提升百倍或千倍以上,当
然查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的I/O。
这是加上主键索引的速度
索引就是将数据库中的字段进行排序的结果,通过索引就能找到想要的数据(本质是一种数据结构 B+tree)。就像新华字典里按照首字母排序进行索检索一样,这就是索引。
索引的类型:
主键索引(把某列设置为主键,则该列也是索引)
唯一索引(该列具有唯一性,又是索引)
普通索引(index)
全文索引(FULLTEXT)
①创建索引:
alter table 表名 add index(字段名);
添加主键索引 alter table 表名 add primary key(字段名);
②删除索引
drop index 索引名 on 表名 或者是 alter table 表名 drop index 索引名
删除主键索引: alter table 表名 drop primary key;
③查询索引:
show index from 表名 或 show keys from 表名 或 desc 表名(可以再sql语句后加\G方便查看)
④查看索引的使用情况
show status like ‘Handler_read%’;
handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高,说明查询低效(这里主要是我做实验没用索引导致,数值比较大)。
**较频繁的作为查询条件字段应该创建索引
**唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件 比如: select * from emp where sex = '男'
**更新特别频繁的字段不适合创建索引 比如登入次数
**不会出现在where子句中的字段不该创建索引。
①查询要使用到索引最重要的是在查询条件中使用索引
②对于使用like的查询,查询如果是 ‘%sdhd’ 不会使用到索引‘sdhd%’ 会使用到索引。
为emp表中的ename加上索引,对比如下:
③如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引。
④对于多列索引,不是使用的第一部分,则不会使用索引。
对于innodb存储而言,用的不是第一个索引,则加索引和不加索引的效率差不多
对于innodb(第2个)和myisam(第1个)(都加索引的情况下)对比而言
如果用第一个索引,则很快
对于innodb而言:
对于myisam则很快:
如果是独立索引,相对会快得多(第一个:myisam,第二个:innodb):
⑥尽量不要在where条件中使用表达式,会进行全表扫描(可用第二种方式)
看一下handler_read_rnd_next这个变量值明显进行了全表扫描
⑦优化group by 语句
默认情况,MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但用户想要避免排序结果的消耗,则可以使用order by null禁止排序
测试了几次基本没差别(不知为啥都说这样能优化??)
对于innodb(第2个)和myisam(第1个)对比而言
但如果有查询条件的话,二者查询效率差不多
⑨选择合适的存储引擎
MyISAM:如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高。其优势是访问的速度快(从上面的图标对比看到)。
InnoDB:提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM,写的处理效率差一些并且会占用更多的磁盘空间。
10.选择合适的数据类型====在精度要求高的应用中,建议使用定点数来存储数值,以保证结果的准确性。
====对于存储引擎是MyISAM的数据库,如果经常做删除和修改记录的操作,要定时执行optimize table table_name;功能对表进行碎片整 理
====日期类型要根据实际需要选择能够满足应用的最小存储的日期类型,比如发帖时间用int来存储,找三天内的帖子,只要date(时间-3*24*60*60);
对表进行水平划分
如果一个表的记录数太多了,比如上千万条,而且需要经常检索,那么我们就有必要化整为零了。如果我拆成100个表,那么每个表只有10万条记录。比如系统界面上只提供按月查询的功能,那么把表按月 拆分成12个,每个查询只查询一个表就够了。
11.对表进行垂直划分
有些表记录数并不多,可能也就2、3万条,但是字段却很长,表占用空间很大,检索表时需要执行大量I/O,严重降低了性能。这个时候需要把大的字段拆分到另一个表,并且该表与原表是一对一的关系。
12.选择适当的字段类型
特别是主键 选择字段的一般原则是保小不保大,能用占用字节小的字段就不用大字段。比如主键, 建议使用自增类型,这样省空间,空间就是效率!按4个字节和按32个字节定位一条记录,谁快谁慢太明显了。涉及到 几个表做join时,效果就更明显了。建议使用一个不含业务逻辑的id做主键
13.文件、图片等大文件用文件系统存储数据库只存储路径。图片和文件存放在文件系统,甚至单独放在一台服务器(图床).
第三部分:数据库参数配置
最重要的参数就是内存 ,我们主要用的innodb引擎,所以下面两个参数调的很大
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G
对于myisam,需要调整key_buffer_size
当然调整参数还是要看状态,用show status语句可以看到当前状态,以决定改调整 哪些参数
第四部分:硬件资源和操作系统
如果你的机器内存超过4G,那么毋庸置疑应当采用64位操作系统和64位mysql
读写分离
如果数据库压力很大,一台机器支撑不了,那么可以用mysql复制实现多台机器同步,将数据库的压力分散。