c 网站建设,织梦做网站如何套取别人网站的模板,网站开发购物店,在线制作印章免费目录
一、MySQL事务
1. 概念
2. 事务的ACID原则
3. MySQL实现事务的方法
4. MySQL实现事务的步骤
5. 事务的原子性、一致性、持久性
6. 事务的隔离性
7. MySql中的锁
1. 共享锁
2. 排他锁
3. 行级锁
4. 表级锁
5. 间隙锁
6. 临键锁
7. 记录锁
8. 意向共享锁…目录
一、MySQL事务
1. 概念
2. 事务的ACID原则
3. MySQL实现事务的方法
4. MySQL实现事务的步骤
5. 事务的原子性、一致性、持久性
6. 事务的隔离性
7. MySql中的锁
1. 共享锁
2. 排他锁
3. 行级锁
4. 表级锁
5. 间隙锁
6. 临键锁
7. 记录锁
8. 意向共享锁
9. 意向排它锁
8.事务的隔离级别⭐⭐⭐
9. InnoDB的MVCC 二、MySQL函 数
1. 常用的日期函数
时间差
2. 常用的字符串函数
3. 常用的数学函数
4. 常用的聚合函数
三、慢查询
执行计划
四、索引
1. 索引的分类
2. 主键索引
3. 唯一索引
4. 常规索引
5. 全文索引
6. 索引的操作
7. 优化原则背
7.1 创建索引
7.2 SQL编写
7.3 索引失效 一、MySQL事务
1. 概念 事务Transaction就是将一组SQL语句放在同一批次内去执行如果一个SQL语句出错则该批次内的所有SQL都将被取消执行。 事务是一个过程 在进行中 特点一个事务中如果有一个数据库操作失败那么整个事务的所有数据库操作都会失败数据库数据就会回滚到该事务开始之前的状态。 限制MySQL数据库中仅InnoDB和BDB类型的数据库表 支持事务。 2. 事务的ACID原则 原子性意味着数据库中的 事务执行是作为原子粒度。即不可再分整个语句要么执行要么不执行 。 一致性即在事务开始之前和 事务结束以后数据 库的完整性约束没有 被破坏。 隔离性事务的执行是互不 干扰的一个事务 不可能看到其他事 务运行时中间某 一时刻的数据。 持久性意味着在事务完成以后 该事务所对数据库所 作的更改便持久的保存 在数据库之中并不会 被回滚。 原子性Atomicity指事务是一个不可分割的最小工作单位事务中的操作只有都发生和都不发生两种情况一致性Consistency事务必须使数据库从一个一致状态变换到另外一个一致状态举一个栗子李二给王五转账50元其事务就是让李二账户上减去50元王五账户上加上50元一致性是指其他事务看到的情况是要么李二还没有给王五转账的状态要么王五已经成功接收到李二的50元转账。而对于李二少了50元王五还没加上50元这个中间状态是不可见的。隔离性Isolation一个事务的执行不能被其他事务干扰即一个事务内部的操作及使用的数据对并发的其他事务是隔离的并发执行的各个事务之间不能互相干扰。持久性Durability一个事务一旦提交成功它对数据库中数据的改变将是永久性的接下来的其他操作或故障不应对其有任何影响。 3. MySQL实现事务的方法 START TRANSACTION开始一个事务,标记事务的起始 点。COMMIT提交一个事务给数据库。ROLLBACK将事务回滚数据回到本次事务的初始状态。SET AUTOCOMMIT使用该语句来改变自动提交模式等于0时关闭自动提交模式 等于1时开启自动提交模式。默认为1使用事务时为0。 互斥 要么提交 要么回滚 4. MySQL实现事务的步骤 关闭MySQL自动提交 SET AUTOCOMMIT 0开启一个事务标记事务的起始点 START TRANSACTION;结束事务 向数据库提交事务 COMMIT;将事务回滚所有的数据库操作被取消 ROLLBACK 开启MySQL自动提交 SET AUTOCOMMIT 1 -- 创建表
create table bank(bid int primary key auto_increment,bname varchar(10),bmoney decimal(20,2)
);insert into bank(bname,bmoney)
values(zkt1,20000000000000000);
insert into bank(bname,bmoney)
values(zkt2,5);select * from bank;
-- 关闭自动提交
set autocommit 0;
-- 手动开启一个事务
start TRANSACTION;
-- 一组sql语句
update bank set bmoneybmoney-1000 where bnamezkt1;
update bank set bmoneybmoney1000 where bnamezkt2;
-- 结束事务
-- 1. 提交
COMMIT;
-- 2.回滚
rollback;
-- 开启自动提交
set autocommit 1; 如果选择提交 则修改成功 如果转账失败 选择rollback 比如转账给zkt3 就会转账失败 这样就可以选择rollback 进行回滚 roolback回到之前的数据 5. 事务的原子性、一致性、持久性 事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。 REDO LOG 称为 重做日志 提供再写入操作恢复提交事务修改 的页操作用来保证事务的持久性。UNDO LOG 称为 回滚日志 回滚行记录到某个特定版本用来保 证事务的原子性、一致性。 6. 事务的隔离性 为了让不同的事务之间相互不存在干扰就需要对事务的操作进行隔离 事务的隔离性也就是将操作同一个数据的事务相互分离让操作之间分开有序的执行 通常数据库里都是采用锁的机制保证事务之间的隔离性。 7. MySql中的锁 锁分类 基于锁的属性分类共享锁读锁、S锁、排他锁写锁X锁。 1. 共享锁 共享锁又称之为读锁简称S锁共享锁就是多个事务对于同一数据可以共享一把锁都能访问到数据但是只能读不能修改。只有当数据上的读锁被释放后其他事务才能对其添加写锁。共享锁主要是为了支持并发的读取数据而出现的读取数据时不允许其他事务对当前数据进行修改操作从而避免”不可重读”的问题的出现。 2. 排他锁 排他锁锁住一行数据后其他事务不能再在其上加其他的锁。 排它锁又称之为写锁、独占锁简称X锁当事务对数据加上写锁后其他事务既不能对该数据添加读锁也不能对该数据添加写锁写锁与其他锁都是互斥的。只有当前数据写锁被释放后其他事务才能对其添加写锁或者是读锁。写锁主要是为了解决在修改数据时不允许其他事务对当前数据进行修改和读取操作从而可以有效避免”脏读”问题的产生。 mysql InnoDB引擎默认的修改数据语句(update,delete,insert)都会自动给涉及到的数据加上排他锁select语句默认不会加任何锁类型如果加排他锁可以使用select ...for update语句。 加过排他锁的数据行在其他事务种是不能修改数据的也不能通过for update和lock in share mode锁的方式查询数据但可以直接通过select ...from...查询数据因为普通查询没有任何锁机制。 基于锁的粒度分类表锁、行锁(记录锁、间隙锁、临键锁)。 3. 行级锁 行级锁是粒度最低的锁发生锁冲突的概率也最低、并发度最高。但是加锁慢、开销大容易发生死锁现象。MySQL中只有InnoDB支持行级锁行级锁可分为共享锁和排他锁。 4. 表级锁 表级锁会对当前操作的整张表加锁最常使用的 MyISAM 与 InnoDB 都支持表级锁定。 MySQL 里面表级别的锁有两种一种是表锁一种是元数据锁meta data lockMDL)。 5. 间隙锁 间隙锁基于非唯一索引它锁定一段范围内的索引记录。使用间隙锁锁住的是一个区间而不仅仅是这个区间中的每一条数据。 6. 临键锁 临键锁是记录锁与间隙锁的组合它的封锁范围既包含索引记录又包含索引区间是一个左开右闭区间。临键锁的主要目的也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC临键锁则也会失效。 每个数据行上的非唯一索引列上都会存在一把临键锁当某个事务持有该数据行的临键锁时会锁住一段左开右闭区间的数据。需要强调的一点是InnoDB 中行级锁是基于索引实现的临键锁只与非唯一索引列有关在唯一索引列包括主键列上不存在临键锁。 7. 记录锁 记录锁是封锁记录记录锁也叫行锁例如 select *from goods where id1 for update; 它会在 id1 的记录上加上记录锁以阻止其他事务插入更新删除 id1 这一行 基于锁的状态分类意向共享锁、意向排它锁。 8. 意向共享锁 意向共享锁(intention shared lock, IS)它预示着事务有意向对表中的某些行加共享S锁。 select ... lock in share mode要设置IS锁事务要获得某些行的S锁必须先获得表的IS锁。 9. 意向排它锁 意向排它锁(intention exclusive lock, IX)它预示着事务有意向对表中的某些行加排它X锁。 select ... for update要设置IX锁事务要获得某些行的X锁必须先获得表的IX锁。 意向锁 不加锁 会有一个版本号 8.事务的隔离级别⭐⭐⭐ 事务并发问题 在事务并发执行的时候如果不进行事务隔离那么就会产生脏写、脏读、 不可重复读、幻读的问题。 脏写 Dirty Write 对于两个事务 Session A、Session B如果事务Session A 修改了另一个未提交 事务Session B 修 改过 的数据那就意味着发生了脏写最总写了什么不知道。脏读 Dirty Read 对于两个事务 Session A、Session BSession A 读取 了已经被 Session B 更新 但还没有被提交的字段。 之后若 Session B 回滚 Session A 读取 的内容就是临时且无效 的。不可重复读 Non-Repeatable Read 对于两个事务Session A、Session BSession A 读取 了一个字段然后 Session B 更新 了该字段。 之后 Session A 再次读取 同一个字段 值就不同 了。那就意味着发生了不可重复读。 我们在Session B中提交了几个 隐式事务 注意是隐式事务意味着语句结束事务就提交了这些事务 都修改了studentno列为1的记录的列name的值每次事务提交之后如果Session A中的事务都可以查看 到最新的值这种现象也被称之为 不可重复读 。幻读 Phantom 对于两个事务Session A、Session B, Session A 从一个表中读取 了一个字段, 然后 Session B 在该表中插入了一些新的行。 之后, 如果 Session A 再次读取同一个表, 就会多出几行。那就意味着发生了幻读。 Session A中的事务先根据条件 studentno 0这个条件查询表student得到了name列值为张三的记录 之后Session B中提交了一个 隐式事务 该事务向表student中插入了一条新记录之后Session A中的事务 再根据相同的条件 studentno 0查询表student得到的结果集中包含Session B中的事务新插入的那条记 录这种现象也被称之为 幻读 。我们把新插入的那些记录称之为 幻影记录 。 事务的隔离级别 READ_UNCOMMITTED 读未提交READ_COMMITTED 读提交不可重复读REPEATABLE_READ 可重复读默认的隔离级别SERIALIZABLE 串行化 事务的隔离级别解决的问题 9. InnoDB的MVCC MVCC Multiversion Concurrency Control多版本并发控制。顾名思义MVCC 是通过数据行的多个 版 本管理来实现数据库的 并发控制 。这项技术使得在InnoDB的事务隔离级别下执行 一致性读 操作有 了保 证。换言之就是为了查询一些正在被另一个事务更新的行并且可以看到它们被更新之前的值 这样 在做查询的时候就不用等待另一个事务释放锁。 MVCC 的实现依赖于隐藏字段、Undo Log、Read View。 InnoDB就是通过MVCC机制解决可重复读中的幻读问题 二、MySQL函 数 MySQL函数是一种控制流程函数属于数据库用语言。 日期时间函数对于日期和时间类型的字段进行处理字符串函数对于字符串类型的字段处理数学函数用作常规的数学运算聚合函数常用于GROUP BY从句的 SELECT查询中自定义函数根据实际需求自定义函数 1. 常用的日期函数 函数名返回值函数名返回值CURDATE()返回当前的日期HOUR(time)返回time的小时值(0~23)CURTIME()返回当前的时间MINUTE(time)返回time的分钟值(0~59)NOW()返回当前的日期和时间SECOND(time)返回time的秒(0~59)DAYOFWEEK(date)返回date所代表的一星期中的第几天(1~7)DATE_FORMAT(date,fmt)依照指定的fmt格式格式化日期 date值DAYOFMONTH(date)返回date是一个月的第几天 (1~31)WEEK(date)返回日期date为一年中的第几周(0~53)DAYOFYEAR(date)返回date是一年的第几天 (1~366)DAY(date)返回date的日MONTH(date)返回date的月份值(1~12)YEAR(date)返回日期date的年份 (1000~9999) # 函数-- 日期函数
select curdate(); select curtime(); select now(); select dayofweek(now()); select dayofmonth(now()); select dayofyear(now()); select month(now()) -- 获取到当月过生日的学生信息
select * from student where month(birthday)month(now()); select date_format(now(),%a#%b#%D); 时间差 -- 时间差 返回值为天
select datediff(now(),2024-1-1);
select datediff(2024-1-1,now());select TIMESTAMPDIFF(month,now(),2024-1-1);select TIMESTAMPDIFF(second,2002-11-18,now()); A.T_DATE B.T_DATE interval 1 hour——即 A表时间 B表时间 1小时也可以为分钟、秒。
或者
DATE_ADD(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit) 时间间隔字面量说明INTERVAL 1 YEAR时间间隔为1年INTERVAL 2 Month时间间隔为2月INTERVAL 3 DAY时间间隔为3天INTERVAL 4 HOUR时间间隔为4小时INTERVAL 25 MINUTE时间间隔为25分钟INTERVAL 45 SECOND时间间隔为45秒 select now() - interval 1 year;select DATE_ADD(now(),interval 5 year);select DATE_SUB(now(),interval 5 year); 2. 常用的字符串函数 函数名返回值ASCII(char)返回字符的ASCII码值BIT_LENGTH(str)返回字符串的比特长度CONCAT(s1,s2...sn)将s1,s2...,sn连接成字符串QUOTE(str)用反斜杠转义str中的单引号CONCAT_WS(sep, s1,s2...sn)将s1,s2...,sn连接成字符串并用sep字符间隔LTRIM(str)从字符串str中切掉开头的空格TRIM(str)去除字符串首部和尾部的所有空格POSITION(substr in str)返回子串substr在字符串str中第一次出现的位置从1开始LOWER(str)返回将字符串str中所有字符改变为小写的结果UPPER(str)返回将字符串str中所有字符转变为大写的结果LEFT(str,x)返回字符串str中最左边的x个字符STRCMP(s1,s2)比较字符串s1和s2LENGTH(s)返回字符串str中的字符数REVERSE(str)返回颠倒字符串str的结果 select concat(hello,zkt1); select CONCAT_WS(abc,hello,zkt2,nb); 3. 常用的数学函数 函数名返回值ABS(x)返回x的绝对值BIN(x)返回x的二进制CEILING(x)返回大于x的最小整数值EXP(x)返回值e自然对数的底的x次方FLOOR(x)返回小于x的最大整数值GREATEST(x1,x2,...,xn)返回集合中最大的值LEAST(x1,x2,...,xn)返回集合中最小的值LN(x)返回x的自然对数LOG(x,y)返回x的以y为底的对数MOD(x,y)返回x/y的模余数PI()返回pi的值圆周率ROUND(x,y)返回参数x的四舍五入的有y位小数的值SIGN(x)返回代表数字x的符号的值SQRT(x)返回一个数的平方根TRUNCATE(x,y)返回数字x截短为y位小数的结果RAND()返回0到1内的随机值可以通过提供一个参数(种子)使RAND()随机数生成 -- 向上取整
select ceiling(3.14); --4
-- 向下取整
select floor(3.99); --3
-- 四舍五入
select round(3.25,1); -3.3
-- 需要传入两个参数直接截断 不进行四舍五入
select truncate(3.19,1); --3.1 4. 常用的聚合函数 函数名返回值AVG(col)返回指定列的平均值COUNT(col)返回指定列中非NULL值的个数MIN(col)返回指定列的最小值MAX(col)返回指定列的最大值SUM(col)返回指定列的所有值之和GROUP_CONCAT(col)返回由属于一组的列值连接组合而成的结果 详见MySQL2 -- 聚合函数
select ssex,count(*) from student group by ssex;-- 一般不用
select ssex,GROUP_CONCAT(sname) from student group by ssex; 三、慢查询 MySQL默认10秒内没有响应SQL结果,则为慢查询 # 慢查询
-- 显示到mysql数据库的连接数
show status like connections;
-- 查看慢查询的状态
Show variables like %slow_query%;
-- 设置慢查询的到表 mysql.slow_log
set global log_outputTABLE;
-- 设置慢查询的时间
set global long_query_time3;
-- 开启慢查询
set global slow_query_logON;
-- 慢查询的次数
show status like slow_queries;
-- 慢查询记录
select * From mysql.slow_log ;
-- 慢查询sql语句
select convert(sql_text using utf8) sql_text from mysql.slow_log
-- 关闭慢查询
set global slow_query_logOFF; 设置的最大连接数 count 用* 或者常量统计 几乎没有区别 用非主键的字段去统计 时间会很慢 -- 慢查询-- 相关操作
show status like connections;
Show variables like %slow_query%;
set global slow_query_logON;
set global log_outputTABLE;
set global long_query_time5;show status like slow_queries;
select * From mysql.slow_log ;
select convert(sql_text using utf8) sql_text from mysql.slow_logselect count(*) from emp;show create table emp;CREATE TABLE emp (eid bigint(20) DEFAULT NULL,ename varchar(10) DEFAULT NULL,esex varchar(5) DEFAULT NULL,ebirthday datetime DEFAULT NULL,ehisday datetime DEFAULT NULL,job varchar(15) DEFAULT NULL,emoney decimal(10,2) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci执行计划 问题 当我们在查询前能否预先估计查询究竟要涉及多少行、使用哪些索引、运行时间呢 答案是能的mysql提供了相应的功能和语法来实现该功能。 分析 MySql提供了EXPLAIN语法用来进行查询分析在SQL语句前加一个EXPLAIN即可。 比如我们要分析如下SQL语句 explain select * from table where table.id 1 运行上面的sql语句后你会看到 下面的表头信息 table | type | possible_keys | key | key_len | ref | rows | Extra EXPLAIN列的解释 table 显示这一行的数据是关于哪张表的 type 这是重要的列显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、 index和ALL 说明不同连接类型的解释按照效率高低的顺序排序 system表只有一行system表。这是const连接类型的特殊情况。const 表中的一个记录的最大值能够匹配这个查询索引可以是主键或惟一索引。因为只有一行这个值实际就是常数因为 MYSQL先读这个值然后把它当做常数来对待。eq_ref在连接中MYSQL在查询时从前面的表中对每一个记录的联合都从表中读取一个记录它在查询使用了索引为主键 或惟一键的全部时使用。ref这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分比如利用最左边前缀时发生。对于之前的 表的每一个行联合全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。range这个连接类型使用索引返回一个范围中的行比如使用或查找东西时发生的情况。index这个连接类型对前面的表中的每一个记录联合进行完全扫描比ALL更好因为索引一般小于表数据。ALL这个连接类型对于前面的每一个记录联合进行完全扫描这一般比较糟糕应该尽量避免。 possible_keys 显示可能应用在这张表中的索引。如果为空没有可能的索引。可以为相关的域从WHERE语句中选择 一个合适的语句 key 实际使用的索引。如果为NULL则没有使用索引。很少的情况下MYSQL会选择优化不足的索引。 这种情况下可以在SELECT语句中使用USE INDEXindexname来强制使用一个索引或者用 IGNORE INDEXindexname来强制MYSQL忽略索引 key_len 使用的索引的长度。在不损失精确性的情况下长度越短越好 ref 显示索引的哪一列被使用了如果可能的话是一个常数 rows MYSQL认为必须检查的用来返回请求数据的行数 Extra 关于MYSQL如何解析查询的额外信息。是Using temporary和Using filesort意思MYSQL根本不能使用索 引结果是检索会很慢 说明extra列返回的描述的意义 Distinct 一旦mysql找到了与行相联合匹配的行就不再搜索了。Not exists mysql优化了LEFT JOIN一旦它找到了匹配LEFT JOIN标准的行就不再搜索了。Range checked for each Recordindex map:# 没有找到理想的索引因此对从前面表中来的每一 个行组合mysql检查使用哪个索引并用它来从表中返回行。这是使用索引的最慢的连接之一。Using filesort 看到这个的时候查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的 行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。Using index 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的这发生在对表 的全部的请求列都是同一个索引的部分的时候。Using temporary 看到这个的时候查询需要优化了。这里mysql需要创建一个临时表来存储结果 这通常发生在对不同的列集进行ORDER BY上而不是GROUP BY上。Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表 中的全部行并且连接类型ALL或index这就会发生或者是查询有问题。 四、索引 索引是对数据库表中一列或多列的值进行排序的一种结构使用索引可快速访问数据库表中的特定信息。 特点 高效性利用索引可以提高数据库的查询 效率.唯一性索引可以确保所查的数据的唯一 性完整性用户可以加速表和表之间的连接 实现表与表之间的参照完整性特殊能力通过使用索引可以在查询过程 中使用优化隐藏器提高系统 性能。 缺点 虽然索引大大提高了查询速度同时却会降低更新表的速度如对表进行INSERT、 UPDATE和DELETE。因为更新表时MySQL不仅要保存数据还要保存一下索引文件。建立索引会占用磁盘 空间的索引文件。如果你在一个大表上创建了多种组合索引索引文件的会膨胀很快。 索引只是提高效率的一个因素如果你的MySQL有大数据量的表就需要花时间研究建立 最优秀的索引或优化查询语句。 1. 索引的分类 主键索引在数据库关系图中为表定义一个主键将自动创建主键索引。唯一索引不允许具有索引值相同的行从而禁止重复的索引或键值。常规索引最基本的索引类型没有唯一性之类的限制。全文索引搜索引擎的关键技术用于检索文本信息 可以是词语或者段落。 2. 主键索引 语法 CREATE TABLE 表名 (
字段1 INT(11) AUTO_INCREMENT PRIMARY KEY,
#或 PRIMARY KEY(字段1)
)说明
• 某一个属性组能唯一标识一条记录
• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置。添加主键索引前 select * from emp where eid 10000
时间: 9.862ms
时间: 9.743ms 添加主键索引后 alter table emp add primary key(eid);
时间: 74.470msselect * from emp where eid 10000
时间: 0.001ms 3. 唯一索引 语法 CREATE TABLE 表名 (
字段1 INT(11) NOT NULL UNIQUE,
#或 UNIQUE KEY(字段1)
)说明
• 快速定位特定数据
• index和key关键字都可设置常规索引
• 应加在查找条件的字段
• 不宜添加太多常规索引影响数据的插入、删除和修改操作。 4. 常规索引 语法 CREATE TABLE 表名 (
字段1 INT(11) NOT NULL,
INDEX/KEY(字段1)
)说明
• 避免同一个表中某数据列中的值重复
• 对比主键索引只能有一个,唯一索引可有多个。添加常规索引前 select * from emp where eid 1000000
时间: 0.002ms添加常规索引后 explain select * from emp where ename 祁单于
时间: 9.188msalter table emp add index(ename);select * from emp where ename like %张%
时间: 2.470ms -- 后模糊可以用到索引
时间: 9.485ms -- 前模糊用不到索引 5. 全文索引 语法 CREATE TABLE 表名 (
字段1 VARCHAR(32) NOT NULL,,
fulltext key (字段名字段名字段名) with parser ngram
)ENGINEinnodb用法 create table wenzhang(wid int PRIMARY KEY auto_increment,title varchar(20), content text, zuozhe varchar(20), FULLTEXT(title,content,zuozhe) with parser ngram
);insert into wenzhang(title,content,zuozhe)
values
(西安往事,这是一个古老的城市在这个城市中有很多的人工厂建筑物,小杨),
(山西往事,这是一个古老的城市这里有很多的人工厂建筑,老候),
(地球往事,这是一个古老的星球这里有很多的人,老刘在西安),
(银河往事,这是一个系打算在这个系之外造一个西安,小彭);select * from wenzhang where match(title,content,zuozhe) AGAINST(西安);alter table wenzhang add fulltext(title,content) with parser ngram -- 中日韩特有的解析引擎select * from wenzhang where match(title,content) AGAINST(西安);必须要添加一个两个字段的全文索引 否则查找不到 注意 Mysql 5.6之前版本只有myisam支持全文索引5.6之后Innodb和myisam均支持全文索引。只有char、varchar、text类型字段能创建全文索引。当大量写入数据时建议先写入数据后再建立全文索引提高效率。Mysql内置ngram 解析器可以解析中日韩三国文字。有汉字的一定要启用它。英文分词用空格逗号中文分词用 ngram_token_size 设定. 6. 索引的操作 查看索引
SHOW INDEX(或KEYS) FROM 表名;删除索引
ALTER TABLE 表名 DROP PRIMARY KEY;
DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP INDEX 索引名;修改索引
ALERT TABLE 表名 ADD 索引类型数据列名;
ALTER TABLE 表名 add FULLTEXT INDEX 索
引名(字段名1字段2) [ WITH PARSER
ngram]; 7. 优化原则背
7.1 创建索引 【强制】InnoDB表必须主键为id int/bigint auto_increment且主键值 禁止被更新 。【强制】InnoDB和MyISAM存储引擎表索引类型必须为 BTREE 。【建议】主键的名称以 pk 开头唯一键以 uni 或 uk 开头普通索引以 idx 开头一律 使用小写格式以字段的名称或缩写作为后缀。【建议】多单词组成的columnname取前几个单词首字母加末单词组成column_name。如: sample 表 member_id 上的索引idx_sample_mid。【建议】单个表上的索引个数 不能超过6个 。【建议】在建立索引时多考虑建立 联合索引 并把区分度最高的字段放在最前面。【建议】在多表 JOIN 的SQL里保证被驱动表的连接列上有索引这样JOIN 执行效率最高。【建议】建表或加索引时保证表里互相不存在 冗余索引 。 比如如果表里已经存在key(a,b) 则key(a)为冗余索引需要删除。 7.2 SQL编写 【强制】程序端SELECT语句必须指定具体字段名称禁止写成 *。【建议】程序端insert语句指定具体字段名称不要写成INSERT INTO t1 VALUES(…)。【建议】除静态表或小表100行以内DML语句必须有WHERE条件且使用索引查找。【建议】INSERT INTO…VALUES(XX),(XX),(XX).. 这里XX的值不要超过5000个。 值过多虽然上线很 快但会引起主从同步延迟。【建议】SELECT语句不要使用UNION推荐使用UNION ALL并且UNION子句个数限制在5个以 内。【建议】线上环境多表 JOIN 不要超过5个表。【建议】减少使用ORDER BY和业务沟通能不排序就不排序或将排序放到程序端去做。ORDER BY、GROUP BY、DISTINCT 这些语句较为耗费CPU数据库的CPU资源是极其宝贵的。【建议】包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句WHERE 条件过滤出来的结果 集请保持在1000行以内否则SQL会很慢。【建议】对单表的多次alter操作必须合并为一次 对于超过100W行的大表进行alter table必须经过DBA审核并在业务低峰期执行多个alter需整 合在一起。 因为alter table会产生 表锁 期间阻塞对于该表的所有写入对于业务可能会产生极 大影响。【建议】批量操作数据时需要控制事务处理间隔时间进行必要的sleep。【建议】事务里包含SQL不超过5个。 因为过长的事务会导致锁数据较久MySQL内部缓存、连接消耗过多等问题。【建议】事务里更新语句尽量基于主键或UNIQUE KEY如UPDATE… WHERE idXX; 否则会产生间隙锁内部扩大锁定范围导致系统性能下降产生死锁。 7.3 索引失效 最佳左前缀法则主键插入顺序计算、函数导致索引失效类型转换导致索引失效范围条件右边的列索引失效不等于(! 或者)索引失效is null可以使用索引is not null无法使用索引like以通配符%开头索引失效OR 前后存在非索引的列索引失效数据库和表的字符集统一使用utf8mb4 越大越好 cardinalit/count(*) 越近1 索引越好 必须是联合索引
