mysql-优化班学习-11-20170622-MySQL索引-3
pt-osc
SchemaChangeOnline
OnlineSchemaChange
【管理员】助教-Gakki<anqingye@qq.com> 2017/6/22 20:40:17
预估
1、并发请求
dau
【管理员】助教-Gakki<anqingye@qq.com> 2017/6/22 20:40:52
10000
100次
tps
【管理员】助教-Gakki<anqingye@qq.com> 2017/6/22 20:43:20
私有云平台
【管理员】助教-Gakki<anqingye@qq.com> 2017/6/22 20:44:23
slow log
SchemaChangeOnline
OnlineSchemaChange
【管理员】助教-Gakki<anqingye@qq.com> 2017/6/22 20:40:17
预估
1、并发请求
dau
【管理员】助教-Gakki<anqingye@qq.com> 2017/6/22 20:40:52
10000
100次
tps
【管理员】助教-Gakki<anqingye@qq.com> 2017/6/22 20:43:20
私有云平台
【管理员】助教-Gakki<anqingye@qq.com> 2017/6/22 20:44:23
slow log
loadrunner模拟用户压测
查询索引使用情况的的语句( 检查索引长度超过20的 ):
【管理员】助教-Gakki<anqingye@qq.com> 20:54:01
select c.table_schema as `db`, c.table_name as `tbl`,
c.COLUMN_NAME as `col`, c.DATA_TYPE as `col_type`,
c.CHARACTER_MAXIMUM_LENGTH as `col_len`,
c.CHARACTER_OCTET_LENGTH as `col_len_bytes`,
s.NON_UNIQUE as `isuniq`, s.INDEX_NAME, s.CARDINALITY,
s.SUB_PART, s.NULLABLE
from information_schema.COLUMNS c inner join information_schema.STATISTICS s
using(table_schema, table_name, COLUMN_NAME) where
c.table_schema not in ('mysql', 'sys', 'performance_schema', 'information_schema', 'test') and
c.DATA_TYPE in ('varchar', 'char', 'text', 'blob') and
((CHARACTER_OCTET_LENGTH > 20 and SUB_PART is null) or
SUB_PART * CHARACTER_OCTET_LENGTH/CHARACTER_MAXIMUM_LENGTH >20);
select c.table_schema as `db`, c.table_name as `tbl`,
c.COLUMN_NAME as `col`, c.DATA_TYPE as `col_type`,
c.CHARACTER_MAXIMUM_LENGTH as `col_len`,
c.CHARACTER_OCTET_LENGTH as `col_len_bytes`,
s.NON_UNIQUE as `isuniq`, s.INDEX_NAME, s.CARDINALITY,
s.SUB_PART, s.NULLABLE
from information_schema.COLUMNS c inner join information_schema.STATISTICS s
using(table_schema, table_name, COLUMN_NAME) where
c.table_schema not in ('mysql', 'sys', 'performance_schema', 'information_schema', 'test') and
c.DATA_TYPE in ('varchar', 'char', 'text', 'blob') and
((CHARACTER_OCTET_LENGTH > 20 and SUB_PART is null) or
SUB_PART * CHARACTER_OCTET_LENGTH/CHARACTER_MAXIMUM_LENGTH >20);
SELECT
a.TABLE_SCHEMA as `db`,
a.TABLE_NAME as `tbl`
FROM
(
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema. TABLES
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
)
) AS a
LEFT JOIN (
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = 'PRIMARY KEY'
) AS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
WHERE
b.TABLE_NAME IS NULL;
索引何时不可用???