江苏龙网

  1. 主页 > 生活百科 > >

一名高级的Javaer，应该了解的 MYSQL 高级知识点( 三 )

mysql


mysql> show variables like 'log_queries_not_using_indexes';+-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| log_queries_not_using_indexes | OFF |mysql> set global log_queries_not_using_indexes=1;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'log_queries_not_using_indexes';+-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| log_queries_not_using_indexes | ON |+-------------------------------+-------+1 row in set (0.00 sec)MySQL存储引擎:InnoDB(推荐):
优点:

  • Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别
  • 支持多版本并发控制的行级锁，由于锁粒度小，写操作和更新操作并发高、速度快 。
  • 支持自增长列 。
  • 支持外键 。
  • 适合于大容量数据库系统，支持自动灾难恢复 。
缺点:
  • 它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表
应用场景 :
  •  当需要使用数据库事务时，该引擎当然是首选 。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率
  • 更新密集的表, InnoDB存储引擎特别适合处理多重并发的更新请求
MyISAM:优点:
  • MyISAM存储引擎在查询大量数据时非常迅速，这是它最突出的优点
  • 另外进行大批量插入操作时执行速度也比较快 。
缺点:
  • MyISAM表没有提供对数据库事务的支持 。
  • 不支持行级锁和外键 。
  • 不适合用于经常UPDATE（更新）的表，效率低 。
应用场景:
  • 以读为主的业务，例如：图片信息数据库，博客数据库，商品库等业务 。
  • 对数据一致性要求不是非常高的业务（不支持事务）
  • 硬件资源比较差的机器可以用 MyiSAM （占用资源少）
MySQL索引优化:普通索引:
CREATE INDEX <索引的名字> ON tablename (字段名);ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );唯一索引:
CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;主键索引:
CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );ALTER TABLE tablename ADD PRIMARY KEY (字段名);复合索引:
用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引） 。复合索引可以代替 多个单一索引，相比多个单一索引复合索引所需的开销更小 。
CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );复合索引注意事项:
  • 1. 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对 更新操作效率有很大影响 。
  • 2. 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索 引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提 高 。
全文索引:
查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很 低 。如果使用全文索引，查询速度会比like快很多倍 。
CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如:
SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aabb');-- * 表示通配符,只能在词的后面SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aa*' IN BOOLEAN MODE);全文索引使用注意事项：
  • 全文索引必须在字符串、文本字段上建立 。
  • 全文索引字段值必须在最小字符和最大字符之间的才会有效 。（innodb：3-84； myisam：4-84）
创建索引的原则:


推荐阅读


上一篇：鼠尾草第二年还会长吗,鼠尾草与海盐好闻吗

下一篇：我写代码时的小倔强